This discussion is archived
5 Replies Latest reply: Apr 10, 2012 9:19 AM by 659603 RSS

ORA-01031: insufficient privileges when loggin in as sqlplus "/ as sysdba"

659603 Newbie
Currently Being Moderated
I have recently got a brand new Solaris Box. Have restored the ORacle Home from a live server.
then gone to restore a database from Tape.

All done fine.

However I cannot seem to login as

$>sqlplus "/ as sysdba"

I can only seem to login as
$>sqlplus /nolog

then
SQL>connect sys/password as sysdba

I have shut the Database down and recreated the password file
orapwd file=$ORACLE_HOME/dbs/orapwFMZ password=passowrd entries=50

Tried again but the same response... a spool of which is below.


$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Apr 5 09:49:23 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: ^C^X^Z



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



$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Apr 5 09:49:36 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/password as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 68386816 bytes
Fixed Size 729088 bytes
Variable Size 54525952 bytes
Database Buffers 12582912 bytes
Redo Buffers 548864 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.



I have inlcuded a list of parameters below FYI
SQL> show parameter remote

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string SHARED
remote_os_authent boolean TRUE
remote_os_roles boolean FALSE

Please can someone help.

Best Regards,

M

Edited by: user5856470 on Apr 5, 2012 2:11 AM
  • 1. Re: ORA-01031: insufficient privileges when loggin in as sqlplus "/ as sysdba"
    NirmalSArri Newbie
    Currently Being Moderated
    There is something not right at the OS level. Check if the user you are logged on to the machine (typically oracle) is part of the ORA_DBA group.


    Good Luck

    Nirmal
  • 2. Re: ORA-01031: insufficient privileges when loggin in as sqlplus "/ as sysdba"
    659603 Newbie
    Currently Being Moderated
    Thanks for pointer in 'right' direction...

    I've asked the unix chap and he says there is only oinstall group.

    Oracle was not in the /etc/group file....

    He has since added it.

    However I still cannot log in as sqlplus "/ as sysdba"

    After the change the present details are...

    $ who am i
    oracle pts/1 Apr 5 16:22 (liveux47d1.leeds.gov.uk)

    $ cat /etc/group
    root::0:
    other::1:root
    bin::2:root,daemon
    sys::3:root,bin,adm
    adm::4:root,daemon
    uucp::5:root
    mail::6:root
    tty::7:root,adm
    lp::8:root,adm
    nuucp::9:root
    staff::10:
    daemon::12:root
    sysadmin::14:
    smmsp::25:
    gdm::50:
    webservd::80:
    postgres::90:
    nobody::60001:
    noaccess::60002:
    nogroup::65534:
    nmc::2000:
    unix::1004:nmc
    oinstall::100:
    dba:101:oracle

    Is this the ORA_DBA group you mentioned? The live system does not have ora_dba but dba in the group file so we have added it in.


    Further help appreciated, as stll cannot connect.

    Thanks

    M

    Edited by: user5856470 on Apr 5, 2012 8:37 AM
  • 3. Re: ORA-01031: insufficient privileges when loggin in as sqlplus "/ as sysdba"
    912595 Expert
    Currently Being Moderated
    Set
    SQLNET.AUTHENTICATION_SERVICES = (ALL)
    in sqlnet.ora file in $ORACLE_HOME/network/admin/sqlnet.ora

    Also set
    SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

    Bouce the database and then try to connect through sqlplus / as sysdba

    Refer to following MOS:
    Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA [ID 730067.1]
  • 4. Re: ORA-01031: insufficient privileges when loggin in as sqlplus "/ as sysdba"
    659603 Newbie
    Currently Being Moderated
    I have tried what has been suggested but with no result



    SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

    SQL> show parameter remote_log

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    remote_login_passwordfile string EXCLUSIVE




    SQLNET.AUTHENTICATION_SERVICES = (ALL)
    NAMES.DIRECTORY_PATH= (TNSNAMES)


    Bounced the database but no result...


    $ sqlplus "/ as sysdba"

    SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 10 15:54:37 2012

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    ERROR:
    ORA-01031: insufficient privileges




    I have reset the parameters back to original. As this is the parameters in the source database system which is working with sqlplus "/ as sysdba"



    As you can see, I can log in as below but not with remote authentication....

    $ sqlplus

    SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 10 15:54:42 2012

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Enter user-name: sys/password as sysdba

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.8.0 - Production

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.


    FYI.


    I did delete the password file and recreated it as well. But cannot still connect as sqlplus "/ as sysdba"

    Any Ideas???
  • 5. Re: ORA-01031: insufficient privileges when loggin in as sqlplus "/ as sysdba"
    659603 Newbie
    Currently Being Moderated
    I'd recreated the password file but was still not working.
    What was missing was the ORA_DBA (or iin the case of unix, the dba) group did not include oracle.


    The first reply gave good pointer. ('ve mentioned you got the correct answer to this site for your ratings. Thanks for your help)


    The “oracle” user connecting as sqlplus “/ as sysdba” needed to be in dba group (on linux (or ORA_DBA) in windows.


    So asked the unix guys to add same as was in source system in the
    /etc/group file

    ie added below ...

    oinstall::100:                    
    dba::101:oracle               ie oracle is now in the dba group.


    Once oracle is added to the dba group for os authentication the below result will be same as source system where I was copying from, otherwise the groups will have a different no.

    $> id -a oracle
    uid=100(oracle) gid=100(oinstall) groups=101(dba)


    As this was now rectified I could logon perfectly.

    $> sqlplus "/ as sysdba"

    SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 10 16:52:36 2012

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to an idle instance.



    For further good explanation on OS authentication

    http://www.orafaq.com/wiki/Oracle_database_Security_FAQ#How_does_one_create.2C_manage_and_drop_database_users.3F

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points