闪回还原点flashback restore point有两种,一种是正常还原点normal restore point,一种是担保还原点guaranteed restore point。
正常还原点和担保还原点的信息都是存储在控制文件中的,针对这点,二者又是存在区别的:正常还原点的信息如果不手动删除,控制文件是可以自动管理删除的,而担保还原点的信息如果不手动删除,就会一直保存在控制文件中,是不会自动删除的。也就是说,只要建立了担保还原定并且没有手动删除掉,那么数据库就可以恢复到这个还原点的状态。
如果担保还原点和flashback database一起使用,那么数据库就可以恢复到担保还原点和之后的任何时间点。
担保还原点必须在数据库处于flashback on 的情况下才可以定义,担保还原点可以保证准确地将数据库flashback到定义的还原点。
在flashback on 状态下创建还原点:
SYS@orcl 19-OCT-14>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
normal restore point
创建一个normal restore point:
SYS@orcl 19-OCT-14>create restore point normal_point;
Restore point created.
查看v$restore_point这个视图就可以看到创建的还原点:
SYS@orcl 19-OCT-14>desc v$restore_point
Name Null? Type
----------------------------------------- -------- ----------------------------
SCN NUMBER
DATABASE_INCARNATION# NUMBER
GUARANTEE_FLASHBACK_DATABASE VARCHAR2(3)
STORAGE_SIZE NUMBER
TIME TIMESTAMP(9)
RESTORE_POINT_TIME TIMESTAMP(9)
PRESERVED VARCHAR2(3)
NAME VARCHAR2(128)
SYS@orcl 19-OCT-14>col name for a30
SYS@orcl 19-OCT-14>select scn,name,guarantee_flashback_database,time from v$restore_point;
SCN NAME GUA TIME
---------- --------------------- --------- --- ---------------------------------------------------------------------------
2218982 NORMAL_POINT NO 19-OCT-14 06.14.36.000000000 PM
可以看到,名为normal_point的还原点是一个正常还原点,而不是担保还原点。
将用户切换到hh,创建一张测试表以模拟误操作:
HH@orcl 19-OCT-14>create table t1 (id int,name char(10));
Table created.
下面切换到sys用户,使用还未建立表t1时创建的restore point来还原数据库(注意,此时要关闭数据库,重启到mount状态):
HH@orcl 19-OCT-14>conn /as sysdba
Connected.
SYS@orcl 19-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 19-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
将数据库还原到还原点normal_point:
SYS@orcl 19-OCT-14>flashback database to restore point normal_point;
Flashback complete.
使用resetlogs打开数据库:
SYS@orcl 19-OCT-14>alter database open resetlogs;
Database altered.
切换到hh,查看表t1是否还存在:
SYS@orcl 19-OCT-14>conn hh/hh
Connected.
HH@orcl 19-OCT-14>select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
可以看到,表t1已经不存在了,说明此时数据库已经回退到创建的还原点normal_point了。
guaranteed restore point
创建一个guaranteed restore point:
SYS@orcl 19-OCT-14>create restore pointguaranteed_point guarantee flashback database;
Restore point created.
查看视图v$restore_point:
SYS@orcl 19-OCT-14>select scn,name,guarantee_flashback_database,time from v$restore_point;
SCN NAME GUA TIME
---------- ------------------------------ --- ---------------------------------------------------------------------------
2219342 GUARANTEED_POINT YES 19-OCT-14 06.30.26.000000000 PM
2218982 NORMAL_POINT NO 19-OCT-14 06.14.36.000000000 PM
可以看到,创建了一个担保还原点guaranteed_point。
切换到用户hh,创建一个测试表t2以模拟误操作:
SYS@orcl 19-OCT-14>conn hh/hh
Connected.
HH@orcl 19-OCT-14>create table t2 (id int,name char(10));
Table created.
切换到sys用户,通过将数据库回退到还原点guaranteed_point来将数据库还原到还未创建表t2的时刻:
同上,要将数据库关闭,重启到mount:
HH@orcl 19-OCT-14>conn /as sysdba
Connected.
SYS@orcl 19-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 19-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 19-OCT-14>flashback database to restore point guaranteed_point;
Flashback complete.
SYS@orcl 19-OCT-14>alter database open resetlogs;
Database altered.
切换到hh用户查看表t2是否还存在:
SYS@orcl 19-OCT-14>conn hh/hh
Connected.
HH@orcl 19-OCT-14>select * from t2;
select * from t2
*
ERROR at line 1:
ORA-00942: table or view does not exist
删除还原点:
SYS@orcl 19-OCT-14>drop restore pointnormal_point;
Restore point dropped.
SYS@orcl 19-OCT-14>drop restore point guaranteed_point;
Restore point dropped.
非flashback on状态下创建还原点:
YS@orcl 19-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 19-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 19-OCT-14>alter database flashback off;
Database altered.
SYS@orcl 19-OCT-14>alter database open;
Database altered.
SYS@orcl 19-OCT-14>select open_mode,flashback_on from v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
READ WRITE NO
创建正常还原点:
SYS@orcl 19-OCT-14>create restore point normal_point;
Restore point created.
SYS@orcl 19-OCT-14>select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
创建担保还原点:
SYS@orcl 19-OCT-14>create restore point guarantee_point guarantee flashback database;
Restore point created.
SYS@orcl 19-OCT-14>select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
可以看到当我们在flashback_on为no的情况下去创建担保还原点,flashback_on的状态就会变成restore point noly