This discussion is archived
7 Replies Latest reply: Sep 24, 2013 6:11 AM by kgronau RSS

Oracle Database Gateway for MS SQL server

d61ac4fe-4517-4052-ba77-c0ada20eb938 Newbie
Currently Being Moderated

Hi,

 

we have Oracle Database Appliance.

OS: Oracle Linux 5 x64

DB: 11.2.0.3 enterprise (Oracle Database Gateway for MS SQL server at the same version)

 

Communication with remote DB is OK, but CPU load is too high without any reason. CPU waste, useless HW load, energy waste.

 

Enterprise manager console:

http://81.89.49.222/em.JPG

OS top:

http://81.89.49.222/top.JPG

 

Please can you help me how to solve this issue? How can I restart these procesess? Any ideas?

 

Thank you

 

Michal

  • 1. Re: Oracle Database Gateway for MS SQL server
    kgronau Guru
    Currently Being Moderated

    Could you please make sure gateway tracing is disabled (HS_FDS_TRACE_LEVEL=off) and then please let me know which process is consuming high CPU - is it the database, the gateway etc.

  • 2. Re: Oracle Database Gateway for MS SQL server
    d61ac4fe-4517-4052-ba77-c0ada20eb938 Newbie
    Currently Being Moderated

    Hi,

     

    we are using connection to multiple MS SQL servers:

     

    [oracle@odamaind1 ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/dg4msql/admin/initaqpdb01.ora

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

    # that are needed for the Database Gateway for Microsoft SQL Server

     

     

    #

    # HS init parameters

    #

    HS_FDS_CONNECT_INFO=[172.18.250.141]://master

    # alternate connect format is hostname/serverinstance/databasename

    HS_FDS_TRACE_LEVEL=OFF

    HS_FDS_RECOVERY_ACCOUNT=RECOVER

    HS_FDS_RECOVERY_PWD=RECOVER

    HS_FDS_SUPPORT_STATISTICS=FALSE

    HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

    HS_NLS_LENGTH_SEMANTICS=CHAR

    HS_FDS_TRANSACTION_MODEL=READ_ONLY

    HS_FDS_TRANSACTION_ISOLATION=UNCOMMITTED

     

    [oracle@odamaind1 ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/dg4msql/admin/initcosima.ora

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

    # that are needed for the Database Gateway for Microsoft SQL Server

     

     

    #

    # HS init parameters

    #

    HS_FDS_CONNECT_INFO=[172.20.250.99]//COSIMA

    HS_FDS_TRACE_LEVEL=OFF

    HS_FDS_RECOVERY_ACCOUNT=RECOVER

    HS_FDS_RECOVERY_PWD=RECOVER

    # custom init parameters

    HS_FDS_SUPPORT_STATISTICS=FALSE

    HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

    HS_NLS_LENGTH_SEMANTICS=CHAR

    HS_FDS_TRANSACTION_MODEL=READ_ONLY

    HS_FDS_TRANSACTION_ISOLATION=UNCOMMITTED

     

    [oracle@odamaind1 ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/dg4msql/admin/initepos.ora

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

    # that are needed for the Database Gateway for Microsoft SQL Server

     

     

    #

    # HS init parameters

    #

    HS_FDS_CONNECT_INFO=[172.18.250.41]:1433//epos

    # alternate connect format is hostname/serverinstance/databasename

    HS_FDS_TRACE_LEVEL=OFF

    HS_FDS_RECOVERY_ACCOUNT=RECOVER

    HS_FDS_RECOVERY_PWD=RECOVER

    HS_FDS_SUPPORT_STATISTICS=FALSE

    HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

    HS_NLS_LENGTH_SEMANTICS=CHAR

    HS_FDS_TRANSACTION_MODEL=READ_ONLY

    HS_FDS_TRANSACTION_ISOLATION=UNCOMMITTED

     

    [oracle@odamaind1 ~]$ cat /u01/app/oracle/product/11.2.0.3/dbhome_1/dg4msql/admin/initepospasohlavky.ora

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

    # that are needed for the Database Gateway for Microsoft SQL Server

     

     

    #

    # HS init parameters

    #

    HS_FDS_CONNECT_INFO=[172.31.250.41]/SQL2012/epos

    # alternate connect format is hostname/serverinstance/databasename

    HS_FDS_TRACE_LEVEL=OFF

    HS_FDS_RECOVERY_ACCOUNT=RECOVER

    HS_FDS_RECOVERY_PWD=RECOVER

    HS_FDS_SUPPORT_STATISTICS=FALSE

    HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

    HS_NLS_LENGTH_SEMANTICS=CHAR

    HS_FDS_TRANSACTION_MODEL=READ_ONLY

    HS_FDS_TRANSACTION_ISOLATION=UNCOMMITTED

     

    dg4msl process, look at posted attachment (print screen of enterprise manager and top from OS).

     

    Thank you

     

    Michal

  • 3. Re: Oracle Database Gateway for MS SQL server
    kgronau Guru
    Currently Being Moderated

    Did you monitor how long those processes keep consuming this amount of CPU and did you check with the foreign database if the gateway session is still active?

  • 4. Re: Oracle Database Gateway for MS SQL server
    d61ac4fe-4517-4052-ba77-c0ada20eb938 Newbie
    Currently Being Moderated

    I don't know exactly how long exists this state, it's more than one month. I don't have an access to foreign databases, those are databases of our customer. I have created SR at oracle, they told me to trace those processes:

     

    [root@odamaind1 ~]# cat dg4odbc1.log

    13452 futex(0x16e5330, FUTEX_WAIT_PRIVATE, 2, NULL <unfinished ...>

    [root@odamaind1 ~]# cat dg4odbc2.log

    18792 futex(0x231d694, FUTEX_WAIT_PRIVATE, 19, NULL <unfinished ...>

    [root@odamaind1 ~]# cat dg4odbc3.log

    19051 futex(0xab3694, FUTEX_WAIT_PRIVATE, 13, NULL <unfinished ...>


    If it can help or it is necessary, I can try to ask customer to access foreign database.

  • 5. Re: Oracle Database Gateway for MS SQL server
    kgronau Guru
    Currently Being Moderated

    That would match the output from top. When you look at the TOP output it shows the dg4msql processes hanging around for thousands of hours. I don't think there are still any processes in the SQL Server database related to these 3 gateway processes. In addition all Oracle processes are up for only 300 hours - so for me everything looks like a hanging and spinning gateway process.

    Normally gateway processes should end when the session where the gateway process was started ends or if the application manually closes the database link. Here something went wrong and the gateway still tries to recover and waits for some feedback.

    I would manually kill that process but before killing it make sure that the Oracle_Home where the gateway is installed contains an rdbms/log directory. It exists when you installed the gateway into the database home, but stand alone gateway installations are commonly missing it. So just in case $OH/rdbms/log is missing, then create it and afterwards kill the gateway process. You probably get a gateway core dump in that rdbms/log directory which you can analyze in detail.

  • 6. Re: Oracle Database Gateway for MS SQL server
    d61ac4fe-4517-4052-ba77-c0ada20eb938 Newbie
    Currently Being Moderated

    I have got directory $OH/rdbms/log with correct permission. I killed those processes but no logfile was created. I think you are right that there were only old dg4msql processes which were not correctly finished for some reason. Thank you for your help.

     

    Michal

  • 7. Re: Oracle Database Gateway for MS SQL server
    kgronau Guru
    Currently Being Moderated

    From the top output and the amount of time how long the gateway process was active compared to the Oracle database uptime it looks like the process was just trying to contact the SQL Server and the Oracle process - but both were already gone.

    What you can do to avoid this kind of issue is you could set a large gateway timeout where the gateway executable will be removed from memory when the process is idle for a certain amount of time.

    Check out the gateway parameter HS_IDLE_TIME (the timeout is specified in minutes an 0 - which is the default- means it does not expire at all).

    The disadvantage is when your application isn't coded correctly and does not close the database links and tries to reuse opened database links after an idle time. As long as the idle timeout didn't remove that idle process you can reuse it normally and the idle timer starts again from scratch. But when the process was removed from the memory the Oracle database won't get notified, it still thinks the link is active and when it now tries to reuse it, it will determine the executable does not exist anymore and report an ORA-28511. The error handler would be easy as you just have to execute the same statement now again and the database will then connect to the SQL Server again using a newly spawned gateway connection.

Legend

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