Oracle Dataguard - Configuring Active dataguard

Active Data Guard is a feature of Oracle Database that allows the physical standby database to be open for read-only and reporting operations while continuously applying changes from the primary database in real-time.
  • Enable Active Data Guard
  • Verify MRP is Running
  • Test Active Data Guard
  • Revert Back to Physical Standby

    • When you open Physical Standby in read only mode, it is known as Active Data Guard. But, Active Data Guard needs license for and you must check with Oracle for same before implementing it.

    Enable Active Data Guard :
    Let us Open Physical Standby and test active data guard

    On standby :
    Open the database : 
        SQL> alter database open;

    Database altered.
    Check the database status and database role : 
        SQL> select name, open_mode, database_role from v$database;

    NAME	  OPEN_MODE	       DATABASE_ROLE
    --------- -------------------- ----------------
    PRIM	  READ ONLY	       PHYSICAL STANDBY
    Start the MRP : 
        SQL> alter database recover managed standby database disconnect;

    Database altered.
    Verify MRP is Running :
    Check if MRP is running in the background or not

    On standby : 
        SQL> select process, status, sequence# from v$managed_standby;

    PROCESS   STATUS	SEQUENCE#
    --------- ------------ ----------
    ARCH	  CLOSING	       15
    DGRD	  ALLOCATED		0
    DGRD	  ALLOCATED		0
    ARCH	  CONNECTED		0
    ARCH	  CONNECTED		0
    ARCH	  CONNECTED		0
    ARCH	  CONNECTED		0
    RFS	  IDLE		       16
    RFS	  IDLE			0
    MRP0	  APPLYING_LOG	       16

    10 rows selected.
    Test Active Data Guard :
    As our active data guard is open for read only queries and background recover is active, let us create a table on primary and see if it is reflected on standby.

    On primary : 
        SQL> create table testable(sno number(2),sname varchar2(10));

    Table created.

    SQL> insert into testable values (1, 'John ');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> alter system switch logfile;

    System altered
    On standby :
    Check the standby whether the data is reflected or not : 
        SQL> select * from testable;

      SNO         SNAME
    -------  ----------
      1           John
    Revert back to physical standby :
    If you want to convert active data guard back to physical standby.

    Stop the MRP :
    On standby : 
        SQL> alter database recover managed standby database cancel;

    Database altered.
    Down the database and startup in mount
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area  536870912 bytes
    Fixed Size		    8622776 bytes
    Variable Size		  297798984 bytes
    Database Buffers	  222298112 bytes
    Redo Buffers		    8151040 bytes
    Database mounted.
    Check the status : 
        SQL> select name, open_mode, database_role from v$database;

    NAME	  OPEN_MODE	       DATABASE_ROLE
    --------- -------------------- ----------------
    PRIM	  MOUNTED	       PHYSICAL STANDBY

    Start the MRP:

    SQL> alter database recover managed standby database disconnect;

    Database altered.
    Verify MRP is Running : 
        SQL> select process, status, sequence# from v$managed_standby;

    PROCESS   STATUS	SEQUENCE#
    --------- ------------ ----------
    ARCH	  CONNECTED		0
    DGRD	  ALLOCATED		0
    DGRD	  ALLOCATED		0
    ARCH	  CONNECTED		0
    ARCH	  CONNECTED		0
    ARCH	  CONNECTED		0
    ARCH	  CONNECTED		0
    RFS           WRITING	       19
    RFS           IDLE			0
    MRP0	  APPLYING_LOG	       19

    10 rows selected.


    (Oracle Dataguard - Switchover & Failover)