在ORACLE 11GR2 的DG环境中，我们可以把备库启动到READ ONLY状态，来实现读的功能，但是在此状态不可以写，不能用于一些测试工作，比如测试PL/SQL等。
ORACLE提供了SNAPSHOT STANDBY的功能，允许把STANDBY数据库启动到SHAPSHOT状态，进行一些测试，测试完成后可以转换到原来的PHYSICAL STANDBY的状态。在SNAPSHOT STANDBY过程中，STANDBY仍然接收日志，但是不运用，当转换成PHYSICAL STANDBY状态时会自己运用以前生成的日志。
1.SNAPSHOT STANDBY DATABASE接收与归档日志，但是不运用日志。
2.转换到PHYSICAL STANDBY DATABASE后会自己的运行以前生成的日志
3.转换到PHYSICAL STANDBY DATABASE后在SNAPHOST STANDBY时更新的所有的数据都将会丢失。
4.SNAPSHOT STANDBY DATABASE不用运行在Maximum Protection mode
测试环境会：OS:RHEL 5.6 X86_86 DB:ORACLE 11.2.0.2
11GR2 搭建活动的物理DG/DATAGRUAD (READ ONLY模式)
11GR2 datagruad 环境搭建BORKER
- 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
- 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已经自动删除了。
本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/986404，如需转载请自行联系原作者