一、问题描述
通过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,很少关注细节。
往往我们遇到的问题，都是没有按照规范去创建数据库导致的，所以说建库其实也是一个技术活。
以上问题还有可能是其他情况引起，这里不多说。
