Making User-Managed Backups&Recovery of the Control File

• Making User-Managed Backups of the Control File

Back up the control file of a database after making a structural modification to a database operating in ARCHIVELOG mode. To back up a database's control file, you must have the ALTER DATABASE system privilege.

手动备份控制文件的方法可以选项在SHUTDOWN后进行冷备或通过以下方法在MOUNT/OPEN下热备

1. Backing Up the Control File to a Binary File

A binary backup is preferable to a trace file backup because it contains additional information such as the archived log history, offline range for read-only and offline tablespaces, and backup sets and copies (if you use RMAN). If COMPATIBLE is 10.2 or higher, binary control file backups include temp file entries.

To back up the control file after a structural change:

1. Make the desired change to the database. For example, you may create a tablespace:

CREATE TABLESPACE tbs_1 DATAFILE 'file_1.f' SIZE 10M;

1. Back up the database's control file, specifying a file name for the output binary file:

ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/backup/cf.bak' REUSE;

Specify REUSE to make the new control file overwrite one that currently exists.

手动恢复方法直接在NOMOUNT下将此文件复制到CONTROL_FILE参数设置值即可

1. Backing Up the Control File to a Trace File

You can back up the control file to a text file that contains a CREATE CONTROLFILE statement. You can edit the trace file to create a script that creates a new control file based on the control file that was current when you created the trace file.

trace file会记录添加临时表空间语句

Temp file entries are included in the output using ALTER TABLESPACE ... ADD TEMPFILE statements.

To back up the control file to a trace file:

1. Mount or open the database. Execute the following SQL statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

1. To find the trace file for your current session:

trace会记录在下面文件中

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

• Making User-Managed Recovery of the Control File

1. Responding to the Loss of a Subset of the Current Control Files

Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one current control file has not been damaged by the media failure.

1.1 Copying a Multiplexed Control File to a Default Location

To replace a damaged control file by copying a multiplexed control file:

1. If the instance is still running, then shut it down:

SQL> SHUTDOWN ABORT

1. Correct the hardware problem that caused the media failure.
2. Use an intact multiplexed copy of the database's current control file to copy over the damaged control files:

% cp /oracle/good_cf.f /oracle/dbs/bad_cf.f

1. Start a new instance and mount and open the database. For example, enter:

SQL> STARTUP

1.2 Copying a Multiplexed Control File to a Nondefault Location

To restore a control file to a nondefault location:

1. If the instance is still running, then shut it down:

SQL> SHUTDOWN ABORT

1. If you cannot correct the hardware problem that caused the media failure, then copy the intact control file to alternative location.

% cp /disk1/oradata/trgt/control01.dbf /new_disk/control01.dbf

1. Edit the parameter file of the database so that the CONTROL_FILES parameter reflects the current locations of all control files and excludes all control files that were not restored.

Assume that the initialization parameter file contains the following setting:

CONTROL_FILES='/disk1/oradata/trgt/control01.dbf','/bad_disk/control02.dbf'

You can edit the CONTROL_FILES initialization parameter as follows:

CONTROL_FILES='/disk1/oradata/trgt/control01.dbf','/new_disk/control02.dbf'

1. Start a new instance and mount and open the database. For example:

SQL> STARTUP

1. Recovering After the Loss of All Current Control Files

You cannot mount and open the database until the control file is accessible again. If you restore a backup control file, then you must open the database with the RESETLOGS option.

As indicated in Table 31-1, the procedure for restoring the control file depends on whether the online redo logs are available.

Table 31-1 Scenarios When Control Files Are Lost

Status of Online Logs	Status of Data Files	Restore Procedure
Available	Current	If the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. You must specify the file name of the online logs containing the changes to open the database. After recovery, open the database with the RESETLOGS option. Note: If you re-create a control file, then it is not necessary to use the OPEN RESETLOGS option after recovery when the online redo logs are accessible.
Unavailable	Current	If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open RESETLOGS.
Available	Backup	Restore a backup control file, perform complete recovery, and then open the database with the RESETLOGS option.
Unavailable	Backup	Restore a backup control file, perform incomplete recovery, and then open RESETLOGS.

2.1 Recovering with a Backup Control File in the Default Location

To restore a backup control file to its default location:

1. If the instance is still running, shut it down:

SQL> SHUTDOWN ABORT

1. Correct the hardware problem that caused the media failure.
2. Restore the backup control file to all locations specified in the CONTROL_FILES parameter in the server parameter file or initialization parameter file:

% cp /backup/control01.dbf /disk1/oradata/trgt/control01.dbf

% cp /backup/control02.dbf /disk2/oradata/trgt/control02.dbf

1. Start a new instance and mount the database. For example, enter:

SQL> STARTUP MOUNT

1. Begin recovery by executing the RECOVER command with the USING BACKUP CONTROLFILE clause. Specify UNTIL CANCEL if you are performing incomplete recovery:

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

1. Apply the prompted archived logs. If you then receive another message saying that the required archived log is missing, then it probably means that a necessary redo record is located in the online redo logs. This situation can occur when unarchived changes were located in the online logs when the instance failed.

For example, assume that you see the following:

ORA-00279: change 55636 generated at 11/08/2002 16:59:47 needed for thread 1

ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_111.arc

ORA-00280: change 55636 for thread 1 is in sequence #111

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

You can specify the name of an online redo log and press Enter (you may have to try this a few times until you find the correct log):

ORACLE_HOME/oradata/redo01.dbf

Log applied.

Media recovery complete.

如果数据文件非current且online logs不可用则可不用应用REDO LOG；如果数据文件为current且online logs不可用则只能重建控制文件

If the online logs are inaccessible, then you can cancel recovery without applying them. If all data files are current, and if redo in the online logs is required for recovery, then you cannot open the database without applying the online logs. If the online logs are inaccessible, then you must re-create the control file, using the procedure described in "Re-Creating a Control File".

1. Open the database with the RESETLOGS option after finishing recovery:

SQL> ALTER DATABASE OPEN RESETLOGS;

2.2 Recovering with a Backup Control File in a Nondefault Location

To restore a control file to a nondefault location:

1. If the instance is still running, shut it down:

SQL> SHUTDOWN ABORT

1. Correct the hardware problem that caused the media failure.
2. Edit all locations specified in the CONTROL_FILES initialization parameter to reflect the new control file locations. Assume that the control file locations listed in the server parameter file are as follows, and both disks are inaccessible:

CONTROL_FILES='/disk1/oradata/trgt/control01.dbf',

              '/disk2/oradata/trgt/control02.dbf'

You can edit the initialization parameter file and specify accessible locations, as shown in the following example:

CONTROL_FILES='/disk3/cf/control01.dbf','/disk4/cf/control02.dbf'

1. Restore the backup control file to all locations specified in the CONTROL_FILES parameter in the server parameter file or initialization parameter file:

% cp /backup/control01.dbf /disk1/oradata/trgt/control01.dbf

% cp /backup/control02.dbf /disk2/oradata/trgt/control02.dbf

1. Start a new instance and mount the database. For example, enter:

SQL> STARTUP MOUNT

1. Begin recovery by executing the RECOVER command with the USING BACKUP CONTROLFILE clause. Specify UNTIL CANCEL if you are performing incomplete recovery:

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

1. Apply the prompted archived logs. If you then receive another message saying that the required archived log is missing, then it probably means that a necessary redo record is located in the online redo logs. This situation can occur when unarchived changes were located in the online logs when the instance failed.

ORA-00279: change 55636 generated at 11/08/2002 16:59:47 needed for thread 1

ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_111.arc

ORA-00280: change 55636 for thread 1 is in sequence #111

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

You can specify the name of an online redo log and press Enter (you may have to try this a few times until you find the correct log):

ORACLE_HOME/oradata/redo01.dbf

Log applied.

Media recovery complete.

如果数据文件非current且online logs不可用则可不用应用REDO LOG；如果数据文件为current且online logs不可用则只能重建控制文件

1. Open the database with the RESETLOGS option after finishing recovery:

SQL> ALTER DATABASE OPEN RESETLOGS;

1. Re-Creating a Control File

If all control files have been lost in a permanent media failure, but all online redo log members remain intact完好无损, then you can recover the database after creating a new control file. You are not required to open the database with the RESETLOGS option after the recovery.

Depending on the existence and currency of a control file backup, you have the options listed in Table 31-2 for generating the text of the CREATE CONTROLFILE statement. The changes to the database are recorded in the alert_SID.log, so check this log when you are deciding which option to choose.

Table 31-2 Options for Creating the Control File

If you...	Then...
Executed ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS after you made the last structural change to the database, and if you have saved the SQL command trace output	Use the CREATE CONTROLFILE statement from the trace output as-is.
Performed your most recent execution of ALTER DATABASE BACKUP CONTROLFILE TO TRACE before you made a structural change to the database	Edit the output of ALTER DATABASE BACKUP CONTROLFILE TO TRACE to reflect the change. For example, if you recently added a data file to the database, then add this data file to the DATAFILE clause of the CREATE CONTROLFILE statement.
Backed up the control file with the ALTER DATABASE BACKUP CONTROLFILE TO filename statement (not the TO TRACE option)	Use the control file copy to obtain SQL output. Create a temporary database instance, mount the backup control file, and then run ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, then edit the trace option to reflect the change.
Do not have a control file backup in either TO TRACE format or TO filename format	Execute the CREATE CONTROLFILE statement manually

Note: If your character set is not the default US7ASCII, then you must specify the character set as an argument to the CREATE CONTROLFILE statement. The database character set is written to the alert log at startup. The character set information is also recorded in the BACKUP CONTROLFILE TO TRACE output.

To create a control file and recover the database:

1. Start the database in NOMOUNT mode. For example, enter:

STARTUP NOMOUNT

1. Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option:

CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG

     MAXLOGFILES 32

     MAXLOGMEMBERS 2

     MAXDATAFILES 32

     MAXINSTANCES 16

     MAXLOGHISTORY 1600

LOGFILE

     GROUP 1 (

       '/diska/prod/sales/db/log1t1.dbf',

       '/diskb/prod/sales/db/log1t2.dbf'

     )  SIZE 100K,

     GROUP 2 (

       '/diska/prod/sales/db/log2t1.dbf',

       '/diskb/prod/sales/db/log2t2.dbf'

     )  SIZE 100K

DATAFILE

     '/diska/prod/sales/db/database1.dbf',

     '/diskb/prod/sales/db/filea.dbf';

After creating the control file, the instance mounts the database.

1. Register logfile to re-create incarnation table

CREATE CONTROLFILE会清空之前归档日志及INCARNATION信息，如果想恢复此信息可以在创建控制文件前通过V$DATABASE_INCARNATION与V$ARCHIVED_LOG查看归档文件信息，在创建控制文件后使用以下语句注册到控制文件

另外使用ALTER DATABASE BACKUP CONTROLFILE TO TRACE的文本中包含了注册了归档命令：

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

-- 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 '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2021_07_16/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

1. Recover the database as usual (without specifying the USING BACKUP CONTROLFILE clause):

RECOVER DATABASE

1. Open the database after recovery completes (the RESETLOGS option is not required):

ALTER DATABASE OPEN;

1. Immediately back up the control file:

ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;