ORA-01031: insufficient privileges – “SQLPLUS / as sysdba” working, but “SQLPLUS sys/sys@YOUTH as sysdba” is not working.

I have faced ORA-01031 oracle error after migrating oracle database from oracle 10g (10.2.0.4.0) to Oracle 11g (11.2.0.1.0)
Error log is as follows:

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

But, I can connect the database with SYS user as follow, but not able to connect SYS user with credentials cum connection string(as mentioned above).

[oracle@newyouth dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:29 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
  • Oracle error itself pretty self explanatory, according to error I have cross checked all the permissions but it doesn’t work for me.
  • Listener.ora and tnsnames.ora files also verified and found correct.
  • Oracle environment variables (like ORACLE_SID,ORACLE_HOME and PATH) also set appropriately with no mistake.
  • REMOTE_LOGIN_PASSWORDFILE parameter set to EXCLUSIVE in pfile/spfile.

Password file also created with following command.

orapwd FILE=orapwblade1 PASSWORD=sys entries=30

Finally, I got the clue from v$pwfile_users dictionary view, This view lists users who have been granted SYSDBA and SYSOPER privileges, So following query must fetch something as I have already created password file. But no luck. Oops.
SQL> select * from v$pwfile_users;
no rows selected

I have double check “$ORACLE_HOME/dbs” directory and came to know there was naming convention mistake with password file(Case sensitivity with ORACLE SID).

[oracle@newyouth dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Feb 10 04:00 hc_BLADE1.dat
-rw-r--r--. 1 oracle oinstall 1018 Feb 10 03:58 initBLADE1.ora
-rw-r-----. 1 oracle oinstall 24 Feb 8 02:24 lkBLADE1
-rw-r-----. 1 oracle oinstall 5120 Feb 10 04:16 orapwblade1
-rw-r-----. 1 oracle oinstall 3584 Feb 10 04:00 spfileBLADE1.ora

So as a part of the solution to my scenario, I have dropped an existing password file and created new one by following command.

orapwd FILE=orapwBLADE1 PASSWORD=sys entries=30

Finally, I got access and v$pwfile_user dictionary view fetched SYS user entry.

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:18:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

Cheers!!
Stay Tune. 🙂

Leave a Reply

%d bloggers like this: