复原点(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)复原点,也不会被保留策略自动删除.