Tuesday 8 December 2020

ORA-01194 after Control file Restore in Oracle and willing to do complete recovery

I got this complaint from one of the customer that they were not able to recover the database post controlfile restore, so lets have a look what happened. 

My oracle version is this : 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0


Firstly the controlfile is lost , so when i restart my database this happens: 

So lets restart our database with startup force (shutdown abort & startup) 


SQL> startup force

ORACLE instance started.


Total System Global Area 3321886736 bytes

Fixed Size                  9140240 bytes

Variable Size             704643072 bytes

Database Buffers         2600468480 bytes

Redo Buffers                7634944 bytes

ORA-00205: error in identifying control file, check alert log for more info


* So i have to restore controlfile now


[oracle@rhel7 PROD]$ rman target/


Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 9 00:09:27 2020

Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


connected to target database: PROD (not mounted)


RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD/autobackup/2020_12_08/o1_mf_s_1058658290_hwzjnw2h_.bkp';


Starting restore at 09-DEC-20

using channel ORA_DISK_1


channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/PROD/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/PROD/control02.ctl

Finished restore at 09-DEC-20



SQL> alter database mount;


Database altered.


SQL> alter database  open ;

alter database  open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



SQL> alter database  open resetlogs;

alter database  open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/system01.dbf'


* Thats it , this is the error : 


SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done



SQL> recover database using backup controlfile;

ORA-00279: change 2290523 generated at 12/08/2020 23:41:34 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_9_%u_.arc

ORA-00280: change 2290523 for thread 1 is in sequence #9



Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_9_%u_.arc

'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7



ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_9_%u_.arc

'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7


* So the problem here is we are not able to recover the database because we dont have enough redo changes to be applied to do complete recovery.

When we do recovery from RMAN , its intelligent enough to understand it and applies the redo logfile redo03.log  that needs to applied.

RMAN> recover database;


Starting recover at 09-DEC-20

released channel: ORA_DISK_1

Starting implicit crosscheck backup at 09-DEC-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 09-DEC-20


Starting implicit crosscheck copy at 09-DEC-20

using channel ORA_DISK_1

Finished implicit crosscheck copy at 09-DEC-20


searching for all files in the recovery area

cataloging files...

cataloging done


List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/PROD/autobackup/2020_12_08/o1_mf_s_1058658290_hwzjnw2h_.bkp


using channel ORA_DISK_1


starting media recovery


archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/oradata/PROD/redo03.log

archived log file name=/u01/app/oracle/oradata/PROD/redo03.log thread=1 sequence=9

media recovery complete, elapsed time: 00:00:00

Finished recover at 09-DEC-20

** Remember ** Although RMAN works for us, but in case you wanted to use sqlplus itself , so you should have done this assuming redo01.log was required if it was sequence 1 which was missing :

SQL>  recover database using backup controlfile;

ORA-00279: change 2294948 generated at 12/09/2020 00:18:15 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/PROD/archivelog/2020_12_09/o1_mf_1_1_%u_.arc

ORA-00280: change 2294948 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

'/u01/app/oracle/oradata/PROD/redo01.log'

Log applied.

Media recovery complete.


** thats it, we are able to open the database now. Happy days

SQL> alter database open resetlogs;

Database altered.