Home » RDBMS Server » Server Administration » ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c (Window 10, 12C)
ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646331] Sat, 26 December 2015 13:13 Go to next message
Bilal Khan
Messages: 117
Registered: April 2010
Location: Pakistan
Senior Member
Hello Experts,

After successfully installing the 12c, how can I connect to pdb to use the scott or hr schemas? By default when I connect as sys or system from sqlplus it is connecting to orcle container i.e orcl not to portible database i.e pdborcl.
i did following steps.
Please help me in this regards.
SQL> CONN SYS AS SYSDBA
Connected.
SQL> show con_name;
CON_NAME 
------------------------------
CDB$ROOT

SQL> select name, con_id from v$pdbs;

NAME                               CON_ID
------------------------------ ----------
PDB$SEED                                2
PDBORCL                                 3


SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORCL                        READ WRITE


SQL> alter session set container = pdborcl;

Session altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDBORCL                        READ WRITE


SQL> alter user hr account unlock;

User altered.

SQL> alter user hr identified by hr;

User altered.


SQL> select table_name from dba_tables
  2  where owner = 'HR';

TABLE_NAME
----------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES

7 rows selected.


SQL> CONN hr/hr @pdborcl;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified 


Warning: You are no longer connected to ORACLE.



As my tnsnames.ora file is as:

# tnsnames.ora Network Configuration File: D:\app\Bilal\product\12.1.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1)
    )
  )

PBDORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

Please help me in this regards that how can i solve this issue

SQL> CONN hr/hr @pdborcl;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Thanks and Regards
Re: ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646332 is a reply to message #646331] Sat, 26 December 2015 19:07 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
post results of following OS commands

lsnrctl status
lsnrctl service

>LISTENER_ORCL1 =
above is non-standard listener name & should be avoided.

>CONN hr/hr @pdborcl;
if you are logged onto the DB Server itself, then there is no need to use SQL*Net (@pdborcl) to connect to any local database.

>(HOST = localhost)
when above is used, then no remote client can ever connect to remote database



Re: ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646333 is a reply to message #646332] Sat, 26 December 2015 22:39 Go to previous messageGo to next message
Bilal Khan
Messages: 117
Registered: April 2010
Location: Pakistan
Senior Member
Dear Sir
The result of lsnrctl status is as
C:\WINDOWS\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 27-DEC-2015 08:54:23
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-CNSB38I)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                26-DEC-2015 21:14:30
Uptime                    0 days 11 hr. 39 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Bilal\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\Bilal\product\12.1.0\dbhome_1\log\diag\tnslsnr\DESKTOP-CNSB38I\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-CNSB38I)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-CNSB38I)(PORT=5500))(Security=(my_wallet_directory=D:\APP\BILAL\admin\orcl1\xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl1" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl1XDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdborcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully




while the result of lsnrctl service
C:\WINDOWS\system32>lsnrctl services

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 27-DEC-2015 08:57:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-CNSB38I)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl1" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "orcl1XDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: DESKTOP-CNSB38I, pid: 10520>
         (ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-CNSB38I)(PORT=58554))
Service "pdborcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
.

sir as i am working on my laptop and oracle 12C is install on laptop, and when i write CONN hr/hr, (Without @pdborcl) it display error message invalid user name/password.
Re: ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646334 is a reply to message #646333] Sun, 27 December 2015 01:44 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Can you try this EZconnect syntax -

connect hr/hr@DESKTOP-CNSB38I:1521/pdborcl

Re: ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646336 is a reply to message #646334] Sun, 27 December 2015 14:19 Go to previous messageGo to next message
Bilal Khan
Messages: 117
Registered: April 2010
Location: Pakistan
Senior Member
Thanks Sir,
its working properly and connect with HR Schema when we connected with Sysdba.
But when i want to directly connect at beginning with HR Schema it display error message i.e
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 28 00:38:36 2015

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

Enter user-name: hr@DESKTOP-CNSB38I:1521/pdborcl
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name: hr@pdborcl
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

How can we resolve this issue.

2nd problem that i face is that whenever i connect with sysdba, it directly connect with container database. i.e
Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT




Is there is any method that we can directly connect with pluggable (PDB) database without connecting with CDB.

Thanks.

[Updated on: Sun, 27 December 2015 14:45]

Report message to a moderator

Re: ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646337 is a reply to message #646336] Sun, 27 December 2015 14:50 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Of course there is a way - you do what I suggested. Which you did not do.

Re: ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646338 is a reply to message #646337] Sun, 27 December 2015 22:49 Go to previous messageGo to next message
Bilal Khan
Messages: 117
Registered: April 2010
Location: Pakistan
Senior Member
Thanks John Watson.
Please suggest me that what should i do to directly connect with PDB instead of CDB and how can i connect with HR Schema from very first page.

Once again thanks.
Re: ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646340 is a reply to message #646338] Mon, 28 December 2015 01:23 Go to previous message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
I suggest that you read and follow simple instructions, such as that which I gave to you.
Previous Topic: How many Oracle instance on Dual Core System
Next Topic: Listener TNS-00515: Connect failed because target host or object does not exist
Goto Forum:
  


Current Time: Sat Oct 20 03:45:06 CDT 2018