标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
分类目录归档:Oracle 监听
连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常
平台系统版本相关信息
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio NLSRTL Version 10.2.0.4.0 - Production SQL> show parameter cluster; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 cluster_interconnects string 192.168.16.11 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual; www.xifenfei.com ------------------- 2012-05-30 11:07:12
pmon和监听负载
pmon和LISTENER进程负载均比较高
PID %CPU ResSize Char Command 10617230 72.9 143924 21014 ora_pmon_ahunicom1 22675560 49.9 142000 1547 oracleahunicom1 (LOCAL=NO) 5243206 30.6 49728 2579 /oracle10/app/product/db/10.2.0/bin/tnslsnr LISTENER -inherit
监听日志
每秒钟很多类此pmon注册监听信息
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0 27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
通过这两点可以确定是因为pmon在不停的动态注册监听导致监听日志,pmon,listener进程异常
查询MOS[ID 982068.1]
问题原因
After altering the value of the parameter REMOTE_LISTENER, excessive CPU is seen for the TNS listener process (TNSLSNR) and the listener.log file grows rapidly. Alert log confirms the REMOTE_LISTENER parameter in the SPFILE was altered. Listener.log shows continuous service_update triggered from PMON to the TNS listener, 100's per second. REMOTE_LISTENER had been set to null twice 查询alert日志,果真发现: ALTER SYSTEM SET remote_listener='' SCOPE=BOTH SID='AHUNICOM1'; ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;
解决方案
alter system set remote_listener = 'remote_rac' scope=memory sid = 'AHUNICOM1'; alter system set remote_listener = '' scope=memory sid = 'AHUNICOM1'; --然后重启节点,pmon和监听恢复正常
提高短连接性能方法测试
创建测试脚本
通过在三个会话中同时执行test_login.sh脚本,模拟当数据库多个短连接情况性能
[oracle@xifenfei tmp]$ more test_login.sh #!/bin/bash echo "start login database `date`*********" >>/tmp/test_1.log e=2000 for((i=1;i<=$e;i=i+1)) do /tmp/login_oracle.sh done echo "end login database `date`*********" >>/tmp/test_1.log [oracle@xifenfei tmp]$ more login_oracle.sh #!/bin/bash sqlplus chf/xifenfei@ORA11G_P<<XFF>/dev/null select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; exit XFF --ORA11G_P根据不同的测试情景指定不同名称
情况1:一个监听情况下
start login database Tue May 1 18:03:32 CST 2012********* start login database Tue May 1 18:03:35 CST 2012********* start login database Tue May 1 18:03:37 CST 2012********* end login database Tue May 1 18:08:20 CST 2012********* end login database Tue May 1 18:08:25 CST 2012********* end login database Tue May 1 18:08:26 CST 2012********* --计算2000个会话登录/查询/推出时间 4:48 4:40 4:49
情况2:三个监听,客户端配置tns负载均衡
--监听配置 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora11g) (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1) (SID_NAME = ora11g) ) ) ADR_BASE_LISTENER = /u01/oracle LISTENER1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522)) ) ) SID_LIST_LISTENER1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora11g) (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1) (SID_NAME = ora11g) ) ) ADR_BASE_LISTENER1 = /u01/oracle LISTENER2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1523)) ) ) SID_LIST_LISTENER2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ora11g) (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1) (SID_NAME = ora11g) ) ) ADR_BASE_LISTENER2 = /u01/oracle --tns配置 ORA11G_M = (DESCRIPTION = (LOAD_BALANCE=ON) (FAILOVER=ON) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1523)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = ora11g) ) ) --测试结果 start login database Tue May 1 17:51:45 CST 2012********* start login database Tue May 1 17:51:49 CST 2012********* start login database Tue May 1 17:51:51 CST 2012********* end login database Tue May 1 17:55:58 CST 2012********* end login database Tue May 1 17:56:06 CST 2012********* end login database Tue May 1 17:56:09 CST 2012********* --计算2000个会话登录/查询/推出时间 4:13 4:17 4:18
情况2:使用常驻连接池DRCP(11g新特性)
--启动默认DRCP SQL> exec dbms_connection_pool.start_pool(); PL/SQL procedure successfully completed. --tns配置 ORA11G_P = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521)) ) (CONNECT_DATA = (SERVER=POOLED) --注意 (SERVICE_NAME = ora11g) ) ) --执行结果 start login database Tue May 1 18:19:58 CST 2012********* start login database Tue May 1 18:20:01 CST 2012********* start login database Tue May 1 18:20:03 CST 2012********* end login database Tue May 1 18:23:16 CST 2012********* end login database Tue May 1 18:23:19 CST 2012********* end login database Tue May 1 18:23:21 CST 2012********* --计算2000个会话登录/查询/推出时间 3:16 3:18 3:19
总结
如果在数据库短连接过程中发现监听是瓶颈的时候,可以考虑使用多个监听+tns 负载均衡,从一定程度上缓解监听瓶颈.如果是11g数据库可以考虑使用其心功能DRCP,从而很大程度上提高短连接过程中数据库的效率.因为DRCP还属于11g的新功能稳定性不知道如何?使用该功能前,请一定要做好相关测试工作.如有可能还是建议从应用层面尽可能的使用长连接,提高数据库会话效率.
发表在 Oracle 监听
评论关闭
xdb组件中关于ftp/http监听
发现监听进程监听8080和2100端口
--监听端口 [oracle@xifenfei ~]$ netstat -nap|grep tnslsnr (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 29866/tnslsnr tcp 0 0 0.0.0.0:1521 0.0.0.0:* LISTEN 29866/tnslsnr tcp 0 0 0.0.0.0:2100 0.0.0.0:* LISTEN 29866/tnslsnr --进程名称 [oracle@xifenfei ~]$ ps -ef|grep 29866 oracle 29866 1 0 00:20 pts/0 00:00:00 /u01/oracle/9.2.0/db_1/bin/tnslsnr LISTENER -inherit
查看监听状态
[oracle@xifenfei ~]$ lsnrctl status LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 08-MAY-2012 00:26:50 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production Start Date 08-MAY-2012 00:20:47 Uptime 0 days 0 hr. 6 min. 3 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /u01/oracle/9.2.0/db_1/network/admin/listener.ora Listener Log File /u01/oracle/9.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=8080))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=2100))(Presentation=FTP)(Session=RAW)) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "xffXDB" has 1 instance(s). Instance "xff", status READY, has 1 handler(s) for this service... Service "xifenfei" has 2 instance(s). Instance "xff", status UNKNOWN, has 1 handler(s) for this service... Instance "xff", status READY, has 1 handler(s) for this service... The command completed successfully --从这里可以看出oracle的listener确实监听了8080和2100端口
查看listener.ora文件
[oracle@xifenfei ~]$ more /u01/oracle/9.2.0/db_1/network/admin/listener.ora # LISTENER.ORA Network Configuration File: /u01/oracle/9.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/oracle/9.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = xifenfei) (ORACLE_HOME = /u01/oracle/9.2.0/db_1) (SID_NAME = xff) ) ) --从中确认未监听8080和2100端口,看来这两个端口是在动态注册监听的时候加入进去的. --我们知道oracle的xdb组件可能会启用http和ftp功能,这两个功能可能会开启响应端口,我们分析xdb组件.
查看xdb组件是否工作正常
SQL> select 2 comp_name, status, version from 3 DBA_REGISTRY where 4 comp_name='Oracle XML Database'; COMP_NAME STATUS VERSION ------------------------------ ---------------------- ---------- Oracle XML Database VALID 9.2.0.4.0 SQL> select count(*) from dba_objects where owner='XDB' and status='INVALID'; COUNT(*) ---------- 0 SQL> show parameter dispatchers; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ dispatchers string (PROTOCOL=TCP) (SERVICE=xffXDB) max_dispatchers integer 5 mts_dispatchers string (PROTOCOL=TCP) (SERVICE=xffXDB) mts_max_dispatchers integer 5 --查询证明xdb组件应该工作正常
查看xdb中ftp和http启动相关端口
SQL> select dbms_xdb.GETFTPPORT() from dual; select dbms_xdb.GETFTPPORT() from dual * ERROR at line 1: ORA-00904: "DBMS_XDB"."GETFTPPORT": invalid identifier SQL> select dbms_xdb.GETHTTPPORT() from dual; select dbms_xdb.GETHTTPPORT() from dual * ERROR at line 1: ORA-00904: "DBMS_XDB"."GETHTTPPORT": invalid identifier --9i中为提供上述查询端口的相关程序. SQL> set long 10000 SQL> set pagesize 0 SQL> SELECT dbms_xdb.cfg_get FROM dual; --从中找到类此这样记录,确实ftp启用2100端口,http启用8080端口 <ftpconfig> <ftp-port>2100</ftp-port> <ftp-listener>local_listener</ftp-listener> <ftp-protocol>tcp</ftp-protocol> <session-timeout>6000</session-timeout> </ftpconfig> <httpconfig> <http-port>8080</http-port> <http-listener>local_listener</http-listener> <http-protocol>tcp</http-protocol> <session-timeout>6000</session-timeout> <server-name>XDB HTTP Server</server-name> </httpconfig> --到这里我们可以确定是由于xdb组件中的ftp和http功能自动注册导致监听了2100和8080端口
修改xdb中监听ftp和http端口
SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),' 2 /xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',8888)); Call completed. SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(), 2 '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()',2222)); Call completed. SQL> commit; Commit complete. SQL> exec dbms_xdb.cfg_refresh; PL/SQL procedure successfully completed. SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production [oracle@xifenfei ~]$ lsnrctl status LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 08-MAY-2012 00:57:13 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production Start Date 08-MAY-2012 00:20:47 Uptime 0 days 0 hr. 36 min. 26 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /u01/oracle/9.2.0/db_1/network/admin/listener.ora Listener Log File /u01/oracle/9.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=8888))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=2222))(Presentation=FTP)(Session=RAW)) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "xffXDB" has 1 instance(s). Instance "xff", status READY, has 1 handler(s) for this service... Service "xifenfei" has 2 instance(s). Instance "xff", status UNKNOWN, has 1 handler(s) for this service... Instance "xff", status READY, has 1 handler(s) for this service... The command completed successfully
xdb中ftp和http监听
SQL> alter system reset dispatchers scope=spfile sid='*'; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 353441008 bytes Fixed Size 451824 bytes Variable Size 184549376 bytes Database Buffers 167772160 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> show parameter dispatchers; NAME TYPE VALUE ------------------------------------ ---------------------- --------- dispatchers string max_dispatchers integer 5 mts_dispatchers string mts_max_dispatchers integer 5 --SELECT dbms_xdb.cfg_get FROM dual;中信息 <ftpconfig> <ftp-port>2222</ftp-port> <ftp-listener>local_listener</ftp-listener> <ftp-protocol>tcp</ftp-protocol> <session-timeout>6000</session-timeout> </ftpconfig> <httpconfig> <http-port>8888</http-port> <http-listener>local_listener</http-listener> <http-protocol>tcp</http-protocol> <session-timeout>6000</session-timeout> <server-name>XDB HTTP Server</server-name> </httpconfig> [oracle@xifenfei dbs]$ lsnrctl status LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 08-MAY-2012 01:10:07 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production Start Date 08-MAY-2012 00:20:47 Uptime 0 days 0 hr. 49 min. 20 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /u01/oracle/9.2.0/db_1/network/admin/listener.ora Listener Log File /u01/oracle/9.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "xifenfei" has 2 instance(s). Instance "xff", status UNKNOWN, has 1 handler(s) for this service... Instance "xff", status READY, has 1 handler(s) for this service... The command completed successfully --证明已经关闭了xdb 组件的ftp/http监听
xdb组件中的ftp/http监听在9i数据库中,只要你安装了xdb组件,会自动启用这功能.
在10g及其11g中默认不启用.所以为了你的数据库安全,如果不使用这些功能,建议手工关闭
发表在 Oracle 监听
评论关闭