flashback database 方法（转）

flashback database 方法

--oracle10gR2

SQL> shutdown immediate

----------------------------------------------
设置参数:
db_recovery_file_dest
db_recovery_file_dest_size
---------------------------------------------
SQL> startup mount

SQL>alter database flashback on;

SQL> alter database open;

SQL>select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> create table test as select * from dba_extents;
SQL> select sysdate from dual;

SYSDATE
---------
23-JAN-10

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2010-01-23 23:33:34

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     667414

SQL> drop table test purge;

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> shutdown immediate

SQL>flashback database to timestamp to_timestamp('2010-01-23 23:33:34','yyyy-mm-dd hh24:mi:ss');

SQL>alter database open read only;

SQL> select count(*) from test;

  COUNT(*)
----------
      3397

SQL> shutdown immediate

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2082496 bytes
Variable Size             125831488 bytes
Database Buffers           37748736 bytes
Redo Buffers                6303744 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--一旦resetlogs之后，就再也不能flashback database 到resetlogs之前的状态了，所以之前用open read only检查一下是否flashback 成功了。

SQL>alter database open resetlogs;

Database altered.

SQL> select count(*) from test;

  COUNT(*)
----------
      3397

//通过v$flash_recovery_area_usage视图可以查到闪回区的使用情况。
//通过v$flashback_database_log视图可以查到flashback database能恢复到最早的时间和SCN号。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/559237/viewspace-624643/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/559237/viewspace-624643/