Unable to connect to PDBs in Oracle12cR2 on Windows 7

oracle-12c

I am unable to connect to PDB databases of Oracle12cR2. Here are the steps, information of results and some associated files. Please help and thanks in advance!

1) Installed Oracle12cR2 on Windows 7 using all default settings

2) In addition to auto-created OralPDB, manually created one more PDB
called PDBSAM, and created a user Sam in PDBsam.

3) Manually add ORCLPDB and PDBSAM to tnsnames.ora and Listener.ora

4) Both ORCLPDB and PDBSAM are in read/write open mode.
I will use PDBSAM as an example. The same connection error to ORCLPDB.

5) steps:

C:\Users\SChen>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 21 11:03:09 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>
SQL> show con_name

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

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 PDBSAM                         READ WRITE NO

SQL> select user, sysdate from dual;

USER                 SYSDATE
-------------------- ---------
SYS                  21-JAN-18

SQL> alter session set container = pdbsam;

Session altered.

SQL> select username, default_tablespace, account_status from dba_users where username = 'SAM';

USERNAME   DEFAULT_TABLESPACE             ACCOUNT_STATUS
---------- ------------------------------ ---------------------
SAM        SYSTEM                         OPEN

SQL> rem  now trying to connect to PDBSAM as user SAM
SQL> rem
SQL> conn sam@pdbsam
Enter password:
ERROR:
ORA-12518: TNS:listener could not hand off client connection

Warning: You are no longer connected to ORACLE.

6) When PDBSAM entry is removed from listener.ora, I had a different connection error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Warning: You are no longer connected to ORACLE.

7) tnsnames.ora content:

ORCL =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host = localhost)(Port= 1521))
    (CONNECT_DATA = (SERVICE_NAME = orcl)))

ORCLPDB =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host = localhost)(Port= 1521))
    (CONNECT_DATA = (SERVICE_NAME = orclpdb)))

PDBSAM =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host = localhost)(Port= 1521))
    (CONNECT_DATA = (SERVICE_NAME = pdbsam)))

8) listener.ora content:

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (SID_NAME = orclpdb)
      (ORACLE_HOME = C:\app\SAM\virtual\product\12.2.0\dbhome_1)
     )
    (SID_DESC =
      (global_dbname = pdbsam) 
      (SID_NAME = pdbsam)
      (ORACLE_HOME = C:\app\SAM\virtual\product\12.2.0\dbhome_1)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\SAM\virtual\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\SAM\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

9) listener status result:

C:\Users\SAM>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 21-JAN-2018 11:15:23

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date                21-JAN-2018 10:49:26
Uptime                    0 days 0 hr. 26 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\SAM\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\SAM\virtual\diag\tnslsnr\MSC-JZD3HV1LT\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "orclpdb", status UNKNOWN, has 1 handler(s) for this service...
Service "pdbsam" has 1 instance(s).
  Instance "pdbsam", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Best Answer

In this:

 (SID_DESC =
  (SID_NAME = orclpdb)
  (ORACLE_HOME = C:\app\SAM\virtual\product\12.2.0\dbhome_1)
 )
 (SID_DESC =
   (global_dbname = pdbsam) 
   (SID_NAME = pdbsam)
   (ORACLE_HOME = C:\app\SAM\virtual\product\12.2.0\dbhome_1)
 )

SID_NAME should be replaced with the name of your instance. After that, restart the listener.

One way to find the name of the instance is: show parameter instance_name, while logged in. Based on your tnsnames.ora, I guess your instance is called orcl. So:

 (SID_DESC =
  (global_dbname = orclpdb) 
  (SID_NAME = orcl)
  (ORACLE_HOME = C:\app\SAM\virtual\product\12.2.0\dbhome_1)
 )
 (SID_DESC =
   (global_dbname = pdbsam) 
   (SID_NAME = orcl)
   (ORACLE_HOME = C:\app\SAM\virtual\product\12.2.0\dbhome_1)
 )

SID_NAME means the instance name. When you create PDBs, you are not creating new instances.

Related Question