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

---------

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

Comments