4 Replies Latest reply: Jan 4, 2013 3:38 AM by JohnWatson RSS

    Clients Failing to Connect Due to Intermittent ORA-12545 in RAC Environment

    Ankit Ashok Aggarwal
      We facing this problem.
      Whenever we connect oracle client from application server to our database.
      onces it get connect and next moment it shows the error ORA-12545 :Connect Failed Because Target host or Object does not Exist.

      After googling, i found many recommendations for this.
      one of which i liked is :

      +(source : internet)+

      Whe LOCAL_LISTENER is not set at all or is not set correctly, PMON can register both the VIP hostname and the physical hostname with the REMOTE_LISTENER. This is incorrect in RAC but if it does occur, the client can intermittently be routed to a listener endpoint on either of these addresses. If the client is unable to resolve the physical hostname or even the vip hostname, the connection will fail with ORA-12545.
      And important first set is to check the output for : lsnrctl services <listener_name>
      You should not see the physical hostname in the handler information for your RAC instances. Here’s an example of an incorrect lsnrctl services <listener_name> output for a 2-node RAC cluster:
      lsnrctl services oracle_rac_listener
      Service "oracle.oracle.com" has 2 instance(s).
      Instance "orcl1", status READY, has 2 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
      REMOTE SERVER
      (ADDRESS=(PROTOCOL=TCP)(HOST=myphysicalhost1)(PORT=1521))
      "DEDICATED" established:326 refused:0 state:ready
      LOCAL SERVER

      Instance "orcl2", status READY, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:394371 refused:0 state:ready
      REMOTE SERVER
      (ADDRESS=(PROTOCOL=TCP)(HOST=myphysicalhost2)(PORT=1521))
      Here’s an example of what it ought to look like when LOCAL_LISTENER is configured correctly:
      lsnrctl services oracle_rac_listener
      Service "oracle.oracle.com" has 2 instance(s).
      Instance "orcl1", status READY, has 2 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
      REMOTE SERVER
      (ADDRESS=(PROTOCOL=TCP)(HOST=myVIP_host1)(PORT=1521))
      "DEDICATED" established:326 refused:0 state:ready
      LOCAL SERVER

      Instance "orcl2", status READY, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:394371 refused:0 state:ready
      REMOTE SERVER
      (ADDRESS=(PROTOCOL=TCP)(HOST=myVIP_host2)(PORT=1521))
      Log in with privileges to the instance and issue the following commands so that LOCAL_LISTENER is set correctly:
      alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node1>)) scope=both sid='INSTANCE_NAME1';
      Do the same for the 2nd instance where host is set to the <vip_host_node2> and the sid is set to the 2nd instance name.
      alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node2>)) scope=both sid='INSTANCE_NAME2';
      Now the correct address using the VIP hostname will be registered against the listeners in the cluster.
      The client should be able to resolve all forms of the VIP host via their /etc/hosts file. i.e. short name and fully qualified.


      now my concern is :

      To change LOCAL_LISTENER parameter at database into init.ora/spfile, will we require a downtime?? or will it have any bad impact on our exisiting RAC production databases?
      Please suggest??

      Thanks
        • 1. Re: Clients Failing to Connect Due to Intermittent ORA-12545 in RAC Environment
          moreajays
          Hi,

          Its common issue faced in RAC without SCAN , you can do below . it will require restart of listener & do not touch LOCAL_LISTENER parameter is set by grid only

          On Node1:

          1. listener.ora

          LISTENER_REMCORP1 =
          (DESCRIPTION_LIST =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-vip-ebu-db1)(PORT = 1526)(IP = FIRST))
          )
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-ebu-db1)(PORT = 1526)(IP = FIRST))
          )
          )
          )

          2. tnsnames.ora

          LISTENER_REMCORP =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-vip-ebu-db1)(PORT = 1526))
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-vip-ebu-db2)(PORT = 1526))
          )

          3. parameter file

          remote_listener=LISTENER_REMCORP

          On Node2:

          1. listener.ora

          LISTENER_REMCORP2 =
          (DESCRIPTION_LIST =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-vip-ebu-db2)(PORT = 1526)(IP = FIRST))
          )
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-ebu-db2)(PORT = 1526)(IP = FIRST))
          )
          )
          )

          2. tnsnames.ora

          LISTENER_REMCORP =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-vip-ebu-db1)(PORT = 1526))
          (ADDRESS = (PROTOCOL = TCP)(HOST = remedy-vip-ebu-db2)(PORT = 1526))
          )

          3. parameter file

          remote_listener=LISTENER_REMCORP



          Thanks,
          Ajay More
          http://moreajays.blogspot.com
          • 2. Re: Clients Failing to Connect Due to Intermittent ORA-12545 in RAC Environment
            Ankit Ashok Aggarwal
            *....................*
            Node 1 details
            *....................*



            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            local_listener string
            remote_listener string LISTENERS_NFDB
            SQL> exit
            Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
            bit Production
            With the Partitioning, Real Application Clusters, OLAP, Data Mining
            and Real Application Testing options
            ch0d6bl0 $ cd $ORACLE_HOME
            ch0d6bl0 $ pwd
            /u01/app/oracle/product/10.2.0/db_1
            ch0d6bl0 $ cd network/
            ch0d6bl0 $ cd admin/
            ch0d6bl0 $ ls
            listener.ora shrept.lst
            listener10051610AM2802.bak tnsnames.ora
            listener10051610AM3226.bak tnsnames10051610AM2802.bak
            listener10051610AM4008.bak tnsnames10051610AM3226.bak
            org tnsnames10051610AM4008.bak
            samples
            ch0d6bl0 $ cat listener.ora
            # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/n
            etwork/admin/listener.ora
            # Generated by Oracle configuration tools.

            SID_LIST_LISTENER_CH0D6BL0 =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
            (PROGRAM = extproc)
            )
            )

            LISTENER_CH0D6BL0 =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521)(IP = FIRST))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.85.170)(PORT = 1521)(IP = FIRST)
            )
            )
            )

            ch0d6bl0 $ cat tnsnames.ora
            # tnsnames.ora.ch0d6bl0 Network Configuration File: /u01/app/oracle/product/10.2
            .0/db_1/network/admin/tnsnames.ora.ch0d6bl0
            # Generated by Oracle configuration tools.

            LISTENERS_NFDB =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521))
            )

            NFDB2 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = nfdb)
            (INSTANCE_NAME = nfdb2)
            )
            )

            NFDB1 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = nfdb)
            (INSTANCE_NAME = nfdb1)
            )
            )

            NFDB =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521))
            (LOAD_BALANCE = yes)
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = nfdb)
            )
            )

            EXTPROC_CONNECTION_DATA =
            (DESCRIPTION =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
            )
            (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
            )
            )

            TEST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.85.15)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = nfdb)
            )
            )

            *....................*
            Node 2 details
            *....................*


            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
            With the Partitioning, Real Application Clusters, OLAP, Data Mining
            and Real Application Testing options

            SQL> show parameter listener

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            local_listener string
            remote_listener string LISTENERS_NFDB
            SQL> exit
            Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
            With the Partitioning, Real Application Clusters, OLAP, Data Mining
            and Real Application Testing options
            ch1d5bl0 $ cd $ORACLE_HOME/network/admin
            ch1d5bl0 $ ls
            listener.ora org samples shrept.lst tnsnames.ora
            ch1d5bl0 $ cat listener.ora
            # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
            # Generated by Oracle configuration tools.

            LISTENER_CH1D5BL0 =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521)(IP = FIRST))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.85.185)(PORT = 1521)(IP = FIRST))
            )
            )

            SID_LIST_LISTENER_CH1D5BL0 =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
            (PROGRAM = extproc)
            )
            )

            ch1d5bl0 $ cat tnsnames.ora
            # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
            # Generated by Oracle configuration tools.

            LISTENERS_NFDB =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521))
            )

            NFDB2 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = nfdb)
            (INSTANCE_NAME = nfdb2)
            )
            )

            NFDB1 =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = nfdb)
            (INSTANCE_NAME = nfdb1)
            )
            )

            NFDB =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521))
            (LOAD_BALANCE = yes)
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = nfdb)
            )
            )

            NFS =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch0d6bl0-vip)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = ch1d5bl0-vip)(PORT = 1521))
            (LOAD_BALANCE = yes)
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = NFS)
            (FAILOVER_MODE =
            (TYPE = SELECT)
            (METHOD = BASIC)
            (RETRIES = 180)
            (DELAY = 5)
            )
            )
            )

            ch1d5bl0 $





            These configurations are already there at our RAC nodes. can you suggest what next ??

            Its o/s is SunOS ch0d6bl0 5.10 Generic_142900-03 sun4v sparc SUNW,Sun-Blade-T6340
            database is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

            Please suggest now??


            also as we can see LOCAL LISTENER is empty ...so what next??

            Edited by: Ankit Ashok Aggarwal on Jan 4, 2013 2:44 PM
            • 3. Re: Clients Failing to Connect Due to Intermittent ORA-12545 in RAC Environment
              JohnWatson
              You need to set your local_llistener parameter:
              alter system set local_listener=nfdb1 sid='nfdb1';
              alter system set local_listener=nfdb2 sid='nfdb2';
              no downtime, it's a dynamic parameter.
              • 4. Re: Clients Failing to Connect Due to Intermittent ORA-12545 in RAC Environment
                moreajays
                Hi,

                Good to see config is already present , next check/confirm below

                1. tnsping <Node1_alias> , tnsping <Node2_alias> from same & across the nodes
                2. sqlplus <user>@<Node1_alias>/<pwd> , sqlplus <user>@<Node1_alias>/<pwd> from same across the node
                3. Local_listener should not be empty it should point to virtual host entry like below , try ti set if all above fails
                NAME                                 TYPE                             VALUE
                ------------------------------------ -------------------------------- ------------------------------
                local_listener                       string                            (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=121.244.255.54)(PORT=1522))))
                Thanks,
                Ajay More
                http://moreajays.blogspot.com