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.
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.
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?
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.
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:
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:
exec sp_addlogin 'HS_RECOVERY', 'RECOVERY', 'Northwind'
exec sp_grantdbaccess 'HS_RECOVERY'
grant create table to HS_RECOVERY
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);
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?