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;
Comments