STARTUP database failed ORA-38760 to turn on flashback Database (Doc ID 1554596.1)

applies To:Oracle database-enterprise edition-version 10.2.0.4 and later
Information in this document applies to any platform.
SymptomsThis is isn't a RAC specific and could happen in single instances as
If flashback logs required are not available, as for example FLB logs is 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 the been accidently.

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/HOME/RDBMON/ORACLE_MAINT/ADMIN/OASLIVE2/UDUMP/OASLIVE2_ORA_7244.TRC:
Ora-38701:flashback database log 25184 seq 14006 thread 1: "+fra/oaslive/flashback/log_25184.25633.809806337"
Ora-17503:ksfdopn:2 Failed to open file +fra/oaslive/flashback/log_25184.25633.809806337
Ora-15012:asm file ' +fra/oaslive/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 ... <& lt;<< Error on opening database

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

The current flashback log deleted is accidently from the OS level. cause

When the flashback log is deleted then database open would 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/HOME/RDBMON/ORACLE_MAINT/ADMIN/OASLIVE2/UDUMP/OASLIVE2_ORA_7244.TRC:
Ora-38701:flashback database log 25184 seq 14006 thread 1: "+fra/oaslive/flashback/log_25184.25633.809806337"
Ora-17503:ksfdopn:2 Failed to open file +fra/oaslive/flashback/log_25184.25633.809806337
Ora-15012:asm file ' +fra/oaslive/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-seq-thread 1: "/backup/rman_backup/p ROD1/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:

(1) Turn off FLASHBACK: sql> ALTER DATABASE flashback off;

The Alert log would have the following entries when your 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 <& lt;<< 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'll still try to write to flashback log even though yo u have disable it and it would fail again with same error:

Completed:alter Database Flashback off <& lt;<< 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/ORACLE_MAINT/ADMIN/OASLIVE2/UDUMP/OASLIVE2_ORA_19796.TRC:
Ora-38701:flashback database log 25184 seq 14006 thread 1: "+fra/oaslive/flashback/log_25184.25633.809806337"
Ora-17503:ksfdopn:2 Failed to open file +fra/oaslive/flashback/log_25184.25633.809806337
Ora-15012:asm file ' +fra/oaslive/flashback/log_25184.25633.809806337 ' does not exist <& lt;<< 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 ... <& lt;<< Again failed with ORA-38760

Check If flashback the 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 only

Flashback_on-possible values are as follows:

Yes-flashback is on
No-flashback is off
Restore point Only-flashback was 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 deletes so this database would not try to write to flashback log on start Up

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

2.1) Check the name from V$restore_point view but this would also fail with same error:sql> select * from V$restore_po int
SELECT * FROM V$restore_point
*
ERROR at line 1:
Ora-38701:flashback database log 2 seq 2 thread 1: "+fra/oaslive/flashback/log_2.2286.801367563"
Ora-17503:ksfdopn:2 Failed to open file +fra/oaslive/flashback/log_2.2286.801367563
Ora-15012:asm file ' +fra/oaslive/flashback/log_2.2286.801367563 ' does not exist

Here we are don't 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 is purging Alert log every year, starting so could.

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 the 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 =, numrecs = 2048)
RESTORE Point #1:
Restore point name:standby_flashback_testing 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 standby_flashback_testing;

sql> ALTER DATABASE open;

Database Altered