|
|
IOTN :: Field Book :: ORACLE 
|
|
flashback table, flashback query [ 9i~ ] [ truncate ORA-01466 ]
|
|
최길호
[LIST]
|
2006-01-18 11:58:54, 조회 : 21,997 |
추가
select count(1) from sys.aud$
as of timestamp (sysdate-10/1440);
COUNT(1)
----------
3099
SYS@db11> select count(1) from sys.aud$ as of timestamp (sysdate-10/1440);
COUNT(1)
----------
3099
SYS@db11> truncate table sys.aud$;
Table truncated.
SYS@db11> select count(1) from sys.aud$ as of timestamp (sysdate-10/1440);
ORA-01466: unable to read data - table definition has changed
select distinct versions_xid, versions_startscn, versions_endscn from scott.emp
versions between scn minvalue and maxvalue ;
select operation,undo_sql from SYS.FLASHBACK_TRANSACTION_QUERY
where COMMIT_TIMESTAMP between
to_timestamp('2020-05-17 23:25', 'yyyy-mm-dd hh24:mi')
and
to_timestamp('2020-05-17 23:26', 'yyyy-mm-dd hh24:mi')
and table_name='EMP' and
xid = HEXTORAW('0009000F0000063F')
;
추가
FLASHBACK TABLE emp TO BEFORE DROP
RENAME TO int2_admin_emp;
SELECT OWNER,OBJECT_NAME, ORIGINAL_NAME, TYPE FROM DBA_RECYCLEBIN;
OWNER OBJECT_NAME ORIGINAL_NAME TYPE
----- ------------------------------ ------------------------- --------
SCOTT BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX INDEX
SCOTT BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX INDEX
SCOTT BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX INDEX
SCOTT BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK INDEX
SCOTT BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY TABLE
SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';
ALTER INDEX SCOTT."BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
추가
SELECT NAME, VALUE/60 MINUTES_RETAINED FROM V$PARAMETER WHERE NAME = 'undo_retention';
ALTER TABLE hr.employees ENABLE ROW MOVEMENT;
SELECT other.owner, other.table_name
FROM sys.all_constraints this, sys.all_constraints other
WHERE this.owner = 'HR'
AND this.table_name = 'EMPLOYEES'
AND this.r_owner = other.owner
AND this.r_constraint_name = other.constraint_name
AND this.constraint_type='R';
FLASHBACK TABLE hr.employees TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00','YYYY-MM-DD HH24:MI:SS');
flashback table
============================================================================
select object_name,original_name,droptime from recyclebin order by droptime desc;
flashback table tname to before drop;
flashback table "BIN$i8mdH/ZDDBfgQKjABAoQOg==$0" to before drop rename to tname;
간단한 사용예
============================================================================
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------
06/01/18 11:56:49.251000 +09:00
SQL> exec dbms_flashback.enable_at_time('06/01/18 11:54:34');
2006/03/27일 update
============================================================================
Flashback Query를 이용한 Table Recovery
1) 개요
테이블의 데이터를 삭제,변경 후 커밋 하였을 경우 복구 방법은 여러가지가 있다.
* Point-In-Time Recovery : 백업을 restore 후 특정시점까지 media recovery 후
resetlogs 옵션으로 오픈
* log miner : redo log file을 이용하여 undo statement를 추출해 복구
* flashback query : undo segment를 이용하여 undo_retention에 지정된 시점까지 복구
* FLASHBACK TABLE 명령 : 10g 에서만 가능
2) 가정
* PITR 의 경우 시스템 다운이 있으므로 undo_retention 이 지났거나
redo log 또는 archive log를 사용할 수 없을 경우 불가피하게 적용
oracle 7 의 경우는 오로지 PITR 만 가능
* undo_retention 에 해당하는 시간이 이미 경과하여 flashback query를
사용할 수 없는 경우 log miner 적용
* 설명하려는 flashback query의 경우 9.2.0.4 에서 테스트하였으며
8i 에서도 가능한지는 테스트하지 못하였음
* flashback query에 관한 자세한 내용은 어느 정도 알고 있다고 가정
3) 복구 방법
flashback 에 관련된 자료를 찾아보면 10g가 아닌 한 대부분 PL/SQL 을 이용한 복구 방법을 제시하고 있다.
하지만 undo_retention이 작게 설정되어 있을 경우 PL/SQL 을 사용하더라도 복구는 빨리 진행되기 어렵다.
대부분의 자료들은 8i에서 테스트된 내용을 담고 있으므로 8i의 제약사항이던가
아니면 테스트가 되지 않았던가 둘중의 하나 일 것이다.
따라서 flashback query를 보다 편하게 사용하여 복구를 진행토록 하는 방법을 알아본다.
시간에 따라 다음 SQL 문장들을 따라해 보면 간단히 복구작업을 할 수 있다.
***********************************************************
SCOTT USER
***********************************************************
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
2 dbms_flashback.get_system_change_number
3 from dual;
TO_CHAR(SYSDATE,'YY GET_SYSTEM_CHANGE_NUMBER
------------------- ------------------------
2005-10-06 10:53:17 1761113450
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete from dept;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
2 dbms_flashback.get_system_change_number
3 from dual;
TO_CHAR(SYSDATE,'YY GET_SYSTEM_CHANGE_NUMBER
------------------- ------------------------
2005-10-06 10:54:36 1761121544
***********************************************************
SYTEM USER
***********************************************************
SQL> grant execute on dbms_flashback to scott;
Grant succeeded.
=> undo_retention 만 지나지 않았다면 실제로 이 시점에서 grant 해줘도 상관없다.
또한 scott user는 이미 세션을 닫았어도 상관없다.
***********************************************************
SCOTT USER
#1
***********************************************************
SQL> select * from dept;
no rows selected
SQL> exec dbms_flashback.enable_at_time(sysdate-5/1440);
=> 시간을 지정하여 flashback
PL/SQL procedure successfully completed.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> create table dept_recovered as select * from dept;
create table dept_recovered as select * from dept
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode
SQL> delete from dept;
delete from dept
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode
=> flashback mode에서는 dml 또는 ddl 이 안된다.
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
SQL> select * from dept;
no rows selected
***********************************************************
SCOTT USER
#2
***********************************************************
SQL> exec dbms_flashback.enable_at_system_change_number(1761113450);
=> SCN을 지정하여 flashback
PL/SQL procedure successfully completed.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> create table dept_recovered as select * from dept;
create table dept_recovered as select * from dept
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode
SQL> delete from dept;
delete from dept
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode
=> flashback mode에서는 dml 또는 ddl 이 안된다.
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
***********************************************************
SCOTT USER
#3
***********************************************************
SQL> select * from dept;
no rows selected
SQL> create table dept_backup as select * from dept where 1=2;
Table created.
SQL> exec dbms_flashback.enable_at_system_change_number(1761113450);
PL/SQL procedure successfully completed.
SQL> insert into dept_backup select * from dept;
insert into dept_backup select * from dept
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
=> dept_backup 테이블을 flashback 시점에서는 알 수 없는게 당연하다.
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
******************************************************
잠깐 정리
- flashback 모드에서는 dml, ddl 이 안되므로 위와 같은 CTAS(create table as select) 도 불가능
- 미리 만들어 놓은 테이블에도 insert 불가능
- 그래서 PL/SQL 을 사용하는데 복구에는 속도가 중요하므로 사용상에 제약이 많음
제약 회피 방법
- dbms_flashback package를 사용하여 scn 을 이전 시점으로 변경하지 않고
query 실행 시점에만 잠깐씩 옮겨 backup 테이블을 만드는 방법을 사용한다.
- 변경된 건수가 많아서 하나씩 체크하여 복구하는 것 보다는 backup 테이블을 만들어 예전데이터를
모두 변경시켜 놓고 추후 minus 나 hash anti-join, outer join 등을 이용하여
변경 또는 삭제된 부분만 추출하여 복구하는 것이 효율적이다.
******************************************************
SQL> select * from dept;
no rows selected
SQL> select * from dept as of scn 1761113450;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from dept as of timestamp (sysdate-30/1440);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept_backup select * from dept as of scn 1761113450;
4 rows created.
SQL> create table dept_recovered as select * from dept as of scn 1761113450;
Table created.
SQL> select * from dept_backup;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from dept_recovered;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4) 결론
초기 대응만 빨리 할 수 있다면 데이터 변경 시점 전의 데이터를 온라인 중에서 쉽게 복구할 수 있다.
|
18.97.9.172
|
|
|
|
|
|
SET NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949
-- sys or system
alter system set undo_retention=10800 --3H;
GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
-- user
-- oops delete -.-
delete emp where EMPNO=7934;
-- oops commit -.-^
commit;
alter session set nls_date_format='YY/MM/DD HH24:MI:SS';
select sysdate from dual;
exec dbms_flashback.enable_at_time( SYSDATE - 10 / 1440 );
-- exec dbms_flashback.enable_at_time( to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS') );
select * from emp where EMPNO=7934;
exec dbms_flashback.disable();
-- select as of
select * from emp as of timestamp to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS') where EMPNO=7934;
-- recover EMPNO=7934
insert into emp select * from emp as of timestamp to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS')
where EMPNO=7934;
as of timestamp (sysdate-10/1440); |
2008-10-13
17:17:52
|
|
|
|
|
insert into emp
(select * from emp as of timestamp
to_timestamp('08/10/13 11:30:00','YY/MM/DD HH24:MI:SS'
minus select * from emp
);
081020 oracle magazine |
2008-10-20
12:20:51
|
|
|
|
-- flashback 10분전 data 검색
select count(1) from emp
as of timestamp (sysdate-10/1440)
where EMPNO=7934;
-- 임시 테이블 생성
create table r_emp tablespace users
as select * from emp where 1=2;
-- 10분전 data recovery
insert into r_emp select * from emp
as of timestamp (sysdate-10/1440)
where EMPNO=7934;
--
INSERT INTO emp
(SELECT * FROM emp
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
where EMPNO=7934;
)
MINUS SELECT * FROM emp) where EMPNO=7934; ; |
2008-11-20
17:41:16
|
|
|
|
pkg 확인
SELECT * FROM ALL_SOURCE
AS OF timestamp(systimestamp - interval '30' minute)
WHERE NAME = 'PKG_NAME'
ORDER BY LINE; |
2019-05-02
16:47:58
|
|
Copyright 1999-2024 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
|
|
|
11/21