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 scott@pdborcl
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