Creating ReadOnly service in Oracle ADG (Active DataGuard)

Every setup generally has a problem of developers connecting to active (or Primary) instance to run a few selects to know the status of their transactions or may be a sort of Health check.

If you have an Active DataGuard setup, create a service that runs only at standby site, and route all your reads (SELECTs) to offload the traffic and buffer utilisation at Primary site.

Let us see how to do that..

Login to Primary site:

oracle-pr>srvctl add service -s REPORT_DR_ONLY -d RACCDB -preferred RACCDB1,RACCDB2 -role PHYSICAL_STANDBY

oracle-pr>srvctl status service -d RACCDB
Service REPORT_DR_ONLY is not running.

oracle-pr> srvctl start service -s REPORT_DR_ONLY -d RACCDB
oracle-pr> srvctl status service -d RACCDB
Service REPORT_DR_ONLY is running on instance(s) RACCDB1,RACCDB2

Now login to DR site:

oracle-dr> srvctl add service -s REPORT_DR_ONLY -d RACCDB_AS01 -preferred RACCDB1,RACCDB2 -role PHYSICAL_STANDBY
oracle-dr> srvctl status service -d raccdb_as01
Service REPORT_DR_ONLY is not running.

Now the interesting part is, since this is ReadOnly service which is suppose to run only at DR site, anyone would first try to start the service only at DR site.

You will face below issue if the service is planned to start first in DR site.

oracle-dr> srvctl start service -s REPORT_DR_ONLY -d RACCDB_AS01
PRCD-1084 : Failed to start service REPORT_DR_ONLY
PRCR-1079 : Failed to start resource ora.raccdb_as01.report_dr_only.svc
CRS-5017: The resource action "ora.raccdb_as01.report_dr_only.svc start" encountered the following error:
ORA-44317: database open read-only
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 53
ORA-06512: at "SYS.DBMS_SERVICE", line 192
ORA-06512: at line 1
. For details refer to "(:CLSN00107:)" in "/app/oracle/diag/crs/dbgenre-r3/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.raccdb_as01.report_dr_only.svc' on 'dbgenre-3' failed
CRS-2632: There are no more servers to try to place resource 'ora.raccdb_as01.report_dr_only.svc' on that would satisfy its placement policy
CRS-5017: The resource action "ora.raccdb_as01.report_dr_only.svc start" encountered the following error:
ORA-44317: database open read-only
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 53
ORA-06512: at "SYS.DBMS_SERVICE", line 192
ORA-06512: at line 1
. For details refer to "(:CLSN00107:)" in "/app/oracle/diag/crs/dbgenre-r4/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.raccdb_as01.report_dr_only.svc' on 'dbgenre-r4' failed

So first start the service in Primary site ones and then bring it down. By doing this we make some sys entries to register the service to the database at primary site and it will be replicated to DR.

After doing this you should be able to start the service at DR site.

Go to Primary site:

oracle-pr> srvctl start service -s REPORT_DR_ONLY -d RACCDB
oracle-pr> srvctl stop service -s REPORT_DR_ONLY -d RACCDB

Now start the service in DR site:

oracle-dr> srvctl start service -s REPORT_DR_ONLY -d RACCDB_AS01

oracle-dr> srvctl status service -d RACCDB_AS01
Service REPORT_DR_ONLY is running on instance(s) RACCDB1,RACCDB2

Hope this post was helpful.

Regards,

Balaaji Dhananjayan