STARTUP Database failed ORA-38760 to turn on Flashback Database

SYMPTOMS

This is not RAC specific and could happen in Single Instances as well
if Flashback logs required are not available, as for example FLB logs was deleted using OS commands.

The following example shows symptoms seen in a RAC environment.

One of the RAC Instances does not come up, its looking for a Flash back log which has been accidently deleted.

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

——–From alert log of the thread 2 ——————

Thu May 16 05:17:45 2013
Allocated 15937344 bytes in shared pool for flashback generation buffer
Starting background process RVWR
RVWR started with pid=33, OS id=7292
Thu May 16 05:17:48 2013
Errors in file /<path>/udump/<SID>_ora_7244.trc:
ORA-38701: Flashback database log 25184 seq 14006 thread 1: “+<Disk>/<db unique name>/flashback/log_25184.25633.809806337”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_25184.25633.809806337
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_25184.25633.809806337’ does not exist
Thu May 16 05:17:48 2013
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE   MOUNT
Thu May 16 05:17:48 2013
ALTER DATABASE OPEN
ORA-38760 signalled during: ALTER DATABASE OPEN…                                                                   <<<< Error on opening database

Oracle is trying to turn on flashback database on startup automatically and failing with ORA-38760 as flashback log is missing.

CHANGES

Current flashback log deleted accidently from OS level.

CAUSE

When Current flashback log is deleted then database open will fail with following error:

Starting ORACLE instance (normal)
…….

Thu May 16 05:17:45 2013
Allocated 15937344 bytes in shared pool for flashback generation buffer
Starting background process RVWR
RVWR started with pid=33, OS id=7292
Thu May 16 05:17:48 2013
Errors in file /<path>/udump/<sid>_ora_7244.trc:
ORA-38701: Flashback database log 25184 seq 14006 thread 1: “+<Disk>/<db unique name>/flashback/log_25184.25633.809806337”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_25184.25633.809806337
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_25184.25633.809806337’ does not exist
Thu May 16 05:17:48 2013
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE   MOUNT
Thu May 16 05:17:48 2013
ALTER DATABASE OPEN
ORA-38760 signalled during: ALTER DATABASE OPEN…

OR Instance may crash with following error:ORA-38701: Flashback database log 101 seq 101 thread 1: “/<path>/flashback/o1_mf_15fb13pw_.flb”
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sat Apr 9 03:13:52 2005
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5725

SOLUTION

There are 2 possible solutions when a flashback log file was deleted manually:

(1) Turn off Flashback:SQL> alter database flashback off;

The Alert log will have the following entries when you turn off flashback:Thu May 16 05:18:01 2013
alter database flashback off
Thu May 16 05:18:01 2013
Flashback Database Disabled
…….
Completed: alter database flashback off                                                                              <<<<  Flashback disabled
…..

After this turn the flashback on again if required.SQL> alter database flashback on;

Now you can open the database.

(2) In cases where “Guaranteed Restore Point” is defined, RVWR will still try to write to flashback log even though you have disable it and it will fail again with same error:Completed: alter database flashback off                                                                              <<<<  Flashback disabled
…..

Thu May 16 05:19:05 2013
Shutting down instance: further logons disabled
Thu May 16 05:19:05 2013
Stopping background process CJQ0
……

Shutting down instance (immediate)
……..
Thu May 16 05:24:49 2013
Starting ORACLE instance (normal)
…….
Thu May 16 05:25:04 2013
Allocated 15937344 bytes in shared pool for flashback generation buffer
Starting background process RVWR
RVWR started with pid=33, OS id=19851
Thu May 16 05:25:05 2013
Errors in file /<path>/udump/<SID>_ora_19796.trc:                                                  
ORA-38701: Flashback database log 25184 seq 14006 thread 1: “+<Disk>/<db unique name>/flashback/log_25184.25633.809806337”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_25184.25633.809806337
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_25184.25633.809806337’ does not exist                                <<<< Still looking for deleted flashback log
Thu May 16 05:25:05 2013
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE   MOUNT
Thu May 16 05:25:05 2013
ALTER DATABASE OPEN
ORA-38760 signalled during: ALTER DATABASE OPEN…                                                                   <<<< Again failed with ORA-38760

Check if flashback database is disable or not. When checked the flashback status after disabling flashback, it shows ‘RESTORE POINT ONLY’ instead of ‘NO’.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
——————
RESTORE POINT ONLYFLASHBACK_ON – possible values are as follows:

    YES                – Flashback is on
    NO                 – Flashback is off
    RESTORE POINT ONLY – Flashback is on but one can only flashback to guaranteed restore points

 
So the cause of the issue is Guaranteed Restore Point created on database.
The database would still try to write flashback data to the current flashback log because the database still has at least one Guaranteed Restore Point declared in the controlfile.

Find out the name of Guaranteed Restore Point and delete so that database would not try to write to flashback log on startup:

Now we have 3 options to know the restore point name:

2.1) Check the name from v$restore_point view but that would also fail with same error:SQL> select * from v$restore_point;
select * from v$restore_point
*
ERROR at line 1:
ORA-38701: Flashback database log 2 seq 2 thread 1: “+<Disk>/<db unique name>/flashback/log_2.2286.801367563”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_2.2286.801367563
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_2.2286.801367563’ does not exist

Here we are not able to query v$restore _point to find out the name.

2.2) Search for restore point name in alert log. In this case customer was purging alert log every year starting so could not find name for the Restore Point.

2.3) Dump the controlfile to get the restore point name:SQL> oradebug setmypid
SQL> alter session set events ‘immediate trace name controlf level 9’;
SQL> oradebug tracefile_name

From trace file of controlfile dump, we could see below information:***************************************************************************
RESTORE POINT RECORDS
***************************************************************************
 (size = 212, compat size = 212, section max = 2048, section in-use = 1,
  last-recid= 1, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 240, numrecs = 2048)
RESTORE POINT #1:
 restore point name: <restore point test> guarantee flag: 1 incarnation: 2next record 0                            <<<< Name of restore point
 restore point scn: 0x0000.fbff3d87 12/07/2012 02:16:32

Now we have name of Guaranteed Restore Point:

SQL> Drop restore point <restore point test>;

SQL> alter database open;

Database AlteredNote:  This error may also occur if the FRA is full and thus a flashback log cannot be created.  Check V$flash_recovery_area_usage and/or V$recovery_file_dest regarding space availability in FRA