How to use SYSBACKUP privilege to perform backup and recovery tasks without accessing data....

SYSBACKUP  role 

create OS user

useradd -c "Oracle backup user"  orabackup -p root123

[root@ora-prim ~]# su - orabackup

[orabackup@ora-prim ~]$ pwd

/home/orabackup

 cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/

[root@ora-prim admin]# ls -ltr

total 16

-rw-r--r-- 1 oracle oinstall 373 Oct 31  2013 shrept.lst

drwxr-xr-x 2 oracle oinstall  61 Nov 27 19:49 samples

-rw-r--r-- 1 oracle oinstall 460 Dec  3 22:51 listener.ora

-rw-r--r-- 1 oracle oinstall 601 Dec  8 17:40 tnsnames.ora

-rw-r--r-- 1 oracle oinstall 177 Dec  8 17:41 sqlnet.ora

[root@ora-prim admin]# cp tnsnames.ora /home/orabackup

[root@ora-prim orabackup]# chown orabackup:orabackup tnsnames.ora

[root@ora-prim orabackup]# pwd

/home/orabackup

[root@ora-prim orabackup]# cp /home/oracle/.bash_profile .

[orabackup@ora-prim ~]$ cat .bash_profile

cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export ORACLE_UNQNAME=prim

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export ORACLE_SID=prim

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export TNS_ADMIN=/home/orabackup   

[orabackup@ora-prim ~]$ sqlplus anuj/vihaan@vihaan

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 10 08:00:43 2016

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

Last Successful login time: Sat Dec 10 2016 06:14:29 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> !id

uid=1001(orabackup) gid=1001(orabackup) groups=1001(orabackup)

SQL> grant sysbackup to orabackup ;

grant sysbackup to orabackup

                   *

ERROR at line 1:

ORA-01917: user or role 'ORABACKUP' does not exist

good news ...

Now creating new user 

SQL> alter session set container=vihaan ;

Session altered.

SQL> create user ORABACKUP identified by root123 ;

User created.

SQL> grant sysbackup to orabackup ;

Grant succeeded.

[root@ora-prim orabackup]# su - orabackup

Last login: Sat Dec 10 07:58:34 GMT 2016 on pts/0

[orabackup@ora-prim ~]$ sqlplus ORABACKUP/root123@vihaan as sysbackup

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 10 08:13:49 2016

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

ORABACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          3

set pages 200 linesize 200 

col grantee      format a25

col granted_role format a25

select * from dba_sys_privs where grantee = 'SYSBACKUP';

col current_schema format a20

col session_user format a20

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

CURRENT_SCHEMA       SESSION_USER

-------------------- --------------------

SYS                  SYSBACKUP

 SQL> show user

USER is "SYSBACKUP"

RMAN> connect target ORABACKUP/root123@vihaan as sysbackup

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "as": expecting one of: "newline, ;"

RMAN-01007: at line 1 column 41 file: standard input

use like this ...

RMAN> connect target 'ORABACKUP/root123@vihaan as sysbackup'

connected to target database: PRIM (DBID=4221955747)

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

RMAN> list backup;

specification does not match any backup in the repository

RMAN> backup current controlfile;

Starting backup at 10-DEC-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=62 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 10-DEC-16

channel ORA_DISK_1: finished piece 1 at 10-DEC-16

piece handle=/u01/app/oracle/fast_recovery_area/PRIM/42C8A9F4DB2B18EBE0534700A8C0C45C/backupset/2016_12_10/o1_mf_ncnnf_TAG20161210T082630_d4qh1v9r_.bkp tag=TAG20161210T082630 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09

Finished backup at 10-DEC-16

Starting Control File and SPFILE Autobackup at 10-DEC-16

piece handle=/u01/app/oracle/fast_recovery_area/PRIM/autobackup/2016_12_10/o1_mf_s_930212804_d4qh247h_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-DEC-16

RMAN>  list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2       Full    17.23M     DISK        00:00:06     10-DEC-16

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20161210T082630

        Piece Name: /u01/app/oracle/fast_recovery_area/PRIM/42C8A9F4DB2B18EBE0534700A8C0C45C/backupset/2016_12_10/o1_mf_ncnnf_TAG20161210T082630_d4qh1v9r_.bkp

  Control File Included: Ckp SCN: 2518618      Ckp time: 10-DEC-16

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

3       Full    17.27M     DISK        00:00:00     10-DEC-16

        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20161210T082644

        Piece Name: /u01/app/oracle/fast_recovery_area/PRIM/autobackup/2016_12_10/o1_mf_s_930212804_d4qh247h_.bkp

  SPFILE Included: Modification time: 10-DEC-16

  SPFILE db_unique_name: PRIM

  Control File Included: Ckp SCN: 2518644      Ckp time: 10-DEC-16