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 resynchronized.
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.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.