Oracle Replication through Oracle Data Guard

This is a step by step guide to create Physical Standby database in Oracle using Oracle Data Guard.

1. Force Logging on Primary Instance


Set Force Logging on Primary database instance.

alter database force logging;

Verify by running below query.

SELECT force_logging FROM v$database;

2. Add standby redo log files

ALTER DATABASE ADD STANDBY LOGFILE 'C:\ORACLE\ORADATA\ORCL\SREDO01.log' SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE 'C:\ORACLE\ORADATA\ORCL\SREDO02.log' SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE 'C:\ORACLE\ORADATA\ORCL\SREDO03.log' SIZE 50M REUSE;

3. PFILE for Primary

create pfile='C:\Oracle\Oracle12c\database\pfile.ora' from spfile;

Add below lines in PFILE just created.

*.log_archive_dest_1='location=C:\Oracle\archivelogs\orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_format='orcl_%t_%s_%r.arc'
*.db_unique_name='orcl'
*.log_archive_config='DG_CONFIG=(orcl,orcl_standby)'
*.log_archive_dest_2='SERVICE=orcl_standby ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_standby'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.fal_server='orcl_standby'
*.db_file_name_convert='C:\Oracle\oradata\orcl\','C:\Oracle\oradata\orcl\'
*.log_file_name_convert='C:\Oracle\oradata\orcl\','C:\Oracle\oradata\orcl\'
*.standby_file_management='AUTO'

4. Enable Archiving and create SPFILE


Enable Archive Logs (if it is not already done), start Oracle using new PFILE and create SPFILE

SHUTDOWN IMMEDIATE;
STARTUP nomount pfile='C:\Oracle\Oracle12c\database\pfile.ora';
create spfile from pfile='C:\Oracle\Oracle12c\database\pfile.ora';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

5. Create a Backup Copy of the Primary Database Data Files


In this example I am using OS copy rather than RMAN duplication.

SHUTDOWN IMMEDIATE;

Now copy data files from C:\Oracle\Oradata\ORCL (may be different path in your case) of Primary to some folder in Standby database server.


6. Create a Control File for the Standby Database

STARTUP MOUNT;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\CONTROL01.ctl';
ALTER DATABASE OPEN;

Now copy this control file to some folder in Standby database server as well.


7. PFILE for Standby Database


Create a Parameter File for the Standby Database

create pfile='C:\Oracle\Oracle12c\database\pfile.ora' from spfile;

Add below lines in the pfile just created

*.DB_UNIQUE_NAME=orcl_standby
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_standby)'
*.DB_FILE_NAME_CONVERT='C:\Oracle\oradata\orcl\','C:\Oracle\oradata\orcl\'
*.LOG_FILE_NAME_CONVERT='C:\Oracle\oradata\orcl\','C:\Oracle\oradata\orcl\'
*.LOG_ARCHIVE_FORMAT='orcl_%t_%s_%r.arc'
*.LOG_ARCHIVE_DEST_1='location=C:\Oracle\archivelogs\orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER='orcl'

8. Copy Data & Other Files


First shutdown standby database

SHUTDOWN IMMEDIATE;

Now copy Data files which were copied in above steps to the respective data files folder of standby server.
Copy Standby control file to the respective data files folder of standby server.
Now run following on Standby DB

STARTUP nomount pfile='C:\Oracle\Oracle12c\database\pfile.ora';
create spfile from pfile='C:\Oracle\Oracle12c\database\pfile.ora';
SHUTDOWN IMMEDIATE;
STARTUP;

9. TNS NAMES


Both Primary and Standby should have same TNSNAMES.ORA file

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

10. LISTENER


Listener.ORA on Primary

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\Oracle\Oracle12c)
	  (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Listener.ORA on Standby

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\Oracle\Oracle12c)
	  (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

11. Restart Listeners


Restart listeners on Primary and Standby

lsnrctl stop
lsnrctl start

Test listners

tnsping ORCL
tnsping ORCL_STANDBY

12. Start Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;