ORACLE DATAGUARD SETUP WITH STANDBY DATABASE CREATION

Hello everyone,

I have compiled this article in order to show step by step guide on how to setup oracle Dataguard along with Standby database creation. I have also published a article on common issues faced during the data guard setup. I will link the article down below. Please do like, share and comment to show your support. All suggestions are welcome. Have a great day!

Why Oracle Data Guard ?

1. High Availability: Minimizes downtime with automated failover and switchover capabilities.
2. Disaster Recovery: Provides geographical redundancy and ensures zero data loss with Maximum Protection mode.
3. Data Protection: Guards against data corruption and allows backup offloading to standby databases.
4. Read-Only Access: Offloads reporting and queries to standby databases, improving performance.
5. Simplified Management: Centralized management and easy role transitions using Data Guard Broker.

Steps to setup Oracle Data Guard -

1. Prepare the Primary Database -

1. Enable Force Logging:

    ALTER DATABASE FORCE LOGGING;        

2. Create a Password File:

orapwd file=$ORACLE_HOME/dbs/orapw<primary_db> password=<password> entries=5        

3. Configure Initialization Parameters:

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_db,standby_db)';        

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_db';        

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db';        

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc';        

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;        

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;        

ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=1;        

ALTER SYSTEM SET FAL_SERVER=standby_db;        

ALTER SYSTEM SET FAL_CLIENT=primary_db;        

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;        

4. Create Standby Redo Logs:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/path/to/standby_redo1.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/path/to/standby_redo2.log') SIZE 50M;        

Create standby redo logs as per needed.

2. Backup the Primary Database and Transfer to Standby -

1. Create a Backup of the Primary Database:

    RMAN> BACKUP DATABASE PLUS ARCHIVELOG;        

2. Transfer the Backup to the Standby Server:

    scp /path/to/backup_files user@standby_server:/path/to/destination/        

3. Set Up the Standby Database -

1. Prepare the Standby Initialization Parameters:

    CREATE PFILE='/path/to/initstandby.ora' FROM SPFILE;        

Modify the DB name, DB unique name, controlfile path in the pfile as needed along with the diagnostic dest.

2. Start the Standby Instance:

    STARTUP NOMOUNT PFILE='/path/to/initstandby.ora';        

3. Restore the Control File for the Standby Database:

    RMAN> RESTORE STANDBY CONTROLFILE FROM '/path/to/controlfile.bak';        

    RMAN> ALTER DATABASE MOUNT;        

4. Restore the Datafiles:

    RMAN> RESTORE DATABASE;        

    RMAN> RECOVER DATABASE;        

5. Create Standby Redo Logs (if not done earlier):

    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/path/to/standby_redo1.log') SIZE 50M;        

Repeat for each thread and additional standby redo logs as needed.

4. Start Data Guard Broker -

1. Enable Data Guard Broker on Primary and Standby:

    ALTER SYSTEM SET DG_BROKER_START=TRUE;        

2. Create a Configuration Using DGMGRL:

    dgmgrl sys/password@primary_db        

    DGMGRL> CREATE CONFIGURATION 'my_dg_config' AS PRIMARY DATABASE IS 'primary_db' CONNECT IDENTIFIER IS 'primary_db';        

    DGMGRL> ADD DATABASE 'standby_db' AS CONNECT IDENTIFIER IS 'standby_db' MAINTAINED AS PHYSICAL;        

    DGMGRL> ENABLE CONFIGURATION;        

5. Verify Data Guard Configuration -

1. Check Configuration Status:

    DGMGRL> SHOW CONFIGURATION;        

2. Monitor Log Apply Services:

    DGMGRL> SHOW DATABASE 'standby_db' LOGXPTSTATUS;        

    DGMGRL> SHOW DATABASE 'standby_db' STATUSREPORT;        

If you have made it till here without facing any issues then congratulations. But if any of you faced errors or issues during the setup process. Refer to my article on Trouleshooting Oracle DataGuard Issues.