11GR2新功能：DATAGURAD STANDBY的SNAPHOST测试-原创手记-慕课网

在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(*)

----------

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已经自动删除了。

11GR2 DATAGUARD SNAPORACLE DG