This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Sep 10, 2012 6:56 AM by JackK RSS

Insufficient privileges while connecting (and starting)

JackK Explorer
Currently Being Moderated
Hi.
I have Oracle11gXE. Few minutes ago I was able to connect to DB with:
SQL> conn sys/ora@xe as sysdba
Then I'd like to swich the database to ARCHIVELOG mode:
SQL> shutdown immediate;
After that I disconnect and tried to connect again:
$ sqlplus sys/ora@xe as sysdba

SQL*Plus: Release 11.2.0 ...

Copyright ...

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 
What is wrong?
  • 1. Re: Insufficient privileges while connecting
    orafad Oracle ACE
    Currently Being Moderated
    Does XE live on the local or on a remote machine?

    If local, try instead
    sqlplus / as sysdba
    or
    sql> conn / as sysdba
  • 2. Re: Insufficient privileges while connecting
    JackK Explorer
    Currently Being Moderated
    DB is on the local machine (it's on my VPS but I am trying to connect from that VPS). conn / as sysdba ended with error: ORA-01031: insufficient privileges
    My "environment" is:
    # id -a root
    uid=0(root) gid=0(root) groups=0(root),1001(dba)
    
    # env
    TERM=xterm
    SHELL=/bin/bash
    XDG_SESSION_COOKIE=516158e06509694a140dbf5c007d6efe-1345097403.244086-1706168186
    SSH_CLIENT=193.93.68.35 2576 22
    SSH_TTY=/dev/pts/0
    USER=root
    TWO_TASK=xe
    MAIL=/var/mail/root
    PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/u01/app/oracle/product/11.2.0/xe/bin
    PWD=/root
    LANG=pl_PL.UTF-8
    SHLVL=1
    HOME=/root
    LOGNAME=root
    SSH_CONNECTION=193.93.68.35 2576 37.28.152.94 22
    ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
    _=/usr/bin/env
    -----------------
    Edited by: JackK on Aug 16, 2012 8:19 AM

    I was installing Oracle11gXE as a root. It's because the installation was from a DEB package.
    There is no oinstall group on my system.

    --------------
    Edited by: JackK on Aug 16, 2012 8:42 AM
    # tnsnames.ora Network Configuration File:
    
    XE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = jackk)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SID = XE)
        )
      )
    
    EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
        )
        (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
        )
      )
    # listener.ora Network Configuration File:
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = PLSExtProc)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
          (PROGRAM = extproc)
        )
        (SID_DESC =
          (SID_NAME = xe)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
        )
      )
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
          (ADDRESS = (PROTOCOL = TCP)(HOST = jackk)(PORT = 1521))
        )
      )
    
    DEFAULT_SERVICE_LISTENER = (XE)
  • 3. Re: Insufficient privileges while connecting
    Paul M. Oracle ACE
    Currently Being Moderated
    DB is on the local machine

    conn / as sysdba ended with error: ORA-01031: insufficient privileges

    TWO_TASK=xe
    If you're working locally you shouldn't set TWO_TASK environment variable, which identifies a connection string (it's the same as working over the network).
    $ unset TWO_TASK
    Also, you're using root user : did you try another user (e.g. oracle) ? From your previous post looks like it worked before : how did you connect at that time ?
  • 4. Re: Insufficient privileges while connecting
    JackK Explorer
    Currently Being Moderated
    I unset TWO_TASK but it doesn't help. DB is started automatically on system startup. I can start/stop the instance using +/etc/init.d/oracle_xe+ script only.
    I tried with oracle user with the same effect.
  • 5. Re: Insufficient privileges while connecting
    Paul M. Oracle ACE
    Currently Being Moderated
    I tried with oracle user with the same effect.
    How exactly did you try ? Please post all details.
  • 6. Re: Insufficient privileges while connecting
    JackK Explorer
    Currently Being Moderated
    Now I can connect to DB but cannot start it up again
    $ whoami
    oracle
    $ sqlplus sys@xe as sysdba
    Enter password:
    
    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
    
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup nomount;
    ORA-01031: insufficient privileges
    SQL>
    Help me, please... I need to switch my database to ARCHIVELOG MODE but I'm not able to, because of this issue. (The same as a root user.)
  • 7. Re: Insufficient privileges while connecting
    orafad Oracle ACE
    Currently Being Moderated
    As the oracle user:

    (assuming env properly setup, and local instance)

    $ sqlplus / as sysdba


    If this does not work, verify group memberships (dba group).

    $ id
  • 8. Re: Insufficient privileges while connecting
    JackK Explorer
    Currently Being Moderated
    $ whoami
    oracle
    $ sqlplus / as sysdba
    
    SQL*Plus: Release ...
    
    Copyright (c) ...
    
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name:
    $ export ORACLE_SID=xe
    $ sqlplus / as sysdba
    
    ...
    
    Connected to an idle instance.
    
    SQL>
    But DB is running! (I see my www page on the Internet without any errors.)
    $ id
    uid=1001(oracle) gid=1001(dba) groups=1001(dba)
  • 9. Re: Insufficient privileges while connecting
    Paul M. Oracle ACE
    Currently Being Moderated
    $ export ORACLE_SID=xe
    Linux is not case insensitive....
  • 10. Re: Insufficient privileges while connecting
    JackK Explorer
    Currently Being Moderated
    Changing "xe" to "XE" didn't help.
  • 11. Re: Insufficient privileges while connecting
    JackK Explorer
    Currently Being Moderated
    Changing "xe" to "XE" didn't help.
  • 12. Re: Insufficient privileges while connecting
    orafad Oracle ACE
    Currently Being Moderated
    >
    But DB is running!
    What db is running?


    Please post output from:

    $ ps -ef | grep [p]mon

    $ env | sort
  • 13. Re: Insufficient privileges while connecting
    JackK Explorer
    Currently Being Moderated
    # ps -ef | grep [p]mon
    oracle        1932     1  0  Sep09 ?     00:00:01 xe_pmon_XE
    
    # env | sort
    HISTCONTROL=ignorespace
    HOME=/root
    LANG=pl_PL.UTF-8
    LOGNAME=root
    MAIL=/var/mail/root
    MC_SID=7571
    MC_TMPDIR=/tmp/mc-root
    NLS_LANG=POLISH_POLAND.AL32UTF8
    OLDPWD=/etc/init.d
    ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
    ORACLE_SID=xe
    PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/u01/app/oracle/product/11.2.0/xe/bin:/u01/app/oracle/product/11.2.0/xe/bin
    PWD=/root
    SHELL=/bin/bash
    SHLVL=2
    SSH_CLIENT=193.93.68.35 3074 22
    SSH_CONNECTION=193.93.68.35 3074 XX.XX.XX.XX 22
    SSH_TTY=/dev/pts/0
    TERM=xterm
    USER=root
    _=/usr/bin/env
    XDG_SESSION_COOKIE=516158e06509694a140dbf5c007d6efe-1347252988.315268-523640876
  • 14. Re: Insufficient privileges while connecting
    Paul M. Oracle ACE
    Currently Being Moderated
    Changing "xe" to "XE" didn't help.
    That is, you got exactly the same result, or what else ?

    Also, it seems that you're logged as root, login as oracle instead, or "su - oracle" from root, and try
    $ . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
    $ sqlplus / as sysdba
    Result ?
1 2 Previous Next

Legend

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