復原點(restore point)是和閃回資料庫(flashback database)相關的一個功能.對於閃回資料庫,很少用,因為在生產庫中是
不太可能把整個庫直接回退到過去某個時間點的.但在10g上模仿11g的snapshot standby時用到這個功能,發現有保證的復原點可以在standby上部署臨時
測試環境,並且又可以在不啟用閃回資料庫的情況下使用.
以下測試:
[oracle@ct6604 ~]$ sqlplus / as sysdba
--檢視閃回恢復區的資訊
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4182M
recovery_parallelism                 integer     0
--未開啟閃回資料庫
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
--建立測試表,scott.selectshen
SQL> create table scott.selectshen as select * from dba_objects;

Table created.
--當前記錄資料為86274
SQL> select count(1) from scott.selectshen;

COUNT(1)
----------
     86274
--檢視當前是否有閃回日誌
SQL> ho ls /u01/app/oracle/fast_recovery_area/CT6604/flashback
--建立復原點restore01
SQL> create restore point restore01 guarantee flashback database;

Restore point created.
--檢視當前閃回資料庫的狀態
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY
--檢視當前閃回日誌
SQL> ho ls -l /u01/app/oracle/fast_recovery_area/CT6604/flashback
total 102416
-rw-r-----. 1 oracle oinstall 52436992 Aug 11 22:11 o1_mf_bwn0o3w6_.flb
-rw-r-----. 1 oracle oinstall 52436992 Aug 11 22:11 o1_mf_bwn0o5g3_.flb
--對測試表做delete操作
SQL> delete from scott.selectshen where object_id>200;

86075 rows deleted.

SQL> commit;

Commit complete.
--當前記錄資料為199
SQL> select count(1) from scott.selectshen;

COUNT(1)
----------
       199

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

Total System Global Area 730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             482345024 bytes
Database Buffers          243269632 bytes
Redo Buffers                2842624 bytes
Database mounted.
--把資料庫回退到復原點restore01
SQL> flashback database to restore point restore01;

Flashback complete.
--以只讀方式開啟資料庫
SQL> alter database open read only;

Database altered.
--可以看到當前的記錄數為復原點時刻的記錄數86274
SQL> select count(1) from scott.selectshen;

COUNT(1)
----------
     86274

--如果想以回退資料庫之前的狀態開啟資料庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             482345024 bytes
Database Buffers          243269632 bytes
Redo Buffers                2842624 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--應用復原點之後的redolog
SQL> recover database;
Media recovery complete.
--開啟資料庫
SQL> alter database open;

Database altered.
--可以看到當前的記錄數為回退資料庫之前的記錄數119
SQL> select count(1) from scott.selectshen;

COUNT(1)
----------
       199

--如果想以復原點時刻開啟資料庫,並不應用之後的redolog
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             482345024 bytes
Database Buffers          243269632 bytes
Redo Buffers                2842624 bytes
Database mounted.
--把資料庫回退到復原點restore01
SQL> flashback database to restore point restore01;

Flashback complete.
--開啟資料庫並resetlogs,不恢復redolog
SQL> alter database open resetlogs;

Database altered.
--可以看到當前的記錄數為復原點時刻的記錄數86274
SQL> select count(1) from scott.selectshen;

COUNT(1)
----------
     86274
--刪除復原點
SQL> drop restore point restore01;

Restore point dropped.
--刪除復原點後flashback_on的狀態又變成NO
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
--刪除復原點後,閃回日誌已被清除
SQL> ho ls -l /u01/app/oracle/fast_recovery_area/CT6604/flashback
total 0

備註:
1.11g中可以建立as of 某個scn或時間點的復原點,但只能建立當前scn或當前時間點之前的復原點,不能建立將來的.並且as of不能配合guarantee使用,
要真正的開flashback database.
2.保證的復原點(guaranteed restore point)不會被保留策略自動刪除,並且11g中增加保留(preserve)復原點,也不會被保留策略自動刪除.

oracle小知識點3--有保證的復原點guaranteed restore point

相關文章