Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Can Oracle SQLPlus V 11.2.0.1.0 connect to an Oracle 10g database?

Posted on 2011-03-01
10
748 Views
Last Modified: 2012-05-11
I am new to Oracle 11g and I am trying to connect to a Oracle 10g database using the SQLPlus tool.  I am using SQLPlus V 11.2.0.1.0 and I am getting the following error when I try to connect.

C:\>SET ORACLE_SID=DEV3

C:\>sqlplus ora_test/password@dev3

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 1 11:17:21 2011

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
 
I made sure that my tnsnames.ora file has the correct connection information to this oracle 10g database.

Any ideas?
0
Comment
Question by:RayManAaa
  • 4
  • 3
  • 3
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35009031
Do you have multiple Oracle installs on that machine?

Make sure you are using the correct tnsnames.ora from the correct oracle home.



0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35009172
Yes it can but you need to have your sql*net configured

check sqlnet.ora for the path

if you are using tnsnames, then make sure tnsnames.ora is correct

if you are using OID then make sure ldap.ora is correct
0
 

Author Comment

by:RayManAaa
ID: 35010093
Do you have multiple Oracle installs on that machine?
Make sure you are using the correct tnsnames.ora from the correct oracle home.

No, this is a completely different machine.
Yes, I double checked and it looks correct.


Yes it can but you need to have your sql*net configured
check sqlnet.ora for the path
if you are using tnsnames, then make sure tnsnames.ora is correct
if you are using OID then make sure ldap.ora is correct

In regards to the sqlnet.ora file looks I have several for some reason.  I think the one you are referring to is located in the D:\ORA11g\product\11.2.0\dbhome_1\NETWORK\ADMIN directory.  But I also have another sqlnet.ora file located in the following directory D:\app\product\11.1.0\client_1\network\admin.

The one located in the first directory has the following information.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME, EZCONNECT)

The second location has this information.

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

How do I tell which sqlnet.ora file that I am really using?

I am using a Oracle account not a windows domain account.  




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: 35010122
>>No, this is a completely different machine.
>> But I also have another sqlnet.ora file located in the following directory D:\app\product\11.1.0\client_1\network\admin.

So, you DO have multiple ORACLE_HOMES.

you need to configure the tnsnames.ora file for the home that contains the sqlplus you are running.

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 35010699
(TNSNAMES, ONAMES, HOSTNAME, EZCONNECT)

this is wrong for 11g  ONAMES stopped being supported in after 9i


This one is correct,
(TNSNAMES, EZCONNECT)


you might be able to use HOSTNAME too,
depends on your system but it's not in common use that I've seen.
0
 

Author Comment

by:RayManAaa
ID: 35020560
Please forgive me for asking this question but how do I tell how many ORACLE_HOMES do I have?

Also, how do I tell if I have SYSDBA permssions to this Oracle 10g instance?

I made the changes to the SQLnet.ora file as requested and ran the following, but this also failed.

C:\>SET ORACLE_SID=DEV3

C:\>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 2 14:04:29 2011

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

Enter user-name: ora_test
Enter password:XXXX
ERROR:
ORA-12560: TNS:protocol adapter error

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35020604
check the registry

HKLM\SOFTWARE\ORACLE\KEY_xxxxxxxx


0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35020650
>>but how do I tell how many ORACLE_HOMES do I have?

answered above for Windows.

>>Also, how do I tell if I have SYSDBA permssions to this Oracle 10g instance?

try connecting 'as sysdba':  sqlplus /@10gdatabase as sysdba

If it fails, it might only mean that remove 'sys' logins are not allowed.  You verify the true permissions, ask the DBA or try connecting 'as sysdba' from the database server itself.

0
 

Author Comment

by:RayManAaa
ID: 35108541
Thank you guys,

it looks like I had several oracle_homes which was confusing me.  I have been installing Oracle 11g several times to learn about all the different installation options.  When I updated the correct sqlnet.ora file I was able to connect with no issues.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 35109154
Don't forget to close out the question and award the pints to the posts that helped.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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

792 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