Oracle 11g DataGuard 的Snapshot Standby数据库功能，可以让物理备库从只读状态 切换到可读写状态。
在snapshot standby状态下，备库可以进行读写操作，同时备库还会正常接收主库的归档和日志信息，但不会应用这些redo日志。
当备库读写状态下任务完成后，还可以从Snapshot Standby数据库角色切换回Physical standby备库角色，恢复与主库数据同步。
切换回物理备库后，在Snapshot standby状态下写入备库的数据将不再存在。
【实验环境】
Red Hat Enterprise Linux Server release 5.4
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
【DG主库、物理备库结构信息】
【实验过程】
主库 Primary database：绿色物理备库 Physical standby database:黄色
1、切换前准备
1.1、查看当前备库模式
sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;
1.2、主备库查看测试表信息
1.3、确认备库闪回区已设置
未设置闪回区报错
设置闪回区后查看
sys@DGDB>show parameter db_recovery
1.4、备库取消日志应用
在日志应用状态转换报错
备库取消日志应用
sys@DGDB>alter database recover managed standby database cancel;
sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;
2、physical standby 切换为 snapshot standby
2.1、备库转换为snapshot standby
sys@DGDB>alter database convert to snapshot standby; -- 转化成 snapshot standby
查看当前备库状态 snapshot standby
sys@DGDB>select
database_role, open_mode, protection_mode, protection_level from v$database;
转换过程中告警日志
|
Sun Aug 24 01:18:07 2014 alter database convert to snapshot standby Starting background process RVWR Sun Aug 24 01:18:07 2014 RVWR started with pid=27, OS id=17153 Allocated 3981120 bytes in shared pool for flashback generation buffer Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_08/24/2014 01:18:07 krsv_proc_kill: Killing 3 processes (all RFS) All dispatchers and shared servers shutdown CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. Sun Aug 24 01:18:10 2014 SMON: disabling cache recovery Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 1216097 Resetting resetlogs activation ID 2198976699 (0x8311b8bb) Online log /u02/oradata/sh/redo01.log: Thread 1 Group 1 was previously cleared Online log /u02/oradata/sh/redo02.log: Thread 1 Group 2 was previously cleared Online log /u02/oradata/sh/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1216095 Sun Aug 24 01:18:11 2014 Setting recovery target incarnation to 3 CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby Completed: alter database convert to snapshot standby |
2.2、snapshot standby开库，可读写
sys@DGDB>alter database open;
sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;
2.3、对测试表进行插入操作
现在可以 读写操作 standby database
sys@DGDB>insert into lvxinghao.test select * from scott.emp;
sys@DGDB>select count(*) from lvxinghao.test;
3、snapshot standby 切回 日志应用状态
3.1、备库重启到mount状态
idle>startup mount force;
idle>select database_role, open_mode, protection_mode, protection_level from v$database;
3.2、备库由snapshot standby 切换为 physical standby
idle>alter database convert to physical standby;
切换过程中跟踪日志
|
Sun Aug 24 01:26:09 2014 alter database convert to physical standby ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SH) Flashback Restore Start Flashback Restore Complete Drop guaranteed restore point Stopping background process RVWR Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DGDB/SHANGHAI/flashback/o1_mf_9zlxdhmc_.flb Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DGDB/SHANGHAI/flashback/o1_mf_9zlxdl5k_.flb Guaranteed restore point dropped Clearing standby activation ID 2202357444 (0x83454ec4) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Shutting down archive processes Archiving is disabled Sun Aug 24 01:26:09 2014 ARCH shutting down ARC3: Archival stopped Sun Aug 24 01:26:09 2014 ARCH shutting down ARC2: Archival stopped Sun Aug 24 01:26:09 2014 ARCH shutting down ARC1: Archival stopped Sun Aug 24 01:26:09 2014 ARCH shutting down ARC0: Archival stopped Completed: alter database convert to physical standby |
切换完成后实例处于started状态
sys@DGDB>select instance_name,status from v$instance;
3.3、重启备库到mount状态，查看当前备库角色
sys@DGDB>startup mount force;
sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;
3.4、备库开库，查看测试表
sys@DGDB>alter database open;
sys@DGDB>select database_role, open_mode, protection_mode, protection_level from v$database;
查看测试表 条数不再是24条，而是恢复到切换前与主库相同的10条
3.5、备库上Real-time Apply 方式应用日志
sys@DGDB>alter database recover managed standby database using current logfile disconnect;
【实验总结】
1、切换Snapshot standby前，物理备库需设置快速回复区db_recovery_file_dest。
2、物理备库切换为Snapshot standby状态前，需取消备库的日志应用。
3、物理备库切换为Snapshot standby时，会创建一个restore point，当需要从snapshot standby状态切回原来的状态时，会回到这个restore point的状态，期间在snapshot standby 的备库上写入的数据将不再存在。
吕星昊
2014.8.24