总结oracle flashback 特性
Oracle 9i只能够使用flashback query。10G依然可以使用闪回查询，与9i没有变化。但flashback功能大幅强化，新增很多特性；如闪回表，闪回数据库等等。11g又增加了flashback archive ,可以提供不依靠undo的闪回功能。通过实验完成闪回功能测试，仅供参考。
10g默认启用除了闪回数据库之外的所有闪回功能，如果想打开Flashback database,要使用闪回区。Flashback database使用flashbacklog & onlinelog/archivelog 实现数据库闪回，flashbackarchive通过永久tablespace实现闪回。其他都是通过undo段实现闪回。
SQL> grant flashback any table to scott;
Grant succeeded
SQL> grant execute on dbms_flashback to scott;
Grant succeeded
1 ONE - Flashback query
1.1 基于scn的查询
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
479197
SQL> select sal from emp where ename='SCOTT';
SAL
---------
3000.00
SQL> update emp set sal = 5000 where ename='SCOTT';
1 row updated
SQL> commit;
Commit complete
SQL> select sal from emp where ename='SCOTT';
SAL
---------
5000.00
SQL> select sal from emp as of scn 479197 where ename='SCOTT';
SAL
---------
3000.00
1.2 基于时间的查询
SQL> select sal from emp as of timestamp to_timestamp('20111105 20:30:00','yyyymmdd hh24:mi:ss') where ename='SCOTT';
SAL
---------
3000.00
1.3 可以创建闪回表
SQL> create table f_emp as select * from emp as of scn 479197;
Table created
说明:
1.要求，使用自动管理的undo表空间，但不要求DB处于归档模式；
2.使用当前数据字典，所以如果表结构发生变化，无法闪回；
3.drop truncate等ddl操作无法闪回;
4.受到参数undo_retention的限制，之前的数据，不能保证闪回成功；
2 TWO - Flashback drop
必须打开回收站
10.1版本中，修改隐藏参数 _recyclebin
alter system set "_recyclebin" = false;
10.2版本中，
alter system set recyclebin = off;
SQL> conn sys/czjie@ORCL as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
注意：
alter system set recyclebin = off/on DEFERRED;
2.1 flashback drop table
SQL> conn scott/tiger@ORCL
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> drop table emp;
Table dropped
SQL> select original_name,object_name,type,droptime from recyclebin;
ORIGINAL_NAME OBJECT_NAME TYPE DROPTIME
------------------------ -------------------------------------- ------------------------- -------------------
EMP BIN$sP1RQPcQ6frgQAB/AQALZQ==$0 TABLE 2011-11-05:20:54:08
SQL> flashback table emp to before drop;
Done
2.2 如果有多张表重名，先恢复后删除的
SQL> create table emp2 as select * from emp where rownum<=3;
Table created
SQL> drop table emp2;
Table dropped
SQL> create table emp2 as select * from emp where rownum<=2;
Table created
SQL> drop table emp2;
Table dropped
SQL> select original_name,object_name,type,droptime from recyclebin;
ORIGINAL_NAME OBJECT_NAME TYPE DROPTIME
-------------------------------- ------------------------------ ------------------------- -------------------
EMP2 BIN$sP1RQPcR6frgQAB/AQALZQ==$0 TABLE 2011-11-05:20:58:49
EMP2 BIN$sP1RQPcS6frgQAB/AQALZQ==$0 TABLE 2011-11-05:20:59:14
SQL> flashback table emp2 to before drop;
Done
SQL> select original_name,object_name,type,droptime from recyclebin;
ORIGINAL_NAME OBJECT_NAME TYPE DROPTIME
-------------------------------- ------------------------------ ------------------------- -------------------
EMP2 BIN$sP1RQPcR6frgQAB/AQALZQ==$0 TABLE 2011-11-05:20:58:49
SQL> select count(*) from emp2;
COUNT(*)
----------
2
--可以看到最晚时间为20:59:14的且只有3行表数据的emp2给恢复了;
下面再次恢复20:58:49删除的表emp2:
SQL> flashback table emp2 to before drop;
flashback table emp2 to before drop
ORA-38312: original name is used by an existing object
注意：错误提示原始名称已被现有对象使用
SQL> flashback table emp2 to before drop rename to emp3;
Done
SQL> select count(*) from emp3;
COUNT(*)
----------
3
2.3 索引会同时恢复，但是名字会更改；
SQL> create index test on emp2(ename);
Index created
SQL> drop table emp2;
Table dropped。
SQL> flashback table emp2 to before drop;
Done
SQL> select table_name,index_name,status from user_indexes where table_name='EMP2';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
EMP2 BIN$sP1RQPcT6frgQAB/AQALZQ==$0 VALID
SQL> alter index "BIN$sP1RQPcT6frgQAB/AQALZQ==$0" rename to test;
Index altered
SQL> select table_name,index_name,status from user_indexes where table_name='EMP2';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
EMP2 TEST VALID
2.4 扩展
1.purge recyclebin; 清空回收站
2、drop table tableName purge; 删除表而不进回收站
3.drop tablespace including contents and datafiles； 数据不入回收站
4.drop user cascade； 数据不入回收站
3 Three - flashback table
3.1 Flashback dml
SQL> create table emp2 as select * from emp;
Table created
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
482035
SQL> update emp2 set sal=1 where ename = 'SCOTT';
1 row updated
SQL> commit;
Commit complete
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
482089
SQL> update emp2 set sal=2 where ename = 'SCOTT';
1 row updated
SQL> commit;
Commit complete
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
482117
SQL> update emp2 set sal=3 where ename = 'SCOTT';
1 row updated
SQL> commit;
Commit complete
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
482128
SQL> update emp2 set sal=4 where ename = 'SCOTT';
1 row updated
SQL> commit;
Commit complete
SQL> flashback table emp2 to scn 482035;
flashback table emp2 to scn 482035
ORA-08189: cannot flashback the table because row movement is not enabled
ORA-08189: 因为未启用行移动功能, 不能闪回表
SQL> alter table emp2 enable row movement;
Table altered
SQL> flashback table emp2 to scn 482117;
Done
SQL> select sal from emp2 where ename='SCOTT';
SAL
---------
2.00
SQL> select sal from emp2 where ename='SCOTT';
SAL
---------
2.00
SQL> flashback table emp2 to scn 482089;
Done
SQL> select sal from emp2 where ename='SCOTT';
SAL
---------
1.00
SQL> flashback table emp2 to scn 482128;
Done
SQL> select sal from emp2 where ename='SCOTT';
SAL
---------
3.00
也可以根据时间闪回
-- 闪回到5分钟之前
SQL> flashback table emp2 to timestamp systimestamp - interval '5' minute;
SQL> flashback table emp2 to timestamp to_timestamp('20111105 21:35:00','yyyymmdd hh24:mi:ss');
2.Ddl 无法闪回
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
482797
SQL> truncate table emp2;
Table truncated
SQL> flashback table emp2 to scn 482797;
ORA-01466: unable to read data - table definition has changed
ORA-01466: 无法读取数据 - 表定义已更改
3.2 说明
1.DDL操作无法闪回
2.必须enable row movement;
3.索引同时闪回
4.如果表上有触发器，触发器实效。可以使用enable triggers关键字使flashback同时启动trigger；
4 Four - Flashback version
通过flashback version 可以查到表上发生了哪些dml操作
SQL> create table test(name date);
Table created
SQL> truncate table test;
Table truncated
SQL> insert into test values(sysdate);
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> commit;
Commit complete
SQL> insert into test values(sysdate);
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> commit;
Commit complete
SQL> insert into test values(sysdate);
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> commit;
Commit complete
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered
伪列(pseudo_columns)包括以下：
VERSIONS_STARTSCN：创建行时具有的SCN
VERSIONS_STARTTIME：创建行时此版本的时间标记
VERSIONS_ENDSCN：此行的最后一次操作的SCN
VERSIONS_ENDTIME：此行的最后一次操作的时间
VERSIONS_XID：创建此版本的事务ID
VERSIONS_OPERATION：此事务完成的操作：I：INSERT、U：UPDATE、D：DELETE
SQL> select versions_starttime,versions_startscn,versions_xid,versions_operation from test versions between timestamp minvalue and maxvalue order by 1;
VERSIONS_STARTTIME VERSIONS_STARTSCN VERSIONS_XID VERSIONS_OPERATION
------------------------------------------------- ----------------- ---------------- ------------------
05-11？ -11 09.59.59 ？？ 483569 01001C00C4000000 I
05-11？ -11 09.59.59 ？？ 483569 01001C00C4000000 I
05-11？ -11 09.59.59 ？？ 483569 01001C00C4000000 I
05-11？ -11 10.01.04 ？？ 483711 03001C0002010000 I
05-11？ -11 10.01.04 ？？ 483711 03001C0002010000 I
05-11？ -11 10.01.04 ？？ 483711 03001C0002010000 I
05-11？ -11 10.01.37 ？？ 483723 04002B00C8000000 I
05-11？ -11 10.01.37 ？？ 483723 04002B00C8000000 I
05-11？ -11 10.01.37 ？？ 483723 04002B00C8000000 I
9 rows selected
SQL> truncate table test;
Table truncated
SQL> select versions_starttime,versions_startscn,versions_xid,versions_operation from test versions between timestamp minvalue and maxvalue order by 1;
VERSIONS_STARTTIME VERSIONS_STARTSCN VERSIONS_XID VERSIONS_OPERATION
------------------------------------------------- ----------------- ---------------- ------------------
说明：
1.只能分析dml操作
2.如果发生了ddl操作，之前dml操作，无法查询
3.查询也可以指定scn 或者timestamp
versions between scn xxx and xxx;
versions between timestamp to_date(xxxxxxx) and to_date(xxxxxxx);
Five - Flashback transaction query
通过flashback version 可以找到事务id，而flashback transaction则可以找到事物的undo sql 通过undosql可以会滚已经提交的事物；
SQL> delete from emp where ename = 'SCOTT';
1 row deleted
SQL> commit;
Commit complete
SQL> delete from emp where deptno=10;
3 rows deleted
SQL> commit;
Commit complete
SQL> select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') versions_starttime,versions_startscn,versions_xid,versions_operation from emp versions between timestamp minvalue and maxvalue order by 1;
VERSIONS_STARTTIME VERSIONS_STARTSCN VERSIONS_XID VERSIONS_OPERATION
------------------- ----------------- ---------------- ------------------
2011-11-05 22:11:30 484033 0900030023010000 D
2011-11-05 22:12:18 484056 0A001C00C7000000 D
2011-11-05 22:12:18 484056 0A001C00C7000000 D
2011-11-05 22:12:18 484056 0A001C00C7000000 D
SQL> desc flashback_transaction_query;
Name Type Nullable Default Comments
---------------- -------------- -------- ------- -----------------------------------------
XID RAW(8) Y Transaction identifier
START_SCN NUMBER Y Transaction start SCN
START_TIMESTAMP DATE Y Transaction start timestamp
COMMIT_SCN NUMBER Y Transaction commit SCN
COMMIT_TIMESTAMP DATE Y Transaction commit timestamp
LOGON_USER VARCHAR2(30) Y Logon user for transaction
UNDO_CHANGE# NUMBER Y 1-based undo change number
OPERATION VARCHAR2(32) Y forward operation for this undo
TABLE_NAME VARCHAR2(256) Y table name to which this undo applies
TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies
ROW_ID VARCHAR2(19) Y rowid to which this undo applies
UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo
SQL> select undo_sql from flashback_transaction_query where xid=hextoraw('0900030023010000');
ORA-01031: insufficient privileges
ORA-01031: 权限不足
SQL> conn sys/czjie@orcl as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as sys
SQL> grant select any transaction to scott;
Grant succeeded
SQL> conn scott/tiger@orcl
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> select undo_sql from flashback_transaction_query where xid=hextoraw('0900030023010000');
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7788','SCOTT','ANALYST','7566',TO_DATE('19-4？ -87', 'DD-MON-RR'),'5000',NULL,'20');
SQL> select undo_sql from flashback_transaction_query where xid=hextoraw('0A001C00C7000000');
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7934','MILLER','CLERK','7782',TO_DATE('23-1？ -82', 'DD-MON-RR'),'1300',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7839','KING','PRESIDENT',NULL,TO_DATE('17-11？ -81', 'DD-MON-RR'),'5000',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-6？ -81', 'DD-MON-RR'),'2450',NULL,'10');
SQL> insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7788','SCOTT','ANALYST','7566',TO_DATE('19-4？ -87', 'DD-MON-RR'),'5000',NULL,'20');
/
1 row inserted
SQL> select * from emp where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 5000.00 20
SQL> insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7934','MILLER','CLERK','7782',TO_DATE('23-1？ -82', 'DD-MON-RR'),'1300',NULL,'10');
1 row inserted
SQL> insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7839','KING','PRESIDENT',NULL,TO_DATE('17-11？ -81', 'DD-MON-RR'),'5000',NULL,'10');
1 row inserted
SQL> insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-6？ -81', 'DD-MON-RR'),'2450',NULL,'10');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from emp where deptno = 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
7839 KING PRESIDENT 1981/11/17 5000.00 10
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
至此，已经提交的事物成功会滚；
6 Six - Flashback Database
flashback database 在sqlplus or rman中都可以使用，命令略有不同
RMAN
RMAN> FLASHBACK DATABASE TO TIME =
2> "TO_DATE('2004-05-27 16:00:00','YYYY-MM-DD HH24:MI:SS')";
RMAN> FLASHBACK DATABASE TO SCN=23565;
RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;
SQLPLUS
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-1/24);
SQL> FLASHBACK DATABASE TO SCN 53943;
SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;
6.1 打开flashback
SQL> select name,flashback_on,log_mode from v$database;
NAME FLASHBACK_ON LOG_MODE
--------- ------------------ ------------
ORCL NO NOARCHIVELOG
flashback_on：是否开启闪回（否）；
log_mode：日志模式，是否归档（非归档模式）；
6.1.1 操作步骤：
1)先关闭数据库：shutdown immediate
2)启动到mount状态：startup mount
3)开启归档功能：alter database archivelog;
4)打开flashback：alter database flashback on;
5)打开数据库：alter database open;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 96470996 bytes
Database Buffers 117440512 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alert system set db_recovery_file_dest_size=10g;
#mkdir /opt/ora10g/product/10.2.0/recover
SQL> alter system set db_recovery_file_dest='/opt/ora10g/product/10.2.0/recover';
System altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> select name,flashback_on,log_mode from v$database;
NAME FLASHBACK_ON LOG_MODE
--------- ------------------ ------------
ORCL YES ARCHIVELOG
SQL> alter database open;
Database altered.
拓展：
开启闪回flashback功能（归档下才可开启数据库闪回）
查看flashback是否开启：select name,flashback_on from v$database;
查看闪回恢复区及大小是否设置：show parameter db_recovery；
先设置闪回恢复区的大小：alter system set db_recovery_file_dest_size='2G';（这里系统默认也是2G，也未改）
再设置闪回恢复区路径：alter system set db_recovery_file_dest='/u01/oracle/db_recovery_file_dest';(这步我并未使用)
设置数据库回退的时间，默认1440分钟为一天时间：alter system set db_flashback_retention_target = 1440；
如果不是在归档模式下试图打开flashback，则会出现下面情况：
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
ORA-38707:
Media recovery is not enabled.
Cause: An ALTER DATABASE FLASHBACK ON command failed because media recovery was not enabled.
Action: Turn on media recovery with an ALTER DATABASE ARCHIVELOG command and then retry the command.
6.2 比较重要的数据字典
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG .38 0 1
6 rows selected.
SQL> select OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME,RETENTION_TARGET from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET
-------------------- --------- ----------------
485108 05-NOV-11 1440
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
485904
6.3 测试删除用户的flashback
SQL> drop user scott cascade;
drop user scott cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL> drop user scott cascade;
User dropped.
SQL> startup mount force; -- 相当于一次断电的数据库重启
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 100665300 bytes
Database Buffers 113246208 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> flashback database to scn 485904;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tiger
Connected.
-- flashback database to timestamy(to_date('xxxxxxxxxx','xxxxxxxxxxx'));
-- flashback database to timestamp(sysdate-1/24);
6.4 Incarnation 问题
同不完全恢复一样，Flashback database也有incarnation的问题需要注意：如果OLDEST_FLASHBACK_SCN小于v$database.resetlog_scn。那么就可以闪会到上一个incarnation,可以指定incarnation,或者使用下面命令到上一个incarnation。
FLASHBACK DATABASE TO BEFORE RESETLOGS;
6.4.1 准备工作
SQL> select OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME,RETENTION_TARGET from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET
-------------------- --------- ----------------
485108 05-NOV-11 1440
SQL> select incarnation# from v$database_incarnation where status='CURRENT';
INCARNATION#
------------
3
SQL> begin
for i in 1 .. 10000 loop
insert into scott.t values(dbms_flashback.get_system_change_number);
commit;
end loop;
end;
/
PL/SQL
过程已成功完成。
SQL> select min(tscn),max(tscn) from scott.t;
MIN(TSCN) MAX(TSCN)
---------- ----------
1130562 1149237
6.4.2 恢复到某一点，并reseglogs打开数据库
SQL> startup mount force;
SQL> flashback database to scn 1140000;
SQL> alter database open resetlogs;
SQL> select min(tscn),max(tscn) from scott.t;
MIN(TSCN) MAX(TSCN)
---------- ----------
1130562 1139997
6.4.3尝试恢复到上次resetlogs点之前
SQL> startup mount force;
SQL> flashback database to scn 1135000;
SQL> alter database open resetlogs;
SQL> select min(tscn),max(tscn) from scott.t;
MIN(TSCN) MAX(TSCN)
---------- ----------
1130562 1134999
-- 恢复成功
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1166421
6.4.4 我们想恢复t表里面所有数据，所以决定恢复到scn 1149239
SQL> startup mount force
SQL> flashback database to scn 1149239;
SQL> alter database open resetlogs;
SQL> select min(tscn),max(tscn) from scott.t;
MIN(TSCN) MAX(TSCN)
---------- ----------
1130562 1134999
-- 数据没有恢复
6.4.5 reset database 成功恢复数据
SQL> startup mount force;
RMAN>reset database to incarnation 19;
SQL> flashback database to scn 1149239;
SQL> alter database open resetlogs;
SQL> select min(tscn),max(tscn) from scott.t;
MIN(TSCN) MAX(TSCN)
---------- ----------
1130562 1149237
-- 数据成功恢复
6.5 闪回与归档
/*
注意，闪回不仅需要闪回日志，并且需要归档日志的存在。如下案例
1.--查找能够闪回的最早的scn
SQL> select OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME,RETENTION_TARGET from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET
-------------------- --------- ----------------
485108 05-NOV-11 1440
2.--切换并且归档日志
alter system archive log current;
3.-- 删除所有归档日志
4.--再次查询，没有变化
SQL> select OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME,RETENTION_TARGET
2 from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBA RETENTION_TARGET
-------------------- -------------- ----------------
301202568 01-7月 -09 1440
5.--尝试闪回
startup mount force;
flashback database to scn 301202568 ;
flashback database to scn 301202568
第 1 行出现错误:
ORA-38754: FLASHBACK DATABASE 没有启动; 所需的重做日志不可用
ORA-38761: 无法访问重做日志序列 84 (在线程 1, 原型 2 中)
6.6 Flashback database read only
使用flashdatabase 如果以resetlogs模式打开数据库，会造成部分数据丢失。
10g支持flashback database后，以readonly模式打开数据库，而不必resetlogs 对于删除表的错误操作，可以使用下面步骤
startup mount force
flashback database to scn 1149239;
alter database open readonly;
exp
startup mount force;
recover database;
alter database open;
imp
7 Seven - flashback archive
-- 11g提供了不再依赖undo的flashback archive
-- 必须建立flashback archive,将闪回信息存放到永久表空间
7.1 准备
首先创建三个测试测试闪回归档的表空间
create tablespace tbs_flash datafile 'E:\oracle\oradata\orcl\flash.dbf' size 200m;
create tablespace tbs_flash2 datafile 'E:\oracle\oradata\orcl\flash2.dbf' size 200m;
create tablespace tbs_flash3 datafile 'E:\oracle\oradata\orcl\flash3.dbf' size 200m;
7.2 创建并管理
flashback archive
-- 必须有一个默认的archive
-- 每个archive 可以对应多个表空间，每个archive 有一个主表空间
-- 不同的archive可以设定不同的保留策略
create flashback archive default flash1 tablespace tbs_flash [quota xxx] retention 1 year;
create flashback archive flash2 tablespace tbs_flash2 [quota xxx] retention 2 day;
-- 将表空间增加到
flashback archive
alter flashback archive flash2 add tablespace tbs_flash3 [quota xxx];
--
将表空间移出
flashback archive
alter flashback archive flash2 remove tablespace tbs_flash2;
ORA-55626:
无法删除闪回归档的主表空间
alter flashback archive flash2 remove tablespace tbs_flash3;
-- 管理quota
alter flashback archive flash1 modify tablespace tbs_flash quota 200m;
-- 管理闪回时间
alter flashback archive flash1 modify retention 1 month;
-- 删除
flashback archive
drop flashback archive flash1;
7.3 创建表并使用
flashback archive
create table emp2 as select * from emp;
alter table emp2 flashback archive;
-- 需要 flashback archive administer 的权限
-- 建立在默认
flashback archive
create table emp3 as select * from emp;
alter table emp3 flashback archive flash2;
-- 指定flashback archive
7.4 闪回归档的管理
--所有dml操作，都会写flashback archivelog
--如果flashback archive用尽，dml操作会收到错误 ORA-55617
--删除某个archive 一天之前的log alter flashback archive flash1 purge before timestamp(systimestamp - interval '1' day);
--删除某scn之前的log
alter flashback archive flash1 purge before scn 900000;
--使用了flashback archive 则对该表的所有ddl操作都不允许
truncate table emp2;
收到错误
ORA-55610
--取消表的flashback archive
alter table emp2 no flashback archive;
7.5 常用数据字典
dba/all/user_flashback_archive
dba/all/user_flashback_archive_ts
dba/all/user_flashback_archive_tables
desc dba_flashback_archive;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
FLASHBACK_ARCHIVE# NOT NULL NUMBER
RETENTION_IN_DAYS NOT NULL NUMBER
CREATE_TIME TIMESTAMP(9)
LAST_PURGE_TIME TIMESTAMP(9)
STATUS VARCHAR2(7)
desc dba_flashback_archive_ts;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
FLASHBACK_ARCHIVE# NOT NULL NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
QUOTA_IN_MB VARCHAR2(40)
desc dba_flashback_archive_tables;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
OWNER_NAME NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME VARCHAR2(53)
作者：czjie
出处：http://www.cnblogs.com/czjie/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
浙公网安备 33010602011771号