闪回总结
================
从9i开始Oracle提供了一项新的避免数据丢失的技术，闪回。在Oracle10g中，闪回的功能大大加强
================
一、9i中的闪回
从9i开始支持 as of 的查询
不需要在调用
dbms_flashback包就可以闪回
select count(*) from a as of timestamp to_timestamp('20090327155148','yyyymmddhh24miss');
还可以基于scn查询
1、基于时间的闪回
dbms_flashback是oracle提供的一个标准数据包，在sql*plus下输入
SQL>desc dbms_flashback;
后会得到该包内容的描述，
PROCEDURE DISABLE
PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
QUERY_SCN NUMBER IN
PROCEDURE ENABLE_AT_TIME
参数名称 类型 输入/输出默认值？
------------------------------ ----------------------- ------ --------
QUERY_TIME TIMESTAMP IN
FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER
在使用该包的时候只需要在sql*plus下输入以下命令：
SQL>execute dbms_flashback.enable_at_time('想要回闪到的具体时间')；
就可以实现数据库的回闪。
闪回得到的结果并不是把被删除的数据直接找了回来，而是在回滚段中找到了被删除表的一个镜相，所以表还是被删除了，但因为镜相已经找到，可以利用他来进行数据恢复。
例：
SQL> create table an as select table_name from all_tables;
SQL> select count(*) from an;
COUNT(*)
----------
835
SQL> delete from an;
已删除835行。
SQL> commit;
提交完成。
SQL> exec dbms_flashback.enable_at_time(to_date('20080110131400','yyyymmddhh24miss'))
PL/SQL 过程已成功完成。
SQL> select count(*) from an;
COUNT(*)
----------
835
此时丢失的数据已经找回，但找到的只是丢失数据的镜像，实际在表中还是没有数据，需要将镜像中的数据再导入到表中。
在闪回状态开启的情况下不能进行数据操作，所以找回数据的工作要用块来实现
具体内容如下：
DECLARE
t_table_name VARCHAR2(30);
CURSOR c_table_name IS SELECT * FROM an;
BEGIN
OPEN c_table_name;
dbms_flashback.disable;
LOOP
FETCH c_table_name INTO t_table_name;
EXIT WHEN c_table_name%NOTFOUND;
insert into an values (t_table_name);
commit;
END LOOP;
CLOSE c_table_name;
END;
/
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
进一步确认
QL> select count(*) from an;
COUNT(*)
----------
835
——————————————————————————————————————————————————————————————————
2、基于SCN的闪回
a、确认数据库的SCN变化
SQL> col fscn for 999999999999999999999999999999
SQL> col nscn for 999999999999999999999999999999
SQL> select name,first_change# fscn,next_change# nscn,first_time from v$archived_log;
NAME FSCN NSCN FIRST_TIME
------------------------------- ------------------------------- ----------
D:\ORACLE\ORA92\RDBMS\ARC01241.001 2414844 2430503 07-1月 -08
D:\ORACLE\ORA92\RDBMS\ARC01242.001 2430503 2442352 07-1月 -08
D:\ORACLE\ORA92\RDBMS\ARC01243.001 2442352 2454621 10-1月 -08
D:\ORACLE\ORA92\RDBMS\ARC01244.001 2454621 2470521 10-1月 -08
D:\ORACLE\ORA92\RDBMS\ARC01245.001 2470521 2485502 10-1月 -08
获取当前的SCN
SQL> select dbms_flashback.get_system_change_number fscn from dual;
FSCN
-------------------------------
2490359
2、连接数据库
conn an/an as sysdba
SQL> create table an_temp as select * from an where 1=2;
表已创建。
SQL> delete from an;
已删除835行。
SQL> commit;
提交完成。
SQL> alter system checkpoint;
系统已更改。
SQL> select count(*) from an;
COUNT(*)
----------
0
SQL> select count(*) from an as of scn 2490594;
COUNT(*)
----------
835
SQL> insert into an_temp select * from an as of scn 2490594;
已创建835行。
SQL> commit;
提交完成。
SQL> insert into an select * from an_temp;
已创建835行。
SQL> commit;
提交完成。
如果不能确定准确的SCN，可以选择合适的SCN向前恢复并多尝试几次，获得最佳的值。
==================================================================
二、10g的闪回
与Oracle9i相比，Oracle10g的闪回功能得到了大大的增强，从简单的闪回查询增加到了多种形式，分别是
1、flashback database
2、flashback table
3、flashback drop
4、flashback version query
5、flashback transaction query
关于闪回的权限
普通用户：
闪回查询
闪回版本查询、
闪回事务查询
DBA
闪回数据库
闪回删除（owner也可以）
1、flashback database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBA
-------------------- --------------
400033 18-8月 -10
SQL> alter session set nls_date_format='yyyymmddhh24miss';
Session altered.
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBA
-------------------- --------------
400033 20100818115118
SQL> select sysdate from dual;
SYSDATE
--------------
20100818120915
SQL> select table_name,tablespace_name from dba_tables where wner='AN';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ATEST ATEST
SQL> DROP TABLE AN.ATEST;
Table dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBA RETENTION_TARGET FLASHBACK_SIZE
-------------------- -------------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
400033 18-8月 -10 1440 8192000
0
SQL> desc an.atest
ERROR:
ORA-04043: object an.atest does not exist
SQL> flashback database to timestamp to_timestamp('20100818120915','yyyymmddhh24miss');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> desc an.atest
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
resetlogs打开数据库后不能再flashback回之前。
——————————————————————————————————————————————————————————————————
2、flashback table
基于时间的恢复（表row movement开启）
-----------------
准备数据
SQL> create table test as select table_name from all_tables;
表已创建。
SQL> insert into test select * from test;
已创建1569行。
SQL> /
已创建3138行。
SQL> /
已创建6276行。
SQL> /
已创建12552行。
SQL> /
已创建25104行。
SQL> /
已创建50208行。
SQL> commit;
提交完成。
-----------------
确定时间
SQL> set time on
16:20:53 SQL> select count(*) from test;
COUNT(*)
----------
100416
----------------
修改表状态
16:21:39 SQL> alter table test enable row movement;
表已更改。
----------------
删除数据
16:21:58 SQL> delete from test where rownum<10001;
已删除10000行。
16:26:21 SQL> commit;
提交完成。
-----------------
恢复数据
16:26:24 SQL> flashback table test to timestamp to_timestamp('20080107162100','yyyymmddhh24miss');
闪回完成。
16:28:16 SQL> select count(*) from test;
COUNT(*)
----------
100416
----------------------------------------
基于时间的恢复（表row movement没有开启）
按照9i的方式运行行
exec dbms_flashback.enable_at_time('想要闪回的时间')
再通过块找回数据就可以
——————————————————————————————————————————————————————————————————
如果被purge命令删除将不能被闪回
3、flashback drop
15分钟前表FCT_REVENUE_WTAB被意外删除
登陆表的所有者用户
SQL> select * from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$Qye6B/O3N4PgQAB/AQAh3Q==$0 FCT_REVENUE_WTAB DROP TABLE BIAS 2007-12-26:14:27:05 2008-01-07:17:13:53 3942624527 YES YES 539376 539376 539376 1152
BIN$Qym54WGGFmTgQAB/AQAz9w==$0 FCT_REVENUE_WTAB DROP TABLE BIAS 2008-01-07:17:16:05 2008-01-07:17:53:18 3942705613 YES YES 542243 542243 542243 8
已选择77行。
SQL> spool off
找到对表进行的DDL操作
BIN$Qye6B/O3N4PgQAB/AQAh3Q==$0 FCT_REVENUE_WTAB DROP TABLE BIAS 2007-12-26:14:27:05 2008-01-07:17:13:53 3942624527 YES YES 539376 539376 539376 1152
BIN$Qym54WGGFmTgQAB/AQAz9w==$0 FCT_REVENUE_WTAB DROP TABLE BIAS 2008-01-07:17:16:05 2008-01-07:17:53:18 3942705613 YES YES 542243 542243 542243 8
在服务器端操作
SQL> conn etlop/e1t2l3o4p
Connected.
SQL> flashback table FCT_REVENUE_WTAB to before drop;
Flashback complete.
SQL> select count(*) from FCT_REVENUE_WTAB;
COUNT(*)
----------
0
这个不是需要的表
SQL> flashback table FCT_REVENUE_WTAB to before drop rename to FCT_REVENUE_WTAB_old
2 ;
Flashback complete.
SQL> select count(*) from FCT_REVENUE_WTAB_old
2 ;
COUNT(*)
----------
131183
找回丢失的数据
——————————————————————————————————————————————————————————————————
4、flashback version query
a、准备数据
create table test1 as select 1 a, 2 b from dual;
Table created
SQL> insert into test1 values(2,2);
1 row inserted
SQL> insert into test1 values(3,3);
1 row inserted
SQL> insert into test1 values(4,4);
1 row inserted
SQL> insert into test1 values(5,5);
1 row inserted
SQL> update test1 set a = 55 where a = 5;
1 row updated
SQL> delete from test1 where a = 4;
1 row deleted
SQL> commit;
Commit complete
-------------------------------------------
b、闪回查询
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, a, b from test1 versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V A B
--------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------- - ---------- ----------
1 2
16-1月 -08 04.07.56 下午 0500180075000000 I 5 5
16-1月 -08 04.07.56 下午 0500180075000000 I 3 3
16-1月 -08 04.06.50 下午 0400220056000000 I 4 4
16-1月 -08 04.04.05 下午 0200140076000000 I 2 2
-------------------------------------------
3、根据xid查询数据的改变情况
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
16:48:34 SQL> select * from flashback_transaction_query where xid ='0200140076000000';
XID START_SCN START_TIMESTAM COMMIT_SCN COMMIT_TIMESTA LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL
---------------- ---------- -------------- ---------- -------------- ------------------------------ ------------ -------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0200140076000000 232743 16-1月 -08 232751 16-1月 -08 AN 1 DELETE PLAN_TABLE$ SYS AAQAcJAABAAAAcKAAA /* No SQL_UNDO for temporary tables */
0200140076000000 232743 16-1月 -08 232751 16-1月 -08 AN 2 INSERT PLAN_TABLE$ SYS AAQAcJAABAAAAcKAAA delete from "SYS"."PLAN_TABLE$" where ROWID = 'AAQAcJAABAAAAcKAAA';
0200140076000000 232743 16-1月 -08 232751 16-1月 -08 AN 3 INSERT TEST1 AN AAACgHAAEAAAAAeAAA delete from "AN"."TEST1" where ROWID = 'AAACgHAAEAAAAAeAAA';
0200140076000000 232743 16-1月 -08 232751 16-1月 -08 AN 4 BEGIN
---------------------------------------------------------
4、数据回复
通过flashback_transaction_query的查询，对数据进行的操作都已经知道，这时可以通过系统生成的redo的sql来恢复数据，也可以通过PL/SQL块来进行回复。
SQL> select * from test1 versions between timestamp minvalue and maxvalue;
SQL> select a,b,versions_xid from test1 versions between timestamp minvalue and maxvalue;
SQL> select a,b,versions_xid, versions_operation from test1 versions between timestamp minvalue and maxvalue;
SQL> select a,b,versions_xid, versions_operation, versions_starttime, versions_endtime from test1 versions between timestamp minvalue and maxvalue;
SQL> select a,b,versions_xid, versions_operation, versions_starttime, versions_endtime from test1 versions between timestamp minvalue and maxvalue;
SQL> select undo_sql from flashback_transaction_query where xid = '0200140076000000';
SQL> select undo_sql from flashback_transaction_query where xid = '0800240011020000';
UNDO_SQL
------------------------------------------------------------------/* No SQL_UNDO for temporary tables */
delete from "SYS"."PLAN_TABLE$" where ROWID = 'AAQAcJAABAAAAcKAAA';
delete from "AN"."TEST1" where ROWID = 'AAACgHAAEAAAAAeAAA';
17:18:09 SQL> select undo_sql from flashback_transaction_query where xid ='0400220056000000';
UNDO_SQL
--------------
delete from "AN"."TEST1" where ROWID = 'AAACgHAAEAAAAAeAAB';
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13177610/viewspace-671303/ ，如需转载，请注明出处，否则将追究法律责任。
