Script To Clear The Pending Transaction In Oracle?

sprasad
Posts: 24
Joined: Sun Oct 09, 2016 2:52 pm

Script To Clear The Pending Transaction In Oracle?

Postby sprasad » Sun Oct 09, 2016 3:27 pm

Script To Clear The Pending Transaction In Oracle?

San
Posts: 24
Joined: Sun Oct 09, 2016 2:53 pm

Re: Script To Clear The Pending Transaction In Oracle?

Postby San » Sun Oct 09, 2016 3:28 pm

To Clear The 2pc Pending Transaction In Oracle


The Following Hints will help you out to understand and clear the transaction.

The transaction to be deleted is in the prepared state and has to be either
force committed or rolled back

select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 prepared

rollback force '37.16.108'; ==>For example

Rollback complete.

select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 forced rollback

Commit;

alter system set "_smu_debug_mode" = 4;
Rollback complete.

exec dbms_transaction.purge_lost_db_entry('37.16.108'); ==>For example


STATE can have the following values:

Collecting

This category normally applies only to the global coordinator or local coordinators. The node is currently collecting information from other database servers before it can decide whether it can prepare.

Prepared

The node has prepared and may or may not have acknowledged this to its local coordinator with a prepared message. However, no commit request has been received. The node remains prepared, holding any local resource locks necessary for the transaction to commit.

Committed

The node (any type) has committed the transaction, but other nodes involved in the transaction may not have done the same. That is, the transaction is still pending at one or more nodes.

Forced Commit

A pending transaction can be forced to commit at the discretion of a database administrator . This entry occurs if a transaction is manually committed at a local node.

Forced termination (rollback)

A pending transaction can be forced to roll back at the discretion of a database administrator. This entry occurs if this transaction is manually rolled back at a local node.

Pre Activity to be carried on before executing the script are as follows.


Select status,local_tran_id from dba_2pc_pending;

Check The Status And Execute the script

Source Code For 2pcPending Transation

ORACLE_SID=<Oracle Sid>
ORACLE_HOME=<Oracle Home Path>
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

DispCntPre()
{
sqlplus -s /nolog <<!
conn userid/password
set hea off
set pagesize 0
select count(*) from PENDING_SESSIONS$ where local_tran_id='$1' and state='PREPARED';
exit
!
}

DispCnt()
{
if [ $1 -eq 1 ]
then
sqlplus -s /nolog <<!
conn userid/password
set hea off
set pagesize 0
select count(*) from PENDING_SESSIONS$ where state='COLLECTING';
exit
!
else
sqlplus -s /nolog <<!
conn userid/password
set hea off
set pagesize 0
select count(*) from PENDING_SESSIONS$ where state='PREPARED';
exit
!
fi
}

ViewRec()
{
if [ $1 -eq 1 ]
then
sqlplus -s /nolog <<!
conn userid/password
set hea off
set pagesize 0
select lpad(local_tran_id,35,' '),lpad(state,20,' ') from PENDING_SESSIONS$ where state='COLLECTING';
exit
!
else
sqlplus -s /nolog <<!
conn userid/password
set hea off
set pagesize 0
select lpad(local_tran_id,35,' '),lpad(state,20,' ') from PENDING_SESSIONS$ where state='PREPARED';
exit
!
fi
}

DelRec()
{
sqlplus -s /nolog <<!
conn userid/password
set hea off
set pagesize 0
exec dbms_transaction.purge_lost_db_entry('$1');
Commit;
exit
!
}
DelRecPre()
{
sqlplus -s /nolog <<!
conn perfstat/statperf8i
set hea off
set pagesize 0
rollback force '$1';
commit;
alter system set "_smu_debug_mode" = 4;
exec dbms_transaction.purge_lost_db_entry('$1');
exit
!
}

Disply()
{
cls
echo ""
echo ""
echo " ***********************************************************************"
echo " * *"
echo " * Available 2PC Pending Records *"
echo " * *"
echo " ***********************************************************************"
echo ""
echo ""
echo " Local Transaction ID Status \n "
ViewRec $1 | grep -v Connected
echo "\n\n\n"
echo " Do You Want To Remove It From The Database (y/n) : \c"
read Cho
if [ "$Cho" = "y" ]
then
echo "\n Enter The Local Transaction Id To be Deleted : \c"
read lti
if [ $1 -eq 1 ]
then
conf=`DelRec $lti | grep -v Connected | grep deleted | awk '{print $1}'`
conf=`echo $conf | awk '{print $1}'`
if [ "$conf" = "0" ]
then
echo "\n **************** No Records Deleted ****************"
else
echo "\n **************** Records Deleted ****************"
fi
else
cnt=`DispCntPre $lti | grep -v Connected`
if [ $cnt -eq 0 ]
then
echo "\n **************** No Records Deleted ****************"
else
DelRecPre $lti
echo "\n **************** Records Deleted ****************"
fi
fi
cnt=`DispCnt $1 | grep -v Connected`
if [ $cnt -eq 0 ]
then
MainScr
else
echo "\n Do You Want To Remove another Record : \c "
read cho
if [ "$cho" = "y" -o "$cho" = "Y" ]
then
Disply $1
else
MainScr
fi
fi
else
MainScr
fi
}

MainScr()
{
cls
echo ""
echo ""
echo " ***********************************************************************"
echo " * *"
echo " * 2pc Pending Transaction *"
echo " * *"
echo " ***********************************************************************"
echo ""
echo ""
echo " Main Menu "
echo ""
echo ""
echo " 1. Clear Collecting State "
echo " 2. Clear Prepared State "
echo " 0. EXIT "
echo "\n\n\n\n\n"
echo " Kindly enter the choice :\c"
read choice
if [ $choice -eq 0 ]
then
exit
fi
if [ $choice -eq 1 -o $choice -eq 2 ]
then
cnt=`DispCnt $choice | grep -v Connected`
if [ $cnt -eq 0 ]
then
echo "\n\n **************** No Records Found ****************"
read ch
else
Disply $choice
fi
else
MainScr
fi
}
MainScr


Return to “Scripting”

Who is online

Users browsing this forum: No registered users and 1 guest