Oracle案例--错误码之ORA-01031

ORA-01031 权限问题解决方法

表象

shell

oracle@mmsc101:~/product/11/network/admin> sqlplus sys/sys@mmsgdb as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun May 15 16:44:47 2011

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

ERROR:
ORA-01031: 鏉冮檺涓嶈冻


Enter user-name: 

报错，ORA-01031，用户权限不足导致，官方参考解决方法信息如下：

shell

oracle@mmsc101:~/product/11/network/admin> oerr ora 01031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.
// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.
oracle@mmsc101:~/product/11/network/admin> 

解决过程

尝试赋权限

shell

grant create session to sys with admin option;
grant dba to sys;
alter user sys default role all;

赋权限后尝试登陆，结果：失败

shell

oracle@mmsc101:~/product/11/network/admin> sqlplus sys/sys@mmsgdb as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun May 15 16:59:57 2011

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

ERROR:
ORA-01031: 鏉冮檺涓嶈冻


Enter user-name: 

之后，查询视图

shell

SQL> select * from v$pwfile_users;

no rows selected

SQL>

视图表中无记录，说明当前oracle并没有使用口令文件，而在系统的dbs目录下，的确是有个口令文件的：orapwmmsgdb，难道口令文件有问题，还是用户的密码不对。这里先否定用户密码不对问题，因为如果密码不对，报错则不是ORA-01031了，而是其他的ORA错误码了，问题就出现在口令文件上了。

dbs目录下，删除原来的口令文件，尝试重新创建新的口令文件。

shell

oracle@mmsc101:~/product/11/dbs> orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

  where
    file - name of password file (required),
    password - password for SYS, will be prompted if not specified at command line,
    entries - maximum number of distinct DBA (optional),
    force - whether to overwrite existing file (optional),
    ignorecase - passwords are case-insensitive (optional),
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
    
  There must be no spaces around the equal-to (=) character.

创建新的口令文件

shell

oracle@mmsc101:~/product/11/dbs> orapwd file=/opt/oracle/product/11/dbs/orapwmmsgdb password=sys

重启数据库

shell

oracle@mmsc101:~/product/11/dbs> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun May 15 17:08:23 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> startup force
ORACLE instance started.

Total System Global Area  567922688 bytes
Fixed Size                  2161600 bytes
Variable Size             243270720 bytes
Database Buffers          314572800 bytes
Redo Buffers                7917568 bytes
Database mounted.
Database opened.
SQL> 

尝试登陆，还是报错，继续定位

shell

oracle@mmsc101:~/product/11/dbs> sqlplus sys/sys@mmsgdb as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun May 15 17:08:54 2011

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

ERROR:
ORA-01031: 鏉冮檺涓嶈冻


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@mmsc101:~/product/11/dbs> 

再次查询视图

shell

SQL> select * from v$pwfile_users;

no rows selected

SQL>

仍然无数据，说明还是没有使用口令文件，继续……

查看tns文件信息

shell

oracle@mmsc101:~/product/11/network/admin> more tnsnames.ora 
MMSGDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.137.49.36)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11)
    )
  )

MMSGDB_37 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.137.49.37)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mmsgdb)
    )
  )
oracle@mmsc101:~/product/11/network/admin>

tns文件信息显示，数据库名是mmsgdb，实例名是ora11，orapw的帮助命令中，并没有写明filename中该如何命名，实际命名规则则是：
file=$ORACLE_HOME/dbs/orapw$ORACLE_SID

$ORACLE_SID是什么

这里的$ORACLE_SID是什么呢？数据库名？实例名？

都不是，确切的讲，这个东西是环境变量值。证据如下：

shell

oracle@mmsc101:~> more .bash_profile 

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/11
export ORA_CRS_HOME=/opt/oracle/product/11
export ORACLE_SID=ora11
export ORACLE_TERM=xterm
export LD_LIBRARY_PATH=$ORACLE_HOME/lib64:$ORACLE_HOME/lib:/usr/lib
export ORACLE_DOC=$ORACLE_HOME/doc
export PATH=$ORACLE_HOME/bin:/sbin:/usr/sbin:/usr/ccs/bin:/usr/bin:/sbin:$ORACLE_HOME/OPatch_11.1.0.8.1/OPatch:$PATH:/bin:/usr/ccs/b
in
export TNS_NAMES=$ORACLE_HOME/network/admin
export TNS_ADMIN=$ORACLE_HOME/network/admin
export DISPLAY=10.137.48.37:1.0
# CLASSPATH must include the following JRE locations:
CLASSPATH=/opt/oracle/product/11/JRE:/opt/oracle/product/11/jlib:/opt/oracle/product/11/rdbms/jlib
export CLASSPATH=$CLASSPATH:/opt/oracle/product/11/network/jlib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

之前orapw创建的口令文件名称是orapwmmsgdb，显然是没有正确的口令文件，导致OS认证失败，尝试与MMSGDB建立连接，没有权限去登陆其他的数据库，自然报权限问题。

获取解决方法

重新创建正确的口令文件：

shell

oracle@mmsc101:~/product/11/network/admin> orapwd file=/opt/oracle/product/11/dbs/orapwora11 password=sys

验证方法是否有效

查询视图

shell

oracle@mmsc101:~/product/11/network/admin> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun May 15 18:05:49 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select * from v$pwfile_users;

USERNAME
--------------------------------------------------------------------------------
SYSDBA          SYSOPER         SYSASM
--------------- --------------- ---------------
SYS
TRUE            TRUE            FALSE


SQL>

已经有数据了，尝试sys用户登陆试试：

shell

oracle@mmsc101:~/product/11/network/admin> sqlplus sys/sys@mmsgdb as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun May 15 17:45:21 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
oracle@mmsc101:~/product/11/network/admin> 

登陆成功，问题得到解决。

总结

1、这个问题，开始是找寻了network/admin目录下的sqlnet.ora文件，认为数据库解析文件出现了问题，也给该文件增加了权限，没啥效果。接着尝试tnsping操作，发现是可以与数据库正常建立连接的，此路就此终止，因为不是sqlnet.ora文件导致出现的问题；

2、就是用户的口令了，刚开始是不知道sys用户的密码是多少，报错也就没放在心上，就直接修改了sys的密码为sys，尝试登陆，还是报错；

3、再接着就是给sys用户副权限，因为报错就是权限不足啊，赋了权限后还是没解决；

4、认证出现了问题？于是就查找口令问题（因为登陆方式中使用了口令认证方式登陆，而非OS认证），查询视图，没数据，而dbs目录下有口令文件，疑惑就产生了……

做了上述操作后，才发现，orapw命令的帮助信息并不是很详尽，给出的file命名规则，感觉可以随意命名的，结果却不是，规则在帮助命令中展示的并不详细。

ORA-01031这个权限错误问题，总的总结如下：遇见整问题，先别到处乱找东西，先查询视图，因为出现问题的最大可能性就是口令文件命名出错了，而且口令文件是区分大小写的。

来源: Transcendent
文章作者: Gavin Wang
文章链接: Oracle案例--错误码之ORA-01031 | Transcendent
本文章著作权归作者所有，任何形式的转载都请注明出处。