DBMS_SERVICE.CREATE_SERVICE and JDBC Notes

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;