ORA-01031: insufficient privileges
I. description of the problem
Unable to log in to the database through sqlplus / as sysdba, indicating insufficient permissions.
Second, simulation test
1. Phenomenon
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
two。 Log in using the user password
$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. Log in successfully after starting monitoring
$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
$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. Continue to prompt for insufficient permissions to report an error.
$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. View user groups
$id oracle
Uid=500 (oracle) gid=500 (oinstall) groups=500 (oinstall)-missing dba group
$exit
Logout
6. Log in successfully after joining dba group
[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
$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.
III. Summary
Usually, people pay little attention to why Oracle users belong to group dba and pay little attention to details.
Often the problems we encounter are caused by the failure to create a database in accordance with the specifications, so building a database is actually a technical job.
The above problems may also be caused by other circumstances, I will not say much here.
