Recovery of the most crucial element in Oracle database plays an important role. with the help of show parameter control_files we can find the default location of controlfile in our database. Being an Oracle DBA we must know that why do need oracle controlfile in any oracle. There are some main points which remind us to keep a backup of controlfile.
- The database name
- Names and locations of associated data files and redo log files
- The timestamp of the database creation
- The current log sequence number
- Checkpoint information
With the help of this article, we are going to learn about the steps which are required to restore from a loss of all current control files to the default location.
The following scenario simulates a loss of all the control files and the restore process using a backup control file with any Recovery Catalog.
In this situation, you are only able to open your database in NOMOUNT mode. Also remember that when you lose all (or one) control files and restore them (or one of them) from a backup control file, you have to perform a recovery of your database and open it with the RESETLOGS option, even if any datafile is restored (like in this scenario).
That’s not always true when you’re dealing with “created” control file (I hope to simulate that scenario one day), as long as you must specify RESETLOGS if the online logs are lost or NORESETLOGS if the online logs are available.
Anyway, a control file restored from a backup has an SCN taken at that “remote” time, different compared with those currently available in the datafiles and redo logs and so they have to be re–synchronized.
Generally speaking, having the instance in NOMOUNT mode means your control files are still not read (if available), so RMAN is not able to know how to find information about an “unidentified” database: DBID indeed is contained into the control file.
If you are using a flash recovery area or a recovery catalog (best practice’s solution) then you don’t have to set the DBID before executing the RESTORE command of your NOMOUNTED instance, saving time and avoiding extra manual steps always prone to error.
Let’s start. My instance is running
[oracle@localhost orcl]$ ps -ef|grep smon oracle 11655 1 0 08:13 ? 00:00:00 ora_smon_orcl oracle 11811 2820 0 08:20 pts/1 00:00:00 grep smon
Suddenly all my control file are lost.
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
When trying to create a tablespace some errors are thrown:
SQL> create tablespace t1 datafile '/home/oracle/app/oracle/oradata/orcl/t101.dbf' size 1M; create tablespace t1 datafile '/home/oracle/app/oracle/oradata/orcl/t101.dbf' size 1M * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3
The instance is crashed
[oracle@localhost orcl]$ ps -ef|grep smon oracle 11655 1 0 08:13 ? 00:00:00 ora_smon_orcl
As you can verify the mentioned (/home/oracle/app/oracle/oradata/orcl/control01.ctl) file doesn’t exist.
[oracle@localhost orcl]$ ll total 2502160 -rw-rw---- 1 oracle oracle 7348224 Jul 21 08:14 APEX_1930613455248703.dbf -rw-rw---- 1 oracle oracle 85991424 Jul 21 08:14 example01.dbf drwxrwxr-x 2 oracle oracle 4096 Jul 21 08:11 non_default_location -rw-rw---- 1 oracle oracle 52429312 Jul 21 08:23 redo01.log -rw-rw---- 1 oracle oracle 52429312 Jul 21 08:14 redo02.log -rw-rw---- 1 oracle oracle 52429312 Jul 21 08:14 redo03.log -rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:23 sysaux01.dbf -rw-rw---- 1 oracle oracle 871374848 Jul 21 08:20 system01.dbf -rw-rw---- 1 oracle oracle 20979712 Jul 21 07:14 temp01.dbf -rw-rw---- 1 oracle oracle 41951232 Jul 21 08:19 undotbs01.dbf -rw-rw---- 1 oracle oracle 235937792 Jul 21 08:14 users01.dbf
Let’s try to restore the missing control files, starting the instance in NOMOUNT mode:
SQL> startup nomount; ORACLE instance started. Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 360712888 bytes Database Buffers 88080384 bytes Redo Buffers 6008832 bytes
Connect using RMAN and issue the RESTORE CONTROLFILE FROM AUTOBACKUP command. DBID is not set, but because I’m using the flash recovery area, RMAN is able to find a backup control file.
[oracle@localhost orcl]$ rman target / RMAN> restore controlfile from autobackup; Starting restore at 21-07-2012 08:36:22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK recovery area destination: /home/oracle/app/oracle/flash_recovery_area database name (or database unique name) used for search: ORCL channel ORA_DISK_1: AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl Finished restore at 21-07-2012 08:36:25
Let’s see if the instance is able to read our restored control files, bringing the database in MOUNT state:
RMAN> alter database mount; database mounted released channel: ORA_DISK_1
What does it happen if I try to simply open the database ? It fails with a clear error.
RMAN> alter database open; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 07/21/2012 08:42:50 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
As said at the beginning of this post when you restore a control file from a backup you have first to recover the database…
RMAN> recover database; Starting recover at 21-07-2012 08:43:26 Starting implicit crosscheck backup at 21-07-2012 08:43:26 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK Crosschecked 7 objects Finished implicit crosscheck backup at 21-07-2012 08:43:28 Starting implicit crosscheck copy at 21-07-2012 08:43:28 using channel ORA_DISK_1 Crosschecked 6 objects Finished implicit crosscheck copy at 21-07-2012 08:43:28 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc thread=1 sequence=2 archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc thread=1 sequence=3 archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=4 media recovery complete, elapsed time: 00:00:01 Finished recover at 21-07-2012 08:43:31
… and then open it with the RESETLOGS option.
RMAN> alter database open resetlogs; database opened
The database is now open and control files are available again.
[oracle@localhost orcl]$ ll total 2511696 -rw-rw---- 1 oracle oracle 7348224 Jul 21 08:44 APEX_1930613455248703.dbf -rw-rw---- 1 oracle oracle 9748480 Jul 21 08:45 control01.ctl -rw-rw---- 1 oracle oracle 85991424 Jul 21 08:44 example01.dbf drwxrwxr-x 2 oracle oracle 4096 Jul 21 08:11 non_default_location -rw-rw---- 1 oracle oracle 52429312 Jul 21 08:45 redo01.log -rw-rw---- 1 oracle oracle 52429312 Jul 21 08:44 redo02.log -rw-rw---- 1 oracle oracle 52429312 Jul 21 08:44 redo03.log -rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:44 sysaux01.dbf -rw-rw---- 1 oracle oracle 871374848 Jul 21 08:44 system01.dbf -rw-rw---- 1 oracle oracle 20979712 Jul 21 07:14 temp01.dbf -rw-rw---- 1 oracle oracle 41951232 Jul 21 08:44 undotbs01.dbf -rw-rw---- 1 oracle oracle 235937792 Jul 21 08:44 users01.dbf
Once the update is done, Follow the same process of upgrading agents.
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
