4.4. Recovering Control Files
Control files are key database files required in the recovery process. Control files contain RMAN metadata information and the required repository information, if you're not using the Recovery Manager catalog. In Oracle 10g, RMAN has introduced control file autobackup, which allows you to configure RMAN to automatically back up the control file with other backups directly to the flash recovery area. This assures that you will have a control file for recovery purposes.
There are some methods of recovering the control file that you should be aware of. The first is using the control file autobackup to recover the control file. You should also be aware of how to create a new control file. The next sections walk you through each of these methods of recovering the control file.
4.4.1. Recovering a Control File Autobackup
Using the control file autobackup to recover the control file is a fairly straightforward process. You must first configure RMAN settings to perform a control file autobackup, which consists of enabling a configuration parameter. The control file autobackup configures all backups to automatically back up the control file. Once this has been configured, a RMAN backup should be performed. This backup will contain a control file that will be used for recovery operations. Then you can perform a recover control file operation. If you are not using the recovery catalog, you need to specify the database identifier (DBID) after connecting to the target database.
Let's walk through these steps without using the recovery catalog:
First, you must configure RMAN to perform a control file autobackup:
RMAN> connect target connected to target database: ORA101T (DBID=2615281366) RMAN> configure controlfile autobackup on; using target database controlfile instead of recovery catalog new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; #
default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:ORACLEPRODUCT10.1.0
DB_1DATABASES NCFORA101T.ORA'; # default RMAN>
Next, perform a backup with the control file autobackup enabled:
RMAN> run 2> { 3> backup database; 4> backup (archivelog all); 5> } Starting backup at 04-SEP-04 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=39 devtype=DISK channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:ORACLEORADATAORA101TSYSTEM01.DBF input datafile fno=00003 name=C:ORACLEORADATAORA101TSYSAUX01.DBF input datafile fno=00005 name=C:ORACLEORADATAORA101TEXAMPLE01.DBF input datafile fno=00002 name=C:ORACLEORADATAORA101TUNDOTBS01.DBF input datafile fno=00006 name=C:ORACLEORADATAORA101TINDEX01.DBF input datafile fno=00004 name=C:ORACLEORADATAORA101TUSERS01.DBF channel ORA_DISK_1: starting piece 1 at 04-SEP-04 channel ORA_DISK_1: finished piece 1 at 04-SEP-04 piece handle=C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
2004_09_04O1_MF_NNNDF_TAG20040904T124044_0MN6L0H4_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:27 Finished backup at 04-SEP-04 Starting backup at 04-SEP-04 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=47 recid=41 stamp=535950026 input archive log thread=1 sequence=48 recid=42 stamp=535964440 input archive log thread=1 sequence=49 recid=43 stamp=535975236 input archive log thread=1 sequence=50 recid=44 stamp=535979525 input archive log thread=1 sequence=51 recid=45 stamp=535979527 input archive log thread=1 sequence=52 recid=46 stamp=535979533 input archive log thread=1 sequence=53 recid=47 stamp=535979589 input archive log thread=1 sequence=54 recid=48 stamp=535984936 channel ORA_DISK_1: starting piece 1 at 04-SEP-04 channel ORA_DISK_1: finished piece 1 at 04-SEP-04 piece handle=
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
2004_09_04O1_MF_ANNNN_TAG20040904T124216_0MN6O67V_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:43 Finished backup at 04-SEP-04 Starting Control File Autobackup at 04-SEP-04 piece handle=
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TAUTOBACKUP
2004_09_04O1_MF_N_535984987_0MN6PG3P_.BKP comment=NONE Finished Control File Autobackup at 04-SEP-04
Next, you simulate the missing control files by deleting all the control files. (The database will need to be shut down to perform this simulated failure.)
C:oracleoradataora101t> delete *.ctl
Next, start the database in NOMOUNT mode, which is required because there is no control file to mount:
C:Documents and Settingsdstuns> sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Sat Sep 4 12:55:43 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 88080384 bytes
Fixed Size 787588 bytes Variable Size 78642044 bytes Database Buffers 8388608 bytes Redo Buffers 262144 bytes SQL>
Next, connect to RMAN and the target database. You will also need to specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable. The DBID was obtained in step 1 from connecting to the target database before the failure was introduced:
RMAN> connect target / connected to target database (not started) RMAN> set dbid 2615281366; executing command: SET DBID
Next, restore the control file from backup:
RMAN> restore controlfile from autobackup; Starting restore at 04-SEP-04 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=49 devtype=DISK recovery area destination: C:oracleflash_recovery_areaora101t database name (or lock name space) used for search: ORA101T channel ORA_DISK_1: autobackup found in the recovery area channel ORA_DISK_1: autobackup found: C:ORACLEFLASH_RECOVERY_AREAORA101TORA1 01TAUTOBACKUP2004_09_04O1_MF_N_535984987_0MN6PG3P_.BKP channel ORA_DISK_1: controlfile restore from autobackup complete output filename=C:ORACLEORADATAORA101TCONTROL01.CTL output filename=C:ORACLEORADATAORA101TCONTROL02.CTL output filename=C:ORACLEORADATAORA101TCONTROL03.CTL Finished restore at 04-SEP-04 RMAN>
Next, mount the database and begin to recover the database:
RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> recover database; Starting recover at 04-SEP-04 Starting implicit crosscheck backup at 04-SEP-04 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=49 devtype=DISK Crosschecked 16 objects Finished implicit crosscheck backup at 04-SEP-04 Starting implicit crosscheck copy at 04-SEP-04 using channel ORA_DISK_1 Crosschecked 8 objects Finished implicit crosscheck copy at 04-SEP-04 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name:
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TAUTOBACKUP
2004_09_04O1_MF_N_535984987_0MN6PG3P_.BKP using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 55 is already on disk as file C:ORACLEORADATAOR A101TREDO03.LOG archive log filename=C:ORACLEORADATAORA101TREDO03.LOG thread=1 sequence=55 media recovery complete Finished recover at 04-SEP-04
As you can see, recovering the control file requires some extra steps that are not part of your typical database recovery. This is because the control file has the information necessary to mount the database. If this is not available, the database cannot be mounted until the control file has been restored. Also, the control file contains RMAN information about the target database, which must be manually specified using the SET DBID command.
4.4.2. Re-creating a Control File
The procedure for creating a control file is a valuable recovery measure. The control file contains the physical map of an Oracle database. In other words, the control file has all the locations of the physical files, including datafiles, redo logs, and control files. The control file also has information about whether the database is in ARCHIVELOG mode, as well as RMAN metadata information.
The control file create script can be created with the command ALTER DATABASE BACKUP CONTROL-FILE TO TRACE. This command generates an ASCII representation of the binary control file as an Oracle trace file. The ASCII backup control file is in the form of data control language (DCL) statements or commands. The ASCII backup control file can be used to rebuild the binary control file. Either SPFILE or PFILE are required to start the database with appropriate initialization parameters when rebuilding the control file.
Let's walk through the steps for creating a control file:
First, create the ASCII control file:
C:Documents and Settingsdstuns> sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Sat Sep 4 15:09:25 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> alter database backup controlfile to trace; Database altered. SQL>
Next, display this trace file by locating the file in the UDUMP directory for the Oracle SID (ORACLE_SID) that you performed the command on. The backup control file has two scenarios as the contents. The first is Set #1 NORESETLOGS case, and the second is Set #2 RESETLOGS case. The RESETLOGS case is used for incomplete recovery, and NORESETLOGS is used for complete recovery options:
C:oracleadminora101tudump> edit ora101t_ora_3428.trc Dump file c:oracleadminora101tudumpora101t_ora_3428.trc Sat Sep 04 15:09:41 2004 ORACLE V10.1.0.2.0 - Production vsnsta=0 vsnsql=13 vsnxtr=3 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options Windows XP Version V5.1 Service Pack 1 CPU : 1 - type 586 Process Affinity: 0x00000000 Memory (A/P) : PH:47M/510M, PG:745M/1249M, VA:1806M/2047M Instance name: ora101t Redo thread mounted by this instance: 1 Oracle process number: 12 Windows thread id: 3428, image: ORACLE.EXE (SHAD) *** SERVICE NAME:(SYS$USERS) 2004-09-04 15:09:41.900 *** SESSION ID:(52.3) 2004-09-04 15:09:41.900 *** 2004-09-04 15:09:41.900 -- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database initialization file. -- -- LOG_ARCHIVE_DEST='' -- LOG_ARCHIVE_DUPLEX_DEST='' -- -- LOG_ARCHIVE_FORMAT=ARC%S_%R.%T -- -- DB_UNIQUE_NAME="ora101t" -- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=2 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%RDBMS
-- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_1='LOCATION=c:oracleoraarchora101t' -- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_
NAME' -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_1=ENABLE -- -- Below are two sets of SQL statements, each of which creates a new -- control file and uses it to open the database. The first set opens -- the database with the NORESETLOGS option and should be used only if -- the current versions of all online logs are available. The second -- set opens the database with the RESETLOGS option and should be used -- if online logs are unavailable. -- The appropriate set of statements can be copied from the trace into -- a script file, edited as necessary, and executed when there is a -- need to re-create the control file. -- -- Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL statement -- will place the database in the appropriate protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORA101T" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 454
LOGFILE GROUP 1 ( 'C:ORACLEORADATAORA101TREDO01_MIRROR.LOG', 'C:ORACLEORADATAORA101TREDO01.LOG' ) SIZE 10M, GROUP 2 ( 'C:ORACLEORADATAORA101TREDO02.LOG', 'C:ORACLEORADATAORA101TREDO02_MIRROR.LOG' ) SIZE 10M, GROUP 3 ( 'C:ORACLEORADATAORA101TREDO03.LOG', 'C:ORACLEORADATAORA101TREDO03_MIRROR.LOG' ) SIZE 10M -- STANDBY LOGFILE DATAFILE 'C:ORACLEORADATAORA101TSYSTEM01.DBF', 'C:ORACLEORADATAORA101TUNDOTBS01.DBF', 'C:ORACLEORADATAORA101TSYSAUX01.DBF', 'C:ORACLEORADATAORA101TUSERS01.DBF', 'C:ORACLEORADATAORA101TEXAMPLE01.DBF', 'C:ORACLEORADATAORA101TINDEX01.DBF' CHARACTER SET WE8MSWIN1252 ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1'), -- Configure RMAN configuration record 2 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILEAUTOBACKUP','ON'), -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE
'C:ORACLEORAARCHORA101TARC00001_0520387048.001'; -- ALTER DATABASE REGISTER LOGFILE
'C:ORACLEORAARCHORA101TARC00001_0527361115.001'; -- ALTER DATABASE REGISTER LOGFILE
'C:ORACLEORAARCHORA101TARC00001_0535986437.001'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; -- Database can now be opened normally. ALTER DATABASE OPEN; -- No tempfile entries found to add. -- -- Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will be
invalidated. -- Use this only if online logs are damaged. After mounting the created -- controlfile, the following SQL statement will place the database in the -- appropriate protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORA101T" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 454 LOGFILE GROUP 1 ( 'C:ORACLEORADATAORA101TREDO01_MIRROR.LOG', 'C:ORACLEORADATAORA101TREDO01.LOG' ) SIZE 10M, GROUP 2 ( 'C:ORACLEORADATAORA101TREDO02.LOG', 'C:ORACLEORADATAORA101TREDO02_MIRROR.LOG' ) SIZE 10M, GROUP 3 ( 'C:ORACLEORADATAORA101TREDO03.LOG', 'C:ORACLEORADATAORA101TREDO03_MIRROR.LOG'
) SIZE 10M -- STANDBY LOGFILE DATAFILE 'C:ORACLEORADATAORA101TSYSTEM01.DBF', 'C:ORACLEORADATAORA101TUNDOTBS01.DBF', 'C:ORACLEORADATAORA101TSYSAUX01.DBF', 'C:ORACLEORADATAORA101TUSERS01.DBF', 'C:ORACLEORADATAORA101TEXAMPLE01.DBF', 'C:ORACLEORADATAORA101TINDEX01.DBF' CHARACTER SET WE8MSWIN1252 ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICETYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1'), -- Configure RMAN configuration record 2 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE
AUTOBACKUP','ON'), -- Commands to re-create incarnation table below log names MUST be changed to -- existing filenames on disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE
'C:ORACLEORAARCHORA101TARC00001_0520387048.001'; -- ALTER DATABASE REGISTER LOGFILE
'C:ORACLEORAARCHORA101TARC00001_0527361115.001'; -- ALTER DATABASE REGISTER LOGFILE
'C:ORACLEORAARCHORA101TARC00001_0535986437.001'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- No tempfile entries found to add. --
Next, copy out the appropriate case needed to run. In this case, use the NORESETLOGS case, because you are recovering the database up to the point of failure. Let's look at the newly created copy of this trace file, which you will now call BACKUP_CONTROLFILE_NORESET.TXT:
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORA101T" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 454 LOGFILE GROUP 1 ( 'C:ORACLEORADATAORA101TREDO01_MIRROR.LOG', 'C:ORACLEORADATAORA101TREDO01.LOG' ) SIZE 10M, GROUP 2 ( 'C:ORACLEORADATAORA101TREDO02.LOG', 'C:ORACLEORADATAORA101TREDO02_MIRROR.LOG' ) SIZE 10M, GROUP 3 ( 'C:ORACLEORADATAORA101TREDO03.LOG', 'C:ORACLEORADATAORA101TREDO03_MIRROR.LOG' ) SIZE 10M -- STANDBY LOGFILE DATAFILE 'C:ORACLEORADATAORA101TSYSTEM01.DBF', 'C:ORACLEORADATAORA101TUNDOTBS01.DBF', 'C:ORACLEORADATAORA101TSYSAUX01.DBF', 'C:ORACLEORADATAORA101TUSERS01.DBF', 'C:ORACLEORADATAORA101TEXAMPLE01.DBF', 'C:ORACLEORADATAORA101TINDEX01.DBF' CHARACTER SET WE8MSWIN1252 ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1'), -- Configure RMAN configuration record 2 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILEAUTOBACKUP','ON'), -- Commands to re-create incarnation table below log names MUST be changed to -- existing filenames on disk. Any one log file from each branch can be used to -- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'C:ORACLEORAARCHORA101TARC00001_0520387048.001'; -- ALTER DATABASE REGISTER LOGFILE 'C:ORACLEORAARCHORA101TARC00001_0527361115.001'; -- ALTER DATABASE REGISTER LOGFILE 'C:ORACLEORAARCHORA101TARC00001_0535986437.001'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; -- Database can now be opened normally. ALTER DATABASE OPEN;
Next, simulate the loss of all control files by deleting the control files for the database. This is performed with the database shut down:
C:oracleoradataora101t> delete *.ctl
Now, use SQL*Plus and connect as SYSDBA. Then, run the BACKUP_CONTROLFILE_NORESET.TXT script:
C:oracleadminora101tudump> sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Sat Sep 4 15:44:37 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. C:oracleadminora101tudump> sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Sat Sep 4 15:44:37 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> @backup_controlfile_noreset.txt ORACLE instance started. Total System Global Area 88080384 bytes Fixed Size 787588 bytes Variable Size 78642044 bytes
Database Buffers 8388608 bytes Redo Buffers 262144 bytes Control file created. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required ALTER SYSTEM ARCHIVE LOG ALL * ERROR at line 1: ORA-00271: there are no logs that need archiving Database altered. SQL>
Verify that the control files have been rebuilt by going to the datafile directory listing the control files:
C:oracleoradataora101t> dir *.ctl Volume in drive C has no label. Volume Serial Number is 385B-CF22 Directory of C:oracleoradataora101t 09/04/2004 03:48 PM 3,391,488 CONTROL01.CTL 09/04/2004 03:48 PM 3,391,488 CONTROL02.CTL 09/04/2004 03:48 PM 3,391,488 CONTROL03.CTL 3 File(s) 10,174,464 bytes 0 Dir(s) 18,775,740,416 bytes free C:oracleoradataora101t>
As you can see, this process is fairly straightforward. The ASCII control file actually reproduces the binary control files. These can be verified by viewing the physical control files in the appropriate directory.