How to create a RAC Database Service With Physical Standby Role Option? (Doc ID 1129143.1)

How to create a RAC Database Service With Physical Standby Role Option? (Doc ID 1129143.1)

In this Document
Goal
Solution
References

APPLIES TO:
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.2.0.1 [Release 11.2 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
GOAL
Starting as a new feature in 11gR2 and higher - in the service add command syntax using the srvctl command, there is a new option that can be used with the srvctl add service command which is [-l <primary, physical-standby, logical_standby>], this option allows the service to be able to start up only when the service role matches the database role that is saved in the OCR.

We can now add a service to a database in the OCR using the following command:

srvctl add service -d <db_unique_name> -s <service_name> [-l <primary, physical_standby, logical_standby>]

Some options may not be valid for setup, please consult main document for syntax reference. Note that the -l option is not mandatory

What is generally required is the addition of two services: one to be used when the database is in PRIMARY role for transactions and the other is to be used when the database is in PHYSICAL_STANDBY role for reporting purpose.

SOLUTION
In a dataguard broker with PRIMARY RAC and PHYSICAL_STANDBY RAC configuration, the switchover using the dataguard broker causes the database role to be changed in the OCR.

At the services startup, in such a case, the CRS agent will check the database ROLE attribute that is saved in the OCR, to see if the database role is matching the service role or not, and if a role mismatch has been detected the following message will appear in the agent log file:

2010-06-14 17:52:15.206: [ora...svc][53] [start] SvcAgent::start: not starting service report1. Role mismatch - Service role: physical_standby, current DB role: PRIMARY }

The PHYSICAL_STANDBY role type service status will appear in the OCR, with target ONLINE and status OFFLINE:

ora...svc
1 ONLINE OFFLINE
2 ONLINE OFFLINE
3 ONLINE OFFLINE

Another observation is that the START_DEPENDENCY will show pullup:always if the the service ROLE matches the current database ROLE when it is created, as shown in the following example: service is created as physical_standby role on the standby database :

srvctl add service -d -s -l physical_standby -r

crsctl stat res ora...svc -p shows:

START_DEPENDENCIES=hard(ora..db,type:ora.cluster_vip_net1.type) weak(type:ora.listener.type) pullup(type:ora.cluster_vip_net1.type) pullup:always(ora..db)

It is normal to see in such a case that the dependency on the database resource is showing as pullup:always, and this is what it should be, because the service should always be started, and pullup its depending resource if it was down.

So the recommendation here is that you should always create the standby service while the database is in standby role, so that you get the dependency created correctly, as if the database was in primary role while you are adding a service with standby role, this is going to cause the dependency to change to pullup without always. If the dependency is pullup without always then the desired service will not be automatically started on the physical standby when it is in the physical standby role. If the dependency is created as pullup without always and pullup:always is desired, the service can be modified after creation using 'crsctl modify resource' as in the following example:

crsctl modify resource -attr "START_DEPENDCIES=hard(ora..db,type:ora.cluster_vip_net1.type) weak(type:ora.listener.type) pullup:always (ora..db,type:ora.cluster_vip_net1.type)"

This is the intended behavior of the software in such a case, and there are no discrepancies in the status of the service in the OCR, also the fact that the logs showed in the agent log file at the service startup time while the service role is not matching the database role due to switchover, is also normal.

However, service can be created using above command on standby, but when starting the service on standby, it will fail to start:

$ srvctl add service -d -s -l physical_standby -r ","
$ srvctl start service -d -s
PRCD-1084 : Failed to start service
PRCR-1079 : Failed to start resource ora...svc
CRS-5017: The resource action "ora...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...svc' on '' failed
CRS-5017: The resource action "ora...svc clean" encountered the following error:
ORA-44304: service service1 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

CRS-2632: There are no more servers to try to place resource 'ora...svc' on that would satisfy its placement policy
CRS-5017: The resource action "ora...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...svc' on '' failed
CRS-5017: The resource action "ora...svc clean" encountered the following error:
ORA-44304: service 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 is due to the fact that the database is not open for read/write.
To workaround the issue, please do the following:

In this example, both primary and standby database are RAC.
2 nodes RAC primary () and 2 nodes RAC standby (), they are using same instance names (,).

1. Create the service resource in primary cluster, for example, add service1 and service2

$ $ORACLE_HOME/bin/srvctl add service -d -s -l physical_standby -r ","
$ $ORACLE_HOME/bin/srvctl add service -d -s -l physical_standby -r ","

2. Start this service from primary to allow the services register to the database, then the redo entries will be transferred and applied to the standby DB.

$ $ORACLE_HOME/bin/srvctl start service -d

3. Then stop the service, as it should NOT be running if the DB is in primary mode.

$ $ORACLE_HOME/bin/srvctl stop service -d
$ $ORACLE_HOME/bin/srvctl status service -d
Service service1 is not running.
Service service2 is not running.

Even restart the primary DB with "srvctl start database" command, it would not start the services, unless explicitly start with "srvctl start service" command.

4. Wait until the last log sequence is applied to the standby DB.

5. Create the service resource in the standby site:

$ $ORACLE_HOME/bin/srvctl add service -d -s -l physical_standby -r ","
$ $ORACLE_HOME/bin/srvctl add service -d -s -l physical_standby -r ","

6. Then can start the service in standby site, should see the service registered to the listener

$ $ORACLE_HOME/bin/srvctl start service -d
$ $ORACLE_HOME/bin/srvctl status service -d
Service is running on instance(s) ,
Service is running on instance(s) ,

7. If stop and restart the standby DB by srvctl command now, should see the service started automatically.

$ $ORACLE_HOME/bin/srvctl stop database -d
$ $ORACLE_HOME/bin/srvctl start database -d
$ $ORACLE_HOME/bin/srvctl status service -d
Service is running on instance(s) ,
Service is running on instance(s) ,

Additional Reference

http://docs.oracle.com/cd/E11882_01/rac.112/e41960/hafeats.htm#BABJJCIH

Database Role for a Service

If you configured Oracle Data Guard in your environment, then you can define a role for each service using the -l option. When you specify a role for a service, Oracle Clusterware automatically starts the service only when the database role matches the role you specified for the service. Valid roles are PRIMARY, PHYSICAL_STANDBY, LOGICAL_STANDBY, and SNAPSHOT_STANDBY.

If multiple databases in the cluster offer the same service name, then Oracle RAC balances connections to that service across all such databases. This is useful for standby and active Oracle Data Guard databases, but if you want client connections to a service to be directed to a particular database, then the service name must be unique within the cluster (not offered by any other database).

REFERENCES