Solved

ORA-12514

Posted on 2011-02-25
16
328 Views
Last Modified: 2012-05-11
http://www.experts-exchange.com/Database/Oracle/Q_26848079.html

I have opened a new thread for this question.
I am having this trouble and I am connecting to oracle database by "DIRECT" giving host address, port and sid, instead of the usual tnsand alias..
Please help me fix this...
0
Comment
Question by:D-pk
  • 9
  • 7
16 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34981903
Either configure the tnsnames.ora file with the proper information or try the ezconnect method.

I've never tried this with exp but it appears to work for sqlplus:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:45033135081903

try (replace the information with your user/password and dbserver info:

exp 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=152
1)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora9ir2.kyte.com)))'
0
 

Author Comment

by:D-pk
ID: 34999144
Sorry for the delayed response...
I have just the oracle client installed on my local PC (When I installed it, I didnt even have the tnsnames.ora file I copied that in to the ADMIN folder). I got the tnsnames.ora file from my teammate. He is able to login with the alias with TNS option. But I am not able to, I have to use the "DIRECT" option, IP address of the database and its SID and port to login. If I use the TNS option its throwing the error:

ORA-12154 -- TNS: could not resolve the connect identifier specified.
I am using TOAD tool

Thanks@
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34999268
I've never used Toad so I'm not sure if it has it's own Oracle piece or uses an installed Oracle client.

Anyway the ora-12154 means there is either a configuration issue with your tnsnames.ora file or you aren't using a valid entry in that file.

since you copied it from a co-worker, are they on the same network you are on and are they connecting to the same databases you are on?

If you have sqlplus installed, I would prefer to troubleshoot this error using sqlplus and not Toad.
0
 

Author Comment

by:D-pk
ID: 34999304
I know posted on the wrong thread and got confused, I dont know how to delete it from the other one...

I am not talking about the exp utility. I want to clarify about just this error.
Whenever I try to login I get this error. I want to fix this first.

Yes my co-worker is on the same network and connecting to the same database.

I tried using SQL*PLUS, I still get the same error.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34999355
>> dont know how to delete it from the other one...

I've taken care of deleting the posts in that thread.

>>I tried using SQL*PLUS, I still get the same error.

Do you have more than one Oracle client/product installed on the machine?

Are you running sqlplus from UNIX or Windows?
0
 

Author Comment

by:D-pk
ID: 34999488
Oracle Client installed on my local PC windows, the sever is in UNIX box.
I am running sqlplus in my local windows machine.
In unix it works fine..

Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34999526
If the tnsnames.ora file from the Windows machine doesn't contain any sensitive server names or database names, can you post it?

If the names are sensitive, can you dummy them up and post the dummied file?
0
 

Author Comment

by:D-pk
ID: 34999579
PPROD =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = 192.42.185.211)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SID = fprdd1)

    )
  )
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34999982
From a cmd prompt post the results of:

ping 192.42.185.211
and
tnsping pprod
0
 

Author Comment

by:D-pk
ID: 35000520
the ping is fine..
But tnsping gave me the following error:

Used parameter files:
C:\oracle\product\10.2.0\client_3\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.42.185.211) (PORT = 1521)) (CONNECT_DATA = (SID = fprdd1)))
OK (20 msec)

My SQLNET.ORA file contains:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT)

Thanks@
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35000583
>>But tnsping gave me the following error:

What error? It ran fine.  See "OK (20 msec)"

now from a command prompt:
sqlplus username/password@PPROD
0
 

Author Comment

by:D-pk
ID: 35000645
I gave this: sqlplus username/password@PPROD (Of Course I agve my username and password)
and got:

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 28 12:03:18 2011

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35000675
log into the server: 192.42.185.211 and from a command prompt post the results of: lsnrctl status
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35000685
I just noticed from your other post "C:\oracle\product\10.2.0\client_3"

This implies you have two other Oracle client installs.  Verify you are running the sqlplus from the proper ORACLE_HOME.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35000705
from a command prompt:
set TNS_ADMIN=C:\oracle\product\10.2.0\client_3\network\admin

then try: sqlplus username/password@PPROD
and/or
C:\oracle\product\10.2.0\client_3\bin\sqlplus username/password@PPROD
0
 

Author Closing Comment

by:D-pk
ID: 35001424
Perfect! I just uninstalled the other versions and it worked fine..
Thanks a lot slightwv!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Number Format 1 45
Invalid Identifier Error 3 65
What is the version of ojdbc6.jar 2 24
history tablespace temp usage 2 16
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now