oracle小知识点3

知识点3"/>

oracle小知识点3

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

来自 “ ITPUB博客 ” ，链接：/，如需转载，请注明出处，否则将追究法律责任。

转载于:/