This discussion is archived
12 Replies Latest reply: Feb 25, 2011 9:06 AM by redy007 RSS

ORA-01031:  when login as sysdba

626620 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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