0

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 ??

2

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

|improve this answer|||||
  • Thank you for your help, I edited my question, the result of: select * from PDB_PLUG_IN_VIOLATIONS; is: no rows selected – rainman Nov 8 '17 at 21:16
  • I've added a couple of things to check, then, based on that. – CaM Nov 8 '17 at 21:23
  • @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. – jmk Nov 9 '17 at 9:45
  • 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. – CaM Nov 9 '17 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.