Home > Oracle, RDBMS > Solving ORA-01031: insufficient privileges while connecting as sqlplus / as sysdba.

Solving ORA-01031: insufficient privileges while connecting as sqlplus / as sysdba.

Many times we can see an issue like this:
This is a very common and frequent error that can occur after the new oracle software install or due to some permissions changes at OS level. I will discuss the approach to solve ORA-1031 error on both Windows and Linux environment but first a little theory.
An administrative account is a user that is granted SYSOPER or SYSDBA privileges. Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups (dba on Unix/Linux and ORA_DBA on Windows). External password files are created with the orapwd utility.
If an administrative users belongs to the ‘dba’ group on Linux or the ‘ORA_DBA’ group on Windows, he/she can connect like this:

sqlplus / as sysdba

No password is required. This is equivalent to the unsupported “connect internal” method.
A password is required for “non-secure” administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:

sqlplus sys/password as sysdba

The Oracle password file ($ORACLE_HOME/dbs/orapw$ORACLE_SID or $ORACLE_HOME/database/orapw$ORACLE_SID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators will be allowed to connect.
Follow this procedure to create a new password file:
– Log in as the Oracle software owner
– Run command: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
– Shutdown the database (SHUTDOWN IMMEDIATE)
– Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set
– Startup the database (STARTUP)
Keep in mind that the orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table (at least as long as orapwd is running) and in the shell’s history file of many systems.
Last but not least, avoid at all cost the OPS$ accounts and setting the REMOTE_OS_AUTHENT initialization parameter (deprecated in Oracle 11g) which could be a security risk in a client/server environment.

Now, let’s go back to the ORA-01031 error – how to solve it in Windows environment?
First of all, be sure that your OS user belongs to the ORA_DBA group.
Then, set the SQLNET.AUTHENTICATION_SERVICES parameter to NTS in the sqlnet.ora file (located in the $ORACLE_HOME/network/admin):


which allows Windows users to be authenticated using Windows NT native security.
With your user account added to the ORA_DBA group and the SQLNET.AUTHENTICATION_SERVICES parameter being set to NTS, you should have sufficient privileges and be able to connect as SYSDBA without a password.

How to solve the ORA-01031 error in UNIX/Linux environment?
1. Check that $ORACLE_SID and $ORACLE_HOME are set correctly as:


Find the values returned by above commands and match these values under /etc/oratab file, these have to be listed there.
The values above are matching with /etc/oratab entries.
If the $ORACLE_SID and $ORACLE_HOME are not set properly then set it as:

export ORACLE_SID=orcl11gr2
export ORACLE_HOME=/u01/app/oracle/product/1120

and try to connect as “/ as sysdba” – it should work now.
If these are correct but still the error is coming then move to step 2.
2. Ensure you are invoking sqlplus from correct $ORACLE_HOME by checking the $PATH environment variable:

echo $PATH

If this is correct but still the error is coming then move to step 3.
3. Set the SQLNET.AUTHENTICATION_SERVICES parameter to ALL in the sqlnet.ora file (located in the $ORACLE_HOME/network/admin):


If this is correct but still the error is coming then move to step 4.
4. Ensure $TWO_TASK is not set.

echo $TWO_TASK

If it return any lines, then unset the environment variable as:

unset TWO_TASK

If this is correct but still the error is coming then move to step 5.
5. Check the permissions on the Oracle executable file.
It should show the permissions as on the above screen – if its not the same then issue the following command to set the correct permissions:

chmod 6751 oracle

If these are correct but still the error is coming then move to step 6.
6. Make sure that dba group at OS level only exists once in /etc/group file and that the users belonging to the dba group are properly comma separated. Check that the oracle user uid and gid are the same in /etc/group and /etc/passwd.
If these are correct but still the error is coming then move to step 7.
7. Check for the dba group at OS level. We need to make sure that OS user issuing “connect / as sysdba” belongs to dba group at OS level.
There is one file we need to check for this: $ORACLE_HOME/rdbms/lib/config.s or $ORACLE_HOME/rdbms/lib/config.c (file name vary depending on OS). The value for SS_DBA_GRP in these file is typically set to “dba” :
Let’s check it as oracle user:

[oracle@orclprod ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(orcldba)

Look for the groups value here – 54322(orcldba), the value is orcldba so we need to modify the config.c or config.s so that it should look like:

#define SS_DBA_GRP "orcldba"

After making changes to config file make sure that:
– no oracle processes are running
– login as oracle
– $LD_LIBRARY_PATH and $ORACLE_HOME are set properly
and relink oracle binaries with the following command:

$ORACLE_HOME/bin/relink all

If all previous settings are correct and still ORA-1031 is coming then the only option is to take strace output and check while opening which file the error is coming – for example:

strace -o /tmp/strace.txt sqlplus "/ as sysdba"
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: