Davis Apps DBA: RMAN: CONTROL FILE RECOVERY SCENARIOS

Every Oracle Database has a control file as one of its important file. It is a small binary file that records the physical structure of the database.

The control file includes:

– Database name
– Names and locations of associated datafiles and redo log files
– The timestamp of the database creation
– The current log sequence number
– Checkpoint information

Without the control file, the database cannot be mounted and recovery is difficult. The control file must be available for writing by the Oracle Database server whenever the database is open.

By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation.

You can also create control files later, if you lose control files or want to change particular settings in the control files. Remember that anytime you restore a control file from a backup, you are required to perform media recovery on your entire database and then open it with the open resetlogs command. This is true even if you don’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).

Below are some of the recovery scenarios related to control files.

CASE 1: LOST ALL CONTROL FILES AND USING FLASH RECOVER AREA (FRA) BUT NOT RECOVERY CATALOG

— CASE 1 – SCENARIO 1: AUTOBACKUP OF CONTROL FILE IS ENABLED
— CASE 1 – SCENARIO 2: AUTOBACKUP OF CONTROL FILE IS NOT ENABLED

CASE 2: LOST ALL CONTROL FILES AND USING RECOVERY CATALOG BUT NOT FLASH RECOVER AREA (FRA)

CASE 3: LOST ALL CONTROL FILES AND NOT USING EITHER RECOVERY CATALOG OR FLASH RECOVER AREA (FRA)

CASE 4: LOST ONLY ONE CONTROL FILE

CASE 5: RECREATING THE CONTROL FILES (FOR DATABASE CLONING)

Let us look at all the above cases with some example:

Database Version used: 11gR2 (11.2.0.4)

OS used: RHEL 6

Target Database Name: ORCL

RMAN Database Name: catdb

CASE 1: LOST ALL CONTROL FILES AND USING FLASH RECOVER AREA (FRA) BUT NOT RECOVERY CATALOG

SCENARIO 1: AUTOBACKUP OF CONTROL FILE IS ENABLED

It is highly recommended to enable autobackup of the control file

We had two control files, one at FRA location and another in Non-FRA location. We lost both of the control files

Control file 1: /u01/oracle/DB11G/oradata/orcl/control01.ctl,
COntrol file 2: /u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl

Now you cannot start the database since the oracle process is unable to locate the control files.

SYS@orcl > startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
ORA-00205: error in identifying control file, check alert log for more info

Let us start the restore process of control file using RMAN

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

NOTE: Now since you enabled autobackups of your control file and also used a flash recovery area, you don’t have to explicitly provide RMAN with the name and location of backup files or your target database identifier (DBID). This is the simplest method that RMAN provides for restoring a control file.

RMAN uses the value of your operating system ORACLE_SID variable to look in the default location for control file backups in the flash recovery area

RMAN> restore controlfile from autobackup;

Starting restore at 19-FEB-2016 12:50:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

..
…
Finished restore at 19-FEB-2016 12:50:04

NOTE: As we have restored a control file from a backup, we are required to perform media recovery on entire database and then open it with the open resetlogs command. This is true although we didn’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).

What will happen if you try to open database at this stage without doing recovery:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/19/2016 13:11:39
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oracle/DB11G/oradata/orcl/system01.dbf’

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

RMAN> alter database open resetlogs;

database opened

CASE 1: LOST ALL CONTROL FILES AND USING FLASH RECOVER AREA (FRA) BUT NOT RECOVERY CATALOG

SCENARIO 2: AUTOBACKUP OF CONTROL FILE IS NOT ENABLED

Remember that control files autobackup OFF doesn’t mean you don’t have control files backup. RMAN will by default back up your control file anytime you back up datafile 1 (which is SYSTEM data file), regardless of whether you have the autobackup of your control file feature enabled.

When you don’t have the autobackup of your control file enabled, then by default RMAN will place the backup of your control file in FRA directory path.
When you restore your control file and when the autobackup feature has not been enabled, then RMAN is unable to determine by itself the default location. You must directly tell RMAN from which backup piece to restore the control file.

Now important thing is to find which backup piece contains the backup of your control file. If we have the RMAN output log from a backup, then we will be easily able to find it.

For example, here is the partial output of RMAN messages during database backup:
including current control file in backup set

including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 19-FEB-2016 13:02:58
channel ORA_DISK_1: finished piece 1 at 19-FEB-2016 13:03:01
piece handle=/u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T130101_9jb702jq_.bkp tag=TAG20160219T130101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-FEB-2016 13:03:01

…
So the backup piece which contains control file is o1_mf_ncsnf_TAG20160219T130101_9jb702jq_.bkp.

Let us start the restore process now.

$ rman

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

RMAN> restore controlfile from ‘/u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T130101_9jb702jq_.bkp';

Starting restore at 19-FEB-2016 13:10:28
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 13:10:31

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 19-FEB-2016 13:12:36
….
..
using channel ORA_DISK_1

starting media recovery

..
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-2016 13:12:42

RMAN> alter database open resetlogs;

database opened

CASE 2: LOST ALL CONTROL FILES AND USING RECOVERY CATALOG BUT NOT FLASH RECOVER AREA (FRA)

When you are using a recovery catalog it is simpler to restore the control files. All you need to do is ensure that you connect to both your target database and the recovery catalog. Then issue startup nomount, and issue the restore controlfile command.

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 19 13:26:11 2016

RMAN> connect target /

connected to target database (not started)

RMAN> connect catalog rman/rman@catdb

connected to recovery catalog database

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

RMAN> restore controlfile;

Starting restore at 19-FEB-2016 13:26:46
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /backups/orcl/controlfile_orcl_c-1279650270-20160219-02
channel ORA_DISK_1: piece handle=/backups/orcl/controlfile_orcl_c-1279650270-20160219-02 tag=TAG20160219T131851
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 13:26:50

Say If AUTOBACKUP OF CONTROL FILE WAS NOT ENABLED, then also RMAN ‘restore controlfile;’ comannd would have located the right backup piece to get control file.

As shown below:

RMAN> restore controlfile;

Starting restore at 19-FEB-2016 13:59:42
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T133923_9jb97f0z_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T133923_9jb97f0z_.bkp tag=TAG20160219T133923
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 13:59:45

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 19-FEB-2016 13:28:17

….
..
using channel ORA_DISK_1

starting media recovery

..
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-2016 13:28:25

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

CASE 3: LOST ALL CONTROL FILES AND NOT USING EITHER RECOVERY CATALOG OR FLASH RECOVER AREA (FRA)

Let us assume our backup strategy doesn’t take advantage of either a flash recovery area or a recovery catalog.

If you’re using a flash recovery area with the autobackup of your control file enabled, then you shouldn’t need the DBID when restoring the control file. If you’re neither using a flash recovery area nor using a recovery catalog, then you might have to know your DBID before you restore the control file.

RMAN uses the DBID to uniquely identify databases. The DBID helps RMAN identify the correct RMAN backup piece from which to restore the control file.

If you are trying to restore a control file without setting DBID, then it will give you the error related to DBID as follows:

RMAN> restore controlfile from autobackup;

Starting restore at 19-FEB-2016 14:20:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

AUTOBACKUP search with format “%F” not attempted because DBID was not set

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/19/2016 14:20:24
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

[/stextbox]

So you need to first find the DBID for your target database, so that RMAN should be aware of the database for which restore is required.

Two of the easy methods are described below.

METHOD 1: Getting the DBID from an Autobackup File

If you check any of the RMAN backup log or check the RMAN generated autobackup files, you can easily find DBID

For example below is RMAN log snippet:

Starting Control File and SPFILE Autobackup at 19-FEB-2016 14:17:52
piece handle=/backups/orcl/controlfile_orcl_c-1279650270-20160219-05 comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-2016 14:17:55

The auto generated control file is : /backups/orcl/controlfile_orcl_c-1279650270-20160219-05

As you can see in the name

c >> refers to control file backup
1279650270 >> DBID
20160219 >> refers to DATE when backup was created

METHOD 2: Dumping Files

If any of the datafiles, online redo log files, or archived redo log files are physically available, you can use the SQL alter system dump statement to write the DBID to a trace file.

Your database does not have to be mounted for this to work. For example, here is the syntax for taking a datafile dump:

SQL> connect / as sysdba

SQL> startup nomount;

SQL> alter system dump datafile ‘/u01/oracle/DB11G/oradata/orcl/system01.dbf’ block min 1 block max 10;

Use this syntax to take a dump of an archived redo log file or online redo log file:

SQL> alter system dump logfile ‘<log file name>';

The trace file with the DBID will be in your user dump destination. If you search for the string “Db ID,” you should find something similar to this output:

Db ID=1279650270=0x4c45e9de, Db Name=’ORCL’

Now since you got the DBID, we can start the control file restore

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 19 14:41:00 2016

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

RMAN> set dbid 1279650270;

executing command: SET DBID

RMAN> set controlfile autobackup format for device type disk to ‘/backups/orcl/controlfile_orcl_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 19-FEB-2016 14:41:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160219
channel ORA_DISK_1: AUTOBACKUP found: /backups/orcl/controlfile_orcl_c-1279650270-20160219-06
channel ORA_DISK_1: restoring control file from AUTOBACKUP /backups/orcl/controlfile_orcl_c-1279650270-20160219-06
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 14:41:44

In above case, we used below RMAN command because in our database the controlfile autobackup is manualy configured to store in non-default location

RMAN> set controlfile autobackup format for device type disk to ‘/backups/orcl/controlfile_orcl_%F';

if you have not done any such RMAN manual configuration then for your database the control file autobackup will go to default location: $ORACLE_HOME/dbs and you don’t need to use above command then.

Also, In the above case it was assumed that the autobackup of control file was enabled

If autobackup of control file was not enabled then we will have to use below command instead to provide complete path of control file backup.

RMAN> restore controlfile from ‘/backups/orcl/controlfile_orcl_c-1279650270-20160219-05′;

Also in this scenario, you will not need be required to set the DBID because you’re pointing the RMAN at a specific backup file which contians DBID details.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 19-FEB-2016 14:28:17

….
..
using channel ORA_DISK_1

starting media recovery

..
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-2016 14:28:25

RMAN> alter database open resetlogs;

database opened

CASE 4: LOST ONLY ONE CONTROL FILE AND YOU HAD MULTIPLEXED CONTROL FILE

Before Oracle can start up normally, it must be able to locate and open each of the control files identified by the control_files initialization parameter. The control files are identical copies of each other. If a multiplexed control file becomes damaged, you can either modify the control_files initialization parameter to match the locations of the remaining good control files or copy a good control file to the location of the damaged or missing control file. If you have one good copy of a control file, you can use that to replace the damaged control files. This minimizes the need to restore your control file from a backup.

Say our database was down and when we tried to start it we got the below error:

SYS@orcl > startup
ORACLE instance started.

and below will be the error that you will see in the alert log at this stage>

Wed Feb 19 12:07:58 2016

ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oracle/DB11G/oradata/orcl/control01.ctl’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

It means oracle was unable to access one of the control file.

Say our database was running and somehow we lost one of the control file. The database will start throwing errors like below:

SYS@orcl > select name from v$database

*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oracle/DB11G/oradata/orcl/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Now shutdown immediate won’t work and you will have to resort to shutdown abort to shutdown the database instance:

SYS@orcl > shutdown immediate;

ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oracle/DB11G/oradata/orcl/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SYS@orcl > shutdown abort;

ORACLE instance shut down.

Now either you copy the good control file that we have available to the missing control file location or modify the control_files parameter in the initialization file or spfile to remove the lost control file information from spfile/pfile.

Method 1: Copy the good control file that we have available to the missing control file location

$ cp /u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl /u01/oracle/DB11G/oradata/orcl/control01.ctl

STARTUP the database normally now

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 19 12:05:17 2016

Connected to an idle instance.

SYS@orcl > startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.

Method 2: modify the control_files parameter in the initialization file or spfile

Just starting database in nomount mode is enough to modify the control_files parameter in the initialization file or spfile

SQL> startup nomount;

SQL> alter system set control_files=’/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl’ scope=spfile;

SYS@orcl > alter system set control_files=’/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl’ scope=spfile;

System altered.

SYS@orcl > shutdown immediate;

ORA-01507: database not mounted
ORACLE instance shut down.

SYS@orcl > startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.

SYS@orcl > show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl

So we now have only one control file in our database, which is not recommended. You should multiplex this control file as soon as possible to save your self form any possible disaster.

CASE 5: RECREATING THE CONTROL FILES (FOR DATABASE CLONING)

Here we are not using RMAN for control file restore. You are cloning your database to another database and you will be using a new database name for it. This means we can not use the same binary control file and we need to recreate it.

You can use the output of the following command from source database to re-create your control file:

SQL> alter database backup controlfile to trace;

The previous command generates a trace file that is placed in your user dump directory

There are two SQL statements in the trace file: one with noresetlogs and another with resetlogs. You’ll have to edit the trace file so that it contains only the appropriate SQL statement that you require.

For our database cloning we will use the SQL statement with RESETLOG option

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
‘/u01/oracle/DB11G/oradata/ORCL/onlinelog/o1_mf_4_9hl96kyw_.log’,
‘/u01/oracle/DB11G/fast_recovery_area/ORCL/onlinelog/o1_mf_4_9hl96l67_.log’
) SIZE 100M BLOCKSIZE 512,
GROUP 5 (
‘/u01/oracle/DB11G/oradata/ORCL/onlinelog/o1_mf_5_9hl934pt_.log’,
‘/u01/oracle/DB11G/fast_recovery_area/ORCL/onlinelog/o1_mf_5_9hl934xp_.log’
) SIZE 100M BLOCKSIZE 512,
GROUP 6 (
‘/u01/oracle/DB11G/oradata/ORCL/onlinelog/o1_mf_6_9hl972k3_.log’,
‘/u01/oracle/DB11G/fast_recovery_area/ORCL/onlinelog/o1_mf_6_9hl972tr_.log’
) SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/u01/oracle/DB11G/oradata/orcl/system01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/sysaux01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/undotbs01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/users01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/example01.dbf’
CHARACTER SET AL32UTF8
;

After editing the control file trace file as per requirement, move it to target server and execute it as sysdba

You need to:

> change ‘REUSE’ to ‘SET’
> change database name ‘ORCL’ to the target database name
> change location of logfiles and datafiles.

SQL> connect / as sysdba

SQL> shutdown immediate;

SQL> @control_file_trace.trc

You should now have control files re-created in every location identified by your control_files initialization parameter in your target database and you can go ahead with the database recovery steps..

Davis Apps DBA

Tuesday, July 12, 2016

RMAN: CONTROL FILE RECOVERY SCENARIOS

No comments:

Post a Comment

IAS Cache initialization failed

Total Pageviews

Report Abuse