1 2 3 Previous Next 36 Replies Latest reply: Aug 3, 2010 4:20 AM by 695836 RSS

    ORA-01031: insufficient privileges

    755443
      Hi all,

      I've a 10.2.0.4 instance. It was running for long time a go. Today, I want log in use sqlplus as sysdba user, and I get the error: ORA-01031: insufficient privileges

      On linux, I'm login as oracle user. I always did the same without problems but today is impossible.

      I've try to recreate the orapwd file but, I get the same error.

      Please, any way to log in as sysdba with sqlplus without need to restart the instance?

      Regards,
      dbajug
        • 1. Re: ORA-01031: insufficient privileges
          618702
          Dear dbajug,

          Have you tried to login as;
          sqlplus / as sysdba
          OR
          sqlplus sys/password@oracle_sid as sysdba
          What is your database state, is it open or idle? Can you please show the "lsnrctl status" output please?

          Ogan
          • 2. Re: ORA-01031: insufficient privileges
            755443
            Hi Ogan,

            thx for your support.

            If i try connect with all connection string I've no problem:

            [oracle@srvprodb01 ~]$ sqlplus sys/*******@BPELPRO as sysdba

            SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 30 12:13:55 2010

            Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


            Connected to:
            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options

            SQL> select status from v$instance;

            STATUS
            ------------
            OPEN


            but with short mode I can't:


            [oracle@srvprodb01 ~]$ sqlplus '/as sysdba'

            SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 30 12:11:13 2010

            Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

            ERROR:
            ORA-01031: insufficient privileges


            The listener is ok and running:
            [oracle@srvprodb01 ~]$ lsnrctl status LISTENER_BPELPRO

            LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 30-JUL-2010 12:10:05

            Copyright (c) 1991, 2007, Oracle. All rights reserved.

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vip-lhorabpel)(PORT=1525)))
            STATUS of the LISTENER
            ------------------------
            Alias LISTENER_BPELPRO
            Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
            Start Date 22-JUN-2010 14:10:13
            Uptime 37 days 21 hr. 59 min. 52 sec
            Trace Level off
            Security ON: Local OS Authentication
            SNMP OFF
            Listener Parameter File /oracle/10.2.0/db_1/network/admin/listener.ora
            Listener Log File /oracle/10.2.0/db_1/network/log/listener_bpelpro.log
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.115)(PORT=1525)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.121.11.153)(PORT=1523)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
            Services Summary...
            Service "BPELPRO" has 1 instance(s).
            Instance "BPELPRO", status READY, has 1 handler(s) for this service...
            Service "PLSExtProc" has 1 instance(s).
            Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
            Service "bpelproXDB" has 1 instance(s).
            Instance "BPELPRO", status READY, has 1 handler(s) for this service...
            Service "bpelpro_XPT" has 1 instance(s).
            Instance "BPELPRO", status READY, has 1 handler(s) for this service...
            The command completed successfully


            Regards,
            dbajug
            • 3. Re: ORA-01031: insufficient privileges
              618702
              Dear dbajug,

              Here is an illustration for you;
              $ rm orapwopttest
              $ sqlplus / as sysdba
              
              SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 30 13:28:16 2010
              
              Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
              
              
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> exit
              Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              $ sqlplus sys/password@opttest as sysdba
              
              SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 30 13:28:24 2010
              
              Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
              
              ERROR:
              ORA-01031: insufficient privileges
              
              
              Enter user-name: ^C
              
              $
              $ orapwd file=orapwopttest password=password;
              $ sqlplus sys/password@opttest as sysdba
              
              SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 30 13:29:05 2010
              
              Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
              
              
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL>
              Your problem is nothing to do with the password file than. It could be because of your environmental variables maybe. Can you please show the "env" output and your database name?
              $ env
              ------------
              $ sqlplus sys/password@oracle_sid as sysdba
              
              SQL> select name from v$database;
              By the way do you have the same databases on 10.121.11.153 and 192.168.31.115 ip address?

              Also can you please try to connect to the database like;
              $ set ORACLE_SID=oracle_sid
              $ sqlplus / as sysdba
              Hope That Helps.

              Ogan
              • 4. Re: ORA-01031: insufficient privileges
                Chinar
                +[oracle@srvprodb01 ~]$ sqlplus '/as sysdba'+

                SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 30 12:11:13 2010

                Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

                ERROR:
                ORA-01031: insufficient privileges

                Do not execute sqlplus '/as sysdba' ,you need execute sqlplus "/as sysdba",in additionally to connecting sqlplus "/as sysdba" your currently logged user(OS user) must member of dba group(OS group).Check this.
                • 5. Re: ORA-01031: insufficient privileges
                  755443
                  Hi Ogan,

                  Here you have the results you demand:

                  [oracle@srvprodb01 ~]$ env
                  HOSTNAME=srvprodb01
                  SHELL=/bin/bash
                  TERM=vt100
                  HISTSIZE=1000
                  NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                  OLDPWD=/oracle/10.2.0/db_1/dbs
                  USER=oracle
                  LD_LIBRARY_PATH=/oracle/10.2.0/db_1/lib
                  LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:
                  ORACLE_SID=BPELPRO
                  ORACLE_HOSTNAME=vip-lhorabpel
                  ORACLE_BASE=/oracle
                  MAIL=/var/spool/mail/oracle
                  PATH=/oracle/10.2.0/db_1/bin:/oracle/10.2.0/db_1/opmn/bin:/oracle/10.2.0/db_1/dcm/bin:/oracle/10.2.0/db_1/bin:/oracle/10.2.0/db_1/opmn/bin:/oracle/10.2.0/db_1/dcm/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/oracle/bin
                  INPUTRC=/etc/inputrc
                  PWD=/oracle
                  LANG=en_US.UTF-8
                  ORACLE_TERM=xterm
                  SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
                  SHLVL=1
                  HOME=/oracle
                  LOGNAME=oracle
                  CVS_RSH=ssh
                  LESSOPEN=|/usr/bin/lesspipe.sh %s
                  ORACLE_HOME=/oracle/10.2.0/db_1
                  G_BROKEN_FILENAMES=1
                  _=/bin/env

                  SQL> select name from v$database;

                  NAME
                  ---------
                  BPELPRO


                  Before use sqlplus, I always charge this enviroment file:
                  [oracle@srvprodb01 ~]$ cat ora10_BPELPRO.env
                  ORACLE_BASE=/oracle
                  ORACLE_HOME=/oracle/10.2.0/db_1
                  ORACLE_SID=BPELPRO
                  ORACLE_TERM=xterm
                  NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                  PATH=$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$ORACLE_HOME/dcm/bin:$PATH
                  LD_LIBRARY_PATH=$ORACLE_HOME/lib
                  ORACLE_HOSTNAME=vip-lhorabpel
                  export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM NLS_LANG PATH LD_LIBRARY_PATH ORACLE_HOSTNAME


                  I'm searching information using google and I find some forums where people propose modify the sqlnet.ora file. Whats your opinion?

                  regards,
                  dbajug
                  • 6. Re: ORA-01031: insufficient privileges
                    Chinar
                    Post there result of following commands which entered from terminal:
                    who
                    id <user_name>
                    /*There user_name is result of first command*/
                    • 7. Re: ORA-01031: insufficient privileges
                      755443
                      Hi Chinar,

                      I can' t see the reasor to do this. Excuse me but, how can it be related with?

                      I'm logged as oracle user which is member of dba group.

                      Regards,
                      dbajug
                      • 8. Re: ORA-01031: insufficient privileges
                        Chinar
                        dbajug wrote:
                        Hi Chinar,

                        I can' t see the reasor to do this. Excuse me but, how can it be related with?

                        I'm logged as oracle user which is member of dba group.

                        Regards,
                        dbajug
                        But i see.Please post there
                        id oracle
                        and $ORACLE_HOME/network/admin/sqlnet.ora file there
                        • 9. Re: ORA-01031: insufficient privileges
                          755443
                          Hi Chinar,

                          The results you demand:

                          [oracle@srvprodb01 ~]$ id oracle
                          uid=10000(oracle) gid=10001(oinstall) groups=10001(oinstall)

                          I don't have sqlnet file as well.

                          Regards,
                          dbajug
                          • 10. Re: ORA-01031: insufficient privileges
                            Chinar
                            dbajug wrote:
                            Hi Chinar,

                            The results you demand:

                            [oracle@srvprodb01 ~]$ id oracle
                            uid=10000(oracle) gid=10001(oinstall) groups=10001(oinstall)

                            I don't have sqlnet file as well.

                            Regards,
                            dbajug
                            Well,your oracle user actually is not member of dba group,which this you can see from above.Your oracle user is only member of oinstall. Provide this.AS
                            su
                            usermod -g dba oracle
                            Edited by: Chinar on Jul 30, 2010 4:22 AM
                            • 11. Re: ORA-01031: insufficient privileges
                              rajeysh
                              create a password file for your database,

                              set command shows password file is in /oracle folder create a new password file in ORACLE_HOME/dbs folder
                              PWD=/oracle

                              you created a password file in $ORACLE_HOME check it and correct it.
                              • 12. Re: ORA-01031: insufficient privileges
                                Chinar
                                rajeysh wrote:
                                create a password file for your database,

                                set command shows password file is in /oracle folder create a new password file in ORACLE_HOME/dbs folder
                                PWD=/oracle

                                you created a password file in $ORACLE_HOME check it and correct it.
                                Rajeysh.
                                Do not need create password file.Only need change Oracle Software owner OS group to dba.
                                • 13. Re: ORA-01031: insufficient privileges
                                  618702
                                  He does not need to create the password file again. He can connect to the database by giving the SID.

                                  Ogan
                                  • 14. Re: ORA-01031: insufficient privileges
                                    Chinar
                                    Right.I said same
                                    1 2 3 Previous Next