Step by step guide on how to create a physical standby database using RMAN without using duplicate command

Step by step guide on how to create a physical standby database using RMAN without using duplicate command

Below are the steps to accomplish the task :

Step 1: Backup the database that includes backup of datafiles, archivelogs and controlfile for standby
Step 2: Move the backups to the standby server
Step 3: Make proper changes in the parameter files of both primary and standby database
Step 4: Do the restore and recover on standby database
Step 5: Put the standby database in recover managed mode

Example :

Step 1: Backup the primary database that includes backup of datafiles, archivelogs and controlfile for standby

[oracle@test-br ~]$ rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Dec 9 14:56:49 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=3959250272)
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate channel c3 type disk;
6> backup database plus archivelog;
7> }

using target database control file instead of recovery catalog

allocated channel: c1
channel c1: sid=159 devtype=DISK
allocated channel: c2
channel c2: sid=138 devtype=DISK
allocated channel: c3
channel c3: sid=137 devtype=DISK
Starting backup at 09:DEC:2011:14:57:27
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=32 recid=44 stamp=640387774
input archive log thread=1 sequence=33 recid=45 stamp=640387945
input archive log thread=1 sequence=34 recid=48 stamp=640668312
channel c1: starting piece 1 at 09:DEC:2011:14:57:32
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=35 recid=50 stamp=640882651
channel c2: starting piece 1 at 09:DEC:2011:14:57:32
channel c3: starting archive log backupset
channel c3: specifying archive log(s) in backup set
input archive log thread=1 sequence=20 recid=20 stamp=640385760
input archive log thread=1 sequence=21 recid=21 stamp=640385772
input archive log thread=1 sequence=22 recid=22 stamp=640385775
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c3: finished piece 1 at 09:DEC:2011:14:57:36
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c3: backup set complete, elapsed time: 00:00:05
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=26 recid=26 stamp=640386253
input archive log thread=1 sequence=27 recid=27 stamp=640386256
input archive log thread=1 sequence=28 recid=36 stamp=640387757
input archive log thread=1 sequence=29 recid=37 stamp=640387764
input archive log thread=1 sequence=30 recid=40 stamp=640387769
input archive log thread=1 sequence=31 recid=41 stamp=640387771
channel c2: starting piece 1 at 09:DEC:2011:14:57:37
channel c2: finished piece 1 at 09:DEC:2011:14:57:40
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c1: finished piece 1 at 09:DEC:2011:14:57:41
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145731_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:10
Finished backup at 09:DEC:2011:14:57:41
Starting backup at 09:DEC:2011:14:57:41
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/oracle/product/oradata/PROD/data/undo01.dbf
input datafile fno=00003 name=/u01/oracle/product/oradata/PROD/data/sysaux01.dbf
channel c1: starting piece 1 at 09:DEC:2011:14:57:41
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oracle/product/oradata/PROD/data/sys01.dbf
input datafile fno=00004 name=/u01/oracle/product/oradata/PROD/data/rman.dbf
channel c2: starting piece 1 at 09:DEC:2011:14:57:41
channel c3: starting full datafile backupset
channel c3: specifying datafile(s) in backupset
including current control file in backupset
channel c3: starting piece 1 at 09:DEC:2011:14:57:45
channel c3: finished piece 1 at 09:DEC:2011:14:57:52
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T145741_comment=NONE
channel c3: backup set complete, elapsed time: 00:00:11
channel c3: starting full datafile backupset
channel c3: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c3: starting piece 1 at 09:DEC:2011:14:57:53
channel c3: finished piece 1 at 09:DEC:2011:14:57:56
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnsnf_TAG20111209T145741_comment=NONE
channel c3: backup set complete, elapsed time: 00:00:04
channel c1: finished piece 1 at 09:DEC:2011:14:58:31
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:50
channel c2: finished piece 1 at 09:DEC:2011:14:58:31
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_comment=NONE
channel c2: backup set complete, elapsed time: 00:00:50
Finished backup at 09:DEC:2011:14:58:31
Starting backup at 09:DEC:2011:14:58:31
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=36 recid=51 stamp=640882711
channel c1: starting piece 1 at 09:DEC:2011:14:58:32
channel c1: finished piece 1 at 09:DEC:2011:14:58:33
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 09:DEC:2011:14:58:33
released channel: c1
released channel: c2
released channel: c3

Backup controlfile for standby 

RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup current controlfile for standby;
5> }
allocated channel: c1
channel c1: sid=159 devtype=DISK
Starting backup at 09:DEC:2011:15:00:09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including standby control file in backupset
channel c1: starting piece 1 at 09:DEC:2011:15:00:10
channel c1: finished piece 1 at 09:DEC:2011:15:00:11
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T150009_comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 09:DEC:2011:15:00:11
released channel: c1

Step 2: Move the backups to the standby server

+ FTP or SCP the backup pieces.
+ If the backups are on NFS mount then mount the NFS on standby server with the same name as you mounted on primary database.
+ Until 9i, you need to move the backup-pieces in exactly the same location on standby as they were created on primary.
+ From 10g onwards, use CATALOG BACKUPPIECE command if you are moving backup-pieces to a different location.
+ If the backups are on tape then make sure that you make proper changes on standby server so that you can restore the backups on standby server.

Step 3: Make proper changes in the parameter files of both primary and standby database

Add the below parameter in primary database parameter file :
log_archive_dest_2='SERVICE=STANDBY'

Add the below parameters in standby database parameter file :

Copy the primary database parameter file and make necessary changes :
db_unique_name='standby'
instance_name='standby'
db_file_name_convert='/u01/oracle/product/oradata/PROD/data/','/u01/oracle/product/PROD/oradata/standby/data/','/log_file_name_convert='/u01/oracle/product/oradata/PROD/log/','/u01/oracle/product/PROD/oradata/standby/log'
standby_archive_dest='/u01/oracle/product/PROD/oradata/standby/arch1'

Step 4: Do the restore and recover on standby database

Note : After the restore try to identify the last archivelog sequence that is backed up and add 1 to it and do the recovery using it. In the below example the last archivelog that is backed up is sequence 36

[oracle@test-br ~]$ export ORACLE_SID=standby

[oracle@test-br ~]$ rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Dec 9 15:18:28 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 603979776 bytes
Fixed Size 1262224 bytes
Variable Size 163581296 bytes
Database Buffers 432013312 bytes
Redo Buffers 7122944 bytes
RMAN> set dbid=3959250272;  -- From primary database 
executing command: SET DBID
For 9i to restore controlfile you need to use the below command :
RMAN> restore controlfile from '/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T150009_3oqff2fb_.bkp';
From 10g onwards you need to use the below command to restore controlfile :
RMAN> restore standby controlfile from '/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_ncnnf_TAG20111209T150009_3oqff2fb_.bkp';
Starting restore at 09:DEC:2011:15:19:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oracle/product/PROD/oradata/standby/control01.ctl
output filename=/u01/oracle/product/PROD/oradata/standby/control02.ctl
output filename=/u01/oracle/product/PROD/oradata/standby/control03.ctl
Finished restore at 09:DEC:2011:15:19:53
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1

Restore Database

RMAN> restore database;

Starting restore at 09:DEC:2011:15:20:18
Starting implicit crosscheck backup at 09:DEC:2011:15:20:18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 09:DEC:2011:15:20:20
Starting implicit crosscheck copy at 09:DEC:2011:15:20:20
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 09:DEC:2011:15:20:20
searching for all files in the recovery area
cataloging files…
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/oracle/product/PROD/oradata/standby/data/undo01.dbf
restoring datafile 00003 to /u01/oracle/product/PROD/oradata/standby/data/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fk4_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fk4_.bkp tag=TAG20111209T145741
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/product/PROD/oradata/standby/data/sys01.dbf
restoring datafile 00004 to /u01/oracle/product/PROD/oradata/standby/data/rman.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fod_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_nnndf_TAG20111209T145741_3oqf8fod_.bkp tag=TAG20111209T145741
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 09:DEC:2011:15:21:21
The below command will list all the archivelogs which are backed up and from this list we need to identify the maximum sequence for recovery.

RMAN> list backup of archivelog all;

List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— ——————–
4 65.50K DISK 00:00:01 03:DEC:2011:20:10:18
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20111203T201017
Piece Name: /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_03/o1_mf_annnn_TAG20111203T201017_3o85blm6_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——————– ———- ———
1 9 272579 03:DEC:2011:20:08:38 272631 03:DEC:2011:20:10:16
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— ——————–
5 688.80M DISK 00:00:48 03:DEC:2011:20:12:22
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20111203T201133
Piece Name: /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_03/o1_mf_annnn_TAG20111203T201133_3o85f0hl_.bkp
List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——————– ———- ———
1 1 1 03:DEC:2011:18:49:10 66282 03:DEC:2011:18:54:55
1 2 66282 03:DEC:2011:18:54:55 100553 03:DEC:2011:18:56:09
1 3 100553 03:DEC:2011:18:56:09 131293 03:DEC:2011:18:57:52
1 4 131293 03:DEC:2011:18:57:52 168479 03:DEC:2011:19:05:44
1 5 168479 03:DEC:2011:19:05:44 210171 03:DEC:2011:19:06:50
1 6 210171 03:DEC:2011:19:06:50 240733 03:DEC:2011:19:08:25
1 7 240733 03:DEC:2011:19:08:25 266780 03:DEC:2011:19:10:40
1 8 266780 03:DEC:2011:19:10:40 272579 03:DEC:2011:20:08:38
1 9 272579 03:DEC:2011:20:08:38 272631 03:DEC:2011:20:10:16
1 10 272631 03:DEC:2011:20:10:16 272683 03:DEC:2011:20:10:42
1 11 272683 03:DEC:2011:20:10:42 272706 03:DEC:2011:20:10:54
1 12 272706 03:DEC:2011:20:10:54 272724 03:DEC:2011:20:10:57
1 13 272724 03:DEC:2011:20:10:57 272744 03:DEC:2011:20:11:00
1 14 272744 03:DEC:2011:20:11:00 272763 03:DEC:2011:20:11:03
1 15 272763 03:DEC:2011:20:11:03 272782 03:DEC:2011:20:11:06
1 16 272782 03:DEC:2011:20:11:06 272802 03:DEC:2011:20:11:09
1 17 272802 03:DEC:2011:20:11:09 272821 03:DEC:2011:20:11:12
1 18 272821 03:DEC:2011:20:11:12 272840 03:DEC:2011:20:11:15
1 19 272840 03:DEC:2011:20:11:15 272861 03:DEC:2011:20:11:33
List of Archived Logs in backup set 10
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——————– ———- ———
1 32 275335 03:DEC:2011:21:29:31 275338 03:DEC:2011:21:29:34
1 33 275338 03:DEC:2011:21:29:34 275545 03:DEC:2011:21:32:25
1 34 275545 03:DEC:2011:21:32:25 400351 07:DEC:2011:03:25:09
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— ——————–
15 2.00K DISK 00:00:01 09:DEC:2011:14:58:32
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20111209T145831
Piece Name: /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_3oqfb0p6_.bkp
List of Archived Logs in backup set 15
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——————– ———- ———
1 36 489499 09:DEC:2011:14:57:27 489527 09:DEC:2011:14:58:31
RMAN> recover database until sequence 37;  -- or just "recover database"
Starting recover at 09:DEC:2011:15:22:38
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=36
channel ORA_DISK_1: reading from backup piece /u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_3oqfb0p6_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/product/PROD/flash_recovery_area/PROD/backupset/2011_12_09/o1_mf_annnn_TAG20111209T145831_3oqfb0p6_.bkp tag=TAG20111209T145831
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/oracle/product/PROD/flash_recovery_area/ABC/archivelog/2011_12_09/o1_mf_1_36_3oqgq81h_.arc thread=1 sequence=36
channel default: deleting archive log(s)
archive log filename=/u01/oracle/product/PROD/flash_recovery_area/ABC/archivelog/2011_12_09/o1_mf_1_36_3oqgq81h_.arc recid=1 stamp=640884160
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/u01/oracle/product/PROD/oradata/standby/data/sys01.dbf’
media recovery complete, elapsed time: 00:00:00
Finished recover at 09:DEC:2011:15:22:41
RMAN> exit
Recovery Manager complete.

Note : No need to worry about the errors, you can safely ignore and move to step 5.
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/u01/oracle/product/PROD/oradata/standby/data/sys01.dbf’

Step 5: Put the standby database in recover managed mode

After ensuring connectivity from primary to standby using the service specified in LOG_ARCHIVE_DEST_2 parameter of primary and creating a password file on standby with the same sys password as primary,
[oracle@test-br ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 10.2.0.2.0 – Production on Sun Dec 9 15:49:26 2011
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database recover managed standby database disconnect from session;
Database altered.