18. BACKING OUT TRANSACTION WITH FLASHBACK

• target transaction 과 관련된 transaction 을 Online 상에서 roll back 하기 위해 사용되는 논리적 Recovery 옵션
• 9i 에서는 Automatic undo management 관리 방식을 통해서 row 단위로만 가능
• 10g 에 와서는 table level 과 database level 까지 복구 할 수 있도록 지원
• 11g 에서는 Transaction 단위로 복원해 줄 수 있게 향상
• 온라인상에서 데이터를 복구하기 위한 방법으로 9i 와 10g 에서도 Transaction 단위로 복원이 가능, 이는 Flashback_transaction_query를
  이용했던 부분인데 undo segment 를 조회하는 역할로 제한 되었고, PK 와 FK 또는 Transaction 종속성에 대해서는 지원하지 않았다.
• 11g 에서는 아카이브된 로그를 이용하면서 종속성이 있는 다른 Transaction 까지도 자동으로 복원이 가능하도록 더욱 향상된 기능을 지원해 준다
• 과거 처럼 Flashback 으로 특정시점 전체 또는 부분으로 되돌아 가는 것이 아니라, 잘못된 transaction 부분만 골라서 Rollback 처리를 해도 해당 Transaction 과 관련된
  다른 Transaction 만으로 제한해서 함께 또는 선택적으로 Rollback 처리가 가능해 졌다.

1. Transaction 1 은 5개의 rows 를 입력.
2. Transaction 2 는 2개의 rows 를 update.
3. Transaction 3 은 또다시 3개의 rows 를 update, 그 중 1개는 Transaction 2 에서 이미 update 한 1개의 row 와 관련.
4. Transaction 4 는 또 다른 1개의 row 를 update. 이것은 이전의 Transaction 2 와 아무런 관련이 없다.
5. 여기서는 이미 Commit 된 Transaction 들 중에서 Transaction 2 를 Roll Back 하고자 했고 Transaction 2 와 연관 있는 Transaction 3 도 함께 Roll back 처리 됨을 확인.
6. Transaction 4 는 그냥 그대로 반영된 상태로 남아 있다는 것을 확인.

sqlplus "/ as sysdba"
ALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;
ALTER SYSTEM SET db_recovery_file_dest_size = 10737418240 SCOPE=BOTH;

shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list
exit

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
GRANT EXECUTE ON dbms_flashback TO hr;
GRANT select any transaction TO hr;

• Transaction #1

/*== Create test data for flashback transaction ==*/
connect hr/hr

/*== Test transaction 1 ==*/
INSERT INTO hr.regions VALUES (10,'Pole');
INSERT INTO hr.regions VALUES (20,'Moon');
INSERT INTO hr.regions VALUES (30,'Venus');
INSERT INTO hr.regions VALUES (40,'Mars');
INSERT INTO hr.regions VALUES (50,'Saturn');
COMMIT;

• Transaction #2
  • Transaction 2 는 region_id 10, 20 를 변경하면서 Transaction 1 에 대한 종속성을 갖게 된다.

/*== Test transaction 2 ==*/
/*== Region 10 and 20 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='Two Poles' WHERE region_id = 10;
UPDATE hr.regions SET region_name='Many Moons' WHERE region_id = 20;
COMMIT;

• Transaction #3
  • Transaction 3 은 region_id 10 을 변경하면서 Transaction 1 과 2 에 대한 종속성을 갖게 된다.
  • region_id 40,50 을 변경하면서 Transaction 1 에 대한 종속성을 갖게 된다.

/*== Test transaction 3 ==*/
/*== Region 10 has a WAW dependency on transaction 1 and 2 ==*/
/*== Region 40 and 50 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='No star' WHERE region_id = 10;
UPDATE hr.regions SET region_name='Red star' WHERE region_id = 40;
UPDATE hr.regions SET region_name='Big star' WHERE region_id = 50;
COMMIT;

• Transaction #4
  • 1개의 rows 에 대한 updating. 1번 transaction 의 입력된 한개를 업데이트 하는것으로 구성.
  • Transaction 4 는 region_id 30 을 변경하면서 Transaction 1 에 대한 종속성을 갖게 된다

/*== Test transaction 4 ==*/
/*== Region 30 has a WAW dependency on transaction 1 ==*/
UPDATE hr.regions SET region_name='Still called Venus' WHERE region_id = 30;
COMMIT;

connect / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;

prompt "Setup for Flashback Transaction completed"
exit

Nonconflict Only - 대상 Transaction의 충돌하지 않는 변경 사항만 취소.
Nocascade Force - 대상 Transaction의 모든 변경 사항이 취소.
Cascade - 대상 Transaction의 모든 변경 사항과 종속 Transaction의 모든 변경 사항이 취소.

BEGIN
  DBMS_FLASHBACK.transaction_backout (numtxns => 1,
                                      xids    => xid_array('060015009D030000'),
                                      options => DBMS_FLASHBACK.cascade);
END;
/

SELECT *
FROM   dba_flashback_txn_state
WHERE  xid = '060015009D030000';

SELECT xid_report
FROM   dba_flashback_txn_report
WHERE  compensating_xid = '05001800A0030000';

• 내부적으로는 아래와 같이 Rollback 처리가 되는 것을 확인

SQL 실행
Flashback Transaction을 커밋하기 전에 SQL 질의를 실행하여 변경으로 인한 영향을 확인할 수 있습니다.
---------------------------------
[exec=yes] update "HR"."REGIONS" set "REGION_NAME" = 'Moon' where "REGION_ID" = '20' and
"REGION_NAME" = 'Many Moons'
[exec=no] update "HR"."REGIONS" set "REGION_NAME" = 'Pole' where "REGION_ID" = '10' and
"REGION_NAME" = 'Two Poles'
---------------------------------
[exec=no] update "HR"."REGIONS" set "REGION_NAME" = 'Saturn' where "REGION_ID" = '50' and
"REGION_NAME" = 'Big star'
[exec=no] update "HR"."REGIONS" set "REGION_NAME" = 'Mars' where "REGION_ID" = '40' and
"REGION_NAME" = 'Red star'
[exec=no] update "HR"."REGIONS" set "REGION_NAME" = 'Two Poles' where "REGION_ID" = '10' and
"REGION_NAME" = 'No star'