DB스토리

--Flashback mode 로 전환 하는 절차 ---- OMF 모드에서.

--1. 모드 확인

 S SYS> select log_mode, flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

--2. mount

 S SYS> shutdown immediate

--3 mount 모드로

 S SYS> startup mount

 S SYS> alter database flashback on;

 Database altered.

--4. open

 S SYS> alter database open;

 Database altered.

--5 . 동작 확인

 S SYS> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3G
S SYS> !
[oracle@orcl ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/flashback/
[oracle@orcl flashback]$ ll
total 8024
-rw-r-----  1 oracle oinstall 8200192 Jun 15 16:05 o1_mf_53cwmp8p_.flb

 Flashback table 사용 예

S SCOTT> update emp set ename='babo';

13 rows updated.

S SCOTT> commit;

Commit complete.
S SCOTT> select ename,sal from emp;

ENAME             SAL
---------- ----------
babo              800
babo             1600
babo             1250
babo             2975
babo             1250
babo             2850
babo             2450
babo             5000
babo             1500
babo              950
babo             3000
babo             1300
babo

13 rows selected.

S SCOTT> flashback table emp to timestamp systimestamp-1/24/6;
flashback table emp to timestamp systimestamp-1/24/6
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

S SCOTT> alter table emp enable row movement;

Table altered.

S SCOTT> flashback table emp to timestamp systimestamp-1/24/6;

Flashback complete.

S SCOTT> select ename,sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
KING             5000
TURNER           1500
JAMES             950
FORD             3000
MILLER           1300
aa

 rock 에서는 안됨

S HR> update scott.emp set sal=9000 where ename='BLAKE';

1 row updated.

S SCOTT> flashback table emp to timestamp systimestamp-1/24/6;
flashback table emp to timestamp systimestamp-1/24/6
                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

rock 찾아서 죽이고 하면 됨

v$lock

v$session

v$access  <--- 접근 중인 놈들 보는거. 

Flashback Query   => AS OF

  select * from dept as of timestamp to_timestamp('090616 01.15.20 PM', 'yymmdd hh.mi.ss AM');

 flashback table dept to timestamp sysdate-1;

                =

update dept d set loc=(select loc from dept as of timestamp to_timestamp('090616 01.15.20 PM', 'yymmdd hh.mi.ss AM' whrer d.loc=loc);

Flashback version query

col versions_starttime form a22
col versions_endtime form a22
select versions_starttime,versions_endtime,ename,sal from emp
versions between timestamp minvalue and maxvalue
where not(versions_endtime is null and versions_starttime is null)
order by deptno,versions_endtime
/

Flashback transaciton query

col versions_starttime form a22
col versions_endtime form a22
select versions_starttime,versions_endtime,versions_xid,ename,sal from emp
versions between timestamp minvalue and maxvalue
where not(versions_endtime is null and versions_starttime is null)
order by deptno,versions_endtime
/

 VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     ENAME             SAL
---------------------- ---------------------- ---------------- ---------- ----------
                       16-JUN-09 01.22.11 PM                   KING             5000
16-JUN-09 01.22.11 PM  16-JUN-09 01.22.32 PM  03001500674F0000 KING             6000
16-JUN-09 01.22.32 PM                         07001300554F0000 KING             8000

6000과 8000 사이에 어떤 질의가 날라간지 알고 싶으면. 

 S SYS> select undo_sql from flashback_transaction_query
  2  where xid='07001300554F0000';

UNDO_SQL
--------------------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '7000' where ROWID = 'AAAM74AAEAAAAHfAAH';
update "SCOTT"."EMP" set "SAL" = '6000' where ROWID = 'AAAM74AAEAAAAHfAAH';