Solved

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

Posted on 2011-03-01
10
750 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
[X]
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
  • 4
  • 3
  • 3
10 Comments
 
LVL 77

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

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 77

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 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

749 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