Resolving Flashback Database Error: (ORA-38760: This database instance failed to turn on flashback database)

Resolving Flashback Database Error: (ORA-38760: This database instance failed to turn on flashback database)

Unable to allocate flashback log of 1634 blocks from
current recovery area of size 42949672960 bytes.
Recovery Writer (RVWR) is stuck until more space is
available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
Use ALTER SYSTEM SET db_recovery_file_dest_size command
to add space. DO NOT manually remove flashback log files
to create space.


so I brought down the db (aborted it)

SQL:.:(.):.> startup mount;
ORACLE instance started.

Total System Global Area 1694498816 bytes
Fixed Size 2139448 bytes
Variable Size 673405640 bytes
Database Buffers 889192448 bytes
Redo Buffers 129761280 bytes
Database mounted.
SQL:.:(.):.> select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE OLDEST_FLASHBACK_SCN OLDEST_FL
------------------------ -------------- -------------------- ---------
0 4.2926E+10 0

SQL:.:(.):.> SELECT NAME, SCN, TIME,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES'; 2 3 4

NAME SCN TIME GUA
------------------------------ ---------- --------------------------------------------------------------------------- ---
GRP_1 9.2118E+12 21-AUG-09 02.21.03.000000000 PM YES
GRP_2 9.2138E+12 26-AUG-09 08.29.42.000000000 AM YES

SQL:.:(.):.> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
2
OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
0


SQL:.:(.):.> flashback database to restore point GRP_2;

Flashback complete.

SQL:.:(.):.> select count(1) from v$datafile_header where fuzzy = 'YES';

COUNT(1)
----------
0

SQL:.:(.):.> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


in the log
---------------------------------------------------------------------------------------------------------------
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 15 processes
Flashback Media Recovery Log /sbclocal/app/oracle/admin/TESTDB/arch/TESTDB_0001_0695484339_0000000024.arc
Fri Aug 28 10:13:39 2009
Incomplete Recovery applied until change 9213823318145
Flashback Media Recovery Complete
Completed: flashback database to restore point GRP_2
Fri Aug 28 10:24:07 2009
alter database open resetlogs
Fri Aug 28 10:25:21 2009
RESETLOGS after incomplete recovery UNTIL CHANGE 9213823318145
Resetting resetlogs activation ID 4173768512 (0xf8c6a740)
Fri Aug 28 10:27:56 2009
Setting recovery target incarnation to 3
Fri Aug 28 10:27:56 2009
ORA-38760 signalled during: alter database open resetlogs...
Fri Aug 28 10:52:29 2009
---------------------------------------------------------------------------------------------------------------



SQL:oradbhost:(TESTDB):PRIMARY> drop restore point GRP_1;

Restore point dropped.


SQL:oradbhost:(TESTDB):PRIMARY> select status from v$instance;

STATUS
------------
MOUNTED

SQL:oradbhost:(TESTDB):PRIMARY> select count(1) from v$datafile_header where fuzzy = 'YES'l
2 .
SQL:oradbhost:(TESTDB):PRIMARY> select count(1) from v$datafile_header where fuzzy = 'YES';

COUNT(1)
----------
0


SQL:oradbhost:(TESTDB):PRIMARY> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL:oradbhost:(TESTDB):PRIMARY> flashback database to restore point GRP_2;
flashback database to restore point GRP_2
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL:oradbhost:(TESTDB):PRIMARY> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


SQL:oradbhost:(TESTDB):PRIMARY> alter system set db_recovery_file_dest_size=50G;

System altered.

SQL:oradbhost:(TESTDB):PRIMARY> flashback database to restore point GRP_2;
flashback database to restore point GRP_2
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL:oradbhost:(TESTDB):PRIMARY> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


-things to do if you got these scenario-
1) turn off the flashback database
2) drop the GRP
3) open the database normally(no resetlogs)
4) create a new GRP from that point