ORA-12154: TNS:could not resolve the connect identifier specified in oracle 12c [message #646331] |
Sat, 26 December 2015 13:13 |
Bilal Khan
Messages: 128 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 #646333 is a reply to message #646332] |
Sat, 26 December 2015 22:39 |
Bilal Khan
Messages: 128 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 #646336 is a reply to message #646334] |
Sun, 27 December 2015 14:19 |
Bilal Khan
Messages: 128 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
|
|
|
|
|
|