ORACLE 11G SNAPSHOT STANDBY实例
ORACLE 11g PHYSICAL STANDBY已经不仅仅只提供灾难恢复功能，其ACTI VE DATA GUARD 特性让备库处于
RED ONLY状态，可以提供做报表查询、读写分离使用；还新增了SNAPSHOT STANDBY 特性，此特性能让备库
暂时处于可读可写状态，为各种性能、压力测试提供了环境，重要的是测试完成后又可转换回PHYSICAL STANDBY，
备库的数据继续与主库保持一致。
SNAPSHOT STANDBY 限制及条件
1. 只有PHYSICAL STANDBY能转换为SNAPSHOT STANDBY。
2. DB_RECOVERY_FILE_DEST必需设置，这里FLASHBACK DATABASE不是必须的。
3.只有PHYSICAL STANDBY 处于mount模式才能转换为SNAPSHOT STANDBY。
设置闪回恢复区
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=500m scope=both;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/arch/flasharch' scope=both;
System altered.
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
查看备库目前模式
SQL> select database_role,db_unique_name,open_mode from v$database;
DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY ogg READ ONLY WITH APPLY
此时备库为PHYSICAL STANDBY，正处于READ ONLY REAL TIME APPLY 模式
取消备库恢复管理模式
SQL> alter database recover managed standby database cancel;
Database altered.
创建SNAPSHOT STANDBY
创建SNAPSHOT STANDBY 命令非常简单
SQL> alter database convert to snapshot standby;
Database altered.
查看alter log 里面的信息，命令已经执行完成，ORACLE创建了guaranteed restore point
Managed Standby Recovery Canceled (ogg)
Completed: alter database recover managed standby database cancel
Wed Oct 30 11:08:18 2013
alter database convert to snapshot standby
Starting background process RVWR
Wed Oct 30 11:08:18 2013
RVWR started with pid=26, OS id=5156
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/30/2013 11:08:18
查看备库模式已经变为SNAPSHOT STADNBY
SQL> select database_role,db_unique_name,open_mode from v$database;
DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE
---------------- ------------------------------ --------------------
SNAPSHOT STANDBY ogg MOUNTED
查看闪回恢复区已经产生文件
SQL> !ls -lt /u01/arch/flasharch/OGG/flashback
total 102528
-rw-r----- 1 oracle oinstall 52436992 Oct 30 11:37 o1_mf_970y12xh_.flb
-rw-r----- 1 oracle oinstall 52436992 Oct 30 11:08 o1_mf_970y15og_.flb
开启备库，进行写测试
SQL> alter database open;
Database altered.
在备库创建一个新用户
SQL> create user xhl_snapstb identified by xhl;
User created.
SQL> grant dba to xhl_snapstb;
Grant succeeded.
在备库创建一个新表
SQL> create table xhl_snapstb.xhl as select * from dba_users;
Table created.
SQL> select count(*) from xhl_snapstb.xhl;
COUNT(*)
----------
9
删除原有表
SQL> select table_name,TABLESPACE_NAME from dba_tables where tablespace_name = 'TBS_XHL';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
XHL TBS_XHL
SQL> DROP TABLE XHL PURGE;
Table dropped.
DROP TABLESPACE测试
Wed Oct 30 11:23:47 2013
drop tablespace TBS_XHL including contents and datafiles
ORA-38881 signalled during: drop tablespace TBS_XHL including contents and datafiles...
这个是不允许的
查看日志传输状态
在SNAPSHOT STANDBY模式下，日志正常传输但不应用，下面语句进行确认
SQL> select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME
from V$DATAGUARD_STATS
WHERE NAME LIKE '%lag'; 2 3
CTIME NAME VALUE DATUM_TIME
---------------- -------------------- -------------------- ------------------------------
20131030 11:34:35 transport lag +00 00:00:00 10/30/2013 11:34:34
20131030 11:34:35 apply lag +00 00:26:34 10/30/2013 11:34:34
transport lag 时间为0
apply lag 时间为26分34秒
我们将现在时间20131030 11:34:35 与之前 guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/30/2013 11:08:18
时间对比，刚好将近26分钟
转换回PHYSICAL STADNBY
现在我们转换会PHYSICAL STANDBY ，确认在SANPSHOT STANDBY模式下所做的修改已经恢复，
必须在mount模式下进行操作
将数据库切换到mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1365740 bytes
Variable Size 318769428 bytes
Database Buffers 197132288 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> select database_role,db_unique_name,open_mode from v$database;
DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE
---------------- ------------------------------ --------------------
SNAPSHOT STANDBY ogg MOUNTED
convert to physical standby
SQL> alter database convert to physical standby;
Database altered.
查看alter log 可以看到数据库进行falshback restore完成，并将闪回恢复区的文件delete
Wed Oct 30 11:40:36 2013
alter database con
