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:

<br />
srvctl add service -db orclsby -service payroll -role physical_standby<br />
srvctl start service -db orclsby -service payroll<br />
PRCD-1084 : Failed to start service payroll<br />
PRCR-1079 : Failed to start resource ora.orclsby.payroll.svc<br />
CRS-5017: The resource action &quot;ora.orclsby.payroll.svc start&quot; encountered the following error:<br />
ORA-44317: database open read-only<br />
ORA-06512: at &quot;SYS.DBMS_SERVICE_ERR&quot;, line 53<br />
ORA-06512: at &quot;SYS.DBMS_SERVICE&quot;, line 192<br />
ORA-06512: at line 1<br />
. For details refer to &quot;(:CLSN00107:)&quot; in &quot;/oracle/app/oracle/diag/crs/dg12cocmu-pc2/crs/trace/ohasd_oraagent_oracle.trc&quot;.</p>
<p>CRS-2674: Start of 'ora.orclsby.payroll.svc' on 'dg12cocmu-pc2' failed</p>
<p>

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:

<br />
srvctl add service -db orcl -service payroll -role physical_standby<br />
srvctl start service -db orcl -service payroll<br />
srvctl stop service -db orcl -service payroll<br />

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

Standby DB:

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

 

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.