flashback database的使用

1、开启数据库flashback功能

SQL> shutdown immediate

SQL> startup mount；

SQL> alter database flashback on;

SQL> alter database force logging;

SQL> select flashback_on, force_logging from v$database;

FLASHBACK_ON       FOR
------------------ ---
YES           YES

2、基于scn号的flashback

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     565814

SQL> create table test (id int, name varchar2(30));

Table created.

SQL> insert into test values(7, 'linux');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

    ID NAME
---------- ------------------------------
     7 linux

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size            1218580 bytes
Variable Size           71305196 bytes
Database Buffers      138412032 bytes
Redo Buffers            7168000 bytes
Database mounted.

SQL> flashback database to scn 565814;

Flashback complete.

SQL>

基于scn号的flashback为不完全恢复，打开时可以使用resetlogs，不应用redo log至最后的一致性。

若需要做完全恢复，保持数据库的一致性，则可以使用recover database应用redo log，再打开数据库

（1）incomplete  recover

SQL> alter database open resetlogs;

Database altered.

SQL>

（2）complete recover

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

3、基于时间点的恢复

SQL> select sysdate from dual;

SYSDATE
-------------------
2010-12-05 19:32:54

SQL> create table test(id int, name varchar2(30));

Table created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size            1218580 bytes
Variable Size           71305196 bytes
Database Buffers      138412032 bytes
Redo Buffers            7168000 bytes
Database mounted.

SQL> flashback database to timestamp to_timestamp('2010-12-05 19:32:54','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

 同样可以使用两种方式打开数据库，一致性与非一致性

（1）incomplete  recover

SQL> alter database open resetlogs;

Database altered.

SQL>

（2）complete recover

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

4、关闭flashback功能

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on, force_logging from v$database;

FLASHBACK_ON       FOR
------------------ ---
NO           YES

SQL>