1 2 Previous Next 20 Replies Latest reply: May 10, 2012 12:28 AM by Kgronau-Oracle RSS

    Transparent Gateway for MSSQL

    933354
      Dear All!

      Could you please look at my problem:

      I have transparent gateway installed on MSSQL server so also listener.org is configured there. But I cannot reload lsnrctl service because it returns me an error:

      TNS-12541 No Listener
      TNS-12560 TNS Protocol adapter error

      Does it mean that on the MSSQL server also Oracle server must be installed, not only Transparent Gateway?

      Thanks in advance
        • 1. Re: Transparent Gateway for MSSQL
          NikolayIvankin
          user8793082 wrote:
          Does it mean that on the MSSQL server also Oracle server must be installed, not only Transparent Gateway?
          Gateway must be installed at Oracle side, not MS
          • 2. Re: Transparent Gateway for MSSQL
            Mkirtley-Oracle
            Hi,
            Which version of Gateway for SQL*Server have you installed ? The only supported version now is the 11g version so you should be installing that. You can install that standalone without a database being installed.
            Could you post the listener.ora file and make sure you start the listener as an administrator user. Have a look at the listener logs as they may give more detail
            If you have other listeners installed make sure they are not using the same port as the gateway listener.

            Regards,
            Mike
            • 3. Re: Transparent Gateway for MSSQL
              933354
              Hi!

              Not neceserry, according to "Oracle® Database Gateway Installation and Configuration Guide":

              Gateway Installation Configurations
              You can install Oracle Database Gateway in either of the following configurations:

              On the same computer as an existing Oracle database but in a different Oraclehome.

              On a system with no Oracle database

              Here is also quite good description about how the agent work but there is no clear information if on the Gateway computer also oracle server must be installed:

              http://oracle-apps-dba.blogspot.com/2008/04/oracle-transparent-gateway-for-ms-sql_16.html


              Regards
              • 4. Re: Transparent Gateway for MSSQL
                Mkirtley-Oracle
                Hi,
                Post the listener information so we can check further.

                Regards,
                Mike
                • 5. Re: Transparent Gateway for MSSQL
                  NikolayIvankin
                  user8793082 wrote:
                  Not neceserry, according to "Oracle® Database Gateway Installation and Configuration Guide":
                  Yes, I know, but it's much simplier to handle all oracle's stuff together.
                  • 6. Re: Transparent Gateway for MSSQL
                    Mkirtley-Oracle
                    Nikolay - it could be argued that what you say -

                    Yes, I know, but it's much simplier to handle all oracle's stuff together.

                    - isn't necessarily true. Very often there are good arguments to have the gateways on a different machine or different ORACLE_HOME from other Oracle products.

                    Regards,
                    Mike
                    • 7. Re: Transparent Gateway for MSSQL
                      NikolayIvankin
                      mkirtley wrote:
                      - isn't necessarily true. Very often there are good arguments to have the gateways on a different machine or different ORACLE_HOME from other Oracle products.
                      You are definitely right, but this way is too complex for a beginner.
                      • 8. Re: Transparent Gateway for MSSQL
                        Mkirtley-Oracle
                        Nikolay,
                        Sorry but I disagree with you. That is why we write the notes about installing and configuring Gateways so that customers can install them where it is best for their setup and business.
                        In fact, in some setups trying to install gateways into an existing ORACLE_HOME could actually cause more work and potential problems and it is much easier and better to install the gateway into a new and separate ORACLE_HOME whether on the same or a different machine, especially for beginners. I can say this having worked with Gateways for some time and know the problems that customers find.

                        Regards,
                        Mike
                        • 9. Re: Transparent Gateway for MSSQL
                          933354
                          Hi!

                          Sorry for the delay, I have few days off.

                          Listner.ora looks as follow (transaprent gateway installed on MS SQL server - localhost):


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

                          SID_LIST_LISTENER =
                          (SID_LIST =
                          (SID_DESC =
                          (SID_NAME = SQLSERVER)
                          (ORACLE_HOME = C:\product\11.1.0\tg_2)
                          (PROGRAM=dg4msql)
                          )
                          )


                          I copied this file to both folder: ..\tg_2\NETWORK\ADMIN and ..\tg_2\dg4msql\admin

                          The result of running command "lsnrctl stop" is as follow:

                          Connecting to <DESCRIPTION = <ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)))
                          TNS-12541: TNS:no listener
                          TNS-12560: TNS: protocol adapter error
                          TNS-00511: No listener
                          32bit Window error: 61: Unknown error


                          I have one doubt - my administrator installed previously Oracle gateway for ODBS in the folder C:\product\11.1.0\tg_1 so the command "lsnrctl" can be directed to wrong program. But I also tried to run this command from the folder C:\product\11.1.0\tg_2 directly and the result is the same.

                          The second thing - which windows service is responsible for handling the listener? I tried to check if it is running but I coulnd't find it.

                          Do you have any suggestion for me?
                          Thanks in advance

                          Edited by: user8793082 on 2012-04-27 13:19
                          • 10. Re: Transparent Gateway for MSSQL
                            Mkirtley-Oracle
                            Hi,
                            Thanks for getting back.
                            The output you are seeing is expected if the listener is not actually running when you issue the 'STOP' command.

                            What happens if you issue the 'START' command ?

                            This is what I see on my system when the listener has not been started -

                            C:\Documents and Settings\mkirtley>lsnrctl

                            LSNRCTL for 32-bit Windows: Version 11.2.0.3.0 - Production on 30-APR-2012 08:27
                            :05

                            Copyright (c) 1991, 2011, Oracle. All rights reserved.

                            Welcome to LSNRCTL, type "help" for information.

                            LSNRCTL>
                            LSNRCTL> status
                            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
                            TNS-12541: TNS:no listener
                            TNS-12560: TNS:protocol adapter error
                            TNS-00511: No listener
                            32-bit Windows Error: 2: No such file or directory
                            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mkirtley-uk.uk.oracle.com)(PORT=1530)))
                            TNS-12541: TNS:no listener
                            TNS-12560: TNS:protocol adapter error
                            TNS-00511: No listener
                            32-bit Windows Error: 61: Unknown error
                            LSNRCTL> start
                            Starting tnslsnr: please wait...

                            TNSLSNR for 32-bit Windows: Version 11.2.0.3.0 - Production
                            System parameter file is C:\product\11.2.0\tg_3\network\admin\listener.ora
                            Log messages written to C:\product\11.2.0\tg_3\diag\tnslsnr\mkirtley-uk\listener
                            \alert\log.xml
                            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
                            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mkirtley-uk.uk.oracle.com)(PORT=1530)))

                            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
                            STATUS of the LISTENER
                            ------------------------
                            Alias LISTENER
                            Version TNSLSNR for 32-bit Windows: Version 11.2.0.3.0 - Produ
                            ction
                            Start Date 30-APR-2012 08:27:42
                            Uptime 0 days 0 hr. 0 min. 2 sec
                            Trace Level off
                            Security ON: Local OS Authentication
                            SNMP OFF
                            Listener Parameter File C:\product\11.2.0\tg_3\network\admin\listener.ora
                            Listener Log File C:\product\11.2.0\tg_3\diag\tnslsnr\mkirtley-uk\listen
                            er\alert\log.xml
                            Listening Endpoints Summary...
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mkirtley-uk.uk.oracle.com)(PORT=1530
                            )))
                            Services Summary...
                            Service "hsmsql" has 1 instance(s).
                            Instance "hsmsql", status UNKNOWN, has 1 handler(s) for this service...
                            Service "test_08" has 1 instance(s).
                            Instance "test_08", status UNKNOWN, has 1 handler(s) for this service...
                            The command completed successfully
                            LSNRCTL>


                            Try this and if you still have problems post the errors here and also have a look at the log file, which on your system should be in -

                            C:\product\11.1.0\tg_2\diag\<host>\listener\alert


                            The listener.ora file only needs to be in the directory ..\tg_2\NETWORK\ADMIN

                            Regards,
                            Mike
                            • 11. Re: Transparent Gateway for MSSQL
                              933354
                              Dear Mike!

                              Thanks for your answer. I'm getting back to work and can handle this case so here are my next remarks:

                              When I run:

                              C:\product\11.1.0\tg_2\BIN>LSNRCTL.EXE start

                              it returns me:

                              Starting tnslsnr: please wait...

                              TNS-12560: TNS:protocol adapter error
                              TNS-00530: Protocol adapter error


                              I read in this forum about such error but answer is not really clear, it's quite general error.


                              I have folder "diagnostic" under C:\product\11.1.0\tg_2 but there is no such subfolder, I can find there only folder config\registration\OUI.xml . I searched for file "*alert*" but nothing has been found.


                              Does it mean something has been missed during installation? As I mentioned only "Microsoft Gateway for SQL" has been marked during installation process.

                              Regards,
                              Lukasz
                              • 12. Re: Transparent Gateway for MSSQL
                                Kgronau-Oracle
                                Lukasz,

                                please open a command line window (cmd) and please make sure you do it as ADMINISTRATIVE user, then execute:
                                set ORACLE_HOME=C:\product\11.1.0\tg_2
                                set PATH=C:\product\11.1.0\tg_2\bin;%PATH%
                                set TNS_ADMIN=C:\product\11.1.0\tg_2\network\admin

                                Make sure you have the tnsnames.ora file in the network\admin directory of the gateway installation

                                Now execute in the command line: lsnrctl start
                                Does the listener now start? If not we'll enable listener tracing to see what's missing...
                                • 13. Re: Transparent Gateway for MSSQL
                                  933354
                                  Hi!

                                  I've run all commands you mentioned but unfortunatelly result is the same when I run "start" command (user is an administrator). I didn't make a restart but as far as I know it is not neceserry, I only opened command application again).

                                  My tnsnames.ora file looks as follow (placed in network\admin folder):

                                  Sql_server=
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)
                                  (HOST = localhost)
                                  (PORT = 1521)
                                  )
                                  (CONNECT_DATA = (SID=SQLSERVER)
                                  )
                                  (HS=OK)
                                  )


                                  Could you please advice how to track missing parts?

                                  Thanks and regards,
                                  Lukasz
                                  • 14. Re: Transparent Gateway for MSSQL
                                    Kgronau-Oracle
                                    Redo the settings as stated in the previous past.
                                    Then please post the whole lsnrctl output into the thread?

                                    Also please provide the opatch output: C:\product\11.1.0\tg_2\OPatch\opatch lsinventory -detail
                                    1 2 Previous Next