TNSNAMES with DataGuard

Oracle version 10g on Windows 2k.

We are using a physical standby database (DataGuard).
How should I set my client's TNSNAMES file in order for them to be able to connect to the PRIMARY database even after a switchover or even a failover?

Thanks
LVL 1
peledcAsked:
Who is Participating?
 
schwertnerCommented:

There is nothing special or magic action  action to do this.
The user should only use stdg1 alias instead of stdg1 ( after switching the standby to normal activity, of course).

Look at the example:


Given
Primary host : stdg2
Standby host : stdg1

Set up the listeners (Net Services)
 
Primary:
 
LISTENER.ORA
LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
                  )
                  (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 138.1.137.235)(PORT = 1521))
      )
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = v10g)
(ORACLE_HOME = /u01/oracle/product/10g)
      (SID_NAME = v10g)
    )
)
 
TNSNAMES.ORA:
 
v10g_stdg1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 138.1.137.234)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = v10g)
    )
  )
v10g_stdg2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 138.1.137.235)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = v10g)
    )
 
NOTE: For the standby make sure you change the ip (HOST) from .234 to .235 appropriately
0
 
schwertnerCommented:
  On the Primary host create a net service name that the Primary database can use to connect to the Standby database.  On the Primary host create a net service name that Standby, when running on the Primary host, can use to connect to the Primary, when it is running on the Standby host.  Example from  Primary’s host tnsnames.ora:
 
      DGD01_hasunclu1 =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = hasunclu1)(PORT = 1521))
          )
          (CONNECT_DATA =
            (SID = DGD01)
      (SERVER = DEDICATED)
          )
        )
      DGD01_hasunclu2 =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = hasunclu2)(PORT = 1521))
          )
          (CONNECT_DATA =
            (SID = DGD01)
            (SERVER = DEDICATED)
          )
        )
 
   The above two net service names must exist in the Standby hosts tnsnames.ora also.
 
   You must also configure a listener on the standby database. If you plan to manage this standby database using the Data Guard broker, you must configure the listener to use the TCP/IP protocol and statically register the standby database service using its SID.
 
0
 
peledcAuthor Commented:
Thanks for response, schwertner, but I'm looking for a way to configure my end users' tnsnames in order to enable them to connect to the standby server when the primary is down.
0
 
peledcAuthor Commented:
Changing the alias is easy. I was looking for a way to do it without changing the alias.
Maybe: failover option in the tnsnames.ora file...

Thanks anyway.
0
 
schwertnerCommented:
Failover has nothing to do with tnsnames.ora file.
This file only helps to establish connections to other 'visible" in the net Oracle instances.
And should be done on the client side (not in the Primary and Standby).
Because suddenly the client should use (becaise of fail of the Primary) the Standby that
acts as new Primary.
the good thing is that mainly this should be done on middle tier systems like Application servers and do not
hurt directly the end users.

Why we need two entries in the above tnsnames.ora?
The answer is simple.
When fail of the Primary occurs we turn on the Standby as Primary.
After that we try to repair the broken Primary and after that
we establish there the new Standby. Thats why we enter there
alias in both directions. Of course we need these aliases to connect to the both instances,
but in normal use one do not need alias for the Primary on the Standby.
At least I can not recall now need for feed back line to the Primary.
If I am wrong, please correct me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.