ORA-12518: TNS:listener could not hand off client connection
One cause can be an Oracle process consuming too much memory.
Should you be working with Multi threaded server connections, you might need to increase the value of large_pool_size.
So basically, search for limits that are reached.
One way you can do this is to issue following query on the database:
select * from v$resource_limit where trim(limit_value) != 'UNLIMITED' and current_utilization > decode(trim(limit_value), 'UNLIMITED', 999999999, limit_value)-10 and current_utilization!=0;A possible workaround is to set following parameter in the listener.ora and restart the listener:
DIRECT_HANDOFF_TTC_LISTENER=OFF
Welcome to our forum for Oracle error: ORA-12518 Add your own message
and getting the error (Unable to addd connection ) without giving any error code, when using
my IP address in place of localhost.
Kindly help. I am in between of a very important module.
We have install 10G and import data from 9i. some user are connected but others get this error
ORA-12518 Message 12518 not found product = RDBMS80; FACILITY=ORA
kindly advice me urgently on my email
thanks
Fix the client and choose the correct Oracle home. If this is a Windows client, you can try to use Oracle Locator Express
Can someone let me know ASAP?
Thanks.
Thanks in advanced.
CJ
ERROR:
ORA-12518: TNS:listener could not hand off client connection
and I am unable to Connect to Oracle
I have Core2 Duo Processor, 1 GB RAM, only Windows XP (SP2)& Oracle Installed
If so, please post them here
11-APR-2008 16:32:02 * (CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)(CID=(PROGRAM=C:\Program Files\AgeTak\ARCEAC\ARCMultiplexer\ARCMUX.exe)(HOST=BDIPL28)(USER=BDIPL))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.28)(PORT=4867)) * establish * ORCL * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 233: Unknown error
Try to restart the OracleServiceORCL service using control panel.
You are probably reaching a Windows limit.
If you need 1000 concurrent connections, you might want to investigate configuring Multi Threaded Server for (some of) your database connections.
i'm getting the above errors radomly whn i try to connect to my application.i 've set the process_limt to 500. my sga_max_target=1400MB and PGA=194MB.can u tell me wat to do to slove dis problem
05-MAY-2008 09:10:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.AAMSUST.local)(CID=(PROGRAM=C:\PROGRA?1\EMC\AUTOST?1\eas53\modules\oraclewindows_3.1\oraclewindowsproxy.exe)(HOST=LAVL4183)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.241.135.135)(PORT=4965)) * establish * orcl.AAMSUST.local * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 1073741824: Unknown error
What could be the problem?
I even put the DIRECT_HANDOFF_TTC_LISTENER=OFF in listner.ora and still the same error.
Can it be that you have a value for pga_aggregate_target that exceeds 1Gb?
It was a stupid typo between the listener.ora and tnsnames.ora
Then I worked with ORACLE and PDS support for a couple of weeks. I did tunning and tested what I can do. But the error still there.
Finally, I found the issue was caused by Anti-virus application. on this box, IT update the Anti- to Microsoft Forefront but the old Trend one did not removed. I stop the Trend. then the error gone immediatly.
Change registry key
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\SubSystem\Windows
Windows SharedSection=1024, 3072,512 the 3rd value may be different for you.
Remove the 3rd value.
this related Microsoft article 126962 (http://support.microsoft.com/kb/126962/) "Out of Memory" error message appears when you have a large number of programs running
Ora-12518: TNS listener could not hand off client connection.
where insert this
DIRECT_HANDOFF_TTC_LISTENER=OFF in listner.ora
my listner.ora file is like this
# LISTENER.ORA Network Configuration File: D:\Oracle\Ora81\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HDMC_MIS1)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Oracle\Ora81)
(PROGRAM = extproc)
)
)
Hi,
We are getting "listener could not hand off client connection" error often when more load on Database.
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 2: No such file or directory
We are having a RAM of 3.5GB and 3 oracle DB's on Windows Server 2003 each having 584MB of SGA.
Current Maximum Initial
Resource Name Utilization Utilization Allocation Limit
------------------------------ ------------ ------------ ---------- ----------
processes 46 150 150 150
sessions 50 155 170 170
-------------------------------------------------------------
Please help me on this for resolution.
Thanks in advance.
Thanks & Regards,
Manjunatha Reddy MC
Hi all,
thanks in advance for your help.
We have a serious problem.
I have created a database in Oracle 11.2.0.1
> when I connect as local connection it works fine (SQLPLUS user/**)
> BUT when I connect as client connection we have a TNS-12518 error(sqlplus user/**@service_name)
here is the error messages :
///////////////////////////////
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
thanks in advance !
Would you happen to have ip restriction in place on the listener?
Check your $ORACLE_HOME/network/admin/sqlnet.ora file on the database server for entries like
tcp.validnode_checking = YES
tcp.invited_nodes = {list of IP addresses}
tcp.excluded_nodes = {list of IP addresses}
Hi,
1°) there is no restriction in the listener;
in our SQLNET.ora we have only this parameter :
"NAMES.DIRECTORY_PATH= (TNSNAMES)"
2°) We did two tests :
- I have re-created my database on other unix server with Oralce 11.1.0.7 => it-s work fine
- I tried a client connection with an other instance on my server (Orcacle 11.2.0.1) => it-s work fine
3°) here is our listener : it work's fine for SID_NAME "DBTEST01" and "DBTEST02" and it's NOT OK ofr instance "tbi840_1"
LISTENER=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=rs53)
(PORT=1563)
) )
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=IPC)
(KEY=ORA_11_2_0_1)
) ) ) )
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=DBTEST01)
(ORACLE_HOME=/oracle/product/11.2.0.1)
)
(SID_DESC=
(SID_NAME=DBTEST02)
(ORACLE_HOME=/oracle/product/11.2.0.1)
)
(SID_DESC=
(SID_NAME=tbi840_1)
(ORACLE_HOME=/oracle/product/11.2.0.1)
)
)
So, I still need your help,
thanks.
Do you have enough memory to run 3 database on 1 server?
What is the setting of pga_aggregate_target and processes?
What is the output of
lsnrctl services
lsnrctl status
If you have enough memory on the server, is it possible to create a new database, but without the underscore in the SID? Maybe this is causing you problems.
Is the size of listener.log > 2Gb? In the past, this caused problems as well.
So,
1°) yes we have enough memory (30 Go) for our databases; my instance is defined with
"memory_target= 1G"
2°) I've just created the database without the underscore (tbi8401) and we have the same error
3°) the listener.log is under 2 Go (about 240 Ko); here is the messages in listener.log
<txt>TNS-12518: TNS : le processus d'écoute n'a pas pu transmettre la connexion client
TNS-12547: TNS : contact perdu
TNS-12560: TNS : erreur d'adaptateur de protocole
TNS-00517: Contact perdu
IBM/AIX RISC System/6000 Error: 32: Broken pipe
4°) pga_aggregate_target and processes
NAME TYPE VALUE
------------------------------- --------------- -------
processes integer 200
pga_aggregate_target big integer 0
here is a view of V$MEMORY_DYNAMIC_COMPONENTS
SQL> select component, current_size, min_size, max_size, last_oper_time from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE LAST_OPER_T
------------------------------ ------------ ---------- ---------- -----------
shared pool 226492416 176160768 226492416 01-SEPT.-10
large pool 4194304 0 4194304 01-SEPT.-10
java pool 4194304 4194304 4194304
streams pool 0 0 0
SGA Target 641728512 641728512 641728512
DEFAULT buffer cache 394264576 394264576 444596224 01-SEPT.-10
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
PGA Target 432013312 432013312 432013312
ASM Buffer Cache 0 0 0
5°) LSNRCTL SERVICES
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 01-SEP-2010 16:37:57
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rs53)(PORT=1563)))
Services Summary...
Service "D0TRT2" has 1 instance(s).
Instance "D0TRT2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:5 refused:0
LOCAL SERVER
Service "D0TRT3" has 1 instance(s).
Instance "D0TRT3", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "D1TRT2" has 1 instance(s).
Instance "D1TRT2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "D1TRT3" has 1 instance(s).
Instance "D1TRT3", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "D2TRT2" has 1 instance(s).
Instance "D2TRT2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "D2TRT3" has 1 instance(s).
Instance "D2TRT3", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "D3TRT2" has 1 instance(s).
Instance "D3TRT2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "D3TRT3" has 1 instance(s).
Instance "D3TRT3", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "ECFS_11_2" has 1 instance(s).
Instance "ECFS_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "EC_11_2" has 1 instance(s).
Instance "EC_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "EFCB_11_2" has 1 instance(s).
Instance "EFCB_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "EG_11_2" has 1 instance(s).
Instance "EG_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "EI_11_2" has 1 instance(s).
Instance "EI_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "ELC_11_2" has 1 instance(s).
Instance "ELC_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "EL_11_2" has 1 instance(s).
Instance "EL_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "ENB_11_2" has 1 instance(s).
Instance "ENB_11_2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "Q0TRT2" has 1 instance(s).
Instance "Q0TRT2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "Q0TRT3" has 1 instance(s).
Instance "Q0TRT3", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "Q1TRT2" has 1 instance(s).
Instance "Q1TRT2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "Q1TRT3" has 1 instance(s).
Instance "Q1TRT3", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "SATRT2" has 1 instance(s).
Instance "SATRT2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "SATRT3" has 1 instance(s).
Instance "SATRT3", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "tbi8401" has 1 instance(s).
Instance "tbi8401", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:1
LOCAL SERVER
Service "tbi840_1" has 1 instance(s).
Instance "tbi840_1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
6°) LSNRCTL STATUS
RS53-tbi840-1:/timint/tbi840/V1/bin/dbora/tbi8401> lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 01-SEP-2010 16:39:13
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rs53)(PORT=1563)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date 01-SEP-2010 14:08:22
Uptime 0 days 2 hr. 30 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.1/network/admin/listener.ora
Listener Log File /oracle/product/11.2.0.1/log/diag/tnslsnr/rs53/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rs53)(PORT=1563)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ORA_11_2_0_1)))
Services Summary...
Service "D0TRT2" has 1 instance(s).
Instance "D0TRT2", status UNKNOWN, has 1 handler(s) for this service...
Service "D0TRT3" has 1 instance(s).
Instance "D0TRT3", status UNKNOWN, has 1 handler(s) for this service...
Service "D1TRT2" has 1 instance(s).
Instance "D1TRT2", status UNKNOWN, has 1 handler(s) for this service...
Service "D1TRT3" has 1 instance(s).
Instance "D1TRT3", status UNKNOWN, has 1 handler(s) for this service...
Service "D2TRT2" has 1 instance(s).
Instance "D2TRT2", status UNKNOWN, has 1 handler(s) for this service...
Service "D2TRT3" has 1 instance(s).
Instance "D2TRT3", status UNKNOWN, has 1 handler(s) for this service...
Service "D3TRT2" has 1 instance(s).
Instance "D3TRT2", status UNKNOWN, has 1 handler(s) for this service...
Service "D3TRT3" has 1 instance(s).
Instance "D3TRT3", status UNKNOWN, has 1 handler(s) for this service...
Service "ECFS_11_2" has 1 instance(s).
Instance "ECFS_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "EC_11_2" has 1 instance(s).
Instance "EC_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "EFCB_11_2" has 1 instance(s).
Instance "EFCB_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "EG_11_2" has 1 instance(s).
Instance "EG_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "EI_11_2" has 1 instance(s).
Instance "EI_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "ELC_11_2" has 1 instance(s).
Instance "ELC_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "EL_11_2" has 1 instance(s).
Instance "EL_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "ENB_11_2" has 1 instance(s).
Instance "ENB_11_2", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "Q0TRT2" has 1 instance(s).
Instance "Q0TRT2", status UNKNOWN, has 1 handler(s) for this service...
Service "Q0TRT3" has 1 instance(s).
Instance "Q0TRT3", status UNKNOWN, has 1 handler(s) for this service...
Service "Q1TRT2" has 1 instance(s).
Instance "Q1TRT2", status UNKNOWN, has 1 handler(s) for this service...
Service "Q1TRT3" has 1 instance(s).
Instance "Q1TRT3", status UNKNOWN, has 1 handler(s) for this service...
Service "SATRT2" has 1 instance(s).
Instance "SATRT2", status UNKNOWN, has 1 handler(s) for this service...
Service "SATRT3" has 1 instance(s).
Instance "SATRT3", status UNKNOWN, has 1 handler(s) for this service...
Service "tbi8401" has 1 instance(s).
Instance "tbi8401", status UNKNOWN, has 1 handler(s) for this service...
Service "tbi840_1" has 1 instance(s).
Instance "tbi840_1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Thanks & Regards,
Can you post the content of:
select * from v$resouce_limit
Do you see additional errors in the alert.log file?
If you used the previous method of memory allocation (so using sga_target/sga_max_size) instead of memory_target, do you still get this error?
Do you have enough swap space available?
Is the setting of 'allowed processes' large enough on Unix level?
1°) resource_limit :
SQL> select * from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
-------------------- -------------------------- -------------------- ------------- -------------
processes 22 31 200 200
sessions 27 41 324 324
enqueue_locks 106 120 3980 3980
enqueue_resources 105 135 883 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
gcs_resources 0 0 0 0
gcs_shadows 0 0 0 0
dml_locks 0 0 600 UNLIMITED
temporary_table_locks 0 3 UNLIMITED UNLIMITED
transactions 0 0 356 UNLIMITED
branches 0 0 356 UNLIMITED
cmtcallbk 0 1 356 UNLIMITED
max_rollback_segments 11 11 356 65535
sort_segment_locks 0 3 UNLIMITED UNLIMITED
k2q_locks 0 0 648 UNLIMITED
max_shared_servers 0 0 UNLIMITED UNLIMITED
parallel_max_servers 0 0 60 3600
1°) additional erros in "LOG.XML" :
- I don't know if this message is an error (and I don't think it) :
<txt>Directory does not exist for read/write [/oracle/product/11.2.0.1/log] [/oracle/product/11.2.0.1/log/diag/clients]
- error message in SQLNET.log file
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rs53)(PORT=1563))(CONNECT_DATA=(SID=tbi840_1)(CID=(PROGRAM=sqlplus@rs53)(HOST=rs53)(USER=tbi840))))
VERSION INFORMATION:
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Time: 01-SEP-2010 14:09:59
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
3°) Do you have enough swap space available?
in our server we have 30 Go memory and 60 Go swap ; and 0 % of swap was used;
4°) Is the setting of 'allowed processes' large enough on Unix level?
It can't be a problem of "allowed processes"; in fact my Unix administrator tried to connect my database with the ORACLE user and the problem is the same;
the user unix have a number of processes allowed ILLIMITED. So my user is UNLIMITED too.
Here is the détail of the command 'ulimit -a' for my Unix user :
RS53-tbi840-1:/timint/tbi840/V1> ulimit -a time(seconds) unlimited file(blocks) unlimited data(kbytes) 262144 stack(kbytes) 32768 memory(kbytes) 65536 coredump(blocks) 2097151 nofiles(descriptors) 2000 threads(per process) unlimited processes(per user) unlimited
regards,
Hi, all,
any suggestions for my problem ?
Hi all,
now the connection work's fine;
explications : the problem was a problem of "acces denied" on a directory.
Suppose you create a UNIX database on /filesystem/appli/oradata/etc....; well, since Oralce 11g if the owner of the directory "oradata" is not the group "dba" then you'll have the error TNS-12518 when you'll tried to connect the database using the listener.
See you soon and thanks for your help,
Note : this problem doesn't exist with Oralce 10g
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.