5 Replies Latest reply: Jan 24, 2013 6:02 AM by user8930540 RSS

    ORA-01031 on a RAC database - issue with 2nd node

    user8930540
      Dear All,

      We have a 2 node RAC cluster, which is hosting 6 databases.Of which one of the database backup has started failing consistently due to ORA-01031 error,when trying to connect to the target database.We later explored this unique scenario.

      (i) Connecting to the database using 'sys/xxxx@db1 as sysdba' or 'sys/xxxx@db2 as sysdba' works just fine
      (ii) Connecting to the database using 'sys/xxxx@db as sysdba' fails with ORA-01031 insufficient privilege
      (iii) This issue impacts only one database (specifically the second instance - when the connection is redirected towards 2nd node when we use DB_NAME to connect, ORA-01031 errror occurs) ,rest are all (instances/db) fine.
      (iv) REMOTE_LOGIN_PASSWORD parameter was never changed/updated - it was staying as exclusive from the begining the databases were build by me.
      (v) The connection was working fine,not sure what broke in the course of its lifecycle.
      (vi) All the databases carry the same password for sys.
      (vii) v$pwfile_users is reporting as expected

      select * from v$pwfile_users;

      USERNAME SYSDB SYSOP SYSAS
      ------------------------------ ----- ----- -----
      SYS TRUE TRUE FALSE

      (viii) To depict how the issue looks like;

      SQL> conn sys/xxxxxxx@<DB_NAME> as sysdba
      Connected.

      SQL> select instance_name from v$instance;

      INSTANCE_NAME
      ----------------
      DB_NAME1

      SQL> conn sys/xxxxxxx@<DB_NAME> as sysdba
      Connected.
      SQL> conn sys/xxxxxxx@<DB_NAME> as sysdba
      ERROR:
      ORA-01031: insufficient privileges


      Warning: You are no longer connected to ORACLE.
      SQL> conn sys/xxxxxxx@<DB_NAME> as sysdba
      Connected.
      SQL> conn sys/xxxxxxx@<DB_NAME> as sysdba
      Connected.
      SQL> conn sys/xxxxxxx@<DB_NAME> as sysdba
      ERROR:
      ORA-01031: insufficient privileges


      Warning: You are no longer connected to ORACLE.

      My failure attempts:
      (a) Backed up the password file & then tried recreating password file, but this requires a DB bounce (being this is prod, I dont have the leverage to do that) - since the remote_login_password is set to exclusive
      (b) Backed up the password file and tried copying node 1 password file to node 2(keeping the naming convention intact)
      (c) Backed up the password file and tried copying the password file from other instance to this instance with naming convetion staying intact.
      (d) Tried creating one other dba user, to check if the new user is getting recognized, still same error.
      (e) The rwx on the orapw**** file is same across all the instances.

      I made a work around to let my DB db backup running using the 2nd node alone.But I am wondering what is the cause and fix.....

      Please help ASAP!

      Thanks & Regards
      Raja
        • 1. Re: ORA-01031 on a RAC database - issue with 2nd node
          user8930540
          Dear All,

          MY DB/GRID version is : 11.2.0.3.2
          OS: RHEL 5.

          Thanks & Regards
          Raja
          • 2. Re: ORA-01031 on a RAC database - issue with 2nd node
            P.Forstmann
            I am not sure of what is the root case of your issue but you should be able to change password file for a running database running 11.2 and RAC (if ORACLE_HOME is not shared you should copy password file from the node where you have changed it to the other nodes). I have just tested it with 11.2.0.1.
            [oracle@rac1 dbs]$ pwd
            /u01/app/oracle/product/11.2.0/db_1/dbs
            [oracle@rac1 dbs]$ rm orapwRAC1
            [oracle@rac1 dbs]$ orapwd file=orapwRAC1 password=change
            [oracle@rac1 dbs]$ sqlplus sys/change@rac as sysdba
            
            SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 23 20:07:33 2013
            
            Copyright (c) 1982, 2009, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
            Data Mining and Real Application Testing options
            
            SQL> show parameter cluster
            
            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- ------------------------------
            cluster_database                     boolean     TRUE
            cluster_database_instances           integer     2
            cluster_interconnects                string
            SQL> select * from v$pwfile_users;
            
            USERNAME                       SYSDB SYSOP SYSAS
            ------------------------------ ----- ----- -----
            SYS                            TRUE  TRUE  FALSE
            
            SQL> exit
            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
            Data Mining and Real Application Testing options
            • 3. Re: ORA-01031 on a RAC database - issue with 2nd node
              Osama_Mustafa
              Did you check
              srvctl starting / stopping RAC Database fails with ORA-01031: insufficient privileges and ORA-01005: null password given [ID 1450253.1]
              CONNECT AS SYSDBA Intermittently Fails with Error ORA-01017/ORA-01031 on RAC with Load Balancing [ID 790214.1]
              • 4. Re: ORA-01031 on a RAC database - issue with 2nd node
                user8930540
                Hi Osama,

                I reviewed those notes, my comments are as follows.
                We are able to connect to node 2 fine, if we address our connection to node2.But an attempt to connect to db using the db/service name(having 2 instances) fails.

                sys@node2 as sysdba ->fine
                sys@db as sysdba -> node 2 fails with ORA-01031.

                I took a strace for listener, I am currently checking it.I will let you know on what I find more.

                Thanks
                • 5. Re: ORA-01031 on a RAC database - issue with 2nd node
                  user8930540
                  Hi Forstmann,

                  I did try the password file copy from node 1, like I highlighted in my problem description. Someone modified the password file on Jan 1 2013, I can see that.I am trying to restore it from the filesystem backup.

                  I will keep everyone posted.

                  This is still a weird issue...I am hoping to find the cause.

                  Thanks & Regards
                  Raja