Can you please explain this?
For names server do we have to make an Oracle database on the names server machine or is it just a networking component to be installed with configuration files?

The scenario is normal Primary/Physical Standby configuraion (without RAC)
I want to use Tnsnames.ora file and I do not want to make changes to it on all client machines(in thousands) after switchover/failover operations.
I configure a common service name for both the primary and standby databases.
I have two servers PRODSRVRA and PRODSRVRB.
PRODSRVRA hosts Primary database initially (Dbname - PROD , Instance Name PROD , Service Name PROD)
PRODSRVRB hosts Standby database initially (Dbname - PROD , Instance Name PROD, Service Name PROD)
Both servers have two listeners.
Listener on Port 1522 is started on server hosting Primary database and is used for connect time failover.
Listeners on Port 1523 is used for communication between Primary and Standby databases for Log transport.

Listener.ora on PRODSRVRA

LSNRPRODA =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRA)(PORT = 1522))
)
)

SID_LIST_LSNRPRODA =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = c:\Oracle\Product\9.2.0.4)
(SID_NAME = PROD)
)
)

LSNRPRIM =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRA)(PORT = 1523))
)
)

SID_LIST_LSNRPRIM =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = c:\Oracle\Product\9.2.0.4)
(SID_NAME = PROD)
)
)

Listener.ora on PRODSRVRB

LSNRPRODB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRB)(PORT = 1522))
)
)

SID_LIST_LSNRPRODB =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =c:\Oracle\Product\9.2.0.4)
(SID_NAME = PROD)
)
)

LSNRSTDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRB)(PORT = 1523))
)
)

SID_LIST_LSNRSTDBY =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =c:\Oracle\Product\9.2.0.4)
(SID_NAME = PROD)
)
)


TNSNAMES.ORA configuration on the Primary Database server - PRODSRVRA
STDBY=
( DESCRIPTION =
( ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP) (HOST = PRODBSRVRB) (PORT = 1523) )
)
( CONNECT_DATA = (SERVICE_NAME =PROD) (SERVER = DEDICATED) )
)

TNSNAMES.ORA configuration on Standby Database server - PRODSRVRB

PRIM=
( DESCRIPTION =
( ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP) (HOST = PRODSRVRA) (PORT = 1523) )
)
( CONNECT_DATA = (SERVICE_NAME =PROD) (SERVER = DEDICATED) )
)



Failover Configuration in Tnsnames.ora on Clients (PCs)

PROD.WORLD =
(DESCRIPTION_LIST =
(FAILOVER = TRUE)
(LOAD_BALANCE = FALSE)
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = PRODSRVRA)(PORT = 1522))
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(SERVER = DEDICATED)
)
)
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = PRODSRVRB)(PORT = 1522))
(CONNECT_DATA =
(SERVICE_NAME =PROD)
(SERVER = DEDICATED)
)
)
)

Primary is working - the listener on PRODSRVRA is up but the listener on PRODSRVRB is down
When the clients connect using PROD.WORLD Primary database is available they will connect to the Primary database.

Failover Situation :
Primary is down. The standby database on PRODSRVRB is made primary.
The listener( port1522) on Standby server PRODSRVRB is started.
The clients will connect to New Primary on PRODSRVRB using PROD.WORLD


Switchover situation
The listener ( port1522) on Old Primary is taken down. The Standby database is made Primary.
The listener( port1522) on Standby server PRODSRVRB is started.
The clients connect using PROD.WORLD to the new Primary database on PRODSRVRB.
The log transport services will continue using the other listeners working on port 1523.

Let me know will this TNSANMES.ora thing work without changing anything on Client PCs or if I am missing something?

Drawbacks accroding to me:
1) Global DB name cannot be used for connect time failover as mentioned in some documents - So may be OEM and Dataguard monitor etc. camnnot be used.
2) Connections to New Primary Database will be slow as they first look for the Old Primary database server.


Thanks

Assuming that production and master have the same SID, I use a simple tnsnames.ora

database =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST=box1)(PORT=1521))
(ADDRESS = (PROTOCOL = TCP)(HOST=box2)(PORT=1521))
)
(CONNECT_DATA =
(SID=SID1)
)
)

On each box, i have two listeners:
- one always running (ie port 1522) for administration purpose,
- the other one (here one port 1521) running only on the production box.

The only 'trick' is to be sure that the second one is started and stoped accordingly to each box status.

So I can safely assume that I do not have change the TNSNAMES.ora on all the clients to have them connect to Standby database in failover/switchover situations(No RAC).
Can you please give your suggestions/comments on 2)?

Sorry I meant for question 1) above what are your comments?

1) Global DB name cannot be used for connect time failover as mentioned in some documents - So may be OEM and Dataguard monitor etc. cannot be used.
a) I want your comments on above statement 1)whether what I think is right or not?
Sir I want to know -
b) What is the use/function of Global Db name? Why it is required?
c) if we cannot use Global Db name for connect time failover
What we are going to lose?


Thanks

It will not work in case of failover...

Check this out

Site 1 -- up & running
Site 2 -- stand by
Site 1 -- crashes & fails over to Site2
Connections will go to Site1 & bounce it to Site2 -- OK
DBA brings Site1 as standby & listener is up on Site1(needed for dataguard)

Now........you will get

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

I have a DR setup for Headoffice and DR sites.
If primary database server crashed all of sudden and is not available for access say for 2 hours, I have no choice but to issue fail over command to make the secondary (DR Configured via DATA GUARD) database server as Primary and open with resetlogs-new incarnation.
As and when the original Primary server comes back, I will need to rebuild it as a Secondary -Dataguard (with online backup and restore) and then say with 10 minutes downtime, I will issue switch over commands to bring back the original configuration of Primary/Secondary.

There has always been one question in my mind that in the above scenerio, can I save the full back and restore to build the primary again? Is there a way that when the primary server comes back online (assuming the data is intact as it was the time of crash), I will just make it standby and apply the missing logs from the new primary server (which was old secondary). Yes the incarnation no, the archived log seq will not match (the old primary still at old scn, while the new primary will have the new log seq nos).
I mean with all the tech we have (flashback etc), we can not avoid rebuliding the crashed primary just like as if we did switch over???

In reference to the original question

"How can the clients automatically connect to the new Primary database (previously Standby
database) in case of failover/switchover using Tnsnames.ora without changing tnsnames.ora
file on each client machine. Do I have to make two listeners on each server and a common
service name?"

This is what we do..

1. We have started the ONS services on both the primary host and standby host.
2. Our TNS names entry is defined as below on both the primary and standby and also the clients.

DB1 =
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=ON)
(ADDRESS=(PROTOCOL=TCP)( HOST=<host1>)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)( HOST=<host2>)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=DB1)))

3. We have created this service DB1 on the primary using dbms_service.create_service and started it using dbms_service.start_service.

4. We have created a trigger on the primary (which will be replicated to standby) which will take care of starting the service only on the host which is primary

CREATE OR REPLACE TRIGGER manage_JDBCservice after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('DB1');
ELSE
DBMS_SERVICE.STOP_SERVICE('DB1');
END IF;
END;
/

Tom - please let us know what you think ?

Hi Tom,

I would like to know how to restrict users to this services, eg. how can I ensure that a user can only connect to this service "DB1" but not to another running service (assuming that there are also other services like "DB2", "DB1XDB",... running)?

Thanks,
Markus

Back to the basic question:

How can the clients automatically connect to the new Primary database (previously Standby
database) in case of failover/switchover using Tnsnames.ora without changing tnsnames.ora
file on each client machine.

Here it solution:
-----------------
1) tnsnames.ora entry will have connect time failover configured for primary and standby.

2) If primary database is down but primary node is up. The above will work fine.

3) But if primary database is down and primary node is also down. The each attempt to connect using tnsnames.ora will go through a TCP timeout.

solution for 3:

b) On another node in primary subnet - bind/start the Failed database node IP. This can be done via a script.
Make sure to shut this down, when bringing up the failed primary node. (This is kind of a manual VIP failover)

Note: If the whole primary site is down and All nodes in that subnet are down. Then you might still need to do a DNS change.

Hope this helps.

Cheers
Ivan

I am havving the same issue, when I added "failover=yes" at client tnsnames.ora file.... the clients can not connect to the primary database from application but can connect from SQLPLUS.
------------------------------
Oracle 10.2.0.1.0
primary------ machine:test9 ... sid: primary
standby------machine:standby .. sid: standby
------------------------------
primary machine tnsnames.ora
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = test9)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = primary)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = standby)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = STANDBY)
)
)
---------------------------------
standby tnsnames.ora

standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = standby)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = STANDBY)
)
)

primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = test9 )
(Port = 1521)
)
)
(CONNECT_DATA = (SID = primary)
)
)

-----------------------------
CLIENT TNSNAMES.ora

--------------------------------------------------------------------------------
prim.world=
(DESCRIPTION_LIST=
(FAILOVER=true)
(LOAD_BALANCE=no)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= test9)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SID=primary)
)
)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=standby)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SID=standby)
)
)
)

----------------------------------
What other parameter should I add or modify to client get connection to database ? I am waiting for your reply sir

Kind Regards

Tom
Can you please show me how to configure asm and add asm files to database?

Sir, Let me elaborate...

On client testing machine I have installed Oracle forms 6i for ERP APPLICATION, Some times it's get connected sometimes it's not. (like around 70% request are getting successfull connection & around 30% request are getting ora-01033 oracle initialization or shutdown in progress

------------------
I have made below entry in TNSNAMES.ora file
--------
prim.world=
(DESCRIPTION_LIST=
(FAILOVER=true)
(LOAD_BALANCE=no)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= test9)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SID=primary)
)
)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=standby)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SID=standby)
)
)
)

I have just performed switchover operation, standby is now up as Primary, and logs are being applied at standby (old primary).

When I am trying to connect I am getting below error.

Enter user-name: erp/erp@prim.world
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
--------------------------------------

I have created new entry in client's TNSNAMES.ora for standby database to check whether it's database issues or TNS issue. 

----------------------------
standby.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (Host = standby)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = standby)
    )
  )
-------------------------
Enter user-name: erp/erp@standby.world

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

I think it's the prim.world TNS entry issue. 

Below is my understanding:

the host connection in tnsname.or is a "tcp timeout checking", which means it only check if the ip is avaliable. If the ip/host is accessable, but the oracle is not availabe, like the listener is down (ORA-01033), it will NOT try the next host, but just throw error. You can refer to the meaning of TRANSPORT_CONNECT_TIMEOUT.

i really want to know if the original solution work (by h from VA, USA): configure two listener on both primary and standby. It sounds a very good idea, but i doult if it is necessary to configure the same service name on both primary and standby. and if i'm right, for log transport the
serives should be different as uniq_db_name.

Node A (primary):
listener1 - for connect time failover (ON)
service_admin
listener2 - for log transport (ON)
service_a

Node B (standby):
listener1 - for connect time failover (OFF)
service_admin
listener2 - for log transport (ON)
service_b

tnsname.ora for clients:
prod.work{service_admin@node_a, service_admin@node_b}

connect time failover: Use dbms_service.create_service to create service_admin on active(primary) node.



1) Global DB name cannot be used for connect time failover as mentioned in some documents - So maybe OEM and Dataguard monitor etc. camnnot be used.
//Global DB Name is used for data guard broker, it can be configured in listener2(transport)
2) Connections to New Primary Database will be slow as they first look for the Old Primary database server.
//We can set timeout parameters in tnsname.ora:
//CONNECT_TIMEOUT, TRANSPORT_CONNECT_TIMEOUT, RETRY_COUNT

Hello,

We have a 4-node primary database and 2 other clusters (each 4-node as well) as standby databases. One of the standby is read-only.

We have a cluster name that identifies each cluster (and all their respective 4 nodes) and likewise a cluster name exists for the 2 different standbys as well. The tns entry in our case looks like:

(DESCRIPTION=(SDU=32768)(enable=broken)(LOAD_BALANCE=yes)
(ADDRESS=(PROTOCOL=TCP)(HOST=dcb90385.houston.hp.com)(PORT=1526))
(ADDRESS=(PROTOCOL=TCP)(HOST=dcb21258.austin.hp.com)(PORT=1526))
(ADDRESS=(PROTOCOL=TCP)(HOST=dcb50996.dallas.hp.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=SRVDB)))

In the above entry, dcb90385.houston.hp.com is the cluster name for Houston cluster.

Here Houston is primary, Austin (raad only) and Dallas are standbys.

If a user makes a connection to the database using the above tns entry, the connection will always goes to primary database right? Later, if Dallas becomes primary (Houston as standby), will the same tns entry lands the user process into Dallas database?

Other question: What if I want a set of users to connect to Read-only database? What would be the ideal way to do that? The read-only database can also shift between all the 3 clusters.

Thanks,

I am sorry if I have not communicated well, Houston, Dallas and Austin are not 3 servers, they are 3 clusters that has 4 servers each. We have put all the 3 clusters in the tns names for transparency purpose for the users. Users wouldn't know whether they are logged in to Houston cluster or Austin cluster. I think load balance comes in when we are shifting load among various nodes within a cluster.

About another tns entry for connecting to read-only database, I guess I will be using a different service name right? That service name should be active on the read-only cluster and need to rotate to other cluster if read-only shifts to a different one.

Thanks,

Thanks,

Hello,

I always want to connect to "primary" database. My intention of having all the cluster names in tns entry is to make sure the user always connect to "primary". It can be Houston or austin or atlanta (not like connecting to houston first or austin next). I don't see a role of load balance here since Houston/Austin/Atlanta are 3 separate clusters.

My original question is whether the tns entry that I had posted earlier makes a connection to primary database irrespective of the cluster it is in.

Thanks,

If I set load_balance "on" then it will randomly make a connection to the host present. What if the connection lands into a standby database (when it is random it can happen right)? I always want the connection to land into Primary database.

LOAD_BALANCE
When set to on, instructs Oracle Net to progress through the list of protocol addresses in a random sequence, balancing the load on the various listeners. When set to off, instructs Oracle Net to try the addresses sequentially until one succeeds.
Client load balancing is turned on by default for multiple connect descriptors (DESCRIPTION_LIST).

Tom, your answer to the last review is exactly what I was looking for. I have a black box app which connects using JDBC's thin driver with the syntax host_name:port/service_name, so when a switchover/failover occurs I cannot enjoy the benefits of an Oracle Net connection string.

Registering the new primary with a remote listener looks like the answer, I'm thinking of using:

remote_listener=<hostname_for_the_app>
service_names=(<db_unique_name>, <db_name>)

The idea is that only the primary has the common service <db_name> registered at any point in time, so that the connections are always routed to the primary. However, I fear both instances might register the same service at some point because:

1) I see an instance registers with the listener right when it's started in NOMOUNT state. At least with the local listener, I did not have the chance to test it with a remote listener. Since a physical standby must be MOUNTed for log applying, it will register when it "should not". Note that even if registration only happened when the instance opens the DB, I'd still be in trouble if/when I open the standby read only.

2) I could set a trigger for a primary to de-register the <db_name> service right before switching over to the standby, but it won't fire on failovers (whether manual or FSFO initiated)

So what should be the smooth way around this? I also considered NOT setting service_names on the parameter file, using a trigger on DB_ROLE_CHANGE to perform an "alter system set service_names service_names=(<db_unique_name>, <db_name>) scope=memory" only from the new primary. That will solve problem 1) but it's still not solving problem 2)

Thanks for your time, keep on your great work!