Oracle DataGuard switchover切換一例

liupzmin發表於2016-02-16
Oracle DataGuard switchover切換一例

做DG的切換測試,發現了一些有趣的小問題,寥作記錄

1.主庫當時歸檔日誌狀態


2.備庫當時歸檔日誌狀態

3.此時進行切換

主庫的alert日誌


  1. Tue Feb 16 14:55:15 2016
  2. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
  3. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5441] (minstd)
  4. Waiting for all non-current ORLs to be archived...
  5. All non-current ORLs have been archived.
  6. Waiting for all FAL entries to be archived...
  7. All FAL entries have been archived.
  8. Waiting for potential Physical Standby switchover target to become synchronized...
  9. Active, synchronized Physical Standby switchover target has been identified
  10. Switchover End-Of-Redo Log thread 1 sequence 85 has been fixed
  11. Switchover: Primary highest seen SCN set to 0x0.0x101743
  12. ARCH: Noswitch archival of thread 1, sequence 85
  13. ARCH: End-Of-Redo Branch archival of thread 1 sequence 85
  14. ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
  15. ARCH: Standby redo logfile selected for thread 1 sequence 85 for destination LOG_ARCHIVE_DEST_2
  16. Archived Log entry 17 added for thread 1 sequence 85 ID 0x97bd9e7c dest 1:
  17. ARCH: Archiving is disabled due to current logfile archival
  18. Primary will check for some target standby to have received alls redo
  19. Final check for a synchronized target standby. Check will be made once.
  20. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
  21. Active, synchronized target has been identified
  22. Target has also received all redo
這時主庫做了一次日誌切換,加入EOR標記,並傳輸日誌到所有備庫,然後檢查確認所有備庫全部接受到所有的redo

這時查詢備庫



85號日誌確實已經收到並且應用,備庫alert如下

  1. Tue Feb 16 14:52:22 2016
  2. Archived Log entry 19 added for thread 1 sequence 84 ID 0x97bd9e7c dest 1:
  3. Media Recovery Waiting for thread 1 sequence 85 (in transit)
  4. Recovery of Online Redo Log: Thread 1 Group 4 Seq 85 Reading mem 0
  5. Mem# 0: /u01/app/oradata/min/stdb_redo01.log
  6. Tue Feb 16 14:55:17 2016
  7. RFS[5]: Assigned to RFS process 7837
  8. RFS[5]: Selected log 4 for thread 1 sequence 85 dbid -1749202365 branch 903912515
  9. Tue Feb 16 14:55:17 2016
  10. Archived Log entry 20 added for thread 1 sequence 85 ID 0x97bd9e7c dest 1:
  11. Tue Feb 16 14:55:17 2016
  12. RFS[2]: Possible network disconnect with primary database
  13. Tue Feb 16 14:55:17 2016
  14. RFS[6]: Assigned to RFS process 7782
  15. RFS[6]: Possible network disconnect with primary database
  16. Tue Feb 16 14:55:17 2016
  17. RFS[1]: Possible network disconnect with primary database
  18. Tue Feb 16 14:55:17 2016
  19. RFS[7]: Assigned to RFS process 7835
  20. RFS[7]: Possible network disconnect with primary database
  21. Tue Feb 16 14:55:17 2016
  22. Resetting standby activation ID 2545786492 (0x97bd9e7c)
  23. Media Recovery End-Of-Redo indicator encountered
  24. Media Recovery Continuing
  25. Media Recovery Waiting for thread 1 sequence 86
收到85號日誌之後,與主庫失去聯絡,在應用85號日誌的時候遇到EOR標記

備庫切換為主庫


新主庫alert

  1. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
  2. ALTER DATABASE SWITCHOVER TO PRIMARY (min)
  3. Maximum wait for role transition is 15 minutes.
  4. All dispatchers and shared servers shutdown
  5. CLOSE: killing server sessions.
  6. CLOSE: all sessions shutdown successfully.
  7. Tue Feb 16 15:00:29 2016
  8. SMON: disabling cache recovery
  9. Backup controlfile written to trace file /home/oracle/app/oracle/diag/rdbms/min/min/trace/min_ora_7726.trc
  10. SwitchOver after complete recovery through change 1054531
  11. Online log /u01/app/oradata/min/redo01.log: Thread 1 Group 1 was previously cleared
  12. Online log /u01/app/oradata/min/redo02.log: Thread 1 Group 2 was previously cleared
  13. Online log /u01/app/oradata/min/redo03.log: Thread 1 Group 3 was previously cleared
  14. Standby became primary SCN: 1054529
  15. AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
  16. Switchover: Complete - Database mounted as primary
  17. Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
  18. Tue Feb 16 15:00:42 2016
  19. alter database open
  20. Tue Feb 16 15:00:42 2016
  21. Assigning activation ID 2545831887 (0x97be4fcf)
  22. Thread 1 advanced to log sequence 87 (thread open)
  23. Tue Feb 16 15:00:42 2016
  24. ARC8: Becoming the 'no SRL' ARCH
  25. Thread 1 opened at log sequence 87
  26. Current log# 2 seq# 87 mem# 0: /u01/app/oradata/min/redo02.log
  27. Successful open of redo thread 1
  28. MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
  29. Tue Feb 16 15:00:42 2016
  30. ARC9: Becoming the 'no SRL' ARCH
  31. Tue Feb 16 15:00:42 2016
  32. ARCa: Becoming the 'no SRL' ARCH
  33. Tue Feb 16 15:00:42 2016
  34. SMON: enabling cache recovery
  35. Archived Log entry 21 added for thread 1 sequence 86 ID 0x97be4fcf dest 1:
  36. Tue Feb 16 15:00:42 2016
  37. ARCt: Becoming the 'no SRL' ARCH
  38. Tue Feb 16 15:00:42 2016
  39. NSA2 started with pid=18, OS id=7850
  40. [7726] Successfully onlined Undo Tablespace 2.
  41. Undo initialization finished serial:0 start:86759444 end:86759474 diff:30 (0 seconds)
  42. Dictionary check beginning
  43. Dictionary check complete
  44. Verifying file header compatibility for 11g tablespace encryption..
  45. Verifying 11g file header compatibility for tablespace encryption completed
  46. SMON: enabling tx recovery
  47. Database Characterset is ZHS16GBK
  48. Starting background process SMCO
  49. Tue Feb 16 15:00:42 2016
  50. idle dispatcher 'D000' terminated, pid = (17, 1)
  51. ARCt: Standby redo logfile selected for thread 1 sequence 86 for destination LOG_ARCHIVE_DEST_2
  52. Tue Feb 16 15:00:42 2016
  53. SMCO started with pid=50, OS id=7852
  54. No Resource Manager plan active
  55. Starting background process QMNC
  56. Tue Feb 16 15:00:42 2016
  57. QMNC started with pid=52, OS id=7856
  58. LOGSTDBY: Validating controlfile with logical metadata
  59. LOGSTDBY: Validation complete
  60. Completed: alter database open
  61. Tue Feb 16 15:00:43 2016
  62. ARC0: Becoming the 'no SRL' ARCH
  63. Tue Feb 16 15:00:43 2016
  64. ARC1: Becoming the 'no SRL' ARCH
  65. ARC0: Archive log rejected (thread 1 sequence 86) at host 'minstd'
  66. FAL[server, ARC0]: FAL archive failed, see trace file.
  67. ARCH: FAL archive failed. Archiver continuing
  68. ORACLE Instance min - Archival Error. Archiver continuing.
  69. Thread 1 advanced to log sequence 88 (LGWR switch)
  70. Current log# 3 seq# 88 mem# 0: /u01/app/oradata/min/redo03.log
  71. Tue Feb 16 15:00:45 2016
  72. ARC2: Becoming the 'no SRL' ARCH
  73. Archived Log entry 23 added for thread 1 sequence 87 ID 0x97be4fcf dest 1:
  74. Tue Feb 16 15:00:45 2016
  75. ARC3: Becoming the 'no SRL' ARCH
  76. ARC3: Standby redo logfile selected for thread 1 sequence 87 for destination LOG_ARCHIVE_DEST_2
  77. ******************************************************************
  78. LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
  79. ******************************************************************
  80. LNS: Standby redo logfile selected for thread 1 sequence 88 for destination LOG_ARCHIVE_DEST_2
  81. Tue Feb 16 15:01:05 2016
  82. ARCr: Becoming the 'no SRL' ARCH
新主庫開啟的時候向主庫傳輸86號日誌被拒絕,這裡我猜測是一個gap檢測的問題,至於為何報錯,暫時沒搞清楚,我覺得出現了FAL應該是備庫檢測到gap來請求日誌的,但為何拒絕不甚清楚,而在新備庫的日誌卻發現是接受成功的


  1. Tue Feb 16 15:00:42 2016
  2. Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/archive
  3. RFS[1]: Assigned to RFS process 5901
  4. RFS[1]: Selected log 4 for thread 1 sequence 86 dbid -1749202365 branch 903912515
  5. Tue Feb 16 15:00:42 2016
  6. Archived Log entry 19 added for thread 1 sequence 86 ID 0x97be4fcf dest 1:
  7. Tue Feb 16 15:00:45 2016
  8. RFS[2]: Assigned to RFS process 5905
  9. RFS[2]: Selected log 4 for thread 1 sequence 87 dbid -1749202365 branch 903912515
  10. Tue Feb 16 15:00:45 2016
  11. Archived Log entry 20 added for thread 1 sequence 87 ID 0x97be4fcf dest 1:
  12. Tue Feb 16 15:00:45 2016
  13. Primary database is in MAXIMUM PERFORMANCE mode
  14. RFS[3]: Assigned to RFS process 5907
  15. RFS[3]: Selected log 4 for thread 1 sequence 88 dbid -1749202365 branch 903912515
  16. Tue Feb 16 15:01:36 2016
  17. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect
  18. Attempt to start background Managed Standby Recovery process (minstd)
  19. Tue Feb 16 15:01:36 2016
  20. MRP0 started with pid=55, OS id=5923
  21. MRP0: Background Managed Standby Recovery process started (minstd)
  22. started logmerger process
可見86,87已經接收到了

下一刻便開始正常傳輸了,主庫日誌資訊如下:

  1. Tue Feb 16 15:00:45 2016
  2. ARC3: Becoming the 'no SRL' ARCH
  3. ARC3: Standby redo logfile selected for thread 1 sequence 87 for destination LOG_ARCHIVE_DEST_2
  4. ******************************************************************
  5. LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
  6. ******************************************************************
  7. LNS: Standby redo logfile selected for thread 1 sequence 88 for destination LOG_ARCHIVE_DEST_2
  8. Tue Feb 16 15:01:05 2016
  9. ARCr: Becoming the 'no SRL' ARCH
有一個歸檔目的地2啟用的提示,難道是一開始未啟用?

此時再來看一下歸檔日誌資訊
新主庫:

備庫歸檔目的地86,97兩個日誌的FAL都是yes,而且在新主庫檢視切換期間產生的84,85兩個日誌的applied狀態為yes,而在新備庫(原主庫)查詢84,85在新主庫(原備庫)的applied狀態卻為NO,很有意思的一個現象

新備庫:


總結:

1.switchover前後每個主庫角色都會切換1次日誌(本次實驗為準,並不絕對)
2.新主庫產生的前2個日誌是以FAL方式傳輸到備庫
3.在原主庫查詢switcover之前產生的兩個日誌的applied狀態時為NO,而在新的主庫(原備庫)查詢日誌的應用狀態是為YES的

關於本文中提到的FAL報錯的問題,希望廣大DBA朋友幫助解惑,如文中還有其他錯誤之處,還望批評指正

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

相關文章