This post about to restore from a loss of all current control files to a non-default location using autobackup. One of the many features included in RMAN is the ability to automatically backup your control file. It is very important for (some) recovery purposes to ensure you have a recent copy of your control file, especially if it contains your recovery catalog. There is one feature of control file autobackups which a lot of DBA’s seem to be unfamiliar with, which is why I thought i’d write up a brief article on it. If you have this feature enabled and you make a structural change to the database, the control file is automatically backed up. ie, If you add a datafile, rename a file, etc. Information which could affect your ability to recover. This backup will be placed on disk even if your regular backup goes to tape.
What happens when we configure autobackup on -If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up
Like the previous scenario the following one simulates again a database losing all the control files, but they will be restored using the autobackup to a non-default location.
As already stated in the mentioned previous post when losing all current control files 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).
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 accessed (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.”
The instance is not running.
[oracle@localhost ORCL]$ ps -ef|grep smon oracle 12901 2820 0 07:24 pts/1 00:00:00 grep smon
Let’s simulate the loss of all current control files.
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
In my future non default location there still isn’t any file.
[oracle@localhost non_default_location]$ pwd /home/oracle/app/oracle/oradata/orcl/non_default_location [oracle@localhost non_default_location]$ ll total 0
Connect through RMAN and…
[oracle@localhost orcl]$ rman target / Recovery Manager: Release 18.104.22.168.0 - Production on Mon Jul 23 07:27:53 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started)
… start the instance 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
Execute the following command to restore the autobackup control file copy to a different location compared to the originals.
RMAN> restore controlfile to '/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' from autobackup; Starting restore at 23-07-2012 07:30:26 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 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_789209074_80omm3d0_.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_789209074_80omm3d0_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete Finished restore at 23-07-2012 07:30:29
After the execution of restore command you can find a control file under the specified location
[oracle@localhost non_default_location]$ ll total 9536 -rw-rw---- 1 oracle oracle 9748480 Jul 23 07:30 control01.ctl
Is it possible to mount the database ? No, of course.
RMAN> alter database mount; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 07/23/2012 07:34:00 ORA-00205: error in identifying control file, check alert log for more info
You have to modify at least the control_files parameter and set the location of the new available control file.
[oracle@localhost non_default_location]$ sqlplus / as sysdba SQL*Plus: Release 22.214.171.124.0 Production on Mon Jul 23 07:35:29 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' scope=spfile; System altered. Shutdown the instance. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down.
Connect the instance with RMAN and start it in mount mode
[oracle@localhost orcl]$ rman target / 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
Issue the recover command for the whole database…
RMAN> recover database; Starting recover at 23-07-2012 07:38:49 Starting implicit crosscheck backup at 23-07-2012 07:38:49 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK Crosschecked 7 objects Finished implicit crosscheck backup at 23-07-2012 07:38:51 Starting implicit crosscheck copy at 23-07-2012 07:38:51 using channel ORA_DISK_1 Crosschecked 6 objects Finished implicit crosscheck copy at 23-07-2012 07:38:52 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/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_07_17/o1_mf_s_788864449_80c39jlo_.bkp using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1 media recovery complete, elapsed time: 00:00:01 Finished recover at 23-07-2012 07:38:54
…and, as already stated, open it with the RESETLOGS option.
RMAN> alter database open resetlogs; database opened
Now if your original location becomes available again, you may want to configure the control_files parameter to the original value.
SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl 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 the instance…
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
…and copy the only available 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 9536 -rw-rw---- 1 oracle oracle 9748480 Jul 23 07:30 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 and start it once again.
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 188.8.131.52.0 Production on Mon Jul 23 07:40:13 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. 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.
The instance is available, the database is in OPEN mode and ready to be used with control_files parameter modified.
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
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