This content has been marked as final. Show 5 replies
Because JDBC clients use FCF rather than TAF, db services for JDBC clients are not configured for AQ HA events. Instead a trigger is required to notify JDBC clients when a data
guard failover occurs.
1) Use DBMS_SERVICE.CREATE_SERVICE to create the db service.
2) Configure ONS in $ORACLE_HOME/opmn/conf on all hosts that may contain a primary database refer Oracle Database JDBC developers guide
ernie = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.server)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = standby.server)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ernie) ) )
3) Start the ONS daemon
4) Create the service trigger to relocate the db service after a role transition
5) Create a trigger on the DB_ROLE_CHANGE system event that calls a C program named the FAN ONS Publisher.
JDBC clients are notified of the primary site failure and instructed to reconnect to the new primary database.
refet to the white paper "Client Failover best practices fo Highly Available Oracle Databases: Oracle Database 10g Release 2"
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; /
i've implemented this solution with the trigger.
However i've got one issue that when the listener is reloaded r restarted then the service does not register automatically.
I must restart the service manually.
How can i ensure that the service automatically register when the listener restart ?
I think you must carefully read my friend mseberg reply.
And check my video tutorial : http://www.youtube.com/watch?v=VvYoz9trVnI
Mahir M. Quluzade
The pmon process handles the registration with the listener, and will do so every ~60s or so (dont know the exact interval)
You can manually force the the registration with:
alter system register;