 Posted 1/14/2009 11:49:16 PM
|
|
|
|
So I noticed the following in the alert log for which I have no clue on how to do .
The users were complaining that they had application issue but the database seemed to be fine and connections were ok and other users were ok but the distributed transactions were the ones having problems. I didn't know what to do.
DISTRIB TRAN bea1.1B1ACBD08DDDC3D5B838
is local tran 1.44.168955 (hex=01.2c.293fb)
insert pending prepared tran, scn=10251917462492 (hex=952.f5e60bdc)
DISTRIB TRAN bea1.0F11E80A89A25DB12D9B
is local tran 2.27.266178 (hex=02.1b.40fc2)
insert pending prepared tran, scn=10251917462490 (hex=952.f5e60bda)
DISTRIB TRAN bea1.13A0807B8F33B4D288AE
is local tran 6.15.190782 (hex=06.0f.2e93e)
insert pending prepared tran, scn=10251917868003 (hex=952.f5ec3be3)
DISTRIB TRAN bea1.13A2807B8F33B4D288AE
is local tran 8.26.211463 (hex=08.1a.33a07)
insert pending prepared tran, scn=10251917867952 (hex=952.f5ec3bb0)
DISTRIB TRAN bea1.0F352E5D72CC2AB61D0D
is local tran 9.19.232071 (hex=09.13.38a87)
insert pending prepared tran, scn=10251917879005 (hex=952.f5ec66dd)
DISTRIB TRAN bea1.13A4807B8F33B4D288AE
is local tran 10.8.228017 (hex=0a.08.37ab1)
insert pending prepared tran, scn=10251917867950 (hex=952.f5ec3bae)
DISTRIB TRAN bea1.13A3807B8F33B4D288AE
is local tran 13.29.162036 (hex=0d.1d.278f4)
insert pending prepared tran, scn=10251917867985 (hex=952.f5ec3bd1)
Wed Jan 14 00:21:08 2009
DISTRIB TRAN bea1.0F11E80A89A25DB12D9B
is local tran 2.27.266178 (hex=02.1b.40fc2)
change pending prepared tran, scn=10251917462490 (hex=952.f5e60bda)
to pending collecting tran, scn=10251917462490 (hex=952.f5e60bda)
DISTRIB TRAN bea1.0F11E80A89A25DB12D9B
is local tran 2.27.266178 (hex=02.1b.40fc2))
delete pending collecting tran, scn=10251917462490 (hex=952.f5e60bda)
Wed Jan 14 00:21:53 2009
DISTRIB TRAN bea1.0F19E80A89A25DB12D9B
is local tran 15.18.185684 (hex=0f.12.2d554)
change pending prepared tran, scn=10251917463595 (hex=952.f5e6102b)
to pending collecting tran, scn=10251917463595 (hex=952.f5e6102b)
DISTRIB TRAN bea1.0F19E80A89A25DB12D9B
is local tran 15.18.185684 (hex=0f.12.2d554))
delete pending collecting tran, scn=10251917463595 (hex=952.f5e6102b)
Wed Jan 14 00:21:55 2009
DISTRIB TRAN bea1.1B1ACBD08DDDC3D5B838
is local tran 1.44.168955 (hex=01.2c.293fb)
change pending prepared tran, scn=10251917462492 (hex=952.f5e60bdc)
to pending collecting tran, scn=10251917462492 (hex=952.f5e60bdc)
DISTRIB TRAN bea1.1B1ACBD08DDDC3D5B838
is local tran 1.44.168955 (hex=01.2c.293fb))
delete pending collecting tran, scn=10251917462492 (hex=952.f5e60bdc)
Wed Jan 14 00:21:58 2009
DISTRIB TRAN bea1.0EEA8F1DC033C4B87C21
is local tran 30.37.180674 (hex=1e.25.2c1c2)
change pending prepared tran, scn=10251917467013 (hex=952.f5e61d85)
to pending collecting tran, scn=10251917467013 (hex=952.f5e61d85)
DISTRIB TRAN bea1.0EEA8F1DC033C4B87C21
is local tran 30.37.180674 (hex=1e.25.2c1c2))
delete pending collecting tran, scn=10251917467013 (hex=952.f5e61d85)
Wed Jan 14 00:22:09 2009
DISTRIB TRAN bea1.106EFC4D055B5ADCE982
is local tran 17.6.153054 (hex=11.06.255de)
change pending prepared tran, scn=10251917497020 (hex=952.f5e692bc)
to pending collecting tran, scn=10251917497020 (hex=952.f5e692bc)
DISTRIB TRAN bea1.106EFC4D055B5ADCE982
is local tran 17.6.153054 (hex=11.06.255de))
delete pending collecting tran, scn=10251917497020 (hex=952.f5e692bc)
Wed Jan 14 00:22:40 2009
DISTRIB TRAN bea1.1B22CBD08DDDC3D5B838
is local tran 29.33.176598 (hex=1d.21.2b1d6)
change pending prepared tran, scn=10251917589948 (hex=952.f5e7fdbc)
to pending collecting tran, scn=10251917589948 (hex=952.f5e7fdbc)
DISTRIB TRAN bea1.1B22CBD08DDDC3D5B838
is local tran 29.33.176598 (hex=1d.21.2b1d6))
delete pending collecting tran, scn=10251917589948 (hex=952.f5e7fdbc)
Wed Jan 14 00:22:43 2009
DISTRIB TRAN bea1.0F262E5D72CC2AB61D0D
is local tran 32.39.143275 (hex=20.27.22fab)
change pending prepared tran, scn=10251917596126 (hex=952.f5e815de)
to pending collecting tran, scn=10251917596126 (hex=952.f5e815de)
DISTRIB TRAN bea1.0F262E5D72CC2AB61D0D
is local tran 32.39.143275 (hex=20.27.22fab))
delete pending collecting tran, scn=10251917596126 (hex=952.f5e815de)
Wed Jan 14 00:24:13 2009
DISTRIB TRAN bea1.0F352E5D72CC2AB61D0D
is local tran 9.19.232071 (hex=09.13.38a87)
change pending prepared tran, scn=10251917879005 (hex=952.f5ec66dd)
to pending collecting tran, scn=10251917879005 (hex=952.f5ec66dd)
DISTRIB TRAN bea1.0F352E5D72CC2AB61D0D
is local tran 9.19.232071 (hex=09.13.38a87))
delete pending collecting tran, scn=10251917879005 (hex=952.f5ec66dd)
Anyone have any clue ? I did a google search but no luck.
|
|
|
Posted 1/14/2009 11:51:01 PM
|
|
|
|
I noticed on google that someone received a similar message:
** 2007-12-14 18:11:25.437
*** SESSION ID 5.1) 2007-12-14 18:11:25.382
*** 2007-12-14 18:11:25.437
DISTRIB TRAN MIS.bb3dc717.10.10.989268
is local tran 10.10.989268 (hex=0a.0a.f1854)
insert pending collecting tran, scn=8369930770050 (hex=79c.c6c01682)
*** 2007-12-17 16:13:50.092
Using 4 slaves for 1 dead transactions
Recovered xid: 0x0001.050.000e9ff0 in 60 milliseconds; nchk=0, size=14
*** 2008-01-29 00:31:57.484
Using 4 slaves for 1 dead transactions
Recovered xid: 0x0002.01c.000d66e1 in 0 milliseconds; nchk=0, size=11
*** 2008-01-29 17:12:14.564
DISTRIB TRAN PREPAID.WORLD.996ad23c.1.21.5864
is local tran 2.78.878386 (hex=02.4e.d6732)
insert pending prepared tran, scn=8391310591393 (hex=7a1.c11645a1)
It looks like the user checked dba_2pc_pending and 1 record existing in that table and he gave rollback force. Even after that the record is there in dba_2pc_pending table.
Still no luck.
|
|
|
Posted 1/15/2009 12:06:54 AM
|
|
|
|
Hi,
You Can try the following to rollback your transactions:
(It appears that you are seeing distributed transactions as Prepared instead of collecting.
Try running this:
SQL> select LOCAL_TRAN_ID,STATE from DBA_2PC_PENDING;
Then based on your results issue the following:
SQL> rollback force '';
SQL> commit;
SQL> alter system set "_smu_debug_mode" = 4;
SQL> exec dbms_transaction.purge_lost_db_entry('');
SQL> exit
You should repeat this for each entry that you get back from the first query.
Good luck.
Stacy
|
|
|
Posted 1/15/2009 12:09:53 AM
|
|
|
|
Hey,
I tried it out and it worked out pretty good!! ! Thanks.
SQL> select LOCAL_TRAN_ID,STATE from DBA_2PC_PENDING;
LOCAL_TRAN_ID STATE
---------------------- ----------------
21.30.169279 prepared
25.25.143970 prepared
34.0.90039 prepared
6.15.190782 prepared
8.26.211463 prepared
10.8.228017 prepared
13.29.162036 prepared
7 rows selected.
SQL> rollback force '21.30.169279';
Rollback complete.
SQL> select LOCAL_TRAN_ID,STATE from DBA_2PC_PENDING;
LOCAL_TRAN_ID STATE
---------------------- ----------------
21.30.169279 forced rollback
25.25.143970 prepared
34.0.90039 prepared
6.15.190782 prepared
8.26.211463 prepared
10.8.228017 prepared
13.29.162036 prepared
7 rows selected.
SQL> commit;
Commit complete.
SQL> alter system set "_smu_debug_mode" = 4;
System altered.
SQL> exec dbms_transaction.purge_lost_db_entry('21.30.169279');
PL/SQL procedure successfully completed.
SQL> exit
and I repeated this for the other results from the first query.
Thank you so much!!!
|
|
|
Posted 5/13/2009 4:07:16 AM
|
|
|
|
| If you see collecting:
SQL> select LOCAL_TRAN_ID,STATE from DBA_2PC_PENDING;
LOCAL_TRAN_ID STATE
---------------------- ----------------
3.70.1108483 collecting
then issue the following to fix it: SQL> execute dbms_transaction.purge_lost_db_entry('3.70.1108483'); PL/SQL procedure successfully completed. SQL> select LOCAL_TRAN_ID,STATE from DBA_2PC_PENDING; no rows selected
|
|
|
|