This content has been marked as final. Show 2 replies
So in the example below replace "ernie" with the alias you want the client to use.
I can show you how I do it :
First an entry need to be added to the client tnsnames.ora that uses a SERVICE_NAME instead of a SID.
Next the service 'ernie' needs to be created manually on the primary database.
ernie = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Primary.host)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = Standby.host)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ernie) ) )
After creating the service needs to be manually started.
BEGIN DBMS_SERVICE.CREATE_SERVICE('ernie','ernie'); END; /
Several of the default parameters can now be set for 'ernie'.
BEGIN DBMS_SERVICE.START_SERVICE('ernie'); END; /
Finally a database STARTUP trigger should be created to ensures that this service is only offered if the database is primary.
BEGIN DBMS_SERVICE.MODIFY_SERVICE ('ernie', FAILOVER_METHOD => 'BASIC', FAILOVER_TYPE => 'SELECT', FAILOVER_RETRIES => 200, FAILOVER_DELAY => 1); END; /
lsnrctl status - should show the new service.
CREATE TRIGGER CHECK_ERNIE_START AFTER STARTUP ON DATABASE DECLARE V_ROLE VARCHAR(30); BEGIN SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE; IF V_ROLE = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('ernie'); ELSE DBMS_SERVICE.STOP_SERVICE('ernie'); END IF; END; /
When I do this the Database will still register with the listener. I don't give that to the clients. That one will still be available but nobody knows about it. Meanwhile "ernie" moves with the database role.
So in my example the default just hangs out in the background.
Edited by: mseberg on Dec 29, 2012 3:51 PM