Wednesday, November 8, 2017

DB Does Not Open Post FlashBack ORA-01139: RESETLOGS option only valid after an incomplete database recovery


During one of the flashback activity on one of RAC DB on Exadata system, we faced a challenge while opening DB post flashback. 
we tried to open DB in resetlogs on single instance and it would not open. 

alter database open resetlogs 

ERROR at line 1: 
ORA-38760: This database instance failed to turn on flashback database 

we try then turning the flashback off then open the database but after turning flash back off following is the status. 

alter database flashback off; 
SYS@DBS01P>select flashback_on from v$database; 

FLASHBACK_ON 
------------------ 
RESTORE POINT ONLY 

SYS@DBS01P>select open_mode from v$database; 

OPEN_MODE 
-------------------- 
MOUNTED 

alter database open resetlogs 

ERROR at line 1: 
ORA-01139: RESETLOGS option only valid after an incomplete database recovery 

ERROR at line 1: 
ORA-00600: internal error code, arguments: [kcvcrv_fb_inc_mismatch], [46], [525459024], [959427550], [45], [4004329076], [958125589], [], [], [], [], [] 


Upon research we found that the issue is locks on datafiles and controlfile. 
after flashback database the locks on header and data files didnt clear and caused the failure while starting the DB. 

Hence we need to  recreate controfile from trace with NO RESETLOGS as resetlogs was already done. 

we tried doing but but we faced an error stating that we need DB Open in Read/Write Mode, which is not happening to us. So another way is to restore controlfile to temp location and use it to recreate controlfile. 

1. create pfile from current spfile. 
create pfile='/tmp/DBS01P_new.ora' from spfile;

2. Open pfile and modify controlfile location. 
control_files='/tmp/DBS01P_new.ctl'

3. Mount the instance with new pfile 
startup nomount pfile='/tmp/DBS01P_new.ora'

4. Restore controlfile from backup and create trace file 

Backup Piece -  Piece Name: /ora/DBS01P/backup01/c-2841507472-20171106-01
restore controlfile to '/tmp/DBS01P_new.ctl' from '/ora/DBS01P/backup01/c-2841507472-20171106-01'

5. Create trace from current controlfile.
alter database backup controlfile to trace

6. Open the trace file and just copy the NORESETLOGS Command as follows to new file. 

CREATE CONTROLFILE REUSE DATABASE "DBS01P" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 4192
LOGFILE
  GROUP 1 '+DATAC1/DBS01P/onlinelog/group_1.296.907412313'  SIZE 4096M BLOCKSIZE 512,
  GROUP 2 '+DATAC1/DBS01P/onlinelog/group_2.295.907412287'  SIZE 4096M BLOCKSIZE 512,
  GROUP 3 '+DATAC1/DBS01P/onlinelog/group_3.277.907412331'  SIZE 4096M BLOCKSIZE 512,
  GROUP 4 '+DATAC1/DBS01P/onlinelog/group_4.313.907412375'  SIZE 4096M BLOCKSIZE 512,
  GROUP 5 '+DATAC1/DBS01P/onlinelog/group_5.287.907412391'  SIZE 4096M BLOCKSIZE 512,
  GROUP 6 '+DATAC1/DBS01P/onlinelog/group_6.278.907412419'  SIZE 4096M BLOCKSIZE 512,
  GROUP 7 '+DATAC1/DBS01P/onlinelog/group_7.289.907412449'  SIZE 4096M BLOCKSIZE 512,
  GROUP 8 '+DATAC1/DBS01P/onlinelog/group_8.288.907412459'  SIZE 4096M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATAC1/DBS01P/datafile/system.1405.904054341',
  '+DATAC1/DBS01P/datafile/sysaux.1403.904054339',
  .............
  '+DATAC1/DBS01P/datafile/dpa_tbs.9286.950976195'
CHARACTER SET AL32UTF8
;

 29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58 
CREATE CONTROLFILE REUSE DATABASE "DBS01P" NORESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

## This activity needs the DB to start in non cluster mode. 

SYS@DBS01P1>alter system set cluster_database=false scope=spfile sid='*';
System altered.

SYS@DBS01P1>shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SYS@DBS01P1>startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.0262E+11 bytes

Fixed Size                  2265224 bytes
Variable Size            1.7985E+10 bytes
Database Buffers         8.4289E+10 bytes
Redo Buffers              342851584 bytes

SYS@DBS01P1>sho parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

SYS@DBS01P1> @create_ctl
;
Control file created.

SYS@DBS01P1>SYS@DBS01P1>recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SYS@DBS01P1>alter database open ;
Database altered.

SYS@DBS01P1>select  open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

Now one can put the DB in cluster mode and start the other instances. 
Hope this will help you to overcome any controlfile corruption issues.