1、概念:
闪回(flashback):当出现逻辑错误时(比如用户误删除了表、或者系统管理员误删除了用户等),能够非常快速的完成对业务数据的恢复。
2、应用范围:
(1)系统管理员误删除了用户
(2)用户失误截断了表或者更改了表的数据(truncate,delete,update,insert)。
不适应情况:
(1)对于数据块损坏或者联机日志文件损坏,必须采用介质恢复。
(2)用户错误的执行了某个批处理任务,或者该批处理任务的脚本编写有问题,使得多个表的逻辑出现问题,无法采用闪回表的方式进行恢复。
3、检查闪回环境。
(1)归档必须开启。
[oracle@anpc ~]$ sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /Oracle/arch
Oldest online log sequence 681
Next log sequence to archive 683
Current log sequence 683
(2)闪回区设置:
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /Oracle/app/flash_recovery_are
a
db_recovery_file_dest_size big integer 4G
SQL> show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
默认:24小时,1440分钟。
此参数是设定闪回数据的最长保存时间
(3)闪回开启:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1820540928 bytes
Fixed Size 2145344 bytes
Variable Size 1258292160 bytes
Database Buffers 553648128 bytes
Redo Buffers 6455296 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
4、闪回实验:
(1)闪回数据库。
SQL> create user ftest identified by ftest default tablespace users;
User created.
SQL> grant connect,resource to ftest;
Grant succeeded.
SQL> connect ftest/ftest
Connected.
SQL> create table testf as select * from user_objects;
Table created.
SQL> select count(*) from testf;
COUNT(*)
----------
1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as "the time drop user" from dual;
the time drop user
-------------------
2011-06-02 17:28:52
SQL> drop user ftest cascade;
User dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1820540928 bytes
Fixed Size 2145344 bytes
Variable Size 1258292160 bytes
Database Buffers 553648128 bytes
Redo Buffers 6455296 bytes
Database mounted.
SQL> flashback database to timestamp to_date('2011-06-02 17:28:52','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open read only
2 ;
Database altered.
SQL> connect ftest/ftest
Connected.
SQL> select * from testf;
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
------------ ------------ ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
TESTF
79652 79652 TABLE
02-JUN-11 02-JUN-11 2011-06-02:17:13:50 VALID N N N 1
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1820540928 bytes
Fixed Size 2145344 bytes
Variable Size 1258292160 bytes
Database Buffers 553648128 bytes
Redo Buffers 6455296 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
(2)闪回表:
所谓闪回表,就是将表里的数据会退到历史上的某个时间点。
原理:利用undo表空间里记录的数据被改变前的值,闪回。
与闪回密切关系的数值:
A、undo_retention undo保留时间默认900秒。
B、retention guarantee 模式修改,当undo表空间不够时避免commit的事务被覆盖,而不能保留默认900S。
alter tablespace undotbs1 retention guarantee;
alter tablespace undotbs1 retention noguarantee;
C、数据行的移动特性。
实验测试:
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;
System altered.
SQL> delete from test;
9 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table test to timestamp to_date('2011-06-03 11:13:54','yyyy-mm-dd hh24:mi:ss');
flashback table test to timestamp to_date('2011-06-03 11:13:54','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table test enable row movement;
Table altered.
SQL> flashback table test to timestamp to_date('2011-06-03 11:13:54','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> select count(*) from test;
COUNT(*)
----------
9
注意:
如果闪回的期间发生过DDL操作,则闪回表操作失败。
(3)Oracle回收站的使用。
SQL> drop table testf;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTF BIN$pMZ2DnFf/HrgQKjADP1k6Q==$0 TABLE 2011-06-03:10:25:02
恢复表并改表名:
SQL> flashback table testf to before drop rename to testfnew;
Flashback complete.
SQL> drop table testfnew;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTFNEW BIN$pMZ2DnFg/HrgQKjADP1k6Q==$0 TABLE 2011-06-03:10:52:01
恢复表不更改表名
SQL> flashback table "BIN$pMZ2DnFg/HrgQKjADP1k6Q==$0" to before drop;
Flashback complete.
SQL> select count(*) from testfnew;
COUNT(*)
----------
1
注意:表恢复过来后表上的对象需要重建,如索引重建。
备注--与回收站有关的命令:
SQL> purge index "索引命名";清除回收站的索引。
SQL> purge tablespace users;清除回收站里面属于user表空间的所有对象占用的空间。
SQL> purge user_recyclebin; 清除回收站里面属于当前用户的所有对象占用的空间。
SQL> purge dba_recyclebin; 清楚回收站里所有对象占用的空间。
SQL> DROP TABLE TABLENAME PURGE; 彻底删除表,不放到回收站。
闪回(flashback):当出现逻辑错误时(比如用户误删除了表、或者系统管理员误删除了用户等),能够非常快速的完成对业务数据的恢复。
2、应用范围:
(1)系统管理员误删除了用户
(2)用户失误截断了表或者更改了表的数据(truncate,delete,update,insert)。
不适应情况:
(1)对于数据块损坏或者联机日志文件损坏,必须采用介质恢复。
(2)用户错误的执行了某个批处理任务,或者该批处理任务的脚本编写有问题,使得多个表的逻辑出现问题,无法采用闪回表的方式进行恢复。
3、检查闪回环境。
(1)归档必须开启。
[oracle@anpc ~]$ sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /Oracle/arch
Oldest online log sequence 681
Next log sequence to archive 683
Current log sequence 683
(2)闪回区设置:
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /Oracle/app/flash_recovery_are
a
db_recovery_file_dest_size big integer 4G
SQL> show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
默认:24小时,1440分钟。
此参数是设定闪回数据的最长保存时间
(3)闪回开启:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1820540928 bytes
Fixed Size 2145344 bytes
Variable Size 1258292160 bytes
Database Buffers 553648128 bytes
Redo Buffers 6455296 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
4、闪回实验:
(1)闪回数据库。
SQL> create user ftest identified by ftest default tablespace users;
User created.
SQL> grant connect,resource to ftest;
Grant succeeded.
SQL> connect ftest/ftest
Connected.
SQL> create table testf as select * from user_objects;
Table created.
SQL> select count(*) from testf;
COUNT(*)
----------
1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as "the time drop user" from dual;
the time drop user
-------------------
2011-06-02 17:28:52
SQL> drop user ftest cascade;
User dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1820540928 bytes
Fixed Size 2145344 bytes
Variable Size 1258292160 bytes
Database Buffers 553648128 bytes
Redo Buffers 6455296 bytes
Database mounted.
SQL> flashback database to timestamp to_date('2011-06-02 17:28:52','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open read only
2 ;
Database altered.
SQL> connect ftest/ftest
Connected.
SQL> select * from testf;
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
------------ ------------ ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
TESTF
79652 79652 TABLE
02-JUN-11 02-JUN-11 2011-06-02:17:13:50 VALID N N N 1
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1820540928 bytes
Fixed Size 2145344 bytes
Variable Size 1258292160 bytes
Database Buffers 553648128 bytes
Redo Buffers 6455296 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
(2)闪回表:
所谓闪回表,就是将表里的数据会退到历史上的某个时间点。
原理:利用undo表空间里记录的数据被改变前的值,闪回。
与闪回密切关系的数值:
A、undo_retention undo保留时间默认900秒。
B、retention guarantee 模式修改,当undo表空间不够时避免commit的事务被覆盖,而不能保留默认900S。
alter tablespace undotbs1 retention guarantee;
alter tablespace undotbs1 retention noguarantee;
C、数据行的移动特性。
实验测试:
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;
System altered.
SQL> delete from test;
9 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table test to timestamp to_date('2011-06-03 11:13:54','yyyy-mm-dd hh24:mi:ss');
flashback table test to timestamp to_date('2011-06-03 11:13:54','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table test enable row movement;
Table altered.
SQL> flashback table test to timestamp to_date('2011-06-03 11:13:54','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> select count(*) from test;
COUNT(*)
----------
9
注意:
如果闪回的期间发生过DDL操作,则闪回表操作失败。
(3)Oracle回收站的使用。
SQL> drop table testf;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTF BIN$pMZ2DnFf/HrgQKjADP1k6Q==$0 TABLE 2011-06-03:10:25:02
恢复表并改表名:
SQL> flashback table testf to before drop rename to testfnew;
Flashback complete.
SQL> drop table testfnew;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTFNEW BIN$pMZ2DnFg/HrgQKjADP1k6Q==$0 TABLE 2011-06-03:10:52:01
恢复表不更改表名
SQL> flashback table "BIN$pMZ2DnFg/HrgQKjADP1k6Q==$0" to before drop;
Flashback complete.
SQL> select count(*) from testfnew;
COUNT(*)
----------
1
注意:表恢复过来后表上的对象需要重建,如索引重建。
备注--与回收站有关的命令:
SQL> purge index "索引命名";清除回收站的索引。
SQL> purge tablespace users;清除回收站里面属于user表空间的所有对象占用的空间。
SQL> purge user_recyclebin; 清除回收站里面属于当前用户的所有对象占用的空间。
SQL> purge dba_recyclebin; 清楚回收站里所有对象占用的空间。
SQL> DROP TABLE TABLENAME PURGE; 彻底删除表,不放到回收站。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24070945/viewspace-697056/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24070945/viewspace-697056/