Step-by-Step Primary and Standby Database Configuration

Primary Server Configuration (primary_config.sql) in Oracle Dataguard Setup:

Primary Server Configuration (primary_config.sql)

-- Set the database to archive log mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Enable force logging
ALTER DATABASE FORCE LOGGING;

-- Set the primary database parameters
ALTER SYSTEM SET db_unique_name='primary_db' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(primary_db,standby_db)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_db' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=standby_db ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_1=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET fal_server=standby_db SCOPE=BOTH;
ALTER SYSTEM SET fal_client=primary_db SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;

-- Add standby redo log files
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/primary_db/redo01.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/primary_db/redo02.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/primary_db/redo03.log') SIZE 500M;

Standby Server Configuration (standby_config.sql)

-- Set the standby database parameters
ALTER SYSTEM SET db_unique_name='standby_db' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(primary_db,standby_db)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby_db' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=primary_db ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary_db' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_1=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET fal_server=primary_db SCOPE=BOTH;
ALTER SYSTEM SET fal_client=standby_db SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management=AUTO SCOPE=BOTH;

-- Add standby redo log files
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/standby_db/redo01.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/standby_db/redo02.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/standby_db/redo03.log') SIZE 500M;

Note:

1. In the context of Oracle Data Guard, primary_db and standby_db refer to the unique database names (DB_UNIQUE_NAME) of the primary and standby databases, respectively. These names are used to identify and configure the databases within the Data Guard environment.
2. In the following command, SERVICE=standby_db refers to the TNS entry for the standby database, enabling the primary database to send redo data to the standby database.

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