作者：Digital Observer（施嘉伟）
Oracle ACE Pro: Database
PostgreSQL ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师
公众号/墨天轮：Digital Observer；CSDN/PGfans：施嘉伟；ITPUB：sjw1933
内容介绍
ORACLE数据库是一个庞大的软件，各个部件协同工作，有时候一个环节出现问题，往往会导致重大的问题，特别是有时候外部环境因素造成的问题，会直接影响数据库的正常运行，比如存储，比如网络，再比如主机。本次案例要分析的就是一次数据库连接异常的问题，而引起数据连接异常的问题，多种多样，不胜枚举，而具体问题具体分析，本能的反应应该是网络造成的连接问题。最终，我们还是通过ORACLE的网络跟踪技术及数据库信息的查看解决了故障。
故障的起因主要由于利用rman异地恢复数据库，remote_listener参数设置问题导致连接异常。
概念普及
sqlnet是oracle提供的与网络层面交互的一个工具，比如如何解析客户端发起的连接，如何对客户端发起的连接进行辨别，如何对客户端连接进行阻隔限制，或者启用日志及跟踪（log and trace）功能等等一系列的功能，sqlnet通过写入文件参数来进行分析并产生作用， sqlnet配置文件的存放位置一般在：$ORACLE_HOME/network/admin目录下，本次处理网络故障我们就是使用了sqlnet的启用日志及跟踪功能。
sqlnet网络连接跟踪具体设置如下:
TRACE_LEVEL_CLIENT=16 TRACE_FILE_CLIENT=CLIENT TRACE_TIMESTAMP_CLIENT=ON trace_directory_client=D:\oracle\product\10.2.0\db_1\network\ADMIN |
详细解释一下以上参数值： TRACE_LEVEL_CLIENT –开启客户端跟踪级别
TRACE_LEVEL_LISTENER的取值范围为0~16，当然级别越高，收集的信息就相对越全面，系统默认是0，即不生成trace信息
off or 0 for no trace output
user or 4 for user trace information
admin or 10 for administration trace information
support or 16 for Oracle Support Services trace information
TRACE_FILE_CLIENT --设置客户端和服务器端的trace文件的名称
TRACE_TIMESTAMP_CLIENT --是否在trace中写入每条trace信息的dd-mon-yyyy hh:mi:ss:mi时间戳
TRACE_DIRECTORY_CLIENT --设置客户端和服务器端的trace文件的目录
故障排查
一、客户端操作如下：
1.客户端连接异常
C:\Documents and Settings\Administrator>sqlplus system/abc123@orcl SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 2 13:21:16 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR: ORA-12545: 因目标主机或对象不存在, 连接失败 请输入用户名: |
从报错来看似乎是监听的问题
2.tnsping检查连接串配置及服务器监听状态
C:\Documents and Settings\Administrator>tnsping orcl TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-12月-2014 13:22:17 Copyright (c) 1997, 2005, Oracle. All rights reserved. 已使用的参数文件: F:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.94)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0 .93)(PORT = 1521)) (LOAD_BALANCE = yes)) (CONNECT_DATA = (SERVER = SHARED) (SERV ICE_NAME = orcl) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC)))) OK (30 毫秒) |
从tnsping的测试来看，连接串监听都正常
3.由于是生产库是rac环境的尝试单节点登入
C:\Documents and Settings\Administrator>sqlplus system/abc123@orcl1 SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 2 13:26:17 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options
SQL> exit 从 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options 断开 C:\Documents and Settings\Administrator>sqlplus system/abc123@orcl2 SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 2 13:26:24 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options |
从单节点的登入来看配置都正常而且可以正常连接,问题很奇怪为了进一步排查开启开启客户端跟踪
在客户端的sqlnet.ora文件中添加如下内容： TRACE_LEVEL_CLIENT=16 TRACE_FILE_CLIENT=CLIENT TRACE_TIMESTAMP_CLIENT=ON trace_directory_client=D:\oracle\product\10.2.0\db_1\network\ADMIN |
4. 重新用客户端登入收集跟踪信息
跟踪信息如下： [02-12月-2014 14:10:32:968] nsmfr: normal exit [02-12月-2014 14:10:32:968] nsmfr: entry [02-12月-2014 14:10:32:968] nsmfr: 736 bytes at 0xe4b9d8 [02-12月-2014 14:10:32:968] nsmfr: normal exit [02-12月-2014 14:10:32:968] nsclose: normal exit [02-12月-2014 14:10:32:968] nscall: connecting... [02-12月-2014 14:10:32:968] nsc2addr: entry [02-12月-2014 14:10:32:968] nsc2addr: (ADDRESS=(PROTOCOL=tcp)(HOST=hp)(PORT=3554)) [02-12月-2014 14:10:32:968] nttbnd2addr: entry [02-12月-2014 14:10:32:968] snlinGetAddrInfo: entry [02-12月-2014 14:10:32:968] snlinGetAddrInfo: Invalid IP address string hp [02-12月-2014 14:10:32:968] snlinFreeAddrInfo: entry [02-12月-2014 14:10:32:968] snlinFreeAddrInfo: exit [02-12月-2014 14:10:32:968] snlinGetAddrInfo: exit [02-12月-2014 14:10:32:968] nttbnd2addr: looking up IP addr for host: hp [02-12月-2014 14:10:32:968] snlinGetAddrInfo: entry [02-12月-2014 14:10:35:218] snlinGetAddrInfo: Name resolution failed for hp [02-12月-2014 14:10:35:218] snlinFreeAddrInfo: entry [02-12月-2014 14:10:35:218] snlinFreeAddrInfo: exit [02-12月-2014 14:10:35:218] snlinGetAddrInfo: exit [02-12月-2014 14:10:35:218] nttbnd2addr: *** hostname lookup failure! *** [02-12月-2014 14:10:35:218] nttbnd2addr: exit [02-12月-2014 14:10:35:218] nserror: entry [02-12月-2014 14:10:35:218] nserror: nsres: id=0, op=77, ns=12545, ns2=12560; nt[0]=515, nt[1]=1001, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0 [02-12月-2014 14:10:35:218] nsc2addr: error exit |
从以上跟踪信息，当我们尝试使用orcl连接串连接时被路由到了HP这个主机，可是tnsnames.ora连接串里面根本没有配置相关的信息，那怎么会呢从客户端的配置似乎查不出什么原因，尝试从生产库查找。
二、服务器端配置查看
1.查看生产库的local_listener和remote_listener配置
SQL> show parameter local NAME TYPE VALUE --------------------------- --------------------------------------- local_listener string (address=(protocol=tcp)(host=192.168.0.93)(port=1521)) SQL> show parameter remote NAME TYPE VALUE --------------------------- --------------------------------------- remote_listener string LISTENERS_ORCL |
从配置上来看都没有任何异常
2.查看生产库的监听状态
C:\Documents and Settings\Administrator>lsnrctl status LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 02-12月-2014 14:15:29 Copyright (c) 1991, 2007, Oracle. All rights reserved. 正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - Production 启动日期 24-11月-2014 20:52:17 正常运行时间 7 天 17 小时 23 分 13 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 D:\oracle\product\10g\network\admin\listener.ora 监听程序日志文件 D:\oracle\product\10g\network\log\listener.log 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=svr01)(PORT=1521))) 服务摘要.. 服务 "+ASM" 包含 1 个例程。 例程 "+asm2", 状态 BLOCKED, 包含此服务的 1 个处理程序... 服务 "+ASM_XPT" 包含 1 个例程。 例程 "+asm2", 状态 BLOCKED, 包含此服务的 1 个处理程序... 服务 "orcl" 包含 3 个例程。 例程 "orcl1", 状态 READY, 包含此服务的 31 个处理程序... 例程 "orcl2", 状态 READY, 包含此服务的 32 个处理程序... 例程 "orclstd", 状态 READY, 包含此服务的 2 个处理程序... 服务 "orcl_XPT" 包含 3 个例程。 例程 "orcl1", 状态 READY, 包含此服务的 31 个处理程序... 例程 "orcl2", 状态 READY, 包含此服务的 32 个处理程序... 例程 "orclstd", 状态 READY, 包含此服务的 2 个处理程序... 命令执行成功 |
从监听来看似乎也看不出什么端倪
3.生产库尝试用orcl连接串连接
C:\Documents and Settings\Administrator>sqlplus system/abc123@orcl SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 12月 2 13:45:51 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 连接到: SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- orclstd SQL> show parameter remote NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_listener string LISTENERS_ORCL C:\Documents and Settings\Administrator>tnsping listeners_orcl TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 02-12月-2014 14:01:53 Copyright (c) 1997, 2007, Oracle. All rights reserved. 已使用的参数文件: D:\oracle\product\10g\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 Attempting to contact (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = svr02-vip)(PORT = 1521)) (ADDRESS = ( PROTOCOL = TCP)(HOST = svr01-vip)(PORT = 1521))) OK (20 毫秒) C:\Documents and Settings\Administrator>sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 12月 2 14:01:02 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from gv$instance; INSTANCE_NAME ---------------- orcl2 orcl1 |
从以上的连接来看其实已经很明显了，当时用orcl连接串连接时连接到orclstd，由于orclstd库remote_listener参数所引起，orclstd库在远程注册实例状态了，然后路由到备机了，导致生产使用orcl报错。
注:(orclstd这个库是利用生产的rman备份恢复回来的数据库)
4.查看生产监听状态正式以上判断
C:\Documents and Settings\Administrator>lsnrctl status
LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 02-12月-2014 14:15:29
Copyright (c) 1991, 2007, Oracle. All rights reserved.
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - Production 启动日期 24-11月-2014 20:52:17 正常运行时间 7 天 17 小时 23 分 13 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 D:\oracle\product\10g\network\admin\listener.ora 监听程序日志文件 D:\oracle\product\10g\network\log\listener.log 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=svr01)(PORT=1521))) 服务摘要.. 服务 "+ASM" 包含 1 个例程。 例程 "+asm2", 状态 BLOCKED, 包含此服务的 1 个处理程序... 服务 "+ASM_XPT" 包含 1 个例程。 例程 "+asm2", 状态 BLOCKED, 包含此服务的 1 个处理程序... 服务 "orcl" 包含 3 个例程。 例程 "orcl1", 状态 READY, 包含此服务的 31 个处理程序... 例程 "orcl2", 状态 READY, 包含此服务的 32 个处理程序... 例程 "orclstd", 状态 READY, 包含此服务的 2 个处理程序... 服务 "orcl_XPT" 包含 3 个例程。 例程 "orcl1", 状态 READY, 包含此服务的 31 个处理程序... 例程 "orcl2", 状态 READY, 包含此服务的 32 个处理程序... 例程 "orclstd", 状态 READY, 包含此服务的 2 个处理程序... --确实证实了之前的判断，罪魁祸首水落石出 命令执行成功 |
5.证实备机是否主机名为hp
C:\Documents and Settings\Administrator>hostname hp
C:\Documents and Settings\Administrator>ping hp Pinging hp [192.168.0.80] with 32 bytes of data: Reply from 192.168.0.80: bytes=32 time<1ms TTL=128 |
故障后期处理
1.注销备机的remote_listener alter system set remote_listener='' scope=both; 2.生产库重新加载监听 C:\Documents and Settings\Administrator>lsnrctl reload LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 02-12月-2014 14:18:51 Copyright (c) 1991, 2007, Oracle. All rights reserved.
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) 命令执行成功
C:\Documents and Settings\Administrator>lsnrctl status LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 02-12月-2014 14:18:57 Copyright (c) 1991, 2007, Oracle. All rights reserved.
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - Production 启动日期 24-11月-2014 20:52:17 正常运行时间 7 天 17 小时 26 分 40 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 D:\oracle\product\10g\network\admin\listener.ora 监听程序日志文件 D:\oracle\product\10g\network\log\listener.log 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=svr01)(PORT=1521))) 服务摘要.. 服务 "+ASM" 包含 1 个例程。 例程 "+asm2", 状态 BLOCKED, 包含此服务的 1 个处理程序... 服务 "+ASM_XPT" 包含 1 个例程。 例程 "+asm2", 状态 BLOCKED, 包含此服务的 1 个处理程序... 服务 "orcl" 包含 2 个例程。 例程 "orcl1", 状态 READY, 包含此服务的 31 个处理程序... 例程 "orcl2", 状态 READY, 包含此服务的 1 个处理程序... 服务 "orcl_XPT" 包含 2 个例程。 例程 "orcl1", 状态 READY, 包含此服务的 31 个处理程序... 例程 "orcl2", 状态 READY, 包含此服务的 1 个处理程序... --orclstd 已经消失了 命令执行成功 3.客户端再次利用orcl连接数据库正常 D:\>sqlplus system/abc123@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 2 14:21:01 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string orcl2 lock_name_space string log_file_name_convert string service_names string orcl 到此处问题已经解决 |
技术结论
通过以上一步步的推敲，终于发现了问题的根本原因，由于利用rman异机恢复时remote_listener参数所引起。orclstd库在远程注册实例状态了，然后路由到80备机了，导致生产使用orcl报错。
