Solved

Oracle 9i and Oracle 11g

Posted on 2011-02-15
10
1,002 Views
Last Modified: 2012-05-11
Hi,
I have 2 Oracle Installation: Oracle 9i client and Oracle 11.2 server
Oracle 9i in: C:\oraclient9\
Oracle 11g in: C:\app\a.l\product\11.2.0\

I've an application that have to connect to Oracle via ODBC But every time I try to connect the odbc search Oracle 9i and I get this error:
ORA-12541: TNS no listener

If I try to start with sqlplus from: Start --> Run --> sqlplusw I get Oracle 9i version

If I run sqlplus from start --> Program --> Oracle 11 --> I can connect correctly.

If I try to create a new ODBC It connect always the Oracle 9i version.

In attach I send you tnsnames.ora and listener.ora from both versions.

Have you any idea How can I create a new ODBC to connect at Oracle 11g?

Thanks in advance!


listener11g.txt
tnsnames11g.txt
tnsnames-9i.txt
0
Comment
Question by:ralph_rea
10 Comments
 
LVL 4

Expert Comment

by:mhenry20
ID: 34897990
I would try copying the tnsnames.ora and sqlnet.ora from the working 11G network/admin directory to the 9i network/admin directory.  That way the alias will be consistent.

Good luck.
0
 

Author Comment

by:ralph_rea
ID: 34898679
I tried to copy tnsnames.ora and sqlnet.ora from the working 11G network/admin directory to the 9i network/admin directory but when I try create a new ODBC with "test connection" I get this error:

Unable to Connect
SQLState=S1000
[Oracle][ODBC][Ora]:ORA-12541: TNS no listener

Have someone any idea??
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34902333
You need to set Oracle_home and Oracle_sid before using them.
please use cmd --> set ORACLE_HOME='full path of other oracle home' ---> set ORACLE_SID=<new> ---> sqlplus / as sysdba

Also make sure listener is running.
use lsnrctl command. Location of listener $ORACLE_HOME/network/admin
0
 

Author Comment

by:ralph_rea
ID: 34905029
virdi_ds,
in my case I have to set Oracle_home and Oracle_sid like this?
set ORACLE_HOME=C:\app\a.l\product\11.2.0\db_1
set ORACLE_SID=ORCL

sqlplus / as sysdba
shutdown
startup

is it right?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34933543
hi

i must agree with @ralph_rea

and Author Yes.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34943693
If by setting this parameter, you are able to connect, then next step is to change environment variables in your window machine.
To do that use the following.
Right click on My Computer, click on Advanced tab, select Environment Variables, and change ORACLE_HOME and PATH variables.
You can also change Registory, which I am not recommending right now.
0
 

Author Comment

by:ralph_rea
ID: 34944132
I tried:
set ORACLE_HOME=C:\app\a.l\product\11.2.0\dbhome_1
set ORACLE_SID=ORCL

but when I try: sqlplus / as sysdba I get this error:
Error 14 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

If I try to start with sqlplus from: Start --> Run --> sqlplusw I get Oracle 9i version with error:
ORA-12541: TNS no listener

In Environment Variables The PATH variables is:
C:\oraclient9\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;C:\app\a.l\product\11.2.0\dbhome_1\BIN;C:\app\a.l\product\11.2.0\dbhome_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Sybase\SQL Anywhere 9\win32;C:\Program Files\Sybase\Shared\win32;C:\Program Files\Sybase\SQL Anywhere 9\drivers;C:\Program Files\Sybase\Shared\Sybase Central 4.2;c:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\PROGRA~1\XRay

What I wrong?
0
 

Author Comment

by:ralph_rea
ID: 34944583
C:\app\a.l\product\11.2.0\dbhome_1\BIN>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\a.l\AppData\Roaming
ASANY9=C:\Program Files\Sybase\SQL Anywhere 9
ASANYSH9=C:\Program Files\Sybase\Shared
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=SEVER103
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\a.l
LOCALAPPDATA=C:\Users\a.l\AppData\Local
LOGONSERVER=\\DOMINIOOP
NUMBER_OF_PROCESSORS=2
OS=Windows_NT
Path=C:\oraclient9\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program F
acle\jre\1.1.8\bin;C:\app\a.l\product\11.2.0\dbhome_1\BIN;C:\app\a.le
duct\11.2.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System
;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Sybase\SQL
e 9\win32;C:\Program Files\Sybase\Shared\win32;C:\Program Files\Sybase\SQ
ere 9\drivers;C:\Program Files\Sybase\Shared\Sybase Central 4.2;c:\Progra
\Microsoft SQL Server\90\Tools\binn\;C:\PROGRA~1\XRay
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 15 Model 6 Stepping 2, GenuineIntel
PROCESSOR_LEVEL=15
PROCESSOR_REVISION=0602
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\A3FA9~1.LEN\AppData\Local\Temp
TMP=C:\Users\A3FA9~1.LEN\AppData\Local\Temp
TNS_ADMIN1=C:\app\a.l\product\11.2.0\dbhome_1\NETWORK\ADMIN
USERDNSDOMAIN=DOMOPENPLAN.LOCAL
USERDOMAIN=DOMOPENPLAN
USERNAME=a.l
USERPROFILE=C:\Users\a.l
WF_RESOURCES=C:\oraclient9\WF\RES\WFus.RES
windir=C:\Windows
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34944681
Confusion:
You installed Oracle Apps 11i, right?
Which database are you using with it? 9i or 8i.

Apart from above, did you install Oracle Database Server 11g as well?
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
ID: 34946649
Whenever you install Apps, DB will be automatically installed.
Now If you want to connect from 9i client, you need to set ORACLE_BASE, ORACLE_HOME and ORACLE_SID pointing to 9i directory.
if you want to set apps 11i, then find ORACLE_HOME from context file.
or login as aplmgr user and use sql plus.
I believe directory will be 11.0.3
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 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

11 Experts available now in Live!

Get 1:1 Help Now