如何trace Oracle PMON进程动态注册过程？这个问题我想到2个答案，对PMON做event trace或者采用Oracle Network Server因该都可以达到目的。
让我们来实践一下!
Oracle Network Server Trace模式
1. 启用Oracle SqlNet服务器端trace,这需要我们修改sqlnet.ora配置文件
1 [maclean@rh2 ~]$ echo "TRACE_LEVEL_SERVER = 16
2 > TRACE_FILE_SERVER = SERVER
3 > TRACE_DIRECTORY_SERVER= /home/maclean/ntrc" > $ORACLE_HOME/network/admin/sqlnet.ora
4 [maclean@rh2 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
5 TRACE_LEVEL_SERVER = 16
6 TRACE_FILE_SERVER = SERVER
7 TRACE_DIRECTORY_SERVER= /home/maclean/ntrc
2. 触发trace
01 SQL> conn / as sysdba
02 Connected.
03
04 SQL> select spid from V$process, V$session where audsid=userenv('SESSIONID') and paddr=addr;
05 SPID
06 ------------
07 4264
08
09 SQL> alter system register;
10 System altered.
11
12 /*在之前指定的TRACE_DIRECTORY_SERVER目录下将出现形如server_$spid.trc的trace文件*/
13
14 [maclean@rh2 ntrc]$ cat server_4290.trc|grep nsprecv|grep -A 2 -B 2 reg
15 [20-AUG-2010 10:42:53:896] nsprecv: 00 00 00 00 00 15 61 6C |......al|
16 [20-AUG-2010 10:42:53:896] nsprecv: 74 65 72 20 73 79 73 74 |ter.syst|
17 [20-AUG-2010 10:42:53:896] nsprecv: 65 6D 20 72 65 67 69 73 |em.regis|
18 [20-AUG-2010 10:42:53:896] nsprecv: 74 65 72 01 00 00 00 01 |ter.....|
19 [20-AUG-2010 10:42:53:896] nsprecv: 00 00 00 00 00 00 00 00 |........|
20
21 /*分析该trace文件后可以匹配到以上动态注册语句*/
22
23 /*记得将server端sqlnet trace设置disable掉*/
24
25 [maclean@rh2 ntrc]$ echo "" > $ORACLE_HOME/network/admin/sqlnet.ora
PMON TRACE模式
1.找出PMON的进程号
1 SQL> select spid ,program from v$process where program like '%PMON%';
2
3 SPID PROGRAM
4 ------------ ------------------------------------------------
5 4050 oracle@rh2 (PMON)
2.执行10257事件trace
01 SQL> oradebug setospid 4050;
02 Oracle pid: 2, Unix process pid: 4050, image: oracle@rh2 (PMON)
03
04 SQL> oradebug event 10257 trace name context forever,level 16;
05 Statement processed.
06
07 SQL> alter system register;
08 System altered.
09
10 SQL> oradebug event 10257 trace name context off;
11 Statement processed.
12
13 SQL> oradebug tracefile_name;
14 /s01/10gdb/admin/YOUYUS/bdump/youyus_pmon_4050.trc
15
16 cat /s01/10gdb/admin/YOUYUS/bdump/youyus_pmon_4050.trc
17 .......................
18 kmmlrl: register now
19 kmmgdnu: cXDB
20 goodness=0, delta=1,
21 flags=0x5:unblocked/not overloaded, update=0x6:G/D/-
22 kmmgdnu: YOUYUS
23 goodness=0, delta=1,
24 flags=0x4:unblocked/not overloaded, update=0x6:G/D/-
25 kmmlrl: nsgr update returned 0
26 kmmlrl: nsgr register returned 0
27
28 /*可以看到注册了YOUYUS和cXDB2个service的过程*/
若无法正常注册可以按照以下步骤检查:
1. 检查Oracle Net命名方式是否正确设置，该参数由sqlnet.ora配置文件中的NAMES.DIRECTORY_PATH指定。
2. 确认SQL NET配置文件如sqlnet.ora，tnsnames.ora等的位置正确配载。
3. 检查是否设置了TNS_ADMIN环境变量，该变量将影响配置文件的正确路径。
4. 确认LOCAL_LISTENER或REMOTE_LISTENER中指定的service可以被正常tnsping通,若以上参数未指定值则尝试步骤6。
5. 确认主机名可以被正确解析为ip地址，如以下示例:
01 C:\Users\weisly>nslookup www.oracle.com
02 服务器: dir-605
03 Address: 192.168.0.1
04
05 非权威应答:
06 名称: a398.g.akamai.net.0.1.cn.akamaitech.net
07 Addresses: 117.104.136.98
08 117.104.136.128
09 Aliases: www.oracle.com
10 www.oracle.com.edgesuite.net
11 a398.g.akamai.net
6. 修改LOCAL_LISTENER为非服务别名解析的形式，这样可以排除配置文件可能造成的影响，从而确认问题是否由配置文件设置不当而引起:
1 SQL> conn / as sysdba
2 Connected.
3
4 SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh2)(PORT=1521)))';
5 System altered.
6
7 SQL> alter system set REMOTE_LISTENER=' (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = rh1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = rh2)(PORT = 1521)))';
8 System altered.
7. 将主机名替换为ip地址并测试是否解决问题。
8. 以IPC协议替代常见的TCP协议测试，绕过TCP和主机名等因素可能造成的问题，以确认问题是否由这些因素引起:
01 SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=KEY1)))';
02 System altered.
03
04 /*其中KEY值需要和listener.ora中指定的一致。*/
05
06 SQL> host
07 [maclean@rh2 admin]$ lsnrctl status KEY1
08
09 LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-AUG-2010 11:30:06
10 Copyright (c) 1991, 2007, Oracle. All rights reserved.
11 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=KEY1)))
12 STATUS of the LISTENER
13 ------------------------
14 Alias KEY1
15 Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
16 Start Date 20-AUG-2010 11:29:15
17 Uptime 0 days 0 hr. 0 min. 50 sec
18 Trace Level off
19 Security ON: Local OS Authentication
20 SNMP OFF
21 Listener Parameter File /s01/10gdb/network/admin/listener.ora
22 Listener Log File /s01/10gdb/network/log/key1.log
23 Listening Endpoints Summary...
24 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=KEY1)))
25 Services Summary...
26 Service "YOUYUS" has 1 instance(s).
27 Instance "YOUYUS", status READY, has 1 handler(s) for this service...
28 Service "YOUYUS_XPT" has 1 instance(s).
29 Instance "YOUYUS", status READY, has 1 handler(s) for this service...
30 Service "cXDB" has 1 instance(s).
31 Instance "YOUYUS", status READY, has 10 handler(s) for this service...
32 The command completed successfully