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 first published in January 2008

Dig deeper on Oracle database administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close