DNS 引起經典RAC故障

lhrbest發表於2016-12-19
DNS

DNS 引起經典RAC故障

作者：吳偉龍(PrudentWoo)


一、環境介紹：

這是一套四年前部署的RAC系統，之前執行一直很好，沒有出過問題，平時基本處於無人管的狀態。

OS:Redhat EnterPrise Linux 5.8 x86_x64

DB:Oracle Database EnterPrise  11.2.0.4

GI:Oracle Grid Infrastructure 11.2.0.4


二、問題描述：

         昨天臨近下班接到現場人員故障請求，描述為資料庫無法連線，報ORA-12547:TNS: lost  CONNECT。當時第一反應是網路和監聽故障，讓現場人員進行tnsping和ping都是正常的。


三、問題現象：

         我到達現場後，首先檢視了資料庫的狀態，發現資料庫例項是停止執行狀態，並且從日誌中看不出明顯報錯；

      資料庫日誌：

  1. Starting up:
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  3. With the Partitioning, Real Application Clusters, OLAP, Data Mining
  4. and Real Application Testing options.
  5. ORACLE_HOME = /u01/app/oracle/11.2.0.4/product/db_1
  6. System name:    Linux
  7. Node name:    db01
  8. Release:    3.8.13-44.1.1.el6uek.x86_64
  9. Version:    #2 SMP Wed Sep 10 06:10:25 PDT 2014
  10. Machine:    x86_64
  11. VM name:    VMWare Version: 6
  12. Using parameter settings in server-side pfile /u01/app/oracle/11.2.0.4/product/db_1/dbs/initwoo1.ora
  13. System parameters with non-default values:
  14.   processes = 600
  15.   sessions = 922
  16.   spfile = "+DATA/woo/spfilewoo.ora"
  17.   nls_language = "SIMPLIFIED CHINESE"
  18.   nls_territory = "CHINA"
  19.   memory_target = 1584M
  20.   control_files = "+DATA/woo/controlfile/current.260.930748953"
  21.   control_files = "+FRA01/woo/controlfile/current.256.930748953"
  22.   db_block_size = 8192
  23.   compatible = "11.2.0.4.0"
  24.   cluster_database = TRUE
  25.   db_create_file_dest = "+DATA"
  26.   db_recovery_file_dest = "+FRA01"
  27.   db_recovery_file_dest_size= 4407M
  28.   thread = 1
  29.   undo_tablespace = "UNDOTBS1"
  30.   instance_number = 1
  31.   remote_login_passwordfile= "EXCLUSIVE"
  32.   db_domain = ""
  33.   dispatchers = "(PROTOCOL=TCP) (SERVICE=wooXDB)"
  34.   remote_listener = "scan.prudentwoo.com:1521"
  35.   audit_file_dest = "/u01/app/oracle/admin/woo/adump"
  36.   audit_trail = "DB"
  37.   db_name = "woo"
  38.   open_cursors = 300
  39.   diagnostic_dest = "/u01/app/oracle"
  40. Cluster communication is configured to use the following interface(s) for this instance
  41.   169.254.51.38
  42.   169.254.243.157
  43. cluster interconnect IPC version:Oracle UDP/IP (generic)
  44. IPC Vendor 1 proto 2
  45. Fri Dec 16 15:24:55 2016
  46. USER (ospid: 4044): terminating the instance due to error 119
  47. Instance terminated by USER, pid = 4044

     資料庫狀態：

  1. [oracle@db01 ~]$ crsctl status res -t
  2. --------------------------------------------------------------------------------
  3. NAME TARGET STATE SERVER STATE_DETAILS
  4. --------------------------------------------------------------------------------
  5. Local Resources
  6. --------------------------------------------------------------------------------
  7. ora.BAK01.dg
  8.                ONLINE ONLINE db01
  9.                ONLINE ONLINE db02
  10. ora.DATA.dg
  11.                ONLINE ONLINE db01
  12.                ONLINE ONLINE db02
  13. ora.FRA01.dg
  14.                ONLINE ONLINE db01
  15.                ONLINE ONLINE db02
  16. ora.LISTENER.lsnr
  17.                ONLINE ONLINE db01
  18.                ONLINE ONLINE db02
  19. ora.OCR_VOT.dg
  20.                ONLINE ONLINE db01
  21.                ONLINE ONLINE db02
  22. ora.asm
  23.                ONLINE ONLINE db01 Started
  24.                ONLINE ONLINE db02 Started
  25. ora.gsd
  26.                OFFLINE OFFLINE db01
  27.                OFFLINE OFFLINE db02
  28. ora.net1.network
  29.                ONLINE ONLINE db01
  30.                ONLINE ONLINE db02
  31. ora.ons
  32.                ONLINE ONLINE db01
  33.                ONLINE ONLINE db02
  34. --------------------------------------------------------------------------------
  35. Cluster Resources
  36. --------------------------------------------------------------------------------
  37. ora.LISTENER_SCAN1.lsnr
  38.       1 ONLINE ONLINE db02
  39. ora.LISTENER_SCAN2.lsnr
  40.       1 ONLINE ONLINE db01
  41. ora.LISTENER_SCAN3.lsnr
  42.       1 ONLINE ONLINE db01
  43. ora.cvu
  44.       1 ONLINE ONLINE db01
  45. ora.db01.vip
  46.       1 ONLINE ONLINE db01
  47. ora.db02.vip
  48.       1 ONLINE ONLINE db02
  49. ora.oc4j
  50.       1 ONLINE ONLINE db01
  51. ora.scan1.vip
  52.       1 ONLINE ONLINE db02
  53. ora.scan2.vip
  54.       1 ONLINE ONLINE db01
  55. ora.scan3.vip
  56.       1 ONLINE ONLINE db01
  57. ora.woo.db
  58.       1 ONLINE OFFLINE Instance Shutdown
  59.       2 ONLINE OFFLINE Instance Shutdown
  1. [oracle@db01 ~]$ srvctl status database -d woo
  2. Instance woo1 is not running on node db01
  3. Instance woo2 is not running on node db02

四、手工帶起資料庫：

  1. [oracle@db01 trace]$ srvctl start database -d woo
  2. PRCR-1079 : Failed to start resource ora.woo.db
  3. CRS-5017: The resource action "ora.woo.db start" encountered the following error:
  4. ORA-00119: invalid specification for system parameter REMOTE_LISTENER
  5. ORA-00132: syntax error or unresolved network name 'scan.prudentwoo.com:1521'
  6. . For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4/product/grid/log/db02/agent/crsd/oraagent_oracle/oraagent_oracle.log".

  8. CRS-5017: The resource action "ora.woo.db start" encountered the following error:
  9. ORA-00119: invalid specification for system parameter REMOTE_LISTENER
  10. ORA-00132: syntax error or unresolved network name 'scan.prudentwoo.com:1521'
  11. . For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4/product/grid/log/db01/agent/crsd/oraagent_oracle/oraagent_oracle.log".

  13. CRS-2674: Start of 'ora.woo.db' on 'db02' failed
  14. CRS-2674: Start of 'ora.woo.db' on 'db01' failed
  15. CRS-2632: There are no more servers to try to place resource 'ora.woo.db' on that would satisfy its placement policy

    日誌資訊：

  1. alert.log:
  2. [oracle@db01 trace]$ tail -0f alert_woo1.log
  3. Fri Dec 16 15:37:08 2016
  4. Starting ORACLE instance (normal)
  5. LICENSE_MAX_SESSION = 0
  6. LICENSE_SESSIONS_WARNING = 0
  7. Initial number of CPU is 2
  8. Private Interface 'eth1:1' configured from GPnP for use as a private interconnect.
  9.   [name='eth1:1', type=1, ip=169.254.51.38, mac=00-0c-29-7c-44-ca, net=169.254.0.0/17, mask=255.255.128.0, use=haip:cluster_interconnect/62]
  10. Private Interface 'eth2:1' configured from GPnP for use as a private interconnect.
  11.   [name='eth2:1', type=1, ip=169.254.243.157, mac=00-0c-29-7c-44-d4, net=169.254.128.0/17, mask=255.255.128.0, use=haip:cluster_interconnect/62]
  12. Public Interface 'eth0' configured from GPnP for use as a public interface.
  13.   [name='eth0', type=1, ip=192.168.84.11, mac=00-0c-29-7c-44-c0, net=192.168.84.0/24, mask=255.255.255.0, use=public/1]
  14. Public Interface 'eth0:1' configured from GPnP for use as a public interface.
  15.   [name='eth0:1', type=1, ip=192.168.84.22, mac=00-0c-29-7c-44-c0, net=192.168.84.0/24, mask=255.255.255.0, use=public/1]
  16. Public Interface 'eth0:3' configured from GPnP for use as a public interface.
  17.   [name='eth0:3', type=1, ip=192.168.84.20, mac=00-0c-29-7c-44-c0, net=192.168.84.0/24, mask=255.255.255.0, use=public/1]
  18. Public Interface 'eth0:5' configured from GPnP for use as a public interface.
  19.   [name='eth0:5', type=1, ip=192.168.84.13, mac=00-0c-29-7c-44-c0, net=192.168.84.0/24, mask=255.255.255.0, use=public/1]
  20. CELL communication is configured to use 0 interface(s):
  21. CELL IP affinity details:
  22.     NUMA status: non-NUMA system
  23.     cellaffinity.ora status: N/A
  24. CELL communication will use 1 IP group(s):
  25.     Grp 0:
  26. Picked latch-free SCN scheme 3
  27. Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
  28. Autotune of undo retention is turned on.
  29. LICENSE_MAX_USERS = 0
  30. SYS auditing is disabled
  31. Starting up:
  32. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  33. With the Partitioning, Real Application Clusters, OLAP, Data Mining
  34. and Real Application Testing options.
  35. ORACLE_HOME = /u01/app/oracle/11.2.0.4/product/db_1
  36. System name:    Linux
  37. Node name:    db01
  38. Release:    3.8.13-44.1.1.el6uek.x86_64
  39. Version:    #2 SMP Wed Sep 10 06:10:25 PDT 2014
  40. Machine:    x86_64
  41. VM name:    VMWare Version: 6
  42. Using parameter settings in server-side pfile /u01/app/oracle/11.2.0.4/product/db_1/dbs/initwoo1.ora
  43. System parameters with non-default values:
  44.   processes = 600
  45.   sessions = 922
  46.   spfile = "+DATA/woo/spfilewoo.ora"
  47.   nls_language = "SIMPLIFIED CHINESE"
  48.   nls_territory = "CHINA"
  49.   memory_target = 1584M
  50.   control_files = "+DATA/woo/controlfile/current.260.930748953"
  51.   control_files = "+FRA01/woo/controlfile/current.256.930748953"
  52.   db_block_size = 8192
  53.   compatible = "11.2.0.4.0"
  54.   cluster_database = TRUE
  55.   db_create_file_dest = "+DATA"
  56.   db_recovery_file_dest = "+FRA01"
  57.   db_recovery_file_dest_size= 4407M
  58.   thread = 1
  59.   undo_tablespace = "UNDOTBS1"
  60.   instance_number = 1
  61.   remote_login_passwordfile= "EXCLUSIVE"
  62.   db_domain = ""
  63.   dispatchers = "(PROTOCOL=TCP) (SERVICE=wooXDB)"
  64.   remote_listener = "scan.prudentwoo.com:1521"
  65.   audit_file_dest = "/u01/app/oracle/admin/woo/adump"
  66.   audit_trail = "DB"
  67.   db_name = "woo"
  68.   open_cursors = 300
  69.   diagnostic_dest = "/u01/app/oracle"
  70. Cluster communication is configured to use the following interface(s) for this instance
  71.   169.254.51.38
  72.   169.254.243.157
  73. cluster interconnect IPC version:Oracle UDP/IP (generic)
  74. IPC Vendor 1 proto 2
  75. Fri Dec 16 15:37:49 2016
  76. USER (ospid: 6043): terminating the instance due to error 119
  77. Instance terminated by USER, pid = 6043

五、問題分析：

         我從啟動資料庫來看，發現資料庫此時無法正常啟動，並隨著報ORA-00132，日誌報error 119。

         根據啟動提示可以將問題定位到scan，因scan故障引起資料庫無法正常啟動。


六、檢查scan配置資訊：

  1. #check scan info:
  2. [oracle@db01 ~]$ srvctl config scan
  3. SCAN name: scan.prudentwoo.com, Network: 1/192.168.84.0/255.255.255.0/eth0
  4. SCAN VIP name: scan1, IP: /scan.prudentwoo.com/192.168.84.21
  5. SCAN VIP name: scan2, IP: /scan.prudentwoo.com/192.168.84.22
  6. SCAN VIP name: scan3, IP: /scan.prudentwoo.com/192.168.84.20


  9. [oracle@db01 ~]$ ping 192.168.84.20 -c 2
  10. PING 192.168.84.20 (192.168.84.20) 56(84) bytes of data.
  11. 64 bytes from 192.168.84.20: icmp_seq=1 ttl=64 time=0.032 ms
  12. 64 bytes from 192.168.84.20: icmp_seq=2 ttl=64 time=0.039 ms

  14. --- 192.168.84.20 ping statistics ---
  15. 2 packets transmitted, 2 received, 0% packet loss, time 1000ms
  16. rtt min/avg/max/mdev = 0.032/0.035/0.039/0.006 ms
  17. [oracle@db01 ~]$ ping 192.168.84.21 -c 2
  18. PING 192.168.84.21 (192.168.84.21) 56(84) bytes of data.
  19. 64 bytes from 192.168.84.21: icmp_seq=1 ttl=64 time=0.231 ms
  20. 64 bytes from 192.168.84.21: icmp_seq=2 ttl=64 time=0.292 ms

  22. --- 192.168.84.21 ping statistics ---
  23. 2 packets transmitted, 2 received, 0% packet loss, time 1001ms
  24. rtt min/avg/max/mdev = 0.231/0.261/0.292/0.034 ms
  25. [oracle@db01 ~]$ ping 192.168.84.22 -c 2
  26. PING 192.168.84.22 (192.168.84.22) 56(84) bytes of data.
  27. 64 bytes from 192.168.84.22: icmp_seq=1 ttl=64 time=0.024 ms
  28. 64 bytes from 192.168.84.22: icmp_seq=2 ttl=64 time=0.034 ms

  30. --- 192.168.84.22 ping statistics ---
  31. 2 packets transmitted, 2 received, 0% packet loss, time 999ms
  32. rtt min/avg/max/mdev = 0.024/0.029/0.034/0.005 ms
  33. [oracle@db01 ~]$ ping scan.prudentwoo.com -c 2
  34. ping: unknown host scan.prudentwoo.com


           我們可以看到，現在scan對應的三個地址都是通的，說明SCAN的服務是好的，但是ping scan所對應的域名的時候報無法找到主機，無法解析域名，那麼下一步可以定位應該是域名服務出問題了。



七、在兩臺資料庫伺服器上檢查域名(dns)服務，結果是域名伺服器沒有在這兩臺資料伺服器上：

  1. #check dns client and server:
  2. [oracle@db01 ~]$ /sbin/chkconfig --list|grep named
  3. [oracle@db01 ~]$ ssh db02 '/sbin/chkconfig --list|grep named'
  4. [oracle@db01 ~]$

  6. check dns client:
  7. [oracle@db01 ~]$ cat /etc/resolv.conf
  8. search prudentwoo.com
  9. nameserver 192.168.84.15

八、根據resolv.conf配置找到真正的域名伺服器，發現域名域名伺服器hang住：

  1. [oracle@db01 ~]$ ping 192.168.84.15 -c 2
  2. PING 192.168.84.15 (192.168.84.15) 56(84) bytes of data.
  3. From 192.168.84.11 icmp_seq=1 Destination Host Unreachable
  4. From 192.168.84.11 icmp_seq=2 Destination Host Unreachable

  6. --- 192.168.84.15 ping statistics ---
  7. 2 packets transmitted, 0 received, +2 errors, 100% packet loss, time 3007ms
  8. pipe 2

九、修復域名伺服器，現在可以正常解析：

  1. [oracle@db01 ~]$ ping scan.prudentwoo.com -c 2
  2. PING scan.prudentwoo.com (192.168.84.21) 56(84) bytes of data.
  3. 64 bytes from scan.prudentwoo.com (192.168.84.21): icmp_seq=1 ttl=64 time=0.494 ms
  4. 64 bytes from scan.prudentwoo.com (192.168.84.21): icmp_seq=2 ttl=64 time=0.289 ms

  6. --- scan.prudentwoo.com ping statistics ---
  7. 2 packets transmitted, 2 received, 0% packet loss, time 1001ms
  8. rtt min/avg/max/mdev = 0.289/0.391/0.494/0.104 ms

十、再次啟動資料庫：

  1. [oracle@db01 ~]$ srvctl start database -d woo
  2. [oracle@db01 ~]$ srvctl status database -d woo
  3. Instance woo1 is running on node db01
  4. Instance woo2 is running on node db02

  6. [oracle@db01 ~]$ srvctl config database -d woo
  7. Database unique name: woo
  8. Database name: woo
  9. Oracle home: /u01/app/oracle/11.2.0.4/product/db_1
  10. Oracle user: oracle
  11. Spfile: +DATA/woo/spfilewoo.ora
  12. Domain:
  13. Start options: open
  14. Stop options: immediate
  15. Database role: PRIMARY
  16. Management policy: AUTOMATIC
  17. Server pools: woo
  18. Database instances: woo1,woo2
  19. Disk Groups: DATA,FRA01
  20. Mount point paths:
  21. Services:
  22. Type: RAC
  23. Database is administrator managed

能正常啟動，故障修復。


       從整個問題的處理思路來看該故障不僅考驗解決資料庫故障能力，同時安裝，基本執行原理都有考察到，當然考驗更多的應該還是和DNS服務的深入理解。

      當然我是很慶幸的，出於職業敏感度，一堆報錯中瞬間發現問題根源ORA-00132，而沒有從其它報錯資訊入手。








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

相關文章