locked
Connection Errors RRS feed

  • Question

  • Hi Folks,
     I am trying To Connect Oracle DB In SSIS i am getting The Following Error:

    "Test connection failed because of an error in initialising provider.ORA-12154:TNS:no listener"

    Thanks & regards
    SIva.Irigineni
    Monday, January 28, 2013 11:40 AM

Answers

  • >>When i ran both the commands in cmdprompt.. both are failed 

    I hope you changed servername with actual servername while running the command. and mydb is the tns name for the oracle database.

    since both failed , I assume client is not able to find the oracle server. 

    Do you have firewall on ? is your oracle server on and are you able to login to the database?

     

    Regards
    Satheesh

    • Marked as answer by Eileen Zhao Monday, February 4, 2013 6:02 AM
    Tuesday, January 29, 2013 3:07 PM

All replies

  • is your tnsnames.ora is set correctly?

    use 

    TELNET SeverName portnumber 

    to check if the client can access oracle server



    Regards
    Satheesh

    Monday, January 28, 2013 12:34 PM
  • Hi Satheesh,

                      If u don't mind where should i check that "tnsnames.ora "

    Thanks For The Reply...

    Regards

    Siva

    Monday, January 28, 2013 1:06 PM
  • Mine is at C:\oracle\instantclient_10_2, but it depends on what you have installed.
    You can search for tnsnames.ora in the oracle folder and see what pops up.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Monday, January 28, 2013 1:12 PM
  • Hi Koen,

        I diidn't get this Folder (C:\oracle\instantclient_10_2,)..

    I am Using Oarcle11g Trail Version..

    Thanks For Ur suggestion..

    Regards

    Siva

    Monday, January 28, 2013 1:42 PM
  • Did you do a search on tnsnames.roa like I suggested?

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Monday, January 28, 2013 1:44 PM
  • It should be inside  %ORACLE HOME%\NETWORK\ADMIN directory.

    http://www.orafaq.com/wiki/Tnsnames.ora


    Regards
    Satheesh

    Monday, January 28, 2013 2:53 PM
  • Hai,

    Thanks For Reply...If I run command: lsnrctl start orcl
    The result is: Missing Listener Name,orcl, in LISTENER.ORA

    Where Should i add listener Name In That ORA File

    thanks&regards

    siva

    Tuesday, January 29, 2013 9:32 AM
  • Hi,

    First of all, The client machine from which you are trying to connect to oracle server, you have to setup the tnsnames.ora file

    This is the connection information the client will use to connect to the oracle server. Is that setup correctly?

    and where did you run the command

     lsnrctl start orcl

    These are commands used by a DBA in order to start and stop oracle listers


    Regards
    Satheesh

    Tuesday, January 29, 2013 9:41 AM
  • Let's start with the basics: did you install the oracle client and the oracle odac on your computer?

    http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, January 29, 2013 1:10 PM
  • Hai Satheesh,

    I had install(Oracle) in my local machine .... and i will run this command in "LSNRCTLL> start orcl"

    in Command Prompt. i started all the oracle related services also...

    tnsnames.ora, listener.ora both the files are exixts

    regards

    Siva

    Tuesday, January 29, 2013 1:36 PM
  • tnsnames.ora, listener.ora both the files are exixts

    The file doesn't just need to exist, it also needs to contain the exact connection information.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, January 29, 2013 1:38 PM
  • Hai koen,

    it contains exact information alsooo...

    # tnsnames.ora Network Configuration File: D:\app\SivaI\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    LISTENER_ORCL =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl.10.255.164)
    LISTENER_ORCL =
     (DESCRIPTION = 
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.119)(PORT = 1521))
       )
     (CONNECT_DATA =
       (SERVICE_NAME = LISTENER_ORCL)
         )
      )
    # listener.ora Network Configuration File: D:\app\SivaI\product\11.2.0\dbhome_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = D:\app\SivaI\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:D:\app\SivaI\product\11.2.0\dbhome_1\bin\oraclr11.dll")
        )
      )

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.255.158)(PORT = 1521))
        )
      )

    ADR_BASE_LISTENER = D:\app\SivaI

    Tuesday, January 29, 2013 2:13 PM
  • I think you've come to the point where you better ask your question in an Oracle forum :)

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, January 29, 2013 2:15 PM
  • Is that file in your path? And are you using the 32 bit client if you are running in BIDS?

    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, January 29, 2013 2:15 PM
  • Okay the files exists.

    can you check couple of things?

    try

    telnet servername 1521

    ---Is should not give any errors

    and did you try a tnsping?

    tnsping mydb

    should succedd if it is able to locate the server


    Regards
    Satheesh

    Tuesday, January 29, 2013 2:32 PM
  • Yes.. i am using windows xp sp3 32bit...

    regards
    siva
    Tuesday, January 29, 2013 2:36 PM
  • When i ran both the commands in cmdprompt.. both are failed 
    Tuesday, January 29, 2013 2:42 PM
  • >>When i ran both the commands in cmdprompt.. both are failed 

    I hope you changed servername with actual servername while running the command. and mydb is the tns name for the oracle database.

    since both failed , I assume client is not able to find the oracle server. 

    Do you have firewall on ? is your oracle server on and are you able to login to the database?

     

    Regards
    Satheesh

    • Marked as answer by Eileen Zhao Monday, February 4, 2013 6:02 AM
    Tuesday, January 29, 2013 3:07 PM