oracle dba手记1chapter2-06一次监听故障的

1、一次故障的与处理（网名）ITPUB Oracle 专题深入讨论版版主，现任云和恩墨西区交付经理，擅长 Oracle 数据库故障 处理和性能优化。个人 ： #İ( Oracle Oracle Database Performance Tuning Guide 10g Release 2 (10.2) / The Oracle Performance Improvement Method TopTen Mistakes Found in Oracle Systems # f95 ( # sİ . y/M% *lS # 7y Metalink 858539.1 / 7 o/6# K KK1 1 Or

2、acle % #9 %C4 : 9 3 %# Oracle _ 4 000 .K% 6 000 .K% + +B FOS ( TNS 1 4 9 İ o/#.n . #. 100ms K% + Listener -9 x.v9 &E.İ KpY İiE HP-UX11.31 & Oracle 9208 . İ & 7 / IP 2 TNS1 14s 6 14s 6 % SQL SELECT 7 /7 NET hosts nsswitch.conf M hosts M resolv.conf nsswitch.conf M listener.log Mr K F S spack / Logons

3、/Sec 7K r UQ w rw r 9 Oracle & Bug 5576565 - Small listener memory leak per connection sqlnet.log Mr %İ U L % İ 4Q# SQL 4 =+ 1F5. p2 x ftp RM7 6. 2% TNSK o(%# U sqlplus / as sysdba 7. 2/etc & resolv.conf 122 #İ(% . 10 . w . 4.1 100ms # #. A d|/ 10 . 4. 1100ms . 100 ms oG e U7 . n 4. %Kİ #6 K trace t

4、russ tusc p o G truss p & HP-UX % tusc p E 1F/ Servros / c 0 4Q 7A W 4Q 7 7 Q T 1.log2 sqlplus Servros SQL select sfrom v$pros where addr=(select paddr from v$seswhere sid=(select sidfrom v$myswhere rownum=1);S191903 2% CTRL+C / tusc yM 1.log / r& ( Attached to pros 4595 (/oracle/app/oracle/product/

5、9.2.0/b 64-bit )nslsnr LISTENER -inherit)1244792894.476963 4595 read(17, 0 x9ff6950, 64) sleng1244792894.487425 4595 read(17, N T P 0 1 9 1 8 5 n, 64) = 11 123 #İ(1244792894.488420 4595 get() = 4595 (1)1244792894.488498 4595 fcntl(17, F_SETFD, 1) = 01244792894.488735 4595 write(16, 000= , 4) = 41244

6、792894.489328 4595 write(16, ( A D D R E S S = ( P R O T O C ., 61) . = 611244792894.489445 4595 write(16, 000401, 4) = 4FyK # System Call) 1 5 K 1 .1 1 1244792894.488735 K1V 7F 1970 1 1 0 0 0 2 4595 3 Read z 4 (17, 0 x9ff6950, 64) 5 sleng iLF 6-1 /9#& 6-1 /K (m. &E#&%/. i .1,. 1 $ TCP # ( r6 .6K %1

7、| | K Oracle .| ,. o# QUEUESIZE 7 listen BACKLOG !o; r AIX & SOMAXCONN HP-UX & TCP_CONN_REQUEST_MAX 2 #/ TCP 6 # accept Fork #. L1 1 y #/ y6 47 ./fNZoG y Hang X #.% L ps-ef | grep tnslsnr J1 1 tnslsnr 124 #İ( 1 Fork #. L1 2 1 Q# C4 Oracle Server Pros 2 exec oracle .M . 2 1 Oracle ServerPros 7 Servro

8、s ( 2 p Servros #? ros R#? q N T P 0 6. Serv1 . . Servros . s SQL &L% . / tusc 9KA Ky/9 L?y 4595 19190 % Servros s E Servros Q# 4595.out / 1 9 1 9 0 6 K. &L? 1. TCP TNS I& 1244792894.645890 4595 getsockname(9, 0 x91244792894.646092 4595 getpeername(9, 0 x9 ENOTCONN1244792894.646355 4595 accept(9, 0

9、x91244792894.646500 4595 getsockname(14, 0 x9ff7dc0, 0 x9ff7dc0, 0 x9ff7db0)= 0ff7db0)ERR#235ff7dd0, 0 x9ff7dc0, 0 x9ff7db0)= 14ff7db0)= 01244792894.646611 4595 ioctl(14, FIONBIO, 0 x91244792894.646770 4595 setsockopt(14, 0 x6, TCP_Nff7dfc)= 0AY, 0 x9ff7e54, 4) = 01244792894.646859 4595 fcntl(14, F_

10、SETFD, 1)= 01244792894.648495 4595 poll(0 x6000000000203f40, 5, -1)= 21244792894.648864 4595 read(14, 0a60001000014 01, 00b0., 8208)= 1661244792894.648986 4595 ioctl(14, FIONBIO, 0 x91244792894.649084 4595 gettimeofday(0 x9ff93dc)= 0ff9500, NULL)= 01244792894.649576 4595 fcntl(14, F_SETFD, 0)= 02 45

11、95.out grep “19190” 1.log 19190.out #İ(y I& 1244792894.657371 191891244792894.657768 191891244792894.658279 191891244792894.658969 191891244792894.659253 191891244792894.659386 191891244792894.659522 191891244792894.659698 191891244792894.659861 19189fork() (returning as child .). = 4595get() = 1918

12、9 (4595)lwp_self() = 1015954set_userthreadid(1) = 1sysconf(UMA_PM) = 1mpctl(MPC_GETLWP_BINDVALUE, 0, -4) = 104mpctl(MPC_SETLWP, -7, -4) = -6lwp_self() = 1015954lwp_getscheduler(LWP_SCHED_SPECIFIC, 1015954, 0 x9f7f6d1288,0 x9f7f6d128c) = 01244792894.668856 191891244792894.669454 19189fork() = 19190ex

13、it(0)WIFEXITED(0)4 2 19189 y % #.Q % Q F&K read .9 95ms 5 2 71 19190 exec 1 Oracle Server s Pro1244792894.669111 191901244792894.669360 191901244792894.669837 191901244792894.670074 191901244792894.670158 191901244792894.670279 191901244792894.670378 191901244792894.670691 191901244792894.670804 191

14、90fork() (returning as child .) . = 19189get() = 19190 (19189)lwp_self() = 1015956set_userthreadid(1) = 1sysconf(UMA_PM) = 1mpctl(MPC_GETLWP_BINDVALUE, 0, -4) = 104mpctl(MPC_SETLWP, -7, -4) = -6lwp_self() = 1015956lwp_getscheduler(LWP_SCHED_SPECIFIC, 1015956, 0 x9f7f6d1288,0 x9f7f6d128c) = 012447928

15、94.6708951244792894.6709401244792894.6717601244792894.6719221244792894.6720601244792894.6721881244792894.6723171244792894.6724301244792894.6729691244792894.68362019190191901919019190191901919019190191901919019190close(16) = 0close(17) = 0setpgrp3(2) = 19190setpgrp3(2) ERR#1 EPERMclose(0) = 0close(1)

16、 = 0close(2) = 0getuid() = 110 (110)setpgrp3(2) ERR#1 EPERMexecve(/oracle/app/oracle/product/9.2.0/bin/oracle,0 x6000000000024720, 0 x60000000013251f0) = 0 64-bit6 Servros /tk 1244792894.683620 19190 execve(/oracle/app/oracle/product/9.2.0/bin/oracle, 0 x6000000000024720, 0 x60000000013251f0) = 0 64

17、-bit1244792894.683791 19190 mmap(NULL, 8192, PROT_REROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0 x9f7f7fc0001244792894.683938 19190 open(/usr/lib/hpux64/dld.so, O_RDONLY|0 x800, 0) = 01244792894.684004 19190 read(0, 7fE L F 02020101010000000., 1024) = 1024 126 1244792894.674259 4595 wa

18、it(19189, WIFEXITED(0), 0) = 191891244792894.674511 4595 close(15) = 01244792894.674685 4595 close(18) = 01244792894.769552 4595 read(17, N T P 0 1 9 1 9 0 n, 64) = 11 #İ(1244792894.684073 19190 mmap(NULL, 594624, PROT_RE0, 0) = 0 xc000000000010000ROT_EXEC, MAP_SHARED|MAP_FILE|MAP_ SHLIB,1244792894.684105 19190 sysconf(_SC_PAGE_SIZE) = 40961244792894.767735 19190 brk(0 x6000000000620000) = 01244792894.769215 19190 get1244792894.769277 19190 get() = 19190 (1)() = 19190 (1