DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Taf

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    Taf

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Switchover / Failover

    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:
    Code:
    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2009
    Posts
    91
    Quote Originally Posted by LKBrwn_DBA View Post
    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:
    Code:
    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.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Net Services

    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Feb 2009
    Posts
    91
    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"

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Same old, same old

    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Feb 2009
    Posts
    91
    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.

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Service_name vs. Sid

    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.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width