The Problem
The following example shows symptoms seen in a RAC environment. One of the RAC Instances does not come up, it’s looking for a Flash backlog that has been accidentally 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 //udump/ _ora_7244.trc: ORA-38701: Flashback database log 25184 seq 14006 thread 1: "+ / /flashback/log_25184.25633.809806337" ORA-17503: ksfdopn:2 Failed to open file + / /flashback/log_25184.25633.809806337 ORA-15012: ASM file '+ / /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. The current flashback logs were deleted accidentally from OS level.
When Current flashback log is deleted then database may also crash with below 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
There are 2 possible solutions when a flashback log file was deleted manually:
Solution 1: Turn off Flashback
Turn off the flashback using:
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;
Solution 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 '+/ /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 ONLY
FLASHBACK_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:
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. 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.
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 Altered