Oracle Data Guard Physical Standby Database Setup: Step-by-Step Guide

Oracle Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. In this guide, we'll walk through the complete process of setting up a physical standby database using Oracle Data Guard.

Environment Details

Primary Database:
- Host: rac121.goraclab.com
- SID: TEST
- DB_UNIQUE_NAME: test
Standby Database:
- Host: rac122.goraclab.com
- SID: TESTSTBY
- DB_UNIQUE_NAME: teststby

1. Primary Database Configuration

1.1. Verify Primary Database Status

SQL> select name, open_mode, database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY

1.2. Enable Force Logging

Force logging ensures all changes made to the database are logged, which is critical for Data Guard replication:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

1.3. Verify Database Parameters

SQL> show parameter db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      test

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      test

1.4. Ensure Archive Log Mode is Enabled

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

If archive log mode is not enabled, enable it using:

SQL> shut immediate 
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

1.5. Configure Data Guard Parameters

1.5.1. Set Data Guard Configuration

SQL> alter system set log_archive_config='dg_config=(test,teststby)';

System altered.

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(test,teststby)

1.5.2. Configure Archive Log Destination for Standby

SQL> alter system set log_archive_dest_2='service=teststby noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=teststby';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

1.5.3. Set Archive Log Format

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

System altered.

1.5.4. Increase Archive Processes

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

System altered.

1.5.5. Configure Password File and FAL Server

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

SQL> ALTER SYSTEM SET FAL_SERVER=teststby;

System altered.

1.5.6. Enable Automatic File Management

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

1.6. Create Parameter File for Standby

SQL> create pfile='/u01/app/pfile_test.ora' from spfile;

File created.

1.7. Add Standby Redo Logs

Standby redo logs are required for real-time apply:

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/TEST/STANDBY/stby_log01.log','/u01/app/oracle/oradata/TEST/STANDBY/stby_log02.log') size 200m;
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/TEST/STANDBY/stby_log03.log','/u01/app/oracle/oradata/TEST/STANDBY/stby_log04.log') size 200m;
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/TEST/STANDBY/stby_log05.log','/u01/app/oracle/oradata/TEST/STANDBY/stby_log06.log') size 200m;
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/TEST/STANDBY/stby_log07.log','/u01/app/oracle/oradata/TEST/STANDBY/stby_log08.log') size 200m;
SQL> alter database add standby logfile group 8 ('/u01/app/oracle/oradata/TEST/STANDBY/stby_log09.log','/u01/app/oracle/oradata/TEST/STANDBY/stby_log10.log') size 200m;

2. Network Configuration

2.1. Primary Database Listener Configuration

Create/modify listener.ora on the primary server:

# listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (SID_NAME = test)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac121.goraclab.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

2.2. Primary Database TNS Configuration

Create/modify tnsnames.ora on the primary server:

TEST =
     (DESCRIPTION =
         (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = rac121.goraclab.com)(PORT = 1521))
          )
     (CONNECT_DATA =
        (SERVICE_NAME = TEST)
     )
     )

TESTSTBY =
        (DESCRIPTION =
         (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = rac122.goraclab.com)(PORT = 1521))
         )
     (CONNECT_DATA =
       (SERVICE_NAME = TESTSTBY)
     )
        )

2.3. Standby Database Listener Configuration

Create/modify listener.ora on the standby server:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = teststby)
   (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
   (SID_NAME = teststby)
  )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac122.goraclab.com)(PORT = 1521))
    )
  )

2.4. Standby Database TNS Configuration

Create/modify tnsnames.ora on the standby server:

TEST =
     (DESCRIPTION =
         (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = rac121.goraclab.com)(PORT = 1521))
          )
     (CONNECT_DATA =
        (SERVICE_NAME = TEST)
     )
     )

TESTSTBY =
        (DESCRIPTION =
         (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST =  rac122.goraclab.com)(PORT = 1521))
         )
     (CONNECT_DATA =
       (SERVICE_NAME = TESTSTBY)(UR=A)
     )
       )

3. Copy Files from Primary to Standby

3.1. Copy Password File

[oracle@rac121 dbs]$ scp -P 122 orapwtest oracle@lab.goraclab.com:/u01/app/oracle/product/19c/db_1/dbs/

3.2. Copy Parameter File

[oracle@rac121 app]$ scp -P 122 pfile_test.ora oracle@lab.goraclab.com:/u01/app/oracle/product/19c/db_1/dbs/

3.3. Rename Files on Standby

[oracle@rac122 dbs]$ mv orapwtest orapwteststby
[oracle@rac122 dbs]$ mv pfile_test.ora initteststby.ora

4. Configure Standby Parameter File

Modify the parameter file (initteststby.ora) on the standby server with the following parameters:

*.audit_file_dest='/u01/app/oracle/admin/teststby/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TESTSTBY/controlfile/control01.ctl','/u01/app/FRA/TESTSTBY/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='test'
*.db_unique_name='teststby'
*.db_recovery_file_dest='/u01/app/FRA'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_server='TEST'
*.log_archive_config='dg_config=(test,teststby)'
*.log_archive_dest_1='LOCATION=/u01/app/ARCHIVE'
*.log_archive_dest_2='service=test noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=test'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=834m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2501m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

5. Create Standby Database Using RMAN Duplication

5.1. Set Environment Variables

export ORACLE_SID=teststby

5.2. Start Standby Instance in NOMOUNT State

sqlplus / as sysdba
SQL> startup nomount;

5.3. Connect to RMAN and Duplicate Database

rman target sys/sys@test auxiliary sys/sys@teststby

RMAN> duplicate target database for standby from active database nofilenamecheck;

6. Configure and Enable Standby Recovery

6.1. Verify Standby Database Status

SQL> select name,open_mode,database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

6.2. Create SPFILE from PFILE

SQL> create spfile from pfile;

File created.

SQL> shut immediate;
Database dismounted.
ORACLE instance shut down.

6.3. Start Database and Verify Status

SQL> startup
ORACLE instance started.

Total System Global Area 2634022312 bytes
Fixed Size                  8900008 bytes
Variable Size             553648128 bytes
Database Buffers         2063597568 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> select name,open_mode,database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ ONLY            PHYSICAL STANDBY

6.4. Enable Managed Recovery Process

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

Database altered.

6.5. Verify MRP Process is Running

SQL> !ps -ef|grep mrp
oracle    2239     1  0 11:46 ?        00:00:00 ora_mrp0_teststby
oracle    2278  1581  0 11:46 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp
oracle    2281  2278  0 11:46 pts/2    00:00:00 grep mrp

6.6. Verify Log Apply Progress

SQL> select sequence#, thread#, to_char(FIRST_TIME,'dd-mm-yyyy hh24:mi:ss') "SYNC TIME" 
     from v$log_history 
     where sequence# in (select max(sequence#) from v$log_history group by thread#);

 SEQUENCE#    THREAD# SYNC TIME
---------- ---------- -------------------
        11          1 26-11-2022 11:50:11

7. Monitoring and Management Tips

7.1. Monitor Log Apply Progress

To check if the standby database is keeping up with the primary:

-- On Primary:
SQL> select sequence#, first_change#, next_change# from v$archived_log order by sequence#;

-- On Standby:
SQL> select sequence#, first_change#, next_change# from v$archived_log order by sequence#;

7.2. Check Data Guard Status

-- On either database:
SQL> select status, error from v$archive_dest_status where dest_id=2;

7.3. Check Gap Status

-- On Standby:
SQL> select * from v$archive_gap;

7.4. Switch Logs on Primary to Test Replication

-- On Primary:
SQL> alter system switch logfile;

8. Common Issues and Troubleshooting

Log Apply Delay: Check network bandwidth and latency between primary and standby.
Archive Gap: Use v$archive_gap to identify missing logs and manually transfer them if needed.
Network Issues: Verify tnsnames.ora and listener.ora configurations on both servers.
Disk Space: Monitor archive log destinations for available space.

Setting up Oracle Data Guard Physical Standby provides high availability and disaster recovery capabilities for your Oracle database. By following this step-by-step guide, you can successfully establish a robust standby database infrastructure that ensures business continuity and data protection.

Remember that Oracle Data Guard configuration may need to be adjusted based on your specific requirements, including network bandwidth, storage capacity, and recovery time objectives.

DBA NAVIGATOR