This discussion is archived
8 Replies Latest reply: Dec 3, 2012 5:20 AM by 887834 RSS

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

887834 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot! I got my issue resolved!!

    Thank you again!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points