12 Replies Latest reply on Mar 3, 2019 10:35 AM by Santhosh T

    Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'

    Santhosh T

      Hi Experts,

       

      I am trying to make a connection from Oracle 11g to Sql Server 2014. I have followed the steps described in the below link but getting the service status as 'unknown' in lsnrctl status.

       

      Heterogeneous Database connections - Oracle to SQL Server

       

       

      Could you please give me some help to fix this. I am with Oracle 11g (11.2.0.2)

       

      Thanks

        • 2. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
          EdStevens

          Santhosh T wrote:

           

          Hi Experts,

           

          I am trying to make a connection from Oracle 11g to Sql Server 2014. I have followed the steps described in the below link but getting the service status as 'unknown' in lsnrctl status.

           

          Heterogeneous Database connections - Oracle to SQL Server

           

           

          Could you please give me some help to fix this. I am with Oracle 11g (11.2.0.2)

           

          Thanks

          There is nothing to fix.  This is quite normal.

          Think of the listener like a desk clerk at a hotel.  He starts his shift (lsnrctl start) and is given a list of guests who might be expecting calls (read the SID_LIST section of listener.ora).  Now, he has no way of knowing if those guests are in the building or not.  If someone asks to speak to one of those guests, he will try to connection them, but until then, the status of that guest is UNKNOWN.

           

          Now, suppose a guest (FRED) drops by the front desk every few minutes just to let the clerk know he (FRED) is in  (alter system register). Now the desk clerk will know that FRED is "READY" to receive guests.

           

          --- Edit

          And, since you posted in HETEROGENEOUS SERVICES and mentioned MSSQL, you should know that MSSQL has no mechanism for registering with the oracle listener, so any entries for it will necessarily be UNKNOWN.

          • 3. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
            Gaz in Oz

            ...so what is the error you get when you use the db link you created in Oracle to try and do something with the mssql db?

            (presumably do a select, insert, update or delete).

            • 4. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
              Santhosh T

              Thanks both for your inputs. I am receiving the below error while testing the DB link, how / where to check and fix this one.

               

               

              Thanks

              • 5. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                Gaz in Oz

                That screenshot, You are using the "CREATE DATABASE LINK" directive, then it looks like you are trying to run a SQL script.

                What ever guide you are following, if it tells you to do that, then the guide is very wrong indeed.

                 

                You create a database link to the remote database and put the tnsnames entry in the USING part. You can then execute queries on objects in the remote database... and don't use double-quotes where they are not needed.

                 

                For example, create a database link:

                CREATE DATABASE LINK  mssql_db_link

                CONNECT TO "remote_user" IDENTIFIED BY "remote_user_password"

                USING '<the HS = OK tnsnames entry for the remote db>';

                Using the newly created db link in a select statement to select from the remote mssql db:

                SELECT *

                FROM  "RemoteTable"@mssql_db_link;

                Of course, you can use the db_link where ever a db link is allowed, including in SQL scripts.

                • 6. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                  Santhosh T

                  I have created using the HS OK entry but still its throwing the same error. ORA 28545.

                   

                  • 7. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                    Gaz in Oz

                    Stop copy/pasting screenshots. Copy/paste the actual text.

                    The screenshot of a sql script you posted is a broken attempt at creating a db link. It is not the way to use an already created db link.

                    There is a distinct difference on creating a db link and using it.

                    I have created using the HS OK entry but still its throwing the same error. ORA 28545.

                    That screenshot shows  a working tns alias lookup that tnsping can use the host = and port = to verify there is a listener running.

                    That is as far as tnsping goes. tnsping does NOT test connectivity to the remote database.

                     

                    Apply that to what ever you are trying to do as well as read actual Oracle documentation as top.gun posted and:

                    https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm

                    to get the proper syntax to successfully CREATE a db link that you can the test by "selecting something from somewhere@db_link"

                    Stop reading badly written random websites purporting to be a knowledgeable source.

                    • 8. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                      EdStevens

                      Santhosh T wrote:

                       

                      I have created using the HS OK entry but still its throwing the same error. ORA 28545.

                       

                      Error?  I don't see any error.  All I see is a successful tnsping.

                      • 9. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                        Santhosh T

                        Thank you very much to you both for your reply and assistance.

                         

                        I have followed the below Steps to make the connection between Oracle 11.2.0.2 to SQL Server 2014 (64Bit).

                        The same steps worked for another connection but the difference is, that is SQL Server 2008 (64bit).

                         

                        Apologies for any inconvenience caused, I am little confused with these things.

                         

                        1) Created the ODBC connection(system DSN - ODBC Driver 13 for SQL Server ) of 32 and 64 bit with the same name of the SQL Server (PSPRDFSCM).

                         

                        2) Added the entry in the tnsnames.ora file like below

                         

                        # tnsnames.ora Network Configuration File: D:\app\famis\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

                        # Generated by Oracle configuration tools.

                         

                        FAMISTST =

                          (DESCRIPTION =

                        (ADDRESS = (PROTOCOL = TCP)(HOST = SRPFMSDBSFT.***.edu)(PORT = 1521))

                        (CONNECT_DATA =

                          (SERVER = DEDICATED)

                          (SERVICE_NAME = FAMISTST.***.edu)

                        )

                          )

                         

                        #OLD one  

                        PRDFSCM =

                          (DESCRIPTION =

                        (ADDRESS = (PROTOCOL = TCP)(HOST = srpfmsdbsft.***.edu)(PORT = 1521))

                        (CONNECT_DATA =

                          (SID = prdfscm)

                        )

                        (HS = OK)

                        #Newly added one

                        PSPRDFSCM =

                          (DESCRIPTION =

                        (ADDRESS = (PROTOCOL = TCP)(HOST = srpfmsdbsft.***.edu)(PORT = 1521))

                        (CONNECT_DATA =

                          (SID = psprdfscm)

                        )

                        (HS = OK)

                          )

                         

                        3) Added the entry in the Listener.ora file

                         

                        # listener.ora Network Configuration File: D:\app\famis\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\famis\product\11.2.0\dbhome_1)

                          (PROGRAM = extproc)

                          (ENVS = "EXTPROC_DLLS=ONLY:D:\app\famis\product\11.2.0\dbhome_1\bin\oraclr11.dll")

                        )

                        (SID_DESC =

                          (SID_NAME = prdfscm)

                          (ORACLE_HOME = D:\app\famis\product\11.2.0\dbhome_1)

                          (PROGRAM = dg4odbc)

                        )

                        (SID_DESC =

                          (SID_NAME = psprdfscm)

                          (ORACLE_HOME = D:\app\famis\product\11.2.0\dbhome_1)

                          (PROGRAM = dg4odbc)

                        )

                        (SID_DESC =

                          (SID_NAME = BMS)

                          (ORACLE_HOME = D:\app\famis\product\11.2.0\dbhome_1)

                          (PROGRAM = dg4odbc)

                        )

                          )

                         

                        LISTENER =

                          (DESCRIPTION_LIST =

                        (DESCRIPTION =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = SRPFMSDBSFT.***.edu)(PORT = 1521))

                        )

                          )

                         

                        ADR_BASE_LISTENER = D:\app\famis\product\11.2.0

                         

                        4) Created the file with name as "initpsprdfscm.ora" in the HS folder as below (D:\app\famis\product\11.2.0\dbhome_1\hs\admin)

                         

                        # This is a sample agent init file that contains the HS parameters that are

                        # needed for the Database Gateway for ODBC

                         

                         

                        #

                        # HS init parameters

                        #

                        HS_FDS_CONNECT_INFO = psprdfscm

                        HS_FDS_TRACE_LEVEL = off

                        HS_FDS_TRACE_LEVEL = 5

                         

                         

                        #

                        # Environment variables required for the non-Oracle system

                        #

                        #set <envvar>=<value>

                         

                        5) Created the DATABASE Link as below.

                         

                        drop database link "PSPRDFSCM.***.edu";

                         

                        create database link "PSPRDFSCM.***.edu"

                        connect to "famisprd.sql"

                        identified by "*******"

                        using 'PSPRDFSCM';

                        6) Trying to query the table name using the DB link.

                         

                        SQL> select loader_req_id from "dbo"."PS_PO_REQLOAD_RQST"@PSPRDFSCM

                         

                         

                        This query is giving me the error.

                         

                         

                        ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 65535

                         

                        Thanks

                        • 10. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                          Gaz in Oz

                          The ODBC connections you set up, did you test that connectivity in odbcad32.exe and was that/they successful?

                          drop database link "PSPRDFSCM.***.edu";

                           

                          create database link "PSPRDFSCM.***.edu"

                          connect to "famisprd.sql"

                          identified by "*******"

                          using 'PSPRDFSCM';

                          So just to be clear, the username of the remote database is famisprd.sql?

                          Something to try is to drop the double-quotes round the database link name and to not have the .***.edu either. For example:

                          drop database link "PSPRDFSCM.***.edu";

                           

                          create database link PSPRDFSCM

                          connect to "famisprd.sql"

                          identified by "*******"

                          using 'PSPRDFSCM';

                          and see how you go.

                          • 11. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                            EdStevens

                            'famisprd.sql' is a very unusual username, to say the least.

                             

                            You might want to review this article I wrote, showing all the steps involved in configuring an HS connection:

                             

                            https://edstevensdba.wordpress.com/2018/09/19/the-oracle-transparent-gateway/

                            • 12. Re: Oracle to SQL Server 2014 Connection - LSNRCTL Service Status 'unknown'
                              Santhosh T

                              Thanks a ton Dear Gaz,

                               

                              name and to not have the .***.edu either. For example:

                              This worked. I have removed the domain name from the database link and created.

                               

                              But wondering, when i checked it in the Toad under DB Links tab, still the link name was PSPRDFSCM.***.edu

                               

                              Anyways, thanks again for your help in this regard.

                               

                              Thanks