?
Solved

TNSNAMES with DataGuard

Posted on 2005-04-15
5
Medium Priority
?
1,750 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:peledc
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13797985
  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
 
LVL 1

Author Comment

by:peledc
ID: 13798422
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
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 13804705

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
 
LVL 1

Author Comment

by:peledc
ID: 13806234
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
 
LVL 48

Expert Comment

by:schwertner
ID: 13813089
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question