Sunday, January 8, 2012

Connection To RMAN Auxiliary Fails With ORA-01031: INSUFFICIENT PRIVILEGES

Symptoms
Connecting to an auxiliary instance via RMAN fails with ORA-01031 errors. The same is seen when properly testing a connection via SQL*Plus to the auxiliary instance.

RMAN-04006: ERROR FROM AUXILIARY DATABASE: ORA-01031: INSUFFICIENT PRIVILEGES
Changes
You may have tried to add the connecting user to the password file but that has failed:
SQL> select * from v$pwfile_users;
No rows
SQL>  grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01109: database not open


Because the auxiliary is nomounted, the addition of a user to the password file for the auxiliary is not allowed, nor does it resolve this problem.

You have verified the same problem happens in SQL*Plus as does RMAN.  For example, if in RMAN you are issuing:
 
RMAN>  connect auxiliary <user/password>@<auxiliary_db>


then for testing SQL*Plus you'd have to issue this from the operating system prompt:
 
% sqlplus "<user/password>@<auxiliary_db> as sysdba"


also shows the ORA-1031 error.

(connecting in two steps to the auxiliary is not a valid test).
Cause
There may be a mismatch in the "case" of characters between these items on the host where the auxiliary database resides:

1)  The entry in the listener.ora file for service_name for the auxiliary instance.
2)  The entry for db_name in the init<sid>.ora file for the auxiliary instance.
3)  The naming of the password file.
4)  The $ORACLE_SID for the auxiliary instance.

If all four items have matching case characters, you may find the errors connecting remotely via the password file are resolved.
Solution
Set the reference to service_name in the listener.ora, db_name in the init<sid>.ora, the password file name itself, and the $ORACLE_SID to all uppercase.

Listener example:

   (DESCRIPTION=
   (ADDRESS=...)
   (ADDRESS=...)
   (CONNECT_DATA=
   (SERVICE_NAME=SALES.US.ACME.COM)))

The init<sid>.ora file:

  db_name=SALES

The password file:

  orapwSALES.ora  or orapwSALES

The $ORACLE_SID:

  $ ORACLE_SID=SALES; export ORACLE_SID

11 comments:

  1. PLEASE keep up the good work!!

    ReplyDelete
  2. Hi ,
    i have tried the step which you have mentioned.iam requesting you to guide me to resolve the same issue.

    [oracle@oracle10g admin]$ rman target / auxiliary sys@oursby

    Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 9 18:37:14 2005

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    connected to target database: OURDB (DBID=543793856)
    auxiliary database Password:
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00554: initialization of internal recovery manager package failed
    RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges
    [oracle@oracle10g admin]$


    ReplyDelete
  3. Please copy password file from Primary database

    ReplyDelete
    Replies
    1. Hi,
      thanks Srini, i tried by copying the file to the standby from primary.But still i am facing the same issue.

      TNS-01106: Listener using listener name LISTENER has already been started
      [oracle@oracle10g dbs]$ rman target / auxiliary system@oursby

      Recovery Manager: Release 10.2.0.1.0 - Production on Thu Nov 10 06:16:04 2005

      Copyright (c) 1982, 2005, Oracle. All rights reserved.

      connected to target database: OURDB (DBID=543793856)
      auxiliary database Password:
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-00554: initialization of internal recovery manager package failed
      RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

      Delete
  4. thanks! My problem was "just" different "CASE" - you helped me!

    ReplyDelete
  5. Excellent post! Thank you very much for the solution to my problem today.

    ReplyDelete
  6. I had named password file as orapwdDG1 which should be like orapwDG1.
    Got it correct. Thanks. Great work.....!

    ReplyDelete
  7. Thanks :) ........ listener.ora CASE problem here too. You saved me twice - I'd forgotten your solution on a second occasion - weeks after first finding your solution for first occasion I'd seen issue ....

    ReplyDelete