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