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 convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ogg)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file /u01/arch/flasharch/OGG/flashback/o1_mf_970y12xh_.flb
Deleted Oracle managed file /u01/arch/flasharch/OGG/flashback/o1_mf_970y15og_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 2931571730 (0xaebc3c12)

SQL> ! ls -lt /u01/arch/flasharch/OGG/flashback
total 0

重启数据库到mount状态

SQL> select database_role,db_unique_name,open_mode from v$database;
select database_role,db_unique_name,open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
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.

确认PHYSICAL STANDBY状态

SQL> select database_role,db_unique_name,open_mode from v$database;

DATABASE_ROLE     DB_UNIQUE_NAME         OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY ogg                MOUNTED

开启为READ ONLY-- REAL TIME APPLY状态

SQL> alter database open;

Database altered.

SQL>  alter database recover managed standby database using current logfile disconnect from session;

Database altered.

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

查看LAG情况

此时日志传输 、日志应用LAG都为 0，备库已经重新与主库同步

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 12:13:23 transport lag    +00 00:00:00           10/30/2013 12:13:22
20131030 12:13:23 apply lag       +00 00:00:00           10/30/2013 12:13:22

确认在SNAPSHOT STANDBY下面的操作都已回滚

SQL> select table_name,TABLESPACE_NAME from dba_tables where tablespace_name = 'TBS_XHL';

TABLE_NAME               TABLESPACE_NAME
------------------------------ ------------------------------
XHL                   TBS_XHL

被删除的表已经恢复

SQL> select count(*) from xhl_snapstb.xhl;
select count(*) from xhl_snapstb.xhl
                                 *
ERROR at line 1:
ORA-00942: table or view does not exist

新增表的操作已经撤销

  1* SELECT USERNAME FROM DBA_USERS WHERE USERNAME= 'XHL_SNAPSTB'
SQL> /

no rows selected

新增用户已经撤销

总结：SNAPSHOT STANDBY 模式将备库置于可读写状态，可以在此备库上来回折腾 ，这个

结合REAL APPLICATION TESTING 做升级前测试非常方便。要注意如果在SNAPSHOT STANDBY

上面的数据更改操作过大，恢复回PHYSICAL STANDBY的时间会非常长。