How to Configure DataGuard in Oracle

Introduction to DataGuard

Oracle DataGuard is an advanced feature in Oracle Database that provides comprehensive data protection and disaster recovery solutions for mission-critical databases. It enables high availability, data protection, and database resilience by creating and maintaining standby databases that can take over production load in case of primary database failure. This guide provides step-by-step instructions on configuring Oracle DataGuard using both manual setup and DataGuard Broker.

Prerequisites for DataGuard Configuration

  • Oracle Enterprise Edition installed on both primary and standby servers.
  • Both primary and standby databases must have identical OS and hardware specifications.
  • Network connectivity between primary and standby databases with secured SSH configurations.
  • Configure the primary database in ARCHIVELOG mode and enable forced logging.
  • Ensure both primary and standby databases have the same Oracle database version and patch level.
  • Listener and TNS configurations should be properly set up for both primary and standby databases.

Configuring the Primary Database

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "cdb1" on the primary database.

    
    SQL> show parameter db_name
    NAME				     TYPE	 VALUE
    -------------------- ----------- ------------------------------
    db_name 			     string	 cdb1

    SQL> show parameter db_unique_name
    NAME				     TYPE	 VALUE
    --------------------- ----------- ------------------------------
    db_unique_name			 string	 cdb1

    SQL>
    

The DB_NAME parameter for the standby database should match that of the primary database, but it needs a distinct DB_UNIQUE_NAME. In this example, we’ll set the standby database’s DB_UNIQUE_NAME to "cdb1_stby".

To configure the primary database for DataGuard, set the necessary initialization parameters. These configurations facilitate data transfer between primary and standby databases.

    
    -- Set DataGuard configuration parameters
    ALTER SYSTEM SET log_archive_config='DG_CONFIG=(cdb1,cdb1_stby)' SCOPE=BOTH;
    ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb1' SCOPE=BOTH;
    ALTER SYSTEM SET log_archive_dest_2='SERVICE=cdb1_stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdb1_stby' SCOPE=BOTH;
    ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' SCOPE=SPFILE;
    ALTER SYSTEM SET fal_server='cdb1_stby' SCOPE=BOTH;
    ALTER SYSTEM SET fal_client='cdb1' SCOPE=BOTH;
    ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

    

Once the parameters are set, restart the primary database for changes to take effect.

Configuring Archve logging

Validate that the primary database is in archivelog mode.

    
    select log_mode from v$database;

    LOG_MODE
    ------------
    NOARCHIVELOG

    --If it is noarchivelog mode, switch is to archivelog mode.

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;
    

Configuring logging

Enabled forced logging by issuing the following command.

    
    -- Enable forced logging
    ALTER DATABASE FORCE LOGGING;
    

Creating Standby Redo Logs on the Primary Database

To prepare for smooth role transitions, such as switchovers, it’s essential to create standby redo logs on the primary database. These logs should match or exceed the size of the largest online redo logs, with an additional group created per thread. This redundancy helps ensure data is captured even during role changes. The following example demonstrates how to set up standby redo logs on both primary and standby servers:

For setups using Oracle Managed Files (OMF):

    
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 SIZE 50M;
    

For setups not using Oracle Managed Files (OMF):

    
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('/u01/oradata/cdb1/standby_redo01.log') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('/u01/oradata/cdb1/standby_redo02.log') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/cdb1/standby_redo03.log') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/cdb1/standby_redo04.log') SIZE 50M;
    

Enabling Flashback Database on Primary

If you intend to use the flashback database feature, it is best to enable it now on the primary database, as this will allow it to be activated on the standby database as well. Flashback database can be extremely useful for recovery scenarios and testing. Enable it with the following command:

    
    ALTER DATABASE FLASHBACK ON;
    

Service Configuration for DataGuard

Both the primary and standby databases need entries in the tnsnames.ora file, located at $ORACLE_HOME/network/admin/tnsnames.ora on each server. You can add these entries manually or use the Network Configuration Assistant (netca). The example below shows the entries required for this setup. Note that the SID (not the SERVICE_NAME) is specified here to ensure that the DataGuard Broker can still connect to the databases if they are down, as service names may not be active in such cases.

Primary Database Entry

    
    cdb1 =
        (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-1)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SID = cdb1)
        )
        )
    

Standby Database Entry

    
    cdb1_stby =
        (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-2)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SID = cdb1)
        )
        )
    

Listener Configuration on Primary Server

The listener.ora file on the primary server should have the following configuration, located at $ORACLE_HOME/network/admin/listener.ora. We define the listener details here to allow connectivity even when the database is not running.

    
    LISTENER =
        (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-1)(PORT = 1521))
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        )

    SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
            (GLOBAL_DBNAME = cdb1_DGMGRL)
            (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
            (SID_NAME = cdb1)
            (ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0/db_1/network/admin")
        )
        )

    ADR_BASE_LISTENER = /u01/app/oracle
    

Listener Configuration on Standby Server

The listener.ora file on the standby server requires a similar setup. Because the broker may need to connect while the database is down, we explicitly define the entry for the standby database rather than relying on auto-registration.

    
    LISTENER =
        (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = dbdocs-host-2)(PORT = 1521))
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        )

    SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
            (GLOBAL_DBNAME = cdb1_stby_DGMGRL)
            (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
            (SID_NAME = cdb1)
            (ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0/db_1/network/admin")
        )
        )

    ADR_BASE_LISTENER = /u01/app/oracle
    

Restarting the Listener

After making changes to the listener.ora file, restart the listener on both the primary and standby servers to apply the updates:

    
    lsnrctl stop
    lsnrctl start
    

Setting Up the Standby Database

Prepare for Database Duplication

Start by creating a parameter file for the standby database. Save this file as /tmp/initcdb1_stby.ora with the following content to specify the database name:

    
    *.db_name='cdb1'
    

Next, establish the required directories on the standby server to match the structure of the primary:

    
    mkdir -p /u01/app/oracle/oradata/cdb1/pdbseed
    mkdir -p /u01/app/oracle/oradata/cdb1/pdb1
    mkdir -p /u01/app/oracle/fast_recovery_area/cdb1
    mkdir -p /u01/app/oracle/admin/cdb1/adump
    

Configure Password File

Create a password file on the standby server, ensuring the SYS password matches that of the primary database. Run the following command:

    
    $ orapwd file=/u01/app/oracle/product/19.0.0/db_1/dbs/orapwcdb1 password=Password1 entries=10
    

Duplicating the Database with RMAN

To begin the database duplication, start the standby server's instance using the temporary parameter file:

    
    $ export ORACLE_SID=cdb1
    $ sqlplus / as sysdba
    SQL> STARTUP NOMOUNT PFILE='/tmp/initcdb1_stby.ora';
    

Connect to RMAN, specifying the full connect strings for both the TARGET (primary) and AUXILIARY (standby) databases, avoiding OS authentication.

    
    $ rman TARGET sys/Password1@cdb1 AUXILIARY sys/Password1@cdb1_stby
    

Then, execute the following DUPLICATE command to create the standby database:

    
    duplicate target database
        for standby
        from active database
        dorecover
        spfile
        set db_unique_name='cdb1_stby' COMMENT 'Standby Database'
        nofilenamecheck;
    

Modifying File Locations and Parameters During Duplication

If any file paths need to be converted or additional parameters adjusted, use the SET clause within the DUPLICATE command:

    
    duplicate target database
        for standby
        from active database
        dorecover
        spfile
        set db_unique_name='cdb1_stby' COMMENT 'Standby Database'
        set db_file_name_convert='/old/path1/','/new/path1/','/old/path2/','/new/path2/'
        set log_file_name_convert='/old/path1/','/new/path1/','/old/path2/','/new/path2/'
        set job_queue_processes='0'
        nofilenamecheck;
    

Explanation of Clauses

  • FOR STANDBY: Indicates that the DUPLICATE operation is for creating a standby database, avoiding a DBID change.
  • FROM ACTIVE DATABASE: Creates the standby directly from primary database files, bypassing additional backup steps.
  • DORECOVER: Ensures the DUPLICATE includes a recovery operation, bringing the standby up to date.
  • SPFILE: Enables adjustments to parameter values in the spfile copied from the primary server.
  • NOFILENAMECHECK: Skips validation for destination file paths, useful when paths differ.

With these steps completed, you’re ready to configure the DataGuard broker to manage your setup efficiently.

Using DataGuard Broker

DataGuard Broker provides an easier way to manage DataGuard configurations and monitor roles. To enable the Broker:

    
    -- Enable Broker on both primary and standby databases
    ALTER SYSTEM SET dg_broker_start=true SCOPE=BOTH;
    

After enabling the broker, use the DataGuard command-line interface (DGMGRL) to create the configuration.

    
    $ dgmgrl sys/Password1@cdb1
    
    DGMGRL> CREATE CONFIGURATION 'DGConfig' AS PRIMARY DATABASE IS 'cdb1' CONNECT IDENTIFIER IS 'cdb1';
    DGMGRL> ADD DATABASE 'cdb1_stby' AS CONNECT IDENTIFIER IS 'cdb1_stby' MAINTAINED AS PHYSICAL;
    DGMGRL> ENABLE CONFIGURATION;
    

Validating DataGuard Configuration

Verify that the configuration is complete and that both primary and standby databases are in sync.

    
  DGMGRL> SHOW CONFIGURATION;
  Configuration - DGConfig

  Protection Mode: MaxPerformance
  Members:
  cdb1      - Primary database
  cdb1_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)
    

This command shows the current status of the DataGuard configuration and helps identify any issues that need addressing.

    

DGMGRL> show database cdb1;

Database - cdb1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    cdb1

Database Status:
SUCCESS

DGMGRL> show database cdb1_stby;

Database - cdb1_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 5.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    cdb1

Database Status:
SUCCESS

DGMGRL>
    

Managing Standby Recovery

Stopping and Starting Recovery Mode

You can control the managed recovery process on the standby database by using the following SQL*Plus commands:

    
    -- To stop managed recovery.
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    -- To start managed recovery.
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
    

Performing a Database Switchover

A database can operate in two primary roles (either as primary or standby), and it's possible to switch between these roles without any data loss or resetting redo logs. This is known as a switchover. Here’s how to perform a switchover, starting from the primary database (cdb1) and switching to the standby (cdb1_stby):

    
    $ dgmgrl sys/Password1@cdb1
    DGMGRL> switchover to cdb1_stby;

    -- The command will connect to "cdb1" on database "cdb1_stby"
    -- and perform the switchover, making "cdb1_stby" the new primary.
    

Once completed, connect to the new primary (cdb1_stby) and switch back to make cdb1 the primary again:

    
    $ dgmgrl sys/Password1@cdb1_stby
    DGMGRL> switchover to cdb1;

    -- This command re-establishes cdb1 as the primary and cdb1_stby as standby.
    

Database Failover

If the primary database becomes unavailable, the standby database can take over as the new primary. Connect to the standby database (cdb1_stby) and issue a failover:

    
    $ dgmgrl sys/Password1@cdb1_stby
    DGMGRL> failover to cdb1_stby;

    -- The standby database "cdb1_stby" is now activated as the primary.
    

Since the standby is now the primary, a backup is recommended. The original primary (cdb1) can be reconfigured as a standby if Flashback Database was enabled, using the following command:

    
    DGMGRL> reinstate database cdb1;
    

If Flashback Database is not available, you will need to recreate cdb1 as a standby manually.

Manual Reconfiguration of the Primary as Standby

  1. Cleanup Old Instance: Shutdown the old primary and remove previous data.
  2.     
        sqlplus / as sysdba 
        shutdown immediate;
        exit;
        
    
        rm -Rf /u01/app/oracle/oradata/cdb1/*
        rm -Rf /u01/app/oracle/fast_recovery_area/cdb1
        mkdir -p /u01/app/oracle/oradata/cdb1/pdbseed
        mkdir -p /u01/app/oracle/oradata/cdb1/pdb1
        
    
  3. Restart Instance with Temporary Parameter File:
  4.     
        export ORACLE_SID=cdb1
        sqlplus / as sysdba 
        startup nomount pfile='/tmp/initcdb1_stby.ora';
        exit;
        
        
    
  5. Initiate Database Duplication with RMAN: Connect to RMAN for database duplication.
  6.     
        $ rman target sys/Password1@cdb1_stby auxiliary sys/Password1@cdb1
    
        duplicate target database
            for standby
            from active database
            dorecover
            spfile
            set db_unique_name='cdb1' COMMENT 'Configured as standby'
            nofilenamecheck;
        
    
  7. Enable the New Standby Database: Connect to DGMGRL on the primary and enable the standby configuration.
  8.     
        $ dgmgrl sys/Password1@cdb1_stby
        DGMGRL> enable database cdb1;
        
    

With these steps completed, the original primary database (cdb1) is re-established as a standby, and your setup is fully functional again.

Conclusion

Configuring Oracle DataGuard is essential for organizations requiring high availability and data resiliency. With this setup, the database can quickly recover from any primary failures by promoting a standby database to act as the primary, ensuring minimal downtime and data loss. This guide provides a comprehensive setup, from initial configuration to testing, offering a strong foundation for leveraging Oracle DataGuard in critical environments.



Rate Your Experience

: 1 : 0


Last updated in November, 2024

Online Tests
Read more

Cloud Technology
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access