Skip to content

how to find what was in dba_2pc_pending transaction

how to find what was in dba_2pc_pending transaction published on Комментариев к записи how to find what was in dba_2pc_pending transaction нет

generate logminer list:

select completion_time,'exec DBMS_LOGMNR.ADD_LOGFILE('''||name||''');' from v$archived_log where completion_time > trunc(sysdate) +9/24 and  completion_time < trunc(sysdate) +11/24 and dest_id=1;

build catalog

EXEC DBMS_LOGMNR.START_LOGMNR(   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

find out transaction content:

select global_tran_id,local_tran_id,l.* from (
 (
select global_tran_id,local_tran_id,
from DBA_2PC_PENDING) T
JOIN  V$LOGMNR_CONTENTS L ON (l.xidusn||'.'||l.xidslot||'.'||l.xidsqn)=t.local_tran_id
)

commit\rollback:

select ' commit force '''||local_tran_id||''';' from dba_2pc_pending ;
--select ' rolback force '''||local_tran_id||''';' from dba_2pc_pending ;
select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||local_tran_id||'''); commit;' from dba_2pc_pending ;

if archivelogs are on tape:

select sum(blocks*block_size)/1024/1024/1024 gb_size, THREAD#, min(sequence#),max(sequence#) from gv$archived_log where dest_id=1 and first_time > trunc(sysdate) +9/24
and first_time < trunc(sysdate) +11/24 group by THREAD# ;

run {
restore archivelog thread 1 from sequence 3925 until sequence 3938;
}

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика