04-10 07:21 阅读 170

关注

DNS 引起经典RAC故障

DNS 引起经典RAC故障

一、环境介绍：

这是一套四年前部署的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".
   

7. 
   

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".
    

12. 
    

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
   

7. 
   

8. 
   

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
    

13. 
    

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
    

21. 
    

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
    

29. 
    

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 ~]$
   

5. 
   

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
   

5. 
   

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
   

5. 
   

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
   

5. 
   

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，而没有从其它报错信息入手。