This discussion is archived
2 Replies Latest reply: Dec 31, 2012 8:57 PM by 857396 RSS

how to use the default database service name on creating procedure for data

857396 Newbie
Currently Being Moderated
how to use the default database service name on creating procedure for datagaurd client failover ??? all oracle doc says create a new service as below and enable at DB startup. but our client is using/wanted database default service to connect from application on the datagaurd environment (rac to non rac setup).please help.


Db name is = prod.

exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'prod',network_name =>'prod',failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);

says already the service available.


CREATE OR REPLACE TRIGGER manage_dgservice after startup on database DECLARE role
VARCHAR(30);BEGIN SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'NO' THEN DBMS_SERVICE.START_SERVICE('prod');
END IF;
END;


says trigger created, but during a swithover still the service is listeneing on listener.

tns entry.

prod =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = YES)
(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = prod2)(PORT = 1521)) ---> primary db entry
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = proddr)(PORT = 1521)) --> DR DB entry
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)


thanks in advance.

Edited by: 854393 on Dec 29, 2012 11:52 AM
  • 1. Re: how to use the default database service name on creating procedure for data
    mseberg Guru
    Currently Being Moderated
    Hello;

    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.

    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)
        )
     )
    Next the service 'ernie' needs to be created manually on the primary database.
     
    
    BEGIN
     
       DBMS_SERVICE.CREATE_SERVICE('ernie','ernie');
    
    END;
    /
    After creating the service needs to be manually started.
     BEGIN
     
       DBMS_SERVICE.START_SERVICE('ernie');
     
     END;
     /
    Several of the default parameters can now be set for 'ernie'.
    BEGIN
       DBMS_SERVICE.MODIFY_SERVICE
       ('ernie',
       FAILOVER_METHOD => 'BASIC',
       FAILOVER_TYPE => 'SELECT',
       FAILOVER_RETRIES => 200,
       FAILOVER_DELAY => 1);
    END;
    /
    Finally a database STARTUP trigger should be created to ensures that this service is only offered if the database is primary.



    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;
    /
    lsnrctl status - should show the new service.


    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.

    Best Regards

    mseberg

    Edited by: mseberg on Dec 29, 2012 3:51 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points