This discussion is archived
8 Replies Latest reply: Jul 24, 2012 8:22 AM by 933354 RSS

Transparent gateway for MSSQL - update remote table in trigger

933354 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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