阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

DNS 引起经典RAC故障实例及解决方法

185次阅读
没有评论

共计 10835 个字符,预计需要花费 28 分钟才能阅读完成。

一、环境介绍:

这是一套四年前部署的 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.1344.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 serverside pfile /u01/app/oracle/11.2.0.4/product/db_1/dbs/initwoo1.ora
  13. System parameters with nondefault 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. NAME TARGET STATE SERVER STATE_DETAILS
  3. Local Resources
  4. ora.BAK01.dg
  5.                ONLINE ONLINE db01
  6.                ONLINE ONLINE db02
  7. ora.DATA.dg
  8.                ONLINE ONLINE db01
  9.                ONLINE ONLINE db02
  10. ora.FRA01.dg
  11.                ONLINE ONLINE db01
  12.                ONLINE ONLINE db02
  13. ora.LISTENER.lsnr
  14.                ONLINE ONLINE db01
  15.                ONLINE ONLINE db02
  16. ora.OCR_VOT.dg
  17.                ONLINE ONLINE db01
  18.                ONLINE ONLINE db02
  19. ora.asm
  20.                ONLINE ONLINE db01 Started
  21.                ONLINE ONLINE db02 Started
  22. ora.gsd
  23.                OFFLINE OFFLINE db01
  24.                OFFLINE OFFLINE db02
  25. ora.net1.network
  26.                ONLINE ONLINE db01
  27.                ONLINE ONLINE db02
  28. ora.ons
  29.                ONLINE ONLINE db01
  30.                ONLINE ONLINE db02
  31. Cluster Resources
  32. ora.LISTENER_SCAN1.lsnr
  33.       1 ONLINE ONLINE db02
  34. ora.LISTENER_SCAN2.lsnr
  35.       1 ONLINE ONLINE db01
  36. ora.LISTENER_SCAN3.lsnr
  37.       1 ONLINE ONLINE db01
  38. ora.cvu
  39.       1 ONLINE ONLINE db01
  40. ora.db01.vip
  41.       1 ONLINE ONLINE db01
  42. ora.db02.vip
  43.       1 ONLINE ONLINE db02
  44. ora.oc4j
  45.       1 ONLINE ONLINE db01
  46. ora.scan1.vip
  47.       1 ONLINE ONLINE db02
  48. ora.scan2.vip
  49.       1 ONLINE ONLINE db01
  50. ora.scan3.vip
  51.       1 ONLINE ONLINE db01
  52. ora.woo.db
  53.       1 ONLINE OFFLINE Instance Shutdown
  54.       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. PRCR1079 : Failed to start resource ora.woo.db
  3. CRS5017: The resource action “ora.woo.db start” encountered the following error:
  4. ORA00119: invalid specification for system parameter REMOTE_LISTENER
  5. ORA00132: 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. CRS5017: The resource action “ora.woo.db start” encountered the following error:
  8. ORA00119: invalid specification for system parameter REMOTE_LISTENER
  9. ORA00132: syntax error or unresolved network name ‘scan.prudentwoo.com:1521’
  10. . For details refer to “(:CLSN00107:)” in “/u01/app/11.2.0.4/product/grid/log/db01/agent/crsd/oraagent_oracle/oraagent_oracle.log”.
  11. CRS2674: Start of ‘ora.woo.db’ on ‘db02’ failed
  12. CRS2674: Start of ‘ora.woo.db’ on ‘db01’ failed
  13. CRS2632: 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=000c297c44ca, 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=000c297c44d4, 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=000c297c44c0, 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=000c297c44c0, 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=000c297c44c0, 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=000c297c44c0, 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: nonNUMA system
  23.     cellaffinity.ora status: N/A
  24. CELL communication will use 1 IP group(s):
  25.     Grp 0:
  26. Picked latchfree 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.1344.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 serverside pfile /u01/app/oracle/11.2.0.4/product/db_1/dbs/initwoo1.ora
  43. System parameters with nondefault 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. [oracle@db01 ~]$ ping 192.168.84.20 c 2
  8. PING 192.168.84.20 (192.168.84.20) 56(84) bytes of data.
  9. 64 bytes from 192.168.84.20: icmp_seq=1 ttl=64 time=0.032 ms
  10. 64 bytes from 192.168.84.20: icmp_seq=2 ttl=64 time=0.039 ms
  11. 192.168.84.20 ping statistics
  12. 2 packets transmitted, 2 received, 0% packet loss, time 1000ms
  13. rtt min/avg/max/mdev = 0.032/0.035/0.039/0.006 ms
  14. [oracle@db01 ~]$ ping 192.168.84.21 c 2
  15. PING 192.168.84.21 (192.168.84.21) 56(84) bytes of data.
  16. 64 bytes from 192.168.84.21: icmp_seq=1 ttl=64 time=0.231 ms
  17. 64 bytes from 192.168.84.21: icmp_seq=2 ttl=64 time=0.292 ms
  18. 192.168.84.21 ping statistics
  19. 2 packets transmitted, 2 received, 0% packet loss, time 1001ms
  20. rtt min/avg/max/mdev = 0.231/0.261/0.292/0.034 ms
  21. [oracle@db01 ~]$ ping 192.168.84.22 c 2
  22. PING 192.168.84.22 (192.168.84.22) 56(84) bytes of data.
  23. 64 bytes from 192.168.84.22: icmp_seq=1 ttl=64 time=0.024 ms
  24. 64 bytes from 192.168.84.22: icmp_seq=2 ttl=64 time=0.034 ms
  25. 192.168.84.22 ping statistics
  26. 2 packets transmitted, 2 received, 0% packet loss, time 999ms
  27. rtt min/avg/max/mdev = 0.024/0.029/0.034/0.005 ms
  28. [oracle@db01 ~]$ ping scan.prudentwoo.com c 2
  29. 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. check dns client:
  6. [oracle@db01 ~]$ cat /etc/resolv.conf
  7. search prudentwoo.com
  8. 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. 192.168.84.15 ping statistics
  6. 2 packets transmitted, 0 received, +2 errors, 100% packet loss, time 3007ms
  7. 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. scan.prudentwoo.com ping statistics
  6. 2 packets transmitted, 2 received, 0% packet loss, time 1001ms
  7. 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. [oracle@db01 ~]$ srvctl config database d woo
  6. Database unique name: woo
  7. Database name: woo
  8. Oracle home: /u01/app/oracle/11.2.0.4/product/db_1
  9. Oracle user: oracle
  10. Spfile: +DATA/woo/spfilewoo.ora
  11. Domain:
  12. Start options: open
  13. Stop options: immediate
  14. Database role: PRIMARY
  15. Management policy: AUTOMATIC
  16. Server pools: woo
  17. Database instances: woo1,woo2
  18. Disk Groups: DATA,FRA01
  19. Mount point paths:
  20. Services:
  21. Type: RAC
  22. Database is administrator managed

能正常启动,故障修复。

 

       从整个问题的处理思路来看该故障不仅考验解决数据库故障能力,同时安装,基本运行原理都有考察到,当然考验更多的应该还是 操作系统 和 DNS 服务的深入理解。

      当然我是很庆幸的,出于职业敏感度,一堆报错中瞬间发现问题根源 ORA-00132,而没有从其它报错信息入手。

本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-12/138576.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计10835字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中