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=<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

Tags: , ,

18 thoughts on “Solving ORA-1031 while connecting as “/ as sysdba” :”

  • 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

  • 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

  • oracle:x:500:500:RHEL64Bit:/home/oracle:/bin/bash
    oracleuser:x:501:501:Oracle Software Owner:/home/oracle:/bin/bash

    uid=500(oracle) gid=500(oracle) groups=500(oracle),502(dba),503(oper) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

  • hi
    i upgrade db 10204 to 11203,
    1 is normal db and 1 is asm db,
    my normal db workfine,
    but my asm db not working
    i connect to db using sqlplus as /sysdba
    but cant startup db,get error insufficient privilege

  • Add this step as well in the list of steps mentioned above.

    Check dba privileges for os user by running command ‘id ‘. If the output of the command doesn’t show 1001(dba), then add the same by running command ‘sudo usermod -aG dba ‘.

    Now try to run sqlplus / as sysdba.

  • thanks, we found the Unix team had left two dba groups in /etc/group which was causing the ORA-1031.

    # grep “^dba:” /etc/group
    dba::101:
    dba::30040:grid,oracle

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.