Here I will show the process how to create physical standby database from primary database, hope you will enjoy it. Primary Database: ipxtest (Server stagedb3) Standby Database: teston (Server stagedevdb2) 1) Make sure primary database is running in archive log mode. Primary Database: SYS@ipxtest> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /holding/ipxtest/archive_logs/ Oldest online log sequence 3012 Next log sequence to archive 3017 Current log sequence 3017
2) Determine if FORCE LOGGING is enabled. If it is not enabled, enable FORCE LOGGING mode. Primary Database: SYS@ipxtest> SELECT force_logging FROM v$database; FORCE_LOG --------- NO SYS@ipxtest> ALTER DATABASE FORCE LOGGING; Database altered. SYS@ipxtest> SELECT force_logging FROM v$database; FORCE_LOG --------- YES 3) If standby log file does not exist, create standby log files. Primary Database: SYS@ipxtest> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 2G; Database altered. SYS@ipxtest> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 2G; Database altered. SYS@ipxtest> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 2G; Database altered. SYS@ipxtest> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' SIZE 2G; Database altered. 4)Set primary database initialization parameters Primary Database: SYS@ipxtest> show parameter log_archive_config; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ log_archive_config string SYS@ipxtest> show parameter db_name NAME
TYPE
VALUE SYS@ipxtest> alter system set log_archive_config='dg_config=(ipxtest,teston)'; System altered. SYS@ipxtest> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ standby_file_management string AUTO
SYS@ipxtest> alter system set log_archive_dest_2='service="teston"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="teston", valid_for=(all_logfiles,primary_role)' scope=both; System altered. 5) Tnsfiles in both primary and standby: Primary Database: IPXTEST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stagedb3.ipx.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ipxtest) ) )
TESTON = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stagedevdb2.ipx.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = teston) ) ) Standby Database: IPXTEST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stagedb3.ipx.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ipxtest) ) ) TESTON = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stagedevdb2.ipx.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = teston) (UR = A) ) ) 6) Listener file in standby database: Standby Database: [oracle@stagedevdb2 ~]$ cat /opt/oracle/product/11.2.0.3/network/admin/listener.ora # listener.ora Network Configuration File: /opt/oracle/product/11.2.0.3/network/admin/listener.ora # Generated by Oracle configuration tools.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stagedevdb2.ipx.com)(PORT = 1521)) ) )
SID_LIST_LISTENER= (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = teston) (ORACLE_HOME = /opt/oracle/product/11.2.0.3) (SID_NAME =teston) ) ) ADR_BASE_LISTENER_DGMGRL = /opt/oracle [oracle@stagedevdb2 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-JUL-2015 10:58:14 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stagedevdb2.ipx.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 16-JUL-2015 10:57:04 Uptime 0 days 0 hr. 1 min. 9 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/11.2.0.3/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/stagedevdb2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedevdb2.ipx.com)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "teston" has 1 instance(s). Instance "teston", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
7) Create path in Standby Database if does not exist Standby Database: [oracle@stagedevdb2 ~]$ mkdir -p $ORACLE_BASE/admin/teston/adump [oracle@stagedevdb2 ~]$ mkdir -p /opt/oracle/oradata [oracle@stagedevdb2 ~]$ mkdir -p /opt/oracle/fast_recovery_area 8) Create pfile in standby database Standby Database: [oracle@stagedevdb2 ~]$ echo DB_NAME=teston>/opt/oracle/product/11.2.0.3/dbs/initteston.ora 9) Copy password file from primary to standby database Copy password file from /opt/oracle/product/11.2.0.3/dbs/teston( stagedb3) to /opt/oracle/product/11.2.0.3/dbs/orapwteston (stagedevdb2) 10) startup standby database Standby Database: [oracle@stagedevdb2 ~]$ Export ORACLE_SID=teston [oracle@stagedevdb2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 16 11:20:08 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile=?/dbs/initteston.ora ORACLE instance started. Total System Global Area 329895936 bytes Fixed Size 2228024 bytes Variable Size 255852744 bytes Database Buffers 67108864 bytes Redo Buffers 4706304 bytes 11) create archive logs and standby logs destination in standby database if does not exist Standby Database: [root@stagedevdb2 ~]# mkdir -p /holding/teston/archive_logs [root@stagedevdb2 ~]# chown -R oracle:oinstall /holding/teston/archive_logs [root@stagedevdb2 ~]# mkdir -p /holding/teston/standby_logs [root@stagedevdb2 ~]# chown -R oracle:oinstall /holding/teston/standby_logs [root@stagedevdb2 ~]# mkdir -p /opt/oracle/admin/teston/adump [root@stagedevdb2 ~]# chown -R oracle:oinstall /opt/oracle/admin/teston/adump 12) Create standby database using RMAN: Primary Database: RMAN> connect target sys target database Password: connected to target database: IPXTEST (DBID=4207345083) RMAN> connect auxiliary sys@teston auxiliary database Password: connected to auxiliary database: IPXTEST (DBID=4207345083, not open) RMAN> run{ Primary Database: SQL> alter system switch logfile; System altered. Standby Database: SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> select sequence#, first_time, applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME APPLIED ---------- ------------------- --------------------------- 3026 2015-07-19 16:30:40 YES 3027 2015-07-20 03:24:58 YES 3028 2015-07-20 03:31:44 IN-MEMORY Primary Database: SQL> alter system switch logfile; System altered. SYS@ipxtest> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 3031 Standby Database: SQL> select sequence#, first_time, applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME APPLIED ---------- ------------------- --------------------------- 3026 2015-07-19 16:30:40 YES 3027 2015-07-20 03:24:58 YES 3028 2015-07-20 03:31:44 YES 3029 2015-07-20 09:01:18 YES 3030 2015-07-20 09:04:08 YES 3031 2015-07-20 09:12:36 IN-MEMORY 6 rows selected. 13) Set parameter in Primary and Standby database. Standby Database: SQL> alter
system set fal_client='teston'; SQL> alter system set log_archive_dest_3='service="ipxtest"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="ipxtest", valid_for=(all_logfiles,primary_role)' scope=both;
[root@stagedevdb2 ~]# chown -R oracle:oinstall /holding/ipxtest/standby_logs Primary Database: SQL> alter
system set fal_client='ipxtest'; Broker Configuration Standby Database: SQL> alter system set dg_broker_start=TRUE; System altered. [oracle@stagedevdb2 ~]$ cat /opt/oracle/product/11.2.0.3/network/admin/listener.ora # listener.ora Network Configuration File: /opt/oracle/product/11.2.0.3/network/admin/listener.ora # Generated by Oracle configuration tools.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stagedevdb2.ipx.com)(PORT = 1521)) ) )
SID_LIST_LISTENER= (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = teston) (ORACLE_HOME = /opt/oracle/product/11.2.0.3) (SID_NAME =teston) ) (SID_DESC= (GLOBAL_DBNAME=teston_DGMGRL) (SID_NAME=teston) (ORACLE_HOME = /opt/oracle/product/11.2.0.3) ) ) ADR_BASE_LISTENER_DGMGRL = /opt/oracle
[oracle@stagedevdb2 ~]$ lsnrctl reload LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-JUL-2015 11:30:36 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stagedevdb2.ipx.com)(PORT=1521))) The command completed successfully
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-JUL-2015
11:30:48 Primary Database: SYS@ipxtest> alter system set dg_broker_start=TRUE; System altered. [oracle@stagedb3 ~]$ cat /opt/oracle/product/11.2.0.3/network/admin/listener.ora # listener6576693324201666456.ora Network Configuration File: /tmp/listener6576693324201666456.ora # Generated by Oracle configuration tools. LISTENER_DB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stagedb3.ipx.com)(PORT = 1521)) ) ) SID_LIST_LISTENER_DB = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ipxtest) (ORACLE_HOME = /opt/oracle/product/11.2.0.3) (SID_NAME = ipxtest) ) (SID_DESC = (GLOBAL_DBNAME = ipxtest.ipx.com) (ORACLE_HOME = /opt/oracle/product/11.2.0.3) (SID_NAME = ipxtest) ) (SID_DESC = (GLOBAL_DBNAME = ipxtest_DGMGRL) (ORACLE_HOME = /opt/oracle/product/11.2.0.3) (SID_NAME = ipxtest) ) ) ADR_BASE_LISTENER_DB = /opt/oracle [oracle@stagedb3 ~]$ lsnrctl reload LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-JUL-2015 11:53:24 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully [oracle@stagedb3 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-JUL-2015 13:21:18 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER_DB Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 20-JUL-2015 13:18:23 Uptime 0 days 0 hr. 2 min. 54 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/11.2.0.3/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/stagedb3/listener_db/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedb3.ipx.com)(PORT=1521))) Services Summary... Service "ipxtest" has 1 instance(s). Instance "ipxtest", status UNKNOWN, has 1 handler(s) for this service... Service "ipxtest.ipx.com" has 2 instance(s). Instance "ipxtest", status UNKNOWN, has 1 handler(s) for this service... Instance "ipxtest", status READY, has 1 handler(s) for this service... Service "ipxtest_DGMGRL" has 1 instance(s). Instance "ipxtest", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully Primary Database: [oracle@stagedb3 ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys Password: Connected. DGMGRL> create configuration 'DGConfig1' as primary database is 'ipxtest' > connect identifier is ipxtest; Configuration "DGConfig1" created with primary database "ipxtest" DGMGRL> add database 'teston' as connect identifier is teston; Database "teston" added DGMGRL> enable configuration Enabled. DGMGRL> show configuration Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: ipxtest - Primary database teston - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Check in Standby database: [oracle@stagedevdb2 ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys Password: Connected. DGMGRL> show configuration; Configuration - DGConfig1 Protection Mode: MaxPerformance Databases: ipxtest - Primary database teston - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |