We can also use RMAN duplicate command to create a STANDBY RAC database from Production Standalone/RAC database. We can either use Backups of Production Database or we can create from Active Database also.
PFB steps to create a Standby Database in RAC using Active Database Duplication Method :-
Primary Database is TEST
Standby Database is STBY(RAC)
PFB steps to create a Standby Database in RAC using Active Database Duplication Method :-
Primary Database is TEST
Standby Database is STBY(RAC)
Changes to be done on Primary side :-
1. Database has to be archive log mode
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
2. Change primary database to Force Logging mode :-
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
3. Initialization parameters in Primary database :-
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TEST
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TEST
4. Set the DG_CONFIG setting of LOG_ARCHIVE_CONFIG parameter. for this DB_UNIQUE_NAME of Standby database has to be different from primary.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STBY)';
System altered.
5. Set suitable remote archivelog destinations.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
-- NOTE :- Service and DB_UNIQUE_NAME has to be of Standby database
6. Set parameters related to LOG_ARCHIVE and password file parameters :-
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
7. It is recommended to make sure that Primary can switch roles to become a Standby. For that we need to set some -convert parameters if there are path differences between the servers :-
SQL> ALTER SYSTEM SET FAL_SERVER=STBY;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE; -- Primay server destination and Standby server destination
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE; -- Primay server destination and Standby server destination
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
8. As some parameters cannot be changed while database is open , we need to restart database to before they take effect :-
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 960495616 bytes
Fixed Size 1222816 bytes
Variable Size 247465824 bytes
Database Buffers 700448768 bytes
Redo Buffers 11358208 bytes
Database mounted.
Database opened.
SQL>
9. In both servers entries of Primary as well as Standby are needed in tnsnames.ora file and we are able to do tnsping.
###################ORACLE_DATA_GAURD############################
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SCAN-IP)(PORT = 9010))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY.wellsfargo.com)
)
)
STBY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = VIP-Firstnode)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY.wellsfargo.com)
(INSTANCE_NAME = STBY1)
)
)
STBY2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = VIP-SECONDnode)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY.wellsfargo.com)
(INSTANCE_NAME = STBY2)
)
)
TEST_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST_DGMGRL)
)
)
STBY_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SCAN-IP)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY_DGMGRL)
)
)
###################ORACLE_DATA_GAURD############################
10. Add static entries to listener.ora file
PRIMARY
(SID_DESC =
(GLOBAL_DBNAME = test.db.com)
(SID_NAME = TEST)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME=TEST_DGMGRL)
(ORACLE_HOME=/opt/oracle/product/database/11.2.0.4)
(SID_NAME=test)
)
STANDBY
First node :-
(SID_DESC =
(GLOBAL_DBNAME = STBY.wellsfargo.com)
(SID_NAME = STBY1)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME = STBY_DGMGRL.wellsfargo.com)
(SID_NAME = STBY1)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
Second Node :-
(SID_DESC =
(GLOBAL_DBNAME = STBY.wellsfargo.com)
(SID_NAME = STBY2)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME = STBY_DGMGRL.wellsfargo.com)
(SID_NAME = STBY2)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
11. Reload listener after adding these entries
Lsnrctl reload
12. Add Standby redo logs to primary database :-
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo04.log') SIZE 50M;
13. Copy the passw0rd file from Primary and copy to Standby
14. Copy the init.ora file from primary and change below parameters :-
CREATE PFILE='/initTEST.ora' from spfile;
scp orapwTEST user@STBY_server:$ORACLE_HOME/dbs/orapwSTBY1
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY1.ora
Steps to be done in STANDBY Side :-
15 . Update Pfile for below parameters for Standby server
*.audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
*.diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'
16. Start the STANDBY database with above pfile
export ORACLE_SID=STBY1
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY1.ora'
ORACLE instance started.
Total System Global Area 960495616 bytes
Fixed Size 1222816 bytes
Variable Size 264243040 bytes
Database Buffers 683671552 bytes
Redo Buffers 11358208 bytes
17. Connect to RMAN and use RMAN duplicate command to create STANDBY
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate auxiliary channel ch3 device type disk;
allocate auxiliary channel ch4 device type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
from active database
DORECOVER
spFILE
SET db_unique_name='STBY' COMMENT 'Is STANDBY'
SET LOG_ARCHIVE_DEST_2='SERVICE=TEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
set control_files='+DATA_0220/STBY/controlfile/control01.ctl','+FRA_0220/STBY/controlfile/control02.ctl'
set db_create_file_dest='+DATA_0220/'
set db_create_online_log_dest_1='+DATA_0220/'
set diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'
set audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
set db_recovery_file_dest='+FRA_0220/'
set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','+DATA_0220/STBY/'
set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','+DATA_0220/STBY/'
set FAL_SERVER='TEST'
set cluster_database='false'
set instance_number='1'
set LOG_ARCHIVE_DEST_1='location=+FRA_0220/STBY/';
}
Above command will create the STANDBY database
18. Create pfile from spfile
create pfile='/ora_backup/duplicate_test/STBY/initTEST.ora' from spfile;
19. Add the below parametErs for RAC database
STBY1.log_archive_format='%t_%s_%r.dbf'
STBY2.log_archive_format='%t_%s_%r.dbf'
STBY2.thread=2
STBY1.thread=1
STBY1.undo_tablespace='UNDOTBS1'
STBY2.undo_tablespace='UNDOTBS2'
STBY2.instance_number=2
STBY1.instance_number=1
STBY1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = first_node)(PORT = 9001))'
STBY2. local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = second_node)(PORT = 9001))'
*.cluster_database=TRUE
20. Start the instance with above pfile
==>sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 12:51:57 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
SQL> startup force pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ ONLY
SQL> create spfile='+DATA_0220/STBY/spfileSTBY.ora' from pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';
File created.
SQL> startup force
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.
21. Login to second instance
Export ORACLE_SID=STBY2
==>sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 13:01:32 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select INST_ID,INSTANCE_NAME,HOST_NAME,VERSION,LOGINS from gv$instance;
INST_ID INSTANCE_NAME HOST_NAME VERSION LOGINS
---------- ---------------- ------------------------- ----------------- ----------
2 STBY2 wuprd02a0220 11.2.0.4.0 ALLOWED
1 STBY1 wuprd01a0220 11.2.0.4.0 ALLOWED
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = second_node)(PORT = 9001))
remote_listener string scan_ip:9010
SQL>
24. Create the broker configuration details ( for both Primary and Standby):-
SQL> alter system set dg_broker_start=FALSE;
System altered.
SQL> alter system set dg_broker_config_file1='/opt/oracle/product/database/11.2.0.4/dbs/dr1STBY.dat';
SQL> alter system set dg_broker_config_file2='/opt/oracle/product/database/11.2.0.4/dbs/dr2STBY.dat';
System altered.
SQL> alter system set dg_broker_start=TRUE;
System altered.
Create the configuation
DGMGRL> connect /
Connected.
DGMGRL> create configuration DR as primary database is TEST connect identifier is TEST;
Configuration "dr" created with primary database "TEST"
DGMGRL> add database STBY as connect identifier is STBY maintained as physical;
Database "STBY" added
DGMGRL> show configuration
Configuration - dr
Protection Mode: MaxPerformance
Databases:
TEST - Primary database
STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
Check if every thing is fine by checking Standby Database details
DGMGRL> show database STBY
Database - STBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
STBY1 (apply instance)
STBY2
Database Status:
SUCCESS
DGMGRL> exit
Standby Creation in RAC using RMAN duplicate command completed.
Standby creation for standalone using RMAN duplicate command
I hope this article helped you
Regards,
Amit Rath
1. Database has to be archive log mode
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
2. Change primary database to Force Logging mode :-
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
3. Initialization parameters in Primary database :-
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TEST
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TEST
4. Set the DG_CONFIG setting of LOG_ARCHIVE_CONFIG parameter. for this DB_UNIQUE_NAME of Standby database has to be different from primary.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STBY)';
System altered.
5. Set suitable remote archivelog destinations.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
-- NOTE :- Service and DB_UNIQUE_NAME has to be of Standby database
6. Set parameters related to LOG_ARCHIVE and password file parameters :-
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
7. It is recommended to make sure that Primary can switch roles to become a Standby. For that we need to set some -convert parameters if there are path differences between the servers :-
SQL> ALTER SYSTEM SET FAL_SERVER=STBY;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE; -- Primay server destination and Standby server destination
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE; -- Primay server destination and Standby server destination
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
8. As some parameters cannot be changed while database is open , we need to restart database to before they take effect :-
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 960495616 bytes
Fixed Size 1222816 bytes
Variable Size 247465824 bytes
Database Buffers 700448768 bytes
Redo Buffers 11358208 bytes
Database mounted.
Database opened.
SQL>
9. In both servers entries of Primary as well as Standby are needed in tnsnames.ora file and we are able to do tnsping.
###################ORACLE_DATA_GAURD############################
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SCAN-IP)(PORT = 9010))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY.wellsfargo.com)
)
)
STBY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = VIP-Firstnode)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY.wellsfargo.com)
(INSTANCE_NAME = STBY1)
)
)
STBY2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = VIP-SECONDnode)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY.wellsfargo.com)
(INSTANCE_NAME = STBY2)
)
)
TEST_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST_DGMGRL)
)
)
STBY_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SCAN-IP)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY_DGMGRL)
)
)
###################ORACLE_DATA_GAURD############################
10. Add static entries to listener.ora file
PRIMARY
(SID_DESC =
(GLOBAL_DBNAME = test.db.com)
(SID_NAME = TEST)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME=TEST_DGMGRL)
(ORACLE_HOME=/opt/oracle/product/database/11.2.0.4)
(SID_NAME=test)
)
STANDBY
First node :-
(SID_DESC =
(GLOBAL_DBNAME = STBY.wellsfargo.com)
(SID_NAME = STBY1)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME = STBY_DGMGRL.wellsfargo.com)
(SID_NAME = STBY1)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
Second Node :-
(SID_DESC =
(GLOBAL_DBNAME = STBY.wellsfargo.com)
(SID_NAME = STBY2)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME = STBY_DGMGRL.wellsfargo.com)
(SID_NAME = STBY2)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
Lsnrctl reload
12. Add Standby redo logs to primary database :-
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo04.log') SIZE 50M;
13. Copy the passw0rd file from Primary and copy to Standby
14. Copy the init.ora file from primary and change below parameters :-
CREATE PFILE='/initTEST.ora' from spfile;
scp orapwTEST user@STBY_server:$ORACLE_HOME/dbs/orapwSTBY1
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY1.ora
Steps to be done in STANDBY Side :-
15 . Update Pfile for below parameters for Standby server
*.audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
*.diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'
export ORACLE_SID=STBY1
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY1.ora'
ORACLE instance started.
Total System Global Area 960495616 bytes
Fixed Size 1222816 bytes
Variable Size 264243040 bytes
Database Buffers 683671552 bytes
Redo Buffers 11358208 bytes
17. Connect to RMAN and use RMAN duplicate command to create STANDBY
rman target
sys/****@TEST_DGMGRL auxiliary sys/****@STBY_DGMGRL
run{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate auxiliary channel ch3 device type disk;
allocate auxiliary channel ch4 device type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
from active database
DORECOVER
spFILE
SET db_unique_name='STBY' COMMENT 'Is STANDBY'
SET LOG_ARCHIVE_DEST_2='SERVICE=TEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
set control_files='+DATA_0220/STBY/controlfile/control01.ctl','+FRA_0220/STBY/controlfile/control02.ctl'
set db_create_file_dest='+DATA_0220/'
set db_create_online_log_dest_1='+DATA_0220/'
set diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'
set audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
set db_recovery_file_dest='+FRA_0220/'
set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','+DATA_0220/STBY/'
set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','+DATA_0220/STBY/'
set FAL_SERVER='TEST'
set cluster_database='false'
set instance_number='1'
set LOG_ARCHIVE_DEST_1='location=+FRA_0220/STBY/';
}
Above command will create the STANDBY database
18. Create pfile from spfile
create pfile='/ora_backup/duplicate_test/STBY/initTEST.ora' from spfile;
19. Add the below parametErs for RAC database
STBY1.log_archive_format='%t_%s_%r.dbf'
STBY2.log_archive_format='%t_%s_%r.dbf'
STBY2.thread=2
STBY1.thread=1
STBY1.undo_tablespace='UNDOTBS1'
STBY2.undo_tablespace='UNDOTBS2'
STBY2.instance_number=2
STBY1.instance_number=1
STBY1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = first_node)(PORT = 9001))'
STBY2. local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = second_node)(PORT = 9001))'
*.cluster_database=TRUE
20. Start the instance with above pfile
==>sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 12:51:57 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
SQL> startup force pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ ONLY
SQL> create spfile='+DATA_0220/STBY/spfileSTBY.ora' from pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';
File created.
SQL> startup force
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL>
SQL> show parameter listenner
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = first_node)(PORT = 9001))
remote_listener string scan_ip:9010
Export ORACLE_SID=STBY2
==>sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 13:01:32 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select INST_ID,INSTANCE_NAME,HOST_NAME,VERSION,LOGINS from gv$instance;
INST_ID INSTANCE_NAME HOST_NAME VERSION LOGINS
---------- ---------------- ------------------------- ----------------- ----------
2 STBY2 wuprd02a0220 11.2.0.4.0 ALLOWED
1 STBY1 wuprd01a0220 11.2.0.4.0 ALLOWED
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = second_node)(PORT = 9001))
remote_listener string scan_ip:9010
SQL>
Check all the
parameters in both instances are same or not
22. Start the recovery process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
23. Add the details of Standby database in OCR
Srvctl add database –d STBY –o $ORACLE_HOME -p
Srvctl add instance –d STBY –i STBY1-n firstnode
Srvctl add instance –d STBY –I STBY2 –n secondnode
Srvctl status database –d STBY
SQL> alter system set dg_broker_start=FALSE;
System altered.
SQL> alter system set dg_broker_config_file1='/opt/oracle/product/database/11.2.0.4/dbs/dr1STBY.dat';
SQL> alter system set dg_broker_config_file2='/opt/oracle/product/database/11.2.0.4/dbs/dr2STBY.dat';
System altered.
SQL> alter system set dg_broker_start=TRUE;
System altered.
Create the configuation
DGMGRL> connect /
Connected.
DGMGRL> create configuration DR as primary database is TEST connect identifier is TEST;
Configuration "dr" created with primary database "TEST"
DGMGRL> add database STBY as connect identifier is STBY maintained as physical;
Database "STBY" added
DGMGRL> show configuration
Configuration - dr
Protection Mode: MaxPerformance
Databases:
TEST - Primary database
STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
Check if every thing is fine by checking Standby Database details
DGMGRL> show database STBY
Database - STBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
STBY1 (apply instance)
STBY2
Database Status:
SUCCESS
DGMGRL> exit
Standby Creation in RAC using RMAN duplicate command completed.
Standby creation for standalone using RMAN duplicate command
I hope this article helped you
Regards,
Amit Rath
.png)
