RE: Service registration after listener restart

From: Michael Schmitt <>
Date: Fri, 28 Jan 2011 09:19:02 -0600
Message-ID: <>

Hi Michael,

I had something similar to what you listed originally, but the problem I was having was with client connections after role switches

For example,


    (CONNECT_DATA = (SERVICE_NAME= dbtest))   )

If my database was setup the same as your example, I found that the dbtest service would still run on host_A even if the database running on host_A was the standby (after a switchover). When the users would try to connect with the above tnsnames entry, they would first go to host_A which found the service running but then would fail to connect because the database was mounted. They would never go over to host_B which was the new primary. The way I am trying to work around this was to create a new service that would get set based on a system trigger that first confirmed that this system was the primary (exec dbms_service.start_service('new_service','instance'). However, if the listener is stopped/started, that service that was set via dbms_service then goes away. I believe this is due to the listener registration only happening with the trigger execution or being set manually.


-----Original Message-----
From: [] On Behalf Of Michael Dinh Sent: Thursday, January 27, 2011 1:07 PM To: Michael Schmitt; Subject: RE: Service registration after listener restart

db_unique_name is used for dataguard only

service_name is used for connecting via tnsnames.ora and etc.

FYI: I have removed the hostname and port information for listener display

This is just one simple solution. Not sure how I would handle multiple services.

LSNRCTL for Solaris: Version - Production on 27-JAN-2011 11:01:30

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


Alias                     listener_dbtest
Version                   TNSLSNR for Solaris: Version - Production
Start Date                22-NOV-2010 13:54:25
Uptime                    65 days 21 hr. 7 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/
Listener Log File         /u01/app/oracle/diag/tnslsnr/proddbtest/listener_dbtest/alert/log.xml
Listening Endpoints Summary...
Services Summary...
Service "dbtest" has 1 instance(s).
  Instance "dbtest", status READY, has 1 handler(s) for this service... Service "savvis_dbtest" has 2 instance(s).   Instance "dbtest", status UNKNOWN, has 1 handler(s) for this service...   Instance "dbtest", status READY, has 1 handler(s) for this service... The command completed successfully

> sysdba

SQL*Plus: Release Production on Thu Jan 27 11:02:03 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS_at_dbtest:PRIMARY> show parameter db_unique

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_unique_name                       string                           savvis_dbtest
SYS_at_dbtest:PRIMARY> show parameter service
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
service_names                        string                           dbtest

Michael Dinh  

NOTICE OF CONFIDENTIALITY - This material is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this email (and attachments) is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. Please notify the sender of the error and delete the e-mail you received. Thank you.

-----Original Message-----
From: [] On Behalf Of Michael Schmitt Sent: Thursday, January 27, 2011 10:54 AM To:
Subject: Service registration after listener restart

Hi All,

We have setup Dataguard with a physical standby running 11.2. We created a service that we want to run on the primary for the users to connect to. We have a system trigger that will register the service with the listener for the primary after the database opens. I am registering the service with dbms_service.start_service('<SERVICE>','<INSTANCE>')

This seems to work fine, but if the listener is ever shutdown and restarted, the service will no longer be configured and we need to execute dbms_service again.

Is there a way where I can get that service to run on the primary through a listener restart? I was just going to write a script to monitor it, but was wondering if there was a different way. I can't configure it in the listener.ora since I do not want it to run if the database ever becomes the standby.




Received on Fri Jan 28 2011 - 09:19:02 CST

Original text of this message