flashback database 方法

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 23 23:30:17 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
----------------------------------------------
設定連個引數:
db_recovery_file_dest
db_recovery_file_dest_size
---------------------------------------------
SQL> startup mount
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.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v$database;

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

SQL> create table test as select * from dba_extents;

Table created.

SQL>
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;

Table dropped.

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
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.
SQL> flashback database to timestamp to_timestamp('2010-01-23 23:33:34','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from test;

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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號。