Restore RMAN backup to another server (Disaster Recovery)

Restore RMAN Backup to another server with same database SID

I hope this would be useful when we have to do periodic testing of disaster recovery procedures where we simulate a scenario when the complete database server has crashed and a new server has been provisioned.

The directory structure on the new or target server could be different to that of the source server. So in this case the backup has been restored to the location “W:\oradata\fopsp\data\” from that on the source location ASM “+DATA”.

 

Pre requisition

The controlfile autobackup should be set up on Source database and the backup copy should be backed up after this setting.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'V:\fopsp\%F';

 

Restore the SPFILE

(on Windows Powershell)

PS H:\> dir env:
PS H:\> $env:ORACLE_SID='FOPSP'
PS H:\> $env:ORACLE_HOME='C:\oracle\product\11.2.0\dbhome_1'

PS H:\> rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 24 10:27:28 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database (not started)

RMAN> set DBID 1427950135
executing command: SET DBID

RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITFOPSP.ORA'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    3340603392 bytes

Fixed Size                     2259720 bytes
Variable Size               1040188664 bytes
Database Buffers            2281701376 bytes
Redo Buffers                  16453632 bytes

RMAN> run {
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'V:\dbbackup\oracle\fopsp\%F';
3> restore spfile from autobackup;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 24-AUG-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150824
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150823
channel ORA_DISK_1: AUTOBACKUP found: V:\dbbackup\oracle\fopsp\c-1427950135-20150823-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP V:\dbbackup\oracle\fopsp\c-1427950135-20150823-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 24-AUG-15

RMAN> exit
Recovery Manager complete.

 

Create PFILE from SPFILE and make parameter changes as required

PS H:\> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 24 10:57:38 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

10:57:38 fopsp> create pfile from spfile='C:\Oracle\product\11.2.0\dbhome_1\database\spfilefopsp.ora';
File created.
Elapsed: 00:00:00.10

 

Modify the PFILE “INITfopsp.ora” for the target database server’s directory structure, then create spfile again

10:57:38 fopsp> create spfile from pfile;
File created.
Elapsed: 00:00:00.10

 

Restore the Control files

RMAN> run {
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'V:\dbbackup\oracle\fopsp\%F';
3> restore CONTROLFILE from autobackup;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 24-AUG-15
using channel ORA_DISK_1

recovery area destination: V:\fast_recovery_area
database name (or database unique name) used for search: FOPSP
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150824
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150823
channel ORA_DISK_1: AUTOBACKUP found: V:\dbbackup\oracle\fopsp\c-1427950135-20150823-01
channel ORA_DISK_1: restoring control file from AUTOBACKUP V:\dbbackup\oracle\fopsp\c-1427950135-20150823-01
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=W:\ORADATA\FOPSP\CONTROLFILE\CONTROL01.CTL
Finished restore at 24-AUG-15

 

Mount the database and catalog backup pieces which have been restored in the new location

RMAN> alter database mount;
database mounted

RMAN> catalog start with 'V:\dbbackup\oracle\fopsp';
searching for all files that match the pattern V:\dbbackup\oracle\fopsp

List of Files Unknown to the Database
=====================================
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-00
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-01
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-02
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-03
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150823-00
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150823-01
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888407088_S336_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888428407_S338_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888450622_S340_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888494059_S342_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888514889_S344_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_FULL_AEQF756O_1_888378584

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-00
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-01
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-02
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150822-03
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150823-00
File Name: V:\DBBACKUP\ORACLE\fopsp\C-1427950135-20150823-01
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888407088_S336_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888428407_S338_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888450622_S340_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888494059_S342_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_ARCHLOG_T888514889_S344_P1
File Name: V:\DBBACKUP\ORACLE\fopsp\FOPSP_FULL_AEQF756O_1_888378584

Restore and recover the database

RMAN> RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK PARMS='V:\dbbackup\oracle\fopsp';
set newname for datafile 1 to 'W:\oradata\fopsp\data\SYSTEM01.DBF';
set newname for datafile 2 to 'W:\oradata\fopsp\data\SYSAUX01.DBF';
set newname for datafile 3 to 'Y:\oradata\fopsp\undo\UNDOTBS1_01.DBF';
set newname for datafile 4 to 'W:\oradata\fopsp\data\USERS01.DBF';
set newname for datafile 5 to 'X:\oradata\fopsp\index\FOPS_INDEX01.DBF';
set newname for datafile 6 to 'W:\oradata\fopsp\data\FOPS_DATA01.DBF';
restore database;
switch datafile all;
recover database;
release channel c1;
}

allocated channel: c1
channel c1: SID=156 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 24-AUG-15

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to W:\oradata\fopsp\data\SYSTEM01.DBF
channel c1: restoring datafile 00002 to W:\oradata\fopsp\data\SYSAUX01.DBF
channel c1: restoring datafile 00003 to Y:\oradata\fopsp\undo\UNDOTBS1_01.DBF
channel c1: restoring datafile 00004 to W:\oradata\fopsp\data\USERS01.DBF
channel c1: restoring datafile 00005 to X:\oradata\fopsp\index\FOPS_INDEX01.DBF
channel c1: restoring datafile 00006 to W:\oradata\fopsp\data\FOPS_DATA01.DBF
channel c1: reading from backup piece V:\DBBACKUP\ORACLE\FOPSP\FOPSP_FULL_AEQF756O_1_888378584
channel c1: piece handle=V:\DBBACKUP\ORACLE\FOPSP\FOPSP_FULL_AEQF756O_1_888378584 tag=WEEKLY_FULL_BACKUP
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:01:46
Finished restore at 24-AUG-15

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=888579636 file name=W:\ORADATA\FOPSP\DATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=888579636 file name=W:\ORADATA\FOPSP\DATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=888579636 file name=Y:\ORADATA\FOPSP\UNDO\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=888579636 file name=W:\ORADATA\FOPSP\DATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=888579636 file name=X:\ORADATA\FOPSP\INDEX\FOPS_INDEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=888579636 file name=W:\ORADATA\FOPSP\DATA\FOPS_DATA01.DBF

Starting recover at 24-AUG-15
starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=431
channel c1: reading from backup piece V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888407088_S336_P1
channel c1: piece handle=V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888407088_S336_P1 tag=ARCHIVELOG_LOG_BACKUP
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
archived log file name=Z:\ORADATA\FOPSP\ARCH\ARC0000000431_0881072057.0001 thread=1 sequence=431
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=432
channel c1: reading from backup piece V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888428407_S338_P1
channel c1: piece handle=V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888428407_S338_P1 tag=ARCHIVELOG_LOG_BACKUP
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
archived log file name=Z:\ORADATA\FOPSP\ARCH\ARC0000000432_0881072057.0001 thread=1 sequence=432
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=433
channel c1: reading from backup piece V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888450622_S340_P1
channel c1: piece handle=V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888450622_S340_P1 tag=ARCHIVELOG_LOG_BACKUP
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
archived log file name=Z:\ORADATA\FOPSP\ARCH\ARC0000000433_0881072057.0001 thread=1 sequence=433
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=434
channel c1: reading from backup piece V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888494059_S342_P1
channel c1: piece handle=V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888494059_S342_P1 tag=ARCHIVELOG_LOG_BACKUP
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
archived log file name=Z:\ORADATA\FOPSP\ARCH\ARC0000000434_0881072057.0001 thread=1 sequence=434
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=435
channel c1: restoring archived log
archived log thread=1 sequence=436
channel c1: reading from backup piece V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888514889_S344_P1
channel c1: piece handle=V:\DBBACKUP\ORACLE\FOPSP\FOPSP_ARCHLOG_T888514889_S344_P1 tag=ARCHIVELOG_LOG_BACKUP
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
archived log file name=Z:\ORADATA\FOPSP\ARCH\ARC0000000435_0881072057.0001 thread=1 sequence=435
archived log file name=Z:\ORADATA\FOPSP\ARCH\ARC0000000436_0881072057.0001 thread=1 sequence=436
unable to find archived log
archived log thread=1 sequence=437
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/24/2015 11:41:36
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 437 and starting SCN of 42985160
RMAN> exit
Recovery Manager complete.

 

Open the database with RESETLOGS

PS H:\> sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 24 11:42:18 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

select GROUP#, THREAD#, SEQUENCE#, MEMBERS, ARCHIVED, STATUS  from v$log;
GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1          1          1          1 NO  CURRENT
2          1          0          1 YES UNUSED
3          1          0          1 YES UNUSED

COL MEMBER FOR A70;
SELECT GROUP#,MEMBER,IS_RECOVERY_DEST_FILE FROM V$LOGFILE;
GROUP# MEMBER                                                                 IS_
---------- ---------------------------------------------------------------------- ---
3 +DATA/fopsp/onlinelog/group_3.263.881072063                            NO
2 +DATA/fopsp/onlinelog/group_2.262.881072061                            NO
1 +DATA/fopsp/onlinelog/group_1.261.881072057                            NO

ALTER DATABASE RENAME FILE '+DATA/fopsp/onlinelog/group_1.261.881072057' TO 'W:\oradata\fopsp\redologs\REDO01.LOG';
ALTER DATABASE RENAME FILE '+DATA/fopsp/onlinelog/group_2.262.881072061' TO 'W:\oradata\fopsp\redologs\REDO02.LOG';
ALTER DATABASE RENAME FILE '+DATA/fopsp/onlinelog/group_3.263.881072063' TO 'W:\oradata\fopsp\redologs\REDO03.LOG';
12:07:08 fopsp> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ----------
CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME
----------------------------------------------------------------------------------------------------
1           897207 30-MAY-15          3          1 ONLINE  READ WRITE          0          0
20971520       8192
+DATA/fopsp/tempfile/temp.264.881072079

12:08:24 fopsp> alter database rename file '+DATA/fopsp/tempfile/temp.264.881072079' to 'Y:\oradata\fopsp\temp\TEMP01.DBF';
Database altered.

11:42:24 fopsp> alter database open resetlogs;
Database altered.
Elapsed: 00:00:32.30