DBMS_JOB, Distributed transactions and logging.

[Alex Vilner]

Hi,

We have a classic distributed transaction scenario. A local 8i (8.1.7)
server initiates the transaction, does some processing, calls a remote
procedure on another 8i server over the DB link... Both are running on
Solaris.

We are aware of transaction control not working on the remote side in
distributed transactions, but we have not much control over the local
server processes. The task we are faced with is to log errors on the
remote server, and keep them logged (in tables) even if the
transaction rolls back.

The approach was to try and use DBMS_JOB in the exception block of the
remote procedures. However, it seems that DBMS_JOB does not add a job
to the queue unless there is a commit OR unless DBMS_JOB.RUN (implicit
commit) is called. Is there a workaround this issue?

Or, alternatively, is there a way to have this work:
LOCAL:
BEGIN
remote_proc@db_link;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;

REMOTE:
BEGIN
<do something>
EXCEPTION
WHEN OTHERS THEN
<log into a table>
<PRAGMA AUTONOMOUS_TRANSACTION doesnt work with distributed
transactions>
RAISE; -- back to local
END;

It seems as if it is such a commonly needed task... How do others do
it?
Thank you in advance!

[Marc Billiet]

Alex Vilner heeft geschreven:

> Hi,
>
> We have a classic distributed transaction scenario. A local 8i (8.1.7)
> server initiates the transaction, does some processing, calls a remote
> procedure on another 8i server over the DB link... Both are running on
> Solaris.
>
> We are aware of transaction control not working on the remote side in
> distributed transactions, but we have not much control over the local
> server processes. The task we are faced with is to log errors on the
> remote server, and keep them logged (in tables) even if the
> transaction rolls back.
>
> The approach was to try and use DBMS_JOB in the exception block of the
> remote procedures. However, it seems that DBMS_JOB does not add a job
> to the queue unless there is a commit OR unless DBMS_JOB.RUN (implicit
> commit) is called. Is there a workaround this issue?
>

> Thank you in advance!

What we do is sending the error message to a queue (dbms_aq). This queue
is dequeued by another job which inserts the messages into a table (of
course, the messages can be kept in the queue table as well). You can
also use dbms_pipe, which is easier to use.
Dbms_alert can't be used, because alerts are only sent after a commit.

Marc

[Alex Vilner]

Thank you, Marc!
This is what we decided on doing since we use queues for a lot of
other inter-server communication. The PIPE would not really work due
to complexity of data that is being logged. It is not just a plain
stream...

Thanks again for the response.

Other ideas? Will Oracle fix the transaction management in 9i (or 8i
patches)?

Marc Billiet <marcREMOVE...@bigfoot.com> wrote in message news:<alq78u$pdn$1...@vkhdsu24.hda.hydro.com>...

[Alex Vilner]

Thank you, Marc!
This is what we decided on doing since we use queues for a lot of
other inter-server communication. The PIPE would not really work due
to complexity of data that is being logged. It is not just a plain
stream...

Thanks again for the response.

Other ideas? Will Oracle fix the transaction management in 9i (or 8i
patches)?

Marc Billiet <marcREMOVE...@bigfoot.com> wrote in message news:<alq78u$pdn$1...@vkhdsu24.hda.hydro.com>...

[Vladimir M. Zakharychev]

Research autonomous transactions. You can write a generic error log
procedure that you will call in exception handler, and this procedure
will log error in an autonomous transaction and commit it (while the
outer transaction may rollback at will). To declare a procedure
transaction scope autonomous you use
PRAGMA AUTONOMOUS_TRANSACTION
in procedure declaration block.

hth.

--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

"Alex Vilner" <al...@sinoma.com> wrote in message
news:22e9f6e0.02091...@posting.google.com...

[Alex Vilner]

Vladimir,

Thank you for your reply (Spasibo!) -- the issue is that PRAGMA
AUTONOMOUS_TRANSACTION is not allowed on the remote side of the
distributed transaction, and this is precisely where we want to do the
logging...

--Alex

"Vladimir M. Zakharychev" <b...@dpsp-yes.com> wrote in message news:<altdj6$m0h$1...@babylon.agtel.net>...

[Vladimir M. Zakharychev]

Well then, take a look at DBMS_PIPE. I think it is possible to run a listening
session on remote site that will pick up messages from the pipe which remote
procedures (running locally on that site) will post and log them independently.
Never tried this myself, but I have a gut feeling that it should work.

--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com

Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

"Alex Vilner" <al...@sinoma.com> wrote in message

news:22e9f6e0.02092...@posting.google.com...

[Vladimir M. Zakharychev]

I even came up with a quick demo for this:

create table dtx_log (
time_stamp date default sysdate
,logstr varchar2(1000)
)
/

create or replace procedure dtx_log_send( v dtx_log.logstr%type)
as
l_stat integer;
begin
dbms_pipe.pack_message(v);
-- we are using implicit pipe here, it will be created automatically
-- for us.
l_stat := dbms_pipe.send_message('dtx_log');
exception
when others then null;
end;
/

create or replace procedure dtx_log_listen
as
l_v dtx_log.logstr%type;
done boolean := false;
l_stat integer;
begin
while (not done) loop
begin
l_stat := dbms_pipe.receive_message('dtx_log');
if (l_stat = 0) then
-- got something from the pipe, we don't check what data type we got
-- as we do not expect anything but VARCHAR2 here.
dbms_pipe.unpack_message(l_v);
if (upper(l_v) = 'QUIT') then
-- end of logging signaled, terminate
done := true;
else
-- insert new log entry
insert into dtx_log (logstr) values (l_v);
commit;
end if;
end if;
exception
-- ignore pipe errors
when others then null;
end;
end loop;
end;
/

I then started dtx_log_listen at remote site and called dtx_log_send@remote('message') several
times followed by dtx_log_send@remote('quit'). dtx_log_listen successfully terminated as I
expected, and a quick peek at the log revealed that it worked as expected either:

SQL> select to_char(time_stamp,'HH24:MI:SS') timestamp, substr(logstr,1,60) logstr from dtx_log;

TIMESTAM LOGSTR
-------- ------------------------------------------------------------
17:43:07 remote log entry
17:43:15 another remote log entry
17:43:22 yet another remote log entry

So DBMS_PIPE seems like a solution for you...

--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

"Vladimir M. Zakharychev" <b...@dpsp-yes.com> wrote in message news:amsb9j$71n$1...@babylon.agtel.net...