關於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
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@vmrac1 ~]$ cat /tmp/strace_sysdb1a.output
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
31.05 0.001321 1 1600 326 open
19.93 0.000848 1 839 5 read
16.06 0.000683 137 5 execve
11.71 0.000498 0 1375 mmap
8.56 0.000364 0 773 munmap
6.16 0.000262 2 138 write
2.37 0.000101 0 1329 close
1.55 0.000066 0 303 fstat
1.18 0.000050 1 96 84 shmget
0.87 0.000037 0 227 rt_sigaction
0.56 0.000024 1 21 socket
0.00 0.000000 0 227 55 stat
0.00 0.000000 0 34 4 lstat
0.00 0.000000 0 94 lseek
0.00 0.000000 0 136 mprotect
0.00 0.000000 0 31 brk
0.00 0.000000 0 65 rt_sigprocmask
0.00 0.000000 0 3 1 ioctl
0.00 0.000000 0 66 65 access
0.00 0.000000 0 8 pipe
0.00 0.000000 0 20 shmat
0.00 0.000000 0 4 dup
0.00 0.000000 0 16 16 connect
0.00 0.000000 0 5 bind
0.00 0.000000 0 4 clone
0.00 0.000000 0 49 uname
0.00 0.000000 0 20 shmdt
0.00 0.000000 0 139 fcntl
0.00 0.000000 0 22 getdents
0.00 0.000000 0 20 getcwd
0.00 0.000000 0 4 chdir
0.00 0.000000 0 108 108 mkdir
0.00 0.000000 0 2 readlink
0.00 0.000000 0 6 chmod
0.00 0.000000 0 161 getrlimit
0.00 0.000000 0 28 times
0.00 0.000000 0 29 getuid
0.00 0.000000 0 1 getgid
0.00 0.000000 0 13 geteuid
0.00 0.000000 0 1 getegid
0.00 0.000000 0 4 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 14 setrlimit
0.00 0.000000 0 8 gettid
0.00 0.000000 0 16 futex
0.00 0.000000 0 5 set_tid_address
0.00 0.000000 0 5 set_robust_list
------ ----------- ----------- --------- --------- ----------------
100.00 0.004254 8091 668 total
