【DATAGUARD】物理dg配置客戶端無縫切換 (八.2)--Fast-Start Failover 的配置

Appleses發表於2016-01-30

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寶典今日頭條號地址:

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

● QQ群號:230161599(滿)、618766405

● 微信群:可加我微信,我拉大家進群,非誠勿擾

● 聯絡我請加QQ好友646634621,註明新增緣由

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

● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解

● 版權所有,歡迎分享本文,轉載請保留出處

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

小麥苗的微店

小麥苗出版的資料庫類叢書http://blog.itpub.net/26736162/viewspace-2142121/

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

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號(xiaomaimiaolhr)及QQ群(DBA寶典),學習最實用的資料庫技術。

   小麥苗的微信公眾號      小麥苗的DBA寶典QQ群1     小麥苗的DBA寶典QQ群2        小麥苗的微店

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

【DATAGUARD】物理dg配置客戶端無縫切換 (八.2)--Fast-Start Failover 的配置
DBA筆試面試講解群1
DBA筆試面試講解群2
歡迎與我聯絡



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28628435/viewspace-1984347/,如需轉載,請註明出處,否則將追究法律責任。

相關文章