8 Replies Latest reply: Dec 3, 2012 7:20 AM by 887834 RSS

    Can we have oracle database and gateway for SLQServer on same machine

    887834
      Can we install oracle database and Gateway for sQLServer on same machine>?
      If yes, How does the listener files in gateway home (EG: C:\product\11.2.0\NETWORK\ADMIN) and oracle home (EG: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN) look like?

      Where will we give dg4msql details in tnsnames and listner files?
        • 1. Re: Can we have oracle database and gateway for SLQServer on same machine
          Kgronau-Oracle
          Yes, you can have both products on the same machine. I would recommend you to install both into separate Oracle_Home directories (what you already did with when you use for the Oracle GTW C:\product\11.2.0\ and C:\app\Administrator\product\11.2.0\dbhome_1 for the database) as it allows you to patch components individually.

          When having 2 different OHs for the gateway and database then the best way is to have 2 different listeners, one listener that serves everything in the database home and another one for the gateway products. Again the advantage of maintaining each product separately. It will work also with one listener, but it is commonly leading to difficulties when you upgrade just the gateway to a newer release but keep the database version. So the preferred solution would be to use 2 differnt listeners.

          When you use 2 different listeners, you need to make sure that both listeners use different port numbers, for example use the Oracle listener with the 1521 port and the gateway with 1522. Once you started both listeners, then you need to add to the DATABASE tnsnames.ora file the gateway alias - here make sure that you specify the gateway listener port 1522.
          You might ask why to the home of the database... -> because the gateway is being called by an Oracle database link and so it is the database that needs to know where to find the gateway listener which then requires to have the gateway address information in the tnsnames.ora used by the Oracle database.

          - Klaus
          • 2. Re: Can we have oracle database and gateway for SLQServer on same machine
            887834
            Thanks for the response.

            I have a question here, when we have 2 different homes for oracle and gateway say OH and GOH, how to start listeners in both the homes?

            Here is my listener file in ORACLE HOME: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
            # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
            # Generated by Oracle configuration tools.

            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (GLOBAL_DBNAME = test)
            (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
            (SID_NAME = test)
            )
            )

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

            ADR_BASE_LISTENER = C:\app\Administrator



            Here is my listener file in GATEWAY OME: D:\product\11.2.0\tg_2\NETWORK\ADMIN

            SID_LIST_LISTENER1 =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME=dg4msql)
            (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
            (PROGRAM = D:\product\11.2.0\tg_2\dg4msql)
            )
            )

            LISTENER1 =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.177.223.79)(PORT = 1522))
            )
            )


            ADR_BASE_LISTENER1 = D:\product\11.2.0\tg_2


            Now, I am starting listener from command line.
            C:\Users\Administrator>lsnrctl start listener

            LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 03-DIC-2012 03:29
            :58

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

            Avvio di tnslsnr: attendere...

            TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
            Il file dei parametri di sistema Þ C:\app\Administrator\product\11.2.0\dbhome_1\
            network\admin\listener.ora
            Messaggi di log registrati in C:\app\Administrator\diag\tnslsnr\WIN-77CAQGHJSA2\
            listener\alert\log.xml
            Ascolto su: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.177.223.79)(PORT=1521))
            )

            Connessione a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.177.223.79)(PORT=1521
            )))
            STATO del LISTENER
            ------------------------
            Alias listener
            Versione TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ
            ction
            Data di inizio 03-DIC-2012 03:30:04
            Tempo di attivitÓ 0 giorni 0 ore 0 min. 5 sec.
            Livello trace off
            Sicurezza ON: Local OS Authentication
            SNMP OFF
            File di parametri listenerC:\app\Administrator\product\11.2.0\dbhome_1\network\a
            dmin\listener.ora
            File di log listener C:\app\Administrator\diag\tnslsnr\WIN-77CAQGHJSA2\list
            ener\alert\log.xml
            Summary table degli endpoint di ascolto...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.177.223.79)(PORT=1521)))
            Summary table dei servizi...
            Il servizio "test" ha 1 istanze.
            L'istanza "test", stato UNKNOWN, ha 1 handler per questo servizio...
            Il comando Þ stato eseguito

            C:\Users\Administrator>


            But, if i want to start listener in gateway, how do i start it? I tried below way..

            D: \ product \ 11.2.0 \ tg_2 \ BIN> LSNRCTL.EXE start listener1

            LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 03-DEC-2012 03:31
            : 31

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

            Starting tnslsnr: wait ...

            TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
            The file system parameters Þ C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \
            network \ admin \ listener.ora
            Log messages stored in C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ log \ d
            IAG \ tnslsnr \ WIN-77CAQGHJSA2 \ listener1 \ alert \ log.xml
            TNS-01151: Missing listener name, listener1 in LISTENER.ORA

            Start listener failed. See error messages above ...


            Please let me know how to start listener for dg4msql.
            • 3. Re: Can we have oracle database and gateway for SLQServer on same machine
              Kgronau-Oracle
              you can see, it is using the wrong listener.ora file:
              C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \network \ admin \ listener.ora
              as your gateway listener.ora file is located in D:\product\11.2.0\tg_2\NETWORK\ADMIN.

              In general this could happen when TNS_ADMIN is set in the registry or in the environment. With TNS_ADMIN you force SQL*Net to read all its configuration from a certain location. You can now redefine TNS_ADMIN pointing to the gateway home before starting the gateway listener, or the other option is to just add the listener1 entries also to the listener.ora file located in the DB home.

              When you prefer the first solution, then open a command line window and do:
              set TNS_ADMIN=D:\product\11.2.0\tg_2\NETWORK\ADMIN
              set ORACLE_HOME=D:\product\11.2.0\tg_2\
              cd D:\product\11.2.0\tg_2\bin
              lsnrctl start listener1
              => this should start the gateway listener - if it fails, there might be also a TNS_ADMIn parameter set in the registry and in this case, instead of removing it (due to possible dependencies) add the listener1 entries to the end of the listener.ora file C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \network \ admin \ listener.ora.
              • 4. Re: Can we have oracle database and gateway for SLQServer on same machine
                887834
                1. When i append listener1 details in ORACLE_HOME\listener.ora file, listener is getting started. But, if i am trying to test the gateway connection, It is giving me error

                ERROR at line 1:
                ORA-28545: error diagnosed by Net8 when connecting to an agent
                Unable to retrieve text of NETWORK / NCR message 65535
                ORA-02063: preceding 2 lines from TEST1


                2. When i try setting set TNS_ADMIN=D:\product\11.2.0\tg_2\NETWORK\ADMIN and set ORACLE_HOME=D:\product\11.2.0\tg_2 parameters and start the listener, I am getting error


                D:\product\11.2.0\tg_2\BIN>lsnrctl start listener1

                LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 03-DIC-2012 04:23
                :46

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

                Avvio di tnslsnr: attendere...

                TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
                The file system parameters Þ C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \
                network \ admin \ listener.ora
                Log messages stored in C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ log \ d
                IAG \ tnslsnr \ WIN-77CAQGHJSA2 \ listener1 \ alert \ log.xml
                TNS-01151: Missing listener name, listener1 in LISTENER.ORA
                • 5. Re: Can we have oracle database and gateway for SLQServer on same machine
                  Kgronau-Oracle
                  even with TNS_ADMIn set at the command propmt it uses the listener file from the DB home: The file system parameters Þ C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ network \ admin \ listener.ora which indicates that you have also a TNS_ADMIn set in the registry.

                  When you add the entry to the listener.ora file of the database and start the gateway listener, can you please copy/paste the output?
                  Next please provide the tnsnames.ora entry you've created for the gateway and also the output for a tnsping <gateway alias> - make sure to use the tnsping executable from the DATABASE home.
                  • 6. Re: Can we have oracle database and gateway for SLQServer on same machine
                    887834
                    Here is the output of starting both the listeners. This is output when i appended listener1 settings in Oracle_home listener.ora file.

                    C:\Users\Administrator>lsnrctl start listener

                    LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 03-DIC-2012 04:46
                    :15

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

                    Avvio di tnslsnr: attendere...

                    TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
                    Il file dei parametri di sistema Þ C:\app\Administrator\product\11.2.0\dbhome_1\
                    network\admin\listener.ora
                    Messaggi di log registrati in C:\app\Administrator\diag\tnslsnr\WIN-77CAQGHJSA2\
                    listener\alert\log.xml
                    Ascolto su: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
                    )

                    Connessione a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521
                    )))
                    STATO del LISTENER
                    ------------------------
                    Alias listener
                    Versione TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ
                    ction
                    Data di inizio 03-DIC-2012 04:46:20
                    Tempo di attivitÓ 0 giorni 0 ore 0 min. 5 sec.
                    Livello trace off
                    Sicurezza ON: Local OS Authentication
                    SNMP OFF
                    File di parametri listenerC:\app\Administrator\product\11.2.0\dbhome_1\network\a
                    dmin\listener.ora
                    File di log listener C:\app\Administrator\diag\tnslsnr\WIN-77CAQGHJSA2\list
                    ener\alert\log.xml
                    Summary table degli endpoint di ascolto...
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
                    Summary table dei servizi...
                    Il servizio "test" ha 1 istanze.
                    L'istanza "test", stato UNKNOWN, ha 1 handler per questo servizio...
                    Il comando Þ stato eseguito

                    C:\Users\Administrator>lsnrctl start listener1

                    LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 03-DIC-2012 04:46
                    :23

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

                    Avvio di tnslsnr: attendere...

                    TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
                    Il file dei parametri di sistema Þ C:\app\Administrator\product\11.2.0\dbhome_1\
                    network\admin\listener.ora
                    Messaggi di log registrati in D:\product\11.2.0\tg_2\diag\tnslsnr\WIN-77CAQGHJSA
                    2\listener1\alert\log.xml
                    Ascolto su: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
                    )

                    Connessione a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522
                    )))
                    STATO del LISTENER
                    ------------------------
                    Alias listener1
                    Versione TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ
                    ction
                    Data di inizio 03-DIC-2012 04:46:29
                    Tempo di attivitÓ 0 giorni 0 ore 0 min. 5 sec.
                    Livello trace off
                    Sicurezza ON: Local OS Authentication
                    SNMP OFF
                    File di parametri listenerC:\app\Administrator\product\11.2.0\dbhome_1\network\a
                    dmin\listener.ora
                    File di log listener D:\product\11.2.0\tg_2\diag\tnslsnr\WIN-77CAQGHJSA2\li
                    stener1\alert\log.xml
                    Summary table degli endpoint di ascolto...
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
                    Summary table dei servizi...
                    Il servizio "dg4msql" ha 1 istanze.
                    L'istanza "dg4msql", stato UNKNOWN, ha 1 handler per questo servizio...
                    Il comando Þ stato eseguito

                    C:\Users\Administrator>sqlplus /nolog

                    SQL*Plus: Release 11.2.0.2.0 Production on Lun Dic 3 04:46:34 2012

                    Copyright (c) 1982, 2010, Oracle. All rights reserved.

                    SQL> conn system/Manager1@star
                    Connesso.
                    SQL> create public database link test2 connect to "sa" identified by "pwd"
                    using 'dg4msql';

                    Creato database link.

                    SQL> select count(*) from prefer@test2;
                    select count(*) from prefer@test2
                    *
                    ERRORE alla riga 1:
                    ORA-28545: errore diagnosticato da Net8 durante la connessione a un agente
                    Unable to retrieve text of NETWORK/NCR message 65535
                    ORA-02063: precedente 2 lines da TEST2

                    -----------------------------------------------------------------------------------------------------------------------------------------

                    Output of tnsping from oracle_home

                    C:\Users\Administrator>tnsping dg4msql

                    TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 03-DIC-2
                    012 05:01:28

                    Copyright (c) 1997, 2010, Oracle. All rights reserved.

                    File di parametri utilizzati:
                    C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


                    ╚ stato utilizzato l'adattatore TNSNAMES per risolvere l'alias
                    Tentativo di contattare (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS=OK))
                    OK (0 msec)

                    -----------------------------------------------------------------------------------------------------------------------------------------
                    tnsnames.ora file at D:\product\11.2.0\tg_2\NETWORK\ADMIN

                    dg4msql =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
                    (CONNECT_DATA = (SERVICE_NAME = dg4msql))
                    (HS=OK)
                    )

                    -------------------------------------------------------------------------------------------------------------------------------------------
                    C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN


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

                    dg4msql =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
                    (CONNECT_DATA = (SERVICE_NAME = dg4msql))
                    (HS=OK)
                    )

                    test=
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = test)
                    )
                    )
                    • 7. Re: Can we have oracle database and gateway for SLQServer on same machine
                      Kgronau-Oracle
                      I think I found the issue - have a look again into the listener.ora file You specify for the DG4MSQL gateway: (PROGRAM = D:\product\11.2.0\tg_2\dg4msql), but the executable is in the bin directory, so you should correct it to: (PROGRAM = D:\product\11.2.0\tg_2\bin\dg4msql)

                      After changing the listener.ora, please stop and start the listener1 again and then test it.
                      • 8. Re: Can we have oracle database and gateway for SLQServer on same machine
                        887834
                        Thanks a lot! I got my issue resolved!!

                        Thank you again!