• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

New Connections to the Database lead to ORA-12518 or TNS-12518

By admin

The Problem

ORA-12518 errors in the listener log when new users are connecting to the Database. Example is as follows:

TNS-12518: TNS:listener could not hand off client connection 
  TNS-12547: TNS:lost contact 
   TNS-12560: TNS:protocol adapter error 
    TNS-00517: Lost contact 
     IBM/AIX RISC System/6000 Error: 32: Broken pipe

This is an intermittent problem that would likely occur at peak database usage times.

The Solution

There are two possible causes:

If using DEDICATED connections:

– With 10g, the default PROCESSES value in the database is set to 150. This may be too low for a production system.

If using SHARED SERVER:

– The Dispatcher has reached maximum connections and it’s refusing the newer ones.
– You can verify the Dispatcher’s status by querying the Listener services as follows:

For E.g.:

LSNRCTL> services [listener name]

Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 4 handler(s) for this service...
    Handler(s):
      "D001" established:5515 refused:7 current:245 max:2026 state:ready
         DISPATCHER , pid: >
         (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=))

DEDICATED

Increase the PROCESSES parameter to handle the number of processes that may be needed. Monitor for the ORA-12518 errors in the listener log and increase the value again if necessary. See the Oracle Database Administrator’s Guide 10.2 for these and database tuning details. PROCESSES is a static parameter so the instance will need to be restarted after a change.

SHARED SERVER

Shutdown the Dispatcher using the following command:

SQL> alter system shutdown immediate 'D001';

Later on add new dispatchers as follows:

SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)';
Note: This syntax depends on the way the Dispatcher value is given in the init.ora or Spfile.ora.

While increasing the DISPATCHERS value, also check shared servers ratio.

Filed Under: oracle

Some more articles you might also be interested in …

  1. ORA-28007: the password cannot be reused
  2. How to Move/Restore Oracle Database to New Host and File System using RMAN
  3. RMAN Restore/Duplicate Performs Implicit Crosschecking and Cataloging
  4. Oracle Database : What Is The Search Order For The LDAP.ORA File
  5. Oracle 12c Multitenant Architecture: Troubleshooting Ora-12518
  6. CentOS / RHEL 7 : Oracleasm Create Disk Failed “Instantiating disk: failed”
  7. How to start up the ASM instance when the spfile is misconstrued or lost
  8. How to resize an OCFS2 filesystem on Linux
  9. Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
  10. Why Can I Login AS SYSDBA With Any Username and Password

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • What Is Oracle Key Vault
  • Auditing with Oracle Database Vault Reports
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary