RE: Service registration after listener restart
Date: Fri, 28 Jan 2011 09:19:02 -0600
Message-ID: <E37E556CF8A6C44381D2DA9FC354D24102A1FB479592_at_EVS03.ad.uchicago.edu>
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,
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=host_A)(PORT=1522))
(ADDRESS=(PROTOCOL=TCP)(HOST=host_B)(PORT=1522))
)
(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.
Thanks,
Mike
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Dinh
Sent: Thursday, January 27, 2011 1:07 PM
To: Michael Schmitt; oracle-l_at_freelists.org
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 11.2.0.2.0 - Production on 27-JAN-2011 11:01:30
Copyright (c) 1991, 2010, Oracle. All rights reserved.
STATUS of the LISTENER
Alias listener_dbtest Version TNSLSNR for Solaris: Version 11.2.0.2.0 - 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/11.2.0.2/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/proddbtest/listener_dbtest/alert/log.xmlListening 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
oracle_at_proddbtest:dbtest:/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs
> sysdba
SQL*Plus: Release 11.2.0.2.0 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 11.2.0.2.0 - 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_dbtestSYS_at_dbtest:PRIMARY> show parameter service
NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ service_names string dbtestSYS_at_dbtest:PRIMARY>
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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Schmitt
Sent: Thursday, January 27, 2011 10:54 AM
To: oracle-l_at_freelists.org
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.
Thanks,
Mike
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 28 2011 - 09:19:02 CST