ORA-01031: insufficient privileges

一、问题描述

通过sqlplus / as sysdba 无法登陆数据库，提示权限不足。

二、模拟测试

1.现象

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 11:09:10 2017

Copyright (c) 1982, 2013, 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

2.使用用户密码登陆

<orcldg:orcl:/home/oracle>$sqlplus "sys/oracle@orcl as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 11:09:34 2017

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

ERROR:

ORA-12541: TNS:no listener

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

3.启动监听后登陆成功

<orcldg:orcl:/home/oracle>$lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-NOV-2017 11:09:45

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/orcldg/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcldg)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcldg)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                14-NOV-2017 11:09:45

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/orcldg/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcldg)(PORT=1521)))

Services Summary...

Service "orcl" has 1 instance(s).

  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

<orcldg:orcl:/home/oracle>$sqlplus "sys/oracle@orcl as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 11:09:52 2017

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

Connected to an idle instance.

SQL> exit

Disconnected

4.继续提示权限不足报错

<orcldg:orcl:/home/oracle>$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 11:09:58 2017

Copyright (c) 1982, 2013, 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

5.查看用户组

<orcldg:orcl:/home/oracle>$id oracle

uid=500(oracle) gid=500(oinstall) groups=500(oinstall)   --缺少dba组

<orcldg:orcl:/home/oracle>$exit

logout

6.加入dba组后登陆成功

[root@orcldg ~]# usermod -a -G oinstall,dba oracle

[root@orcldg ~]# id oracle

uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

[root@orcldg ~]# su - oracle

<orcldg:orcl:/home/oracle>$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 11:11:07 2017

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

Connected to an idle instance.

三、小结

通常大家建库很少关注为什么Oracle用户要属于组dba,很少关注细节。

往往我们遇到的问题，都是没有按照规范去创建数据库导致的，所以说建库其实也是一个技术活。

以上问题还有可能是其他情况引起，这里不多说。