This discussion is archived
5 Replies Latest reply: Dec 17, 2012 12:58 AM by MahirM.Quluzade RSS

Failover option @ jdbc for Dataguard || jdbc connects to wrong DB

803768 Newbie
Currently Being Moderated
hi forum,

I'm trying to understand why jdbc tries to connect to the standby DB, instead of the primary. My jdbc connection is configured like below:
connection-url="jdbc:oracle:thin:@(DESCRIPTION =
                                   (LOAD_BALANCE = no)(FAILOVER = yes)
                                   (ADDRESS = (PROTOCOL = TCP)(HOST = tstdg1)(PORT = 1521))
                                   (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tstdg))
                                   (ADDRESS = (PROTOCOL = TCP)(HOST = tstdg2)(PORT = 1521))
                                   (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tstdg_stby))
                                   (FAILOVER_MODE = (type = select)(method = preconnect)(retries = 180)(delay = 10)))"

tstdg1 is the primary DB, whereas tstdg2 is the Dataguard physical standby.
The problem is that jdbc connects to the standby DB instead of the primary one. Is there any option which I can configure to jdbc let always connect to the primary DB first? And only after the primary DB isn't accessible, jdbc should use the standby DB?

thx in advance
  • 1. Re: Failover option @ jdbc for Dataguard || jdbc connects to wrong DB
    mseberg Guru
    Currently Being Moderated
    Hello;

    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.

    Example
    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)
        )
     )
    2) Configure ONS in $ORACLE_HOME/opmn/conf on all hosts that may contain a primary database refer Oracle Database JDBC developers guide

    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"

    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-clientfailoverbestprac-129636.pdf

    OR

    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf

    Trigger Example
    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;
    /
    Best Regards

    mseberg
  • 2. Re: Failover option @ jdbc for Dataguard || jdbc connects to wrong DB
    user11243557 Newbie
    Currently Being Moderated
    hello,
    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 ?


    regards
  • 3. Re: Failover option @ jdbc for Dataguard || jdbc connects to wrong DB
    MahirM.Quluzade Guru
    Currently Being Moderated
    Hi,

    I think you must carefully read my friend mseberg reply.
    And check my video tutorial : http://www.youtube.com/watch?v=VvYoz9trVnI



    Best regards
    Mahir M. Quluzade
    www.mahir-quluzade.com
  • 4. Re: Failover option @ jdbc for Dataguard || jdbc connects to wrong DB
    944515 Newbie
    Currently Being Moderated
    Hi,

    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;

    regards
    /M

Legend

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