Active Data Guard is a good new feature in 11g (although requires a license) which enables us to query the Standby database while redo logs are being applied to it. In earlier releases, we had to stop the log apply, open the database in read only mode and then start the log apply again when the database was taken out of the read only mode.
With Oracle 11g Active Data Guard, we can make use of our standby site to offload reporting and query type applications while at the same time not compromising on the high availability aspect.
How do we enable Active Data Guard?
If we are not using the Data Guard Broker, we need to open the standby database, set it in read only mode and then start the managed recovery as shown below.
SQL> shutdown immediate;
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size 2154936 bytes
Variable Size 847257160 bytes
Database Buffers 213909504 bytes
Redo Buffers 5931008 bytes
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
If we are using the Data Guard Broker CLI, DGMGRL, the procedure is a bit different and is not very clearly explained in the documentation.
You need to stop redo apply first via the SET STATE dgmgrl command, then from a SQL*PLUS session, open the database in read only mode, and then back again from dgmgrl via set SET STATE command, start the redo apply again.
Stop redo apply with the following command from Data Guard Broker CLI
DGMGRL> EDIT DATABASE ‘PRODDB’ SET STATE=’APPLY-OFF’;
Open standby read-only via SQL*Plus
SQL> alter database open read only;
Restart redo apply via broker CLI
DGMGRL> EDIT DATABASE ‘PRODDB’ SET STATE=’APPLY-ON’;
I tried to run the same only via DGMGRL and got this error:
DGMGRL> edit database PRODDB set state=”APPLY-OFF”;
DGMGRL> edit database PRODDB set state=”READ ONLY”;
Error: ORA-16516: current state is invalid for the attempted operation
After we have enabled the Real-Time Query feature, we can confirm the same via the DGMGRL command – SHOW DATABASE
DGMGRL> show database verbose PRODDB_DR
Database – PRODDB_DR
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Even though we have enabled Real-Time Query feature, if we go to Data Guard page via the Enterprise Manager Grid Control GUI, it will show that Real-Time Query is in a Disabled state.
This is apparently a bug which applies to OEM Grid Control 10.2.0.1 to 10.2.0.5 with a 11.2 target database.
Bug 7633734: DG ADMIN PAGE REAL TIME QUERY SHOWS DISABLED WHEN ENABLED FOR 11.2 DATABASES