How to enable Data Guard for Oracle standby database

Assumptions:

- This is what was done to switch to data guard from manual log shipping on existing standby
database
- This is a physical standby
- Maximum Performance mode
- Recovery lag 24 hours
- No role transition is used, just a failover from production to standby database
- Primary database is called PROD and standby is PROD_STBY in the example below


1. On primary database:

orapwd FILE=${ORACLE_HOME}/dbs/orapw<SID> PASSWORD=<sys-password> ENTRIES=10 force=y

Make sure tnsnames.ora has an entry for PROD_STBY

SQL> ALTER DATABASE FORCE LOGGING;

Change the following parameters:

DB_UNIQUE_NAME='PROD'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PROD_STBY)'
LOG_ARCHIVE_DEST_2='SERVICE=PROD_STBY LGWR ASYNC VALID_FOR=ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD_STBY DELAY=1440 REOPEN=60'
LOG_ARCHIVE_DEST_STATE_1='ENABLE'
LOG_ARCHIVE_DEST_STATE_2='ENABLE'
LOG_ARCHIVE_MAX_PROCESSES=4
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'


2. On standby database:

orapwd FILE=${ORACLE_HOME}/dbs/orapw<SID> PASSWORD=<sys-password> ENTRIES=10 force=y

Make sure listener.ora has an entry for standby database

Change the following parameters:

DB_UNIQUE_NAME='PROD_STBY'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PROD_STBY)'
STANDBY_FILE_MANAGEMENT='AUTO'
FAL_SERVER='PROD'
FAL_CLIENT='PROD_STBY'
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

Remove LOG_ARCHIVE_DEST_2 if set

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


3. Check the replication

On primary database:

SQL> select * from v$archive_dest where dest_id = 2;


On standby:

SQL> select sequence#, to_char(first_time, 'DD/MM/YYYY HH24:MI') AS first_time,
to_char(next_time, 'DD/MM/YYYY HH24:MI') AS next_time, applied 
from v$archived_log 
order by sequence#;