Unable to connect to PDBs in Oracle12cR2 on Windows 7

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 des

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.

StackExchange Database Administrators Q#195876, answer score: 2