Question: How to find the name of old restore points when the database fails to open with the below error?
ORA-38760: This database instance failed to turn on flashback database.
All flashback logs have been manually deleted but the database will not open because there is an old restore point that needs to be dropped but the precise name has been forgotten.
1. MOUNT the database and issue:
SQL>alter session set events 'immediate trace name controlf level 9';
2. Look for the trace file generated in the trace sub-directory of the ADR (11G) or the udump directory for releases prior to 11G. The first few lines will be similar to this:
DUMP OF CONTROL FILES, Seq # 4780 = 0x12ac V10 STYLE FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=4058109829=0xf1e1d785, Db Name='[DBNAME]' Activation ID=0=0x0 Control Seq=4780=0x12ac, File size=624=0x270 File Number=0, Blksiz=16384, File Type=1 CONTROL
3. Any restore points will be listed in the section: NORMAL RESTORE POINT RECORDS:
NORMAL RESTORE POINT RECORDS *************************************************************************** (size = 212, compat size = 212, section max = 2083, section in-use = 1, last-recid= 1, old-recno = 1, last-recno = 1) (extent = 1, blkno = 198, numrecs = 2083) Earliest record: RECID #1 Recno 1 Record timestamp 01/24/11 15:37:22 RESTORE POINT #1: restore point name:guarantee flag: 0 incarnation: 1next record 0 restore point scn: 0x0000.000e8733 01/24/2011 15:37:22 Latest record: RECID #1 Recno 1 Record timestamp 01/24/11 15:37:22 RESTORE POINT #1: restore point name: guarantee flag: 0 incarnation: 1next record 0 restore point scn: 0x0000.000e8733 01/24/2011 15:37:22
Adding details for Restore Point Flag as from Tracefile output: ... guarantee flag: 0 # '0' means Restore Point Guaranteed: "No" guarantee flag: 1 # '1' means Restore Point Guaranteed: "Yes"