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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. Oracle sql script to report the list of files stored in ASM and CURRENTLY NOT OPENED
  2. Steps to relink Oracle Libraries
  3. How to Create Undo Tablespace for a Newly Added RAC Instance (ORA-30012)
  4. Troubleshooting Common ORA-1157 Errors (cannot identify/lock data file)
  5. How to get the Values Assigned by Default to a Profile in Oracle Database
  6. How To Create an Encrypted Tablespace in Oracle 12c Pluggable Database
  7. How to Modify an Existing ASM Spfile in a RAC Environment
  8. How to move ASM spfile to a shared device in RAC
  9. Define redo log files in an Oracle RAC environment
  10. RMAN Pluggable Database Backup and Recovery in a Multitenant Environment

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright