Restore Archive Logs to New Location using RMAN

Oracle up to 10g

When attempting to restore archive logs that are already located on disk using RMAN you’ll receive an error if the archivelogs are stored within ASM because until Oracle 11g shipped you could not use the OS copy or move command to retrieve them.

The solution is to use RMAN to copy them from ASM to an OS filesystem:

RMAN> copy archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq_2.245.88866622′ to ‘/tmp/oraclearchive’;

You would have to repeat this command for each archive log to be copied or try the following solution:

RMAN> change archivelog from logseq=60 until logseq=70 uncatalog;

Once the archivelos have been successfully uncataloged you are now able to successfully restore the archive logs to a new location.

RMAN> run {
set archivelog destination to ‘/tmp’;
restore archivelog from logseq=60 until logseq=70;
}

Following the successful restore of the archivelogs you can then continue to recatalog the archivelogs back into the ASM diskgroup.

Example:

RMAN> catalog archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq.60.245.888666222′;

How To Move Datafile from a File System to ASM

Up to 10g R2:

Moving a datafile from the file system can be achieved in two ways:

1. While the database is in shutdown or mount stage
2. While the database is running with the select tablespace offline

Moving an Oracle datafile is in mount state is performed in the following way:

1. Shutdown and mount the database

SQL> shutdown immediate;
SQL> startup mount;

Check disk space in the ASM diskgroup you want to copy the datafile to:

SQL> select file#, name, (bytes/1048576) File_size_MB from v$datafile;

export ORACLE_SID=+ASM

SQL> select name, state, total_mb, free_mb from v$asm_diskgroup;

$ rman target=/
RMAN> copy datafile 4 to ‘+psdisk2′;

Update the controlfile with the new location of the datafile:

$ rman target /
RMAN> switch datafile 4 to copy;

The file is now in the new location;

SQL> select name from v$datafile;

The database may now be opened.

While the Database is Running (with the selected tablespace offline)

In order to move a datafile on a running active database, the tablespace where the datafile resides must be placed offline first. Identify the tablespace which contains the datafile and offline that tablespace.

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;
SQL> alter tablespace users offline;

After you have taken the tablespace offline you can execute the same steps above for copying the datafile to the ASM Diskgroup and then put the tablespace back online.
_

RMAN General Commands Views, etc.

V$ Tables Related to Backup Recovery/Restore

Each release Oracle brought up new features and enhancements to RMAN improving its ease of use and functionality. Using the updateable persistent configuration options (CONFIGURE ….), backup up the database is easier then in the first version of RMAN introduced in 8i. It is help to list the Synonyms containing the word RMAN and BACKUP in them.

Here’s a list from an Oracle 11g database for those with BACKUP in the name:

OBJECT_NAME
——————————
V$BACKUP
V$BACKUP_SET
V$BACKUP_PIECE
V$BACKUP_DATAFILE
V$BACKUP_SPFILE
V$BACKUP_REDOLOG
V$BACKUP_CORRUPTION
V$BACKUP_DEVICE
V$BACKUP_SYNC_IO
V$BACKUP_ASYNC_IO
GV$BACKUP
GV$BACKUP_SET
GV$BACKUP_PIECE
GV$BACKUP_DATAFILE
GV$BACKUP_SPFILE
GV$BACKUP_REDOLOG
GV$BACKUP_CORRUPTION
GV$BACKUP_DEVICE
GV$BACKUP_SYNC_IO
GV$BACKUP_ASYNC_IO
V$BACKUP_FILES
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
V$BACKUP_SET_DETAILS
V$BACKUP_PIECE_DETAILS
V$BACKUP_COPY_DETAILS
V$BACKUP_DATAFILE_DETAILS
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_SPFILE_DETAILS
V$BACKUP_SET_SUMMARY
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_SPFILE_SUMMARY
V$BACKUP_COPY_SUMMARY
V$UNUSABLE_BACKUPFILE_DETAILS
V$RMAN_BACKUP_TYPE
MGMT$HA_BACKUP

39 rows selected.

Here’s a list from the same Oracle 11g database with RMAN in the name:

OBJECT_NAME
——————————
V$RMAN_CONFIGURATION
GV$RMAN_CONFIGURATION
V$RMAN_STATUS
V$RMAN_OUTPUT
GV$RMAN_OUTPUT
V$RMAN_COMPRESSION_ALGORITHM
GV$RMAN_COMPRESSION_ALGORITHM
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_TYPE
V$RMAN_ENCRYPTION_ALGORITHMS
MGMT$HA_RMAN_CONFIG
MGMT$HA_RMAN_CONFIG_ALL

13 rows selected.

Controlfile views to reference are:

V_$ARCHIVE
V_$ARCHIVED_LOG
V_$ARCHIVE_DEST
V_$ARCHIVE_DEST_STATUS
V_$ARCHIVE_GAP
V_$ARCHIVE_PROCESSES
V_$BACKUP_ARCHIVELOG_DETAILS
V_$BACKUP_ARCHIVELOG_SUMMARY
V_$BACKUP_CONTROLFILE_DETAILS
V_$BACKUP_CONTROLFILE_SUMMARY
V_$BACKUP_DATAFILE
V_$BACKUP_DATAFILE_DETAILS
V_$BACKUP_DATAFILE_SUMMARY
V_$CONTROLFILE
V_$CONTROLFILE_RECORD_SECTION
V_$DATAFILE
V_$DATAFILE_COPY
V_$DATAFILE_HEADER
V_$SYSTEM_FIX_CONTROL
V_$PROXY_ARCHIVEDLOG
V_$PROXY_ARCHIVELOG_DETAILS
V_$PROXY_ARCHIVELOG_SUMMARY
V_$PROXY_DATAFILE
GV_$FOREIGN_ARCHIVED_LOG
GV_$PROXY_ARCHIVEDLOG
GV_$PROXY_DATAFILE
GV_$SESSION_FIX_CONTROL
V_$SESSION_FIX_CONTROL
V_$DLM_TRAFFIC_CONTROLLER
V_$FOREIGN_ARCHIVED_LOG
GV_$BACKUP_DATAFILE
GV_$CONTROLFILE
GV_$CONTROLFILE_RECORD_SECTION
GV_$DATAFILE
GV_$DATAFILE_COPY
GV_$DATAFILE_HEADER
GV_$DLM_TRAFFIC_CONTROLLER
GV_$ARCHIVE
GV_$ARCHIVED_LOG
GV_$ARCHIVE_DEST
GV_$ARCHIVE_DEST_STATUS
GV_$ARCHIVE_GAP
GV_$ARCHIVE_PROCESSES
DBA_REGISTERED_ARCHIVED_LOG
DBA_REPRESOL_STATS_CONTROL
USER_REPRESOL_STATS_CONTROL
ALL_REPRESOL_STATS_CONTROL
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
DBA_HIST_DATAFILE
DBA_HIST_WR_CONTROL
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES
GV_$SYSTEM_FIX_CONTROL
PS_CONTROL_TBL_VW
MGMT_V_DB_CONTROLFILES_ECM
MGMT_V_DB_DATAFILES_ECM
MGMT$DB_CONTROLFILES
MGMT$DB_CONTROLFILES_ALL
MGMT$DB_DATAFILES
MGMT$DB_DATAFILES_ALL

62 rows selected.

That is a lot of objects and views. To view the default RMAN configuration and then check the V$ view to see what is stored you can accomplish this with a query like this:

set linesize 132
column name format a40
column value format a40
set echo on
set serveroutput on

select * from v$rman_configuration
/

CONF# NAME VALUE
1 BACKUP OPTIMIZATION ON
2 DEFAULT DEVICE TYPE TO DISK
3 CONTROLFILE AUTOBACKUP ON
4 DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET
PARALLELISM 2

5 DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1
6 ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1
7 SNAPSHOT CONTROLFILE NAME TO ‘/opt/app/oracle/admin/backup/PS90HRD
V/backupsets/snapcf_ev.f’

8 MAXSETSIZE TO UNLIMITED
9 CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE DISK TO ‘+ps90hr_disk3/ps90hrdv/autoback
TYPE up/%F’

9 rows selected.

If you aren’t using a recovery catalog you can look at the controlfile since this is were backup information is maintained if you aren’t using a recovery catalog:

select type, records_total, records_used
from v$controlfile_record_section
where type like ‘%BACKUP%’
/

TYPE RECORDS_TOTAL RECORDS_USED
—————————- ————- ————
BACKUP SET 1227 224
BACKUP PIECE 1000 331
BACKUP DATAFILE 2126 2126
BACKUP REDOLOG 215 215
BACKUP CORRUPTION 1115 0
BACKUP SPFILE 131 93

6 rows selected.

The records used column isn’t zero because I’ve taken numerous backups, however, if you haven’t completed a backup yet this column will be zero. When RMAN reads the files to determine the files that will be backed up, if any corruption is detected then the corruption views are populated with that information such as file#, block# and contiguous blocks after the initial corrupt block.

RMAN will continue to use the persistent backup configuration settings once they are set unless they are overridden. The RMAN “SHOW” command displays those settings:

SHOW option [,parameter, ...]
RETENTION POLICY
EXCLUDE
BACKUP COPIES
CHANNEL
DEFAULT DEVICE TYPE
DEVICE TYPE
SNAPSHOT CONTROLFILE
…
ALL

RMAN> show all;

Recovery Manager: Release 11.1.0.6.0 – Production on Sat Nov 1 21:32:38 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

show all;connected to target database: PS90HRDV (DBID=2173713801)
connected to recovery catalog database

RMAN>
RMAN configuration parameters for database with db_unique_name PS90HRDV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘+ps90hr_disk3/ps90hrdv/autobackup/%F’;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE COMPRESSION ALGORITHM ‘BZIP2′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/snapcf_ev.f’;

You can script the configuration parameters you want to use within a run block to change one or more parameters or just change a single parameter at a time from the RMAN prompt and not use the run block option.

Example:

CONNECT catalog rman/rman
crosscheck copy;
delete expired copy;
crosscheck backup;
delete expired backup;
run {
configure channel 1 device type disk clear;
configure channel 2 device type disk clear;
configure device type disk backup type to backupset parallelism 1;
backup spfile;
configure snapshot controlfile name to ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/snapcf_ev.f’;
backup current controlfile;
configure channel 1 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/df_t%t_s%s_p%p’ maxpieces
ize 1750 M;
configure channel 2 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/df_t%t_s%s_p%p’ maxpieces
ize 1750 M;
configure device type disk backup type to compressed backupset parallelism 2;
backup as compressed backupset incremental level 0 database tag=”ps90hrdv”;
configure channel 1 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/al_t%t_s%s_p%p’ maxpieces
ize 5000M;
configure channel 2 device type disk format ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/al_t%t_s%s_p%p’ maxpieces
ize 5000M;
backup archivelog all delete all input tag=”ps90hrdv_archivelogs”;
configure channel 1 device type disk clear;
configure channel 2 device type disk clear;
configure snapshot controlfile name to ‘/opt/app/oracle/admin/backup/PS90HRDV/backupsets/snapcf_ev.f’;
backup current controlfile;
configure channel 1 device type disk clear;
}

Create Consistent Backup Script

You can create a RMAN script to generate a database backup without a recovery catalog or with a recovery catalog. You can set the autocontrolfile autobackup feature during configuration so the backup controlfile is used as the catalog to avoid putting creating a separate RMAN database to use its catalog.

connect target;
run {
shutdown immediate;
startup mount pfile=’/opt/app/oracle/admin/ORACLE_SID/pfile/initORCLSID.ora’;
backup incremental level 0 database format ‘/opt/app/oracle/oradata/ORCLSID/backup/%d_bckup_%U’ tag=”closed_bkup”;
shutdown;
startup pfile=’/opt/app/oracle/admin/ORACLE_SID/pfile/initORCLSID.ora’;
}
exit

RMAN> list summary;
RMAN> list backup summary;
RMAN> list backup by file;

If you use for example 2 channels for your backup you’ll see 2 backupsets in the earlier query if you run that query again because RMAN will split the backup across 2 channels which means there will be 2 backupsets created plus there will be a 3rd controlfile backup that will be generated by the CONTROLFILE AUTOBACKUP feature.

A controlfile autobackup will generate a backup controlfile for each of the following commands if they are executed:

BACKUP DATABASE;
BACKUP TABLESPACE;
BACKUP DATAFILE;
BACKUP ARCHIVELOG;

To determine which datafiles need a backup execute the following command:

RMAN> report need backup;

To check and see if any RMAN Backupsets are obsolete and can be removed (deleted), important if you are generating disk backupsets and you don’t have unlimited disk space available to you.

RMAN> report obsolete;

SQL> select recid, stamp, completion_time, incremental_level from v$backup_set;

RECID STAMP COMPLETION_TIME INCREMENTAL_LEVEL
———- ———- ————— —————–
74 669457740 30-OCT-08
77 669458037 30-OCT-08
81 669465792 30-OCT-08
83 669465817 30-OCT-08
84 669501608 30-OCT-08
85 669501611 30-OCT-08
86 669502225 30-OCT-08
87 669502228 30-OCT-08
88 669502858 30-OCT-08 0
89 669503121 30-OCT-08 0
90 669503486 30-OCT-08 0
91 669503494 30-OCT-08
92 669503532 30-OCT-08
93 669503537 30-OCT-08
94 669507033 30-OCT-08
95 669507037 30-OCT-08
96 669507656 30-OCT-08
97 669507895 30-OCT-08
98 669508179 30-OCT-08
99 669508185 30-OCT-08
100 669508210 30-OCT-08
101 669508215 30-OCT-08
102 669509706 30-OCT-08
103 669509710 30-OCT-08
104 669510725 30-OCT-08
105 669510729 30-OCT-08
106 669645074 01-NOV-08
107 669645077 01-NOV-08
108 669645085 01-NOV-08
109 669645088 01-NOV-08
110 669645622 01-NOV-08 0
111 669646213 01-NOV-08 0
112 669646640 01-NOV-08
113 669646643 01-NOV-08
114 669646651 01-NOV-08
115 669646654 01-NOV-08
116 669647173 01-NOV-08 0
117 669647495 01-NOV-08 0
118 669647764 01-NOV-08 0
119 669647775 01-NOV-08

column handle format a32
column tag format a18
select recid, set_stamp, tag, status, handle
from v$backup_piece
order by set_stamp
/

RECID SET_STAMP TAG STA HANDLE
———- ———- —————— — ——————————–
93 669465816 TAG20081030T104336 A +PS90HR_DISK3/ps90hrqa/autobacku
p/c-2291650026-20081030-03

94 669501607 TAG20081030T204007 A /dbbackup/PS90HRQA/al_t669501607
_s84_p1

95 669501610 TAG20081030T204010 A +PS90HR_DISK3/ps90hrqa/autobacku
p/c-2291650026-20081030-04

96 669502225 TAG20081030T205024 A +PS90HR_DISK2/ps90hrqa/backupset
/2008_10_30/nnsnf0_tag20081030t2
05024_0.572.669505825

97 669502227 TAG20081030T205027 A +PS90HR_DISK3/ps90hrqa/autobacku
p/c-2291650026-20081030-05

100 669502244 PS90HRQA A /opt/app/oracle/admin/backup/PS9
0HRQA/backupsets/df_t669502244_s
88_p1

101 669502244 PS90HRQA A /opt/app/oracle/admin/backup/PS9
0HRQA/backupsets/df_t669502244_s
88_p2

98 669502245 PS90HRQA A /opt/app/oracle/admin/backup/PS9
0HRQA/backupsets/df_t669502245_s
89_p1

Recover Database Using an Autobackup of the Controlfile without a Recovery Catalog

Set the NLS_LANG environment variable to its proper setting depending on the values you created your database with. Example:

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

sqlplus> startup nomount;

Start RMAN but do not connect to the target database.

$ rman
RMAN>

Set the database identifier for the target database with the SET DBID command. RMAN displays the DBID whenever you connect to the target. You can also get it by running LIST or by querying the catalog.

Recovery Manager: Release 11.1.0.6.0 – Production on Sat Nov 1 20:57:08 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: PS90HRDV (DBID=2173713801)

RMAN> SET DBID 2173713801;

You can use log files to determine the DBID also, every time RMAN connects to the database the DBID is displayed as shown above, if the database is open or mounted.

Connect to the target database:

RMAN> CONNECT TARGET;

Restore the backup controlfile, then perform the recovery by carrying out the following steps:

Optionally, specify the most recent backup time stamp that RMAN can use when searching for a controlfile autobackup to restore.

If a non-default format was used to create the control file, then specify a non-default format for the restore of the control file.

If the channel that created the control file autobackup was device type sbt (tape), then you must allocate one or more sbt (tape) channels. Because you don’t have a repository available you can’t use the automatic channels. If the autobackup was created on a disk channel, however, then you do not need to manually allocate a disk channel.

Restore the autobackup of the control file, optionally set the maximum number of days backward that RMAN can search (up to 366) and the initial sequence number that is should use in its search for the first day.

Mount the database, and because the repository is now available any automatic channels that you configured are now also available.

If the online logs are inaccessible, then restore and recover the database as described in the incomplete restore and recovery post. You must first terminate recovery by setting the UNTIL clause to a time, log sequence number or SCN before the online redo logs are processes. If the online logs are useable, then restore and recover the database as described in the complete restore and recovery post.

In the following example, the online redo logs have been lost. This example limits the restore of the control file autobackup, then performs the recovery of the database to log sequence 13456, which is the most recent archived log:

run {
set controlfile autobackup format for device type disk to ‘/opt/app/oracle/oradata/ORCLSID/backup/%F’;
allocate channel d1 device type disk;
restore controlfile from autobackup
maxseq 5 # start at sequence 5 and count down
maxdays 5; # start at UNTIL TIME and search back 5 days
mount database;
}

RMAN> run {
restore database;
recover database;
alter database open resetlogs;
}

Backup the database after resetlogs.

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:

1. 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.
2. 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.
3. The DBID of the target database has to be known
4. 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.
5. 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

1. 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.
2. 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.