sys用户权限不足,本地登录失败 |ORA-01031 insufficient privileges|

      机器总喜欢挑放假的时候出问题,“双节”(中秋、国庆)快到了,对于搞系统运维的工程师来说其实并不轻松,于是今天赶紧装起一台数据库备用服务器以备半夜“机”叫。

      安装OS就没什么好说的了,从模板机中托一个出来改改IP和HostName就完事了,安装Oracle数据库也不是第一次了,找了一个静默安装的响应文件改一下把数据库装起来,虽然计划是搭建DataGuard的,但是为了测试安装是否成功,还是选择了建库,一切装完后,例行登录数据库发现出了状况:

[oracle@wz_oracle2 dbs]$ sqlplus system/oracle as sysdba    

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 21 16:41:01 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name: 

      一般来说,ORA-01031都是出现在忘记输入as sysdba的时候出现的,这是为什么呢?

      试一下其他方式登录:

[oracle@wz_oracle2 dbs]$
[oracle@wz_oracle2 dbs]$
[oracle@wz_oracle2 dbs]$ sqlplus system/oracle 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 21 16:41:05 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

[oracle@wz_oracle2 dbs]
[oracle@wz_oracle2 dbs]
[oracle@wz_oracle2 dbs]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 21 16:41:16 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

      system 可以登录,由于是新装的数据库,所以system用户不会具有sysdba的权限,可以判断system用的是密码认证方式,密码登录没有问题证明数据库没有问题;

      sys用户默认是sysdba,可以通过操作系统认证登录(dba组中的用户自动视为认证通过)或者使用密码文件进行认证的方式登录(常见于远程登录),修改密码文件的名字再登录:

[oracle@wz_oracle2 dbs]$ mv orapwora8i orapwora8i---
[oracle@wz_oracle2 dbs]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 21 20:34:28 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name: 

      登录失败,可以判断 sys 使用了密码文件进行登录认证。

      现在可以基本认为 sys 用户在操作系统认证这一关过不去了。由于这次安装是使用响应文件进行静默安装的,估计问题就出在这个响应文件上,于是再次仔细审查响应文件的每一个选项,特别是涉及“组”的选项:

[oracle@wz_oracle2 ~]$ id
uid=500(oracle) gid=500(dba) groups=500(dba)
[oracle@wz_oracle2 ~]$ cat enterprise.rsp | egrep -i "grp|group"
UNIX_GROUP_NAME="dba"
s_nameForDBAGrp="oracle"
s_nameForOPERGrp="oracle"
[oracle@wz_oracle2 ~]$

      果然,指定的DBA组合Oper组不对,大意啦~~~~~~。

      这个问题应该如何解决呢,当然全部推倒重来也是可以的,但是有没有“成本”更小的方法呢?求助于万能的google轻易地找到了解决方案,就是修改 $ORACLE_HOME/rdbms/lib/config.c 这个文件:

[oracle@wz_oracle2 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c
/*  SS_DBA_GRP defines the UNIX group ID for adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */
 
#define SS_DBA_GRP "oracle"   /* 改成 dba */
#define SS_OPER_GRP "oracle"  /* 改成 dba */
 
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

再执行 relink all 就可以了:

[oracle@wz_oracle2 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c
/*  SS_DBA_GRP defines the UNIX group ID for adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */
 
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"
 
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};
[oracle@wz_oracle2 ~]$ relink all
.................
.................
.................
.................
.................
[oracle@wz_oracle2 ~]$
[oracle@wz_oracle2 ~]$ sqlplus / as sysdba 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 21 20:48:03 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup ; 
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2022600 bytes
Variable Size             171967288 bytes
Database Buffers          419430400 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL>

      问题解决,使用响应文件静默安装的方式看起来很酷,但是一定要小心谨慎。

posted @ 2010-09-21 20:50  killkill  阅读(28448)  评论(4编辑  收藏  举报