Recovering from a cold backup using RMAN

These are  basic notes that I am using when taking copies of databases that are being used for performance testing work and on which we will be performing regular regressions. I prefer writing  to disk if we have sufficient space as no time is lost in waiting for tape drive availability and  also know that I don’t have to worry about being past the retention data and the backup being deleted. There are 4 databases involved in this test and I prefer to close them all down, startup mount, backup to disk and then make them all available again.

It is a good habit to create the recovery script at the same time as the backup is created and leave a copy in the same directory as the RMAN backup pieces as it aids recovery. Both the channel command and the use of the TAG command both help in this.

Technically this is not a cold backup as the database is open, although not in use, which is why a resetlogs is required at the end. A proper cold backup is to shut the database down and take image copies of the database files and replace them as part of a restore.

Backup the database when it is in mount state

RMAN>

run
{
allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
allocate channel c2 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
backup full
tag rman_backfulcold_MOMPREPERTEST
(database include current controlfile);
}

Restore the database

startup nomount

run
{
allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
restore controlfile from ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_37_P_1_T_713884663′;
}

alter database mount;

run
{
allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
allocate channel c2 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
restore database tag=’rman_backfulcold_MOMPREPERTEST’;
}

–YOU DO NOT NEED TO RECOVER DATABASE otherwise it rolls forward to the current time

alter database open resetlogs;

Logs for both scripts are shown below for reference

Backup first
connected to target database: MOMPRE1A (DBID=2948022450, not open)
using target database control file instead of recovery catalog

RMAN> run
{
allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
allocate channel c2 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
backup full
tag rman_backfulcold_MOMPREPERTEST
(database include current controlfile);
}
allocated channel: c1
channel c1: SID=2174 instance=MOMPRE1A1 device type=DISK
allocated channel: c2
channel c2: SID=2173 instance=MOMPRE1A1 device type=DISK

Starting backup at 2010-03-17:13:16:57
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/mompre1a/datafile/undotbs1.261.709383045
input datafile file number=00007 name=+DATA/mompre1a/datafile/retek_data.288.709494575
input datafile file number=00009 name=+DATA/mompre1a/datafile/momstg.418.710095281
input datafile file number=00011 name=+DATA/mompre1a/datafile/momtrns.420.710095303
input datafile file number=00001 name=+DATA/mompre1a/datafile/system.259.709383033
input datafile file number=00008 name=+DATA/mompre1a/datafile/lob_data.289.709494583

channel c1: starting piece 1 at 2010-03-17:13:16:58
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/mompre1a/datafile/undotbs2.263.709383069
input datafile file number=00006 name=+DATA/mompre1a/datafile/retek_index.287.709494567
input datafile file number=00002 name=+DATA/mompre1a/datafile/sysaux.260.709383037
input datafile file number=00010 name=+DATA/mompre1a/datafile/momclns.419.710095295
input datafile file number=00012 name=+DATA/mompre1a/datafile/owbsys.421.710095313
input datafile file number=00005 name=+DATA/mompre1a/datafile/users.264.709383085
channel c2: starting piece 1 at 2010-03-17:13:16:58
channel c1: finished piece 1 at 2010-03-17:13:17:43
piece handle=/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_35_P_1_T_713884618 tag=RMAN_BACKFULCOLD_MOMPREPERTEST comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
channel c2: finished piece 1 at 2010-03-17:13:17:43
piece handle=/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_36_P_1_T_713884618 tag=RMAN_BACKFULCOLD_MOMPREPERTEST comment=NONE
channel c2: backup set complete, elapsed time: 00:00:45
including current control file in backup set
channel c1: starting piece 1 at 2010-03-17:13:17:44
channel c1: finished piece 1 at 2010-03-17:13:17:45
piece handle=/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_37_P_1_T_713884663 tag=RMAN_BACKFULCOLD_MOMPREPERTEST comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2010-03-17:13:17:45

Starting Control File and SPFILE Autobackup at 2010-03-17:13:17:45
piece handle=+FRA/mompre1a/autobackup/2010_03_17/s_713884325.1849.713884667 comment=NONE
Finished Control File and SPFILE Autobackup at 2010-03-17:13:17:46
released channel: c1
released channel: c2
RMAN> exit
Restore

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2128056 bytes
Variable Size               3154118472 bytes
Database Buffers            1107296256 bytes
Redo Buffers                  12238848 bytes

RMAN> run
{
allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
allocate channel c2 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
restore controlfile from autobackup;
}

allocated channel: c1
channel c1: SID=2174 instance=MOMPRE1A1 device type=DISK

allocated channel: c2
channel c2: SID=2173 instance=MOMPRE1A1 device type=DISK

Starting restore at 2010-03-19:14:59:42

recovery area destination: +FRA
database name (or database unique name) used for search: MOMPRE1A
channel c1: AUTOBACKUP +fra/MOMPRE1A/AUTOBACKUP/2010_03_17/s_713884325.1849.713884667 found in the recovery area
channel c1: looking for AUTOBACKUP on day: 20100319
channel c1: looking for AUTOBACKUP on day: 20100318
channel c1: looking for AUTOBACKUP on day: 20100317
recovery area destination: +FRA
database name (or database unique name) used for search: MOMPRE1A
channel c2: AUTOBACKUP +fra/MOMPRE1A/AUTOBACKUP/2010_03_17/s_713884325.1849.713884667 found in the recovery area
channel c2: looking for AUTOBACKUP on day: 20100319
channel c2: looking for AUTOBACKUP on day: 20100318
channel c2: looking for AUTOBACKUP on day: 20100317
channel c2: skipped, AUTOBACKUP already found
channel c1: restoring control file from AUTOBACKUP +fra/MOMPRE1A/AUTOBACKUP/2010_03_17/s_713884325.1849.713884667
channel c1: control file restore from AUTOBACKUP complete
output file name=+DATA/mompre1a/controlfile/current.256.709383031
output file name=+FRA/mompre1a/controlfile/current.256.709383031
Finished restore at 2010-03-19:14:59:52
released channel: c1
released channel: c2

RMAN> alter database mount;

database mounted

RMAN> run
{
allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
allocate channel c2 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
restore database;
}

allocated channel: c1
channel c1: SID=2173 instance=MOMPRE1A1 device type=DISK

allocated channel: c2
channel c2: SID=2174 instance=MOMPRE1A1 device type=DISK

Starting restore at 2010-03-19:15:00:49
Starting implicit crosscheck backup at 2010-03-19:15:00:49
Crosschecked 25 objects
Crosschecked 8 objects
Finished implicit crosscheck backup at 2010-03-19:15:00:53

Starting implicit crosscheck copy at 2010-03-19:15:00:53
Finished implicit crosscheck copy at 2010-03-19:15:00:53

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: +fra/MOMPRE1A/AUTOBACKUP/2010_03_17/s_713884325.1849.713884667
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_03_18/thread_1_seq_587.1872.713924465
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_03_17/thread_2_seq_588.1870.713919217
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_03_17/thread_1_seq_586.1871.713919217
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_01_27/thread_1_seq_19.261.709385869
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_01_27/thread_2_seq_1.262.709385899
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_01_27/thread_2_seq_2.263.709385901
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_01_27/thread_2_seq_3.264.709385901
File Name: +fra/MOMPRE1A/ARCHIVELOG/2010_01_27/thread_1_seq_20.265.709385903
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to +DATA/mompre1a/datafile/system.259.709383033
channel c1: restoring datafile 00003 to +DATA/mompre1a/datafile/undotbs1.261.709383045
channel c1: restoring datafile 00007 to +DATA/mompre1a/datafile/retek_data.288.709494575
channel c1: restoring datafile 00008 to +DATA/mompre1a/datafile/lob_data.289.709494583
channel c1: restoring datafile 00009 to +DATA/mompre1a/datafile/momstg.418.710095281
channel c1: restoring datafile 00011 to +DATA/mompre1a/datafile/momtrns.420.710095303
channel c1: reading from backup piece /staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_35_P_1_T_713884618
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00002 to +DATA/mompre1a/datafile/sysaux.260.709383037
channel c2: restoring datafile 00004 to +DATA/mompre1a/datafile/undotbs2.263.709383069
channel c2: restoring datafile 00005 to +DATA/mompre1a/datafile/users.264.709383085
channel c2: restoring datafile 00006 to +DATA/mompre1a/datafile/retek_index.287.709494567
channel c2: restoring datafile 00010 to +DATA/mompre1a/datafile/momclns.419.710095295
channel c2: restoring datafile 00012 to +DATA/mompre1a/datafile/owbsys.421.710095313
channel c2: reading from backup piece /staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_36_P_1_T_713884618
channel c1: piece handle=/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_35_P_1_T_713884618 tag=RMAN_BACKFULCOLD_MOMPREPERTEST
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:01:45
channel c2: piece handle=/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_36_P_1_T_713884618 tag=RMAN_BACKFULCOLD_MOMPREPERTEST
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:01:45
Finished restore at 2010-03-19:15:02:44
released channel: c1
released channel: c2

RMAN> alter database open;

RMAN> alter database open RESETLOGS;