Oracle RMAN backup fails with ORA-01031 insufficient privileges

RMAN automatically requests a connection to the target database as SYSDBA

In order to connect to the target using RMAN as SYSDBA, user must do one of the following:

• The user account should be part of the operating system DBA group on the target database server. It gives the ability to connect with SYSDBA privilege without password.
• Create a password file using orapwd command and enable initialization parameter REMOTE_LOGIN_PASSWORDFILE.
• Make sure you are passing correct username and password.

If the target database does not have a password file, then the user you are logged in as must be validated with operating system authentication.

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 18 16:37:36 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN>
RMAN> connect target *
RMAN-00571: ============================================
RMAN-00569: ======== ERROR MESSAGE STACK FOLLOWS ============
RMAN-00571: ============================================
ORA-01031: insufficient privileges

RMAN> **end-of-file**
RMAN>
host command complete

RMAN> 2> 3> 4> 5> 6> 7>
RMAN-00571: ============================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: ============================================
RMAN-03002: failure of allocate command at 04/18/2013 16:37:37
RMAN-06171: not connected to target database

RMAN>
RMAN-00571: =============================================
RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571: =============================================
RMAN-03002: failure of list command at 04/18/2013 16:37:37
RMAN-06171: not connected to target database

Cause:  In above scenario, RMAN denies logon to target database

Solution: - create a password file for the target database or add yourself to the administrator list in the operating system.

1. If you are using Password file then make sure that it should be in the format of orapw<SID> under $ORACLE_HOME/dbs directory and also make sure you are using REMOTE_LOGIN_PASSWORDFILE parameter in init.ora or spfile .

$ orapwd file=$ORACLE_HOME/dbs/orapwTESTDB password=xxxxx

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE scope=spfile;
If using pfile then add or edit the parameter.

Query v$pwfile_users to see the list of users who have been granted SYSDBA and SYSOPER privilege as derived from password file.

SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
----------------------------- ----- ----- -----
SYS TRUE TRUE FALSE

2. Operating system authentication, Check the sqlnet.ora in your$ ORACLE_HOME\network\admin directory and add to it the following line, if not exists.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

If above entry exists and commented then uncomment the line. In case if sqlnet.ora file does not exist then create the file.

Below Authentication Methods Available with Oracle Net Services

NONE means not authentication mode. The user must use valid username and password can be used to access database
ALL means for all authentication modes
NTS means for Windows NT native authentication.

Find out that weather user is part of operating system DBA group, if not add the user to the group.

In unix, you can find the group using below command.
$ cat /etc/group | grep dba

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

ORA-01111: name for data file is unknown in Standby

Many users encountered the issue UNNAMEDnnn file in Standby Database

MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 10:'/home/oracle/11.2.0/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

Uses will encounter this error when standby_file_management is set to auto and file path which is added to the primary database does not exist on standby site. In this situation standby database creates a dummy data file such as UNNAMEDnnnn

If you encounter this error follow the steps
Find the files which are needs to be recovered

SQL> select * from v$recover_file where error=' FILE MISSING';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ------------------
10 ONLINE ONLINE FILE MISSING 0

Run below command to Identify the file name on Primary as well as on Standby
SQL>select file#,name from v$datafile where file#=10;

Set the standby_file_management to Manual
SQL> ALTER SYSTEM set standby_file_management=MANUAL;

Create the actual data file from dummy filename
SQL> ALTER DATABASE CREATE DATAFILE 'datafile_path/UNNAMEDnnnn' as '{standby_file_path}/datafile_name.dbf';

Users will also encounter the same error when they add a data file on Primary database and STANDBY_FILE_MANAGEMENT on standby set to MANUAL. When MRP (Recovery process) trying to apply the archive logs, it will create an UNNAMEDnnnn file under $ORACLE_HOME/dbs

Run below command to create the data file manually in Standby

SQL> ALTER DATABASE CREATE DATAFILE ‘/home/oracle/11.2.0/dbs/UNNAMED00010'as ‘/dbs01/ORAC/oradata/prd_data10.dbf’;

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com