5 Replies Latest reply: Apr 10, 2012 11:19 AM by 659603 RSS

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

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