Cannot connect to oracle pluggable database

Question

Iinstalled oracle in my Windows 10 64 bit pc and I started doing verification steps mentioned in the tutorial which's given in the oracle website which can be found in the link below.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/Windows_DB_Install_OBE/Installing_Oracle_Db12c_Windows.html

When I try to connect to my pluggable database using this command

C:\Users\Chathura>sqlplus sys/Oracle_1@orclpdb as sysdba;

it gives me following message

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 30 19:34:25 2018

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

But when I try to connect to container database it connects successfully.

This is how I created pluggable database.

I read articles regarding this tried adding TNS_ADMIN system variable too, but non of those worked. What I am doing wrong here?

you first need to check if the service of the pdb is known to the listener. This should work out of the box with dynamic listener registration. You can verify this with "lsnrctl status" — steve, Feb 08 '18 at 19:07

score 0 · Answer 1 · answered Jan 30 '18 at 15:11

You have to use the service name for your pluggable, you can't use the SID..that's only going to work for the container.

So find out the service name

[oracle@vbgeneric ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 30-JAN-2018 10:05:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                25-JAN-2018 12:14:50
Uptime                    4 days 21 hr. 50 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vbgeneric/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbgeneric)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "49b6c41a4c152576e0530100007feda7" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "4bb765ec28ff1109e0530100007f49e5" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "app_con" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", 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 "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@vbgeneric ~]$

Then connect, use @servicename vs @SID

in my case that's @orcl

Or connect to your container, then switch over to your PDB

oracle@vbgeneric oracle]$ sqlplus  sys/oracle@orcl12c as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 30 10:09:28 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> ALTER SESSION SET container = orcl;

Session altered.

SQL>

Tim talks about this here.

score 0 · Answer 2 · answered Mar 15 '18 at 09:44

connect to your database ( pluggable database) and make thing configuration
` 
startup mount;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
`
and also that
`
update sys.props$ set VALUE$='WE8MSWIN1252' where NAME='NLS_CHARACTERSET';
 commit;
 shutdown immediate;
 startup;
`
after that just check your tnsnames.ora`enter code here`

score 0 · Answer 3 · edited Jan 30 '22 at 20:47

WINDOWS 10:

Don't know if this is exactly your problem, but I got one very similar. After creating new users to PDB, I couldn't connect to Database. Here is what I did (don't know if you need all of this, but it worked for me).

Try this options on SQL Dev Connection:

connection

On $ORACLE_HOME/network/admin add:

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

On network/admin/sqlnet.ora file add:

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8

Cannot connect to oracle pluggable database

3 Answers3