Strace sqlplus scott/tiger@yws 很好处理tns问题

  • yewushang

    2014-02-28 21:43:07
  • Oracle
  • 原创
从函数调用角度查看Oracle登录发生了什么.
当你hang 在sqlplus / as sysdba ,或者sqlplu username/password@tnsname ,可以尝试跟踪下.很清晰的看出来哪里有问题.
[oracle@yws ~]$ strace -e trace=file sqlplus scott/tiger@yws    --查看打开文件
execve("/u01/oracle/product/10.2.0/bin/sqlplus", ["sqlplus", "scott/tiger@yws"], [/* 23 vars */]) = 0  --执行ORACLE_HOME下bin下命令
access("/etc/ld.so.preload", R_OK)      = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/tls/i686/sse2/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/oracle/product/10.2.0/lib/tls/i686/sse2", 0xbffa409c) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/tls/i686/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/oracle/product/10.2.0/lib/tls/i686", 0xbffa409c) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/tls/sse2/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/oracle/product/10.2.0/lib/tls/sse2", 0xbffa409c) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/tls/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/oracle/product/10.2.0/lib/tls", 0xbffa409c) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/i686/sse2/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/oracle/product/10.2.0/lib/i686/sse2", 0xbffa409c) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/i686/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/oracle/product/10.2.0/lib/i686", 0xbffa409c) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/sse2/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/u01/oracle/product/10.2.0/lib/sse2", 0xbffa409c) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libsqlplus.so", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/lib/libclntsh.so.10.1", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/lib/libnnz10.so", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/tls/i686/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/lib/tls/i686/sse2", 0xbffa4048) = -1 ENOENT (No such file or directory)
open("/lib/tls/i686/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/lib/tls/i686", 0xbffa4048)     = -1 ENOENT (No such file or directory)
open("/lib/tls/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/lib/tls/sse2", 0xbffa4048)     = -1 ENOENT (No such file or directory)
open("/lib/tls/libdl.so.2", O_RDONLY)   = -1 ENOENT (No such file or directory)
stat64("/lib/tls", 0xbffa4048)          = -1 ENOENT (No such file or directory)
open("/lib/i686/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat64("/lib/i686/sse2", 0xbffa4048)    = -1 ENOENT (No such file or directory)
open("/lib/i686/libdl.so.2", O_RDONLY)  = -1 ENOENT (No such file or directory)
stat64("/lib/i686", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open("/lib/sse2/libdl.so.2", O_RDONLY)  = -1 ENOENT (No such file or directory)
stat64("/lib/sse2", 0xbffa4048)         = -1 ENOENT (No such file or directory)
open("/lib/libdl.so.2", O_RDONLY)       = 3
open("/u01/oracle/product/10.2.0/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/i686/libm.so.6", O_RDONLY)   = -1 ENOENT (No such file or directory)
open("/lib/libm.so.6", O_RDONLY)        = 3
open("/u01/oracle/product/10.2.0/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/i686/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/libpthread.so.0", O_RDONLY)  = 3
open("/u01/oracle/product/10.2.0/lib/libnsl.so.1", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libnsl.so.1", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/i686/libnsl.so.1", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/libnsl.so.1", O_RDONLY)      = 3
open("/u01/oracle/product/10.2.0/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/i686/libc.so.6", O_RDONLY)   = -1 ENOENT (No such file or directory)
open("/lib/libc.so.6", O_RDONLY)        = 3
open("/u01/oracle/product/10.2.0/lib/libsqlplusic.so", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libociicus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libociei.so", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/nls/data/lx1boot.nlb", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/nls/data/lx00001.nlb", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/nls/data/lx20001.nlb", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/nls/data/lx10001.nlb", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/nls/data/lx40011.nlb", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/nls/data/lx40001.nlb", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/sqlplus/mesg/sp1us.msb", O_RDONLY) = 3
open("/u01/oracle/product/10.2.0/sqlplus/mesg/sp2us.msb", O_RDONLY) = 4
open("/u01/oracle/product/10.2.0/sqlplus/mesg/cpyus.msb", O_RDONLY) = 5
open("/etc/localtime", O_RDONLY)        = 6
open("/u01/oracle/product/10.2.0/oracore/zoneinfo/timezlrg.dat", O_RDONLY) = 6
open("/u01/oracle/product/10.2.0/rdbms/mesg/ocius.msb", O_RDONLY) = 6

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 28 05:26:43 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

getcwd("/home/oracle"..., 256)          = 13  --获取家目录
access("/u01/oracle/product/10.2.0/network/admin/sqlnet.ora", F_OK) = 0  --读取sqlnet
open("/u01/oracle/product/10.2.0/network/admin/sqlnet.ora", O_RDONLY|O_LARGEFILE) = 7
access("/home/oracle/.sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/cli_20233.trc", F_OK) = -1 ENOENT (No such file or directory)
access("/u01/oracle/product/10.2.0/network/admin/sqlnet.ora", F_OK) = 0
open("/u01/oracle/product/10.2.0/network/admin/sqlnet.ora", O_RDONLY|O_LARGEFILE) = 7
access("/etc/intchg.ora", F_OK)         = -1 ENOENT (No such file or directory)
access("/u01/oracle/product/10.2.0/network/admin/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnav.ora", F_OK)         = -1 ENOENT (No such file or directory)
access("/u01/oracle/product/10.2.0/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnames.ora", F_OK)       = -1 ENOENT (No such file or directory)
access("/u01/oracle/product/10.2.0/network/admin/tnsnames.ora", F_OK) = 0  --读取tnsname.ora
stat64("/u01/oracle/product/10.2.0/network/admin/tnsnames.ora", {st_mode=S_IFREG|0640, st_size=509, ...}) = 0
open("/u01/oracle/product/10.2.0/network/admin/tnsnames.ora", O_RDONLY|O_LARGEFILE) = 7
open("/etc/nsswitch.conf", O_RDONLY)    = 7
open("/u01/oracle/product/10.2.0/lib/libnss_files.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/u01/oracle/product/10.2.0/lib/libnss_files.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/i686/libnss_files.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/libnss_files.so.2", O_RDONLY) = 7
open("/etc/passwd", O_RDONLY)           = 7   --读取用户和书写/proc的cmdline 和主机名
open("/proc/self/cmdline", O_RDONLY)    = 7
getcwd("/home/oracle"..., 256)          = 13
open("/etc/passwd", O_RDONLY)           = 7
open("/etc/resolv.conf", O_RDONLY)      = 8
open("/etc/host.conf", O_RDONLY)        = 8
open("/etc/hosts", O_RDONLY)            = 8
readlink("/proc/self/fd/0", "/dev/pts/7"..., 4095) = 10
open("/etc/passwd", O_RDONLY)           = 9
open("/u01/oracle/product/10.2.0/nls/data/lx2001f.nlb", O_RDONLY) = 9
open("/u01/oracle/product/10.2.0/nls/data/lx207d0.nlb", O_RDONLY) = 9
getcwd("/home/oracle"..., 256)          = 13
open("/u01/oracle/product/10.2.0/nls/data/lx1boot.nlb", O_RDONLY) = 9
open("/etc/hostid", O_RDONLY|O_LARGEFILE) = -1 ENOENT (No such file or directory)
open("/etc/hosts", O_RDONLY)            = 9
open("/etc/hostid", O_RDONLY|O_LARGEFILE) = -1 ENOENT (No such file or directory)
open("/etc/hosts", O_RDONLY)            = 9
open("/u01/oracle/product/10.2.0/rdbms/mesg/oraus.msb", O_RDONLY) = 9

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

access("login.sql", F_OK)               = -1 ENOENT (No such file or directory)
access("/u01/oracle/product/10.2.0/sqlplus/admin/glogin.sql", F_OK) = 0   --最后读取文件
statfs("/u01/oracle/product/10.2.0/sqlplus/admin/glogin.sql", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=4495335, f_bfree=1432794, f_bavail=1200756, f_files=4643968, f_ffree=4516281, f_fsid={0, 0}, f_namelen=255, f_frsize=4096}) = 0
open("/u01/oracle/product/10.2.0/sqlplus/admin/glogin.sql", O_RDONLY|O_LARGEFILE) = 9

 
                     
 [oracle@yws ~]$ strace -e trace=network sqlplus scott/tiger@yws      --跟踪网络  


SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 28 05:33:01 2014


Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


socket(PF_FILE, SOCK_STREAM, 0)         = 7
connect(7, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0)         = 7
connect(7, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_INET, SOCK_DGRAM, IPPROTO_IP) = 7
bind(7, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("127.0.0.1")}, 16) = 0  --localhost
getsockname(7, {sa_family=AF_INET, sin_port=htons(13688), sin_addr=inet_addr("127.0.0.1")}, [16]) = 0
getpeername(7, 0xbfdaccd0, [16])        = -1 ENOTCONN (Transport endpoint is not connected)
getsockopt(7, SOL_SOCKET, SO_SNDBUF, [262144], [4]) = 0
getsockopt(7, SOL_SOCKET, SO_RCVBUF, [1048576], [4]) = 0
socket(PF_FILE, SOCK_STREAM, 0)         = 8
connect(8, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0)         = 8
connect(8, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 8
connect(8, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("192.168.1.234")}, 16) = 0  --和监听端口进行通信
getsockname(8, {sa_family=AF_INET, sin_port=htons(33617), sin_addr=inet_addr("192.168.1.234")}, [16]) = 0
getsockopt(8, SOL_SOCKET, SO_SNDBUF, [50436], [4]) = 0
getsockopt(8, SOL_SOCKET, SO_RCVBUF, [87680], [4]) = 0
setsockopt(8, SOL_TCP, TCP_NODELAY, [1], 4) = 0


点击(此处)折叠或打开

  1. % time seconds usecs/call calls errors syscall
  2. ------ ----------- ----------- --------- --------- ----------------
  3.  41.03 0.002153 22 100 read
  4.  32.32 0.001696 53 32 write
  5.   9.62 0.000505 7 75 36 open
  6.   8.00 0.000420 9 45 close
  7.   6.31 0.000331 10 33 lseek
  8.   1.49 0.000078 6 14 munmap
  9.   1.24 0.000065 1 45 mmap2
  10.   0.00 0.000000 0 1 execve
  11.   0.00 0.000000 0 14 10 access
  12.   0.00 0.000000 0 1 times
  13.   0.00 0.000000 0 9 brk
  14.   0.00 0.000000 0 1 ioctl
  15.   0.00 0.000000 0 1 getppid
  16.   0.00 0.000000 0 1 setrlimit
  17.   0.00 0.000000 0 15 gettimeofday
  18.   0.00 0.000000 0 1 readlink
  19.   0.00 0.000000 0 1 statfs
  20.   0.00 0.000000 0 4 uname
  21.   0.00 0.000000 0 15 mprotect
  22.   0.00 0.000000 0 5 rt_sigaction
  23.   0.00 0.000000 0 3 rt_sigprocmask
  24.   0.00 0.000000 0 3 getcwd
  25.   0.00 0.000000 0 2 getrlimit
  26.   0.00 0.000000 0 15 13 stat64
  27.   0.00 0.000000 0 26 fstat64
  28.   0.00 0.000000 0 4 getuid32
  29.   0.00 0.000000 0 27 fcntl64
  30.   0.00 0.000000 0 3 futex
  31.   0.00 0.000000 0 1 set_thread_area
  32.   0.00 0.000000 0 1 set_tid_address
  33.   0.00 0.000000 0 1 set_robust_list
  34.   0.00 0.000000 0 6 socket
  35.   0.00 0.000000 0 1 bind
  36.   0.00 0.000000 0 5 4 connect
  37.   0.00 0.000000 0 2 getsockname
  38.   0.00 0.000000 0 1 1 getpeername
  39.   0.00 0.000000 0 3 setsockopt
  40.   0.00 0.000000 0 4 getsockopt
  41. ------ ----------- ----------- --------- --------- ----------------
  42. 100.00 0.005248 521 64 total
通过跟踪可以清楚看到哪里有问题.

请使用浏览器的分享功能分享到微信等