5 Replies Latest reply: Oct 9, 2012 4:05 AM by Sebastian Solbach -Dba Community-Oracle RSS

    one tns for the DG

    user597097
      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
          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-Oracle
            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
              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
                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-Oracle
                  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