This discussion is archived
5 Replies Latest reply: Oct 9, 2012 2:05 AM by Sebastian Solbach (DBA Community) RSS

one tns for the DG

user597097 Newbie
Currently Being Moderated
hi

our prod is like that:
2 node rac main
2 node rac dg main

and a banch of 2 node rac that connect to the main db
they are connect through db link (tnsnames) to the main db

but the entry looks like that:

MAIN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=main1v)(PORT=1521))
(ADDRESS = (PROTOCOL=TCP)(HOST=main2v)(PORT=1521))
(LOAD_BALANCE=yes)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=main)
)
)


so in case of failover/switchover all the db cant connect to the dg through this db link

how do i fix that?

is that a good solution:

MAIN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=main1v)(PORT=1521))
(ADDRESS = (PROTOCOL=TCP)(HOST=main2v)(PORT=1521))
(ADDRESS = (PROTOCOL=TCP)(HOST=maindg1v)(PORT=1521))
(ADDRESS = (PROTOCOL=TCP)(HOST=maindg2v)(PORT=1521))
(LOAD_BALANCE=yes)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=main)
)
)
  • 1. Re: one tns for the DG
    CKPT Guru
    Currently Being Moderated
    so in case of failover/switchover all the db cant connect to the dg through this db link
    how do i fix that?
    You will use TNS service in DB_LINK, when you performed switchover/failoer the locations of primary and standby will be changed respectively ..
    So extract DDL , Use that TNS service and try to perform "tnsping <tns>" and see where it is pointing.

    >
    user597097      
         Newbie
         
    Handle:      user597097
    Status Level:      Newbie
    Registered:      Sep 26, 2007
    Total Posts:      52
    Total Questions:      21 (20 unresolved)
    >

    Good record, keep it up... read "Forums Etiquette / Reward Points" https://forums.oracle.com/forums/ann.jspa?annID=718

    Edited by: CKPT on Sep 9, 2012 11:20 PM
  • 2. Re: one tns for the DG
    Sebastian Solbach (DBA Community) Guru
    Currently Being Moderated
    Hi,

    there are some good whitepapers on the MAA website (www.oracle.com/goto/maa).

    Especially:
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf

    Regards
    Sebastian
  • 3. Re: one tns for the DG
    user597097 Newbie
    Currently Being Moderated
    it is looks like this is the sample i was looking for:

    SALES=
    (DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)
    (DESCRIPTION=
    (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
    (ADDRESS_LIST=
    (LOAD_BALANCE=on)
    (ADDRESS=(PROTOCOL=TCP)(HOST=Austin-scan)(PORT=1521)))
    (CONNECT_DATA=(SERVICE_NAME=oltpworkload)))
    (DESCRIPTION=
    (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
    (ADDRESS_LIST=
    (LOAD_BALANCE=on)
    (ADDRESS=(PROTOCOL=TCP)(HOST= Houston-scan)(PORT=1521)))
    (CONNECT_DATA=(SERVICE_NAME=oltpworkload))))



    right?
  • 4. Re: one tns for the DG
    user597097 Newbie
    Currently Being Moderated
    hi,

    well i'm try to use this tns and i get an error:

    SALES=
    (DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)
    (DESCRIPTION=
    (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
    (ADDRESS_LIST=
    (LOAD_BALANCE=on)
    (ADDRESS=(PROTOCOL=TCP)(HOST=pry1v)(PORT=1521)))
    (ADDRESS=(PROTOCOL=TCP)(HOST=pry2v)(PORT=1521)))
    (CONNECT_DATA=(SERVICE_NAME=pry)))
    (DESCRIPTION=
    (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
    (ADDRESS_LIST=
    (LOAD_BALANCE=on)
    (ADDRESS=(PROTOCOL=TCP)(HOST= sec1v)(PORT=1521)))
    (CONNECT_DATA=(SERVICE_NAME=sec_dg))))

    the primary is a rac 2 node and the fail db is non rac single node
    the error is :

    ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
  • 5. Re: one tns for the DG
    Sebastian Solbach (DBA Community) Guru
    Currently Being Moderated
    Hi,

    probably a version problem. The whitepaper I linked is for 11.2.0.X with SCAN and 11.2.0.X client.

    Since you still using VIP addresses, you either use 10.X or 11.1.X version (on client/server).
    In this case you connect string should look different. See:

    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-clientfailoverbestprac-129636.pdf

    If it is a mixed setup (10.2 client 11.2 databases then again there should be something different).

    Regards
    Sebastian

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points