About DATABASE

출처 : http://blog.naver.com/1101kiho/90024390365

#####################

2pc pending 처리 사용예

######################

sqlplus as sysdba (sys권한으로 접속)

spool 20060715_2pc_pending
set time on
set timing on
set echo on

set pages 100

select * from dba_2pc_pending;

alter session set "_smu_debug_mode" = 4;

exec dbms_transaction.purge_lost_db_entry('8.16.204198');

commit;

select * from dba_2pc_pending;

spool off

#########################
# 2pc pending 처리 절차 #
#########################

DISTRIBUTED TRANSACTION TROUBLESHOOTING (ORA-1591해결 방법)

STEP 1: alert.log file을 check한다.
STEP 2: network 환경을 확인한다.
STEP 3: RECO process가 떠 있는지 확인한다.
        os> ps -ef | grep reco
STEP 4: DBA_2PC_PENDING을 조회해 본다.
        SQL>select local_tran_id, global_tran_id, state, mixed, host, commit#
        from dba_2pc_pending;
STEP 7: DBA_2PC_PENDING의 MIXED column을 확인한다.
      - MIXED값이 NO인 경우 : STEP 8 수행
      - MIXED값이 YES인 경우: STEP 9 수행

STEP 8: DBA_2PC_PENDING의 STATE column의 값을 확인한다.

 CASE 8-1: STATE field ---> COMMITTED인 경우
           SQL>exec dbms_transaction.purge_lost_db_entry('<TRANS_ID>');
           SQL>commit;

 CASE 8-2: STATE field ---> PREPARED인 경우  <-- Lock 인상태
           SQL>rollback force '<TRANS_ID>'; 혹은
           SQL>commit force '<TRANS_ID>';

 CASE 8-3: STATE field ---> COLLECTING인 경우
           SQL>exec dbms_transaction.purge_lost_db_entry('<TRANS_ID>');
           SQL>commit;

 CASE 8-4: STATE field ---> FORCED ROLLBACK/FORCED COMMIT 인 경우
           SQL>exec dbms_transaction.purge_lost_db_entry('<TRANS_ID>'); 
           SQL>commit;

STEP 9: 불일치 사항을 파악하고 DBA_2PC_PENDING을 정리한다.
   MIXED가 YES인 상태에서, inconsistency를 받아들이고 DBA_2PC_PENDING view를
   정리하려면 다음과 같이 수행한다.
           SQL>exec dbms_transaction.purge_mixed('1.8.238');
           SQL>commit;

================================================================
exec dbms_transaction.purge_mixed('1.8.238') 에러발생시 조치요령
에러내용
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1

조치
alter session set "_smu_debug_mode" = 4;

# END #########################################################################