11g中的ADG特性本身已经非常有特色,促使很多对于10g中不太灵便的备库升级到11g,对于DBA是一大福利,那么还有一个福利就是snapshot standby了。
在平时的数据更新操作中,DBA可以做好sql审核,如果对于复杂的,繁多的变更,如果有些变更有一定的依赖,数据变化情况比较大,评估有难度,很多问题 单纯在测试环境还发现不了,到了生产就是事儿。如果你饱受这种困扰,snapshot standby就是一个不错的选择。你可以让原本只读的备库可读可写,然后写写画画一番之后回归到上一次的一个临界点,继续应用归档日志。
这种操作基本上没有依赖,非常纯粹,易操作。
我们来举个例子看看。
下面的数据库是一主一备的架构。
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
我们来把备库置为snapshot standby,命令非常简单,在dg broker里面可以使用如下的方式,在sqlplus中也是一个命令就可以搞定,前提是取消日志应用。
dg broker修改为snapshot standby的日志如下:
DGMGRL> convert database s2testmob to snapshot standby;
Converting database "s2testmob" to a Snapshot Standby database, please wait...
Database "s2testmob" converted successfully
切换完成之后再次查看,就会发现s2testmob变为了snapshot standby
[oracle@teststd ~]$ dgmgrl /
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
在备库中会有下面的一些相关日志信息。
Fri Mar 25 22:27:12 2016
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Fri Mar 25 22:27:12 2016
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /U01/app/oracle/diag/rdbms/s2testmob/testmob/trace/testmob_pr00_12884.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 50484782
Fri Mar 25 22:27:12 2016
MRP0: Background Media Recovery process shutdown (testmob)
Managed Standby Recovery Canceled (testmob)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Starting background process RVWR
Fri Mar 25 22:27:13 2016
RVWR started with pid=32, OS id=28987
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/25/2016 22:27:13
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 50484782
Resetting resetlogs activation ID 2146999722 (0x7ff89daa)
Online log /U01/app/oracle/oradata/testmob/redo01.log: Thread 1 Group 1 was previously cleared
Online log /U01/app/oracle/oradata/testmob/redo02.log: Thread 1 Group 2 was previously cleared
Online log /U01/app/oracle/oradata/testmob/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 50484780
Fri Mar 25 22:27:15 2016
Setting recovery target incarnation to 3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
这个时候查看闪回区中,会发现有闪回数据库的日志信息。
[oracle@teststd flashback]$ ll
total 102416
-rw-r----- 1 oracle oinstall 52436992 Mar 25 22:27 o1_mf_chblp1l1_.flb
-rw-r----- 1 oracle oinstall 52436992 Mar 25 22:27 o1_mf_chblp3nb_.flb
[oracle@teststd flashback]$ pwd
/U01/app/oracle/fast_recovery_area/S2testmob/flashback
这个时候查看备库的信息,发现flashback_on的属性已经悄然发生改变。
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
RESTORE POINT ONLY
数据库的角色和状态也发生了相应的变化。
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE                    OPEN_MODE
-------------------------------- ----------------------------------------
SNAPSHOT STANDBY                 READ WRITE
这个时候我们在备库里面就可读可写,我们创建一个用户,新建一个表。
SQL> create user  jeanron identified by jeanron;
User created.
SQL> grant dba to jeanron;
Grant succeeded.
SQL> create table jeanron.test as select *from cat;
Table created.

如果在主库端切换日志,在备库也能够看到RFS依旧可以正常接收归档,但是MRP肯定是还运行不了,也就意味着只会接收归档,但是日志还无法应用。
主库切换日志后,查看备库的日志信息如下:
Fri Mar 25 22:36:04 2016
Archived Log entry 43 added for thread 1 sequence 1734 rlc 846934189 ID 0x7ff89daa dest 2:
RFS[3]: Selected log 4 for thread 1 sequence 1735 dbid 2146967210 branch 846934189
写也写了,这种测试评估还是很有说服力的。完成之后我们就可以切换为备库状态即可。
DGMGRL> convert database s2testmob to physical standby;
Converting database "s2testmob" to a Physical Standby database, please wait...
Operation requires shutdown of instance "testmob" on database "s2testmob"
Shutting down instance "testmob"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        shut down instance "testmob" of database "s2testmob"
        start up and mount instance "testmob" of database "s2testmob"
在备库端还是需要重启一下备库
SQL> shutdown immediate
SQL> startup mount 
SQL> select database_role from v$database;
DATABASE_ROLE
--------------------------------
SNAPSHOT STANDBY
可以直接使用一条命令即可完成切换,切换时间极短。
SQL> alter database convert to physical standby;
Database altered.
查看备库的日志,发现闪回恢复回悄然完成,然后会自动删除闪回日志。
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (testmob)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file /U01/app/oracle/fast_recovery_area/S2testmob/flashback/o1_mf_chblp1l1_.flb
Deleted Oracle managed file /U01/app/oracle/fast_recovery_area/S2testmob/flashback/o1_mf_chblp3nb_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 2207276870 (0x83905f46)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
...
Fri Mar 25 22:45:03 2016
ARCH shutting downARCH shutting down
ARC2: Archival stoppedARC1: Archival stopped
ARC3: Archival stopped
ARC0: Archival stopped
Completed: alter database convert to physical standby

这个时候备库是在nomount状态
SQL> select database_role,open_mode from v$database;
select database_role,open_mode from v$database
                                    *
ERROR at line 1:
ORA-01507: database not mounted
重新mount就报错了,需要重启一下。
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted

SQL> shutdown immediate
SQL> startup mount
以为使用sql命令手工修改,需要在dg broker里面同步一下。
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Snapshot standby database
      Error: ORA-16810: multiple errors or warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR

DGMGRL> convert database s2testmob to physical standby;
Converting database "s2testmob" to a Physical Standby database, please wait...
Operation requires shutdown of instance "testmob" on database "s2testmob"
Shutting down instance "testmob"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish the convert command:
        shut down instance "testmob" of database "s2testmob"
        start up and mount instance "testmob" of database "s2testmob"
其实这个时候备库还没有正式开启日志应用,重新启用一下即可。
DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Physical standby database
      Error: ORA-16766: Redo Apply is stopped
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL> edit database s2testmob set state='ONLINE';
Succeeded.

DGMGRL> show configuration;
Configuration - testmob_dg
  Protection Mode: MaxPerformance
  Databases:
    testmob   - Primary database
    s2testmob - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
整个过程完成之后,就跟什么都没有发生一样,一切又恢复了平静。