Active Services on Physical Standby Database

As we know, in Data Guard environment we use SRVCTL utility to define services to be active in specific database role. If we want to define some service to be active when the database is in primary role we only need to create the service with SRVCTL utility and set the -role parameter to PRIMARY. Then, when the database transitions to primary database the service will be started.

But, if we want to have active service when the database is in physical standby database role we need to make additional definitions on the primary database.

In order to have active service on standby physical database the service must be also defined on the primary database regardless of whether it will be active or not on the primary database.

Standby DB:

srvctl add service -db orclsby -service payroll -role physical_standby
srvctl start service -db orclsby -service payroll
PRCD-1084 : Failed to start service payroll
PRCR-1079 : Failed to start resource ora.orclsby.payroll.svc
CRS-5017: The resource action "ora.orclsby.payroll.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 "/oracle/app/oracle/diag/crs/dg12cocmu-pc2/crs/trace/ohasd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.orclsby.payroll.svc' on 'dg12cocmu-pc2' failed

The service payroll couldn’t be started because redo definition for the service has not been propagated to the standby database (missing information). The service can be started on the physical standby database only after the redo generated by starting of the service has been applied on the physical standby database. For that purpose, first we have to create/define the service on primary database before trying to start the service on physical standby database.

Primary DB:

srvctl add service -db orcl -service payroll -role physical_standby
srvctl start service -db orcl -service payroll
srvctl stop service -db orcl -service payroll

Now, we can try to start the service on the standby database.

Standby DB:

srvctl start service -db orclsby -service payroll
lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-DEC-2015 00:04:30

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=145.32.64.168)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 17-DEC-2015 16:43:18
Uptime 0 days 7 hr. 21 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0/grid/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/dg12cocmu-pc2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=145.32.64.168)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hr.oralab" has 1 instance(s).
Instance "orclsby", status READY, has 1 handler(s) for this service...
Service "orclsby.oralab" has 2 instance(s).
Instance "orclsby", status UNKNOWN, has 1 handler(s) for this service...
Instance "orclsby", status READY, has 1 handler(s) for this service...
Service "orclsbyXDB.oralab" has 1 instance(s).
Instance "orclsby", status READY, has 1 handler(s) for this service...
Service "orclsby_DGB.oralab" has 1 instance(s).
Instance "orclsby", status READY, has 1 handler(s) for this service...
Service "payroll.oralab" has 1 instance(s).
Instance "orclsby", status READY, has 1 handler(s) for this service...
The command completed successfully

 

Update (29.7.2017):

You can start services on physical standby only if the standby database is opened in READ-ONLY (Active Data Guard) mode.
See Simon’s comment.

4 thoughts on “Active Services on Physical Standby Database

  1. Nice article. However, for clarity it should be noted that this only works with Active Data Guard where the standby is opened with Real Time Query. A normal physical standby without RTQ will give the error:

    “CRS-2800: Cannot start resource ‘ora.stby.db’ as it is already in the INTERMEDIATE state on server ‘racserver1′”

Leave a Reply