RE: dataguard connection question

From: Mathias Zarick <Mathias.Zarick_at_trivadis.com>
Date: Wed, 10 Jun 2009 10:15:03 +0200
Message-ID: <370BF313301A024C962B05768686368301BD18D2_at_MSXVS04.trivadis.com>

Hi Steve, Joan,

First here is the white paper:
http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_Cl ientFailoverBestPractices.pdf
Clarification: TAF does not work for jdbc thin. Connect time failover works.
That is, if the jdbc thin tries to connect and gets an error from first server it tries second. Of course, if session dies, app has to reconnect, but can take same jdbc url ...
the service XYTHT_RW is only active on the database that is opened read write (you remember the logon trigger). A service XYTHT_RO can be used for reporting if you open standby read only.
Do net set service_names in init.ora manually, the dbms_service.start_service will do this dynamically for you. Remember to set the local_listener parameter, to allow the newly started service to be announced to the listener.

again the example for jdbc thin
String url =
"jdbc:oracle:thin:_at_(DESCRIPTION=(FAILOVER=ON)(LOADBALANCE=OFF)"
+ "(ADDRESS_LIST="
+ "(ADDRESS=(PROTOCOL=TCP)(HOST=test-xythdb-01)(PORT=11003))"
+ "(ADDRESS=(PROTOCOL=TCP)(HOST=test-xythdb-02)(PORT=11003))"
+ ")"
+ "(CONNECT_DATA=(SERVICE_NAME=XYTHT_RW)))";
no special things in tnsnames.ora regarding jdbc thin connection support.
Nevertheless, if you have oci clients that connect, this would be the client
connect they should use, and for them TAF works:

XYTHT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-xythdb-01 )(PORT = 11003 ))

(ADDRESS = (PROTOCOL = TCP)(HOST = test-xythdb-02 )(PORT = 11003 ))

    )
    (CONNECT_DATA =
      (SERVICE_NAME = XYTHT_RW )
    )
  )

HTH Mathias

-----Original Message-----
From: Steve Baldwin [mailto:stbaldwin_at_multiservice.com] Sent: Tuesday, June 09, 2009 10:57 PM
To: Mathias Zarick
Cc: Joan Hsieh; oracle_l
Subject: Re: dataguard connection question

In my experience TAF is only useful in certain circumstances anyway. When a session is migrated to another node as part of 'automatic failover', no SGA session state is migrated with it. Also, if your session has active transactions you will get an Oracle error during the migration. Assuming this is not the case, if you make any use of PL/SQL global variables to maintain any sort of session state, when your session is failed over, you will lose that state and Oracle will not notify you (via an error message at migration time) that this has happened. You need to detect it yourself and make the appropriate remedy. This can add considerable complexity to your application.

Steve

On 10/06/2009, at 3:13 AM, Mathias Zarick wrote:

> Hi Joan,
>
> yes this is okay.
> I prefer setting it without the need of configuring listeners in a 
> tnsnames.ora.
> so i would use
> alter system set local_listener =
> '(ADDRESS=(PROTOCOL=TCP)(HOST=test-xythdb-01)(PORT=11003))';
> on the other node
> alter system set local_listener =
> '(ADDRESS=(PROTOCOL=TCP)(HOST=test-xythdb-02)(PORT=11003))';
>
> u can also use
> alter system set local_listener =
> '(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=11003))';
> on both nodes, this is more generic
>
> HTH Mathias
>
> -----Original Message-----
> From: Joan Hsieh [mailto:joan.hsieh_at_tufts.edu]
> Sent: Tuesday, June 09, 2009 5:17 PM
> To: Mathias Zarick
> Cc: oracle_l
> Subject: Re: dataguard connection question
>
> Thank you Mathias,
>
> Our local listener set different on both servers as below, is that 
> right? should I change it the same "LISTENER_XYTHT"?
>
> Thanks so much for your help. we scheduled go live on July. I am 
> toasted.
>
>  On test-02
>
>  LISTENER_XYTHT2 =
>    (ADDRESS = (PROTOCOL = TCP)(HOST =
> test-xythdb-02.uit.tufts.edu)(PORT = 11003))
>
>  On test-01
>
>  LISTENER_XYTHT1 =
>    (ADDRESS = (PROTOCOL = TCP)(HOST =
> test-xythdb-01.uit.tufts.edu)(PORT = 11003))
>
>
>
> Mathias Zarick wrote:
>> Hi Joan,
>>
>> the trick is like this:
>> It should also be described in on of the MAA White Papers.
>> Use an after startup on database trigger that starts up a service
> after
>> opening read write.
>> Use this service and both host in jdbc connect url.
>> It is important that dynamic service registration is able to connect
> to
>> local listener,
>> so local_listener init.ora Parameter has to be adapted correctly...
>>
>>
>> alter system set local_listener =
>> '(ADDRESS=(PROTOCOL=TCP)(HOST=test-xythdb-01)(PORT=11003))';
>>
>>
>> examples:
>>
>> exec DBMS_SERVICE.CREATE_SERVICE ( -
>>  service_name => 'XYTHT_RW', -
>>  network_name => 'XYTHT_RW', -
>>  failover_method => 'BASIC', -
>>  failover_type => 'SESSION', -
>>  failover_retries => 3600, -
>>  failover_delay => 1);
>>
>> exec DBMS_SERVICE.CREATE_SERVICE ( -
>>  service_name => 'XYTHT_RO', -
>>  network_name => 'XYTHT_RO', -
>>  failover_method => 'BASIC', -
>>  failover_type => 'SESSION', -
>>  failover_retries => 3600, -
>>  failover_delay => 1);
>>
>>
>> CREATE OR REPLACE TRIGGER service_trigger  after startup on database 
>> DECLARE
>>  db_name       VARCHAR(9);
>>  db_domain     VARCHAR(128);
>>  database_role VARCHAR(30);
>> BEGIN
>>  SELECT value
>>    INTO db_name
>>    FROM v$parameter
>>      WHERE name = 'db_name';
>>
>>  SELECT value
>>    INTO db_domain
>>    FROM v$parameter
>>      WHERE name = 'db_domain';
>>
>>  SELECT database_role
>>    INTO database_role
>>    FROM v$database;
>>
>>  IF database_role = 'PRIMARY' THEN
>>    dbms_service.start_service(rtrim(db_name||'_RW.'||db_domain,'.'));
>>  ELSE
>>    dbms_service.start_service(rtrim(db_name||'_RO.'||db_domain,'.'));
>>  END IF;
>> END;
>> /
>>
>> jdbc snippets:
>> String url =
>> "jdbc:oracle:thin:_at_(DESCRIPTION=(FAILOVER=ON)(LOADBALANCE=OFF)"
>> + "(ADDRESS_LIST="
>> + "(ADDRESS=(PROTOCOL=TCP)(HOST=test-xythdb-01)(PORT=11003))"
>> + "(ADDRESS=(PROTOCOL=TCP)(HOST=test-xythdb-02)(PORT=11003))"
>> + ")"
>> + "(CONNECT_DATA=(SERVICE_NAME=XYTHT_RW)))";
>> ds = new oracle.jdbc.pool.OracleDataSource();
>> ds.setURL(url);
>> conn = ds.getConnection(user, password);
>>
>> HTH Mathias
>>
>>
>>
>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org
>> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joan Hsieh
>> Sent: Tuesday, June 09, 2009 1:35 AM
>> To: oracle_l
>> Subject: dataguard connection question
>>
>> Hi List,
>>
>> I have question regarding dataguard, the primary database is xytht1,
> the
>> standby is xytht2. the oracle is 11.1.0.7. Our client is using jdbc
> thin
>> client to connect the primary database. the connection string is
>>
>>
> jdbc:oracle:thin:_at_(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-
> xythdb-
>> 01.uit.tufts.edu)(PORT=11003))(CONNECT_DATA=(SERVICE_NAME=XYTHT1)))
>> JDBCConnectionPool:395
>>
>> After I tested switchover, the client lost connection, and have to 
>> manually reconfigure to change the HOST and service name. This is not

>> acceptable to our client. I have no knowledge on how to automatically

>> switchover/failover for the client jdbc connection. Do you have any 
>> ideas on this? Thank you for any helps.
>>
>> Joan
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>


------------------------

This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 10 2009 - 03:15:03 CDT

This message: [ Message body ]
Next message: Joerg Jost: "Wrong execution plan"
Previous message: Frits Hoogland: "Re: Oracle 10g installation on OUL 5"
Maybe in reply to: Joan Hsieh: "dataguard connection question"
Next in thread: Mathias Zarick: "Re: dataguard connection question"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message