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?

Answer 1

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.

Answer 2

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`

Answer 3

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).

1. Try this options on SQL Dev Connection:

connection

2. On $ORACLE_HOME/network/admin add:

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

3. On network/admin/sqlnet.ora file add:

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8