Physical DR Configuration

Prerequisite

1. Must have password file for both Primary and Standby database with same SYS password

 $orapwd file=orapwTESTDR entries=30 ignorecase=Y  
SQL> select * from V$PWFILE_USERS;

2. Both Primary and Standby should has same DB_NAME and Different DB_UNIQUE_NAME.

3. The standby redo log must have at least one or more redo log group than the redo log at the primary database.

4. Size of standby redolog files should be same as primary online redolog files.

5. Oracle recommends to keep standby redologs on both primary and standby database for make switchover quick.

Primary DB Configuration


1. Check that the primary database is in archivelog mode.

SQL> select log_mode from v$database;

If not, switch to archivelog mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

2. Enabled forced logging by issuing following command.

SQL> ALTER DATABASE FORCE LOGGING;

3. Check the setting for DB_NAME & DB_UNIQUE_NAME parameters.

SQL> show parameter db_name;
SQL> show parameter db_unique_name;
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ebs' SCOPE=SPFILE;

4. Verify the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary database(DB_UNIQUE_NAME for both primary and standby DB)

SQL> show parameter LOG_ARCHIVE_CONFIG;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ebs,ebs_st)'; 

5. Set suitable remote archive log destinations.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ebs_st NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ebs_st';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 

6. The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters set to appropriate values and REMOTE_LOGIN_PASSWORDFILE set to exclusive.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; 

7. It is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters.

SQL> ALTER SYSTEM SET FAL_SERVER=ebs_st(standby db name);
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

8. Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.

ebs=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebst.aitkenspence.lk)(PORT=1526))
            (CONNECT_DATA= (SID=ebs) ) )

ebs_st=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=demo.astech.lk)(PORT=1526))
            (CONNECT_DATA= (SID=ebs) ) )

9. Listener file "$ORACLE_HOME/network/admin/listener.ora" of Primary DB.

ebs =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL=TCP)(HOST=ebst.aitkenspence.lk)(PORT=1526)) ) )

SID_LIST_ebs =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/ebs/db/tech_st/11.1.0)
      (SID_NAME = ebs) ) )

10. Create a Standby controlfile for the standby database by issuing the following command on the primary database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ebs_stby.ctl'; 

11. Create standby logfile.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/ebs/db/apps_st/data/standby_redo01.log') SIZE 50M; 

12. Take a backup of the primary database.

 Standby DB Configuration

1. Restore database using backup of primary DB.

2. Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters.

SQL> show parameter db_name;
SQL> show parameter db_unique_name;
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ebs_st' SCOPE=SPFILE; 

3. Verify that the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary and standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ebs,ebs_st)'; 

4. It is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters.

SQL> ALTER SYSTEM SET FAL_SERVER=DB11G; (primary db name)  
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

5. Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.

ebs_st =
(DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=demo.astech.lk)(PORT=1526))
                (CONNECT_DATA=(SID=ebs)) )

ebs=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebst.aitkenspence.lk)(PORT=1526))
            (CONNECT_DATA= (SID=ebs) ) )

6. Listener file "$ORACLE_HOME/network/admin/listener.ora" of Standby DB.

ebs =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=demo.astech.lk)(PORT=1526)) )

SID_LIST_EBS =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ebs_st)
      (ORACLE_HOME = /u01/ebs/db/tech_st/11.1.0)
      (SID_NAME = ebs) ) )

7. Create Redo logfile and standby logfile on standby server.

SQL> ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/ebs/online_redo01.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/ebs/db/apps_st/data/standby_redo01.log') SIZE 50M;

8. Startup standby database in mount mode using standby controlfile (created from primary).

SQL> startup nomount;
SQL> alter database mount standby database; 

9. Start the apply process on standby server.

SQL> alter database recover managed standby database;
SQL> alter database recover managed standby database disconnect;
For Real-Time Apply
SQL> alter database recover managed standby database using current logfile disconnect; 

10. Checking errors in both servers.

SQL> SELECT DEST_ID "ID",STATUS "DB_status",DESTINATION "Archive_dest",ERROR "Error" FROM V$ARCHIVE_DEST;

SQL> select MESSAGE,to_char(timestamp,'DD-MON-YYYY hh24.mi.ss') date_time from v$dataguard_status;

11. Check the new archived redo log has arrived at the standby server and been applied.

SQL>SELECT sequence#,first_time,next_time,archived,applied FROM v$archived_log ORDER BY sequence#;

SQL>ALTER SYSTEM SWITCH LOGFILE; 

Reference
Business Continuity for Oracle E-Business Release 12 Using Oracle 11g Release 2 and later Physical Standby Database (Doc ID 1070033.1)