Thursday, June 18, 2009

READ-ONLY standby database in Oracle 11g Data Guard

Oracle 11g came with new feature called active data guard which allows to apply redo
while database is opened for read-only access. This feature is not free and license should
be purchased, that's why I was wondering how it can be managed in dgmgrl command line
to open standby database just in read-only mode w/o active recovery and how to return it back.

I created Data Guard configuration with Oracle 11.1.0.7 on EL 5.2 32-bit
DGMGRL> show configuration verbose

Configuration
Name: bee_dg
Enabled: YES
Protection Mode: MaxPerformance
Databases:
bee - Primary database
beesby - Physical standby database

Fast-Start Failover: DISABLED

Current status for "bee_dg":
SUCCESS

And using dgmgrl commands changed state of standby database to read-only:
DGMGRL> show database beesby

Database
Name: beesby
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
newsby

Current status for "beesby":
SUCCESS

DGMGRL> edit database beesby set state = read-only;
Succeeded.
DGMGRL> show database beesby

Database
Name: beesby
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: READ-ONLY
Instance(s):
newsby

Current status for "beesby":
SUCCESS

In alert log the following entries appeared:
...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
...
ALTER DATABASE OPEN READ ONLY
...

So operation to change standby database to read only state finished okay
but what would happen if to change status to apply-on back.
(Oracle 11g documentation has lack of states to change in DG
"EDIT DATABASE (state)" : OFFLINE, ONLINE, READ-ONLY)

I changed state back to APPLY-ON and that's what I got in alert log:
DGMGRL> edit database beesby set state = apply-on;
Succeeded.

...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
...


Data Guard changed standby database state to active data guard becuase it was not bounced
to mount mode with further start of managed recovery but simply automatic recovery had been
started.
I tried different show commands to get information about active data guard usage
but did not find any that reflects real state of standby database only queries from
v$managed_standby and v$database helped
newsby> select (select process from v$managed_standby where process like 'MRP%'), (select open_mode from v$database) from dual;

(SELECTPR (SELECTOPE
--------- ----------
MRP0 READ ONLY

they exactly told me about active data guard option

But how to put standby database back to mount mode together with managed recovery?
One of the states that can be set is OFFLINE, so I decided to execute a couple of
"edit database" commands to change state to OFFLINE and then to APPLY-ON
DGMGRL> edit database beesby set state = offline;
Operation requires shutdown of instance "newsby" on database "beesby"
Shutting down instance "newsby"...
Database closed.
Database dismounted.
ORACLE instance shut down.

alert.log:
...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
...
Shutting down instance (immediate)
...

Unfortunately all my further attempts to startup standby instance in mount mode using dgmgrl
command "edit database beesby set state = apply-on;" were not successful even I set up static listener configuration and was able to connect through tns name to idle standby instance
DGMGRL> edit database beesby set state = apply-on;
Error: ORA-01034: ORACLE not available

Failed.

Only when I set environment variables for standby database I started instance
simply using "startup mount" in dgmgrl
DGMGRL> edit database beesby set state = apply-on;
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

DGMGRL> startup mount
ORACLE instance started.
Database mounted.

select statements showed me the status that I wanted - mounted with managed recovery
newsby> select (select process from v$managed_standby where process like 'MRP%'), (select open_mode from v$database) from dual;

(SELECTPR (SELECTOPE
--------- ----------
MRP0 MOUNTED


In addition I checked v$option trying to find information about either "Active Data Guard"
or "Real-time Query" options but did not see any of them.
Also it is not clear how to avoid installation of "Active Data Guard" option or how to disable it
if it is not purchased


Have a good day!

2 comments:

Unknown said...

Hi,

Can you please confirm if following steps are ok for to
Put standby database in read only mode and convert it back to recovery managed using data guard broker utility (not active data guard.


- put standby database in read only mode uisng dgmgrl:


DGMGRL> EDIT DATABASE '' SET STATE='READ-ONLY';


- convert from read only to managed recovery(not active data guard) using dgmgrl:


DGMGRL> EDIT DATABASE '' SET STATE='OFFLINE';

which shutdown standby database


DGMGRL>STARTUP MOUNT

DGMGRL> EDIT DATABASE '' SET STATE='APPLY-ON';

Thanks,
ANu.

goryunov said...

Hi Anu,

I think you got those steps right