Oracle Flashback ±â´É À̶õ?
DB °ü¸®Áß¿¡ ½Ç¼ö·Î µ¥ÀÌŸ¸¦ »èÁ¦Çϰųª µ¥ÀÌŸÀÇ °ªÀ» À߸ø º¯°æ ÇÏ´Â ½Ç¼ö°¡ °¡²û ¹ß»ýÀ» ÇÕ´Ï´Ù.
ÀÌ·¯ÇÑ ¿À·ù¸¦ ¹Ù·Î ÀνÄÇÒ °æ¿ì´Â RollbackÀ̶ó´Â ¸í·ÉÀ¸·Î ¹Ù·Î Àü¿¡ ¼öÇàÇÑ ÀÛ¾÷À» ¿ø»óº¹±Í½Ãų ¼ö ÀÖÁö¸¸, COMMITÀ» ÇÑ ÀÌÈÄ ½ÃÁ¡À̳ª, ÇÑÂü ½Ã°£ÀÌ Áö³ ÈÄ¿¡ ¾Ë¾Ò´Ù¸é °£´ÜÇÏ°Ô º¹±¸ÇϱⰡ ³°¨ ÇÕ´Ï´Ù.
ÀÌ·¯ÇÑ °æ¿ì¿¡ ƯÁ¤ÇÑ ½Ã°£ ¶Ç´Â ½ÃÁ¡À¸·Î µÇµ¹¸± ¼ö ÀÖ´Â ±â´ÉÀÌ Oracle Flashback ±â´É ÀÔ´Ï´Ù.
°£´ÜÇÏ°Ô ¸»Çؼ Flashback ±â´ÉÀº ƯÁ¤ÇÑ °ú°Å½ÃÁ¡ÀÇ ÁúÀǸ¦ ½ÇÇàÇÒ ¼ö ÀÖ°Ô ÇØ ÁÝ´Ï´Ù.
µ¥ÀÌŸº£À̽º¿¡ ±¸Á¶ÀûÀÎ º¯È¸¦ °¡ÇÏÁö ¾Ê°í °ú°Å ÀÏÁ¤ ½ÃÁ¡ÀÇ µ¥ÀÌŸ »óŸ¦ È®ÀÎÇÒ ¼ö ÀÖ´Â ±â´É ÀÔ´Ï´Ù..
ÀÏÁ¾ÀÇ ¿À¶óŬ¿¡¼ Áö¿øÇϴ ŸÀÓ¸Ó½ÅÀ̶ó°í ÇÒ ¼ö ÀÖÁÒ...
¢Â FlashbackÀ» »ç¿ëÇϱâ À§ÇÑ ¿ä±¸Á¶°Ç
- ÀÚµ¿ ¾ðµÎ °ü¸® ½Ã½ºÅÛÀ» »ç¿ëÇØ¾ß ÇÕ´Ï´Ù. (UNDO_MANAGEMENT ÆĶó¹ÌÅ͸¦ AUTO·Î ¼³Á¤)
.UNDO_MANAGEMENT = AUTO
- ÀÌÀüÀÇ ¾î´À ½ÃÁ¡±îÁöÀÇ ¾ðµÎ(UNDO)Á¤º¸¸¦ º¸À¯ÇÏ¿© Flashback Query¸¦ ¼öÇàÇÒ°ÍÀÎÁö
UNDO_RETENTION ÆĶó¹ÌÅ͸¦ ¼³Á¤ÇØ¾ß ÇÕ´Ï´Ù.
.ALTER SYSTEM SET UNDO_RETENTION=1800
- ÀϹݻç¿ëÀÚ°¡ Flashback ±â´ÉÀ» ÀÌ¿ëÇϱâ À§Çؼ DBMS_FLASHBACKÆÐÅ°Áö¿¡ ´ëÇÑ EXECUTE±ÇÇÑÀÌ ÀÖ¾î¾ß ÇÕ´Ï´Ù.
¢Â Flashback »ç¿ëÇϱâ
FlashbackÀÇ »ç¿ë ¹æ¹ýÀº °ú°Å½ÃÁ¡ÀÇ Æ¯Á¤ ½Ã°£À¸·Î »ç¿ëÇÏ´Â ¹æ¹ý°ú SCN(System Change Number)À» »ç¿ëÇÏ´Â ¹æ¹ýÀÌ ÀÖ½À´Ï´Ù.
- °ú°Å½ÃÁ¡ÀÇ ½Ã°£ »ç¿ë: DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);
- SCN »ç¿ë : DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);
°ú°Å½ÃÁ¡ÀÇ ½Ã°£À» ÁöÁ¤ÇÏ¿© Flashback ±â´ÉÀ» »ç¿ë ÇÒ °æ¿ì ¿À¶óŬÀº ³»ºÎÀûÀ¸·Î À̸¦ SCNÀ¸·Î ÀüȯÇÏ¿© ó¸® ÇÕ´Ï´Ù. ½Ã°£ Á¤º¸¸¦ SCNÀ¸·Î MappingÇÏ´Â ½Ã°£ÀÌ ÇÊ¿äÇѵ¥ Åë»ó 5ºÐ ÁÖ±â·Î ÀÌ·ç¾î Áý´Ï´Ù.
µû¶ó¼, ½Ã°£À¸·Î ÁöÁ¤ÇÒ¶§´Â ÇöÀ纸´Ù 5ºÐÀÌ»ó Â÷ÀÌ°¡ ³ª´Â °ú°Å½ÃÁ¡À» ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù.
¶ÇÇÑ Flashback ±â´ÉÀº ¹«ÇÑ´ë·Î ÀÌÀüÀÇ µ¥ÀÌÅ͸¦ Á¶È¸ÇÒ ¼ö ÀÖ´Â ±â´ÉÀÌ ¾Æ´Ï°í,
°ü¸®ÀÚ°¡ UNDO_RETENTION ÆĶó¹ÌÅ͸¦ ÅëÇؼ Á¤ÇØÁØ ½Ã°£(ÃÊ) µ¿¾ÈÀÇ µ¥ÀÌÅ͸¦ Á¶È¸ÇÒ ¼ö ÀÖ½À´Ï´Ù.
- µðÆúÆ® UNDO_RETENTION ½Ã°£Àº 10800(3½Ã°£) ÀÔ´Ï´Ù.
±×¸®°í Flashback data¸¦ Âü°íÇÏ´Â °æ¿ì¿£ DML, DDLµîÀÇ ÀÛ¾÷À» Á÷Á¢ ¼öÇà ÇÒ ¼ö ¾ø½À´Ï´Ù.
¢Â Flashback »ç¿ë¿¹Á¦
C:\>SQLPLUS /NOLOG
¢Æ¢Æ¢Æ¢Æ¢Æ¢Æ Flashback »ç¿ëÀ» À§ÇÑ È¯°æ¼³Á¤ ½ÃÀÛ ¢Æ¢Æ¢Æ¢Æ¢Æ¢Æ
-- SYSDBA ±ÇÇÑÀ¸·Î Á¢¼Ó
SQL>CONN / AS SYSDBA
-- UNDO MANAGEMENT MODE È®ÀÎ
SQL>SHOW PARAMETER UNDO;
NAME TYPE VALUE
--------------------------- ----------- ---------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
-- undo_management°¡ MANUAL·Î µÇ¾îÀÖÀ»°æ¿ì ¾Æ·¡¿Í °°ÀÌ º¯°æÇÏ°í UNDO Å×ÀÌºí½ºÆäÀ̽º¸¦ »ý¼ºÇÏ°í ÁöÁ¤ÇÕ´Ï´Ù..
SQL>ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE=SPFILE;
-- UNDO Å×ÀÌºí ½ºÆäÀ̽º »ý¼º
SQL>CREATE UNDO TABLESPACE UNDOTBS2
DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS2.dbf' SIZE 100M;
-- UNDO Å×ÀÌºí ½ºÆäÀ̽º ÁöÁ¤
SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2
-- UNDO_RETENTION ½Ã°£À» º¯°æÇÏ½Ã¸é ½ÇÁ¦ Àû¿ëÀ» À§ÇØ 5ºÐÁ¤µµ ±â´Ù·Á¾ß ÇÕ´Ï´Ù.
SQL>ALTER SYSTEM SET UNDO_RETENTION=1800
-- scottÀ¯Àú¿¡°Ô DBMS_FLASHBACK EXEUCTE ±ÇÇÑ ºÎ¿©
SQL>GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
¢Æ¢Æ¢Æ¢Æ¢Æ¢Æ Flashback »ç¿ëÀ» À§ÇÑ È¯°æ¼³Á¤ ³¡ ¢Æ¢Æ¢Æ¢Æ¢Æ¢Æ
-- Å×½ºÆ®¸¦ À§Çؼ scott À¯Àú¿¡ Á¢¼ÓÀ» ÇÕ´Ï´Ù.
SQL>CONN scott/tiger
-- emp Å×À̺í 14°ÇÀÇ µ¥ÀÌÅÍ È®ÀÎ
SQL>SELECT * FROM emp;
14 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù...
-- µ¥ÀÌÅÍ »èÁ¦ÇϱâÀüÀÇ ³¯Â¥¸¦ È®ÀÎ ÇÕ´Ï´Ù.
-- FlashbackÀ» ÀÌ¿ëÇÏ¿© ÀÌ ½ÃÁ¡¿¡¼ µ¥ÀÌÅ͸¦ Á¶È¸, º¹±¸ ÇÒ °ÍÀÔ´Ï´Ù.
SQL>SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YY
-------------------
2006-01-21 17:16:51
-- µ¥ÀÌÅ͸¦ »èÁ¦ ÇÕ´Ï´Ù.
SQL>DELETE FROM emp;
14 ÇàÀÌ »èÁ¦µÇ¾ú½À´Ï´Ù.
-- commit ¼öÇà
SQL>COMMIT;
-- µ¥ÀÌÅÍ È®ÀÎ
SQL>SELECT * FROM emp;
¼±ÅÃµÈ ·¹Äڵ尡 ¾ø½À´Ï´Ù.
-- Flashback °ú°Å½ÃÁ¡ÀÇ ½Ã°£»ç¿ë ¸ðµå Enable·Î µ¥ÀÌÅÍ È®ÀÎ
SQL>EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'))
PL/SQL 󸮰¡ Á¤»óÀûÀ¸·Î ¿Ï·áµÇ¾ú½À´Ï´Ù.
-- µ¥ÀÌÅ͸¦ È®ÀÎ ÇÒ ¼ö ÀÖ½À´Ï´Ù.
SQL>SELECT * FROM emp;
14 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.
-- Flashback Disable·Î º¯°æ
SQL> EXEC DBMS_FLASHBACK.DISABLE;
PL/SQL 󸮰¡ Á¤»óÀûÀ¸·Î ¿Ï·áµÇ¾ú½À´Ï´Ù.
-- Flashback Disable·Î º¯°æÇÏ¸é µ¥ÀÌÅ͸¦ È®ÀÎ ÇÒ ¼ö ¾ø½À´Ï´Ù.
SQL>SELECT * FROM emp;
¼±ÅÃµÈ ·¹Äڵ尡 ¾ø½À´Ï´Ù.
Flashback Disable »óÅ¿¡¼µµ ÀÌÀüµ¥ÀÌÅ͸¦ º¸±âÀ§Çؼ´Â µ¥ÀÌÅÍ º¹±¸ ÀÛ¾÷À» ÁøÇàÇØ¾ß ÇÕ´Ï´Ù.
¾Æ·¡´Â »èÁ¦µÈ µ¥ÀÌÅÍ º¹±¸ ¿¹Á¦ ÀÔ´Ï´Ù.
¢Â »èÁ¦µÈ µ¥ÀÌÅÍ º¹±¸
Oracle9i Database Release 2ÀÌ»ó ¹öÀü¿¡¼´Â SELECT...AS OF ¸í·ÉÀ» »ç¿ëÇÏ¿© ½±°Ô Flashback µ¥ÀÌÅ͸¦ º¹±¸ ÇÒ ¼ö ÀÖ½À´Ï´Ù.
Oracle9i Database Release1 ¹öÀü¿¡¼´Â DBMS_FLASHBACKÇÁ·Î½ÃÀú¸¦ ÀÌ¿ëÇؼ µ¥ÀÌÅ͸¦ º¹±¸ÇØ¾ß ÇÕ´Ï.
-- »èÁ¦µÈ µ¥ÀÌÅÍ º¹±¸(¿À¶óŬ ¹öÀü Release 9.2.0.1.0 ½ÇÇà)
SQL>INSERT INTO emp
(SELECT * FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'))
-- º¹±¸µÈ µ¥ÀÌÅÍ È®ÀÎ
SQL>SELECT * FROM emp;
14 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.
-- À§¿¡ º¹±¸µÈ µ¥ÀÌŸ¸¦ RollbackÀ¸·Î Áö¿ì°í DBMS_FLASHBACK ÆÐÅ°Áö¸¦ ÀÌ¿ëÇؼ º¹±¸ÇØ º¾´Ï´Ù.
-- »èÁ¦µÈ µ¥ÀÌÅÍ º¹±¸
SQL>DECLARE
CURSOR emp_cursor is
SELECT * FROM emp;
v_emp emp%ROWTYPE;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'));
OPEN emp_cursor;
-- FlashbackÀ» DisableÇßÁö¸¸ Ä¿¼(test_cursor)´Â ¿©ÀüÈ÷ °ú°Å½ÃÁ¡ÀÇ µ¥ÀÌÅ͸¦ °¡Áö°í ÀÖ½À´Ï´Ù.
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH emp_cursor INTO v_emp;
EXIT WHEN emp_cursor%NOTFOUND;
INSERT INTO emp VALUES (v_emp.empno, v_emp.ename, v_emp.job, v_emp.mgr, v_emp.hiredate, v_emp.sal, v_emp.comm, v_emp.deptno);
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
PL/SQL 󸮰¡ Á¤»óÀûÀ¸·Î ¿Ï·áµÇ¾ú½À´Ï´Ù.
-- º¹±¸µÈ µ¥ÀÌÅÍ È®ÀÎ
SQL>SELECT * FROM emp;
14 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.
Âü°í¹®Çå
- Oracle9i Flashback Query ¿À¶óŬ ±â¼ú¹é¼ 2002³â 3¿ù pdf ¹®¼
- Oracle Technical Bulletins No. 17863 (V9I) ORACLE 9I New Feature : ORACLE FLASHBACK
- °¸í±Ô´ÔÀÇ °Á : Flashback (°ú°Å½ÃÁ¡¿¡¼ ÁúÀǽÇÇà)
|