| Author |
Message
|
| sriram.e |
 Posted: Thu Dec 24, 2009 12:35 pm Post subject: Oracle DB connectivity issue |
 |
|
Novice
Joined: 14 Nov 2006
Posts: 12
|
Hi All,
We are using WMB 6.1.0.3 on AIX box to connect to Oracle DB.There is no Oracle client nor tnsname is configured. All we did is update ODBC64.ini with new orcale configuration,ran mqsisetdbparm and recycled.
We created simple msg flow in 64bit EG just to check oracle connectivity and getting the following error,
MQInput --> Compute --> MQOutput
[IBM][ODBC Oracle Wire Protocol driver][Oracle]TNS-12505: TNS:listener could not resolve SID given in connect descriptor'
Here is the odbc config,
[ORACLEDB]
Driver=/usr/opt/wmqi/ODBC64/V5.3/lib/UKora23.so
Description=DataDirect 5.3 64bit Oracle Wire Protocol
HostName=abcav.sys.xxxxx.com
PortNumber=1600
SID=DEVXXX.ABC.COM
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
Any help will be appreciated! |
|
| Back to top |
|
 |
| sriram.e |
| Posted: Mon Dec 28, 2009 10:57 am Post subject: |
|
|
Novice
Joined: 14 Nov 2006
Posts: 12
|
|
| Back to top |
|
|
| rekarm01 |
| Posted: Mon Dec 28, 2009 3:33 pm Post subject: Re: Oracle DB connectivity issue |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
|
| Back to top |
|
|
| upcominggeek |
| Posted: Mon Feb 15, 2010 10:55 pm Post subject: |
|
|
Apprentice
Joined: 21 Aug 2006
Posts: 35
Location: Boston, US
|
Well, only if I had more information!
I am trying to connect to a remote Oracle DB which can be connected via sqlplus64 userid/password@<DB Alias> from MB machine.
Customer has given me the tnsnames.ora for this alias and I am trying to connect to the SERVICE_NAME provided in the tnsnames.ora file.
Getting the same error! Error is BIP2322E: Database error: SQL State '08001'; Native Error Code '-1'; Error Text '[IBM][ODBC Oracle Wire Protocol driver][Oracle]TNS-12505: TNS:listener could not resolve SID given in connect descriptor'
Oracle is not installed in the MB machine, but this is just a user database.
I have the correct odbc64.ini file with the information from the tnsnames.ora, I have also setup the ODBCINI varaible, the MQSI_LIBPATH64 variable.
Unfortunately for me and my client, troubleshooting from the Oracle side is a tough job since it is handled by a separate team. But does this error mean for sure that it is because of an issue with the Oracle TNS Listener? I would rather be 100% sure before putting the ball on Oracle team's side.
Broker version is 6.1.0.2, running on Red Hat Linux 64
Oracle is also on 64 bit. I was not able to run lsnrctl as I could not find this script in my machine.
Also wanted to check whether we need to set the ORACLE_HOME variable in this case if Oracle being accessed is just a user database. I could not find for sure from the documentation when exactly this variable needs to be implemented.
Any pointers appreciated.
Thanks,
UpcomingGeek |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Feb 16, 2010 12:22 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
It used to be the case that you had to install the ORacle Client software on the machine running Broker if you were using a remote oracle DB.
Has this been done?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| upcominggeek |
| Posted: Tue Feb 16, 2010 6:32 am Post subject: |
|
|
Apprentice
Joined: 21 Aug 2006
Posts: 35
Location: Boston, US
|
I am rechecking the Oracle client installation location with my client now. But yes, it is installed. There is already a PERL program running in the same machine that access this database by using Oracle DB Alias, Userid, Pwd. I can connect to the Oracle DB from this box by using sqlplus64. I can also see the folder /usr/lib/oracle/10.2.0.4/client64 which has a bin and lib directory although I cannot find the oraenv file under bin. (http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ah14500_.htm)
And I have set Oracle Home to point to this location as well and added Ora Home /lib and bin to LD_LIBRARY_PATH
Thanks,
Emil |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 16, 2010 6:58 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Recheck that the Oracle home information is configured in the environment broker runs under.... i.e. that it is set as part of the .profile or through an extension script run through mqsiprofile. |
|
| Back to top |
|
|
| upcominggeek |
| Posted: Tue Feb 16, 2010 8:31 am Post subject: |
|
|
Apprentice
Joined: 21 Aug 2006
Posts: 35
Location: Boston, US
|
Yes, the ORACLE_HOME is getting loaded and all the other variables like LD_LIBRARY PATH and LIBPATH64 are having oracle home/bin and lib in it.
I found out from my client that their SID was different from the one I was trying earlier, but even the one that he finally gave me(which he got from Oracle DBA) is giving me the same error.
On top of all, the client doesnt even seem to have an IBM Contract number to open a PMR!!
Is it necessary to load $ORACLE_HOME/bin/oraenv in the beginning? I cannot even find this file under this location |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Feb 16, 2010 9:03 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| upcominggeek wrote: |
| On top of all, the client doesnt even seem to have an IBM Contract number to open a PMR!! |
The client should contact the IBM sales area from which they obtained their license to get this detail.
The client did of course contact IBM sales to obtain a license I'm sure... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| upcominggeek |
| Posted: Tue Feb 16, 2010 11:09 am Post subject: |
|
|
Apprentice
Joined: 21 Aug 2006
Posts: 35
Location: Boston, US
|
So I was finally able to get the IBM customer id. Have opened a PMR regarding the same.
Just wondering (fully aware that I am asking an Oracle question here), is it possible for a remote machine to identify the Oracle server by Service Name, ip and port; but not by SID, ip and port? Possibly due to SID not being exposed to outside world? Oracle client on MB server do not even have tnsping, so I am kind of crippled to test out if this SID is indeed available on this machine. |
|
| Back to top |
|
|
| upcominggeek |
| Posted: Wed Feb 17, 2010 9:41 pm Post subject: |
|
|
Apprentice
Joined: 21 Aug 2006
Posts: 35
Location: Boston, US
|
Finally, found a workaround. I tried to provide ServiceName parameter in the odbc64.ini instead of SID as suggested. And it worked!! ServiceName is listed as a parameter for OracleRACDB, but not for Oracle. But seems like both works for Oracle.
Now, that is the good news. The bad news is that I am not able to get my Stored Proc to work. For some reason, MB wants to always put 3 qualifiers to the Stored Proc. <Schema>.<Pkg>.<Proc> while my procedure is a standalong procedure. I keep getting the error
BIP2920E: When attempting to obtain a procedure definition, the database reported that the procedure 'a.b.c' does not exist, or cannot be accessed.
I tried declaring the procedure as "schema.stored proc" and broker put DSN in front.
I tried declaring the procedure as "stored proc" alone and in the call statement adding the IN Database.schema and then the broker put the Userid/DSN (both have been given as same in my case although I was getting similar errors when I gave them differently) as the package name.
I tried declaring the procedure as "%.stored proc" and in the call statement adding the EXTERNAL SCHEMA 'schema'. Again, MB added DSN to the front.
Am I missing something here? Am I being misled by the error?
I can execute the stored proc directly from oracle client using the same id/password. So I believe this shouldn't be an access issue. |
|
| Back to top |
|
|
| upcominggeek |
| Posted: Thu Feb 18, 2010 5:56 am Post subject: |
|
|
Apprentice
Joined: 21 Aug 2006
Posts: 35
Location: Boston, US
|
As mentioned in another post, am wondering if the issue is because of underscores in the procedure name!
I checked the ODBC trace and it has the underscores escaped though. |
|
| Back to top |
|
|
| upcominggeek |
| Posted: Thu Feb 18, 2010 6:07 am Post subject: |
|
|
Apprentice
Joined: 21 Aug 2006
Posts: 35
Location: Boston, US
|
Hmm,
From ODBC trace, it seems like MB is making the correct col without any package name. But after preparing the call and when it finally executes it, it is failing with the reason given below (from odbc trace). Seems like MB is not able to find the procedure!!
ppid=29295:pid= 72b3:4088a940 EXIT SQLFetchScroll with return code 100 (SQL_NO_DATA_FOUND)
SQLHSTMT 0x0c112b10
SQLSMALLINT 1 <SQL_FETCH_NEXT>
SQLINTEGER 0 |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Feb 18, 2010 9:33 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| upcominggeek wrote: |
Hmm,
From ODBC trace, it seems like MB is making the correct col without any package name. But after preparing the call and when it finally executes it, it is failing with the reason given below (from odbc trace). Seems like MB is not able to find the procedure!!
ppid=29295:pid= 72b3:4088a940 EXIT SQLFetchScroll with return code 100 (SQL_NO_DATA_FOUND)
SQLHSTMT 0x0c112b10
SQLSMALLINT 1 <SQL_FETCH_NEXT>
SQLINTEGER 0 |
From SQL code 100 it looks more that what ever the procedure was doing was supposed to return something. However the data was not found.
How does your procedure handle this scenario?
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
