flashback是从oracle9i开始实现的一种恢复功能,完善在10g。10g中利用recyclebin和flashback query功能可以快速的
恢复被误删除的table,也可以flashback database 到某个时间点。
一,flashback query
flashback query是利用oracle undo来获取之前的数据。
1.1创建测试数据
SQL> create table flashback_tab(id number);
表已创建。
SQL> insert into flashback_tab values(1);
已创建 1 行。
SQL> insert into flashback_tab values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from flashback_tab;
ID
----------
1
2
1.2用flashback查询以前的数据
主要是两种方法在from后接as of scn(基于系统改变号)和as of timestamp(基于时间查询)
1.2.1用scn查询恢复
首先获取scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5827914
获取scn可以通过v$database的current_scn,和dbms_flashback.get_system_change_number
(非dba登入用grant execute dbms_flash on user_name授权)
SQL> conn / as sysdba
已连接。
SQL> grant execute on dbms_flashback to admin;
授权成功。
SQL> conn admin/admin
已连接。
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
5828288
SQL> delete from flashback_tab where id=1;
已删除 1 行。
SQL> select * from flashback_tab;
ID
----------
2
SQL> select * from flashback_tab as of scn 5827914;
ID
----------
1
2
1.2.2利用scn恢复数据
SQL> insert into flashback_tab select * from flashback_tab as of scn 5827914 whe
re id=1;
已创建 1 行。
SQL> select * from flashback_tab;
ID
----------
2
1
commit;
1.2.3用dbms_flashback.get_system_change_number查询
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
5831223
SQL> delete from flashback_tab where id=2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> exec dbms_flashback.enable_at_system_change_number(5831223);
PL/SQL 过程已成功完成。
SQL> select * from flashback_tab;
ID
----------
1
2
用时间查询用
exec dbms_flashback.enable_ai_timestamp(to_date('2013-10-27 23:45:21','yyyy-mm-dd hh24:mi:ss'));
1.2.4用游标来恢复数据
首先执行 exec dbms_flashback.disable,否则报错。因为在闪回模式下不能使用dml/ddl
SQL> exec dbms_flashback.disable;
PL/SQL 过程已成功完成。
#脚本
declare
cursor cur_flashback is select * from flashback_tab as of scn 5831223 where id=2;
t_row cur_flashback%rowtype;
begin
open cur_flashback;
loop
fetch cur_flashback into t_row;
exit when cur_flashback%notfound;
insert into flashback_tab values(t_row.id);
end loop;
close cur_flashback;
commit;
end;
SQL> declare
2 cursor cur_flashback is select * from flashback_tab as of scn 5831223 where
id=2;
3 t_row cur_flashback%rowtype;
4 begin
5 open cur_flashback;
6 loop
7 fetch cur_flashback into t_row;
8 exit when cur_flashback%notfound;
9 insert into flashback_tab values(t_row.id);
10 end loop;
11 close cur_flashback;
12 commit;
13 end;
14 /
PL/SQL 过程已成功完成。
1.2.5用version query和flashback_transaction_query来跟踪事务
#version query
versions_startscn 该记录开始的scn
versions_endscn 记录结束的scn
versions_xid 事务id
versions_operation 记录的操作
SQL> select * from flashback_tab;
ID
----------
1
2
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5858617
SQL> delete from flashback_tab where id = 2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5858639
SQL> select id,versions_startscn,versions_endscn,versions_operation,versions_xid
2 from flashback_tab versions between scn minvalue and maxvalue;
ID VERSIONS_STARTSCN VERSIONS_ENDSCN VE VERSIONS_XID
---------- ----------------- --------------- -- ----------------
2 5858636 D 01000B0040090000
1
2 5858636
也可以使用指定scn 5858617 and 5858639或者timestamp to_date() and to_date();
#flashback_transaction_query
如果该用户没有查询该事务的权限,执行
GRANT SELECT ANY TRANSACTION TO USER_NAME;
SQL> desc flashback_transaction_query
名称 是否为空? 类型
----------------------------------------------------- -------- ----------------
--------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
SQL> insert into flashback_tab values(2);
已创建 1 行。
commit;
SQL> select * from flashback_tab;(当然你也可以用as of scn插入数据哈!)
ID
----------
1
2
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5863636
SQL> delete from flashback_tab where id=1;
已删除 1 行。
SQL> commit;
提交完成。
SQL> delete from flashback_tab where id=2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> select * from flashback_tab;
未选定行
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5863693
SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql
2 from flashback_transaction_query q where q.xid in(
3 select versions_xid from flashback_tab versions between scn minvalue and ma
xvalue
4 );
XID COMMIT_SCN COMMIT_TIMESTA OPERATION UNDO_SQL
---------------- ---------- -------------- --------------- ---------------------
---------
05001E00BC0D0000 5863622 28-10月-13 UNKNOWN
05001E00BC0D0000 5863622 28-10月-13 UNKNOWN
05001E00BC0D0000 5863622 28-10月-13 BEGIN
08000900A10C0000 5863649 28-10月-13 UNKNOWN
08000900A10C0000 5863649 28-10月-13 BEGIN
06001000420C0000 5863656 28-10月-13 UNKNOWN
06001000420C0000 5863656 28-10月-13 BEGIN
已选择7行。
1.3用as of timestamp查询和恢复
现在由于人为失误误删除id=22的数据,现在我想查询并恢复它,用as of timestamp可以将表恢复到一个指定的时间
SQL> create table fsb_tab(id number);
表已创建。
SQL> insert into fsb_tab values(11);
已创建 1 行。
SQL> insert into fsb_tab values(22);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from fsb_tab;
ID
----------
11
22
SQL> delete from where id=22;
SQL>COMMIT;
提交完成。
SQL> SELECT * FROM FSB_TAB AS OF TIMESTAMP SYSDATE-3/1440;
ID
----------
11
22
SQL> insert into fsb_tab select * from fsb_tab as of timestamp sysdate-3/1440 wh
ere id=22;
已创建 1 行。
SQL> select * from fsb_tab;
ID
----------
11
22
SQL>commit;
或者用一下方法:
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
27-10月-13 11.45.21.040000 下午 +08:00
SQL> select * from fsb_tab as of timestamp to_timestamp('2013-10-27 23:45:21','y
yyy-mm-dd hh24:mi:ss');
ID
----------
11
22
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
5749421
二,flashback table
2.1恢复删除的table
先恢复之前删除的数据
SQL> insert into flashback_tab select * from flashback_tab as of scn 5863622;
已创建2行。
SQL> select * from flashback_tab;
ID
----------
1
2
SQL> commit;
提交完成。
SQL> select * from flashback_tab;
ID
----------
1
2
drop table flashback_tab;
SQL> desc recyclebin;
名称 是否为空? 类型
----------------------------------------------------- -------- ---------------
--------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
#select object_name,original_name from recyclebin;
SQL> select object_name,original_name from recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ -----------------------
---------------
BIN$btbj8FyzRBmZqpjng0loEA==$0 FLASHBACK_TAB
#flashback table flashback_tab to before drop;
SQL> flashback table flashback_tab to before drop;
闪回完成。
SQL> select * from flashback_tab;
ID
----------
1
2
2.2恢复索引
SQL> create index flashback_idx on flashback_tab(id);
索引已创建。
drop table flashback_tab;
flashback table flashback_tab to before drop;
select index_name from user_indexes where table_name = 'flashback_tab';
SQL> select index_name from user_indexes where table_name = 'FLASHBACK_TAB';
INDEX_NAME
------------------------------------------------------------
BIN$R+ddhmi4QCaAvUMIiE8qYg==$0
alter index "BIN$R+ddhmi4QCaAvUMIiE8qYg==$0" rename to flashback_idx1;
SQL> select index_name from user_indexes where table_name='FLASHBACK_TAB';
INDEX_NAME
------------------------------------------------------------
FLASHBACK_IDX1
如果在恢复flashback_tab之前,又创建了一个同名flashback_tab表时会报错。
解决办法只要重命名你要恢复的flashback_tab。
flashback table FLASHBACK_TAB to before drop rename to flashback_tab_old;
*要允许表能行移动,默认下是disable
可以分别从数据字典user_tables,all_tables,dba_tables中查看row_movement属性
SQL> select row_movement from user_tables where table_name='FLASHBACK_TAB';
ROW_MOVEMENT
----------------
ENABLED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5809527
SQL> delete from flashback_tab where id=1;
已删除 1 行。
SQL>COMMIT;
SQL> flashback table flashback_tab to scn 5809527;
闪回完成。
SQL> select * from flashback_tab;
ID
----------
1
2
2.3关于drop的简单用法
drop table name purge;完全删除
purge table name;
purge tablespace name;
purge recyclebin;清空回收站
三,flashback database
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5825669
SQL> select * from flashback_tab;
ID
----------
1
2
SQL> drop table flashback_tab purge;
表已删除。
SQL> select * from flashback_tab;
select * from flashback_tab
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> conn / as sysdba
已连接。
SQL> flashback database to scn 5825669;
flashback database to scn 5825669
*
第 1 行出现错误:
ORA-38757: 要闪回数据库, 数据库必须已装载但不能打开。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE 例程已经启动。
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 327156176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5804032 bytes
数据库装载完毕。
SQL> conn / as sysdba
已连接。
SQL> flashback database to scn 5825669;
闪回完成。
SQL> ALTER DATABASE OPEN RESETLOGS;
数据库已更改。
闪回完成。
SQL> conn admin/admin
已连接。
SQL> select * from flashback_tab;
ID
----------
1
2
恢复被误删除的table,也可以flashback database 到某个时间点。
一,flashback query
flashback query是利用oracle undo来获取之前的数据。
1.1创建测试数据
SQL> create table flashback_tab(id number);
表已创建。
SQL> insert into flashback_tab values(1);
已创建 1 行。
SQL> insert into flashback_tab values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from flashback_tab;
ID
----------
1
2
1.2用flashback查询以前的数据
主要是两种方法在from后接as of scn(基于系统改变号)和as of timestamp(基于时间查询)
1.2.1用scn查询恢复
首先获取scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5827914
获取scn可以通过v$database的current_scn,和dbms_flashback.get_system_change_number
(非dba登入用grant execute dbms_flash on user_name授权)
SQL> conn / as sysdba
已连接。
SQL> grant execute on dbms_flashback to admin;
授权成功。
SQL> conn admin/admin
已连接。
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
5828288
SQL> delete from flashback_tab where id=1;
已删除 1 行。
SQL> select * from flashback_tab;
ID
----------
2
SQL> select * from flashback_tab as of scn 5827914;
ID
----------
1
2
1.2.2利用scn恢复数据
SQL> insert into flashback_tab select * from flashback_tab as of scn 5827914 whe
re id=1;
已创建 1 行。
SQL> select * from flashback_tab;
ID
----------
2
1
commit;
1.2.3用dbms_flashback.get_system_change_number查询
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
5831223
SQL> delete from flashback_tab where id=2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> exec dbms_flashback.enable_at_system_change_number(5831223);
PL/SQL 过程已成功完成。
SQL> select * from flashback_tab;
ID
----------
1
2
用时间查询用
exec dbms_flashback.enable_ai_timestamp(to_date('2013-10-27 23:45:21','yyyy-mm-dd hh24:mi:ss'));
1.2.4用游标来恢复数据
首先执行 exec dbms_flashback.disable,否则报错。因为在闪回模式下不能使用dml/ddl
SQL> exec dbms_flashback.disable;
PL/SQL 过程已成功完成。
#脚本
declare
cursor cur_flashback is select * from flashback_tab as of scn 5831223 where id=2;
t_row cur_flashback%rowtype;
begin
open cur_flashback;
loop
fetch cur_flashback into t_row;
exit when cur_flashback%notfound;
insert into flashback_tab values(t_row.id);
end loop;
close cur_flashback;
commit;
end;
SQL> declare
2 cursor cur_flashback is select * from flashback_tab as of scn 5831223 where
id=2;
3 t_row cur_flashback%rowtype;
4 begin
5 open cur_flashback;
6 loop
7 fetch cur_flashback into t_row;
8 exit when cur_flashback%notfound;
9 insert into flashback_tab values(t_row.id);
10 end loop;
11 close cur_flashback;
12 commit;
13 end;
14 /
PL/SQL 过程已成功完成。
1.2.5用version query和flashback_transaction_query来跟踪事务
#version query
versions_startscn 该记录开始的scn
versions_endscn 记录结束的scn
versions_xid 事务id
versions_operation 记录的操作
SQL> select * from flashback_tab;
ID
----------
1
2
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5858617
SQL> delete from flashback_tab where id = 2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5858639
SQL> select id,versions_startscn,versions_endscn,versions_operation,versions_xid
2 from flashback_tab versions between scn minvalue and maxvalue;
ID VERSIONS_STARTSCN VERSIONS_ENDSCN VE VERSIONS_XID
---------- ----------------- --------------- -- ----------------
2 5858636 D 01000B0040090000
1
2 5858636
也可以使用指定scn 5858617 and 5858639或者timestamp to_date() and to_date();
#flashback_transaction_query
如果该用户没有查询该事务的权限,执行
GRANT SELECT ANY TRANSACTION TO USER_NAME;
SQL> desc flashback_transaction_query
名称 是否为空? 类型
----------------------------------------------------- -------- ----------------
--------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
SQL> insert into flashback_tab values(2);
已创建 1 行。
commit;
SQL> select * from flashback_tab;(当然你也可以用as of scn插入数据哈!)
ID
----------
1
2
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5863636
SQL> delete from flashback_tab where id=1;
已删除 1 行。
SQL> commit;
提交完成。
SQL> delete from flashback_tab where id=2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> select * from flashback_tab;
未选定行
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5863693
SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql
2 from flashback_transaction_query q where q.xid in(
3 select versions_xid from flashback_tab versions between scn minvalue and ma
xvalue
4 );
XID COMMIT_SCN COMMIT_TIMESTA OPERATION UNDO_SQL
---------------- ---------- -------------- --------------- ---------------------
---------
05001E00BC0D0000 5863622 28-10月-13 UNKNOWN
05001E00BC0D0000 5863622 28-10月-13 UNKNOWN
05001E00BC0D0000 5863622 28-10月-13 BEGIN
08000900A10C0000 5863649 28-10月-13 UNKNOWN
08000900A10C0000 5863649 28-10月-13 BEGIN
06001000420C0000 5863656 28-10月-13 UNKNOWN
06001000420C0000 5863656 28-10月-13 BEGIN
已选择7行。
1.3用as of timestamp查询和恢复
现在由于人为失误误删除id=22的数据,现在我想查询并恢复它,用as of timestamp可以将表恢复到一个指定的时间
SQL> create table fsb_tab(id number);
表已创建。
SQL> insert into fsb_tab values(11);
已创建 1 行。
SQL> insert into fsb_tab values(22);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from fsb_tab;
ID
----------
11
22
SQL> delete from where id=22;
SQL>COMMIT;
提交完成。
SQL> SELECT * FROM FSB_TAB AS OF TIMESTAMP SYSDATE-3/1440;
ID
----------
11
22
SQL> insert into fsb_tab select * from fsb_tab as of timestamp sysdate-3/1440 wh
ere id=22;
已创建 1 行。
SQL> select * from fsb_tab;
ID
----------
11
22
SQL>commit;
或者用一下方法:
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
27-10月-13 11.45.21.040000 下午 +08:00
SQL> select * from fsb_tab as of timestamp to_timestamp('2013-10-27 23:45:21','y
yyy-mm-dd hh24:mi:ss');
ID
----------
11
22
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
5749421
二,flashback table
2.1恢复删除的table
先恢复之前删除的数据
SQL> insert into flashback_tab select * from flashback_tab as of scn 5863622;
已创建2行。
SQL> select * from flashback_tab;
ID
----------
1
2
SQL> commit;
提交完成。
SQL> select * from flashback_tab;
ID
----------
1
2
drop table flashback_tab;
SQL> desc recyclebin;
名称 是否为空? 类型
----------------------------------------------------- -------- ---------------
--------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
#select object_name,original_name from recyclebin;
SQL> select object_name,original_name from recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ -----------------------
---------------
BIN$btbj8FyzRBmZqpjng0loEA==$0 FLASHBACK_TAB
#flashback table flashback_tab to before drop;
SQL> flashback table flashback_tab to before drop;
闪回完成。
SQL> select * from flashback_tab;
ID
----------
1
2
2.2恢复索引
SQL> create index flashback_idx on flashback_tab(id);
索引已创建。
drop table flashback_tab;
flashback table flashback_tab to before drop;
select index_name from user_indexes where table_name = 'flashback_tab';
SQL> select index_name from user_indexes where table_name = 'FLASHBACK_TAB';
INDEX_NAME
------------------------------------------------------------
BIN$R+ddhmi4QCaAvUMIiE8qYg==$0
alter index "BIN$R+ddhmi4QCaAvUMIiE8qYg==$0" rename to flashback_idx1;
SQL> select index_name from user_indexes where table_name='FLASHBACK_TAB';
INDEX_NAME
------------------------------------------------------------
FLASHBACK_IDX1
如果在恢复flashback_tab之前,又创建了一个同名flashback_tab表时会报错。
解决办法只要重命名你要恢复的flashback_tab。
flashback table FLASHBACK_TAB to before drop rename to flashback_tab_old;
*要允许表能行移动,默认下是disable
可以分别从数据字典user_tables,all_tables,dba_tables中查看row_movement属性
SQL> select row_movement from user_tables where table_name='FLASHBACK_TAB';
ROW_MOVEMENT
----------------
ENABLED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5809527
SQL> delete from flashback_tab where id=1;
已删除 1 行。
SQL>COMMIT;
SQL> flashback table flashback_tab to scn 5809527;
闪回完成。
SQL> select * from flashback_tab;
ID
----------
1
2
2.3关于drop的简单用法
drop table name purge;完全删除
purge table name;
purge tablespace name;
purge recyclebin;清空回收站
三,flashback database
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5825669
SQL> select * from flashback_tab;
ID
----------
1
2
SQL> drop table flashback_tab purge;
表已删除。
SQL> select * from flashback_tab;
select * from flashback_tab
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> conn / as sysdba
已连接。
SQL> flashback database to scn 5825669;
flashback database to scn 5825669
*
第 1 行出现错误:
ORA-38757: 要闪回数据库, 数据库必须已装载但不能打开。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE 例程已经启动。
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 327156176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5804032 bytes
数据库装载完毕。
SQL> conn / as sysdba
已连接。
SQL> flashback database to scn 5825669;
闪回完成。
SQL> ALTER DATABASE OPEN RESETLOGS;
数据库已更改。
闪回完成。
SQL> conn admin/admin
已连接。
SQL> select * from flashback_tab;
ID
----------
1
2
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26844646/viewspace-775134/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26844646/viewspace-775134/