Solved

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

Posted on 2011-03-01
10
737 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 73

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
 
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 73

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
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.

 

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 73

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

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 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

10 Experts available now in Live!

Get 1:1 Help Now