DBMS_SERVICE.STOP_SERVICE does not stop Services when Database opened as Read Only or is a Standby Database (Doc ID 838438.1)

Last updated on JUNE 22, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4
Oracle Net Services - Version: 9.2.0.1 to 10.2.0.4
This problem can occur on any platform.

Symptoms

When you put the STANDBY database in READ ONLY mode and execute the following package:
On the STANDBY DATABASE: 
1- Connect to the STANDBY and then run the following in SQL Plus:

    
    
    
SQL> alter database open read only;
 
2- Then Start the Service:

    
    
    
    SYSTEM_STST>exec DBMS_SERVICE.START_SERVICE('COFISTST_AGENT');
PL/SQL procedure successfully completed.

You now see that this service is present in the services of the Standby listener when checking the status:

      
      
      
lsnrctl status

 
LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 16-JAN-2009 14:
13:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XX.XXX.YYY)(PORT=
1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - 
Production
Start Date                16-JAN-2009 13:49:23
Uptime                    0 days 0 hr. 23 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\ORACLE\ORA102\network\admin\listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.YYY)(PORT=1521)))
Services Summary...
Service "COFISTST_AGENT" has 1 instance(s). ==> You can see that the service is open.
  Instance "stst", status READY, has 1 handler(s) for this service...
Service "STST.world" has 1 instance(s).
  Instance "STST", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

  


3- Then you stop the service:

      
      
      
SYSTEM_STST>exec DBMS_SERVICE.STOP_SERVICE('COFISTST_AGENT') 
However, you see that the service is still showing up with the listener:

          
          
          
lsnrctl status 


LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 16-JAN-2009 14:
15:51

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XX.XXX.YYY)(PORT=
1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - 
Production
Start Date                16-JAN-2009 13:49:23
Uptime                    0 days 0 hr. 26 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\ORACLE\ORA102\network\admin\listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.YYY)(PORT=1521)))
Services Summary...
Service "STANDBY1_STST" has 1 instance(s).
  Instance "stst", status READY, has 1 handler(s) for this service...
Service "STST.world" has 1 instance(s).
  Instance "STST", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

-->> The service is still open and you can still connect to the standby database
through this service so the question is why ...




P.S. You will see the same behavior if you invoke the following command as well:
SYSTEM_STST>exec DBMS_SERVICE.DISCONNECT_SESSION('COFISTST_AGENT');
PL/SQL procedure successfully completed
4- 
So The only way to stop the service is to shutdown the standby database:

          
          
          
shutdown immediate
startup

Now when you run the listener status command on the Standby listener, you will see:

          
          
          
lsnrctl status


LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 16-JAN-2009 14:
19:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XX.XXX.YYY)(PORT=
1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - 
Production
Start Date                16-JAN-2009 13:49:23
Uptime                    0 days 0 hr. 30 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\ORACLE\ORA102\network\admin\listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.YYY)(PORT=1521)))
Services Summary...
Service "STST.world" has 1 instance(s).
  Instance "STST", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
==> The service is now stopped.


So why do these two packages (DBMS_SERVICE.STOP_SERVICE and DBMS_SERVICE.DISCONNECT_SESSION)
not stop the services on a Standby or Read Only Database?

Changes

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms