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