8 Replies Latest reply: Jul 24, 2012 10:22 AM by 933354 RSS

    Transparent gateway for MSSQL - update remote table in trigger

    933354
      Hi!

      I want to create trigger on my oracle table. The oracle database has a database link created to MSSQL database using transaprent gateway. Trigger should update table from this MSSQL database.

      So the trigger looks as follow:

      CREATE OR REPLACE TRIGGER "MYNEWTRIGGER"
      before INSERT OR UPDATE OF READFLAG ON MYLOCALTABLE
      FOR EACH ROW
      BEGIN
      update MyRemoteTablel@RemoteDatabaseLink
      set "ReadFlag" = 3
      END;

      When I'm updating the field READFLAG in MYLOCALTABLE it returns me an error:

      ORA-01591: lock held by in-doubt distributed transaction

      So I see that my transaction is not committed. When I execute only the update statement it looks the same unless I also run commit statement. The problem is that inside the trigger I cannot use commit.
      Local tables are updated without problem inside the trigger.

      My database version is 11g

      Any suggestion how to handle that?

      Thanks in advance

      Edited by: userLukasz on 2012-07-19 10:18
        • 1. Re: Transparent gateway for MSSQL - update remote table in trigger
          Kgronau-Oracle
          to update a remote table in a trigger using the gateway you need to make sure the gateway is able to participate in distributed transactions.

          There's a note available on My Oracle Support that describes the mandatory steps to configure the gateway: How to Setup DG4MSQL to Use Distributed Transactions          [Document 227011.1]     


          The note also contains a simple test to verify the 2PC set up. If you're not sure if your gateway is configured correctly to participate in distributed transactions, then just do this test. Also have a look at the alert.log of the Oracle database it sometimes indicates a faulty 2PC gateway configuration.
          • 2. Re: Transparent gateway for MSSQL - update remote table in trigger
            933354
            Thanks,

            I've created necessery table in my MS SQL database and also user RECOVER with the same password (initXXX file is setup correctly with this username and password) but still the same.
            Do I need to restarat listener?
            • 3. Re: Transparent gateway for MSSQL - update remote table in trigger
              Kgronau-Oracle
              No need to restart anything. The config file is read on the fly as soon as a new gateway process is started.

              Just go ahead and perform the small test described in this note that does a local and a remote insert into a test table, then execute the commit. No error should be returned when the config is correct.
              Else you'll get a message stating the distributed transaction failed.
              • 4. Re: Transparent gateway for MSSQL - update remote table in trigger
                933354
                I cannot find description how to perform this test - right now I have no access to My Oracle Support, also I cannot find it on the google
                • 5. Re: Transparent gateway for MSSQL - update remote table in trigger
                  Kgronau-Oracle
                  let me copy/paste the relevant info - make sure that the recovery table is OWNED by the recovery user as described in this note. The sample I was talking about is starting with the creation of the demo table trans within Oracle and SQL Server:

                  How to Setup DG4MSQL to Use Distributed Transactions
                  The gateway must be set up correctly and a select * from all_catalog@ should be possible.
                  Now the following steps must be done to set up the gateway to work in transaction mode:
                  - init.ora of the gateway should contain the additional lines:

                  HS_FDS_RECOVERY_ACCOUNT=HS_RECOVERY
                  HS_FDS_RECOVERY_PWD=RECOVERY
                  HS_FDS_TRANSACTION_LOG=HS_TRANSACTION_LOG
                  HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM

                  SIDE NOTE:

                  If the table name of the transaction log table in the foreign database was created in small letters, HS_FDS_TRANSACTION_LOG= HS_TRANSACTION_LOG can be changed to: HS_FDS_TRANSACTION_LOG= hs_transaction_log


                  - the remote database:

                  a username -as specified at HS_FDS_RECOVERY_ACCOUNT- must be created.

                  Please be aware, that usernames and passwords at the SQL Server are case sensitive. So please make sure to write the username and password as they appear in the init.ora of the gateway.
                  To create the username at the SQL Server the following syntax should be used while connected as dbo user:

                  exec sp_addlogin 'username', 'password', 'defdb'

                  (defdb is the database that is addressed by the gateway; the init.ora of the gateway points to a servername and a database. This database must be specified as defdb for example Northwind).

                  The sample below shows also the minimum database security for the recover user:

                  USE master
                  GO
                  exec sp_addlogin 'HS_RECOVERY', 'RECOVERY', 'Northwind'
                  GO
                  USE Northwind
                  GO
                  exec sp_grantdbaccess 'HS_RECOVERY'
                  GO
                  grant create table to HS_RECOVERY
                  GO


                  Participating in transactions, some info must be logged in a table. To create the necessary table, Oracle provides a script called dg4msql_tx.sql in $ORACLE_HOME\dg4msql\admin directory for 11g gateways.
                  So logon to the Northwind database as recover user (HS_RECOVERY) and run the Script dg4msql_tx.sql.


                  All configuration work is done now and a very simple test should follow up the correct configuration of the gateway:
                  The requirement is a table in the Oracle database:

                  create table trans (a number);

                  and a table in the remote database with the same user used by the gateway (database link); not the RECOVER user account:

                  create table trans (a numeric);


                  Enable gateway tracing (change/add in the init.ora of the gateway: HS_FDS_TRACE_LEVEL=debug) and delete all other trace files present in the $ORACLE_HOME/tg4mslq/trace or $ORACLE_HOME/dg4msql/trace directory.
                  Now start SQL*Plus and connect to the Oracle database with the link to the SQL Server via the gateway. i

                  insert into trans values (111);
                  insert into trans@ values (111);
                  commit;


                  The commit will now initiate the transaction.
                  • 6. Re: Transparent gateway for MSSQL - update remote table in trigger
                    933354
                    I've done everything from the instruction but result is the same. Below you can find a part from trace file which I think contain the error reason:

                    +Oracle][ODBC SQL Server Driver][SQL Server]Invalid object name 'RECOVER.dbo.HS_TRANSACTION_LOG'. {42S02,NativeErr = 208}[Oracle][ODBC SQL Server Driver][SQL Server]The cursor was not declared.+


                    initXXX.I file looks as follow:


                    HS_FDS_RECOVERY_ACCOUNT=RECOVER
                    HS_FDS_RECOVERY_PWD=RECOVER
                    HS_FDS_TRANSACTION_LOG=dbo.HS_TRANSACTION_LOG
                    HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM


                    I've tried to do not use dbo. at the begining of table name but result is the same (+Invalid object name 'RECOVER.HS_TRANSACTION_LOG+) Maybe because I use MSSQL 2008 the init file must be a bit different?

                    Thanks in advance
                    • 7. Re: Transparent gateway for MSSQL - update remote table in trigger
                      Kgronau-Oracle
                      As mentioned earlier, the recovery table HS_TRANSACTION_LOG must belong to the recovery user - in your case it belongs to the dbo user.

                      Please follow the steps very carefully.
                      • 8. Re: Transparent gateway for MSSQL - update remote table in trigger
                        933354
                        OK, now I've got it. The new schema in MSSQL must be created manually and this table must be assigned to this schema, not to the standard one.
                        Now it works, thanks.