Re: Question for Dataguard users

The best guide for this has usually been:
Client Failover Best Practices for Highly Available Oracle Databases: Oracle Database 11g Release 2

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

it covers how to make JDBC urls as well as TNS entries that have both the primary and standby in the same entry.
The JDBC client must set the oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR property. This property enables the JDBC client to quickly traverse an ADDRESS_LIST in the event of a failure.

For example, if the client attempts to connect to a host that is unavailable, the connection attempt will be bounded to the time specified by the SQLnetDef.TCP_CONNTIMEOUT_STR property after which the client attempts to connect to the next host in the ADDRESS_LIST. The behavior continues for each host in the ADDRESS_LIST until a connection is made. Setting the property to a value of 3 seconds will suffice in most environments. It is important to note that the SQLnetDef.TCP_CONNTIMEOUT_STR property should be set on the data source and not on the implicit connection cache.




________________________________
From: "japplewhite_at_austinisd.org" <japplewhite_at_austinisd.org>
To: oracle-l-freelists <oracle-l_at_freelists.org>
Cc: oracle-l-bounce_at_freelists.org; RStorey@DCSO.nashville.org
Sent: Wednesday, July 27, 2011 5:17 PM
Subject: RE: Question for Dataguard users


We (IT), not Us (DBAs), do control our
own LAN / WAN, so it works very easily for us.  From what I understand
it is relatively trivial to apply / transfer a DNS alias to / from a server
so maybe the other IT shop won't balk.  We (DBAs) do control the DB
Service_Names, so that part is no problem. 

Yes, if we have a DB of SID=db01 on
host srvr01, we'll have a DNS alias of db01 applied to server srv01.  The
entry in any TNSNames.ora would have HOST=db01 and Service_Name=db01.  If
we also had a Service_Name of  ABC on the db01 database, we'd have
a DNS alias abc on server srv01.  Entries in TNSNames.ora files would
have HOST=abc and Service_Name=abc. 

The caveat with Standbys is to use HOST=srv01
- the real hostname - in the Listener.ora file.  I found that out
the hard way when I had the DNS alias as HOST in a Primary Listener.ora
file.  When the DNS alias was moved to the Standby and I went to shut
down the Listener on the Primary, the Listener on the Switched Over Standby
went down.  The lsnrctl utility obediently followed the DNS alias
as well and shut that Listener down remotely.  Now all the Listener.ora
files use HOST=<TheRealHostname>. 

It may seem complex at first, but the
flexibility and not having to fool around with edits to TNSNames.ora files
are well worth the setup. 

Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)  /  512.935.5929 (pager)
 



From:      
 "Storey, Robert
(DCSO)" <RStorey_at_DCSO.nashville.org> 
To:      
 <japplewhite_at_austinisd.org>,
oracle-l-freelists <oracle-l_at_freelists.org> 
Date:      
 07/27/2011 03:22 PM 
Subject:    
   RE: Question
for Dataguard users 
Sent by:    
   oracle-l-bounce_at_freelists.org 
________________________________
 


Excellent approach. 
Not sure if it will work for me since our department does not have any
control over out network.  We (the IT shop) have to work through the
city IT shop to get anything network wise done.  Still doable, just
adds a layer of futzing to the issue. 
  
So what do you put in the
HOST field of the tnsnames?  So, you have a database on host ABC that
has a database of DB01.  You create a DNS entry called DB01 that points
to host ABC?  So, in the HOST field do of tnsnames  you would
put DB01? 
  
Thanks 
  
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of japplewhite@austinisd.org
Sent: Wednesday, July 27, 2011 3:06 PM
To: oracle-l-freelists
Subject: Re: Question for Dataguard users 
  
Robert,

We handle that via DNS aliases.  We never use the actual hostname
in any TNSNames.ora file or JDBC or other connect string.  We have
our Sys Admins add a DNS alias for every database on a server.  It's
exactly the same as the Service_Name for the database.  We may have
several Service_Names per actual database.  There is a DNS alias for
each of those Service_Names on the host server.  That allows us to
start a new Vendor App as a schema in an existing database (with its own
Service_Name and host DNS alias) and see if it comes to eventually need
its own database.  If so, or we just want to move the schema, we move
the Service_Name and DNS alias to the new DB and server and all connect
strings continue to work.

The same thing works for Switchovers / Failovers to Standbys from Primarys.
 When we do a Switch / Failover, we just prep the DBs, have the Sys
Admins switch the DNS aliases.  Then all Clients can reconnect to
the new Primary.  No muss, no fuss.  Works great.

Jack C. Applewhite - Database Administrator

From:        "Storey,
Robert (DCSO)" <RStorey_at_DCSO.nashville.org>
To:        oracle-l-freelists
<oracle-l_at_freelists.org>
Date:        07/27/2011
12:44 PM
Subject:        Question
for Dataguard users
Sent by:        oracle-l-bounce_at_freelists.org 

________________________________


Okay, have a question for the dataguard users.  Probably more for
the non-RAC folks than anything.
 
My setup is a primary and a single physical standby.
 
In the event of a switchover, all user connections are broken and the switch
occurs.  Then users have to reconnect.
 
But, I’m looking for the best way to structure the TNSNAMES file.  I’m
probably over thinking this, but, there has to be a way to create both
entries for both servers in the file, but only have them use the production
one.  Something tickles my brain from way back that it will run the
TNS list in order looking for a connection.
 
Just curious how others have set up their files.
 
Thanks 

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