12 Replies Latest reply on Feb 25, 2011 5:06 PM by redy007

    ORA-01031:  when login as sysdba

    626620
      We have Oracle 11.2 on Redhat 5.6. on the server box, I can login as sysdba by doing
      sqlplus / as sysdba
      But failed when I do
       sqlplus sys/****@cchdev as sysdba
      ERROR:
      ORA-01031: insufficient privileges
      or In sqlplus
       conn sys/****@cchdev as sysdba
      ERROR:
      ORA-01031: insufficient privileges
      I suspect the password is wrong, then I changed password. But I got the same error when using new password.

      I could login all three way above last time. WHat is wrong?
        • 1. Re: ORA-01031:  when login as sysdba
          178210
          check all the entries in sqlnet.ora files for testing just rename the file and try
          • 2. Re: ORA-01031:  when login as sysdba
            sb92075
            I could login all three way above last time. WHat is wrong?
            What changed?

            post results from following command

            env | sort
            • 3. Re: ORA-01031:  when login as sysdba
              626620
              Here is the result od env|sort
              [oracle@cchORdev1 ~]$ env|sort
              BI_CONFIG_DIR=/u01/app/obiee/server/Config
              _=/bin/env
              BI_OC4J_DIR=/u01/app/obiee/oc4j_bi
              BI_RPD_DIR=/u01/app/obiee/server/Repository
              CLASSPATH=/u01/app/oracle/product/11.2.0/dbhome_1/JRE:/u01/app/oracle/product/11.2.0/dbhome_1/jlib:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/jlib
              CVS_RSH=ssh
              G_BROKEN_FILENAMES=1
              HISTSIZE=1000
              HOME=/home/oracle
              HOSTNAME=cchORdev1
              INPUTRC=/etc/inputrc
              KDEDIR=/usr
              KDE_IS_PRELINKED=1
              KDE_NO_IPV6=1
              LANG=en_US.UTF-8
              LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib
              LESSOPEN=|/usr/bin/lesspipe.sh %s
              LOGNAME=oracle
              LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
              MAIL=/var/spool/mail/oracle
              OBI_BASE=/u01/app/obiee/
              OBI_PRESENTATION_CONFIG=/u01/app/obiee//web/config
              ORA_ALERT_DIR=/var/oracle/diag/rdbms/cchdev/CCHDEV/trace
              ORA_BI_HOME=/u01/app/obiee
              ORACLE_BASE=/u01/app/oracle
              ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
              ORACLE_SID=cchdev
              ORACLE_TERM=xterm
              ORA_DUMP_DIR=/u05/oracle/dpdump
              PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/sbin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin
              PWD=/home/oracle
              SHELL=/bin/bash
              SHLVL=1
              SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
              SSH_CLIENT=10.2.0.54 6032 22
              SSH_CONNECTION=10.2.0.54 6032 10.2.0.200 22
              SSH_TTY=/dev/pts/1
              TERM=xterm
              TMPDIR=/tmp
              TMP=/tmp
              TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
              USER=oracle
              [oracle@cchORdev1 ~]$
              Edited by: user623617 on Apr 16, 2010 12:11 PM
              • 4. Re: ORA-01031:  when login as sysdba
                Srini Chavali-Oracle
                Pl see the troubleshooting steps in MOS Doc 69642.1 (UNIX: Checklist for Resolving Connect AS SYSDBA Issues)

                HTH
                Srini
                • 5. Re: ORA-01031:  when login as sysdba
                  626620
                  check all the entries in sqlnet.ora files for testing just rename the file and try
                  The sqlnet.ora file is very short
                  # sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
                  # Generated by Oracle configuration tools.
                  
                  NAMES.DIRECTORY_PATH= (TNSNAMES)
                  
                  ADR_BASE = /u01/app/oracle
                  Renamed sqlnet.ora to sqlnet.ora1. See the same result
                  • 6. Re: ORA-01031:  when login as sysdba
                    626620
                    Another observation: This message 'ORA-01031: insufficient privileges' appear when THE PASSWORD IS WRONG and when LOGIN AS SYSDBA. If I login the database with non-sysdba user and a wrong password, it will give me 'ORA-01017: invalid username/password; logon denied'. Same when login as sysdba with wrong pasword on other good databases
                    14:22:09 SQL> conn sys/aaak@cchdev as sysdba
                    ERROR:
                    ORA-01031: insufficient privileges
                    
                    14:25:49 SQL> conn bi/n@cchdev
                    ERROR:
                    ORA-01017: invalid username/password; logon denied
                    
                    14:21:36 SQL> conn sys/aaak@cchprd as sysdba
                    ERROR:
                    ORA-01017: invalid username/password; logon denied
                    To make sure I have the right password, I login as system and changed the password for sys user, and login as sysdba with the new password. Still get the same error ORA-01031
                    • 7. Re: ORA-01031:  when login as sysdba
                      Lubiez Jean-Valentin
                      Hello,


                      You may try to recreate the password file with the parameter nosysdba=n.

                      Please find a link about orapwd utility:

                      http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dba007.htm

                      If the password file was created with nosysdba=y it can prevent you to connect to SYS AS SYSDBA.


                      Hope this help.
                      Best regards,
                      Jean
                      • 8. Re: ORA-01031:  when login as sysdba
                        639320
                        Hi,

                        I got the same issue and manage to fix it:
                        Here is the context:
                        First install on OEL 5.5 (source server) of Oracle 11.2 and no connect issue.
                        After duplicating this oracle_home to other servers with the Grid Control, I got this ora-1031 consistently on the all target servers ...
                        I went to $ORACLE_HOME/rdbms/lib/config.c, put the right value for SS_DBA_GRP and SS_OPER_GRP, relink all.
                        -> no more ORA_01031 during connect / as sysdba !
                        Probably a bug of the OH cloning tool ?
                        • 9. Re: ORA-01031:  when login as sysdba
                          Mohamed ELAzab
                          Dear,
                          In most cases this error comes when you try to startup or stop the instance with none priviledged user.You can try to check this:
                          If someone receives this error while trying to startup the instance, the logged on user must belong todba group on Unix.
                          try to makde sure that the user belong to this group first using:
                          id -a oracle_user
                          Kind regards
                          Mohamed ELAzab
                          http://mohamedelazab.blogspot.com/
                          • 10. Re: ORA-01031:  when login as sysdba
                            redy007
                            user636317 has right, same procedure worked for me - my version of Oracle is 10.2.0.5.

                            Btw. you will need to kill pmon or smon to shutdown the database.
                            • 11. Re: ORA-01031:  when login as sysdba
                              sb92075
                              Btw. you will need to kill pmon or smon to shutdown the database.
                              OUCH!
                              it is like trying to kill a fly with a shotgun.

                              SHUTDOWN ABORT
                              is prefered method
                              • 12. Re: ORA-01031:  when login as sysdba
                                redy007
                                sb92075: I don't want to make any flame and please correct me if I am saying it wrong, but isn't shutdown abort same as kill -9 pmon? And second thing is if sys is only one who is able to shutdown database, how to deal with?

                                Two MOS which helped me: 69642.1 and 400459.1