flashback系列文章三（flashback database）

從oracle10g開始，新增flashback database功能。
需要首先開啟flashback_on，把資料庫啟動到mount狀態

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES
SQL> alter database open;

Database altered.
類似於redo log，在啟用flashback database之後oracle會啟用rvwr程式，把undo寫入到閃回日誌檔案中
[oracle@rac1 ~]$ ps -ef|grep rvwr|grep -v grep
oracle    7421     1  0 16:28 ?        00:00:00 ora_rvwr_dcits

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 2G
recovery_parallelism                 integer     0

[oracle@rac1 flashback]$ date
Tue Dec 31 17:58:38 CST 2013
[oracle@rac1 flashback]$ pwd
/u01/app/oracle/flash_recovery_area/DCITS/flashback
[oracle@rac1 flashback]$ ll
total 8020
-rw-r-----  1 oracle oinstall 8200192 Dec 31 17:56 o1_mf_9d501fd4_.flb
可見flashback設定成on之後已經生成了一個flashback日誌了


db_flashback_retention_target引數以分鐘為單位，定義了資料庫能夠閃回的時間上限。
SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440


建立個測試表，記錄下時間和scn，等下用timestamp和scn恢復下資料庫

SQL> conn scott/tiger
Connected.
SQL> create table test(id number);

Table created.

SQL> insert into test values(100);

1 row created.

SQL> insert into test values(200);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,;yyyy-mm-dd hh24:mi:ss') from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-12-31 22:06:20

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  835057

SQL> drop table test;

Table dropped.

下面我們分別用時間戳和scn進行恢復資料庫

SQL> conn sys/oracle as sysdba
Connected.

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

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              71304784 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to scn 835057;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn scott/tiger
Connected.
SQL> select * from test;

        ID
----------
       100
       200

可以看見刪掉的表被恢復了。
下面類似的透過timestamp來flashback database

SQL> conn sys/oracle as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              71304784 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> flashback database to timestamp to_timestamp('2013-12-31 22:06:20','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn scott/tiger
Connected.
SQL> select * from test;

        ID
----------
       100
       200

如果確認恢復完畢了，使用resetlogs重新啟動下資料庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              71304784 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.