10 Replies Latest reply: Oct 15, 2012 2:11 PM by user8990594 RSS

    ODBC error in oracle 11g

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