Re: An OPS & tnsnames question

From: Scott <oraracdba_at_yahoo.com>
Date: Wed, 29 Aug 2001 20:10:51 -0700
Message-ID: <F001.0037C6F9.20010829202526@fatcity.com>

Andrey, The Oracle failover stuff is documented in the net8 guide and I believe there are some examples as well. The TNS syntax you are using is not failover but more or less a load leveling across the listeners. If you want to do TAF you will need to use this syntax.

ALIAS1.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE1)(PORT =
1521))

)
(CONNECT_DATA =
(SID = OPS1)
(server=dedicated)

     (failover_mode=
       (backup=ALIAS2.world)
       (type=select)
       (method=basic)))

ALIAS2.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE2)(PORT =
1521))

)
(CONNECT_DATA =
(SID = OPS2)
(server=dedicated)

     (failover_mode=
       (backup=ALIAS1.world)
       (type=select)
       (method=basic)))

I haven't used Oracle8 TAF syntax in a while so this example may or may not work but it is close.

If you decide to use 8i or 9i this is the TNS you will want to use.

# TNSNAMES.ORA Network Configuration File: e:\stuff\tnsnames.ora
# Generated by Oracle configuration tools.

# Try each address, in order, until one succeeds

ALIAS1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

)
(CONNECT_DATA =
(SERVICE_NAME = ops)
(failover_mode=

        (type=select) 
        (method=basic)
        (retries=20)
        (delay=15))

)
)

# Use each address in order until destination reached

ALIAS2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

    )
    (SOURCE_ROUTE = yes)
    (CONNECT_DATA =
(SERVICE_NAME = ops.us.oracle.com)
(failover_mode=

        (type=select) 
        (method=basic)
        (retries=20)
        (delay=15))

)
)

# Try each address, randomly, until one succeeds

ALIAS3.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
git-opssun1)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST =
git-opssun2)(PORT = 1521))

(LOAD_BALANCE = yes)

)
(CONNECT_DATA =
(SERVICE_NAME = ops)
(failover_mode=

        (type=select) 
        (method=basic)
        (retries=20)
        (delay=15))

)
)

# Try one address selected at random

ALIAS4.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

(LOAD_BALANCE = yes)
(FAILOVER = false)

)
(CONNECT_DATA =
(SERVICE_NAME = ops)
(failover_mode=

        (type=select) 
        (method=basic)
        (retries=20)
        (delay=15))

)
)

# Use only the first address

ALIAS5.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

(FAILOVER = false)

)
(CONNECT_DATA =
(SERVICE_NAME = ops)
(failover_mode=

        (type=select) 
        (method=basic)
        (retries=20)
        (delay=15))

)
)

#alias using backup syntax

ALIAS6.WORLD =
  (DESCRIPTION =
    (failover=on)
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = NODE##)(PORT
= 1521))

)
(CONNECT_DATA =
(SERVICE_NAME = OPS)
(server=dedicated)

     (failover_mode=
       (backup=ALIAS1.world)
       (type=select)
       (method=basic)))

Scott

Andrey Bronfin <andreyb_at_elrontelesoft.com> wrote: > Dear list ! > I've got an OPS database named OPS with 2 instances > OPS1 and OPS2 . > I want the users to connect to OPS1 and ONLY if it > is down , to connect to > OPS2. > It's Oracle 8.0.5.1 on NT4. > > I put the following lines in clients' tnsnames.ora > file : > > OPS.world = (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS = (PROTOCOL = tcp) (HOST = DB1) (PORT = > 1526)) > (CONNECT_DATA = (SID = OPS1)) > ) > (DESCRIPTION = > (ADDRESS = (PROTOCOL = tcp) (HOST = DB2) (PORT = > 1526)) > (CONNECT_DATA = (SID = OPS2)) > ) > ) > > This works perfectly fine except for the case when > OPS1 instance is down , > but the listener is up. > Then clients try to connect to OPS1 and fail - > "Oracle not available" . > When i bring the listener on OPS1 host down - > everybody perfectly connect to > OPS2. > > Is there a solution ? > Thanks a lot in advance ! > > > DBAndrey > > * 03-9254520 > * 053-464562 > * mailto:andreyb_at_elrontelesoft.com > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: andreyb_at_elrontelesoft.com > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists >
> To REMOVE yourself from this mailing list, send an > E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing).

Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott
  INET: oraracdba_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Aug 29 2001 - 22:10:51 CDT