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
Amit,
You are right on the mark. I could solve my problem because of your clear cut step by step instructions. Thanks a bunch — Logan
Glad to know that this helped you.
Cheers!!!!!!!!!
Saurabh Sood
For solaris 64 bit we can try the following thing also.
> rm config.o
> make -f ins_rdbms.mk config.o ioracle
we may get the following error
/usr/ccs/bin/as -P -K PIC -o config.o config.s
/usr/ccs/bin/as: “config.s”, line 12: error: cannot use v9 instructions in a non -v9 target binary
/usr/ccs/bin/as: “config.s”, line 14: error: cannot use v9 instructions in a non-v9 target binary
*** Error code 1
make: Fatal error: Command failed for target `config.o’
Entry in the “env_rdbms.mk” file is wrong.
Solution
Follow below steps:
1.Modify env_rdbms.mk file to reflect following:
From : KPIC_OPTION=-K PIC
To : KPIC_OPTION=-xarch=v9 -K PIC
2. rm config.o
3.make -f ins_rdbms.mk config.o ioracle
4. Now connect as
sqlplus “/ as sysdba”
Receiving Ora-1031 While Connecting As Sysdba After 10.2 Install
Doc ID: 353050.1
Hi Raju,
Thanks for adding your input here.
The case you are facing is specific to SPARC 64 bits platform where upgrading to 10.2
caused problems.
You would be interested in looking at Doc ID: 335684.1
Cheers!!!
Saurabh Sood
I have got these oracle error which are given below.please let me know the solutions.
Apr 14 13:42:34 pmsdbstdbydel SC[SUNWscor.oracle_server.monitor]:oracle-rg:ora-srv: [ID 564643 local7.error] Fault monitor detected
error DBMS_ERROR: 1031 DEFAULT Action=NONE : Insufficient privileges to perform DBMS operations – check Oracle user privileges
Apr 16 15:05:07 pmsdbstdbydel SC[SUNWscor.oracle_server.monitor]:oracle-rg:ora-srv: [ID 564643 local7.error] Fault monitor detected
error DBMS_ERROR: 1031 DEFAULT Action=NONE : Insufficient privileges to perform DBMS operations – check Oracle user privileges
Apr 16 15:18:02 pmsdbstdbydel SC[SUNWscor.oracle_server.monitor]:oracle-rg:ora-srv: [ID 564643 local7.error] Fault monitor detected
error DBMS_ERROR: 1031 DEFAULT Action=NONE : Insufficient privileges to perform DBMS operations – check Oracle user privileges
Hi,
It seems that you are not using sqlplus to connect to the database!!!
Please let me know how you are connecting to the database?
Regards,
Saurabh Sood
Sorry for my bad english(((
And 6 step is that you must run sqlpus from bin directory of your oracle server
Thanks mindinpanic. I have incorporated the comment in article. Appreciate your input.