Control file restore :
■Note 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).
10-1. Restoring Control File Using Flash Recovery Area.
Problem:
You have wisely enabled a flash recovery area, and you use it as a repository for your control file backups. Unfortunately, you’ve lost all your control files, and now you need to use RMAN to restore them.
Solution:
When you use the flash recovery area, you can use one of two very different methods to restore
the control file depending on whether you enabled the autobackup of the control file.
This recipe describes both of these scenarios.
Using the Autobackup of the Control File
When you enable the autobackup of your control file and are using a flash recovery area, then restoring your control file is fairly simple.
First connect to your target database, then issue a startup nomount command, and lastly issue the restore controlfile from autobackup
command:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN restores the control files to the location defined by your control_files initialization parameter.
You should see a message indicating that your control files have been successfully
copied back from an RMAN backup piece.
Here is a partial snippet of the output:
Starting restore at 02-FEB-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
Finished restore at 02-FEB-07
You can now alter your database into mount mode and perform any additional restore
and recovery commands required for your database.
Not Using the Autobackup of the Control File
If you don’t use the autobackup of the control file feature, then restoring the control file becomes more difficult.
If autobackup is disabled, you have to explicitly tell RMAN from
which directory and backup piece to restore the control file. This example specifies a directory and a lengthy filename:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from
'C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.bck';
Here is a partial listing of the RMAN output when restoring your control file:
Starting restore at 02-FEB-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: sid=156 devtype=DISK
How It Works
We highly recommend enabling autobackup of the control file.
By default, the autobackup of your control file is not enabled .
This ensures that the backup piece is placed in a default location that RMAN can use to automatically restore your target database control file.
Autobackup Enabled
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 deduces the default location of the backup file by combining the values of the db_recovery_file_dest initializa-
tion parameter and your operating system ORACLE_SID variable setting.
By default RMAN will look in a directory with the following format:
\<FRA>\<target database SID>\autobackup\YYYY_MM_DD\<backup piece file>
Autobackup Not 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 a directory path named like this:
\<FRA>\<target database SID>\backupset\YYYY_MM_DD\<backup piece file>
■Note RMAN will by default back up your control file anytime you back up datafile 1, regardless of
whether you have the autobackup of your control file feature enabled.
If you have the RMAN output log from a backup, then you should be able to see which
backup piece contains the backup of your control file.
For example, here is the partial output
of RMAN messages during a backup of datafile 1:
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-FEB-07
channel ORA_DISK_1: finished piece 1 at 02-FEB-07
piece handle=
C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.BCK
In this example, the correct backup piece name is as follows:
01_mf_ncnnf_TAG20060923T02kc1vgsh.BCK
■Note : When you restore a control file from a backup, you are required to perform media recovery on your entire database and open your database with the open resetlogs command, even if you didn’t restore any datafiles.
You can determine whether your control file is a backup by querying the CONTROLFILE_TYPE col umn of the V$DATABASE view.
10-2. Restoring Control File Using Recovery Catalog
Problem
You need to restore your control file, and you use a recovery catalog when creating backups.
Solution
Restoring the control file is fairly simple when you use a recovery catalog. All you need to do is ensure that you connect to both your target database and the recovery catalog.
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> restore controlfile;
How It Works
Using a recovery catalog makes it straightforward to restore the control file.
When you issue the restore controlfile command, RMAN will retrieve from the recovery catalog the location and name of the file that contains the control file backup and restores the control file appro-
priately. Because the recovery catalog knows the location of the RMAN backup piece,
it doesn’t matter whether the backup piece is in a flash recovery area or in a configured channel location.
When you’re connected to the recovery catalog, you can view backup information about your control files even while your target database is in nomount mode.
To list backups of your
control files, use the list command as shown here:
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> list backup of controlfile;
If you have registered two databases in the recovery catalog with the same name, then
you might receive an error such as this when you attempt to list backups or restore the control
file:
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20005: target database name is ambiguous
In this situation, you will need to first set your database identifier (DBID) before you can restore your control file.
how to determine your DBID.
The database name that is stored in the recovery catalog is not guaranteed to be unique.
You can verify that you have multiple databases with the same name in your recovery catalog by querying the recovery catalog RC_DATABASE view as shown here:
SQL> connect rcat/rcat@rcat
SQL> select db_key, dbid, name from rc_database;
DB_KEY DBID NAME
------ ---------- ----
1 1124743449 ORCL
4241 1140772490 ORCL
10-3. Determining the Database Identifier
Problem
Your backup strategy doesn’t take advantage of either a flash recovery area or a recovery catalog.
You are trying to restore a control file as follows, and you receive an error message stating that you must explicitly set the database identifier (DBID):
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN specifically instructs you to set the DBID first:
RMAN-06495: must explicitly specify DBID with SET DBID command
You don’t know the DBID for your database, and you aren’t sure how to find the DBID.
Without a control file for your database, you can’t mount the database and query the DBID
value from the V$DATABASE view.
Solution
You can determine the DBID of your database in one of the following ways:
• You can derive the DBID from an autobackup file.
• You can retrieve the DBID from RMAN output.
• You can write the DBID to the alert.log file.
• You can derive DBID from a file dump.
Deriving the DBID from an Autobackup File
If you chose to configure the autobackup control file format, then you are required to include the format variable %F when formatting the name used for the RMAN backup piece.
The format of the %F variable is a unique combination of the database identifier, the date, and a sequence, and it follows this format: c-IIIIIIIIII-YYYYMMDD-QQ. The first ten Is comprise your target data-
base’s DBID.
For example, if the control file backup piece name is c-2601506593-20060918-01,
then the DBID substring is 2601506593.
Description of %F Format Variable
String
g
c Signifies a control file backup.
IIIIIIIIII DBID.
YYYYMMDD Date backup was created. Used by maxdays parameter of the restore controlfile
command.
QQ A hex sequence number that is incremented each time a control file autobackup is
created for a given day. Used by the maxseq parameter of the restore controlfile
command.
The default location for a control file autobackup on Unix systems is ORACLE_HOME/dbs,
and on Windows platforms it’s usually ORACLE_HOME\database.
Writing the DBID to the Alert.log File
Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code
execute as part of your backup job:
COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));
After running the previous code, you should see a text message in your target database
alert.log file that looks like this:
DBID: 2601506593
■Note 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).
10-1. Restoring Control File Using Flash Recovery Area.
Problem:
You have wisely enabled a flash recovery area, and you use it as a repository for your control file backups. Unfortunately, you’ve lost all your control files, and now you need to use RMAN to restore them.
Solution:
When you use the flash recovery area, you can use one of two very different methods to restore
the control file depending on whether you enabled the autobackup of the control file.
This recipe describes both of these scenarios.
Using the Autobackup of the Control File
When you enable the autobackup of your control file and are using a flash recovery area, then restoring your control file is fairly simple.
First connect to your target database, then issue a startup nomount command, and lastly issue the restore controlfile from autobackup
command:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN restores the control files to the location defined by your control_files initialization parameter.
You should see a message indicating that your control files have been successfully
copied back from an RMAN backup piece.
Here is a partial snippet of the output:
Starting restore at 02-FEB-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
Finished restore at 02-FEB-07
You can now alter your database into mount mode and perform any additional restore
and recovery commands required for your database.
Not Using the Autobackup of the Control File
If you don’t use the autobackup of the control file feature, then restoring the control file becomes more difficult.
If autobackup is disabled, you have to explicitly tell RMAN from
which directory and backup piece to restore the control file. This example specifies a directory and a lengthy filename:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from
'C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.bck';
Here is a partial listing of the RMAN output when restoring your control file:
Starting restore at 02-FEB-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: sid=156 devtype=DISK
How It Works
We highly recommend enabling autobackup of the control file.
By default, the autobackup of your control file is not enabled .
This ensures that the backup piece is placed in a default location that RMAN can use to automatically restore your target database control file.
Autobackup Enabled
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 deduces the default location of the backup file by combining the values of the db_recovery_file_dest initializa-
tion parameter and your operating system ORACLE_SID variable setting.
By default RMAN will look in a directory with the following format:
\<FRA>\<target database SID>\autobackup\YYYY_MM_DD\<backup piece file>
Autobackup Not 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 a directory path named like this:
\<FRA>\<target database SID>\backupset\YYYY_MM_DD\<backup piece file>
■Note RMAN will by default back up your control file anytime you back up datafile 1, regardless of
whether you have the autobackup of your control file feature enabled.
If you have the RMAN output log from a backup, then you should be able to see which
backup piece contains the backup of your control file.
For example, here is the partial output
of RMAN messages during a backup of datafile 1:
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-FEB-07
channel ORA_DISK_1: finished piece 1 at 02-FEB-07
piece handle=
C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.BCK
In this example, the correct backup piece name is as follows:
01_mf_ncnnf_TAG20060923T02kc1vgsh.BCK
■Note : When you restore a control file from a backup, you are required to perform media recovery on your entire database and open your database with the open resetlogs command, even if you didn’t restore any datafiles.
You can determine whether your control file is a backup by querying the CONTROLFILE_TYPE col umn of the V$DATABASE view.
10-2. Restoring Control File Using Recovery Catalog
Problem
You need to restore your control file, and you use a recovery catalog when creating backups.
Solution
Restoring the control file is fairly simple when you use a recovery catalog. All you need to do is ensure that you connect to both your target database and the recovery catalog.
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> restore controlfile;
How It Works
Using a recovery catalog makes it straightforward to restore the control file.
When you issue the restore controlfile command, RMAN will retrieve from the recovery catalog the location and name of the file that contains the control file backup and restores the control file appro-
priately. Because the recovery catalog knows the location of the RMAN backup piece,
it doesn’t matter whether the backup piece is in a flash recovery area or in a configured channel location.
When you’re connected to the recovery catalog, you can view backup information about your control files even while your target database is in nomount mode.
To list backups of your
control files, use the list command as shown here:
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> list backup of controlfile;
If you have registered two databases in the recovery catalog with the same name, then
you might receive an error such as this when you attempt to list backups or restore the control
file:
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20005: target database name is ambiguous
In this situation, you will need to first set your database identifier (DBID) before you can restore your control file.
how to determine your DBID.
The database name that is stored in the recovery catalog is not guaranteed to be unique.
You can verify that you have multiple databases with the same name in your recovery catalog by querying the recovery catalog RC_DATABASE view as shown here:
SQL> connect rcat/rcat@rcat
SQL> select db_key, dbid, name from rc_database;
DB_KEY DBID NAME
------ ---------- ----
1 1124743449 ORCL
4241 1140772490 ORCL
10-3. Determining the Database Identifier
Problem
Your backup strategy doesn’t take advantage of either a flash recovery area or a recovery catalog.
You are trying to restore a control file as follows, and you receive an error message stating that you must explicitly set the database identifier (DBID):
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN specifically instructs you to set the DBID first:
RMAN-06495: must explicitly specify DBID with SET DBID command
You don’t know the DBID for your database, and you aren’t sure how to find the DBID.
Without a control file for your database, you can’t mount the database and query the DBID
value from the V$DATABASE view.
Solution
You can determine the DBID of your database in one of the following ways:
• You can derive the DBID from an autobackup file.
• You can retrieve the DBID from RMAN output.
• You can write the DBID to the alert.log file.
• You can derive DBID from a file dump.
Deriving the DBID from an Autobackup File
If you chose to configure the autobackup control file format, then you are required to include the format variable %F when formatting the name used for the RMAN backup piece.
The format of the %F variable is a unique combination of the database identifier, the date, and a sequence, and it follows this format: c-IIIIIIIIII-YYYYMMDD-QQ. The first ten Is comprise your target data-
base’s DBID.
For example, if the control file backup piece name is c-2601506593-20060918-01,
then the DBID substring is 2601506593.
Description of %F Format Variable
String
g
c Signifies a control file backup.
IIIIIIIIII DBID.
YYYYMMDD Date backup was created. Used by maxdays parameter of the restore controlfile
command.
QQ A hex sequence number that is incremented each time a control file autobackup is
created for a given day. Used by the maxseq parameter of the restore controlfile
command.
The default location for a control file autobackup on Unix systems is ORACLE_HOME/dbs,
and on Windows platforms it’s usually ORACLE_HOME\database.
Writing the DBID to the Alert.log File
Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code
execute as part of your backup job:
COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));
After running the previous code, you should see a text message in your target database
alert.log file that looks like this:
DBID: 2601506593
No comments:
Post a Comment