Active Dataguard:
Oracle Active Data Guard is a separately licensed database option for Oracle Database 11g Enterprise Edition.
It includes the Real-time Query feature which enables a physical standby database to be open in read-only mode while Redo Apply is active(MRP).
Users who are connected to a physical standby database can query and report against data that is up-to-date with the primary database.
It also enables you to configure RMAN BCT(Block Change Tracking) for a physical standby database.
With RMAN BCT, you can offload fast incremental backups from the production database to the physical standby database.
Step 1: You must have physical standby database configured in your environment.
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- ------- --------------------
DC DR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED
MAX(SEQUENCE#)
--------------
129
SQL>
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
Step 2: See your MRP process is active. If you try to open the database, you will get below error. You need to stop the MRP process and then open the database.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
SQL> alter database open;
Database altered.
SQL>
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- ------- --------------------
DC DR PHYSICAL STANDBY READ ONLY STANDBY NOT ALLOWED
MAX(SEQUENCE#)
--------------
129
On Primary:
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> @rd
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- ------- --------------------
DC DC PRIMARY READ WRITE CURRENT TO STANDBY
MAX(SEQUENCE#)
--------------
131
Step 3: Now start MRP process and see the DB status.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- ------- --------------------
DC DR PHYSICAL STANDBY READ ONLY WITH APPLY STANDBY NOT ALLOWED
MAX(SEQUENCE#)
--------------
131
Standby Database Alert Logfile:
Thu Aug 03 20:05:37 2017
Media Recovery Log /home/oracle/u01/1_129_947265431.arc
Media Recovery Waiting for thread 1 sequence 130 (in transit)
Archived Log entry 130 added for thread 1 sequence 130 rlc 947265431 ID 0x6308ffab dest 2:
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for thread 1 sequence 131 dbid 1661358039 branch 947265431
Media Recovery Log /home/oracle/u01/1_130_947265431.arc
Media Recovery Waiting for thread 1 sequence 131 (in transit)
Thu Aug 03 20:05:45 2017
Archived Log entry 131 added for thread 1 sequence 131 rlc 947265431 ID 0x6308ffab dest 2:
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for thread 1 sequence 132 dbid 1661358039 branch 947265431
Thu Aug 03 20:05:48 2017
Media Recovery Log /home/oracle/u01/1_131_947265431.arc
Media Recovery Waiting for thread 1 sequence 132 (in transit)
You have successfully converted physical standby database into Active Dataguard.
Also convert back it into physical standby database using below method:
Step 1: You need to stop MRP process before converting back into physical standby database otherwise you will get below error message.
SQL> alter database close;
alter database close
*
ERROR at line 1:
ORA-10457: cannot close standby database due to active media recovery
SQL> alter database recover managed standby database cancel;
Database altered.
Step 2: Close the database instead of restarting it and check the database status.
SQL> alter database close;
Database altered.
SQL>
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- ------- --------------------
DC DR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED
MAX(SEQUENCE#)
--------------
131
Step 3: Start the MRP process.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Step 4: Switch 2-3 logs from primary database and check logs are getting applied on standby from primary.
On Primary:
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
Standby Alert Log File:
Thu Aug 03 20:17:16 2017
Media Recovery Log /home/oracle/u01/1_132_947265431.arc
Media Recovery Log /home/oracle/u01/1_133_947265431.arc
Media Recovery Log /home/oracle/u01/1_134_947265431.arc
Media Recovery Log /home/oracle/u01/1_135_947265431.arc
Media Recovery Waiting for thread 1 sequence 136 (in transit)
SQL>
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- ------- --------------------
DC DR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED
MAX(SEQUENCE#)
--------------
135