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,很少關注細節。

往往我們遇到的問題，都是沒有按照規范去創建數據庫導致的，所以說建庫其實也是一個技術活。

以上問題還有可能是其他情況引起，這里不多說。