一个老系统的老问题:ORA-01031: insufficient privileges

联系:QQ(5163721)

标题:一个老系统的老问题:ORA-01031: insufficient privileges

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

今天打算整合一下所有的vm(除了12c),把他们都放到一个vm中,结果发现遇到郁闷问题:

[oracle@lunar dbs]$ ss

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 9 20:40:25 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 
[oracle@lunar dbs]$

–第一反应是口令文件有问题,于是检查,发现果然缺少口令文件:

[oracle@lunar dbs]$ pwd
/oracle/9.2.0/orahome/dbs
[oracle@lunar dbs]$ ll
total 52
-rw-r--r--  1 oracle oinstall 12920 Mar  9  2002 initdw.ora
-rw-r--r--  1 oracle oinstall  8385 Mar 10  2002 init.ora
-rw-rw----  1 oracle oinstall    24 Oct  9 10:27 lkLUNAR
-rw-r-----  1 oracle oinstall  2560 Nov  9 13:45 spfilelunar.ora
[oracle@lunar dbs]$

--自己生成一个:
[oracle@lunar dbs]$ orapwd file=/oracle/9.2.0/orahome/dbs/orapwlunar password=oracle entries=10
[oracle@lunar dbs]$ ll
total 60
-rw-r--r--  1 oracle oinstall 12920 Mar  9  2002 initdw.ora
-rw-r--r--  1 oracle oinstall  8385 Mar 10  2002 init.ora
-rw-rw----  1 oracle oinstall    24 Oct  9 10:27 lkLUNAR
-rwSr-----  1 oracle oinstall  2560 Nov  9 20:44 orapwlunar
-rw-r-----  1 oracle oinstall  2560 Nov  9 13:45 spfilelunar.ora
[oracle@lunar dbs]$ ss

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 9 20:44:25 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 
[oracle@lunar dbs]$
[oracle@lunar dbs]$ ll
total 60
-rw-r--r--  1 oracle oinstall 12920 Mar  9  2002 initdw.ora
-rw-r--r--  1 oracle oinstall  8385 Mar 10  2002 init.ora
-rw-rw----  1 oracle oinstall    24 Oct  9 10:27 lkLUNAR
-rwSr-----  1 oracle oinstall  2560 Nov  9 20:44 orapwlunar
-rw-r-----  1 oracle oinstall  2560 Nov  9 13:45 spfilelunar.ora
[oracle@lunar dbs]$ 

–还是不行,是否因为大小写的关系(因为之前的那个vm已经迁移到活动硬盘了,懒得打开查看SID了),因此修改为大写试试看:

[oracle@lunar dbs]$mv orapwlunar orapwLUNAR
[oracle@lunar dbs]$ ss

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 9 20:45:07 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 
[oracle@lunar dbs]$ 

--还是不行啊,改回来吧
[oracle@lunar dbs]$ mv orapwLUNAR orapwlunar
[oracle@lunar dbs]$ ll
total 60
-rw-r--r--  1 oracle oinstall 12920 Mar  9  2002 initdw.ora
-rw-r--r--  1 oracle oinstall  8385 Mar 10  2002 init.ora
-rw-rw----  1 oracle oinstall    24 Oct  9 10:27 lkLUNAR
-rwSr-----  1 oracle oinstall  2560 Nov  9 20:44 orapwlunar
-rw-r-----  1 oracle oinstall  2560 Nov  9 13:45 spfilelunar.ora
[oracle@lunar dbs]$ 

–使用密码文件的认证方式测试本地登录是可以的:

[oracle@lunar dbs]$ sqlplus 'sys/oracle as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 9 20:47:30 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  135337420 bytes
Fixed Size                   452044 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> alter user sys identified by oracle;

User altered.

SQL> alter user system identified by oracle;

User altered.

SQL> conn / as sysdba           --但是使用OS认证的登录方式还是不行
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL> conn sys/oracle as sysdba
Connected.
SQL> show parameter pass

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
remote_login_passwordfile            string
EXCLUSIVE
SQL> 

–怀疑是sqlnet.ora的设置有问题,结果居然没有,这个vm是7,8年前的,不知道什么原因,居然没有这个文件,创建一个吧:

[oracle@lunar admin]$ ll
total 52
-rw-r--r--  1 oracle oinstall  825 Jul 26  2003 libnk59.def
-rw-r--r--  1 oracle oinstall  657 Oct  9 10:27 listener.ora
drwxr-xr-x  2 oracle oinstall 4096 Oct  9 10:04 samples
-rw-r--r--  1 oracle oinstall  130 Mar 19  2002 shrept.lst
-rw-r--r--  1 oracle oinstall   24 Aug  8  2000 snmp_ro.ora
-rw-r--r--  1 oracle oinstall  579 Oct  9 10:27 tnsnames.ora
[oracle@lunar admin]$ 

[oracle@lunar admin]$ vi sqlnet.ora 
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

[oracle@lunar admin]$ ll
total 52
-rw-r--r--  1 oracle oinstall  825 Jul 26  2003 libnk59.def
-rw-r--r--  1 oracle oinstall  657 Oct  9 10:27 listener.ora
drwxr-xr-x  2 oracle oinstall 4096 Oct  9 10:04 samples
-rw-r--r--  1 oracle oinstall  130 Mar 19  2002 shrept.lst
-rw-r--r--  1 oracle oinstall   24 Aug  8  2000 snmp_ro.ora
-rwxrwxrwx  1 oracle oinstall   89 Nov  9 21:25 sqlnet.ora
-rw-r--r--  1 oracle oinstall  579 Oct  9 10:27 tnsnames.ora
[oracle@lunar admin]$ 

--创建好了,再试试看:
[oracle@lunar admin]$ ss
Message 395 not found;  product=SQLPlus; facility=SP2
Message 396 not found;  product=SQLPlus; facility=SP2
Message 397 not found;  product=SQLPlus; facility=SP2
Message 398 not found;  product=SQLPlus; facility=SP2
Message 399 not found;  product=SQLPlus; facility=SP2
Message 400 not found;  product=SQLPlus; facility=SP2
Message 403 not found;  product=SQLPlus; facility=SP2
Message 743 not found;  product=SQLPlus; facility=SP2
Message 402 not found;  product=SQLPlus; facility=SP2
Message 401 not found;  product=SQLPlus; facility=SP2
[oracle@lunar admin]$ env|grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/home/oracle/oracle/product
ORA_NLS33=/oracle/9.2.0/orahome/ocommon/nls/admin/data
ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
[oracle@lunar admin]$ 

–这里居然冒出来ORACLE_HOME设置不对,郁闷了。。。。。。

–重新执行环境设置脚本:

[oracle@lunar admin]$ cat ~/ora920.env 
export ORACLE_BASE=/oracle/9.2.0
export ORACLE_HOME=$ORACLE_BASE/orahome
export ORACLE_SID=lunar
export PATH=$ORACLE_HOME/bin:$PATH
export LIBPATH=$ORACLE_HOME/lib
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;
export NLS_LANG=american_america.AL32UTF8

alias ss="sqlplus '/ as sysdba'"

[oracle@lunar admin]$ 

--然后再试试看:
[oracle@lunar admin]$ ss

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 9 20:57:59 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 
[oracle@lunar admin]$ 
[oracle@lunar admin]$ 

–这是,怀疑是oracle用户本身的问题,于是检查oracle用户的属组:

[oracle@lunar admin]$ id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall)
[oracle@lunar admin]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall) context=user_u:system_r:unconfined_t
[oracle@lunar admin]$ 

–经过小伙伴的提醒,发现这个VM居然使用了破烂SELinux,sigh。。。。。

[root@lunar ~]# vi /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - SELinux is fully disabled.
SELINUX=enforcing
# SELINUXTYPE= type of policy in use. Possible values are:
#       targeted - Only targeted network daemons are protected.
#       strict - Full SELinux protection.
SELINUXTYPE=targeted

–SELinux系统比起通常的Linux系统来,安全性能要高的多,它通过对于用户,进程权限的最小化,即使受到攻击,进程或者用户权限被夺去,也不会对整个系统造成重大影响。
–但是往往有些应用的使用也会受到限制,比如有时候tar一个大目录的时候可能会失败,有时候ftp东西的时候有问题,等等
–其实一般ORACLE的文档上也是明确说明要关闭SELinux的,例如:
–5.11 Error While Loading Shared Library When SELinux is Enabled on Red Hat Enterprise Linux 5
–SQL*Plus and Oracle Call Interface (OCI) program calls fail when selinux is in Enforcing mode on Red Hat Enterprise Linux 5.
–Workaround: Shift selinux to Permissive mode on the system.
–This issue is tracked with Oracle bug 6079461.

–于是,将 SELINUX=enforcing 修改为 SELINUX=disabled,这样,下次启动系统后将会停止SElinux
–当然,也可以修改linux启动内核的参数:

[root@lunar ~]# cat /boot/grub/menu.lst
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/VolGroup00/LogVol00
#          initrd /initrd-version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Red Hat Enterprise Linux AS (2.6.9-42.ELsmp)
        root (hd0,0)
        kernel /vmlinuz-2.6.9-42.ELsmp ro root=/dev/VolGroup00/LogVol00 rhgb quiet selinux=0
        initrd /initrd-2.6.9-42.ELsmp.img
title Red Hat Enterprise Linux AS-up (2.6.9-42.EL)
        root (hd0,0)
        kernel /vmlinuz-2.6.9-42.EL ro root=/dev/VolGroup00/LogVol00 rhgb quiet selinux=0
        initrd /initrd-2.6.9-42.EL.img
[root@lunar ~]# 

–顺便记录和检查下当前SELINUX的policy:

[root@lunar ~]# sestatus
SELinux status:         enabled  --已经启用了
SELinuxfs mount:        /selinux
Current mode:           permissive  --当前模式是警告模式
Mode from config file:  error (Success)
Policy version:         18
Policy from config file:targeted

Policy booleans:
allow_syslog_to_console inactive
allow_ypbind            inactive
dhcpd_disable_trans     inactive
httpd_builtin_scripting active
httpd_disable_trans     inactive
httpd_enable_cgi        active
httpd_enable_homedirs   active
httpd_ssi_exec          active
httpd_tty_comm          inactive
httpd_unified           active
mysqld_disable_trans    inactive
named_disable_trans     inactive
named_write_master_zonesinactive
nscd_disable_trans      inactive
ntpd_disable_trans      inactive
pegasus_disable_trans   inactive
portmap_disable_trans   inactive
postgresql_disable_transinactive
snmpd_disable_trans     inactive
squid_disable_trans     inactive
syslogd_disable_trans   inactive
use_nfs_home_dirs       inactive
use_samba_home_dirs     inactive
use_syslogng            inactive
winbind_disable_trans   inactive
ypbind_disable_trans    inactive
[root@lunar ~]# 

–重启linux系统后,检查是否已经关闭SELinux:

[root@lunar ~]# getenforce
Disabled
[root@lunar ~]# 

--再次启动sqlplus,看来已经ok了,O(∩_∩)O哈哈~
[oracle@lunar ~]$ . ora920.env 
[oracle@lunar admin]$ ss

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 9 21:25:40 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> 


此条目发表在 FAQ, ORA-XXXXX 分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注