Create Physical Standby database from Primary database using RMAN

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

---------

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

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

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

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

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

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

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

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

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