I have a local installation of Oracle. I want to add the XMLDB package to it. As step 1, I need to connect to it via sqlplus. And I am not able to.
The installation has a database called Pm.
Here is what I have tried on the command line:
sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 12:11:44 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: SYSTEM@PM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name: SYSTEM@Pm
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name: system@PM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
C:\Users\sgupta>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 12:14:15 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system@Pm
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Here is what the tnsnames.ora file looks like:
# tnsnames.ora Network Configuration File: C:\app\sgupta\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
PM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Pm)
)
)
CM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CM)
)
)
PM20154 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Pm20154)
)
)
And I have checked that the service is running:
Not sure why I keep getting the error 'ORA-12154: TNS:could not resolve the connect identifier specified'.
Best Answer
TLDR; Copy listener.ora and tnsnames.ora from app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN to app/sgupta/product/11.2.0/dbclient_1/network/admin
I had the tnsnames.ora and listern.ora files in C:/app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN.
When I used tnsping on the command line, i was getting following error message:
I noticed how it was looking in the C:\app\sgupta\product\11.2.0\client_1\network\admin folder. And that the sqlnet.ora looked like following:
So I figured sqlnet.ora is expecting a TNSNAMES file in the same folder.
So I copied listener.ora and tnsnames.ora from app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN to app/sgupta/product/11.2.0/dbclient_1/network/admin.
After that tnsping worked. And sqlplus also connected.