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
------------------------------------ --------------------------------- -----------
db_name string ipxtest
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{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'ipxtest','teston'
set db_name='ipxtest'
set db_unique_name='teston'
set db_file_name_convert='/ipxtest/','/teston/'
set log_file_name_convert='/ipxtest/','/teston/'
set control_files='+FLASH'
set log_archive_max_processes='4'
set standby_archive_dest = '/holding/teston/standby_logs'
set log_archive_dest_1 = 'LOCATION=/holding/teston/archive_logs valid_for=(online_logfile, all_roles)'
set log_archive_dest_2 = 'LOCATION=/holding/teston/standby_logs valid_for=(standby_logfile, standby_role)'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(ipxtest,teston)'
;
}
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';
System altered.
SQL> alter system set fal_server='ipxtest';
System altered.
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;
System altered.
SQL> alter system set local_listener = 'teston';
System altered.
[root@stagedevdb2 ~]# mkdir -p /holding/ipxtest/standby_logs
[root@stagedevdb2 ~]# chown -R oracle:oinstall /holding/ipxtest/standby_logs
Primary Database:
SQL> alter system set fal_client='ipxtest';
System altered.
SQL> alter system set fal_server='teston';
System altered.
SQL> alter system set log_archive_dest_2= 'LOCATION=/holding/ipxtest/standby_logs valid_for=(standby_logfile, standby_role)';
System altered.
SQL> alter system set log_archive_dest_3='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.
SQL> alter system set local_listener = 'ipxtest';
System altered.
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
oracle@stagedevdb2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-JUL-2015 11:30:48
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 4 days 0 hr. 33 min. 44 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 "ipxtest.ipx.com" has 1 instance(s).
Instance "ipxtest", 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...
Service "teston.ipx.com" has 1 instance(s).
Instance "ipxtest", status READY, has 1 handler(s) for this service...
Service "teston_DGMGRL" has 1 instance(s).
Instance "teston", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
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