在ORACLE 11GR2 的DG环境中，我们可以把备库启动到READ ONLY状态，来实现读的功能，但是在此状态不可以写，不能用于一些测试工作，比如测试PL/SQL等。
ORACLE提供了SNAPSHOT STANDBY的功能，允许把STANDBY数据库启动到SHAPSHOT状态，进行一些测试，测试完成后可以转换到原来的PHYSICAL STANDBY的状态。在SNAPSHOT STANDBY过程中，STANDBY仍然接收日志，但是不运用，当转换成PHYSICAL STANDBY状态时会自己运用以前生成的日志。
下面是关于snapshot的一些特征
1.SNAPSHOT STANDBY DATABASE接收与归档日志，但是不运用日志。
2.转换到PHYSICAL STANDBY DATABASE后会自己的运行以前生成的日志
3.转换到PHYSICAL STANDBY DATABASE后在SNAPHOST STANDBY时更新的所有的数据都将会丢失。
4.SNAPSHOT STANDBY DATABASE不用运行在Maximum Protection mode
在配置snapshot时也有一些前提条件：
1.数据库要在archivelog模式。
2.要配置FRA
下面开始测试：
测试环境会：OS:RHEL 5.6 X86_86 DB:ORACLE 11.2.0.2
DG环境的搭建见：
11GR2 搭建活动的物理DG/DATAGRUAD (READ ONLY模式) http://luoping.blog.51cto.com/534596/983952
BROKER的搭建见：
11GR2 datagruad 环境搭建BORKER
http://luoping.blog.51cto.com/534596/983983
1.查看数据库运行模式
SQL> select open_mode,log_mode,flashback_on,database_role from v$database;
OPEN_MODE LOG_MODE FLASHBACK_ON DATABASE_ROLE
-------------------- ------------ ------------------ ----------------
READ ONLY WITH APPLY ARCHIVELOG YES PHYSICAL STANDBY
2.查看FRA
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4032M
3.开始切换
#这里查看一下restore_point里面是否有记录
SQL> select scn,name from v$restore_point;
no rows selected
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
SQL> select open_mode,log_mode,flashback_on,database_role from v$database;
OPEN_MODE LOG_MODE FLASHBACK_ON DATABASE_ROLE
-------------------- ------------ ------------------ ----------------
MOUNTED ARCHIVELOG YES SNAPSHOT STANDBY
#这里使用convert to snapshot standby后，查询v$restore_point多了一条记录。
#其实convert to snapshot standby就是利用的restore point这个功能。
所以在前面我们提到要配置FRA
SQL> col name for a30
SQL> set lines 100
SQL> select scn,name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SCN NAME GUA
---------- ------------------------------ ---
1122733 SNAPSHOT_STANDBY_REQUIRED_09/1 YES
0/2012 21:25:15
4.创建表测试
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 255854696 bytes
Database Buffers 50331648 bytes
Redo Buffers 4747264 bytes
Database mounted.
Database opened.
#查看database role是否是SNAPSHOT STANDBY
SQL> select open_mode,log_mode,flashback_on,database_role from v$database;
OPEN_MODE LOG_MODE FLASHBACK_ON DATABASE_ROLE
-------------------- ------------ ------------------ ----------------
READ WRITE ARCHIVELOG YES SNAPSHOT STANDBY
SQL> show user;
USER is "SYS"
SQL> connect scott/oracle
Connected.
SQL> show user;
USER is "SCOTT"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST1 TABLE
#在scott用户下面创建一张test2表
SQL> create table test2 as select * from emp;
Table created.
SQL> select count(*) from test2;
COUNT(*)
----------
14
5.转换成physical standby并查看表是否存在
SQL> conn / as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 255854696 bytes
Database Buffers 50331648 bytes
Redo Buffers 4747264 bytes
Database mounted.
#这里切换成physical standby
SQL> alter database convert to physical standby;
Database altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 255854696 bytes
Database Buffers 50331648 bytes
Redo Buffers 4747264 bytes
Database mounted.
Database opened.
SQL> select open_mode,log_mode,flashback_on,database_role from v$database;
OPEN_MODE LOG_MODE FLASHBACK_ON DATABASE_ROLE
-------------------- ------------ ------------------ ----------------
READ ONLY ARCHIVELOG YES PHYSICAL STANDBY
#这里提示表已经不存在了。说明已经成功。
SQL> select count(*) from scott.test2;
select count(*) from scott.test2
*
ERROR at line 1:
ORA-00942: table or view does not exist
6.查询restore point是否还存在
SQL> select scn,name from v$restore_point;
no rows selected
这里restore point已经自动删除了。
©著作权归作者所有：来自51CTO博客作者7343696的原创作品，如需转载，请注明出处，否则将追究法律责任
11GR2 DATAGUARD SNAPORACLE DG