11g Dataguard中的snapshot standby特性(r8笔记第49天)

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 整个过程完成之后，就跟什么都没有发生一样，一切又恢复了平静。