Unable to connect to pluggable database in Oracle 12c

Question

I have oracle database Oracle Database 12c Enterprise Edition 12.2.0.1.0, and I created a pluggable database named DEVDB to use it in web application development, I created it using the Database Configuration Assistant, during the creation process I created a user/password to connect to that DB, then I modified the file tnsnames.ora adding this:

 DEVDB =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = devdb)
 )
 )

Using DBeaver or SQL developer I can connect to the PDB successfully as you can see in the image blow:

enter image description here

but when I try to connect to the DB trying this from sqlplus command line:

sql> conn user/password@DEVDB

I get the following error message:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Warning: You are no longer connected to ORACLE.

This is show pdbs command result:

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

I reloaded lsnrctl many times and tried again but I get the same error.

I would like to be able to connect the PDB from sqlplus command line and understand the cause of that error

Any help please ??

Answer 1

Is the pluggable database (PDB) online?

Connect to the container database where you attached the PDB. Issue this SQL command: show pdbs; This should list all of your Pluggable Databases (including the seed PDB) and what state they are in.

You can start the DevDB database via this command: alter pluggable database DEVDB open;

If that works and the database opens and comes online, you can tell Oracle to "remember" this state via: alter pluggable database DEVDB save state;

If, however, the database does not come online via the alter...open command, check select * from PDB_PLUG_IN_VIOLATIONS; for possible problems.

From your edits, I see that the PDB is up and available. Try connecting to the DEVDB from another computer?

When you connect to the container, you can then switch your context to the PDB via alter session set container = DEVDB;

In my Oracle 12 environments, I do not have the PDBs listed explicitly in the server's TNSNames file (just the container DB). On client PCs, I then list the PDB in the TNSNAMES file.

On the server, I use the ORACLE_SID environment variable and do not use the @service, so: Set ORACLE_SID=DEVDB and then sqlplus user/pwd (But that is in a Windows environment.)

• rainman: Thank you for your help, I edited my question, the result of: select * from PDB_PLUG_IN_VIOLATIONS; is: no rows selected
• CaM: I've added a couple of things to check, then, based on that.
• jmk: @CaM are you sure that setting the ORACLE_SID to the PDB name works? On my Linux system I get just the Connected to an idle instance. line if I try to do that.
• CaM: In linux, you use either setenv or export to set up the environment variables. docs.oracle.com/database/121/ADMQS/… Those commands only work in unix/linux, not in Windows.