闪回数据库的理论内容,请查看我的另外一篇博文:http://blog.csdn.net/flighting_sky/article/details/9884297
闪回数据库利用flashback log来恢复数据库到过去的一个时间点。
第一步:闪回数据库的环境配置
---使得数据库处于加载状态
SQL>startup mount;
----使得数据库处于归档日志模式
SQL>alter database archivelog;
----启动闪回日志
SQL>alter database flashback on;
----查看配置状态
SQL> select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
第二步:模拟业务错误
SQL> create table tab1 as
2 select * from user_tables;
Table created.
SQL> create table tab2 as
2 select * from user_indexes;
Table created.
SQL> select count(*) from tab1;
COUNT(*)
----------
29
SQL> select count(*) from tab2;
COUNT(*)
----------
36
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-08-13 09:03:58
SQL> drop table tab1;
Table dropped.
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-08-13 09:04:17
SQL> drop table tab2;
Table dropped.
SQL> select sysdate from dual;
SYSDATE
-------------------
2013-08-13 09:04:33
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
2771450 2013-08-11 01:28:58 1440 203390976 117325824
第三步:闪回数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 644468736 bytes
Fixed Size 1376520 bytes
Variable Size 268439288 bytes
Database Buffers 369098752 bytes
Redo Buffers 5554176 bytes
Database mounted.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
2771450 11-AUG-13 1440 203390976 114892800
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> flashback database to timestamp to_timestamp('2013-08-13 09:03:58','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
第四步:只读打开数据库,验证数据库
SQL> alter database open read only;
Database altered.
SQL> select count(*) from tab1;
COUNT(*)
----------
29
SQL> select count(*) from tab2;
COUNT(*)
----------
36
第五步:resetlogs打开数据库;
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 644468736 bytes
Fixed Size 1376520 bytes
Variable Size 268439288 bytes
Database Buffers 369098752 bytes
Redo Buffers 5554176 bytes
数据库装载完毕。
SQL> alter database open resetlogs;
数据库已更改。