Forum Stats

  • 3,855,624 Users
  • 2,264,535 Discussions
  • 7,906,117 Comments

Discussions

ORA-12518: TNS:listener could not hand off client connection

user130038
user130038 Member Posts: 813
edited Nov 13, 2015 9:52AM in Multitenant

Hello there

I am getting "ORA-12518: TNS:listener could not hand off client connection" error while trying to connect to the "PDB". Here are the details of my env and what I am doing:

Windows 7

My PC has Oracle 11gR2 Client installed (this is how I got this PC). And I already have Oracle 11gR2 software installed and a local database named "ORCL" created (which is currently shutdown).

Yesterday, I installed Oracle 12c  (12.1.0) and created ORCL12C (CDB) and a PDBORCL (PDB). I connected to the PDBORCL as SYS and created user "SCOTT".

I am able to connect to ORCL12C (cdb) with no issues (from SQL*Plus and TOAD). I can switch to PDBORCL (alter session set container=pdborcl) with no issue. But I get the above error (ora-12518) when I try to connect to "SCOTT" user created in "PDBORCL" using SQL*Plus or TOAD. I want to connect to SCOTT user and create the SCOTT schema objects, etc. I get the same error even if I try to connect to the PDB as SYS user (alter session command works fine).

I am new to 12C. So it is entirely possible that I am doing something fundamentally wrong.

C:\app\oracle\product\12.1.0\dbhome_1\BIN>sqlplus sys as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 12 12:06:37 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2  250475996 PDB$SEED                       READ ONLY
         3   94279121 PDBORCL                        READ WRITE
SQL>  select service_id,name,pdb from v$services;


SERVICE_ID NAME                                                             PDB
---------- ---------------------------------------------------------------- --------------
         6 pdborcl                                                          PDBORCL
         5 orcl12cXDB                                                       CDB$ROOT
         6 orcl12c                                                          CDB$ROOT
         1 SYS$BACKGROUND                                                   CDB$ROOT
         2 SYS$USERS                                                        CDB$ROOT


SQL>



SQL> select substr(username, 0, 15) username, user_id, account_status from dba_users where username = 'SCOTT';


USERNAME                                                        USER_ID ACCOUNT_STATUS
------------------------------------------------------------ ---------- --------------------------------
SCOTT                                                               103 OPEN


SQL>





SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conenct scott
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> alter session set container=pdborcl;
Session altered.
SQL> connect [email protected]
Enter password:
ERROR:
ORA-12518: TNS:listener could not hand off client connection
Warning: You are no longer connected to ORACLE.
SQL>


Listener is using "C:\ORACLE\ora11g\Network\Admin\listener.ora" file and the "C:\ORACLE\ora11g\Network\Admin\tnsnames.ora".

Listener status:

C:\>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-NOV-2015 12:01:47
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                12-NOV-2015 10:34:54
Uptime                    0 days 1 hr. 26 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\ORACLE\ora11g\Network\Admin\listener.ora
Listener Log File         c:\app\oracle\diag\tnslsnr\MYPC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=blah.blah.com)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL12C" has 1 instance(s).
  Instance "ORCL12C", status UNKNOWN, has 1 handler(s) for this service...
Service "PDBORCL" has 1 instance(s).
  Instance "PDBORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\>

TNSNAMES.ORA file:

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = blah.blah.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = blah.blah.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

listener.ora file:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_2)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\11.2.0\dbhome_2\bin\oraclr11.dll")
    )
   (SID_DESC =
     (GLOBAL_DBNAME = ORCL12C)
     (SID_NAME = ORCL12C)
     (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   )
   (SID_DESC =
     (GLOBAL_DBNAME = PDBORCL)
     (SID_NAME = PDBORCL)
     (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = blah.blah.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\oracle

I read many posts on this error but none of the solutions provided helped.

Please let me know if I have missed any information which may help in narrowing down the issue.

Please advise!

Regards

Best Answer

  • mbe7
    mbe7 Member Posts: 93 Blue Ribbon
    edited Nov 13, 2015 1:46AM Answer ✓

    Hi ,

    I think you're problem results of the following listener configuration:

    >

    1.    (SID_DESC = 
    2.      (GLOBAL_DBNAME = PDBORCL) 
    3.      (SID_NAME = PDBORCL) 
    4.      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) 
    5.    ) 
    6.   ) 

    >


    as you see, you defined a static listener entry for SID PDBORCL. If I read your post correctly, you're CDB Instance SID is ORCL12C and you're PDB is PDBORCL.

    So in my opinion that part of the configuration is the problem. As PDB is registered as dynamic Service to the Instance you added

    a static listener entry for a non existing Instance. And thus you're configuration of the static listener overlays the dynamic PDB Service PDBORCL,

    and that's why it is not working.


    Look at this output from a successfull registerd PDB at a Listener:


    lsnrctl status


    ...

    Services Summary...

    Service "+ASM" has 1 instance(s).

      Instance "+ASM", status READY, has 1 handler(s) for this service...

    Service "ORCL12C" has 2 instance(s).

      Instance "ORCL12C", status UNKNOWN, has 1 handler(s) for this service...

      Instance "ORCL12C", status READY, has 1 handler(s) for this service...

    Service "PDBORCL" has 1 instance(s).

      Instance "ORCL12C", status READY, has 1 handler(s) for this service...

    The command completed successfully

    ..


    Regards


    Timo



Answers

  • Unknown
    edited Nov 12, 2015 5:59PM

    There are several things I don't understand about what you posted.

    Yesterday, I installed Oracle 12c  (12.1.0) and created ORCL12C (CDB) and a PDBORCL (PDB). I connected to the PDBORCL as SYS and created user "SCOTT".
    

    Looks like PDBORCL is the sample database so it already has user scott and the sample schemas.

    So why are you creating your own user SCOTT? Please explain.

    Line #12 shows you are connected to the root/CDB. And line #40 shows a user SCOTT.

    That means you created user SCOTT in the root/CDB and NOT the PDB.

    SQL> alter user scott identified by tiger account unlock;  
    User altered.  
    SQL> conenct scott  
    Enter password:  
    ERROR:  
    ORA-01017: invalid username/password; logon denied  
    Warning: You are no longer connected to ORACLE.  
    SQL> alter session set container=pdborcl;  
    Session altered.  
    SQL> connect [email protected]  
    Enter password:  
    ERROR:  
    ORA-12518: TNS:listener could not hand off client connection  
    Warning: You are no longer connected to ORACLE.  
    
    

    Line #3 has a syntax error. You entered 'conenct' instead of 'connect'. That is why you got the exception on line #6

    Line #7 says you are no longer connected to Oracle. So how can you alter your session in line #8?

    Either you posted the wrong info or you created user SCOTT in the root/CDB.

    user130038
  • mbe7
    mbe7 Member Posts: 93 Blue Ribbon
    edited Nov 13, 2015 1:46AM Answer ✓

    Hi ,

    I think you're problem results of the following listener configuration:

    >

    1.    (SID_DESC = 
    2.      (GLOBAL_DBNAME = PDBORCL) 
    3.      (SID_NAME = PDBORCL) 
    4.      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) 
    5.    ) 
    6.   ) 

    >


    as you see, you defined a static listener entry for SID PDBORCL. If I read your post correctly, you're CDB Instance SID is ORCL12C and you're PDB is PDBORCL.

    So in my opinion that part of the configuration is the problem. As PDB is registered as dynamic Service to the Instance you added

    a static listener entry for a non existing Instance. And thus you're configuration of the static listener overlays the dynamic PDB Service PDBORCL,

    and that's why it is not working.


    Look at this output from a successfull registerd PDB at a Listener:


    lsnrctl status


    ...

    Services Summary...

    Service "+ASM" has 1 instance(s).

      Instance "+ASM", status READY, has 1 handler(s) for this service...

    Service "ORCL12C" has 2 instance(s).

      Instance "ORCL12C", status UNKNOWN, has 1 handler(s) for this service...

      Instance "ORCL12C", status READY, has 1 handler(s) for this service...

    Service "PDBORCL" has 1 instance(s).

      Instance "ORCL12C", status READY, has 1 handler(s) for this service...

    The command completed successfully

    ..


    Regards


    Timo



  • user130038
    user130038 Member Posts: 813
    edited Nov 13, 2015 9:52AM

    Hi Timo

    Thank you so much for this tip. I added thr entry to listener.ora thinking that (since my connection attempts to PDB were failing) it may help resolve the issue.

    I removed the entries from the listener.ora file as you advised, restarted the listener and now I am able to connect to both the CDB and PDB.

    Hi rp0428

    Thank you so much for your response. Mistakes in my post you pointed out were my mistakes when I was copy-pasting the info to this post - apologies for that.

    I think (almost sure) that when I installed and created the 12c database, I did not chose the option to add "sample schemas". Hence "SCOTT" schema was not created. So I decided to add it manually. Following output shows that I created the SCOTT user in the PDB and not the CDB.

    SQL> alter session set container=cdb$root;

    Session altered.


    SQL> select count(0) from dba_users where username = 'SCOTT';

      COUNT(0)

    ----------

             0

    SQL> alter session set container=pdborcl;

    Session altered.

    SQL> select count(0) from dba_users where username = 'SCOTT';

      COUNT(0)

    ----------

             1

    Thank you so much both!

    Regards

This discussion has been closed.