oracle dataguard study之--dataguard failover案例 oracle dg（dataguard）是目前比较常见的数据库ha配置策略。通过实现physical standby和logical standby，可以实现数据冗余容错机制。防止在主库出现严重故障，不能支持服务的时候，没有快速的后备支持环
oracle dataguard study之--dataguard failover案例
oracle dg（dataguard）是目前比较常见的数据库ha配置策略。通过实现physical standby和logical standby，可以实现数据冗余容错机制。防止在主库出现严重故障，不能支持服务的时候，没有快速的后备支持环境。
在dg中，switchover和failover是两个重要的概念，也是dg实现的核心。两者共同点都是primary和standby角色切换，差异在于planned和unplanned之分。switchover关键点在于planned，这个切换动作是在运维机构规划范围内的动作。比如，进行定期系统软硬件升级、设备维修等动作。而failover是真正出现严重系统故障，如数据库宕机、软硬件故障导致的primary不能支持服务，从而进行的切换动作。
根据不同的dg配置，switchover和failover也是有差异的。理论上，switchover是不会造成数据丢失的，primary在切换之后也是在dg配置环境中，作为standby存在的。但是failover则不同，除了运行在最大保护（maximum protection）模式下，primary突发的故障可能引起一部分redo log不能及时的传递到standby端，切换之后很可能有数据损失的情况。更重要的是，primary端在发生failover之后，是不能够直接加入回dg配置的！也就是说，failover之后，primary实际上就是被“抛出”了dg环境。
那么，有什么方法实现primary回到原有的环境呢？这个问题的困难在于保持primary和standby一致。在正常情况下，primary和standby之间是关联同步的，即使发生了switchover，也在可控情况下。failover过程中有数据的缺失，还有primary修复问题。在目前流行版本（11g）中，有三个方法：
ü 环境重建：一种最简单的方法就是直接删除原来的primary库，引用dg重建方法，重新搭建standby端；
ü rman备份恢复：如果primary端保留过一份failover之前的备份，则可以强制原来的primary端恢复到进行failover的时间点，之后作为standby接收当前primary的redo log传递，应用后可以跟上进度；
ü flashback database恢复：flashback技术是作为传统备份还原技术的补充，提供了更加便捷的恢复策略。使用flashback，可以将数据库恢复到failover之前的时间点。之后的过程和rman备份恢复策略相同；
案例分析：
一、在主库端模拟数据库意外宕机
scott@bjdb>conn /as sysdbaconnected.sys@bjdb>alter system switch logfile;system altered.sys@bjdb>shutdown abortoracle instance shut down.
二、在备库端
1、查看切换信息
sys@shdb>select name,database_role,switchover_status from v$database;name database_role switchover_status--------- ---------------- --------------------testdb12 physical standby not allowed可以看到此时备库处于无法切换状态
2、直接切换
sys@shdb>alter database commit to switchover to primary;alert_log:（告警日志）fatal ni connect error 12514, connecting to: (description=(address_list=(address=(protocol=tcp)(host=shsrv)(port=1521)))(connect_data=(service_name=shdb)(cid=(program=oracle)(host=bjsrv)(user=oracle)))) version information: tns for linux: version 11.2.0.3.0 - production tcp/ip nt protocol adapter for linux: version 11.2.0.3.0 - production time: 04-mar-2015 21:25:13 tracing not turned on. tns error struct: ns main err code: 12564 tns-12564: tns:connection refused ns secondary err code: 0 nt main err code: 0 nt secondary err code: 0 nt os err code: 0error 12514 received logging on to the standbyfal[client, mrp0]: error 12514 connecting to shdb for fetching gap sequencewed mar 04 21:26:00 2015alter database commit to switchover to primaryalter database switchover to primary (testdb12)maximum wait for role transition is 15 minutes.switchover: media recovery is still activedatabase not available for switchover end-of-redo archived log file has not been recovereddatabase not available for switchover end-of-redo archived log file has not been recovereddatabase not available for switchover
3、关闭standby mpr进程
sys@shdb>alter database recover managed standby database finish;alter database recover managed standby database finish terminal recovery: request posted (testdb12)wed mar 04 21:34:34 2015begin: standby redo logfile archivalend: standby redo logfile archivalterminal recovery timestamp is '03/04/2015 21:34:34'terminal recovery: applying standby redo logs.terminal recovery: thread 1 seq# 34 redo requiredmedia recovery waiting for thread 1 sequence 34terminal recovery: end-of-redo log allocationterminal recovery: standby redo logfile 4 created '/dsk4/arch_bj/arch_1_0_820054583.log'this standby redo logfile is being created as part of thefailover operation. this standby redo logfile should bedeleted after the switchover to primary operation completes.media recovery log /dsk4/arch_bj/arch_1_0_820054583.logterminal recovery: log 4 reserved for thread 1 sequence 34recovery of online redo log: thread 1 group 4 seq 34 reading mem 0 mem# 0: /dsk4/arch_bj/arch_1_0_820054583.logidentified end-of-redo (failover) for thread 1 sequence 34 at scn 0xffff.ffffffffincomplete recovery applied until change 1234252 time 03/04/2015 21:23:43mrp0: media recovery complete (testdb12)terminal recovery: successful completionwed mar 04 21:34:35 2015arch: archival stopped, error occurred. will continue retryingoracle instance testdb12 - archival errorora-16014: log 4 sequence# 34 not archived, no available destinationsora-00312: online log 4 thread 1: '/dsk4/arch_bj/arch_1_0_820054583.log'forcing arscn to irscn for tr 0:1234252attempt to set limbo arscn 0:1234252 irscn 0:1234252 resetting standby activation id 2865247982 (0xaac836ee)mrp0: background media recovery process shutdown (testdb12)terminal recovery: completion detected (testdb12)completed: alter database recover managed standby database finish
4、切换数据库到primary
sys@shdb>select status from v$instance;status------------opensys@shdb>select name,database_role,switchover_status from v$database;name database_role switchover_status--------- ---------------- --------------------testdb12 physical standby to primarysys@shdb>alter database commit to switchover to primary;database altered.sys@shdb>alter database open;database altered.告警日志：alter database commit to switchover to primaryalter database switchover to primary (testdb12)maximum wait for role transition is 15 minutes.all dispatchers and shared servers shutdownclose: killing server sessions.close: all sessions shutdown successfully.wed mar 04 21:35:47 2015smon: disabling cache recoverybackup controlfile written to trace file /u01/app/oracle/diag/rdbms/bjdb/testdb12/trace/testdb12_ora_3146.trcstandby terminal recovery start scn: 1234251resetlogs after incomplete recovery until change 1234252online log /dsk2/oradata/bjdb/redo01b.log: thread 1 group 1 was previously clearedonline log /dsk1/oradata/bjdb/redo01a.log: thread 1 group 1 was previously clearedonline log /dsk2/oradata/bjdb/redo02b.log: thread 1 group 2 was previously clearedonline log /dsk1/oradata/bjdb/redo02a.log: thread 1 group 2 was previously clearedonline log /dsk2/oradata/bjdb/redo03b.log: thread 1 group 3 was previously clearedonline log /dsk1/oradata/bjdb/redo03a.log: thread 1 group 3 was previously clearedstandby became primary scn: 1234250wed mar 04 21:35:47 2015setting recovery target incarnation to 3audit_trail initialization parameter is changed back to its original value as specified in the parameter file.switchover: complete - database mounted as primarycompleted: alter database commit to switchover to primary
三、原主库修复后，开机
sys@bjdb>startuporacle instance started.total system global area 442601472 bytesfixed size 2229184 bytesvariable size 281021504 bytesdatabase buffers 155189248 bytesredo buffers 4161536 bytesdatabase mounted.database opened.sys@bjdb>select name,database_role,switchover_status from v$database;name database_role switchover_status--------- ---------------- --------------------testdb12 primary failed destination
现在原来的主库被修复后，整个dataguara架构已经被破坏了，所以必须把原来的主库构建成新的备库，重新恢复dataguard的环境。
四、重新构建dataguard
sys@bjdb>select name,database_role from v$database;
name database_role
-------------------------------------------------- ----------------
testdb12 physical standby