关于ORA-01031: insufficient privileges 问题解决笔记
A) File $Oracle_HOME/network/admin/sqlnet.ora
这个是涉及oracle 是os 认证,还是密码文件认证
$ORACLE_HOME/network/admin/sqlnet.ora中的AUTHENTICATION_SERVICES参数:
1、在windows下,SQLNET.AUTHENTICATION_SERVICES必须设置为NTS才能使用OS认证;
不设置或者设置为其他任何值都不能使用OS认证。
2、在linux下,在SQLNET.AUTHENTICATION_SERVICES的值设置为ALL,或者不设置的情况下,OS验证才能成功;
此外还有这个参数
spfile中的REMOTE_LOGIN_PASSWORDFILE参数:
1、值为exclusive时,远程能作为sysdba身份登录
2、值为none时,以sysdba身份无法登录
B) File $ORACLE_HOME/rdbms/lib/config.c(or config.s)
C) The output of command "id"
oracle 用户拥有正确辅组,如果没有dba 组则也无法进行操作系统认证登入数据库
一般以上2个是常见的原因,如果查证后还是有问题可以尝试以下方法:
D) A trace file obtained with the following commands:
Linux:
strace -o /tmp/strace_sysdba.output -cfT sqlplus / as sysdba
AIX, Solaris:
truss -fea -o /tmp/truss_sysdba.output sqlplus / as sysdba
HP-UX:
tusc -afpo /tmp/tusc_sysdba.output -aef sqlplus / as sysdba
For Windows
A) File %ORACLE_HOME%/network/admin/sqlnet.ora
B) The output of the commands "echo %username% and "NET LOCALGROUP ORA_DBA"
C) A pair of client/server SQL*Net traces obtained while reproducing the problem as per Note 395525.1 and Note 374116.1
以下是一个测试案例:【去掉oracle 的 dba 组】
[oracle@vmrac1 ~]$ strace -o /tmp/strace_sysdba.output -cfT sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 26 16:04:46 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name: sys
Enter password:
ERROR:
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1200 (asmadmin), current egid
= 1000 (oinstall)
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
这个是跟踪后的trace 文件不是很看的懂:
[oracle@vmrac1 ~]$ cat /tmp/strace_sysdba.output
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
45.06 0.003262 2 1611 328 open
12.50 0.000905 1 881 5 read
9.55 0.000691 5 132 write
7.90 0.000572 0 1382 mmap
4.68 0.000339 0 886 munmap
4.64 0.000336 11 31 brk
3.59 0.000260 52 5 execve
2.43 0.000176 1 307 fstat
2.33 0.000169 1 136 mprotect
2.04 0.000148 49 3 readlink
1.17 0.000085 0 1340 close
0.93 0.000067 0 233 rt_sigaction
0.86 0.000062 0 272 55 stat
0.55 0.000040 2 20 getcwd
0.52 0.000038 0 144 144 mkdir
0.47 0.000034 1 67 65 access
0.39 0.000028 0 153 fcntl
0.37 0.000027 1 23 socket
0.00 0.000000 0 42 2 lstat
0.00 0.000000 0 116 lseek
0.00 0.000000 0 67 rt_sigprocmask
0.00 0.000000 0 10 2 ioctl
0.00 0.000000 0 8 pipe
0.00 0.000000 0 96 84 shmget
0.00 0.000000 0 20 shmat
0.00 0.000000 0 4 dup
0.00 0.000000 0 18 18 connect
0.00 0.000000 0 5 bind
0.00 0.000000 0 4 clone
0.00 0.000000 0 51 uname
0.00 0.000000 0 20 shmdt
0.00 0.000000 0 22 getdents
0.00 0.000000 0 4 chdir
0.00 0.000000 0 8 chmod
0.00 0.000000 0 168 getrlimit
0.00 0.000000 0 31 times
0.00 0.000000 0 32 getuid
0.00 0.000000 0 1 getgid
0.00 0.000000 0 14 geteuid
0.00 0.000000 0 2 getegid
0.00 0.000000 0 5 getppid
0.00 0.000000 0 8 4 setsid
0.00 0.000000 0 4 sigaltstack
0.00 0.000000 0 5 arch_prctl
0.00 0.000000 0 15 setrlimit
0.00 0.000000 0 8 gettid
0.00 0.000000 0 17 futex
0.00 0.000000 0 5 set_tid_address
0.00 0.000000 0 5 set_robust_list
------ ----------- ----------- --------- --------- ----------------
100.00 0.007239 8441 707 total
[oracle@vmrac1 ~]$ strace -o /tmp/strace_sysdb1a.output -cfT sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 26 16:06:06 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied