ITPub博客

首页 > 数据库 > Oracle > 【DATAGUARD】物理dg配置客户端无缝切换 (八.2)--Fast-Start Failover 的配置

【DATAGUARD】物理dg配置客户端无缝切换 (八.2)--Fast-Start Failover 的配置

原创 Oracle 作者:lhrbest 时间:2015-09-29 20:48:34 0 删除 编辑

DATAGUARD】物理dg配置客户端无缝切换 (.2)--Fast-Start Failover 的配置

1.1  BLOG文档结构图

 

wpsFB06.tmp 

 

1.2  前言部分

 

1.2.1  导读

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

 Data Guard Broker 的配置

Fast-Start Failover 的配置

Oracle DataGuard 之客户端TAF 配置

④ 使用DGMGRL 来管理数据库

 物理dg管理和维护的一些sql

⑥ DataGuard 客户端特级配置

 

注意:本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

1.2.2  实验环境介绍

 

 项目

主库

dg

db 类型

单实例

单实例

db version

11.2.0.3

11.2.0.3

db 存储

FS type

FS type

ORACLE_SID

oradg11g

oradgphy

db_name

oradg11g

oradg11g

主机IP地址:

192.168.59.130

192.168.59.130

OS版本及kernel版本

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

OS hostname

rhel6_lhr

rhel6_lhr

 

 

 

1.2.3  相关参考文章链接

 

dg的系列文章参考:

【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库(一): http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库(二 ):  http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库(三 ):  http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库 (四)--添加一个物理dg节点 :http://blog.itpub.net/26736162/viewspace-1484878/

【DATAGUARD】物理dg的switchover切换(五) http://blog.itpub.net/26736162/viewspace-1753111/

【DATAGUARD】物理dg的failover切换(六)   http://blog.itpub.net/26736162/viewspace-1753130/

【DATAGUARD】物理dg在主库丢失归档文件的情况下的恢复(七)  http://blog.itpub.net/26736162/viewspace-1780863/

DATAGUARD】物理dg配置客户端无缝切换 (八.1)--Data Guard Broker 的配置:http://blog.itpub.net/26736162/viewspace-1811839/

 

1.2.4  本文简介

 

本篇blog是基于cuug的公开课内容,我自己进行实践的操作,视频可以参考:http://blog.itpub.net/26736162/viewspace-1624453/ ,简介我就不多写了,把cuug的内容直接copy过来吧,觉得还是比较有用的。

 

这个技术如果你不知道,不能算是ORACLE高手

这个技术如果你不知道,就不能说你会DataGuard

这个技术如果你不知道,......

 

本次网络课程,研究当主备库发生切换时,如何在主库启动一个service,保证客户端的连接能够继续,而且还能够继续select查询操作,而不管主备库是在哪台服务器上;同时保证新的客户连接没有任何的问题。本课程网络上的例子不多,陈老师花了将近一年的时间人肉搜索,最近才找到,急不可待的要分享给大家。

 

1DataGuard的配置(快速)

2、创建service

3、创建触发器

4、主备库切换测试

 

由于内容较多,我打算分为4个章节来共享给大家,贴个图,不要奇怪,还有一个章节是实验过程中配到的问题解决。

wpsFB07.tmp 

 

本篇为第二节,Fast-Start Failover配置

 

1.3  相关知识点扫盲

 

Fast-Start Failover是建立在broker基础上的一个快速故障转换的机制,通过fast-start failover可以自动检测primary的故障,然后自动的failover到预先指定的standby上面,这样可以最大化的减少故障时间,提高数据库的可用性。

Fast-Start Failover是在broker的基础上再增加了一个单独的observer,用来监控primarystandby数据库的状态,一旦primary不可用,observer就会自动的切换到指定的standby上面。

FAST-START FAILOVER是ORACLE10G的一项新功能。这个功能可以实现当主库宕机时,预定的从库自动快速可靠地进行失败切换(FAILOVER)。切换完成之后,原来的主库恢复正常之后,将会自动地配置为从库。这的确是一项令DBA心动的功能,大大减少了DBA的维护和管理工作。尤其是减少了在出现突然问题时的心慌意乱和手忙脚乱。

 

 

1.4  实验部分

 

1.4.1  实验目标

 

Fast-Start Failover 配置并完成实验。

 

 

1.4.2  前提准备条件

1.4.2.1  primary standby 启用flashback database

在主备库上开启闪回功能,否则后续报错16651

[oracle@rhel6_lhr lhr]$ oerr ora 16651

16651, 0000, "requirements not met for enabling fast-start failover"

// *Cause:  The attempt to enable fast-start failover could not be completed

//          because one or more requirements were not met:

//          - The Data Guard configuration must be in either MaxAvailability

//            or MaxPerformance protection mode.

//          - The LogXptMode property for both the primary database and

//            the fast-start failover target standby database must be

//            set to SYNC if the configuration protection mode is set to

//            MaxAvailability mode.

//          - The LogXptMode property for both the primary database and

//            the fast-start failover target standby database must be

//            set to ASYNC if the configuration protection mode is set to

//            MaxPerformance mode.

//          - The primary database and the fast-start failover target standby

//            database must both have flashback enabled.

//          - No valid target standby database was specified in the primary

//            database FastStartFailoverTarget property prior to the attempt

//            to enable fast-start failover, and more than one standby

//            database exists in the Data Guard configuration.

// *Action: Retry the command after correcting the issue:

//          - Set the Data Guard configuration to either MaxAvailability

//            or MaxPerformance protection mode.

//          - Ensure that the LogXptMode property for both the primary

//            database and the fast-start failover target standby database

//            are set to SYNC if the configuration protection mode is set to

//            MaxAvailability.

//          - Ensure that the LogXptMode property for both the primary

//            database and the fast-start failover target standby database

//            are set to ASYNC if the configuration protection mode is set to

//            MaxPerformance.

//          - Ensure that both the primary database and the fast-start failover

//            target standby database have flashback enabled.

//          - Set the primary database FastStartFailoverTarget property to

//            the DB_UNIQUE_NAME value of the desired target standby database

//            and the desired target standby database FastStartFailoverTarget

//            property to the DB_UNIQUE_NAME value of the primary database.

主库:

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 09:50:17 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

09:50:17 SQL> set line 9999

09:50:18 SQL> col name format a10

col FS_FAILOVER_OBSERVER_HOST format a20

09:50:18 SQL> col DB_UNIQUE_NAME format a10

09:50:18 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_ CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradg11g       2544025 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE           TO STANDBY

 

已用时间:  00: 00: 00.01

09:50:18 SQL> SELECT d.DBID,

09:50:18   2         d.DB_UNIQUE_NAME,

09:50:18   3         d.FORCE_LOGGING,

09:50:18   4         d.FLASHBACK_ON,

09:50:18   5         d.FS_FAILOVER_STATUS,

09:50:18   6         d.FS_FAILOVER_CURRENT_TARGET,

09:50:18   7         d.FS_FAILOVER_THRESHOLD,

09:50:18   8         d.FS_FAILOVER_OBSERVER_PRESENT,

09:50:18   9         d.FS_FAILOVER_OBSERVER_HOST

09:50:18  10    FROM v$database d;

 

      DBID DB_UNIQUE_ FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- ---------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g   YES YES                DISABLED                                                                  0

 

已用时间:  00: 00: 00.01

09:50:24 SQL>

 

备库:

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:18:39 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

10:18:39 SQL> set line 9999

10:19:02 SQL> col name format a10

10:19:02 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:19:02 SQL> col DB_UNIQUE_NAME format a15

10:19:02 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

SELECT d.DBID,

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradgphy            2545958 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

已用时间:  00: 00: 00.00

10:19:02 SQL> 10:19:02   2         d.DB_UNIQUE_NAME,

10:19:02   3         d.FORCE_LOGGING,

10:19:02   4         d.FLASHBACK_ON,

10:19:02   5         d.FS_FAILOVER_STATUS,

10:19:02   6         d.FS_FAILOVER_CURRENT_TARGET,

10:19:02   7         d.FS_FAILOVER_THRESHOLD,

10:19:02   8         d.FS_FAILOVER_OBSERVER_PRESENT,

10:19:02   9         d.FS_FAILOVER_OBSERVER_HOST

10:19:02  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy        YES NO                 DISABLED                                                                  0

 

已用时间:  00: 00: 00.00

10:19:02 SQL> alter database flashback on;

alter database flashback on

*

第 1 行出现错误:

ORA-01153: 激活了不兼容的介质恢复

 

 

已用时间:  00: 00: 00.00

10:19:18 SQL> alter database recover managed standby database cancel;

 

数据库已更改。

 

已用时间:  00: 00: 01.01

10:19:34 SQL> alter database flashback on;

 

数据库已更改。

 

已用时间:  00: 00: 01.40

10:19:38 SQL> set line 9999

10:19:53 SQL> col name format a10

10:19:53 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:19:53 SQL> col DB_UNIQUE_NAME format a15

10:19:53 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradgphy            2545994 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY            NOT ALLOWED

 

已用时间:  00: 00: 00.00

10:19:53 SQL> SELECT d.DBID,

10:19:53   2         d.DB_UNIQUE_NAME,

10:19:53   3         d.FORCE_LOGGING,

10:19:53   4         d.FLASHBACK_ON,

10:19:53   5         d.FS_FAILOVER_STATUS,

10:19:53   6         d.FS_FAILOVER_CURRENT_TARGET,

10:19:53   7         d.FS_FAILOVER_THRESHOLD,

10:19:53   8         d.FS_FAILOVER_OBSERVER_PRESENT,

10:19:53   9         d.FS_FAILOVER_OBSERVER_HOST

10:19:53  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy        YES YES                DISABLED                                                                  0

 

已用时间:  00: 00: 00.00

10:19:53 SQL>

 

 

1.4.2.2  确保broker配置为运行在MAX Availability模式

 

确保broker已经配置,同时运行模式为最大可用模式或者最大性能模式如果数据库运行模式为最大可用模式确保参数LogXptMode 配置为SYNC,如果是最大性能模式,则参数LogXptMode 应该为ASYNC

 


[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright (c) 2000, 2009, Oracle. All rights reserved.

 

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> show configuration

 

配置 - fsf_oradg11g_lhr

 

  保护模式:       MaxPerformance

  数据库:

    oradg11g - 主数据库

    oradgphy - 物理备用数据库

 

快速启动故障转移: DISABLED

 

配置状态:

SUCCESS

 

DGMGRL> show resource verbose 'oradg11g'  logxptmode on site 'oradg11g';

  LogXptMode = 'ASYNC'

DGMGRL> show resource verbose 'oradgphy' logxptmode on site 'oradgphy';

  LogXptMode = 'ASYNC'

DGMGRL> alter resource 'oradg11g' set property logxptmode='SYNC';

已更新属性 "logxptmode"

DGMGRL> alter resource 'oradgphy' set property logxptmode='SYNC';

已更新属性 "logxptmode"

DGMGRL> edit configuration set protection mode as maxavailability;

成功。

DGMGRL> show configuration

 

配置 - fsf_oradg11g_lhr

 

  保护模式:        MaxAvailability

  数据库:

    oradg11g - 主数据库

    oradgphy - 物理备用数据库

 

快速启动故障转移: DISABLED

 

配置状态:

SUCCESS

 

DGMGRL>  show resource verbose 'oradg11g'  logxptmode on site 'oradg11g';

  LogXptMode = 'SYNC'

DGMGRL>  show resource verbose 'oradgphy' logxptmode on site 'oradgphy';

  LogXptMode = 'SYNC'

DGMGRL>

 

 

主库告警日志:

 

Tue Sep 29 10:31:27 2015

ALTER SYSTEM SET log_archive_dest_2='service="tns_oradgphy_dgmgrl"','LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="oradgphy" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;

ALTER SYSTEM SWITCH ALL LOGFILE start (oradg11g)

Tue Sep 29 10:31:27 2015

Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED

Tue Sep 29 10:31:27 2015

NSS2 started with pid=37, OS id=46913

LGWR: Standby redo logfile selected for thread 1 sequence 160 for destination LOG_ARCHIVE_DEST_2

ALTER SYSTEM SWITCH ALL LOGFILE complete (oradg11g)

Thread 1 advanced to log sequence 160 (LGWR switch)

  Current log# 2 seq# 160 mem# 0: /u01/app/oracle/oradata/oradg11g/redo02.log

Tue Sep 29 10:31:30 2015

Archived Log entry 522 added for thread 1 sequence 159 ID 0x5495fd70 dest 1:

Tue Sep 29 10:31:31 2015

ARC3: Archive log rejected (thread 1 sequence 159) at host 'tns_oradgphy_dgmgrl'

FAL[server, ARC3]: FAL archive failed, see trace file.

ARCH: FAL archive failed. Archiver continuing

ORACLE Instance oradg11g - Archival Error. Archiver continuing.

Tue Sep 29 10:31:44 2015

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

ALTER SYSTEM ARCHIVE LOG

Tue Sep 29 10:31:45 2015

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

LGWR: Standby redo logfile selected to archive thread 1 sequence 161

LGWR: Standby redo logfile selected for thread 1 sequence 161 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 161 (LGWR switch)

  Current log# 3 seq# 161 mem# 0: /u01/app/oracle/oradata/oradg11g/redo03.log

Archived Log entry 525 added for thread 1 sequence 160 ID 0x5495fd70 dest 1:

 

 

备库告警日志:

Tue Sep 29 10:31:30 2015

Primary database is in MAXIMUM PERFORMANCE mode

RFS[4]: Assigned to RFS process 46919

RFS[4]: Selected log 5 for thread 1 sequence 160 dbid 1403587593 branch 886695024

Tue Sep 29 10:31:30 2015

Archived Log entry 148 added for thread 1 sequence 159 ID 0x5495fd70 dest 1:

Tue Sep 29 10:31:31 2015

Media Recovery Waiting for thread 1 sequence 160 (in transit)

Recovery of Online Redo Log: Thread 1 Group 5 Seq 160 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo05.log

Tue Sep 29 10:31:44 2015

Archived Log entry 149 added for thread 1 sequence 160 ID 0x5495fd70 dest 1:

Tue Sep 29 10:31:44 2015

Media Recovery Waiting for thread 1 sequence 161

Tue Sep 29 10:31:45 2015

Primary database is in MAXIMUM AVAILABILITY mode

Changing standby controlfile to MAXIMUM AVAILABILITY mode

Standby controlfile consistent with primary

RFS[5]: Assigned to RFS process 46931

RFS[5]: Selected log 4 for thread 1 sequence 161 dbid 1403587593 branch 886695024

Recovery of Online Redo Log: Thread 1 Group 4 Seq 161 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo04.log

 

 

---从数据库层次查看配置情况是否修改:

主库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:43:18 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

10:43:18 SQL>  show parameter log_archive_dest_2

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      service="tns_oradgphy_dgmgrl",

                                                  LGWR SYNC AFFIRM delay=0 opti

                                                 onal compression=disable max_f

                                                 ailure=0 max_connections=1 reo

                                                 pen=300 db_unique_name="oradgp

                                                 hy" net_timeout=30, valid_for=

                                                 (all_logfiles,primary_role)

log_archive_dest_20                  string

log_archive_dest_21                  string

log_archive_dest_22                  string

log_archive_dest_23                  string

log_archive_dest_24                  string

log_archive_dest_25                  string

log_archive_dest_26                  string

log_archive_dest_27                  string

log_archive_dest_28                  string

log_archive_dest_29                  string

10:43:20 SQL> set line 9999

10:43:30 SQL> col name format a10

10:43:30 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:43:30 SQL> col DB_UNIQUE_NAME format a15

10:43:30 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradg11g            2547638 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY          YES READ WRITE           TO STANDBY

 

已用时间:  00: 00: 00.00

10:43:30 SQL> SELECT d.DBID,

10:43:30   2         d.DB_UNIQUE_NAME,

10:43:30   3         d.FORCE_LOGGING,

10:43:30   4         d.FLASHBACK_ON,

10:43:30   5         d.FS_FAILOVER_STATUS,

10:43:30   6         d.FS_FAILOVER_CURRENT_TARGET,

10:43:30   7         d.FS_FAILOVER_THRESHOLD,

10:43:30   8         d.FS_FAILOVER_OBSERVER_PRESENT,

10:43:30   9         d.FS_FAILOVER_OBSERVER_HOST

10:43:30  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g        YES YES                DISABLED                                                                  0

 

已用时间:  00: 00: 00.01

10:43:30 SQL>

 

备库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 10:43:53 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

10:43:53 SQL> show parameter log_archive_dest_2

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      service="tns_oradg11g_dgmgrl",

                                                  LGWR ASYNC NOAFFIRM delay=0 o

                                                 ptional compression=disable ma

                                                 x_failure=0 max_connections=1

                                                 reopen=300 db_unique_name="ora

                                                 dg11g" net_timeout=30, valid_f

                                                 or=(all_logfiles,primary_role)

log_archive_dest_20                  string

log_archive_dest_21                  string

log_archive_dest_22                  string

log_archive_dest_23                  string

log_archive_dest_24                  string

log_archive_dest_25                  string

log_archive_dest_26                  string

log_archive_dest_27                  string

log_archive_dest_28                  string

log_archive_dest_29                  string

10:43:54 SQL> set line 9999

10:44:01 SQL> col name format a10

10:44:01 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:44:01 SQL> col DB_UNIQUE_NAME format a15

10:44:01 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradgphy            2547673 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

已用时间:  00: 00: 00.00

10:44:01 SQL> SELECT d.DBID,

10:44:01   2         d.DB_UNIQUE_NAME,

10:44:01   3         d.FORCE_LOGGING,

10:44:01   4         d.FLASHBACK_ON,

10:44:01   5         d.FS_FAILOVER_STATUS,

10:44:01   6         d.FS_FAILOVER_CURRENT_TARGET,

10:44:01   7         d.FS_FAILOVER_THRESHOLD,

10:44:01   8         d.FS_FAILOVER_OBSERVER_PRESENT,

10:44:01   9         d.FS_FAILOVER_OBSERVER_HOST

10:44:01  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy        YES YES                DISABLED                                                                  0

 

已用时间:  00: 00: 00.00

10:44:01 SQL>

 

 

 

1.4.3  启动observer观察进程

选定第三台机器,安装DGMGRL,用于启动observer,这里命令为observer server配置observer server的配置tnsnames.ora文件,保证observer能正常连接到 primary standby 数据库我们测试就使用同一台机器测试。

 

新开一个单独的窗口:

[oracle@rhel6_lhr lhr]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl "start observer"

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright (c) 2000, 2009, Oracle. All rights reserved.

 

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

观察程序已启动

 

 

 

该窗口一直挂起。。。。
注意启动observer
后,DGMGRL就会阻塞在这个命令上。observer的操作信息以后会在这个窗口显示有启动就有关闭,如下:

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl "stop observer"

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright (c) 2000, 2009, Oracle. All rights reserved.

 

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"

已连接。

完成。

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl "start observer"

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright (c) 2000, 2009, Oracle. All rights reserved.

 

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"

已连接。

观察程序已启动

 

 

 

1.4.4  配置FastStartFailover

 

1.4.4.1  配置每个数据库Failover的目标。这一步是决定当数据库出问题后会自动failover目标

 

DGMGRL> edit database 'oradg11g' set property 'FastStartFailoverTarget'='oradgphy';

已更新属性 "FastStartFailoverTarget"

DGMGRL>

DGMGRL> edit database 'oradgphy' set property 'FastStartFailoverTarget'='oradg11g';

已更新属性 "FastStartFailoverTarget"

1.4.4.2  设定FastStartFailoverThreshold

这个设置是决定了primary坏了多长时间之后会执行自动的failover操作。这里设置的是30s

 

DGMGRL> edit configuration set property FastStartFailoverThreshold=30;

已更新属性 "faststartfailoverthreshold"

1.4.4.3  启用Fast-Start Failover

 

DGMGRL> ENABLE FAST_START FAILOVER;

已启用。

DGMGRL>  SHOW FAST_START FAILOVER;

 

快速启动故障转移: ENABLED

 

  阈值:             30 秒

  目标:             oradgphy

  观察程序:         rhel6_lhr

  滞后限制:         30 秒 (未使用)

  关闭主数据库:     TRUE

  自动恢复:         TRUE

 

可配置的故障转移条件

  健康状况:

    Corrupted Controlfile          YES

    Corrupted Dictionary           YES

    Inaccessible Logfile            NO

    Stuck Archiver                  NO

    Datafile Offline               YES

 

  Oracle 错误条件:

    (无)

 

DGMGRL>

 

主库告警日志:

Tue Sep 29 11:09:03 2015

Fast-Start Failover (FSFO) has been enabled between:

  Primary = "oradg11g"

  Standby = "oradgphy"

Tue Sep 29 11:09:03 2015

FSFP started with pid=42, OS id=49349

 

从告警日志可以看出,主库上启动了一个进程fsfp的进程:

[oracle@rhel6_lhr ~]$ ps -ef|grep fsfp

oracle   49349     1  0 11:09 ?        00:00:00 ora_fsfp_oradg11g

oracle   49383 43618  0 11:09 pts/1    00:00:00 grep fsfp

[oracle@rhel6_lhr ~]$

 

 

 

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradg11g_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright (c) 2000, 2009, Oracle. All rights reserved.

 

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> show configuration verbose

 

配置 - fsf_oradg11g_lhr

 

  保护模式:       MaxAvailability

  数据库:

    oradg11g - 主数据库

    oradgphy - (*) 物理备用数据库

 

  (*) 快速启动故障转移目标

 

  属性:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    FastStartFailoverLagLimit       = '30'

    CommunicationTimeout            = '180'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

 

快速启动故障转移: ENABLED

 

  阈值:             30 秒

  目标:             oradgphy

  观察程序:         rhel6_lhr

  滞后限制:         30 秒 (未使用)

  关闭主数据库:     TRUE

  自动恢复:         TRUE

 

配置状态:

SUCCESS

 

DGMGRL>

 

数据库级别查看,主库:

11:11:56 SQL> set line 9999

11:12:06 SQL> col name format a10

11:12:06 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:12:06 SQL> col DB_UNIQUE_NAME format a15

11:12:06 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradg11g            2549890 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY          YES READ WRITE           TO STANDBY

 

已用时间:  00: 00: 00.00

11:12:06 SQL> SELECT d.DBID,

11:12:06   2         d.DB_UNIQUE_NAME,

11:12:06   3         d.FORCE_LOGGING,

11:12:06   4         d.FLASHBACK_ON,

11:12:06   5         d.FS_FAILOVER_STATUS,

11:12:06   6         d.FS_FAILOVER_CURRENT_TARGET,

11:12:06   7         d.FS_FAILOVER_THRESHOLD,

11:12:06   8         d.FS_FAILOVER_OBSERVER_PRESENT,

11:12:06   9         d.FS_FAILOVER_OBSERVER_HOST

11:12:06  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g        YES YES                SYNCHRONIZED           oradgphy                                          30 YES     rhel6_lhr

 

已用时间:  00: 00: 00.00

11:12:06 SQL>

 

备库:

11:11:29 SQL> set line 9999

11:12:13 SQL> col name format a10

11:12:13 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:12:13 SQL> col DB_UNIQUE_NAME format a15

11:12:13 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradgphy            2549900 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

已用时间:  00: 00: 00.00

11:12:13 SQL> SELECT d.DBID,

11:12:13   2         d.DB_UNIQUE_NAME,

11:12:13   3         d.FORCE_LOGGING,

11:12:13   4         d.FLASHBACK_ON,

11:12:13   5         d.FS_FAILOVER_STATUS,

11:12:13   6         d.FS_FAILOVER_CURRENT_TARGET,

11:12:13   7         d.FS_FAILOVER_THRESHOLD,

11:12:14   8         d.FS_FAILOVER_OBSERVER_PRESENT,

11:12:14   9         d.FS_FAILOVER_OBSERVER_HOST

11:12:14  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy        YES YES                SYNCHRONIZED           oradgphy                                          30 YES     rhel6_lhr

 

已用时间:  00: 00: 00.01

11:12:14 SQL>

 

1.4.5  测试Fast-Start Failover 的功能

1.4.5.1  Shutdown abort 主库

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:18:18 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

11:18:18 SQL> set line 9999

11:18:19 SQL> col name format a10

11:18:19 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:18:19 SQL> col DB_UNIQUE_NAME format a15

11:18:19 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradg11g            2550295 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY          YES READ WRITE           TO STANDBY

 

已用时间:  00: 00: 00.00

11:18:19 SQL> SELECT d.DBID,

11:18:20   2         d.DB_UNIQUE_NAME,

11:18:20   3         d.FORCE_LOGGING,

11:18:20   4         d.FLASHBACK_ON,

11:18:20   5         d.FS_FAILOVER_STATUS,

11:18:20   6         d.FS_FAILOVER_CURRENT_TARGET,

11:18:20   7         d.FS_FAILOVER_THRESHOLD,

11:18:20   8         d.FS_FAILOVER_OBSERVER_PRESENT,

11:18:20   9         d.FS_FAILOVER_OBSERVER_HOST

11:18:20  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g        YES YES                SYNCHRONIZED           oradgphy                                          30 YES     rhel6_lhr

 

已用时间:  00: 00: 00.00

11:18:20 SQL> shutdown abort;

ORACLE 例程已经关闭。

11:18:27 SQL>

 

1.4.5.2  查看告警日志及server窗口

 

11:18:58.99  2015929日 星期二

正在为数据库 "oradgphy" 启动快速启动故障转移...

立即执行故障转移, 请稍候...

故障转移成功, 新的主数据库为 "oradgphy"

11:19:04.72  2015929日 星期二

 

[oracle@rhel6_lhr ~]$ dgmgrl sys/lhr@tns_oradgphy_dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

Copyright (c) 2000, 2009, Oracle. All rights reserved.

 

欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。

已连接。

DGMGRL> show configuration verbose

 

配置 - fsf_oradg11g_lhr

 

  保护模式:        MaxAvailability

  数据库:

    oradgphy - 主数据库

      警告: ORA-16817: 快速启动故障转移配置不同步

 

    oradg11g - (*) 物理备用数据库 (禁用)

      ORA-16661: 需要恢复备用数据库

 

  (*) 快速启动故障转移目标

 

  属性:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    FastStartFailoverLagLimit       = '30'

    CommunicationTimeout            = '180'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

 

快速启动故障转移: ENABLED

 

  阈值:             30 秒

  目标:             oradg11g

  观察程序:         rhel6_lhr

  滞后限制:         30 秒 (未使用)

  关闭主数据库:     TRUE

  自动恢复:         TRUE

 

配置状态:

WARNING

 

DGMGRL>

 

备库告警日志:

Tue Sep 29 11:18:26 2015

RFS[5]: Possible network disconnect with primary database

Tue Sep 29 11:18:26 2015

RFS[6]: Assigned to RFS process 46955

RFS[6]: Possible network disconnect with primary database

Tue Sep 29 11:18:26 2015

RFS[7]: Assigned to RFS process 46921

RFS[7]: Possible network disconnect with primary database

Tue Sep 29 11:18:58 2015

Attempting Fast-Start Failover because the threshold of 30 seconds has elapsed.

Tue Sep 29 11:18:59 2015

Data Guard Broker: Beginning failover

Tue Sep 29 11:18:59 2015

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Tue Sep 29 11:18:59 2015

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_46860.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 2550301

Tue Sep 29 11:18:59 2015

MRP0: Background Media Recovery process shutdown (oradgphy)

Managed Standby Recovery Canceled (oradgphy)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE

Attempt to do a Terminal Recovery (oradgphy)

Media Recovery Start: Managed Standby Recovery (oradgphy)

started logmerger process

Tue Sep 29 11:19:00 2015

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 2 slaves

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

Terminal Recovery timestamp is '09/29/2015 11:19:00'

Terminal Recovery: applying standby redo logs.

Terminal Recovery: thread 1 seq# 163 redo required

Terminal Recovery:

Recovery of Online Redo Log: Thread 1 Group 4 Seq 163 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo04.log

Identified End-Of-Redo (failover) for thread 1 sequence 163 at SCN 0xffff.ffffffff

Incomplete Recovery applied until change 2550302 time 09/29/2015 11:18:25

Media Recovery Complete (oradgphy)

Terminal Recovery: successful completion

Tue Sep 29 11:19:00 2015

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance oradgphy - Archival Error

ORA-16014: log 4 sequence# 163 not archived, no available destinations

ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo04.log'

Forcing ARSCN to IRSCN for TR 0:2550302

Attempt to set limbo arscn 0:2550302 irscn 0:2550302

Resetting standby activation ID 1419115888 (0x5495fd70)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN

ALTER DATABASE SWITCHOVER TO PRIMARY (oradgphy)

Maximum wait for role transition is 15 minutes.

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

Active process 47845 user 'oracle' program 'oracle@rhel6_lhr (TNS V1-V3)'

CLOSE: all sessions shutdown successfully.

Tue Sep 29 11:19:03 2015

SMON: disabling cache recovery

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_rsm0_45481.trc

Standby terminal recovery start SCN: 2550301

RESETLOGS after incomplete recovery UNTIL CHANGE 2550302

Online log /u01/app/oracle/oradata/oradgphy/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 2550300

Tue Sep 29 11:19:03 2015

Setting recovery target incarnation to 5

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.

Switchover: Complete - Database mounted as primary

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

ALTER DATABASE OPEN

Data Guard Broker initializing...

Tue Sep 29 11:19:03 2015

Assigning activation ID 1419206889 (0x549760e9)

LGWR: Primary database is in MAXIMUM AVAILABILITY mode

LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O

LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR

Thread 1 advanced to log sequence 2 (thread open)

Tue Sep 29 11:19:03 2015

ARC3: Becoming the 'no SRL' ARCH

ARC0: Becoming the 'no SRL' ARCH

ARC3: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch

Thread 1 opened at log sequence 2

  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/oradgphy/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

Tue Sep 29 11:19:03 2015

NSA2 started with pid=17, OS id=49982

Error 1034 received logging on to the standby

ARC3: Error 1034 Creating archive log file to 'tns_oradg11g_dgmgrl'

Archived Log entry 152 added for thread 1 sequence 1 ID 0x549760e9 dest 1:

Archiver process freed from errors. No longer stopped

Tue Sep 29 11:19:03 2015

Error 1034 received logging on to the standby

PING[ARC2]: Heartbeat failed to connect to standby 'tns_oradg11g_dgmgrl'. Error is 1034.

[45481] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:44509384 end:44509514 diff:130 (1 seconds)

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is ZHS16GBK

Starting background process SMCO

Tue Sep 29 11:19:04 2015

SMCO started with pid=18, OS id=49996

No Resource Manager plan active

Starting background process QMNC

Tue Sep 29 11:19:04 2015

QMNC started with pid=20, OS id=49998

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: ALTER DATABASE OPEN

ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='oradgphy';

ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='oradgphy';

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';

ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';

ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';

ALTER SYSTEM SET db_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='oradg11g','oradgphy' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;

Failover succeeded. Primary database is now oradgphy.

Tue Sep 29 11:19:04 2015

idle dispatcher 'D000' terminated, pid = (17, 1)

Starting background process CJQ0

Tue Sep 29 11:19:04 2015

CJQ0 started with pid=34, OS id=50027

Thread 1 advanced to log sequence 3 (LGWR switch)

  Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/oradgphy/redo03.log

ARC3: STARTING ARCH PROCESSES

Tue Sep 29 11:19:06 2015

ARC4 started with pid=26, OS id=50033

Tue Sep 29 11:19:07 2015

FSFP started with pid=35, OS id=50037

ARC4: Archival started

ARC3: STARTING ARCH PROCESSES COMPLETE

ARC3: Becoming the 'no SRL' ARCH

krsk_srl_archive_int: Enabling archival of deferred physical standby SRLs

Archived Log entry 153 added for thread 1 sequence 2 ID 0x549760e9 dest 1:

Archived Log entry 154 added for thread 1 sequence 163 ID 0x5495fd70 dest 1:

Shutting down archive processes

ARCH shutting down

ARC4: Archival stopped

Tue Sep 29 11:21:44 2015

ARC0: Becoming the 'no SRL' ARCH

Tue Sep 29 11:21:45 2015

ARC1: Becoming the 'no SRL' ARCH

 

1.4.5.3  sqlplus手动启动原主库到mount状态并观察dgmgrl的server状态

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:22:55 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

已连接到空闲例程。

 

11:22:55 SQL> startup mount;

ORACLE 例程已经启动。

 

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size             385879344 bytes

Database Buffers           20971520 bytes

Redo Buffers                8466432 bytes

数据库装载完毕。

 

 

Server 窗口:

11:24:55.93  2015年9月29日 星期二

正在为数据库 "oradg11g" 启动恢复过程...

正在恢复数据库 "oradg11g", 请稍候...

操作要求关闭实例 "oradg11g" (在数据库 "oradg11g" 上)

正在关闭实例 "oradg11g"...

ORA-01109: 数据库未打开

 

已经卸载数据库。

ORACLE 例程已经关闭。

操作要求启动实例 "oradg11g" (在数据库 "oradg11g" 上)

正在启动实例 "oradg11g"...

ORACLE 例程已经启动。

数据库装载完毕。

继续恢复数据库 "oradg11g"...

已成功恢复数据库 "oradg11g"

11:26:03.30  2015年9月29日 星期二

 

DGMGRL> show configuration verbose

 

配置 - fsf_oradg11g_lhr

 

  保护模式:        MaxAvailability

  数据库:

    oradgphy - 主数据库

    oradg11g - (*) 物理备用数据库

 

  (*) 快速启动故障转移目标

 

  属性:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    FastStartFailoverLagLimit       = '30'

    CommunicationTimeout            = '180'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

 

快速启动故障转移: ENABLED

 

  阈值:             30 秒

  目标:             oradg11g

  观察程序:         rhel6_lhr

  滞后限制:         30 秒 (未使用)

  关闭主数据库:     TRUE

  自动恢复:         TRUE

 

配置状态:

ORA-16610: 命令 "REINSTATE DATABASE oradg11g" 正在进行中

DGM-17017: 无法确定配置状态

 

DGMGRL> show configuration verbose

 

配置 - fsf_oradg11g_lhr

 

  保护模式:        MaxAvailability

  数据库:

    oradgphy - 主数据库

    oradg11g - (*) 物理备用数据库

 

  (*) 快速启动故障转移目标

 

  属性:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    FastStartFailoverLagLimit       = '30'

    CommunicationTimeout            = '180'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

 

快速启动故障转移: ENABLED

 

  阈值:             30 秒

  目标:             oradg11g

  观察程序:         rhel6_lhr

  滞后限制:         30 秒 (未使用)

  关闭主数据库:     TRUE

  自动恢复:         TRUE

 

配置状态:

SUCCESS

 

DGMGRL>

 

 

可以看到状态正常, FSF生效。

1.4.5.4  测试新的环境是否同步

11:26:56 SQL>  archive log list;

数据库日志模式            存档模式

自动存档             启用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     7

下一个存档日志序列   9

当前日志序列           9

11:31:46 SQL> set line 9999

11:31:50 SQL> col name format a10

col FS_FAILOVER_OBSERVER_HOST format a20

11:31:50 SQL> col DB_UNIQUE_NAME format a15

11:31:50 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradgphy            2551424 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY          YES READ WRITE           TO STANDBY

 

已用时间:  00: 00: 00.00

11:31:50 SQL> SELECT d.DBID,

11:31:50   2         d.DB_UNIQUE_NAME,

11:31:50   3         d.FORCE_LOGGING,

11:31:50   4         d.FLASHBACK_ON,

11:31:50   5         d.FS_FAILOVER_STATUS,

11:31:50   6         d.FS_FAILOVER_CURRENT_TARGET,

11:31:50   7         d.FS_FAILOVER_THRESHOLD,

11:31:50   8         d.FS_FAILOVER_OBSERVER_PRESENT,

11:31:50   9         d.FS_FAILOVER_OBSERVER_HOST

11:31:50  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy        YES YES                SYNCHRONIZED           oradg11g                                          30 YES     rhel6_lhr

 

已用时间:  00: 00: 00.00

已用时间:  00: 00: 00.07

11:32:56 SQL> create table lhr.testfsfdg as select * from dual;

 

表已创建。

 

已用时间:  00: 00: 00.36

11:33:05 SQL> select * from lhr.testfsfdg ;

 

D

-

X

 

已用时间:  00: 00: 00.01

11:33:15 SQL>

 

 

 

备库:

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 11:31:39 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

11:31:39 SQL> archive log list;

数据库日志模式            存档模式

自动存档             启用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     8

下一个存档日志序列   0

当前日志序列           9

11:31:41 SQL> set line 9999

11:31:55 SQL> col name format a10

11:31:55 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

11:31:55 SQL> col DB_UNIQUE_NAME format a15

11:31:55 SQL> select dbid,name, DB_UNIQUE_NAME,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

SELECT d.DBID,

 

      DBID NAME       DB_UNIQUE_NAME  CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradg11g            2551429 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

已用时间:  00: 00: 00.01

11:31:55 SQL> 11:31:55   2         d.DB_UNIQUE_NAME,

11:31:55   3         d.FORCE_LOGGING,

11:31:55   4         d.FLASHBACK_ON,

11:31:55   5         d.FS_FAILOVER_STATUS,

11:31:55   6         d.FS_FAILOVER_CURRENT_TARGET,

11:31:55   7         d.FS_FAILOVER_THRESHOLD,

11:31:55   8         d.FS_FAILOVER_OBSERVER_PRESENT,

11:31:55   9         d.FS_FAILOVER_OBSERVER_HOST

11:31:55  10    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g        YES YES                SYNCHRONIZED           oradg11g                                          30 YES     rhel6_lhr

 

已用时间:  00: 00: 00.01

11:31:55 SQL> select * from lhr.testfsfdg ;

 

D

-

X

 

已用时间:  00: 00: 00.00

11:33:21 SQL>

 

11:33:21 SQL> archive log list;

数据库日志模式            存档模式

自动存档             启用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     8

下一个存档日志序列   0

当前日志序列           9

11:35:39 SQL>

 

可以看到日志序列号已经重新开始了。

 

 

1.4.5.5  重新shutdown abort主库回到最初的oradg11g为主库,oradgphy为备库的状态

 

我们重新shutdown abort主库回到最初的oradg11g为主库,oradgphy为备库的状态,

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 9月 29 13:43:03 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

13:43:03 SQL> set line 9999

13:43:17 SQL> col name format a10

13:43:17 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

13:43:17 SQL> col DB_UNIQUE_NAME format a15

13:43:17 SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradgphy                  2550303     2575340 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY          YES READ WRITE           TO STANDBY

 

已用时间:  00: 00: 00.01

13:43:17 SQL> SELECT d.DBID,

13:43:17   2         d.DB_UNIQUE_NAME,

13:43:17   3         d.FORCE_LOGGING,

13:43:17   4         d.FLASHBACK_ON,

13:43:17   5         DATAGUARD_BROKER,

13:43:17   6         d.FS_FAILOVER_STATUS,

13:43:17   7         d.FS_FAILOVER_CURRENT_TARGET,

13:43:17   8         d.FS_FAILOVER_THRESHOLD,

13:43:17   9         d.FS_FAILOVER_OBSERVER_PRESENT,

13:43:17  10         d.FS_FAILOVER_OBSERVER_HOST

13:43:17  11    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       DATAGUAR FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradgphy        YES YES                ENABLED  SYNCHRONIZED           oradg11g                                          30 YES     rhel6_lhr

 

已用时间:  00: 00: 00.00

13:43:17 SQL> archive log list;

数据库日志模式            存档模式

自动存档             启用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     12

下一个存档日志序列   14

当前日志序列           14

13:43:21 SQL> shutdown abort;

ORACLE 例程已经关闭。

13:43:26 SQL>

 

 

 

手动启动备库到mount状态后继续查看:

13:45:15 SQL> set line 9999

13:48:27 SQL> col name format a10

13:48:27 SQL> col FS_FAILOVER_OBSERVER_HOST format a20

13:48:27 SQL> col DB_UNIQUE_NAME format a15

13:48:27 SQL> select dbid,name, DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;

 

      DBID NAME       DB_UNIQUE_NAME  RESETLOGS_CHANGE# CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS

---------- ---------- --------------- ----------------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------

1403587593 ORADG11G   oradg11g                  2575356     2575896 MAXIMUM AVAILABILITY RESYNCHRONIZATION    PRIMARY          YES READ WRITE           NOT ALLOWED

 

已用时间:  00: 00: 00.00

13:48:27 SQL> SELECT d.DBID,

13:48:27   2         d.DB_UNIQUE_NAME,

13:48:27   3         d.FORCE_LOGGING,

13:48:27   4         d.FLASHBACK_ON,

13:48:27   5         DATAGUARD_BROKER,

13:48:27   6         d.FS_FAILOVER_STATUS,

13:48:27   7         d.FS_FAILOVER_CURRENT_TARGET,

13:48:27   8         d.FS_FAILOVER_THRESHOLD,

13:48:27   9         d.FS_FAILOVER_OBSERVER_PRESENT,

13:48:27  10         d.FS_FAILOVER_OBSERVER_HOST

13:48:27  11    FROM v$database d;

 

      DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       DATAGUAR FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER

---------- --------------- --- ------------------ -------- ---------------------- ------------------------------ --------------------- ------- --------------------

1403587593 oradg11g        YES YES                ENABLED  REINSTATE REQUIRED     oradgphy                                          30 YES     rhel6_lhr

 

已用时间:  00: 00: 00.00

13:48:27 SQL> archive log list

数据库日志模式            存档模式

自动存档             启用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     1

下一个存档日志序列   3

当前日志序列           3

13:49:46 SQL>

 

至此,Fast-Start Failover 的配置及其测试完成。


 

1.5  总结

 

本篇为第二节,请查看第三节: Oracle DataGuard 之客户端TAF 配置

 

 

1.6  About Me

 





About Me

.............................................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-1811936/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号:230161599(满)、618766405

● 微信群:可加我微信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友646634621,注明添加缘由

● 于 2015-09-28 09:00~ 2015-09-29 23:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店

.............................................................................................................................................


DBA笔试面试讲解群1
DBA笔试面试讲解群2
欢迎与我联系



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1811936/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1367
  • 访问量
    8348678