DBMS_SERVICE.CREATE_SERVICE and JDBC Notes

Version 1

    DBMS_SERVICE.CREATE_SERVICE and JDBC Notes

     

     

    If you use DBMS_SERVICE.CREATE_SERVICE with your Data Guard setup you may run into issues with your application if it requires a JDBC connection. The two main issues I ran into was application client support this and would the application client support a connect descriptor take can handle the new service.  The JDBC string I tested is as follows:

     

    I used SQL Developer as a pretest since everyone should have access to it.

     

    Tested and working with SQL Developer 4.0.0.13

     

    jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = primary.domain)(PORT =1521))(ADDRESS = (PROTOCOL = TCP)(HOST = standby.domain)(PORT = 1521))(LOAD_BALANCE =yes ))(CONNECT_DATA =(SERVICE_NAME = PANADORA)))

     

    Quick Review of DBMS_SERVICE Setup

     

    Create a service using DBMS_SERVICE.CREATE_SERVICE:

     

     

    BEGIN

    DBMS_SERVICE.CREATE_SERVICE('PANADORA','PANADORA');

    END;

    /

     

     

    Manually start the new service:

     

     

    BEGIN

       DBMS_SERVICE.START_SERVICE('PANADORA');

    END;

    /

     

     

    Modify several of the parameters for 'PANADORA':

     

     

    BEGIN

       DBMS_SERVICE.MODIFY_SERVICE

       ('PANADORA',

       FAILOVER_METHOD => 'BASIC',

       FAILOVER_TYPE => 'SELECT',

       FAILOVER_RETRIES => 200,

       FAILOVER_DELAY => 1);

    END;

    /

     

     

     

    Create a database STARTUP trigger that only offers the new service if the database is primary:

     

     

    CREATE TRIGGER CHECK_PRIMARY_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('PANADORA');

    ELSE

    DBMS_SERVICE.STOP_SERVICE('PANADORA');

    END IF;

    END;