oracle11g跟踪,Oracle 11g DRCP连接跟踪配置

数据驻留连接池(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的角度进行的跟踪操作。备今后不时之需吧。