FLASHBACK - 사례 ( update 문에서 조건식 누락으로 이전 데이터로 복구 수행 )

---------------------------------------------------------------------------------------------------------------

-- 사례 :  update 문에서 조건식 누락으로 이전 데이터로 복구 수행

---------------------------------------------------------------------------------------------------------------

1. UPDATE 오류 퀴리문 수행

UPDATE

TB_US_SELLERMBR_TAB_AUTH

SET

PMT_YN = 'Y'

, UPD_DT = SYSDATE

, UPD_ID = 'admin_bp_ykmoon'

, USE_AGENCY_YN = null

WHERE

--TAB_CD = 'US011001'  : 누락된 조건

AND INSD_SELLERMBR_NO IN (

'SE201601192026131890008496',

'SE201511231608309790007972',

'SE201601130940378710008421',

'IF1423341971620110316213336',

'IF1423192868220101102032551',

'IF1423419672620110623143934'

);

-- 작업시간

17:33:09 208 rows updated.

elapsed: 00:00:00.17

17:33:09 commit;

2. 갱신된 시점 data 확인하기

SELECT * FROM TB_US_SELLERMBR_TAB_AUTH AS OF TIMESTAMP TO_TIMESTAMP('2017-06-29 17:33:09.000000000', 'YYYY-MM-DD HH24:MI:SS.FF')

WHERE INSD_SELLERMBR_NO IN (

'SE201601192026131890008496',

'SE201511231608309790007972',

'SE201601130940378710008421',

'IF1423341971620110316213336',

'IF1423192868220101102032551',

'IF1423419672620110623143934') ;

--AND LNNVL(TAB_CD = 'US011001');

    

3. PK를 이용한 MEAGE 문으로 이전 DATA로 갱신하기.

MERGE INTO TB_US_SELLERMBR_TAB_AUTH T

USING (

    SELECT * 

FROM TB_US_SELLERMBR_TAB_AUTH AS OF TIMESTAMP TO_TIMESTAMP('2017-06-29 17:33:09.000000000', 'YYYY-MM-DD HH24:MI:SS.FF')

    WHERE INSD_SELLERMBR_NO IN (

'SE201601192026131890008496',

'SE201511231608309790007972',

'SE201601130940378710008421',

'IF1423341971620110316213336',

'IF1423192868220101102032551',

'IF1423419672620110623143934')

AND LNNVL(TAB_CD = 'US011001') ) S   -- 해당 조건은 원래 갱신 작업 대상

             -- 이미 처리되었기 때문에 데이터 제외하여 작업완료함.

ON ( T.TENANT_ID = S.TENANT_ID

AND T.INSD_SELLERMBR_NO = S.INSD_SELLERMBR_NO

AND T.TAB_CD = S.TAB_CD )

WHEN MATCHED

THEN

UPDATE SET T.PMT_YN = S.PMT_YN

, T.UPD_DT = S.UPD_DT

, T.UPD_ID = S.UPD_ID

, T.USE_AGENCY_YN = S.USE_AGENCY_YN 

;

4. 원복 데이터 조회하기.    

COMMIT;

---------------------------------------------------------------------------------------------------------------------------------------

-- 기타 참조

---------------------------------------------------------------------------------------------------------------------------------------

Oracle Database 11g 부터 Flashback Data Archive는 영구적인 저장 공간인 Flashback Recovery Area에 변경 내역을 저장합니다.

예를 들어 설명해 보기로 하겠습니다.

 (참고: Flashback Data Archive를 이용하려면 Automatic Undo Management 기능이 활성화되어 있어야 합니다.) 

먼저 아래와 같은 방법으로 Flashback Data Archive를 생성합니다:

SQL> create flashback archive near_term
2 tablespace far_near_term
3 retention 1 month
4 /

Flashback archive created.

여기서 "retention"이 갖는 의미는 나중에 설명하도록 하겠습니다. 아카이브는 far_near_term 테이블스페이스에 생성됩니다.

이제 TRANS 테이블에 발생한 변경 사항을 기록해야 하는 경우를 가정해 보겠습니다. 관리자는 이 테이블에 Flashback Data Archive를 활성화하기만 하면 됩니다.

SQL> alter table trans flashback archive near_term;

Table altered.

이제 TRANS 테이블은 Flashback Data Archive 모드로 전환됩니다. 테이블의 로우에 대한 모든 변경 내역은 영구적으로 추적됩니다. 간단한 예를 확인해 봅시다.

먼저 테이블의 특정 로우를 선택합니다.

SQL> select txn_amt from trans where trans_id = 2;

TXN_AMT
----------
19325.67

SQL> update trans set txn_amt = 2000 where trans_id = 2;
1 row updated.
SQL> commit;
Commit complete.

이제 이 로우의 컬럼을 조회하면 2000이라는 값이 확인될 것입니다. 과거 특정 시점의 값을 확인하려면 아래와 같이 Flashback 쿼리를 실행하면 됩니다.

elect txn_amt
from trans
as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')
where trans_id = 2;

TXN_AMT
----------
19325.67

시간이 어느 정도 흘러 언두 세그먼트에 기록된 언두 데이터가 삭제되고 난 다음, 다시 플래시백 쿼리를 실행해 봅니다:

select txn_amt
from trans
as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')
where trans_id = 2;

데이터는 "retention" 옵션에 설정된 기간 동안 보존됩니다. 보존 기간이 만료되고 새로운 데이터가 입력되면 오래된 데이터는 자동으로 삭제됩니다. 또 아래와 같은 명령으로 직접 데이터를 삭제할 수도 있습니다:

alter flashback archive near_term purge before scn 1234567;

---------------------------------------------------------------------------------------------------------------------------------------

-- 기타 참조

---------------------------------------------------------------------------------------------------------------------------------------

--UNDO_MANAGER = AUTO

SELECT * FROM V$PARAMETER WHERE NAME LIKE 'undo%';

SELECT * FROM V$VERSION;

SELECT CURRENT_SCN FROM V$DATABASE;

-- 12960866928806

SELECT VERSIONS_STARTSCN ST_SCN, VERSIONS_ENDSCN ENDSCN, VERSIONS_XID TXID

        , VERSIONS_OPERATION OPT

        , TEST01.*

  FROM TEST01 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE; 

SELECT * FROM TEST01 AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '10' MINUTE);

INSERT INTO TEST01(NO) VALUES(20);

COMMIT;

SELECT * FROM TEST01;

FLASHBACK TABLE  TEST01 TO SCN '12960866928806'; 

--ORA:08189 ERROR

ALTER TABLE TEST01 ENABLE ROW MOVEMENT; 

FLASHBACK TABLE  TEST01 TO SCN '12960866928806'; 

ALTER TABLE TEST01 DISABLE ROW MOVEMENT; 

DELETE FROM TEST01 ;

COMMIT;

-- ORA_ROWSCN 는 Pseudo columns 입니다. ORACLE이 만들어 주는 의사 컬럼 입니다. 

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) -- 2015-04-21 12:28:26.000000000 

  FROM TEST01; 

-- 이전 데이터 확인.

SELECT * FROM TEST01 AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '3' MINUTE);

SELECT * FROM TEST01 AS OF TIMESTAMP TO_TIMESTAMP('2017-05-19 14:02:00.000000000', 'YYYY-MM-DD HH24:MI:SS.FF'); 

ALTER TABLE TEST01 ENABLE ROW MOVEMENT; 

FLASHBACK TABLE TEST01 TO TIMESTAMP 

  TO_TIMESTAMP('2017-05-19 14:00:00.000000000', 'YYYY-MM-DD HH24:MI:SS.FF'); 

  

SELECT * FROM TEST01;

ALTER TABLE TEST01 DISABLE ROW MOVEMENT; 

DELETE TEST01;

COMMIT;

DECLARE

I_CNT NUMBER := 0;

BEGIN

FOR I_CNT IN 1 .. 5000 LOOP

  INSERT INTO TEST01(NO) VALUES(I_CNT);

END LOOP;

END;

COMMIT;

SELECT * FROM TEST01;

DROP TABLE TEST01;

SELECT R.*

  FROM SYS.DBA_RECYCLEBIN R

 WHERE OWNER = 'SPADMIN';

-- 기존 테이블명으로 복원 

flashback table TEST01 to before drop; 

-- 테이블명을 변경하여 복원

flashback table TEST01 to before drop rename to TEST02;

SELECT * FROM TEST01;

SELECT FLASHBACK_ON FROM V$DATABASE;

-- flashback_on

STARTUP MOUNT 

ALTER DATABASE ARCHIVELOG; 

ALTER DATABASE FLASHBACK ON; 

SELECT FLASHBACK_ON FROM V$DATABASE; 

ALTER DATABASE OPEN;

----------------------------------------------------------------------------------------------------------------