数据驻留连接池(Database Resident Connection Pool)是Oracle 11g推出的新特性。借助DRCP,Oracle Database Server可以向前端应用或者前段中间件提供高效的连接访问。在前端不具有条件使用数据库连接池,并且系统具有高并发、短会话特性的时候,DRCP是一种很简单的配置解决方案。
我们经常要对Server Process进行跟踪访问,来进行调优、调错工作。本篇介绍在DRCP环境下,如何对连接池Server Process进行跟踪的方法。
1、环境准备和配置
DRCP我们选择在oracle 11g环境上进行试验。
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE11.2.0.1.0Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0–Production
首先定义一个目录,用于防止进程跟踪文件。
[oracle@oracle11g listener]$ pwd
/u01/diag/tnslsnr/oracle11g/listener
[oracle@oracle11g listener]$ ls
alertcdumpincidentincpkglckmetadatastagesweeptrace
[oracle@oracle11g listener]$ mkdir drcp
[oracle@oracle11g listener]$ cd drcp
[oracle@oracle11g drcp]$ ls
[oracle@oracle11g drcp]$ pwd
/u01/diag/tnslsnr/oracle11g/listener/drcp
选择drcp子目录作为跟踪文件目录。
修改sqlnet.ora配置文件,添加配置参数信息。
[oracle@oracle11g admin]$ pwd
/u01/oracle/network/admin
[oracle@oracle11g admin]$ vim sqlnet.ora
--在sqlnet.ora文件中添加下面参数内容;
DIAG_ADR_ENABLED = off
TRACE_LEVEL_SERVER = 16
TRACE_TIMESTAMP_SERVER = ON
TRACE_DIRECTORY_SERVER = /u01/diag/tnslsnr/oracle11g/listener/drcp
为了使监听参数生效,此处需要重新启动监听器。
[oracle@oracle11g admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-MAR-2012 05:37:30
Copyright (c) 1991, 2009, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle11g)(PORT=1521)))
The command completed successfully
[oracle@oracle11g admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-MAR-2012 05:37:34
Copyright (c) 1991, 2009, Oracle.All rights reserved.
Starting /u01/oracle/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/oracle/network/admin/listener.ora
Log messages written to /u01/diag/tnslsnr/oracle11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle11g)(PORT=1521)))
STATUS of the LISTENER
------------------------
AliasLISTENER
VersionTNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date01-MAR-2012 05:37:34
Uptime0 days 0 hr. 0 min. 0 sec
Trace Leveloff
SecurityON: Local OS Authentication
SNMPOFF
Listener Parameter File/u01/oracle/network/admin/listener.ora
Listener Log File/u01/diag/tnslsnr/oracle11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g)(PORT=1521)))
The listener supports no services
The command completed successfully
2、监听过程
一旦开启,我们就可以看到跟踪目录下存在文件。
[oracle@oracle11g admin]$ cd /u01/diag/tnslsnr/oracle11g/listener/drcp
[oracle@oracle11g drcp]$ ls
svr_6274.trcsvr_6276.trcsvr_6284.trcsvr_6287.trc
[oracle@oracle11g drcp]$ ps -ef | grep ora_n
oracle629459310 05:38 pts/100:00:00 grep ora_n
[oracle@oracle11g drcp]$ ps -ef | grep ora_l
oracle568910 05:03 ?00:00:00 ora_lgwr_wilson
oracle629659310 05:38 pts/100:00:00 grep ora_l
跟踪文件格式为svr_.trc。如果监视一个特定的session进程,只需要知道对应的process编号即可。
首先开启connection pool。
SQL> exec dbms_connection_pool.configure_pool(minsize => 1,maxsize => 4);
PL/SQL procedure successfully completed
SQL> select connection_pool, status, minsize, maxsize, INACTIVITY_TIMEOUT from dba_cpool_info;
CONNECTION_POOLSTATUSMINSIZEMAXSIZE INACTIVITY_TIMEOUT
------------------------------ ---------------- ---------- ---------- ------------------
SYS_DEFAULT_CONNECTION_POOLINACTIVE14300
SQL> exec dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed
此时对应进程生成。
[oracle@oracle11g drcp]$ ps -ef | grep ora_l
oracle568910 05:03 ?00:00:00 ora_lgwr_wilson
oracle630011 05:39 ?00:00:00 ora_l000_wilson
oracle630259310 05:39 pts/100:00:00 grep ora_l
使用sqlplus进行连接。
[oracle@oracle11g drcp]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 1 05:40:16 2012
Copyright (c) 1982, 2009, Oracle.All rights reserved.
SQL> conn scott/tiger@wilsondrcp
Connected.
SQL> select sid from v$mystat where rownum<2;
SID
----------
141
注意:wilsondrcp是创建的使用pooled方式连接的本地命名服务。
获取到会话对应的process id。
SQL> select paddr from v$session where sid=141;
PADDR
--------
38BCD994
SQL> select pid, spid from v$process where addr='38BCD994';
PID SPID
---------- ------------------------
31 6310
对应的process编号为6310。在OS层面进行验证。
[oracle@oracle11g drcp]$ ps -ef | grep 6310
oracle631010 05:39 ?00:00:00 ora_l001_wilson
oracle632659310 05:41 pts/100:00:00 grep 6310
[oracle@oracle11g drcp]$ ls
svr_6274.trcsvr_6287.trcsvr_6298.trcsvr_6306.trcsvr_6314.trc
svr_6276.trcsvr_6290.trcsvr_6300.trc svr_6310.trcsvr_6322.trc
svr_6284.trcsvr_6292.trcsvr_6304.trcsvr_6312.trcsvr_6324.trc
对应的svr_6310.trc文件为生成的跟踪文件。我们可以从中找到具体的跟踪信息。
[oracle@oracle11g drcp]$ tail -n 10 svr_6310.trc
[01-MAR-2012 05:41:15:765] nsiocancel: exit
[01-MAR-2012 05:41:15:765] nsmfr: entry
[01-MAR-2012 05:41:15:765] nsmfr: 2020 bytes at 0x1071e790
[01-MAR-2012 05:41:15:765] nsmfr: normal exit
[01-MAR-2012 05:41:15:765] nsmfr: entry
[01-MAR-2012 05:41:15:766] nsmfr: 1012 bytes at 0x1071e398
[01-MAR-2012 05:41:15:766] nsmfr: normal exit
[01-MAR-2012 05:41:15:766] nsclose: normal exit
[01-MAR-2012 05:41:15:766] nttwrto: entry
[01-MAR-2012 05:41:15:766] nttwrto: exit
3、结论
本篇介绍了DRCP的跟踪方法,从感觉上看,还是从Oracle Net Service的角度进行的跟踪操作。备今后不时之需吧。