How to create Physical Standby (DR) database using rman duplicate for standby method step by step
Primary :Server : prim-server
DB Name : DC
DB_UNIQUE_NAME : DC
Standby :
Server : stand-server
DB Name : DC
DB_UNIQUE_NAME : DR
Note:
1. Your primary database must be started with spfile and standby database must be started with pfile.
2. Your both primary and standby pfile, spfile, and password file name should be same on both primary and standby database.
3. Set below environment on standby server before starting it in nomount state.
export ORACLE_SID=DC
export ORACLE_HOME=
export PATH=$ORACLE_HOME/bin:$PATH
I had multiple issues while configuring networking between two servers primary and standby and hence you use this document as it is. For DB_NAME also, there can be problem. Use either capital letters or small letters for db_name on both primary and standby server.
Check the standby database log file for any error.
Step 1: Enable the archivelog mode.
SQL> shut immediate;
SQL> startup mount;
SQL> alter database archivelog;
Step 2: Enable force logging.
SQL> select force_logging from v$database;
SQL> alter database force logging;
SQL> select force_logging from v$database;
Step 3: Set below parameters in the primary database and restart the database to take effect.
SQL> alter
system set log_archive_config='dg_config=(DC,DR)' scope=spfile;
SQL> alter
system set log_archive_dest_1='LOCATION=/home/oracle/u01/
valid_for=(all_logfiles,all_roles) db_unique_name=DC' scope=spfile;
SQL> alter
system set log_archive_dest_state_1=enable scope=spfile;
SQL> alter
system set log_archive_dest_2='service=DR async
valid_for=(online_logfile,primary_role) db_unique_name=DR' scope=spfile;
SQL> alter
system set log_archive_dest_state_2=enable scope=spfile;
SQL> alter
system set standby_file_management=auto scope=spfile;
SQL> alter
system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> alter
system set fal_client='DC' scope=spfile;
SQL> alter
system set fal_server='DR' scope=spfile;
SQL> shut immediate;
SQL> startup;
Ensure the above values has been reflected in the database.
Step 4: Add tns and listener entries on both primary and standby server. Also start listeners on both servers.
Primary:
oracle@dc admin]$ cat tnsnames.ora
DC =
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = prim-server)(PORT = 1521))
(CONNECT_DATA =
(SERVER
= DEDICATED)
(SERVICE_NAME = DC)
)
)
DR =
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = stand-server)(PORT = 1521))
(CONNECT_DATA =
(SERVER
= DEDICATED)
(SERVICE_NAME = DC)
)
)
[oracle@dc admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC
=
(GLOBAL_DBNAME = dr.oracle.com)
(ORACLE_HOME = /home/oracle/u01/product/11.2.0/dbhome_1)
(SID_NAME = DC)
)
)
Standby:
oracle@dc admin]$ cat tnsnames.ora
DC =
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = prim-server)(PORT = 1521))
(CONNECT_DATA =
(SERVER
= DEDICATED)
(SERVICE_NAME = DC)
)
)
DR =
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = stand-server)(PORT = 1521))
(CONNECT_DATA =
(SERVER
= DEDICATED)
(SERVICE_NAME = DC)
)
)
[oracle@dr admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST
=
(SID_DESC =
(SID_NAME = DC)
(ORACLE_HOME = /home/oracle/u01/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.92)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/oracle/u01
Step 5: Copy password file from primary server to standby server and ensure that password should be same as primary.
Step 6: Add below two entries in standby server pfile and we will set rest of the parameters using duplicate command.
[oracle@dr dbs]$ cat initDC.ora
DB_NAME='DC'
DB_UNIQUE_NAME='DR'
Step 7: Create below script for duplicate command.
[oracle@dr dbs]$ cat stand.cmd
run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate auxiliary channel st1 type disk;
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='DR'
set control_files='/home/oracle/u01/control01.ctl','/home/oracle/u01/control02.ctl'
set fal_client='DC'
set fal_server='DR'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(DC,DR)'
set log_archive_dest_1='LOCATION=/home/oracle/u01/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR'
set log_archive_dest_2='service=DC ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC'
nofilenamecheck;
}
Step 8: Now start the standby database in nomount mode using newly created pfile.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise
Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
Step 9: Now start the RMAN prompt and first connect target database DC and then auxiliary database DR using below commands.
[oracle@dr dbs]$
[oracle@dr dbs]$
[oracle@dr dbs]$ ls -ltr
total 28
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Jun 21 17:17 lkDC
-rw-r--r--. 1 oracle oinstall 29 Jun 22 16:13
initDC.ora
-rw-r-----. 1 oracle oinstall 1536 Jun 22 16:23
orapwDC
-rw-r-----. 1 oracle oinstall 24 Jun 22 16:23 lkDR
-rw-r--r--. 1 oracle oinstall 676 Jun 22 16:25
stand.cmd
-rw-rw----. 1 oracle oinstall 1544 Jun 22 16:26
hc_DC.dat
[oracle@dr dbs]$
[oracle@dr dbs]$
[oracle@dr dbs]$ rman
Recovery Manager: Release 11.2.0.4.0 - Production on
Thu Jun 22 16:26:24 2017
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
RMAN> connect target sys/oracle@DC
connected to target database: DC (DBID=1661358039)
RMAN> connect auxiliary sys/oracle@DR
connected to auxiliary database: DC (not mounted)
RMAN> @stand.cmd
RMAN> run {
2> allocate channel ch1 type disk;
3> allocate channel ch2 type disk;
4> allocate channel ch3 type disk;
5> allocate auxiliary channel st1 type disk;
6> duplicate target database
7> for standby
8> from active database
9> dorecover
10> spfile
11> set db_unique_name='DR'
12> set
control_files='/home/oracle/u01/control01.ctl','/home/oracle/u01/control02.ctl'
13> set fal_client='DC'
14> set fal_server='DR'
15> set standby_file_management='AUTO'
16> set log_archive_config='dg_config=(DC,DR)'
17> set
log_archive_dest_1='LOCATION=/home/oracle/u01/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR'
18> set log_archive_dest_2='service=DC ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC'
19> nofilenamecheck;
20> }
using target database control file instead of
recovery catalog
allocated channel: ch1
channel ch1: SID=41 device type=DISK
allocated channel: ch2
channel ch2: SID=1 device type=DISK
allocated channel: ch3
channel ch3: SID=42 device type=DISK
allocated channel: st1
channel st1: SID=19 device type=DISK
Starting Duplicate Db at 22-JUN-17
contents of Memory Script:
{
backup as copy reuse
targetfile '/home/oracle/u01/product/11.2.0/dbhome_1/dbs/orapwDC' auxiliary
format
'/home/oracle/u01/product/11.2.0/dbhome_1/dbs/orapwDC'
targetfile
'/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora'
auxiliary format
'/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora'
;
sql clone "alter system set spfile=
''/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora''";
}
executing Memory Script
Starting backup at 22-JUN-17
Finished backup at 22-JUN-17
sql statement: alter system set spfile=
''/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''DR''
comment=
''''
scope=spfile";
sql clone "alter system set control_files =
''/home/oracle/u01/control01.ctl'',
''/home/oracle/u01/control02.ctl'' comment=
''''
scope=spfile";
sql clone "alter system set fal_client =
''DC''
comment=
''''
scope=spfile";
sql clone "alter system set fal_server =
''DR''
comment=
''''
scope=spfile";
sql clone "alter system set standby_file_management
=
''AUTO''
comment=
''''
scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(DC,DR)''
comment=
''''
scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/home/oracle/u01/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR'' comment=
''''
scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=DC
ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC'' comment=
''''
scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''DR'' comment= ''''
scope=spfile
sql statement: alter system set control_files = ''/home/oracle/u01/control01.ctl'',
''/home/oracle/u01/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''DC'' comment= ''''
scope=spfile
sql statement: alter system set fal_server = ''DR'' comment= ''''
scope=spfile
sql statement: alter system set standby_file_management
= ''AUTO''
comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(DC,DR)''
comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/home/oracle/u01/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR'' comment= ''''
scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=DC ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC'' comment= ''''
scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 784998400 bytes
Fixed Size
2257352 bytes
Variable Size
515903032 bytes
Database Buffers 264241152 bytes
Redo Buffers
2596864 bytes
allocated channel: st1
channel stby: SID=18 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/oracle/u01/control01.ctl';
restore clone controlfile to
'/home/oracle/u01/control02.ctl' from
'/home/oracle/u01/control01.ctl';
}
executing Memory Script
Starting backup at 22-JUN-17
channel ch1: starting datafile copy
copying standby control file
output file
name=/home/oracle/u01/product/11.2.0/dbhome_1/dbs/snapcf_DC.f
tag=TAG20170622T162656 RECID=2 STAMP=947348816
channel ch1: datafile copy complete, elapsed time:
00:00:01
Finished backup at 22-JUN-17
Starting restore at 22-JUN-17
channel st1: copied control file copy
Finished restore at 22-JUN-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using
DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile
1 to
"/home/oracle/datafile/DC/temp01.dbf";
switch clone tempfile all;
set newname for datafile
1 to
"/home/oracle/datafile/DC/system01.dbf";
set newname for datafile
2 to
"/home/oracle/datafile/DC/sysaux01.dbf";
set newname for datafile
3 to
"/home/oracle/datafile/DC/undotbs01.dbf";
set newname for datafile
4 to
"/home/oracle/datafile/DC/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/datafile/DC/system01.dbf"
datafile
2
auxiliary format
"/home/oracle/datafile/DC/sysaux01.dbf"
datafile
3
auxiliary format
"/home/oracle/datafile/DC/undotbs01.dbf"
datafile
4
auxiliary format
"/home/oracle/datafile/DC/users01.dbf"
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to
/home/oracle/datafile/DC/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 22-JUN-17
channel ch1: starting datafile copy
input datafile file number=00001
name=/home/oracle/datafile/DC/system01.dbf
channel ch2: starting datafile copy
input datafile file number=00002
name=/home/oracle/datafile/DC/sysaux01.dbf
channel ch3: starting datafile copy
input datafile file number=00003
name=/home/oracle/datafile/DC/undotbs01.dbf
output file
name=/home/oracle/datafile/DC/undotbs01.dbf tag=TAG20170622T162704
channel ch3: datafile copy complete, elapsed time:
00:00:03
channel ch3: starting datafile copy
input datafile file number=00004
name=/home/oracle/datafile/DC/users01.dbf
output file
name=/home/oracle/datafile/DC/users01.dbf tag=TAG20170622T162704
channel ch3: datafile copy complete, elapsed time:
00:00:01
output file name=/home/oracle/datafile/DC/sysaux01.dbf
tag=TAG20170622T162704
channel ch2: datafile copy complete, elapsed time:
00:00:19
output file
name=/home/oracle/datafile/DC/system01.dbf tag=TAG20170622T162704
channel ch1: datafile copy complete, elapsed time:
00:00:29
Finished backup at 22-JUN-17
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/home/oracle/u01/1_14_947265431.arc" auxiliary format
"/home/oracle/u01/1_14_947265431.arc"
;
catalog clone archivelog
"/home/oracle/u01/1_14_947265431.arc";
switch clone datafile all;
}
executing Memory Script
Starting backup at 22-JUN-17
channel ch1: starting archived log copy
input archived log thread=1 sequence=14 RECID=13
STAMP=947348853
output file name=/home/oracle/u01/1_14_947265431.arc
RECID=0 STAMP=0
channel ch1: archived log copy complete, elapsed
time: 00:00:01
Finished backup at 22-JUN-17
cataloged archived log
archived log file
name=/home/oracle/u01/1_14_947265431.arc RECID=1 STAMP=947348854
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=947348854 file
name=/home/oracle/datafile/DC/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=947348854 file
name=/home/oracle/datafile/DC/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=947348854 file
name=/home/oracle/datafile/DC/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=947348854 file
name=/home/oracle/datafile/DC/users01.dbf
contents of Memory Script:
{
set until scn 976400;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 22-JUN-17
starting media recovery
archived log for thread 1 with sequence 14 is
already on disk as file /home/oracle/u01/1_14_947265431.arc
archived log file
name=/home/oracle/u01/1_14_947265431.arc thread=1 sequence=14
media recovery complete, elapsed time: 00:00:00
Finished recover at 22-JUN-17
Finished Duplicate Db at 22-JUN-17
released channel: ch1
released channel: ch2
released channel: ch3
released channel: st1
RMAN>
RMAN> **end-of-file**
RMAN>
Step 10: Start MRP process on standby server.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Step 11: Switch 2-3 logs on primary server and check standby database alert log file to see if logs are reflecting on standby.
SQL> alter system
switch logfile;
SQL> alter system
switch logfile;
SQL> alter system
switch logfile;
DC-DR sync check Query:
On Primary:
SQL> select
name,db_unique_name,database_role,open_mode,controlfile_type from v$database;
SQL> select
max(sequence#) from v$archived_log;
On standby:
SQL> select
name,db_unique_name,database_role,open_mode,controlfile_type from v$database;
SQL> select
max(sequence#) from v$archived_log where applied=’YES’;
***************** END ****************
Great post Rupesh, It's a really nice work done by you, now no need to go through any other standby creation docs over the Internet. Thanks for sharing..
ReplyDelete