Oracle Broker Snapshot Standby测试

Oracle 11g Dataguard Snapshot Standby数据库功能，可将备库置于打开读写状态，进行模拟生产环境主库中测试。当备库Snapshot standby任务完成后，可以切换回物理备库角色。在Snapshot Standby数据库状态下，备库是可以接受主库传过来的日志，但是不能够将变化应用在备库中。

1. 采用DG Broker配置Snapshot Standby配置
1) 查看配置信息
$ dgmgrl sys/oracle
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - dgfxoptdb
Protection Mode: MaxAvailability
Databases:
  fxopt     - Primary database
  fxopt_std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2. 转换备库为镜像库(Snapshot Standby)
DGMGRL> convert database fxopt_std to snapshot standby;
Converting database "fxopt_std" to a Snapshot Standby database, please wait...
Database "fxopt_std" converted successfully
这里必须要开启Flashback功能，否则会失败，报不能创建还原点的错误！
--开启FLASHBACK功能
SQL> select name,flashback_on from v$database;
SQL> show parameter db_recovery
SQL> alter system set db_recovery_file_dest_size='2G';
SQL> alter system set db_recovery_file_dest='/u01/fast_recovery_area';
SQL> alter system set db_flashback_retention_target = 1440;
--重启数据库
SQL> alter database flashback on;
SQL> alter database open;

DGMGRL> show configuration
Configuration - dgfxoptdb
 Protection Mode: MaxAvailability
 Databases:
   fxopt     - Primary database
   fxopt_std - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE             DATABASE_ROLE             DB_UNIQUE_NAME
--------------------- ------------------------  ------------------------
READ WRITE            SNAPSHOT STANDBY          fxopt_std

3. 测试镜像库
1) 备库上操作
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE             DATABASE_ROLE             DB_UNIQUE_NAME
--------------------- ------------------------  ------------------------
READ WRITE            SNAPSHOT STANDBY          fxopt_std

现在备库已经是READ WRITE状态，数据库角色也是SNAPSHOT STANDBY。
SQL> conn abc/abc
Connected.
SQL> select * from tab;
TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
TTT  

SQL> select * from ttt;
NAME                                          SCORE
---------------------------------------- ----------
Evan                                            100

SQL> insert into ttt values('Richard', 98);
1 row created.

SQL> commit;
Commit complete.

SQL> create table abc(id number, name varchar2(20), grade number);
Table created.

SQL> insert into abc values(1,'Richar',2003);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from tab;
TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
ABC                                                          TABLE
TTT                                                          TABLE

SQL> select * from abc;
      ID NAME                                          GRADE
---------- ---------------------------------------- ----------
       1 Richar                                         2003

可以在备库做查询和修改操作。

2） 主库上查看
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ WRITE           PRIMARY          fxopt

在主库上插入记录，并切换日志。
SQL> conn abc/abc
Connected.
SQL> select * from ttt;
NAME                                          SCORE
---------------------------------------- ----------
Evan                                            100

SQL> insert into ttt values('Lilly', 99);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from ttt;
NAME                                          SCORE
---------------------------------------- ----------
Evan                                            100
Lilly                                            99

SQL> conn / as sysdba;
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /ora_rman/arc
Oldest online log sequence     50
Next log sequence to archive   52
Current log sequence           52

查看主库规档日志：
$ ll
-rw-r----- 1 oracle oinstall   154624 Mar  7 15:48 ARC_0001_0000000043_0841415433.arc
-rw-r----- 1 oracle oinstall    58368 Mar  7 15:50 ARC_0001_0000000044_0841415433.arc
-rw-r----- 1 oracle oinstall    11776 Mar  7 15:51 ARC_0001_0000000045_0841415433.arc
-rw-r----- 1 oracle oinstall  1559040 Mar  7 16:01 ARC_0001_0000000046_0841415433.arc
-rw-r----- 1 oracle oinstall     1024 Mar  7 16:02 ARC_0001_0000000047_0841415433.arc
-rw-r----- 1 oracle oinstall   486400 Mar  7 16:02 ARC_0001_0000000048_0841415433.arc
-rw-r----- 1 oracle oinstall    58368 Mar  7 16:03 ARC_0001_0000000049_0841415433.arc
-rw-r----- 1 oracle oinstall    66048 Mar  7 16:06 ARC_0001_0000000050_0841415433.arc
-rw-r----- 1 oracle oinstall    36864 Mar  7 16:07 ARC_0001_0000000051_0841415433.arc

主库上切换日志，说明52号日志是刚才规档的日志。
SQL> alter system switch logfile;
System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /ora_rman/arc
Oldest online log sequence     51
Next log sequence to archive   53
Current log sequence           53

$ ll
-rw-r----- 1 oracle oinstall   154624 Mar  7 15:48 ARC_0001_0000000043_0841415433.arc
-rw-r----- 1 oracle oinstall    58368 Mar  7 15:50 ARC_0001_0000000044_0841415433.arc
-rw-r----- 1 oracle oinstall    11776 Mar  7 15:51 ARC_0001_0000000045_0841415433.arc
-rw-r----- 1 oracle oinstall  1559040 Mar  7 16:01 ARC_0001_0000000046_0841415433.arc
-rw-r----- 1 oracle oinstall     1024 Mar  7 16:02 ARC_0001_0000000047_0841415433.arc
-rw-r----- 1 oracle oinstall   486400 Mar  7 16:02 ARC_0001_0000000048_0841415433.arc
-rw-r----- 1 oracle oinstall    58368 Mar  7 16:03 ARC_0001_0000000049_0841415433.arc
-rw-r----- 1 oracle oinstall    66048 Mar  7 16:06 ARC_0001_0000000050_0841415433.arc
-rw-r----- 1 oracle oinstall    36864 Mar  7 16:07 ARC_0001_0000000051_0841415433.arc
-rw-r----- 1 oracle oinstall   483840 Mar  7 16:17 ARC_0001_0000000052_0841415433.arc

查看备库规档日志：
$ ll
-rw-r----- 1 oracle oinstall   154624 Mar  7 15:51 ARC_0001_0000000043_0841415433.arc
-rw-r----- 1 oracle oinstall    58368 Mar  7 15:51 ARC_0001_0000000044_0841415433.arc
-rw-r----- 1 oracle oinstall    11776 Mar  7 15:51 ARC_0001_0000000045_0841415433.arc
-rw-r----- 1 oracle oinstall  1559040 Mar  7 16:01 ARC_0001_0000000046_0841415433.arc
-rw-r----- 1 oracle oinstall     1024 Mar  7 16:02 ARC_0001_0000000047_0841415433.arc
-rw-r----- 1 oracle oinstall   486400 Mar  7 16:02 ARC_0001_0000000048_0841415433.arc
-rw-r----- 1 oracle oinstall    58368 Mar  7 16:07 ARC_0001_0000000049_0841415433.arc
-rw-r----- 1 oracle oinstall    66048 Mar  7 16:07 ARC_0001_0000000050_0841415433.arc
-rw-r----- 1 oracle oinstall    36864 Mar  7 16:07 ARC_0001_0000000051_0841415433.arc
-rw-r----- 1 oracle oinstall   483840 Mar  7 16:17 ARC_0001_0000000052_0841415433.arc

发现52号日志经传到备库上，只是没有应用。
查看备库日志应用情况，50,51,52号日志都没有应用。
SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIME   NEXT_TIME    APPLIED
---------- ------------ ------------ ------------------
      43 07-MAR-14    07-MAR-14    YES
      44 07-MAR-14    07-MAR-14    YES
      45 07-MAR-14    07-MAR-14    YES
      46 07-MAR-14    07-MAR-14    YES
      47 07-MAR-14    07-MAR-14    YES
      48 07-MAR-14    07-MAR-14    YES
      49 07-MAR-14    07-MAR-14    YES
      50 07-MAR-14    07-MAR-14    NO
      51 07-MAR-14    07-MAR-14    NO
      52 07-MAR-14    07-MAR-14    NO

4. 镜像库(Snapshot Standby)转换备库
Snaphost Standyb是通过闪回功能实现。
镜像库切换回备库的过程是通过闪回到原还点，并应用主库生成日志，实现主备一致。
1）切换回物理备库
DGMGRL> show configuration
Configuration - dgfxoptdb
Protection Mode: MaxAvailability
Databases:
  fxopt     - Primary database
  fxopt_std - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> convert database fxopt_std to physical standby;
Converting database "fxopt_std" to a Snapshot Standby database, please wait...
Database "fxopt_std" converted successfully
DGMGRL> convert database fxopt_std to physical standby;
Converting database "fxopt_std" to a Physical Standby database, please wait...
Operation requires shutdown of instance "fxopt" on database "fxopt_std"
Shutting down instance "fxopt"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "fxopt" on database "fxopt_std"
Starting instance "fxopt"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Continuing to convert database "fxopt_std" ...
Operation requires shutdown of instance "fxopt" on database "fxopt_std"
Shutting down instance "fxopt"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "fxopt" on database "fxopt_std"
Starting instance "fxopt"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database "fxopt_std" converted successfully

2） 转换成功，查看结果，切换成功
DGMGRL> show configuration
Configuration - dgfxoptdb
Protection Mode: MaxAvailability
Databases:
  fxopt     - Primary database
  fxopt_std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

打开Active dataguard特性查看验证，刚才的50,51,52日志记录已经应用。
SQL> conn abc/abc
Connected.
SQL> select * from ttt;
NAME                                          SCORE
---------------------------------------- ----------
Evan                                            100
已经还原到与主库记录一致状态。