티스토리 뷰
--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';
'DataBase > Oracle' 카테고리의 다른 글
Configuring Recovery Manager (0) | 2010.05.18 |
---|---|
Moving Data (0) | 2010.05.18 |
Performing Database Recovery (0) | 2010.05.18 |
Performing Database Backups (0) | 2010.05.18 |
Backup and Recovery Concepts (0) | 2010.05.18 |
- Total
- Today
- Yesterday
- iOS5
- 출시일
- linux
- 아이폰
- 리눅스
- mysql
- 투싼
- Flashback
- 갤럭시S
- 독도
- recovery
- SQLPlus
- PostgreSQL
- index
- 한글
- MS-SQL
- postgresql.conf
- oracle
- PL/SQL
- 인덱스
- Backup
- tablespace
- 윈도우
- parameter
- 오라클
- Managing Schema Objects
- iPhone
- 테이블정보
- 아이폰4
- 컬럼정보
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |