Monday, April 22, 2013

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

2 comments: