This discussion is archived
4 Replies Latest reply: Jan 4, 2013 1:38 AM by JohnWatson RSS

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

AnkitAshokAggarwal Explorer
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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
    AnkitAshokAggarwal Explorer
    Currently Being Moderated
    *....................*
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

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