Creating Physical Standby FROM ACTIVE DATABASE
Reference :
Step by Step Guide on Creating Physical Standby Using
RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)
Below
Document Supports Oracle Version 11.1.0.6 to 11.2.0.4
Requirement:
Creating standby Without Shuttting down the primary
database
Steps:
1. Install Oracle database enterprise edition for Primary
database using Create and configure a database option.
2.Install Oracle database enterprise edition for
standby using Install database software only option.
Primary
|
STANDBY
|
|
DB_NAME
|
TESTPRIM
|
TESTPRIM
|
DB_UNIQUE_NAME
|
TESTPRIM
|
TESTSTD
|
3.Once database Installed shutdown the Primary database
Include Below parameters in pfile.If already exist ignore it. then create
spfile from pfile then start the database using spfile.
Below parameters required for Primary pfile.
*.log_archive_config='DG_CONFIG=(TESTPRIM,TESTSTD)'
*.log_archive_dest_1='LOCATION=/ORACLE/TEST_PRIM/ARCHIVE
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=TESTPRIM'
*.log_archive_dest_2='SERVICE=TESTSTD ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)
DB_UNIQUE_NAME=TESTSTD'
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=2500M
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.undo_tablespace='UNDOTBS1'
*.processes=150
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTPRIMXDB)'
*.open_cursors=300
*.diagnostic_dest='/ORACLE/TEST_PRIM'
*.db_block_size=8192
*.db_domain=''
*.db_name='TESTPRIM'
*.db_unique_name='TESTPRIM'
*.compatible='11.2.0.4.0'
#*.audit_file_dest='/ORACLE/TEST_PRIM/admin/TESTPRIM/adump'
#*.audit_trail='db'
*.control_files='/ORACLE/TEST_PRIM/oradata/TESTPRIM/control01.ctl','/ORACLE/TEST_PRIM/fast_recovery_area/TESTPRIM/control02.ctl'
TESTPRIM.__oracle_base='/ORACLE/TEST_PRIM'#ORACLE_BASE
set from environment
Primary :
Primary tnsnames.ora
TESTPRIM = (DESCRIPTION =(ADDRESS = (PROTOCOL =
TCP)(HOST = HOSTANME)(PORT = 1521))
(CONNECT_DATA
=(SERVER = DEDICATED)(SERVICE_NAME = TESTPRIM)))
Primary listener.ora
TESTPRIM =(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTANME)(PORT
= 1521))))
SID_LIST_TESTPRIM =(SID_LIST =
(SID_DESC =(ORACLE_HOME=
/ORACLE/TEST_PRIM/product/11.2.0)
(SID_NAME =
TESTPRIM)) )
STARTUP_WAIT_TIME_TESTPRIM = 0
CONNECT_TIMEOUT_TESTPRIM = 10
TRACE_LEVEL_TESTPRIM = O
Add standby logfile in Primary :
so that it will be restored in standby.
alter database add standby logfile '/ORACLE/TEST_PRIM/oradata/TESTPRIM/SREDO01.log' size 52428800;
alter database add standby logfile '/ORACLE/TEST_PRIM/oradata/TESTPRIM/SREDO02.log' size 52428800;
alter database add standby logfile '/ORACLE/TEST_PRIM/oradata/TESTPRIM/SREDO03.log' size 52428800;
alter database add standby logfile '/ORACLE/TEST_PRIM/oradata/TESTPRIM/SREDO04.log' size 52428800;
Below Steps required for standby server:
·
Copy password file from Primary to standby.
·
Then Rename the password file in standby.
[oracle@XXXXXX dbs]$ mv orapwTESTPRIM orapwTESTSTD
Standby tnsnames.ora
TESTSTD =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTANME)(PORT
= 1522))
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =
TESTSTD)))
Standby listener.ora
TESTSTD =(DESCRIPTION_LIST =
(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)
(HOST = HOSTANME)(PORT = 1522))))
SID_LIST_TESTSTD =(SID_LIST =
(SID_DESC
=(ORACLE_HOME=/ORACLE/TESTSTAND/product/11.2.0)
(SID_NAME = TESTSTD)))
STARTUP_WAIT_TIME_TESTSTD = 0
CONNECT_TIMEOUT_TESTSTD = 10
TRACE_LEVEL_TESTSTD = O
Then add Primary tnsentry in standby. standby tns entry
in Primary.
Primary tnsnames.ora file After standby tns
entry added in Primary :
TESTPRIM = (DESCRIPTION =(ADDRESS = (PROTOCOL =
TCP)(HOST =HOSTANME)(PORT = 1521))
(CONNECT_DATA
=(SERVER = DEDICATED)(SERVICE_NAME = TESTPRIM)))
TESTSTD =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST
=HOSTANME)(PORT = 1522)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME
= TESTSTD)))
standby tnsnames.ora file After Primary tns
entry added in standby :
TESTSTD =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST
=HOSTANME)(PORT = 1522)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME
= TESTSTD)))
TESTPRIM = (DESCRIPTION =(ADDRESS = (PROTOCOL =
TCP)(HOST = HOSTANME)(PORT = 1521))
(CONNECT_DATA
=(SERVER = DEDICATED)(SERVICE_NAME = TESTPRIM)))
once added come to standby server start the standby
database using pfile with DB_NAME=TESTPRIM parameter.Then start the listener
for standby
Then Come to Primary instance connect to rman
[oracle@XXXXXX ORACLE]$ rman target sys/sys@TESTPRIM
auxiliary sys/sys@TESTSTD
Recovery Manager: Release 11.2.0.4.0 - Production on
Fri Feb 6 03:07:28 2015
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
connected to target database: TESTPRIM
(DBID=3948647613)
connected to auxiliary database: TESTPRIM (not mounted)
RMAN> run
2> {
3> duplicate target database for standby from active
database spfile
4> parameter_value_convert 'TESTPRIM','TESTSTD'
5> set db_unique_name='TESTSTD'
6> set
db_file_name_convert='/ORACLE/TEST_PRIM/oradata/TESTPRIM','/ORACLE/TESTSTAND/oradata'
7> set
log_file_name_convert='/ORACLE/TEST_PRIM/oradata/TESTPRIM','/ORACLE/TESTSTAND/oradata'
8> set
control_files='/ORACLE/TESTSTAND/oradata/STANDCTRL01.ctl','/ORACLE/TESTSTAND/oradata/STANDCTRL02.ctl'
9> set log_archive_max_processes='5'
10> set
fal_client='TESTSTD'
11> set fal_server='TESTPRIM'
12> set standby_file_management='AUTO'
13> set
log_archive_config='dg_config=(TESTPRIM,TEST)'
14> set log_archive_dest_2='service=TESTPRIM ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TESTPRIM'
15> ;
16> }
Then come to stand by server check whether primary and
standby are synching.
Method : 1
SQL> select sequence#,archived,applied from
v$archived_log;
SEQUENCE# ARC APPLIED
7 YES YES
12 YES
YES
9 YES
YES
11 YES
YES
8 YES
YES
10 YES
YES
6 rows selected.
Method : 2
Come to Primary follow below steps
SQL> create table IT(ID number,name varchar2(20));
Table created.
SQL> insert into IT values(1,'Anwar');
1 row created.
SQL> insert into IT values(2,'RAM');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
Then come to stand by check whether the same table
created in standby or not
SQL> select * from IT;
ID NAME
---------- --------------------
1 Anwar
2 RAM
