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=<some_db_name>
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
Recent Comments