段亚东的Oracle笔记

上午请假,应用打电话说连不上了,打电话给同事,让看看是不是监听挂了
半个小时,同事打电话说都搞好了,是监听挂了.
下午回到省厅,看了看没啥情况
刚好晚上要加班,就来梳理一下这个监听这两天为啥老是会自动停了
先提取监听日志,看到如下一段信息


26-OCT-2016 09:10:22 * service_update * orcl1 * 0
26-OCT-2016 09:10:22 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
Wed Oct 26 09:10:25 2016
26-OCT-2016 09:10:25 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
26-OCT-2016 09:10:27 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
26-OCT-2016 09:10:29 * pid * 0
Wed Oct 26 10:54:25 2016
Non critical error DIA-48180 caught while writing to trace file "/u01/app/oracle/diag/tnslsnr/p740a2/listener/trace/ora_48037944_1.trc"
Error message: IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 1
Writing to the above trace file is disabled for now on...
System parameter file is /u01/app/11.2.0/grid/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/p740a2/listener/alert/log.xml
Trace information written to /u01/app/oracle/diag/tnslsnr/p740a2/listener/trace/ora_48037944_1.trc
Trace level is currently 0

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
26-OCT-2016 10:54:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=p740a2)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647296)) * status * 0
26-OCT-2016 10:54:26 * version * 0
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.65)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.115)(PORT=1521)))
26-OCT-2016 10:54:27 * service_register * LsnrAgt * 0
26-OCT-2016 10:54:27 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client


看到监听是在10:54分停了
然后去分析这个trc文件
查看trc文件,发现报错了,竟然没有这个文件


p740a2:/#more /u01/app/oracle/diag/tnslsnr/p740a2/listener/trace/ora_48037944_1.trc
/u01/app/oracle/diag/tnslsnr/p740a2/listener/trace/ora_48037944_1.trc: A file or directory in the path name does not exist.
p740a2:/#ls -l /u01/app/oracle/diag/tnslsnr/p740a2/listener/trace/
total 680952
-rw-r--r-- 1 grid oinstall 3670469 Oct 26 21:28 listener.log
-rw-r----- 1 grid oinstall 13791 Sep 28 16:29 listener.log.bak

没有咋办呢,再来看上面的日志信息.


Wed Oct 26 10:54:25 2016
Non critical error DIA-48180 caught while writing to trace file "/u01/app/oracle/diag/tnslsnr/p740a2/listener/trace/ora_48037944_1.trc"
Error message: IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 1

没有权限
然后来看看这个目录什么权限


p740a2:/#ls -l /u01/app/oracle/diag/tnslsnr/p740a2/listener
total 0
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 alert
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 cdump
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 incident
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 incpkg
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 lck
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 metadata
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 metadata_dgif
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 metadata_pv
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 stage
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 sweep
drwxr-xr-x 2 775 oinstall 256 Oct 26 15:07 trace


可以看到,trace目录时可读的,
但是!775 是什么鬼?


p740a2:/#id oracle
uid=1001(oracle) gid=1000(oinstall) groups=1021(asmdba),1031(dba)
p740a2:/#id grid
uid=1000(grid) gid=1000(oinstall) groups=1020(asmadmin),1021(asmdba),1022(asmoper),1031(dba),1032(oper)
p740a2:/#id 775
3004-820 User not found in /etc/passwd file

通过查看,并没有775这个用户
并且初步可以判定,是这个写权限引起的trc文件不能写了,再去确认一下


p740a2:/#cd /u01/app/oracle/diag/
p740a2:/u01/app/oracle/diag#ls -l
total 0
drwxr-x--- 3 775 oinstall 256 Mar 27 2014 asm
drwxrwxr-x 3 oracle oinstall 256 Sep 06 15:56 clients
drwxrwxr-x 2 oracle oinstall 256 Mar 27 2014 crs
drwxrwxr-x 2 oracle oinstall 256 Mar 27 2014 diagtool
drwxrwxr-x 2 oracle oinstall 256 Mar 27 2014 lsnrctl
drwxrwxr-x 2 oracle oinstall 256 Mar 27 2014 netcman
drwxrwxr-x 2 oracle oinstall 256 Mar 27 2014 ofm
drwxrwxr-x 3 oracle oinstall 256 Mar 27 2014 rdbms
drwxr-xr-x 3 775 oinstall 256 Mar 27 2014 tnslsnr
p740a2:/u01/app/oracle/diag#cd ../
p740a2:/u01/app/oracle#ls -l
total 352
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 Clusterware
drwxr-x--- 3 oracle oinstall 256 Mar 27 2014 admin
drwxrwxr-x 7 775 oinstall 256 Mar 27 2014 cfgtoollogs
drwxr-xr-x 2 775 oinstall 256 Mar 27 2014 checkpoints
drwxrwxr-x 11 775 oinstall 4096 Mar 27 2014 diag
-rw-r--r-- 1 oracle oinstall 109170 Aug 04 2014 gnbzuser20140613.log
drwxr-xr-x 3 775 oinstall 256 Mar 27 2014 p740a2
drwxr-xr-x 3 oracle oinstall 256 Mar 27 2014 product
-rw-r--r-- 1 oracle oinstall 63327 Jun 13 2014 qgbzsj20131206.log
drwxrwxrwx 3 oracle oinstall 256 Mar 14 2014 rman
p740a2:/u01/app/oracle#cd ../
p740a2:/u01/app#ls -l
total 8
drwxr-xr-x 3 root oinstall 256 Mar 27 2014 11.2.0
drwxrwxr-x 2 grid oinstall 256 Mar 27 2014 grid
drwxrwx--- 6 grid oinstall 256 Mar 27 2014 oraInventory
drwxr-xr-x 10 oracle oinstall 4096 May 20 2015 oracle

看到这里彻底晕了,看一下diag目录的修改情况.2014年,估计时刚装库的时候弄的


p740a2:/u01/app/oracle#istat diag
Inode 48815 on device 10/13 Directory
Protection: rwxrwxr-x
Owner: 775() Group: 1000(oinstall)
Link count: 11 Length 4096 bytes

Last updated: Thu Mar 27 15:43:40 CST 2014
Last modified: Thu Mar 27 15:43:40 CST 2014
Last accessed: Wed Oct 26 21:32:40 CST 2016


那么问题来了
领导说不让改
退而求其次说只改各trace目录的权限先收个trc文件也好判断

最后一个问题也来了,到底为啥监听会停止,下回再说
这次就主要说下权限吧,chmod和chown一定不能用错.

The end