How to take RMAN Full DB backup using SYSBACKUP (Doc ID 2140670.1)

In this Document

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Goal

This Article offers step by step procedure for RMAN backup and Recovery using SYSBACKUP privilege

Solution

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

bkp_user --> User with SYSBACKUP privilege

dbname : ORCL

scott.test1 and scott.emp --> Example used for table recovery

STEPS:

1. Creating password file for sysbackup
2. Create user with sysbackup privilege
3. Connect to RMAN with sysbackup privilege
4. Backup datafile example
5. Table recovery using sysbackup privilege
6. Additional Information

 

1. Creating password file for SYSBACKUP

Creating password file will overwrite existing passwordfile

Before creating password file, Check if passwordfile already exist with SYSBACKUP option

SQL > SELECT * FROM V$pwfile_users where USERNAME='SYSBACKUP';

NOTE: Take backup of OLD passwordfile(optional) because creating password files with force=y will overwrite the existing.

$ orapwd file=orapworcl sysbackup=y force=y

Enter password for SYS: <<<< Enter OLD / NEW SYS password >>>>

Enter password for SYSBACKUP: <<<< Enter SYSBACKUP password >>>>

Validate using,

SQL > SELECT * FROM V$pwfile_users where USERNAME='SYSBACKUP';

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 0

In Data Guard environment take following action.

a) After passwordfile recreation copy the passwordfile to standby and rename as per standby SID.
b) If it is RAC setup make sure to copy the passwrodfile to all primary and standby nodes and rename as per respective SID.

2. Create user with SYSBACKUP privilege

Do not Unlock SYSBACKUP user

    https://docs.oracle.com/database/121/ADMIN/dba.htm#ADMIN11040

create new user and provide SYSBACKUP privilege to newly created user

SQL> create user bkp_user identified by <password>;

User created.

SQL> grant sysbackup to bkp_user;

Grant succeeded.

3. Connect to RMAN with sysbackup privilege

NOTE: "as sysbackup" clause is mandatory

$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Sat May 21 11:16:24 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target 'bkp_user/<password>@<connect string> as sysbackup'     <<<<------------    Mandatory

connected to target database: ORCL (DBID=1895852460)

RMAN>

Optionally verify whether connection made by SYSBACKUP privileged user:

 RMAN> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

using target database control file instead of recovery catalog

CURRENT_SCHEMA SESSION_USER
------------------ ------------------

SYS SYSBACKUP

 Post connecting in RMAN using SYSBACKUP privileged user, Backup, restore and recovery operations can be performed.

4. Example : Backup datafile,

$ rman
RMAN> connect target 'bkp_user/<password>@<Connect string> as sysbackup'
RMAN> run {
2> allocate channel c1 device type disk;
3> backup datafile 2;
4> }

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: c1
channel c1: SID=59 device type=DISK

Starting backup at 21-MAY-2016 11:36:00
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/<path>/user.dbf
channel c1: starting piece 1 at 21-MAY-2016 11:36:01
channel c1: finished piece 1 at 21-MAY-2016 11:36:02
piece handle=/<path>/fast_recovery_area/ORCL/backupset/2019_05_21/o1_mf_nnndf_TAG20160521T113601_cmzyp974_.bkp tag=TAG20160521T113601 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAY-2016 11:36:02
released channel: c1

RMAN>

5. Table recovery using SYSBACKUP privilege : Example

$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Sat May 21 12:07:34 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target 'bkp_user/<password>@<connect string> as sysbackup'

connected to target database: BASE (DBID=1895852460)

RMAN> run {
RECOVER TABLE scott.test1 until scn 7536116
AUXILIARY DESTINATION '/u02/TEST'
DATAPUMP DESTINATION '/u02/TEST'
DUMP FILE 'job_parm2.dmp'
NOTABLEIMPORT;
}

6. Additional Information

RMAN connect with sysbackup, any select which require sysdba privilege / object level privilege will fail with 'ORA-01031'

SYSBACKUP has role of 'SELECT_CATALOG_ROLE' and not equivalent to SYSDBA, So selecting data from normal user table will result an error

Example:

RMAN> connect target 'bkp_user/<password>@<Connect string> as sysbackup';

connected to target database: ORCL (DBID=1895852460)

RMAN> select count(*) from v$database;

COUNT(*)
----------
1

RMAN> select count(*) from scott.emp;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/27/2016 21:22:44
ORA-01031: insufficient privileges

RMAN>

While connecting to RMAN with SYSBACKUP privileged user 'as sysbackup' clause is mandatory else we will get ORA-1031.

Example,

RMAN> connect target 'bkp_user/<password>@<connect string> as sysbackup'