Q

Configuring TNSNAMES file for failovers

Our Database Backup and Recovery expert explains how to configure TSNNAMES file for failovers.

We set up an Oracle DB 10g and configured the dataguard. The switchover works fine, but we have one issue left. How can we configure the installation so that we don't have to change a thing in the client's TNSNAMES or in the listener?

Situation: Two servers: DB and DG -> The database is active on DB, no problem to connect -> Switchover, DG becomes...

active -> unable to connect to the database. The client still wants to connect to DB, because the database on DB hasn't really shut down, but is in standby.

Is there a way to automatically connect to the database that is not in standby?

Our TNSNAMES looks like this:

PROD=
  (DESCRIPTION =
    (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = DG)(PORT = 1521))   
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )
If we switch the lines in ADDRESS_LIST, we can connect.

Hope you can help us.

Even though you are not using Real Application Clusters, you can take a clue from RAC and configure your TNSNAMES file to perform a failover should the first node be unavailable. I would modify your TNS entry to be something like the following:

PROD=
  (DESCRIPTION =
    (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = DG)(PORT = 1521))   
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
      (FAILOVER_MODE = (TYPE=SESSION)(METHOD=BASIC)) 
    )
  )
The new entry in bold will tell your Oracle client software to failover to the second server if the first server does not have the Oracle listener running.
This was last published in January 2008

Dig Deeper on Oracle database administration

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close