Step By Step Process To Create Physical Standby Database Using RMAN

Step By Step Process To Create Physical Standby Database Using RMAN

Hi,
I got many mails to post some steps for implementing the Standby Database in a real time environment.This exercise I have completed few years back but didn’t posted in Blog.For implementing the Standby Database using RMAN follow
The below steps:

Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.1.0
Reference:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmstand.htm

Summary of Steps:
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

As a primary responsibility of DBA ,please make sure you backup controlfile,Datafiles & archivelogs .In the below example we will make use of this backup for creating Standy Database.


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

C:\>rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Feb 4 12:28:47 2009

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

connected to target database: TESTDB (DBID=2606456859)

RMAN> run
2> {
3> allocate channel c1 type disk format ‘\\node1.in.Test.com\TESTDB_Backup\%d_DATA_%U_%T';
4> allocate channel c2 type disk format '\\node1.in.Test.com\TESTDB_Backup\%d_DATA_%U_%T';
5> allocate channel c3 type disk format '\\node1.in.Test.com\TESTDB_Backup\%d_DATA_%U_%T';
6> backup database plus archivelog;
7> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=615 instance=TESTDB1 devtype =DISK

allocated channel: c2
channel c2: sid=596 instance=TESTDB1 devtype =DISK

allocated channel: c3
channel c3: sid=619 instance=TESTDB1 devtype =DISK


Starting backup at 04-FEB-09
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=2 sequence=2855 recid=7974 stamp=677037473
input archive log thread=2 sequence=2856 recid=7978 stamp=677070799

…………………………….
…………………………….
…………………………….
…………………………….

channel c3: backup set complete, elapsed time: 00:01:06
Finished backup at 04-FEB-09

Starting backup at 04-FEB-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00004 name=H:\ORADATA\TESTDB\UNDOTBS02.DBF
input datafile fno=00008 name=H:\ORADATA\TESTDB\COMET01.DBF
input datafile fno=00006 name=H:\ORADATA\TESTDB\PHASER01.DBF
input datafile fno=00001 name=H:\ORADATA\TESTDB\SYSTEM01.DBF
input datafile fno=00016 name=H:\ORADATA\TESTDB\HDK_TBS_01.DBF
input datafile fno=00014 name=H:\ORADATA\TESTDB\LCS_TBS_01
input datafile fno=00011 name=H:\ORADATA\TESTDB\COMET04.DBF
channel c1: starting piece 1 at 04-FEB-09
channel c2: starting full datafile backupset
…………………………….
…………………………….
…………………………….
…………………………….
input archive log thread=1 sequence=4872 recid=8154 stamp=677957862
channel c2: starting piece 1 at 04-FEB-09
channel c1: finished piece 1 at 04-FEB-09
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D1K6HK7D_1_1_20090204 tag=TAG20090204T173748 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:36
channel c2: finished piece 1 at 04-FEB-09
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D2K6HK7D_1_1_20090204 tag=TAG20090204T173748 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:36
Finished backup at 04-FEB-09

Starting Control File and SPFILE Autobackup at 04-FEB-09
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\C-2606456859-20090204-00 comment=NONE
Finished Control File and SPFILE Autobackup at 04-FEB-09
released channel: c1
released channel: c2
released channel: c3

RMAN>
RMAN> run
2> {
3> allocate channel c1 type disk format '\\node1.in.Test.com\TESTDB_Backup\%d_CTRL_%U_%T';
4> backup current controlfile for standby;
5> }

allocated channel: c1
channel c1: sid=615 instance=TESTDB1 devtyTEST=DISK

Starting backup at 04-FEB-09
channel c1: starting full datafile backupset
channel c1: sTESTcifying datafile(s) in backupset
including standby control file in backupset
channel c1: starting piece 1 at 04-FEB-09
channel c1: finished piece 1 at 04-FEB-09
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_CTRL_D4K6HOIR_1_1_20090204 tag=TAG20090204T185211 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:08
Finished backup at 04-FEB-09

Starting Control File and SPFILE Autobackup at 04-FEB-09
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\C-2606456859-20090204-01 comment=NONE
Finished Control File and SPFILE Autobackup at 04-FEB-09
released channel: c1

RMAN> exit

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.
+ If the backups are on tape device then make sure that you make proper changes on standby server so that you can restore the backups on standby server.

Note 1: NFS configuration is a relatively straightforward process. The processes that need to be running can all start at boot time with a few modifications to your /etc/rc.conf file.
Note 2:Add the entry in /etc/exports file in Linux.Study the below link for details.In solaris same process is followed by specifying the entry in /etc/dfs/dfstab and in Windows server you need to shared the folder by going to the properties.
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/network-nfs.html


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 :
Create pfile as follows (copy the pfile from primary and do changes in db_unique_name, instance_name, db_file_name_convert, log_file_name_convert and standby_archive_dest.)

db_name = TESTDB
instance_name = TESTcluster
db_files = 1024
db_file_multiblock_read_count = 8
db_block_size = 8192
control_files = F:\ORADATA\TESTDB\STDCONTROL.CTL
sga_max_size = 1288490188
sga_target = 1073741824
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 250
parallel_max_servers = 5
log_archive_format = ARC%S_%R.%T
global_names = true
undo_management = AUTO
undo_tablespace = UNDOTBS1
thread = 1
db_block_size = 8192
remote_login_passwordfile = exclusive
compatible = 10.2.0.3
sort_area_size = 66560
open_cursors = 300
audit_file_dest = C:\oracle10g\product\10.2.0\db_1\admin\TESTDB\ADUMP
background_dump_dest = C:\oracle10g\product\10.2.0\db_1\admin\TESTDB\BDUMP
user_dump_dest = C:\oracle10g\product\10.2.0\db_1\admin\TESTDB\UDUMP
core_dump_dest = C:\oracle10g\product\10.2.0\db_1\admin\TESTDB\CDUMP
LOG_ARCHIVE_DEST_1 = 'LOCATION=F:\oradata\TESTDB\arch'
DB_FILE_NAME_CONVERT = ('H:\ORADATA\TESTDB','F:\ORADATA\TESTDB')
LOG_FILE_NAME_CONVERT = ('I:\ORADATA\TESTDB','F:\oradata\TESTDB', 'J:\ORADATA\TESTDB','F:\oradata\TESTDB')
DB_UNIQUE_NAME = TESTDB
STANDBY_FILE_MANAGEMENT = AUTO
STANDBY_ARCHIVE_DEST ='F:\oradata\TESTDB\arch'


Step 4: Create instance

On windows use following command to create instance
D:\>oradim -NEW TESTcluster -STARTMODE auto -SYSPWD sys

On LINIX or UNIX just set the ORACLE_SID

Step 5: Create password file

Use the following command to create password file
D:\>orapwd file="path of the database folder" password=

Step 6: Do the restore and recover on standby database
Connect to target database and startup using pfile with nomount option.

C:\WINDOWS\system32>set ORACLE_SID=TESTCLUSTER
C:\WINDOWS\system32>rman target sys

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Feb 4 18:54:41 2009

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

target database Password:
connected to target database (not started)

RMAN> set dbid=2606456859;

executing command: SET DBID

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1291845632 bytes

Fixed Size 1292276 bytes
Variable Size 494929932 bytes
Database Buffers 788529152 bytes
Redo Buffers 7094272 bytes

RMAN>
RMAN> restore standby controlfile from '\\node1\TESTDB_Backup\TESTDB_CTRL_D4K6HOIR_1_1_20090204';

Starting restore at 04-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=267 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output filename=F:\ORADATA\TESTDB\STDCONTROL.CTL
Finished restore at 04-FEB-09

RMAN> sql alter database mount standby database’;

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 04-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=263 device type=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: sTESTcifying datafile(s) to restore from backup set
restoring datafile 00002 to F:\ORADATA\TESTDB\UNDOTBS01.DBF
restoring datafile 00007 to F:\ORADATA\TESTDB\RMSUSR01.DBF
restoring datafile 00010 to F:\ORADATA\TESTDB\COMET03.DBF
restoring datafile 00013 to F:\ORADATA\TESTDB\SCADA_TBS01.DBF
restoring datafile 00015 to F:\ORADATA\TESTDB\TEST_TRACE01.DBF
restoring datafile 00017 to F:\ORADATA\TESTDB\INDX_PHASE_01.DBF
restoring datafile 00018 to F:\ORADATA\TESTDB\INDX_EDITOR_01.DBF
channel ORA_DISK_1: reading from backup piece \\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_CVK6H488_1_1_20090204
channel ORA_DISK_1: restored backup piece 1
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_CVK6H488_1_1_20090204 tag=TAG20090204T130511
channel ORA_DISK_1: restore complete, elapsed time: 00:23:57
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to F:\ORADATA\TESTDB\SYSAUX01.DBF
restoring datafile 00005 to F:\ORADATA\TESTDB\USERS01.DBF
restoring datafile 00009 to F:\ORADATA\TESTDB\COMET02.DBF
restoring datafile 00012 to F:\ORADATA\TESTDB\EDITOR_TBS01.DBF
restoring datafile 00019 to F:\ORADATA\TESTDB\INDX_COMET_01.DBF
restoring datafile 00020 to F:\ORADATA\TESTDB\AUTOCALPF.DBF
restoring datafile 00021 to F:\ORADATA\TESTDB\AUTOCALPF_INDEX.DBF
channel ORA_DISK_1: reading from backup piece \\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D0K6H488_1_1_20090204
channel ORA_DISK_1: restored backup piece 1
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D0K6H488_1_1_20090204 tag=TAG20090204T130511
channel ORA_DISK_1: restore complete, elapsed time: 00:28:45
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORADATA\TESTDB\SYSTEM01.DBF
restoring datafile 00004 to F:\ORADATA\TESTDB\UNDOTBS02.DBF
restoring datafile 00006 to F:\ORADATA\TESTDB\PHASER01.DBF
restoring datafile 00008 to F:\ORADATA\TESTDB\COMET01.DBF
restoring datafile 00011 to F:\ORADATA\TESTDB\COMET04.DBF
restoring datafile 00014 to F:\ORADATA\TESTDB\LCS_TBS_01
restoring datafile 00016 to F:\ORADATA\TESTDB\HDK_TBS_01.DBF
channel ORA_DISK_1: reading from backup piece \\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_CUK6H488_1_1_20090204
channel ORA_DISK_1: restored backup piece 1
piece handle=\\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_CUK6H488_1_1_20090204 tag=TAG20090204T130511
channel ORA_DISK_1: restore complete, elapsed time: 00:37:46
Finished restore at 04-FEB-09

Step 7: Recover till the current SCN

Check the backed up archivelogs and recover standby database till that SCN

RMAN> list backup of archivelog all;
……………………………………………………………
……………………………………………………………
……………………………………………………………
……………………………………………………………

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2389 38.81M DISK 00:00:34 04-FEB-09
BP Key: 3706 Status: AVAILABLE Compressed: NO Tag: TAG20090204T173748
Piece Name: \\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D2K6HK7D_1_1_20090204

List of Archived Logs in backup set 2389
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4872 220387955 04-FEB-09 220524445 04-FEB-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2390 46.58M DISK 00:00:35 04-FEB-09
BP Key: 3707 Status: AVAILABLE Compressed: NO Tag: TAG20090204T173748
Piece Name: \\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D1K6HK7D_1_1_20090204

List of Archived Logs in backup set 2390
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
2 2907 220387972 04-FEB-09 220524442 04-FEB-09



“it list all the backed up archives”
“check the highest SCN backed up and give till next SCN number for recovery as follows”

RMAN> recover database until sequence 4873;

Starting recover at 05-FEB-09
using channel ORA_DISK_1

starting media recovery

archive log thread 2 sequence 2908 is already on disk as file F:\ORADATA\TESTDB\ARCH\ARC02908_0643721627.002
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4872
channel ORA_DISK_1: reading from backup piece \\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D2K6HK7D_1_1_20090204
ORA-19870: error reading backup piece \\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D2K6HK7D_1_1_20090204
ORA-19505: failed to identify file "\\NODE1.IN.TEST.COM\TESTDB_BACKUP\TESTDB_DATA_D2K6HK7D_1_1_20090204"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
failover to previous backup
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OTESTN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORADATA\TESTDB\SYSTEM01.DBF'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/05/2009 11:00:30
RMAN-20506: no backup of archivelog found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 4872 lowscn 220387955 found to restore

RMAN> exit


Note : No need to worry about the errors, you can safely ignore and move to step 7
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OTESTN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORADATA\TESTDB\SYSTEM01.DBF'



Step 8: Put the standby database in recover managed mode

C:\WINDOWS\system32>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 5 11:05:21 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Here is your Standby Database ready use it when ever you want to switch the role whenever there is a requirement to do some critical changes in primary or production make your standby as primary and to implement the fast start fail over where something happens to primary than standby will
Automatically become primary.

In my upcoming posts,I will explain few scenarios on failover and switchover cases and useful information for working with Dataguard and Standby Databases.

Hope it helps.