Restore from a loss of all current control files to a non default location using a backup piece

  • Anuradha Mudgal

This post is about the steps which use restore from a loss of all current control files to a non-default location using a backup piece.

First of all just create a location outside the flash recovery area where to save the backup piece containing the current control file:

 

 

[oracle@localhost oracle]$ pwd
/home/oracle/app/oracle/
[oracle@localhost oracle]$ mkdir controlfile_copy
[oracle@localhost oracle]$ cd controlfile_copy/
[oracle@localhost controlfile_copy]$ ll
total 0

Now it’s time to create the backup piece with the current control file.

RMAN> backup current controlfile to destination '/home/oracle/app/oracle/controlfile_copy';

Starting backup at 09-11-2012 06:29:08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 09-11-2012 06:29:10
channel ORA_DISK_1: finished piece 1 at 09-11-2012 06:29:11
piece handle=/home/oracle/app/oracle/controlfile_copy/ORCL/backupset/2012_11_09/o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp tag=TAG20121109T062908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-11-2012 06:29:11

In your local directory RMAN was able to create your backup piece as written in the above log and as you can verify moving through those directories:

[oracle@localhost controlfile_copy]$ ll
total 4
drwxrwx--- 3 oracle oracle 4096 Nov 9 06:29 ORCL
[oracle@localhost controlfile_copy]$ cd ORCL/backupset/2012_11_09/
[oracle@localhost 2012_11_09]$ ll
total 9680
-rw-rw---- 1 oracle oracle 9895936 Nov 9 06:29 o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp

Let’s simulate the loss of all current control files.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

Instance is still running, so I have to kill it.

[oracle@localhost ~]$ ps -ef|grep smon
oracle 2384 1 0 03:58 ? 00:00:03 ora_smon_orcl
oracle 6758 3039 0 06:35 pts/1 00:00:00 grep smon
[oracle@localhost ~]$ kill -9 2384
[oracle@localhost ~]$ ps -ef|grep smon
oracle 6762 3039 0 06:35 pts/1 00:00:00 grep smon

Under my non_default_location directory I will restore the control file: currently it’s empty.

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 0

Use RMAN to connect to your target instance.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 9 07:46:58 2012

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

connected to target database (not started)
Start the database in NOMOUNT mode.
RMAN> startup nomount;

Oracle instance started

Total System Global Area 456146944 bytes

Fixed Size 1344840 bytes
Variable Size 364907192 bytes
Database Buffers 83886080 bytes
Redo Buffers 6008832 bytes

Use the following RESTORE CONTROLFILE command to get the control file backup from your backup piece and restore it to your non default location:

RMAN> restore controlfile to '/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' from '/home/oracle/app/oracle/controlfile_copy/ORCL/backupset/2012_11_09/o1_mf_ncnnf_TAG20121109T062908_89t4sp41_.bkp';

Starting restore at 09-11-2012 07:51:43
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 09-11-2012 07:51:46

After the restore process finishes you can find the control file under non_default_location directory

[oracle@localhost non_default_location]$ ll
total 9632
-rw-rw---- 1 oracle oracle 9846784 Nov 9 07:51 control01.ctl

Instance is still not able to go to the next mode because it doesn’t know where restored control file is located.

RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/09/2012 07:54:04
ORA-00205: error in identifying control file, check alert log for more info

Use sqlplus to connect the instance …

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 9 07:54:50 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

… have a look at the control_files initialization parameter written in spfile …

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/oracle/app/oracle/oradata/orcl/control01.ctl,
/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

… and modify it according to your new control file location.

SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' scope=spfile;

System altered.
Close the instance ...
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

.. and start it again in MOUNT mode using RMAN.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 9 07:56:56 2012

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 456146944 bytes

Fixed Size 1344840 bytes
Variable Size 364907192 bytes
Database Buffers 83886080 bytes
Redo Buffers 6008832 bytes

You have to perform a recovery of the database after the restore of the control file, even if any datafile was restored.

RMAN> recover database;

Starting recover at 09-11-2012 07:57:34
Starting implicit crosscheck backup at 09-11-2012 07:57:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 09-11-2012 07:57:36

Starting implicit crosscheck copy at 09-11-2012 07:57:36
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-11-2012 07:57:36

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/backupset/2012_11_07/o1_mf_ncnnf_TAG20121107T223343_89pnl8z3_.bkp.old
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_21/o1_mf_s_789209074_80omm3d0_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795852591_86xq10nr_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_10_05/o1_mf_s_795834324_86x564xb_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_09_26/o1_mf_s_795045371_867q3d12_.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
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo03.log
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:13
Finished recover at 09-11-2012 07:57:52

After the restore of the control file you have to open the database with RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

If you want to come back to the original settings because the original location is available again, you have to modify again the control_files initialization parameter value.

SQL> show parameter control_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl

Set the control_files parameter to the original configuration:

SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.
Close your instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Copy your current control file to the original locations:

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 9632
-rw-rw---- 1 oracle oracle 9846784 Nov 9 08:01 control01.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

Connect to the instance…

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 9 08:07:28 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

… and start it

SQL> startup
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 369101496 bytes
Database Buffers 79691776 bytes
Redo Buffers 6008832 bytes
Database mounted.
Database opened.

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/oracle/app/oracle/oradata/orcl/control01.ctl,
/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

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

Tagged:

About The Author

"The fragrance always remains in the hand that gives the rose." I am a person who believes in sharing knowledge among all. My Oracle rewards are OCI, OCP, OCA.

Leave a Reply

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