Taf

[PaulH]

I am trying to setup transparent applicaiton failover and I it does not appear to be working. If anybody has any ideas I would appreciate it.

Setup:
I am running a primary and stdby DB under Oracle 10.2 on a Solaris 2.8. For testing pruposes, both my DB's are on the same box.

Currently, my primary database is not running and my stdby DB is running in read only mode.

Here is my tnsnames.ora file. I want it setup so it frist tries to connect to primary and if that fails connect to stdby.

PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
)
)

STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby)
)
)

PROD =
(DESCRIPTION_LIST =
(FAILOVER = TRUE)
(LOAD_BALANCE = FALSE)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.3.7)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = pri) (SERVER = DEDICATED)
(FAILOVER_MODE = (TYPE = SELECT) (METHOD=PRECONNECT) (BACKUP=stdby))
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.3.7)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = stdby) (SERVER = DEDICATED))
)
)

When I connect to my stdby DB using the service name "stdby' this appears to work.

$ echo $ORACLE_SID

$ sqlplus pbh/pbh@stdby

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 21 12:05:39 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

SQL>


When I try to connect with the service name 'PROD' I get the following error:

$ sqlplus pbh/pbh@PROD

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 21 12:06:26 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory


Enter user-name:


Any help would be greatly appreciated.

[LKBrwn_DBA]

The standard configuration is:
1) Pimary & Standby need to be in different Host
2) Both have to have the SAME service name
3) You need to configure your tnsnames.ora entry kinda like this:

PROD =
 (DESCRIPTION=
  (LOAD_BALANCE=ON)
  (FAILOVER=ON)
  (ADDRESS=(PROTOCOL=tcp)(HOST=prihost.mydomain.com)(PORT=1521))
  (ADDRESS=(PROTOCOL=tcp)(HOST=stbyhost.mydomain.com)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=prod.mydomain.com))
 )

PS: Primary and standby on same host defeats the purpose of recoverability and availability.

[PaulH]

The standard configuration is:
1) Pimary & Standby need to be in different Host
2) Both have to have the SAME service name
3) You need to configure your tnsnames.ora entry kinda like this:

PROD =
 (DESCRIPTION=
  (LOAD_BALANCE=ON)
  (FAILOVER=ON)
  (ADDRESS=(PROTOCOL=tcp)(HOST=prihost.mydomain.com)(PORT=1521))
  (ADDRESS=(PROTOCOL=tcp)(HOST=stbyhost.mydomain.com)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=prod.mydomain.com))
 )

PS: Primary and standby on same host defeats the purpose of recoverability and availability.

Thanks for the reply.

I realize in the real world they should be on different servers but I was setting up a test case first to make sure it worked.

The examples I saw online did not have the same service names, can you point me to the place in the documentation where it says that.

[LKBrwn_DBA]

It's in the Oracle® Database Net Services Administrator's Guide.

What "binds" the primary to the standby is the "service name".

I do not beleive you can implement TAF using single server, but I would try configuring a local listener for each db and see if that works.

[PaulH]

TAF works if databases are in the same machine or in a different machine. The only problem is that the listener should be down if the corresponding database is down. Therefore, if you have two databases in the same machine, you should have two listeners with two different port running on the same machine. If the database is down then the listener also be down otherwise TAF doesn't work.

The same is true if you have databases are running on different machine

FYI, In my case, the listener was still up and running and it always tried to connect me to the pri database.

If you want to have pri and stdby on the same machine you need to setup 2 different LISTENERS in your listener.ora file.

Example:

LISTENER1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = devsun3)(Port = 1521))
)
LISTENER2 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = devsun3)(Port = 1531))
)

SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /rdbms/oracle/10g20)
(SID_NAME = pri)
)
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /rdbms/oracle/10g20)
(SID_NAME = stdby)
)
)
TRACE_LEVEL_listener = OFF
STARTUP_WAIT_TIME_listener = 0
CONNECT_TIMEOUT_listener = 10
a) to start listener1 run as "lsnrctl start listener1" or to stop
"lsnrctl stop listener1"
b) to start listener2 run as "lsnrctl start listener2" or to stop
"lsnrctl stop listener2"

[LKBrwn_DBA]

Maybe I didn't say it in so many words, but I did recommend you try it configuring a local listener for each db and see if that worked.

[PaulH]

what I don't get is how come Oracle is not smart enough to try the stdby connection if it can't connect to the pri connection.

Why would it make a difference if the listener is running or not. If yuo get no response back, than try the next service name. I really don't see the benenfit of tying this to the listener.

Thanks to all who commented.

[LKBrwn_DBA]

The reason is that the primary is bound (SQL*Net wise) to the standby by the "service name" and in a failover configuration you need to map both instances to the same service.

A local listener configuration cannot have the same service name for two different instances.

Therefore you need two listeners, each one on different host or both in same host but different port.