Solved – Why was ORA-12537: TNS:connection closed, error returned?

When trying to establish a sqlplus connection to the database, I get the “ORA-12537: TNS:connection closed” error message. How do I resolve this?

On using a tns alias and connecitng to sqlplus I saw the following error.

    SQL> conn vst_server/xxx@node1
    ERROR:
    ORA-12537: TNS connection closed

In the listener log I see this message.

    TNS-12518: TNS:listener could not hand off client connection
    TNS-12560: TNS:protocol adapter error
    TNS-00517: Lost contact
    TNS-12547: TNS:lost contact

Solution

According to the oerr utility the ORA error could be due to:

    12537, 00000, “TNS:connection closed”
    “End of file” condition has been reached; partner has disconnected.

This error could be due to a multiple of reasons.

Reason 1
In a RAC environment for a database which was migrated to a RAC instance, the REMOTE_LISTENER is incorrectly set.

During failover of a service which is configured to use TAF, the connections connected to this service will hang. After the timeout you will get the ORA-12537 error before disconnection.

You will also find more details in the SCAN listener log.

To verify and correct this, first find the RAC cluster name.

    dbtest>cemutlo -n
    db1-clust

Then verify the REMOTE_LISTENER value in the Database.

    sqlplus> show parameter REMOTE_LISTENER
    dbtest:1521

Here the REMOTE_LISTENER is incorrectly set to the hostname instead of the cluster name. Correct this value to use the cluster SCAN name.

    alter system set REMOTE_LISTENER=’db1-clust:1521′ scope=both sid=dbtest1′;

The session should now failover without disconnecting.

Reason 2
If the VIP name is being used for the LOCAL_LISTENER then the Fully Qualified Network Name (FQDN) should be used. Alternatively the VIP IP can be used instead. See Oracle Metalink Doc ID 970619.1

    SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbtest.vst.com)(PORT=1521))))’ service_name=’EQ2′;

    or

    SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=111.21.32.12)(PORT=1521))))’ service_name=’EQ2′;

Reason 3
The Listener process can’t access Oracle binaries in the Database home. The Grid home is installed as a different user than the owner of the Oracle Database binaries. You may find more details in the SCAN listener log for this issue as well.

As the owner of the listener process verify that Oracle Database Binary home can be accessed from the listener home.

    ls -l $ORACLE_HOME/bin/oracle

Reason 4
If the home was cloned or moved at the OS level the Oracle Home binaries need to be relinked.

Related OraTips

Oracle 12c RAC: Installation on Oracle Linux 6 using VirtualBox – A quick guide of Oracle 12c RAC Installation on your laptop running on Windows. A step by step guide to configure Oracle 12c Grid and the database Software.

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP – I am trying to restore the controlfile but and getting the RMAN-06563 error. I know I have a backup on the controlfile but RMAN is not able to see it.

Other Articles

Oracle 12c RAC: New Features Oracle has come a long way from its inception of the 9i RAC database to its current 12c version. I remember the days when we had to deal with RAC related issues, ranging from performance to stability which gradually improved as this product matured. Learn more about the new feature now available with RAC.

Provision VirtualBox using Pre-Built Developer VMs Step by step guide to setup the Oracle VM VirtualBox and import the prebuilt template to quickly create a portable (on your laptop) testing environment in 10 minutes!



Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.

Leave a Reply

Your email address will not be published.