從函式呼叫角度檢視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

點選(此處)摺疊或開啟

% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
41.03 0.002153 22 100 read
32.32 0.001696 53 32 write
9.62 0.000505 7 75 36 open
8.00 0.000420 9 45 close
6.31 0.000331 10 33 lseek
1.49 0.000078 6 14 munmap
1.24 0.000065 1 45 mmap2
0.00 0.000000 0 1 execve
0.00 0.000000 0 14 10 access
0.00 0.000000 0 1 times
0.00 0.000000 0 9 brk
0.00 0.000000 0 1 ioctl
0.00 0.000000 0 1 getppid
0.00 0.000000 0 1 setrlimit
0.00 0.000000 0 15 gettimeofday
0.00 0.000000 0 1 readlink
0.00 0.000000 0 1 statfs
0.00 0.000000 0 4 uname
0.00 0.000000 0 15 mprotect
0.00 0.000000 0 5 rt_sigaction
0.00 0.000000 0 3 rt_sigprocmask
0.00 0.000000 0 3 getcwd
0.00 0.000000 0 2 getrlimit
0.00 0.000000 0 15 13 stat64
0.00 0.000000 0 26 fstat64
0.00 0.000000 0 4 getuid32
0.00 0.000000 0 27 fcntl64
0.00 0.000000 0 3 futex
0.00 0.000000 0 1 set_thread_area
0.00 0.000000 0 1 set_tid_address
0.00 0.000000 0 1 set_robust_list
0.00 0.000000 0 6 socket
0.00 0.000000 0 1 bind
0.00 0.000000 0 5 4 connect
0.00 0.000000 0 2 getsockname
0.00 0.000000 0 1 1 getpeername
0.00 0.000000 0 3 setsockopt
0.00 0.000000 0 4 getsockopt
------ ----------- ----------- --------- --------- ----------------
100.00 0.005248 521 64 total

透過跟蹤可以清楚看到哪裡有問題.

Strace sqlplus scott/tiger@yws 很好處理tns問題

相關文章