1 2 Previous Next 15 Replies Latest reply: Sep 10, 2012 8:56 AM by JackK RSS

    Insufficient privileges while connecting (and starting)

    JackK
      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
          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
            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.
              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
                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.
                  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
                    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
                      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
                        $ 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.
                          $ export ORACLE_SID=xe
                          Linux is not case insensitive....
                          • 10. Re: Insufficient privileges while connecting
                            JackK
                            Changing "xe" to "XE" didn't help.
                            • 11. Re: Insufficient privileges while connecting
                              JackK
                              Changing "xe" to "XE" didn't help.
                              • 12. Re: Insufficient privileges while connecting
                                orafad
                                >
                                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
                                  # 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.
                                    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