4 Replies Latest reply: Feb 10, 2013 11:38 PM by 986120 RSS

    Client connection Failed in RAC

    986120
      Hi experts,

      Am having two node 11gR2(11.2.0.1) cluster in my testing server.

      It can access from outside through SQLDeveloper(as client).

      When I power-off a node, the connection terminated from client.

      Is there any changes required in listener.ora or tnsnames.ora like adding failover & load balancing



      Thanks,
      Bala:)
        • 1. Re: Client connection Failed in RAC
          ursusca
          Are you connecting to SCAN IP or node VIP?
          • 2. Re: Client connection Failed in RAC
            damorgan
            It appears that what you are saying is that you can connect but cannot failover.

            1. Post LISTENER.ORA
            2. Post SQLNET.ORA
            3. Post TNSNAMES.ORA on client and server
            4. Post the output of these two queries (be sure you read the FAQ if you do not know how to properly post listings):
            SELECT inst_id, COUNT(*)
            FROM gv$session
            GROUP BY inst_id;
            
            SELECT ss1.inst_id, ss2.value "GCS CR BLOCKS RECVD", ss1.value "GCS CR BLOCK RECV TIME",
            ((ss1.value / ss2.value) * 10) "AVG CR BLOCK RECV TIME (ms)"
            FROM gv$sysstat ss1, gv$sysstat ss2
            WHERE ss1.name = 'gc cr block receive time'
            AND ss2.name = 'gc cr blocks received'
            AND ss1.inst_id = ss2.inst_id;
            • 3. Re: Client connection Failed in RAC
              JohnWatson
              You need to set up Transparent Application Failover. In 11.2.x, it is probably best to do this in the OCR (though if you want, you can still use the 9i technique of configuring it in your tnsnames.ora file, or the 10g technique of configuring it in the data dictionary with dbms_service).
              Look at the switches -e, -m, -w, and -z of your srvctl add service command.
              --
              John Watson
              http://skillbuilders.com
              • 4. Re: Client connection Failed in RAC
                986120
                @ ursusca

                Am connecting to SCAN-IP
                --------------------
                @ damorgan

                listener.ora

                LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
                LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
                LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
                LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
                ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent

                ---
                sqlnet.ora

                NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

                ADR_BASE = /u01/app/grid

                ---

                tnsnames.ora (SERVER)
                RACDB =(DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.acc.com)(PORT = 1521))
                (FAILOVER=on)
                (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = rac)
                (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 10) (DELAY = 3))
                ))


                As am using SQLdeveloper as client so TNSNAMES.ORA not acailable for client.
                ---

                Query output
                SELECT inst_id, COUNT(*) FROM gv$session GROUP BY inst_id;
                INST_ID COUNT(*)
                ---------- ----------
                1 39
                2 36
                ---
                SELECT ss1.inst_id, ss2.value "GCS CR BLOCKS RECVD", ss1.value "GCS CR BLOCK RECV TIME",
                ((ss1.value / ss2.value) * 10) "AVG CR BLOCK RECV TIME (ms)" FROM gv$sysstat ss1, gv$sysstat ss2
                WHERE ss1.name = 'gc cr block receive time' AND ss2.name = 'gc cr blocks received'
                AND ss1.inst_id = ss2.inst_id;

                INST_ID GCS CR BLOCKS RECVD GCS CR BLOCK RECV TIME AVG CR BLOCK RECV TIME (ms)
                ---------- ------------------- ---------------------- ---------------------------
                1 239 49 2.05020921
                2 151 20 1.32450331

                -------------------------------------------------------------------------------------------------------------------------------------
                @ JohnWatson      
                [oracle@rac1 admin]$ srvctl add service -d racdb -s rac -r "racdb1,racdb2" -P BASIC -e select -m basic -z 10 -w 3
                [oracle@rac1 admin]$ srvctl start service -d racdb -s rac
                [oracle@rac1 admin]$ srvctl config service -d racdb
                Service name: rac
                Service is enabled
                Server pool: racdb_rac
                Cardinality: 2
                Disconnect: false
                Service role: PRIMARY
                Management policy: AUTOMATIC
                DTP transaction: false
                AQ HA notifications: false
                Failover type: SELECT
                Failover method: BASIC
                TAF failover retries: 10
                TAF failover delay: 3
                Connection Load Balancing Goal: LONG
                Runtime Load Balancing Goal: NONE
                TAF policy specification: BASIC
                Preferred instances: racdb1,racdb2
                Available instances: