20160223Oracle 11G Data Guard Failover2

[20160223]Oracle 11G Data Guard Failover-flush redo2.txt

--链接: http://blog.csdn.net/tianlesoftware/article/details/6256542

--昨天测试了使用：
SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

--测试链接 http://blog.itpub.net/267265/viewspace-1992583/

--在主库上执行，而且target_db_name名必须加引号：
SYS@test> ALTER SYSTEM FLUSH REDO TO 'testdg';

--昨天的测试不是太严谨，因为我们的网络很好，而且主库没有任何负载。今天重复测试看看。

1.环境：
SYS@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

DGMGRL> show configuration
Configuration - study

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

--首先关闭备库。在主库做一些dml操作然后执行shutdown abort。然后打开备库到mount状态，看看日志是否传送到备库(mount状态)。

2.建立测试：
--关闭备库。
SYS@testdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

--在主库建立表TXX。

SCOTT@test> create table txx (id number,scn number,ins_date date);
Table created.

SCOTT@test> set numw 12
SCOTT@test> @logfile;
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           GROUP# STATUS     TYPE       MEMBER                                             IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
     1       1      4821    52428800       512       1 YES INACTIVE     13226433055 2016-02-23 08:08:06  13226442338 2016-02-23 09:00:53      1            ONLINE     /u01/app/oracle11g/oradata/test/redo01.log         NO
     2       1      4822    52428800       512       1 NO  CURRENT      13226442338 2016-02-23 09:00:53 2.814750E+14                          2            ONLINE     /u01/app/oracle11g/oradata/test/redo02.log         NO
     3       1      4820    52428800       512       1 YES INACTIVE     13226414075 2016-02-23 05:06:31  13226433055 2016-02-23 08:08:06      3            ONLINE     /u01/app/oracle11g/oradata/test/redo03.log         NO

insert into txx select 1,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;

--中间停顿一会。

insert into txx select 2,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;

insert into txx select 3,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;

insert into txx select 4,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;

insert into txx select 5,current_scn,sysdate from v$database ;
commit ;
alter system archive log current ;

SCOTT@test> select rowid,txx.* from txx;
ROWID                        ID          SCN INS_DATE
------------------ ------------ ------------ -------------------
AABQkIAAEAAAdq0AAA            1  13226444257 2016-02-23 09:21:41
AABQkIAAEAAAdq0AAB            2  13226444430 2016-02-23 09:23:44
AABQkIAAEAAAdq0AAC            3  13226444465 2016-02-23 09:24:02
AABQkIAAEAAAdq0AAD            4  13226444490 2016-02-23 09:24:20
AABQkIAAEAAAdq0AAE            5  13226444516 2016-02-23 09:24:36

SCOTT@test> @logfile;
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           GROUP# STATUS     TYPE       MEMBER                                             IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
     1       1      4827    52428800       512       1 NO  CURRENT      13226444522 2016-02-23 09:24:37 2.814750E+14                          1            ONLINE     /u01/app/oracle11g/oradata/test/redo01.log         NO
     2       1      4825    52428800       512       1 YES ACTIVE       13226444471 2016-02-23 09:24:04  13226444496 2016-02-23 09:24:22      2            ONLINE     /u01/app/oracle11g/oradata/test/redo02.log         NO
     3       1      4826    52428800       512       1 YES ACTIVE       13226444496 2016-02-23 09:24:22  13226444522 2016-02-23 09:24:37      3            ONLINE     /u01/app/oracle11g/oradata/test/redo03.log         NO

--可以发现前面的redo已经归档。
SCOTT@test> @rowid AABQkIAAEAAAdq0AAA
      OBJECT         FILE        BLOCK          ROW DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- -------------------------------------------
      329992            4       121524            0 4,121524             alter system dump datafile 4 block 121524

--模拟假设主库损坏，无法到open状态。
SYS@test> shutdown abort ;
ORACLE instance shut down.

3.开始测试：
--备库与主库打开到mount状态：

SYS@testdg> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             905973200 bytes
Database Buffers          687865856 bytes
Redo Buffers                7344128 bytes
Database mounted.

SYS@test> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             973082064 bytes
Database Buffers          620756992 bytes
Redo Buffers                7344128 bytes
Database mounted.

SYS@testdg> select process,pid,status,group#,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby order by thread#,group# ;
PROCESS       PID STATUS       GROUP#                  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------------------- ---------- ---------- ---------- ---------- ----------
ARCH        10460 CONNECTED    N/A                           0          0          0          0          0
ARCH        10462 CONNECTED    N/A                           0          0          0          0          0
ARCH        10464 CONNECTED    N/A                           0          0          0          0          0
ARCH        10466 CONNECTED    N/A                           0          0          0          0          0
MRP0        10478 APPLYING_LOG N/A                           1       4822       2090     102400          0

--MRP0 在等待日志SEQUENCE#＝4822.

--在主库执行:
SYS@test> ALTER SYSTEM FLUSH REDO TO 'testdg';
System altered.

--补充使用大写看看，依旧不行。
SYS@test> ALTER SYSTEM FLUSH REDO TO TESTDG;
ALTER SYSTEM FLUSH REDO TO TESTDG
                           *
ERROR at line 1:
ORA-00922: missing or invalid option

--这个时候检查备库的alert*.log内容：

Tue Feb 23 09:32:30 2016
RFS[1]: Assigned to RFS process 10566
RFS[1]: Selected log 5 for thread 1 sequence 4822 dbid 2071943378 branch 798551880
Tue Feb 23 09:32:31 2016
Archived Log entry 1318 added for thread 1 sequence 4822 ID 0x806ffa4c dest 1:
RFS[1]: Opened log for thread 1 sequence 4823 dbid 2071943378 branch 798551880
Archived Log entry 1319 added for thread 1 sequence 4823 rlc 798551880 ID 0x806ffa4c dest 2:
Tue Feb 23 09:32:31 2016
Media Recovery Log /u01/app/oracle11g/archivelog/1_4823_798551880.dbf
RFS[1]: Opened log for thread 1 sequence 4824 dbid 2071943378 branch 798551880
Tue Feb 23 09:32:31 2016
RFS[2]: Assigned to RFS process 10568
RFS[2]: Opened log for thread 1 sequence 4825 dbid 2071943378 branch 798551880
Archived Log entry 1320 added for thread 1 sequence 4824 rlc 798551880 ID 0x806ffa4c dest 2:
Tue Feb 23 09:32:31 2016
RFS[3]: Assigned to RFS process 10570
RFS[3]: Opened log for thread 1 sequence 4826 dbid 2071943378 branch 798551880
Archived Log entry 1321 added for thread 1 sequence 4826 rlc 798551880 ID 0x806ffa4c dest 2:
Archived Log entry 1322 added for thread 1 sequence 4825 rlc 798551880 ID 0x806ffa4c dest 2:
Media Recovery Log /u01/app/oracle11g/archivelog/1_4824_798551880.dbf
Media Recovery Log /u01/app/oracle11g/archivelog/1_4825_798551880.dbf
Media Recovery Log /u01/app/oracle11g/archivelog/1_4826_798551880.dbf
Media Recovery Waiting for thread 1 sequence 4827
Tue Feb 23 09:32:33 2016
RFS[4]: Assigned to RFS process 10572
RFS[4]: Selected log 4 for thread 1 sequence 4827 dbid 2071943378 branch 798551880
Tue Feb 23 09:32:33 2016
Archived Log entry 1323 added for thread 1 sequence 4827 ID 0x806ffa4c dest 1:
Tue Feb 23 09:32:34 2016
Standby switchover readiness check: Checking whether recoveryapplied all redo..
Database not available for switchover
  End-Of-REDO archived log file has not been recovered
  Incomplete recovery SCN:3:341542608 archive SCN:3:341562802
Physical Standby did not apply all the redo from the primary.
Media Recovery Log /u01/app/oracle11g/archivelog/1_4827_798551880.dbf
Identified End-Of-Redo (move redo) for thread 1 sequence 4827 at SCN 0x3.145bd5b2
Resetting standby activation ID 2154822220 (0x806ffa4c)
Media Recovery Waiting for thread 1 sequence 4828
Tue Feb 23 09:32:35 2016
Standby switchover readiness check: Checking whether recoveryapplied all redo..
Physical Standby applied all the redo from the primary.

--可以发现已经归档的日志以及在线日志都传输过来了。
--在备库查询:
SYS@testdg> @dgs
PROCESS       PID STATUS       GROUP#                  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------------------- ---------- ---------- ---------- ---------- ----------
ARCH        10460 CONNECTED    N/A                           0          0          0          0          0
ARCH        10464 CONNECTED    N/A                           0          0          0          0          0
RFS         10570 IDLE         N/A                           0          0          0          0          0
RFS         10568 IDLE         N/A                           0          0          0          0          0
RFS         10566 IDLE         N/A                           0          0          0          0          0
ARCH        10462 CLOSING      4                             1       4827          1        168          0
ARCH        10466 CLOSING      5                             1       4822       2048        368          0
MRP0        10478 WAIT_FOR_LOG N/A                           1       4828          0          0          0
8 rows selected.

--可以发现主库的日志已经全部传输过来。

4.验证：
--在备库通过bbed检查表TXX的内容，验证日志应用情况：

BBED> set dba 4,121524
        DBA             0x0101dab4 (16898740 4,121524)

BBED> x /*rnnt rowdata               --*/n--表示显示数字 t--表示日期类型。

rowdata[0]                                  @8078
----------
flag@8078: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8079: 0x01
cols@8080:    3

col    0[2] @8081: 5
col    1[7] @8084: 13226444516
col    2[7] @8092: 2016-02-23 09:24:36

rowdata[22]                                 @8100
-----------
flag@8100: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8101: 0x00
cols@8102:    3

col    0[2] @8103: 4
col    1[7] @8106: 13226444490
col    2[7] @8114: 2016-02-23 09:24:20

rowdata[44]                                 @8122
-----------
flag@8122: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8123: 0x00
cols@8124:    3

col    0[2] @8125: 3
col    1[7] @8128: 13226444465
col    2[7] @8136: 2016-02-23 09:24:02

rowdata[66]                                 @8144
-----------
flag@8144: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8145: 0x00
cols@8146:    3

col    0[2] @8147: 2
col    1[7] @8150: 13226444430
col    2[7] @8158: 2016-02-23 09:23:44

rowdata[88]                                 @8166
-----------
flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8167: 0x00
cols@8168:    3

col    0[2] @8169: 1
col    1[7] @8172: 13226444257
col    2[7] @8180: 2016-02-23 09:21:41

tailchk                                     @8188
-------
BBED-00210: file not found

--可以发现主库的dml操作没有丢失。如果主库损坏在能mount的状态下，执行ALTER SYSTEM FLUSH REDO TO 'testdg';
--这样减少操作丢失，保证业务完整性。

--自己有一个疑问，如果主库的日志损坏了，这样损坏的日志如果应用过来是否很危险，如何检查redo的完整性呢？
--看来在应用日志前打开flashback 也许能减少一定的风险。

时间： 2024-10-23 19:56:56

20160223Oracle 11G Data Guard Failover2的相关文章

Oracle 11g Data Guard环境中的归档管理

11g里面,随着ASM.RAC.Data Guard(包括Active Data Guard)的成熟,使用RAC+ASM+Data Guard越来越成为一种可靠的.维护简单.稳定的高可用性和容灾保护方案.这篇文章谈谈如何管理Oracle 11g Data Guard环境中的归档日志. 归档日志是重要的,不然就不必提到这篇文章,备份恢复需要它,而Data Guard也需要它.在早期版本的Data Guard环境中,常常面临着归档日志管理问题.在Data Guard环境里面,对归档日志管理需要达到以

基于同一主机配置Oracle 11g Data Guard(logical standby)

      Oracle Data Guard逻辑备库是利用主库的一个备份首先建立一个物理备库,然后再将其转换为逻辑备库.这之后主库将日志传递到备库,备库利用logminer从主库的日志中解析出主库所执行过的SQL,在备库上重新执行一遍,从而保证与主库的数据在逻辑上保持一致.与物理备库相对应的是,物理备库使用的是redo apply,逻辑备库使用的是sql apply.因此逻辑备库仅仅保证数据与主库是在逻辑上是一致的,从而逻辑备库可以处于open状态下并进行相应的DML操作.本文描述了创建逻辑备

[20160222]Oracle 11G Data Guard Failover

[20160222]Oracle 11G Data Guard Failover-flush redo.txt --链接: http://blog.csdn.net/tianlesoftware/article/details/6256542 在Oracle 11g里,Data Guard 切换多了一个新的功能:flush redo. Flush 能把没有发送的redo 从主库传送到standby库. 只要主库能启动到mount 状态,那么Flush 就可以把没有发送的归档和current on

基于同一主机配置 Oracle 11g Data Guard

       Oracle Data Guard 为企业数据库提供了最有效和最全面的数据可用性.数据保护和灾难恢复解决方案.它集成管理.监视和自动化软件基础架构来创建和维护一个或多个同步备用数据库,从而保护数据不受故障.灾难.错误和损坏的影响.本文主要描述了在同一主机下如何配置Oracle Data Guard.               有关DG的相关概念,可参考:Oracle Data Guard Concepts and Administration        有关配置DG的参数描述

【DataGuard】11g 新特性：Active Data Guard

  在Oracle 11g之前,物理备库(physical Standby)在应用redo的时候,是不可以打开的,只可以mount.从11g开始,在应用redo的时候,物理备库可以处于read-only模式,这就称为Active Data Guard .通过Active Data Guard,可以在物理备库进行查询或者导出数据,从而减少对主库的访问和压力. Active Data Guard适用于一些只读性的应用,比如,有的应用程序只是查询数据,进行一些报表业务,不会产生redo数据,这些应用可

搜狐畅游高级DBA：Data Guard运维中的实战经验和技巧

本次分享由以下几个部分组成: Data Guard的灾备介绍 备库的设计方案考虑 备库敏感的几个数据文件类操作 对Switchover和Failover的建议 SQL审核之Snapshot Standby Data Guard搭建/重建的小技巧   写在前面 之前有一个朋友很有深意的问我Data Guard和RAC哪个更重要,前提是在高可用和容灾两者之间来选择,只能选其一,我是毫不犹豫选择Data Guard,毕竟这是数据安全的基本防线,我想Data Guard的命名(中文翻译为数据卫士)也是这

DG8——有关Oracle Data Guard Failover 的说明

原文转自:http://blog.csdn.net/tianlesoftware/article/details/6256542 在之前的两篇文章里都对oracle Data Guard的Failover 进行了说明,但是没有个系统的说明,所以在这篇把DG的Failover 做个系统的说明.          物理Data Guard 下Failover 时Redo 的处理问题        http://blog.csdn.net/tianlesoftware/archive/2010/11/

从摆脱Data Guard手工搭建及维护的烦恼说起

讲师介绍  杨建荣 搜狐畅游高级DBA   DBAplus社群联合发起人.现就职于搜狐畅游,Oracle ACE-A.YEP成员,超7年数据库开发和运维经验,擅长电信数据业务.数据库迁移和性能调优. 持Oracle 10G OCP,OCM,MySQL OCP认证,<Oracle DBA工作笔记>作者.   本次分享将分为以下几部分: 半自动化搭建Data Guard 用不用DG Broker 几个实用场景演练 与时俱进:Oracle 12c Data Guard改进 诊断案例:备库批量查询失败

Data Guard Broker Properties

Data Guard Broker Properties This chapter describes the various configuration and database properties that help you view and control the behavior of entire broker configurations, individual databases, redo transport services, and log apply services.