This discussion is archived
10 Replies Latest reply: Oct 15, 2012 12:11 PM by user8990594 RSS

ODBC error in oracle 11g

user8990594 Newbie
Currently Being Moderated
Hi all.
I have installed oracle 11g release 2 64 bit on my system and also installed Informatica powercenter 9.1. the informatica repository is in oracle database. I have configured informatica perfectly since i could run workflow when connecting to flat files etc. i am having problem when i am trying to connect informatica to oracle source system and receiving following error the oracle(tm) client and networking components were not found. I read in many forums posts and realized i should install oracle client. I am encountering issues once the client is installed on my system. the issue is that my repository and integration server stops in informatica powercenter. the error log says TNS could not be resolved. then i tried to log in to database server "sys as sysdba" i am receiving error "ORA-01031: insufficient privileges ". I have done following things to resolve my error
1) checked my user is in ora_dba group
2) checked sqlnet.ora file SQLNET.AUTHENTICATION_SERVICES= (NTS)
I dont know where to start to now fix this error. I have some questions Is there a problem installing both server and client on the same machine.
Your help will be greatly appreciated.
P.s I am not sure whether this to redirected to informatica forums or oracle forums so my apologizes if i am in a wrong place.
Regards
Rajkumar
  • 1. Re: ODBC error in oracle 11g
    gdarling - oracle Expert
    Currently Being Moderated
    Hi,

    The usual cause for 1031 when trying to connect as sysdba is that you dont have sqlnet.authentication_services set to NTS.

    As you've added a 32 bit home to a box with an existing 64 bit home, did you copy the tnsnames and sqlnet from the db home to the client home? The homes are not aware of each other and don't share any config, so you need to configure each one.

    I assume this behavior is not specific to ODBC? Are you able to connect using sqlplus out of the 32 bit home?

    Hope it helps,
    Greg
  • 2. Re: ODBC error in oracle 11g
    user8990594 Newbie
    Currently Being Moderated
    Hi
    Thanks a lot for your help. I have copied the tnsnames ans sqlnet.ora from server to client and now i could sign in to server using sysdba. I have also set sql.authentication_services to NTS. the only problem i have right now is i am getting the oracle(tm) client and networking components were not found when i am trying to create system DSN from informatica powercenter to access data from oracle database. Please help me to resolve this issue.
    Regards
    Rajkumar
  • 3. Re: ODBC error in oracle 11g
    gdarling - oracle Expert
    Currently Being Moderated
    Assuming you have 32 bit client installed, my guess is that maybe you just need to reboot if you haven't since the install. Microsoft ODBC driver relies on your PATH environment variable to find the client dlls, so the service may need to be restarted to get it to pick up the new PATH.

    Greg
  • 4. Re: ODBC error in oracle 11g
    user8990594 Newbie
    Currently Being Moderated
    Hi Greg
    I have restarted the system many times and still the problem persists. I have checked the path in the environment variables and everything seems to be perfect. By the way i am able to connect from client to the server database. please let me know what else could be causing this problem. This problem is eating my brain for past 2 days. Thanks for your help in advance.
    Regards
    Rajkumar
  • 5. Re: ODBC error in oracle 11g
    user8990594 Newbie
    Currently Being Moderated
    Hi Greg
    I am planning to do a fresh installation of oracle database and Informatica power center. Both oracle and informatica are 64 bit. i would like to know which oracle client component should be installed. If you could please provide me the link for the oracle client.
    Regards
    Rajkumar
  • 6. Re: ODBC error in oracle 11g
    user8990594 Newbie
    Currently Being Moderated
    Hi Greg
    I am planning to do a fresh installation of oracle database and Informatica power center. Both oracle and informatica are 64 bit. i would like to know which oracle client component should be installed. If you could please provide me the link for the oracle client.
    Regards
    Rajkumar
  • 7. Re: ODBC error in oracle 11g
    user8990594 Newbie
    Currently Being Moderated
    Hi Greg
    I am planning to do a fresh installation of oracle database and Informatica power center. Both oracle and informatica are 64 bit. i would like to know which oracle client component should be installed. If you could please provide me the link for the oracle client.
    Regards
    Rajkumar
  • 8. Re: ODBC error in oracle 11g
    user8990594 Newbie
    Currently Being Moderated
    Hi Greg
    I am planning to do a fresh installation of oracle database and Informatica power center. Both oracle and informatica are 64 bit. i would like to know which oracle client component should be installed. If you could please provide me the link for the oracle client.
    Regards
    Rajkumar
  • 9. Re: ODBC error in oracle 11g
    gdarling - oracle Expert
    Currently Being Moderated
    Hi Rajkumar,

    I suspect you're actually using 32 bit components, as I've only ever seen the error message you've posted "oracle(tm) client and networking components were not found" come from Microsoft ODBC or OLEDB provider for Oracle, and those only come in 32 bit. The fact that they got loaded in the first place and didnt find the client indicates the process is 32 bit.
    You'll first need to determine for sure whether the process that needs connectivity is 32 bit or 64 bit, then install that bit-ness of Oracle client. You can also install both a 32 bit and a 64 bit client, and in some cases you need both. If you're running it ON the database machine and already have a 64 bit database, that already includes a 64 bit client.
    In either case, you can probably just go with the 11201 client.. Here's the link, click "see all" to get to the client download:
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

    Hope it helps,
    Greg

    Edited by: gdarling on Oct 15, 2012 7:40 AM
  • 10. Re: ODBC error in oracle 11g
    user8990594 Newbie
    Currently Being Moderated
    Thanks a lot for your help . i have atlast succesfully configured oracle client and informatica. i have downloaded the 32 bit client and installed it and now everything works perfectly. thanks a lot...
    Regards
    Rajkumar

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points