Quick how-to: enable Active Data Guard

If you have the required additional license, with Oracle 11gR1 onward you can open your physical standby database for read-only operations. If correctly configured, it gives you the benefit of relieving the primary database from the burden of running those long, heavy reports users are always wanting.

The database section is very easy to implement:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE;
ALTER DATABASE OPEN;    (it will open read-only, don't worry)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 4 USING CURRENT LOGFILE DISCONNECT;

Now, OPEN_MODE in V$DATABASE should read “READ ONLY WITH APPLY”. That’s it, users are able to connect using the DR database name. But to enhance manageability I recommend that you create a specific service for this usage. The commands are

srvctl add service -d mydb_dr -s mydb_rptg.mydomain.com -r mydb1 -a mydb2 -l PHYSICAL_STANDBY
srvctl start service -d mydb_dr -s mydb_rptg.mydomain.com

If you go and create the service only on the standby side, however, you’ll get an error like I got:

PRCD-1084 : Failed to start service mydb_rptg.mydomain.com
PRCR-1079 : Failed to start resource ora.mydb_dr.mydb_rptg.mydomain.com.svc
CRS-5017: The resource action "ora.mydb_dr.mydb_rptg.mydomain.com.svc start" encountered the following error:
ORA-44317: database open read-only
ORA-06512: at "SYS.DBMS_SERVICE", line 478
ORA-06512: at "SYS.DBMS_SERVICE", line 229
ORA-06512: at line 1

CRS-2674: Start of 'ora.mydb_dr.mydb_rptg.mydomain.com.svc' on 'myDRserver01' failed
CRS-5017: The resource action "ora.mydb_dr.mydb_rptg.mydomain.com.svc clean" encountered the following error:
ORA-44304: service mydb_rptg.mydomain.com does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 452
ORA-06512: at "SYS.DBMS_SERVICE", line 400
ORA-06512: at line 1

This happens because for a service to be activated some records regarding it must exist in the data dictionary of the database, and as the standby cannot write it’s own dictionary this error is raised.

The solution is to briefly enable the same service in the primary database:

srvctl add service -d mydb -s mydb_rptg.mydomain.com -r mydb1 -a mydb2 -l PHYSICAL_STANDBY
srvctl start service -d mydb -s mydb_rptg.mydomain.com
srvctl stop service -d mydb -s mydb_rptg.mydomain.com

By doing so the service will be registered in the database dictionary and the records transferred to standby via log transfer. Then you can enable the service on standby. Also, it’s wise to keep the same set of services in both clusters, so you won’t need to worry about them on a failover or switchover.

The value on the -l switch will make this service enable itself only when the database has the physical standby role.

Documentation references:
Use Oracle Active Data Guard Best Practices
Server Control Utility Reference (srvctl)

Advertisements
This entry was posted in Availability, Oracle and tagged . Bookmark the permalink.

3 Responses to Quick how-to: enable Active Data Guard

  1. Hank Su says:

    I encounter the same scenario, I created the DR service in primary, but it did not go over to standby database, so I am not able to start the service from standby side, is there some setting I can check?
    Thanks,

  2. Hank Su says:

    The issue was solved, it is wonderful solution!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s