The restore of spfile and control file from autobackup using maxdays option will fail if the maxdays specified is beyond 366 days:
RMAN> restore controlfile from autobackup maxseq 200 maxdays 500;
Starting restore at 21-oct-08 14:58using channel ORA_DISK_1
RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 10/21/2008 14:58:42RMAN-06494: MAXDAYS = 500 is out of range (1-366)
So how to restore spfile and control file from a backup taken more than 366 days ago? I found that when RMAN performs restore from autobackup file, it did not check the timestamp of the autobackup file but the timestamp that is stated in the autobackup file name. By changing the autobackup file name so that the timestamp is within the accepted range, it can find and read the autobackup file and restore the controlfile / spfile successfully.
Without the maxdays option, RMAN will only look for autobackup file with timestamp within the last 7 days. So if today is 22 Oct 08 and your autobackup file name in $ORACLE_HOME/dbs is “c-1154185322-20081012-01″, the “restore controlfile from autobackup” command will not be able to find or read the autobackup file.
Change it to “c-1154185322-20081021-01″ and RMAN will be able to restore the control file from autobackup with “restore controlfile from autobackup” command. I don’t have a controlfile autobackup created more than 366 days ago, but I think this trick can also address 366-maxdays limitation.
Friday, September 11, 2009
RMAN Restore and Recover of a Database When the Repository and spfile/init.ora Files are Lost
Oracle Database RMAN Restore/Recovery without spfile/init.ora Files
You have for whatever reason lost your spfile and your initORACLESID.ora files and you find yourself in a situation where you need to restore and recover your database.
This doesn’t happen very often and hopefully it never happens to you. The situation can arise during clone (duplicating a database using RMAN) but for whatever the reasons may be for the lost of your initialization files (media problems, etc.) here’s an outline of steps that may help to get your database back up and running.
If you only have the physical backuppieces on disk or tape and have lost your database and initialization files this is basically a disaster and or you have a need to restore and recover a database from and old backup that has long since been deleted or aged out of the RMAN Repository (examples would be a disk backup that was copied to tape prior to its deletion from RMAN).
Oracle 8i you don’t have spfiles but the Disk Backuppieces need to be restored or located in their original backup location, if there are backuppieces that are unknown to the restored control file it isn’t possible to catalog these files in this release but you can manually extract the information from the backupieces using PL/SQL.
Oracle 9i you can restore the spfile from an autobackup as well as the controlfile. You can’t catalog backuppieces unknown to the restored controlfile (same as 8i) but you can use PL/SQL to extract the information from the backuppieces. The disk backupieces must be located in their original backup locations.
Oracle 10g and 11g allows you to restore the spfile and controlfile from autobackups. You don’t have to place the original disk backuppieces in the same exact location as the original backup location. You can catalog unknown backuppieces that are missing from the restored controlfile and you can use PL/SQL to extract any missing information that you may need.
The goal is to restore the spfile and controlfile either from an autobackup controlfile or via a manual extraction process from an rman backuppiece so that the instance can be mounted. The restored controlfile then becomes the RMAN repository (no catalog), allowing a ‘normal’ catalog-free restore and recovery of the database to be executed.
The restore controlfile should therefore should also contain the metadata for the database and archivelog backups which will need to be restored and recovered.
The following constraints have to be acknowledged and understood:
It is not possible to restore an init.ora file from a backuppiece as init.ora files are never included in a backup for any Oracle release.
It is your responsibility to identify the required backuppieces required for the restore. This is where RMAN reports/listings come into play, if you generate these after each backup and store somewhere you can refer to these reports or similar type of listings.
The DBID of the target database has to be known
Unless you are using 10g+ all disk backuppieces must reside in the original backup location in case you need to restore to a new host and identical backup directory must be created on that host.
If you have to restore the spfile, controlfiles and datafiles by extracting this information from the backuppieces you will need to contact Oracle Support Services to work with them in order to accomplish these tasks.
Keep the backup logs for at least one complete backup cycle so that the backuppieces and the target DBID can be identified by reviewing these files.
Pick a format for the backuppiece that makes it easy to identify these files by name should the log files no longer be available.
8i Only – backup your init.ora file using any means possible. I have a crontab script that executes nightly that creates copies of my spfiles (10g and 11g) as well as the listener.ora, tnsnames.ora and other files that aid in the recovery process should I find myself in a situation that requires access to these files. I’ll include the script I use at the end of this post.
8i Only – make sure you backup the controlfile after every backup or archivelog backup, this is just good practice and you’ll see why if you have find yourself in a situation where you need this file. You should always have a controlfile backup that contains the latest backup metadata.
9i+ always use an spfile because RMAN can backup this file automatically whenever an operation occurs (within RMAN) that warrants a backup.
9i+ always turn on CONTROLFILE AUTOBACKUP ON
Become as familiar as possible with your RMAN configuration, monitor where the backups are written, review the logs daily, review your backup frequency and most importantly know where the backup of the controlfile is located at.
Restoring When Everything is Gone
Restore the init.ora file – applies for All Releases. RMAN has to connect to a running instance (a database started with an init.ora file in nomount mode). If an init.ora file is used rather than a spfile this needs to be restored from an OS backup or manually recreated in the proper format.
Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is NOT USED – relevant to Oracle 8i (where controlfile AUTOBACKUP didn’t exist), Oracle 9i+ where the persistent configuration CONTROLFILE AUTOBACKUP was set to OFF (not the default).
Figure out the latest controlfile backuppiece – if at all possible this should contain the latest metadata for the database and archivelog backups that required for the restore/recovery purposes. Use the past RMAN logs, check available backup directories on disk or query the media manager for your tape catalog to identify this information.
Extract the controlfile – Oracle 9i+ you can restore the control file from an explict backuppiece. Start the database in nomount mode via SQL*Plus and issue the command RMAN> restore controlfile from ‘backuppiece’;. If you are having problems identifying which backuppiece may contain a controlfile you will have to try various backuppieces until you locate a controlfile. If you can not locate one you’ll have to call Oracle Support for help and you’ll need to provide them with the following information: Current Schema of the database, files that were backed up, what time they were backed up and names of the backup pieces containing the Database’s files.
Mount the Instance – once the controlfile has been located and extracted you can continue and the next step is to mount the instance so jump down to Step 3.
Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is ON – Oracle 9i+ where the parameter CONTROLFILE AUTOBACKUP is set to ON (default). The autobackup controlfile feature is there to help ease the pain of recovery when the RMAN repository is gone. Controlfile autobackup formats have to include the %F which makes them easy to find and identify. %F equates to – “c-t-yyyymmdd-” (I’m having issues display the proper syntax due to my inability to figure out how to block quote a block of text so I’ll fix this later when I figure out how to do that) . If the controlfile backups are written to disk, the default location is /dbs (unix) and /database (windows) so that is the locations to look for the autobackup files first. If you don’t find them they were written to a non-default directory and if you don’t know where the non-default directory is you’ll have to do a search of ‘find’ in unix to locate them. If you find the file you can use the ‘SET CONTROLFILE AUTOBACKUP FORMAT’ in the script below to force RMAN to use the correct location for the autobackup file. For autobackups written to tape, no further action is required other than to allocate a channel – check the rman logs to find out what the media manager environment variables if any were specified for the channel and make sure you set up a similar if the same variable configuration.
Startup the Instance in NOMOUNT mode as follows (you can’t use SQL*Plus for this so don’t try):$ rman target /
RMAN> startup nomount;RMAN> set dbid=”dbid#”;RMAN> run {set controlfile autobackup format for device type disk to ‘path/%F’;restore spfile from autobackup;restore controlfile from autobackup;startup force mount;}
Notes: You get the dbid from the name of the controlfile autobackup filename or the rman backup logs. If you can’t find or identify the DBID you’re stuck. The ’set controlfile autobackup format’ is only necessary if the controlfile autobackup is in a non-default location - set ‘path’ accordingly. If you are using tapes for the restore: set controlfile autobackup format for device type sbt to ‘%F’;. ’startup FORCE mount’ is required because the instance was originally started without an spfile and if you try to mount without restarting it will fail.
3. Query the Backup History in the Restored Controlfile – Oracle 8i,9i,10g and 11g – after you’ve restored the controlfile and mounted the instance you now need to confirm that the controlfile actually contains the metadata for the database and the archivelog backups that will be required to restore and recover the database. You can use a command similiar to the following for this purpose:
RMAN> list backup;RMAN> list backup of database completed after ‘date’;RMAN> list backup of database completed between ’start date’ and ‘end date’;
The date must be in the appropriate NLS format (NLS_DATE_FORMAT).
Once you’ve found the database backup(s) you need then get the checkpoint scn of the backup (you’ll need this for the restore). You can see the SCN in the output of the above list commands. Then check that the archivelogs needed for the recovery have been backed up and recorded:
RMAN> list backup of archivelog from scn=xxxx;
Now if all the required metadata required for the restore and recovery are present you can continue on with the restore and recovery steps. You need to pick a archive log sequence number to specify for the restore and recovery process.
RMAN> run {set until sequence = xx thread 1;restore database;recover database;alter database open resetlogs;}
3.1 Restored CONTROLFILE does NOT CONTAIN THE DATABASE BACKUP METADATA – Oracle Releases 8i and 9i – located a later (newer) controlfile and try again. Otherwise, phone Oracle Support Services for assistance.
Once the datafiles and archivelogs have been extracted the recovery can be start via RMAN to recover up to (and including the log sequence xx).
RMAN> run {set until sequence xx thread 1;recover database;alter database open resetlogs;}
Oracle Release 10g+
Catalog the required backuppiece(s) into the restored backuppiece ‘X’;
For Disk Backup Pieces:
RMAN> catalog backuppiece ‘path\X’;
Where ‘X’ is the name of the backuppiece.
All of the information regarding the backuppiece content are in the backuppiece header and once you catalog the backuppiece you can then query the contents (list backupset xx;) and proceed with the restore and recovery of the database as normal.
You have for whatever reason lost your spfile and your initORACLESID.ora files and you find yourself in a situation where you need to restore and recover your database.
This doesn’t happen very often and hopefully it never happens to you. The situation can arise during clone (duplicating a database using RMAN) but for whatever the reasons may be for the lost of your initialization files (media problems, etc.) here’s an outline of steps that may help to get your database back up and running.
If you only have the physical backuppieces on disk or tape and have lost your database and initialization files this is basically a disaster and or you have a need to restore and recover a database from and old backup that has long since been deleted or aged out of the RMAN Repository (examples would be a disk backup that was copied to tape prior to its deletion from RMAN).
Oracle 8i you don’t have spfiles but the Disk Backuppieces need to be restored or located in their original backup location, if there are backuppieces that are unknown to the restored control file it isn’t possible to catalog these files in this release but you can manually extract the information from the backupieces using PL/SQL.
Oracle 9i you can restore the spfile from an autobackup as well as the controlfile. You can’t catalog backuppieces unknown to the restored controlfile (same as 8i) but you can use PL/SQL to extract the information from the backuppieces. The disk backupieces must be located in their original backup locations.
Oracle 10g and 11g allows you to restore the spfile and controlfile from autobackups. You don’t have to place the original disk backuppieces in the same exact location as the original backup location. You can catalog unknown backuppieces that are missing from the restored controlfile and you can use PL/SQL to extract any missing information that you may need.
The goal is to restore the spfile and controlfile either from an autobackup controlfile or via a manual extraction process from an rman backuppiece so that the instance can be mounted. The restored controlfile then becomes the RMAN repository (no catalog), allowing a ‘normal’ catalog-free restore and recovery of the database to be executed.
The restore controlfile should therefore should also contain the metadata for the database and archivelog backups which will need to be restored and recovered.
The following constraints have to be acknowledged and understood:
It is not possible to restore an init.ora file from a backuppiece as init.ora files are never included in a backup for any Oracle release.
It is your responsibility to identify the required backuppieces required for the restore. This is where RMAN reports/listings come into play, if you generate these after each backup and store somewhere you can refer to these reports or similar type of listings.
The DBID of the target database has to be known
Unless you are using 10g+ all disk backuppieces must reside in the original backup location in case you need to restore to a new host and identical backup directory must be created on that host.
If you have to restore the spfile, controlfiles and datafiles by extracting this information from the backuppieces you will need to contact Oracle Support Services to work with them in order to accomplish these tasks.
Keep the backup logs for at least one complete backup cycle so that the backuppieces and the target DBID can be identified by reviewing these files.
Pick a format for the backuppiece that makes it easy to identify these files by name should the log files no longer be available.
8i Only – backup your init.ora file using any means possible. I have a crontab script that executes nightly that creates copies of my spfiles (10g and 11g) as well as the listener.ora, tnsnames.ora and other files that aid in the recovery process should I find myself in a situation that requires access to these files. I’ll include the script I use at the end of this post.
8i Only – make sure you backup the controlfile after every backup or archivelog backup, this is just good practice and you’ll see why if you have find yourself in a situation where you need this file. You should always have a controlfile backup that contains the latest backup metadata.
9i+ always use an spfile because RMAN can backup this file automatically whenever an operation occurs (within RMAN) that warrants a backup.
9i+ always turn on CONTROLFILE AUTOBACKUP ON
Become as familiar as possible with your RMAN configuration, monitor where the backups are written, review the logs daily, review your backup frequency and most importantly know where the backup of the controlfile is located at.
Restoring When Everything is Gone
Restore the init.ora file – applies for All Releases. RMAN has to connect to a running instance (a database started with an init.ora file in nomount mode). If an init.ora file is used rather than a spfile this needs to be restored from an OS backup or manually recreated in the proper format.
Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is NOT USED – relevant to Oracle 8i (where controlfile AUTOBACKUP didn’t exist), Oracle 9i+ where the persistent configuration CONTROLFILE AUTOBACKUP was set to OFF (not the default).
Figure out the latest controlfile backuppiece – if at all possible this should contain the latest metadata for the database and archivelog backups that required for the restore/recovery purposes. Use the past RMAN logs, check available backup directories on disk or query the media manager for your tape catalog to identify this information.
Extract the controlfile – Oracle 9i+ you can restore the control file from an explict backuppiece. Start the database in nomount mode via SQL*Plus and issue the command RMAN> restore controlfile from ‘backuppiece’;. If you are having problems identifying which backuppiece may contain a controlfile you will have to try various backuppieces until you locate a controlfile. If you can not locate one you’ll have to call Oracle Support for help and you’ll need to provide them with the following information: Current Schema of the database, files that were backed up, what time they were backed up and names of the backup pieces containing the Database’s files.
Mount the Instance – once the controlfile has been located and extracted you can continue and the next step is to mount the instance so jump down to Step 3.
Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is ON – Oracle 9i+ where the parameter CONTROLFILE AUTOBACKUP is set to ON (default). The autobackup controlfile feature is there to help ease the pain of recovery when the RMAN repository is gone. Controlfile autobackup formats have to include the %F which makes them easy to find and identify. %F equates to – “c-t-yyyymmdd-” (I’m having issues display the proper syntax due to my inability to figure out how to block quote a block of text so I’ll fix this later when I figure out how to do that) . If the controlfile backups are written to disk, the default location is /dbs (unix) and /database (windows) so that is the locations to look for the autobackup files first. If you don’t find them they were written to a non-default directory and if you don’t know where the non-default directory is you’ll have to do a search of ‘find’ in unix to locate them. If you find the file you can use the ‘SET CONTROLFILE AUTOBACKUP FORMAT’ in the script below to force RMAN to use the correct location for the autobackup file. For autobackups written to tape, no further action is required other than to allocate a channel – check the rman logs to find out what the media manager environment variables if any were specified for the channel and make sure you set up a similar if the same variable configuration.
Startup the Instance in NOMOUNT mode as follows (you can’t use SQL*Plus for this so don’t try):$ rman target /
RMAN> startup nomount;RMAN> set dbid=”dbid#”;RMAN> run {set controlfile autobackup format for device type disk to ‘path/%F’;restore spfile from autobackup;restore controlfile from autobackup;startup force mount;}
Notes: You get the dbid from the name of the controlfile autobackup filename or the rman backup logs. If you can’t find or identify the DBID you’re stuck. The ’set controlfile autobackup format’ is only necessary if the controlfile autobackup is in a non-default location - set ‘path’ accordingly. If you are using tapes for the restore: set controlfile autobackup format for device type sbt to ‘%F’;. ’startup FORCE mount’ is required because the instance was originally started without an spfile and if you try to mount without restarting it will fail.
3. Query the Backup History in the Restored Controlfile – Oracle 8i,9i,10g and 11g – after you’ve restored the controlfile and mounted the instance you now need to confirm that the controlfile actually contains the metadata for the database and the archivelog backups that will be required to restore and recover the database. You can use a command similiar to the following for this purpose:
RMAN> list backup;RMAN> list backup of database completed after ‘date’;RMAN> list backup of database completed between ’start date’ and ‘end date’;
The date must be in the appropriate NLS format (NLS_DATE_FORMAT).
Once you’ve found the database backup(s) you need then get the checkpoint scn of the backup (you’ll need this for the restore). You can see the SCN in the output of the above list commands. Then check that the archivelogs needed for the recovery have been backed up and recorded:
RMAN> list backup of archivelog from scn=xxxx;
Now if all the required metadata required for the restore and recovery are present you can continue on with the restore and recovery steps. You need to pick a archive log sequence number to specify for the restore and recovery process.
RMAN> run {set until sequence = xx thread 1;restore database;recover database;alter database open resetlogs;}
3.1 Restored CONTROLFILE does NOT CONTAIN THE DATABASE BACKUP METADATA – Oracle Releases 8i and 9i – located a later (newer) controlfile and try again. Otherwise, phone Oracle Support Services for assistance.
Once the datafiles and archivelogs have been extracted the recovery can be start via RMAN to recover up to (and including the log sequence xx).
RMAN> run {set until sequence xx thread 1;recover database;alter database open resetlogs;}
Oracle Release 10g+
Catalog the required backuppiece(s) into the restored backuppiece ‘X’;
For Disk Backup Pieces:
RMAN> catalog backuppiece ‘path\X’;
Where ‘X’ is the name of the backuppiece.
All of the information regarding the backuppiece content are in the backuppiece header and once you catalog the backuppiece you can then query the contents (list backupset xx;) and proceed with the restore and recovery of the database as normal.
Oracle9i/10g RMAN Duplicate Database Errors
Recovering from RMAN Errors
The options for Manual Completion of an RMAN Duplication Task vary depending on which phase of the duplication failed. The following examples cover Oracle 9i and 10g. Manual completion steps out outlined per phase and most likely you’ll only two to execute a couple of the steps depending on where the failure occurred.
Restore Failure
If you receive an error during the RMAN restore of the database you need to determine what caused the problem and fix it. If very few files have been restored it may be easier to just start over and rerun the task from the beginning. If the duplication process failed after running for a long period of time and you rather not to start from the beginning (especially if it takes hours+) then you can try to recover manually and attempt to complete the process.
A likely cause if you are going from one server to another server is missing files or the rare outside possibility of a bad block in a file required for restore. You need to address whatever problem caused the file(s) to be missing from the restore location.
Remember that in order to successfully duplicate a database using RMAN’s DUPLICATE feature is that ALL files required to restore the database must be present on the remote server and in the same exact location unless you catalog the files if they are intentionally located in another directory. RMAN will not even start the restore process if it can’t find the backupsets in the expected location. A reason RMAN may be interrupted in this phase is because during the copy operating you ran out of disk space in the filesystem where you were depositing the backupsets, backup current controlfile and backup spfile and RMAN can’t locate one of the backupsets or arcivelog files.
$ export ORACLE_SID= (name of database cloning to)$ rman target /RMAN> run {set until scn xxxxxxxx;restore current controlfile from ‘restore directory’;alter database mount;set newname for datafile 1 to ”;set newname for datafile 2 to ”;….restore datafile i,i2,….;}
You need to identify the SCN from the output from the failed RMAN duplicate log and you must use the ‘SET NEWNAME’ for each datafile that remains to be restored as DB_FILE_NAME_CONVERT will not work with a normal restore.
For Oracle 10g+ it really is best to start over if RMAN failed during the first phase or restore step. Any files that have already been restored will be skipped and the duplicate process can be restarted without manual intervention.
Phase 2 Failure
This is the controlfile creation or switch of the datafile names after the datafiles have been restored. You need to review the log files and identify what the problem is and make sure you make a list to use for all the datafiles that have not been switched over. You can then attempt to complete this step manually by rename each datafile if the auxiliary instance uses a different file structure or the ASM Disk Group is different than that of the target’s directory structure or ASM Disk Group.
After the rename (switch) of all the datafiles that need to be renamed:
CREATE CONTROLFILE REUSE SET DATABASE RESETLOGS ARCHIVELOG…
SQL> alter database backup controlfile to trace;
Phase 3 Failure
Failure during the recovery of the restored datafiles. This is the next phase where each datafile is recovered to either a point in time or SCN. Determine the cause from the log file and then to continue after fixing the problem:
$ rman target / auxiliary sys/@RMAN> run {set until scn xxxxxxx;recover clone database;alter clone database open resetlogs;}
Get the ‘UNTIL SCN’ value from the duplicate logfile, connect to the target. Archivelogs will be automatically restored at 10g, restored into the Flash Recovery Area if this is defined. After completing recovery, change the Database Identifier (DBID) using the NID utility on Windows:
$ nid target=sys/oracle
DBNEWID …….…..Change database ID of database AUX? (Y/N)=>Y
The manual duplication process should be complete and you can jump down to Step 6 – Final Actions.
Phase 4 Failure
This phase is the controlfile recreation phase. Check the rman duplicate log and identify the reason the recovery didn’t complete – look for:
media recovery completeFinished recover at
Figure out what the problem is and fix the cause then execute the following after fixing the cause:
CREATE CONTROLFILE REUSE SET DATABASE ‘AUX’ RESETLOGS ARCHIVELOG…
Make sure ALL the files have been restored in the DATAFILE section of the RMAN duplicate log.
Phase 5 Failure
This failure would be in the phase that opens the database with resetlogs. Check the log file again and fix the problem. Look for Thread x closed at log sequence y
If the resetlogs was completed, determine what cause the error and fix the problem and restart the auxiliary instance. If resetlogs wasn’t completed successfully, determine what cause that problem and then open the clone database with resetlogs using RMAN (you can’t use SQL*Plus for this step) and connect to the target database first:
$ rman target / auxiliary sys/oracle@RMAN> alter clone database open resetlogs;
If the duplication process failed only in steps 5 then you are done, no further action is required. The DBID will have already been changed. Otherwise, execute the NID command to change the DBID (Windows).
Final Steps
$ rman target / auxiliary sys/oracle@RMAN> alter clone database open resetlogs;
Add any temp files missing to the new cloned auxiliary database. Files that were manuall restored to the auxiliary instance will be cataloged as datafile copies. Connect to the original target and execute:
RMAN> list copy of database;RMAN> crosscheck copy of datafile ;RMAN> delete expired copy of datafile ;
The options for Manual Completion of an RMAN Duplication Task vary depending on which phase of the duplication failed. The following examples cover Oracle 9i and 10g. Manual completion steps out outlined per phase and most likely you’ll only two to execute a couple of the steps depending on where the failure occurred.
Restore Failure
If you receive an error during the RMAN restore of the database you need to determine what caused the problem and fix it. If very few files have been restored it may be easier to just start over and rerun the task from the beginning. If the duplication process failed after running for a long period of time and you rather not to start from the beginning (especially if it takes hours+) then you can try to recover manually and attempt to complete the process.
A likely cause if you are going from one server to another server is missing files or the rare outside possibility of a bad block in a file required for restore. You need to address whatever problem caused the file(s) to be missing from the restore location.
Remember that in order to successfully duplicate a database using RMAN’s DUPLICATE feature is that ALL files required to restore the database must be present on the remote server and in the same exact location unless you catalog the files if they are intentionally located in another directory. RMAN will not even start the restore process if it can’t find the backupsets in the expected location. A reason RMAN may be interrupted in this phase is because during the copy operating you ran out of disk space in the filesystem where you were depositing the backupsets, backup current controlfile and backup spfile and RMAN can’t locate one of the backupsets or arcivelog files.
$ export ORACLE_SID= (name of database cloning to)$ rman target /RMAN> run {set until scn xxxxxxxx;restore current controlfile from ‘restore directory’;alter database mount;set newname for datafile 1 to ”;set newname for datafile 2 to ”;….restore datafile i,i2,….;}
You need to identify the SCN from the output from the failed RMAN duplicate log and you must use the ‘SET NEWNAME’ for each datafile that remains to be restored as DB_FILE_NAME_CONVERT will not work with a normal restore.
For Oracle 10g+ it really is best to start over if RMAN failed during the first phase or restore step. Any files that have already been restored will be skipped and the duplicate process can be restarted without manual intervention.
Phase 2 Failure
This is the controlfile creation or switch of the datafile names after the datafiles have been restored. You need to review the log files and identify what the problem is and make sure you make a list to use for all the datafiles that have not been switched over. You can then attempt to complete this step manually by rename each datafile if the auxiliary instance uses a different file structure or the ASM Disk Group is different than that of the target’s directory structure or ASM Disk Group.
After the rename (switch) of all the datafiles that need to be renamed:
CREATE CONTROLFILE REUSE SET DATABASE RESETLOGS ARCHIVELOG…
SQL> alter database backup controlfile to trace;
Phase 3 Failure
Failure during the recovery of the restored datafiles. This is the next phase where each datafile is recovered to either a point in time or SCN. Determine the cause from the log file and then to continue after fixing the problem:
$ rman target / auxiliary sys/@RMAN> run {set until scn xxxxxxx;recover clone database;alter clone database open resetlogs;}
Get the ‘UNTIL SCN’ value from the duplicate logfile, connect to the target. Archivelogs will be automatically restored at 10g, restored into the Flash Recovery Area if this is defined. After completing recovery, change the Database Identifier (DBID) using the NID utility on Windows:
$ nid target=sys/oracle
DBNEWID …….…..Change database ID of database AUX? (Y/N)=>Y
The manual duplication process should be complete and you can jump down to Step 6 – Final Actions.
Phase 4 Failure
This phase is the controlfile recreation phase. Check the rman duplicate log and identify the reason the recovery didn’t complete – look for:
media recovery completeFinished recover at
Figure out what the problem is and fix the cause then execute the following after fixing the cause:
CREATE CONTROLFILE REUSE SET DATABASE ‘AUX’ RESETLOGS ARCHIVELOG…
Make sure ALL the files have been restored in the DATAFILE section of the RMAN duplicate log.
Phase 5 Failure
This failure would be in the phase that opens the database with resetlogs. Check the log file again and fix the problem. Look for Thread x closed at log sequence y
If the resetlogs was completed, determine what cause the error and fix the problem and restart the auxiliary instance. If resetlogs wasn’t completed successfully, determine what cause that problem and then open the clone database with resetlogs using RMAN (you can’t use SQL*Plus for this step) and connect to the target database first:
$ rman target / auxiliary sys/oracle@RMAN> alter clone database open resetlogs;
If the duplication process failed only in steps 5 then you are done, no further action is required. The DBID will have already been changed. Otherwise, execute the NID command to change the DBID (Windows).
Final Steps
$ rman target / auxiliary sys/oracle@RMAN> alter clone database open resetlogs;
Add any temp files missing to the new cloned auxiliary database. Files that were manuall restored to the auxiliary instance will be cataloged as datafile copies. Connect to the original target and execute:
RMAN> list copy of database;RMAN> crosscheck copy of datafile ;RMAN> delete expired copy of datafile ;
RMAN Duplicate Database Feature in 11G
RMAN Duplicate Database Feature in 11G
You can create a duplicate database using the RMAN duplicate command. The duplicate database will have a different DBID from the source database and it functions entirely independently because it is completely independent once the duplication has taken place and the duped database is open for use.
Starting with Oracle 11g there are now two ways to duplicate a database:
1. Active database duplication2. Backup-based duplication
Active database duplication involves copying the live running database over the network to the auxiliary destination and creating the duplicate database. The backup-based duplication requires copying over using NFS to make available the backupset(s) to the destination database. The only difference between the two is you do not need pre-existing RMAN backups and copies (archivelogs). The duplication work is performed by the auxiliary channel and this channel corresponds to a server session on the auxiliary instance on the auxiliary (destination) host.
The active database duplication is slower because it is using the network to transport the data blocks instead of accessing existing RMAN backupsets. RMAN carries out the following steps as part of the duplication process:
1. Creates a control file for the duplicate database2. Restarts the auxiliary instance and mounts the duplicate control file3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs4. Opens the duplicate database with the RESETLOGS option
In the case of active database duplication, RMAN copies the target datafiles over the network to the auxiliary (destination) instance.
A RAC Target database can be duplicated as well, this feature is not restricted to non-RAC databases. The procedure is the same as what is outlined below. If the auxiliary database needs to be a RAC-database then you start the process to duplicate a single instance and convert the auxiliary to RAC after the duplicate process has succeeded.
The next section is devoted to describing the process for Active Database Duplication.
Active Database Duplication
1. Prepare the auxiliary database instance2. Create the initialization parameter file for the Auxiliary instance
If you are using SPFILE then according to Oracle the only parameter required for the duplicate database is the DB_NAME parameter. The rest of the parameters can be set in the DUPLICATE command. If you are not using the SPFILE technique for your Oracle initialization parameter file then you will need to create an init.ora file and set the initialization parameters. Again according to Oracle the only required parameters are:
DB_NAMECONTROL_FILESDB_BLOCK_SIZEDB_FILE_NAME_CONVERTLOG_FILE_NAME_CONVERTDB_RECOVERY_FILE_DEST
However, I have found if I use a full meaning a copy of one of my running Database’s parameter file it is easier than specifying the parameters in the DUPLICATE command itself. That is a personal preference and you should go with whatever works best for you. I use SPFILE files for all my Oracle Databases and whenever I have a need to duplicate one of them I will generate (create) an init.ora initialization parameter file for use with RMAN. I will then use my favorite editor to search and replace the database specific parameters with my new Oracle Database parameters like DB_NAME, DB_RECOVERY_FILE_DEST, etc.
3. Create the Oracle Password file for the Auxiliary Instance – this is a requirement for RMAN in order to duplicate a database you need to connect directly to the auxiliary instance using the password file with the same SYSDBA password as the target database’s password. The passwords have to match exactly in order for this to work. You can specify the PASSWORD FILE option on the DUPLICATE command in which case if you do RMAN will copy the source database password file to the destination host and overwrite any existing password file with the same name as the auxiliary instance’s name.
4. Make the necessary changes to the listener.ora and tnsnames.ora file in order to establish SQL*Net connectivity before starting the RMAN duplicate session. You have to be able to connect through Oracle Net to the target and the auxiliary instance in order to use Active Database duplication.5. Start the Auxiliary instance from SQL*Plus – start the database and put it in nomount mode but first take care of the following steps:
I always create a link in $ORACLE_HOME/dbs to the initialization file and I just rename this link when I’m finished with RMAN to switch over to using a spfile instead of the pfile method. This way you do not have to type in a lot of characters whenever you stop, start the auxiliary instance because more than likely you will have to execute the process more than once unless you get it right the very first time! I set up the ADR – in Oracle 11G the new Automatic Diagnostic Recovery filesystem (if you aren’t using ADR set up the $ORACLE_BASE/admin/$ORACLE_SID/bdump/…._) directories prior to bringing up the auxiliary instance in nomount mode. You will need to create the supporting directories prior to starting the auxiliary instance.
Windows:
Create the parameter file (using Oracle’s example):
initNEW.oraDB_NAME=NEWdiagnostic_dest=’E:\opt\oracle’DB_FILE_name_CONVERT=(’I:\oradata\OLD’,'E:\oradata\NEW’)LOG_FILE_NAME_CONVERT=(’I:\oradata\onlinelog\OLD’,'E:\oradata\onlinelog\NEW’)SGA_TARGET=26214400CONTROL_FILES=’E:\oradata\controlfile\NEW\control01.dbf’COMPATIBLE=11.1.0.0.0
Create the Database Service (Windows Only) and password file:
% set ORACLE_SID=NEW% set ORACLE_HOME=E:\opt\app\oracle\product\11.1.0\db_1% oradim -NEW -SID NEW% orapwd FILE=E:\opt\app\oracle\product\11.1.0\db_1\database\PWPDNEW.ora PASSWORD=sys
% sqlplus “/ as sysdba”sql> startup nomount;
6. Create the necessary Oracle Net connectivity in the listener.ora and tnsnames.ora files – you need to make sure you specify SERVER = DEDICATED in the tnsnames.ora entry for your auxiliary instance and if you are using Oracle on HP-UX 11i v3 Itanium there is a bug that requires you to use the string ‘(UR=A)’ at the end of the tnsnames.ora entry (put it after the SERVICE_NAME entry). Confirm the connectivity to the target, auxiliary and you may want to confirm connectivity to your RMAN catalog even though you will not be using the catalog for this type of database duplication.
7. Start RMAN and connect to the source database by specifying the source as the target database. The duplicate database instance will be specified in the AUXILIARY connection. You can invoke the RMAN client on any host so long as that host has connectivity and you can connect to all of the required database instances. If the auxiliary instance requires a text-based initialization parameter file (pfile) then this file must exist and it must reside on the same host that runs the RMAN client application.
% rmanrman> connect target sys/sys@old;rman> connect auxiliary sys/sys
8. Next you will be issuing the DUPLICATE database command in order to start the duplicate process and the simplest case is when you duplicate the target database to a different host and use a different directory structure. This example will assume you are using a recovery catalog, the target database is on hosta and it contains four datafiles. You duplicate the target to database AUX on a different host (hostb) and hostb has a different directory structure. The tablespace USERS is a read-only tablespace for the purpose of this example. Execute the duplciate database command from the Auxiliary site:
rman duplicate target database to ‘NEW’ from active database db_file_name_convert ‘i:\oradata\OLD’,'e:\oradata\NEW’;
A dedicated listener configuration for RMAN is required. Using instance registration requires that the database be mounted in order to register with the listener. RMAN also requires SYSDBA access to the nomount instance (Auxiliary). The control files will be create using the location and names specified in the Oracle initialization file. The use of log_file_name_convert and db_file_name_convert instructs RMAN to generate the “set newname” commands for you. You can create your own set instead of using these two commands and you can use the ‘logfile’ command to specify where the log files will be created and what size they will be created with
You can create a duplicate database using the RMAN duplicate command. The duplicate database will have a different DBID from the source database and it functions entirely independently because it is completely independent once the duplication has taken place and the duped database is open for use.
Starting with Oracle 11g there are now two ways to duplicate a database:
1. Active database duplication2. Backup-based duplication
Active database duplication involves copying the live running database over the network to the auxiliary destination and creating the duplicate database. The backup-based duplication requires copying over using NFS to make available the backupset(s) to the destination database. The only difference between the two is you do not need pre-existing RMAN backups and copies (archivelogs). The duplication work is performed by the auxiliary channel and this channel corresponds to a server session on the auxiliary instance on the auxiliary (destination) host.
The active database duplication is slower because it is using the network to transport the data blocks instead of accessing existing RMAN backupsets. RMAN carries out the following steps as part of the duplication process:
1. Creates a control file for the duplicate database2. Restarts the auxiliary instance and mounts the duplicate control file3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs4. Opens the duplicate database with the RESETLOGS option
In the case of active database duplication, RMAN copies the target datafiles over the network to the auxiliary (destination) instance.
A RAC Target database can be duplicated as well, this feature is not restricted to non-RAC databases. The procedure is the same as what is outlined below. If the auxiliary database needs to be a RAC-database then you start the process to duplicate a single instance and convert the auxiliary to RAC after the duplicate process has succeeded.
The next section is devoted to describing the process for Active Database Duplication.
Active Database Duplication
1. Prepare the auxiliary database instance2. Create the initialization parameter file for the Auxiliary instance
If you are using SPFILE then according to Oracle the only parameter required for the duplicate database is the DB_NAME parameter. The rest of the parameters can be set in the DUPLICATE command. If you are not using the SPFILE technique for your Oracle initialization parameter file then you will need to create an init.ora file and set the initialization parameters. Again according to Oracle the only required parameters are:
DB_NAMECONTROL_FILESDB_BLOCK_SIZEDB_FILE_NAME_CONVERTLOG_FILE_NAME_CONVERTDB_RECOVERY_FILE_DEST
However, I have found if I use a full meaning a copy of one of my running Database’s parameter file it is easier than specifying the parameters in the DUPLICATE command itself. That is a personal preference and you should go with whatever works best for you. I use SPFILE files for all my Oracle Databases and whenever I have a need to duplicate one of them I will generate (create) an init.ora initialization parameter file for use with RMAN. I will then use my favorite editor to search and replace the database specific parameters with my new Oracle Database parameters like DB_NAME, DB_RECOVERY_FILE_DEST, etc.
3. Create the Oracle Password file for the Auxiliary Instance – this is a requirement for RMAN in order to duplicate a database you need to connect directly to the auxiliary instance using the password file with the same SYSDBA password as the target database’s password. The passwords have to match exactly in order for this to work. You can specify the PASSWORD FILE option on the DUPLICATE command in which case if you do RMAN will copy the source database password file to the destination host and overwrite any existing password file with the same name as the auxiliary instance’s name.
4. Make the necessary changes to the listener.ora and tnsnames.ora file in order to establish SQL*Net connectivity before starting the RMAN duplicate session. You have to be able to connect through Oracle Net to the target and the auxiliary instance in order to use Active Database duplication.5. Start the Auxiliary instance from SQL*Plus – start the database and put it in nomount mode but first take care of the following steps:
I always create a link in $ORACLE_HOME/dbs to the initialization file and I just rename this link when I’m finished with RMAN to switch over to using a spfile instead of the pfile method. This way you do not have to type in a lot of characters whenever you stop, start the auxiliary instance because more than likely you will have to execute the process more than once unless you get it right the very first time! I set up the ADR – in Oracle 11G the new Automatic Diagnostic Recovery filesystem (if you aren’t using ADR set up the $ORACLE_BASE/admin/$ORACLE_SID/bdump/…._) directories prior to bringing up the auxiliary instance in nomount mode. You will need to create the supporting directories prior to starting the auxiliary instance.
Windows:
Create the parameter file (using Oracle’s example):
initNEW.oraDB_NAME=NEWdiagnostic_dest=’E:\opt\oracle’DB_FILE_name_CONVERT=(’I:\oradata\OLD’,'E:\oradata\NEW’)LOG_FILE_NAME_CONVERT=(’I:\oradata\onlinelog\OLD’,'E:\oradata\onlinelog\NEW’)SGA_TARGET=26214400CONTROL_FILES=’E:\oradata\controlfile\NEW\control01.dbf’COMPATIBLE=11.1.0.0.0
Create the Database Service (Windows Only) and password file:
% set ORACLE_SID=NEW% set ORACLE_HOME=E:\opt\app\oracle\product\11.1.0\db_1% oradim -NEW -SID NEW% orapwd FILE=E:\opt\app\oracle\product\11.1.0\db_1\database\PWPDNEW.ora PASSWORD=sys
% sqlplus “/ as sysdba”sql> startup nomount;
6. Create the necessary Oracle Net connectivity in the listener.ora and tnsnames.ora files – you need to make sure you specify SERVER = DEDICATED in the tnsnames.ora entry for your auxiliary instance and if you are using Oracle on HP-UX 11i v3 Itanium there is a bug that requires you to use the string ‘(UR=A)’ at the end of the tnsnames.ora entry (put it after the SERVICE_NAME entry). Confirm the connectivity to the target, auxiliary and you may want to confirm connectivity to your RMAN catalog even though you will not be using the catalog for this type of database duplication.
7. Start RMAN and connect to the source database by specifying the source as the target database. The duplicate database instance will be specified in the AUXILIARY connection. You can invoke the RMAN client on any host so long as that host has connectivity and you can connect to all of the required database instances. If the auxiliary instance requires a text-based initialization parameter file (pfile) then this file must exist and it must reside on the same host that runs the RMAN client application.
% rmanrman> connect target sys/sys@old;rman> connect auxiliary sys/sys
8. Next you will be issuing the DUPLICATE database command in order to start the duplicate process and the simplest case is when you duplicate the target database to a different host and use a different directory structure. This example will assume you are using a recovery catalog, the target database is on hosta and it contains four datafiles. You duplicate the target to database AUX on a different host (hostb) and hostb has a different directory structure. The tablespace USERS is a read-only tablespace for the purpose of this example. Execute the duplciate database command from the Auxiliary site:
rman duplicate target database to ‘NEW’ from active database db_file_name_convert ‘i:\oradata\OLD’,'e:\oradata\NEW’;
A dedicated listener configuration for RMAN is required. Using instance registration requires that the database be mounted in order to register with the listener. RMAN also requires SYSDBA access to the nomount instance (Auxiliary). The control files will be create using the location and names specified in the Oracle initialization file. The use of log_file_name_convert and db_file_name_convert instructs RMAN to generate the “set newname” commands for you. You can create your own set instead of using these two commands and you can use the ‘logfile’ command to specify where the log files will be created and what size they will be created with
Subscribe to:
Posts (Atom)