This discussion is archived
5 Replies Latest reply: Jan 24, 2013 4:02 AM by user8930540 RSS

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

user8930540 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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