Solving ORA-1031 while connecting as “/ as sysdba”

Many times we see an issue like this:

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

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 dicuss the approach to solve ORA-1031 error on UNIX environment.

1. Check that oracle_sid and oracle_home are set correctly as:

$ echo $ORACLE_SID
$ echo $ORACLE_HOME

Find the values returned by above command and match these values under /etc/oratab file, these
have to be listed there.

EXAMPLE:
========

$ echo $ORACLE_SID
BSNL

$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_2

$ cat /etc/oratab

BSNL:/u01/app/oracle/product/10.2.0/db_2:N
VSNL:/u01/app/oracle/product/10.2.0/db_2:N

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=BSNL
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2

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 TWO_TASK is not set

$ echo $TWO_TASK

If it return any lines as:

TWO_TASK=
OR
TWO_TASK=

Then unset the environment variable as:

$ unset TWO_TASK

Now try to connect as “/ as sysdba”

If these are correct but still the error is coming then move to step 3.

3.Check the permissions on the oracle executable file:

$ cd $ORACLE_HOME/bin
$ ls -la oracle

It should show the following permissions:

-rwsr-s–x 1 oracle oinstall 96725724 Apr 2 13:43 oracle

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 4.

4. Check for the dba group at OS level. We need to make sure that Operating System users issuing / as sysdba belongs to dba group at OS level.

There is one file we need to check for this i.e $ORACLE_HOME/rdbms/lib/config.s OR $ORACLE_HOME/rdbms/lib/config.c (File name vary from OS to OS on some OS it is config.c and on some OS it is config.s). The value in these file is typically set to “dba” as:

.ascii “dba”

Login as oracle user:
# su – oracle
$ id
uid=111(oracle) gid=123(usdba)

Look for the gid value here.(usdba)
The gid value is usdba so we need to modify the config.c or config.s so that it should look like:
.ascii “usdba”

After making changes to config file relink oracle binaries as:

- Make sure that no oracle processes running
- Login as oracle
- Make sure LD_LIBRARY_PATH and ORACLE_HOME are set properly
$ORACLE_HOME/bin/relink all

If these are correct but still the error is coming then move to step 5.

5. 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.

Example:

usdba::123:oracle,oracle1

ii) Check that the oracle user uid and gid are same in /etc/group and /etc/passwd

If all these 5 settings are correct and still ora-1031 is coming then the only option is to take truss output and check while opening which file the error is coming.
e.g.

$ truss -aefo /tmp/truss.out sqlplus “/ as sysdba”

6. Ensure you are invoking sqlplus from correct ORACLE_HOME

This actually came as comment on this post and I would agree. Ensure that you are using sqlplus from correct ORACLE_HOME. To do this set

export PATH=$PATH:$ORACLE_HOME/bin

You can confirm the home using which sqlplus command