Skip to main content

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

 

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

  1. Log Apply Delay: Check network bandwidth and latency between primary and standby.
  2. Archive Gap: Use v$archive_gap to identify missing logs and manually transfer them if needed.
  3. Network Issues: Verify tnsnames.ora and listener.ora configurations on both servers.
  4. 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.

Labels:

Comments

Post a Comment

Popular posts from this blog

🚀 Automating Oracle Database Patching with Ansible: A Complete Guide

Oracle database patching has long been the bane of DBAs everywhere. It's a critical task that requires precision, expertise, and often results in extended maintenance windows. What if I told you that you could automate this entire process, reducing both risk and downtime while ensuring consistency across your Oracle estate? 💡 In this comprehensive guide, I'll walk you through a production-ready Ansible playbook that completely automates Oracle patch application using OPatch. Whether you're managing a single Oracle instance or hundreds of databases across your enterprise, this solution will transform your patch management strategy! 🎯 🔥 The Challenge: Why Oracle Patching is Complex Before diving into the solution, let's understand why Oracle patching is so challenging: 🔗 Multiple dependencies : OPatch versions, Oracle Home configurations, running processes ⚠️ Risk of corruption : Incorrect patch application can render databases unusable ⏰ Downtime requirements : Da...
Post a Comment
Read more

Oracle RAC Switchover & Switchback: Step-by-Step Guide

 Ensuring business continuity requires regular Disaster Recovery (DR) drills. This guide covers the Switchover and Switchback process between Primary (DC) and Standby (DR) databases . Pre-checks Before Performing Switchover Before starting the activity, ensure there are no active sessions in the database. If any are found, share the session details with the application team, get their confirmation, and terminate the sessions. Primary Database Name: PRIMARY Standby Database Name: STANDBY  Identify Active Sessions set lines 999 pages 999 col machine for a30 col username for a30 col program for a30 compute sum of count on report break on report select inst_id,username,osuser,machine,program,status,count(1) "count" from gv$session where inst_id=1 and program like 'JDBC%' group by inst_id,username,osuser,machine,program,status order by 1,2; select inst_id,username,osuser,machine,program,status,count(1) "count" from gv$session where inst_id=2 and program lik...
Post a Comment
Read more

Mastering Oracle RAC with SRVCTL Commands

Oracle Real Application Clusters (RAC) provide high availability, scalability, and manageability for databases. One of the most powerful tools for managing RAC databases is srvctl , a command-line utility that allows administrators to control various database services. This blog explores essential srvctl commands to help you efficiently manage Oracle RAC environments. 1. Checking Database Configuration and Status  List all available databases on the host:                  srvctl config database   Check the status of a specific database and its instances:                    srvctl status database -d <database_name>   Retrieve detailed status information about a database, including its instances and states:                    srvctl status database -d <database_name> -v 2. Stopping and Starting Databases   ...
Post a Comment
Read more