๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
ORACLE/Backup & Recovery

[Oracle] Flashback Query AS OF ์ ˆ ์‚ฌ์šฉํ•˜๊ธฐ

by ๐ŸŒปโ™š 2020. 7. 21.

Flashback Query AS OF์ ˆ

Oracle 9i release 2 ๋ฒ„์ „์˜ New Feature๋กœ "AS OF"์ ˆ์„ ์ด์šฉํ•ด์„œ ํŠน์ • ์‹œ์ ์˜ ํ…Œ์ด๋ธ” ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•  ์ˆ˜ ์žˆ๊ฒŒ ๋˜์—ˆ์Šต๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ ํŠน์ • ์‹œ์ ์˜ ์ œํ•œ์€ ์žˆ์Šต๋‹ˆ๋‹ค. Flashback Query๋Š” Undo Data์˜ ์ •๋ณด๋ฅผ ์ด์šฉํ•ด์„œ ๊ฒฐ๊ณผ๋ฅผ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค. ๊ทธ๋ž˜์„œ Undo Data๊ฐ€ ์‚ด์•„์žˆ๋Š” ๊ธฐ๊ฐ„๋™์•ˆ๋งŒ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋Š” ์ œํ•œ์ด ์žˆ์Šต๋‹ˆ๋‹ค. ๋ณดํ†ต Transaction์ด ๋งˆ์นœ ํ›„ undo_retention ํŒŒ๋ผ๋ฏธํ„ฐ๊ฐ’ ๋งŒํผ Undo Data๊ฐ€ ๋ณด์กด๋˜์ง€๋งŒ, Oracle 11g๋ถ€ํ„ฐ Flashback Data Archive ๊ธฐ๋Šฅ์„ ์ด์šฉํ•ด์„œ ๋” ์˜ค๋žœ ๊ธฐ๊ฐ„๋™์•ˆ Undo์˜ ์ •๋ณด๋ฅผ ๋ณด์กดํ•  ์ˆ˜ ์žˆ๊ฒŒ ๋˜์—ˆ์Šต๋‹ˆ๋‹ค. Flashback Data Archive ์„ค์ •์— ๋Œ€ํ•œ ๋‚ด์šฉ์€ ์•„๋ž˜ ๊ธ€์„ ์ฐธ์กฐํ•ฉ๋‹ˆ๋‹ค.

 

[Oracle] Flashback Data Archive ๋ฐ์ดํ„ฐ ๋ณต์› ์ž‘์—…

Flashback Data Archive(11g) DML ์ž‘์—…์„ ํ•˜๋ฉด์„œ ์‹ค์ˆ˜๋ฅผ ์ €์งˆ๋Ÿฌ๋ฒ„๋ฆฌ๊ณ  commit๊นŒ์ง€ ํ•ด๋ฒ„๋ฆฌ๋Š” ๊ฒฝ์šฐ๊ฐ€ ์žˆ์Šต๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด ์‹ค์ˆ˜๋กœ delete๋ฌธ์— ์กฐ๊ฑด์ ˆ์„ ํฌํ•จํ•˜์ง€ ์•Š์•„ ๋ชจ๋“  ๋ฐ์ดํ„ฐ๊ฐ€ ์‚ฌ๋ผ์ง€๋Š”... ๊ทธ๋Ÿฐ ๊ฒฝ์šฐ๊ฐ€ ๏ฟฝ๏ฟฝ

myjamong.tistory.com

 

 

Flashback Query ์‹ค์Šต

์‹ค์Šตํ™˜๊ฒฝ

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 11.2.0.1
SID : orcl
host : 601d2fce71dc

 

 

์‹ค์Šต์šฉ ํ…Œ์ด๋ธ” ์ƒ์„ฑ

SYS@orcl> conn scott/tiger
Connected.
SCOTT@orcl> create table emp_test as select * from emp;

Table created.

SCOTT@orcl> select * from emp_test;

scott ๊ณ„์ •์˜ emp ํ…Œ์ด๋ธ”์„ CTAS๊ตฌ๋ฌธ์œผ๋กœ emp_test๋ผ๋Š” ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.

 

 

 

Data Update & Commit

SCOTT@orcl> conn / as sysdba
Connected.
SYS@orcl> 
select 
    current_scn
    ,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
from v$database;

Flashback Query ๋Š” SCN๊ณผ Time์„ ๊ธฐ์ค€์œผ๋กœ ํ•ด๋‹น ์‹œ์ ์œผ๋กœ ๋˜๋Œ๋ฆด ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๋ฏธ๋ฆฌ ํ™•์ธํ•ด๋‘๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

SYS@orcl> conn scott/tiger
Connected.
SCOTT@orcl> update emp_test set sal = 1000;

14 rows updated.

SCOTT@orcl> commit;

Commit complete.

Table์˜ ๊ธ‰์—ฌ๋ฅผ ๋ชจ๋“œ 1000์œผ๋กœ ๋ณ€๊ฒฝํ•˜๊ณ  Commit๊นŒ์ง€ ์™„๋ฃŒํ–ˆ์Šต๋‹ˆ๋‹ค.

 

SCOTT@orcl> select * from emp_test;

๋ฐ์ดํ„ฐ๊ฐ€ ๋ณ€๊ฒฝ๋œ๊ฒƒ์„ ํ™•์ธํ–ˆ์Šต๋‹ˆ๋‹ค. ์ด์ œ ์œ„์—์„œ ์กฐํšŒํ•œ SCN๊ณผ Time์„ ๊ธฐ์ค€์œผ๋กœ ํ•ด๋‹น ์‹œ์ ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ•˜๋Š” Flashback Query๋ฅผ ์‚ฌ์šฉํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

Flashback Query

SCN
SCOTT@orcl> select * from emp_test as of scn 1401101;

 

 

Timestamp
SCOTT@orcl> select * from emp_test as of timestamp to_timestamp('2020-07-21 12:56:14', 'yyyy-mm-dd hh24:mi:ss');

to_timestampํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•ด์„œ ํ•ด๋‹น ์‹œ๊ฐ„์˜ ์‹œ์  ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ–ˆ์Šต๋‹ˆ๋‹ค.

SCOTT@orcl> select * from emp_test as of timestamp sysdate - 1/24/60*8;

sysdate ํ˜„์žฌ์‹œ๊ฐ„์—์„œ 8๋ถ„์ „์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•˜๋„๋ก ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ–ˆ์Šต๋‹ˆ๋‹ค.

 

๋‘๊ฐ€์ง€ ๋ฐฉ์‹์„ ์ด์šฉํ•ด์„œ ๋ชจ๋‘ ์ด์ „ ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ•˜๋Š” ์ž‘์—…์„ ํ–ˆ์Šต๋‹ˆ๋‹ค. Flashback Query๋Š” ์–ด๋””๊นŒ์ง€๋‚˜ Undo Data๋ฅผ ์‚ฌ์šฉํ•˜๊ธฐ ๋•Œ๋ฌธ์— undo_retention ํ˜น์€ Flashback Data Archive์˜ retention ๊ธฐ๊ฐ„ ๋‚ด์— ๋ฐ์ดํ„ฐ๋งŒ ์กฐํšŒ๊ฐ€ ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค.

 

๋Œ“๊ธ€