SYSBACKUP and SYSDG permissions in Oracle 12c

Introduction

In many companies there is a clear separation of duties for various Oracle Database related tasks such as administering ASM and backing up/restoring Oracle databases.

In the past, DBAs used SYSDBA permission for administering ASM and RMAN. As you probably know, SYSDBA is the most powerful permission in Oracle Database which even allows viewing all the application data.

Oracle realized that they need to address the separation of duties requirement of many customers and therefore they have provided in Oracle 11g a dedicated permission for administering ASM - I've written a dedicated blog post in the past for this matter. The SYSASM permission cannot access application data, but it can perform various ASM related management tasks (such as altering diskgroup, adding disks, etc.)

What about RMAN?

Until Oracle Database version 12cR1, there wasn't a good solution from a separation of duties when it comes to RMAN backups as users had to use SYSDBA which also allows them to access any application data (as well as other strong permissions).

In Oracle 12cR1, Oracle introduced the SYSBACKUP permission which allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.

You can view here the full list of operations allowed by this administrative privilege

And what about Data Guard?

Very similar to RMAN, Oracle also introduced in version 12cR1 a dedicated privilege named SYSDG which can be used with the Data Guard Broker and the DGMGRL command-line interface. 

Demo

First, we can connect to a 12c instance and look for those accounts. Next step would be to connect / AS SYSBACKUP since I'm logged with a user that has OS permissions to connect without any username and password

SQL> SELECT username, account_status
  FROM dba_users
 WHERE username LIKE '%SYS%';

USERNAME   ACCOUNT_STATUS
---------- --------------------------------
SYS        OPEN
SYSTEM     OPEN
SYS$UMF    EXPIRED & LOCKED
APPQOSSYS  EXPIRED & LOCKED
GGSYS      EXPIRED & LOCKED
WMSYS      EXPIRED & LOCKED
SYSBACKUP  EXPIRED & LOCKED
SYSRAC     EXPIRED & LOCKED
AUDSYS     EXPIRED & LOCKED
SYSKM      EXPIRED & LOCKED
SYSDG      EXPIRED & LOCKED

SQL> connect / as sysbackup
Connected.
SQL> show user
USER is "SYSBACKUP"

I can also create a new user and grant him the SYSBACKUP or SYSDG permissions

SQL> connect / as sysdba
Connected.

SQL> create user C##PINI identified by PINI;
User created.

SQL> grant SYSBACKUP to C##PINI;
Grant succeeded.

SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;

USERNAME   SYSBA SYSDG
---------- ----- -----
SYS        FALSE FALSE
SYSDG      FALSE TRUE
SYSBACKUP  TRUE  FALSE
SYSKM      FALSE FALSE
C##PINI    TRUE  FALSE

Note that in order to connect to the database as either SYSDG or SYSBACKUP using a password, there must be a password file for it because it is possible to connect even when the database is not up and running, as follows

SQL> connect / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  8793400 bytes
Variable Size             989856456 bytes
Database Buffers          637534208 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> connect c##pini/pini
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.

SQL> connect c##pini/pini as SYSBACKUP;
Connected.

Summary

In this post we've reviewed the SYSDG and SYSBACKUP users and permissions in Oracle 12c which could be useful in case that in your company there is a requirement to have a separation of duties for backup/recovery as well as for Data Guard related administration tasks. I hope you find it useful for you.

Table point in-time recovery using RMAN

Introduction
In this post I'd like to present a new 12c feature - RMAN table point in time recovery.
This feature allows to perform a recovery for a specific table or table partition via RMAN, and it can be very useful in the following cases :

• The table was truncated - so you can't use the 10g Flashback Table feature as the undo is not available
• The table was dropped with the purge clause -so you can't use the 10g Flashback Drop feature
• Table was logically corrupted and no undo is available - so you can't use the 10g Flashback Table feature
• Table was logically corrupted and a DDL operation modified the structure of the table - so you can't use the 10g Flashback Table feature in order to flashback the table prior to the DDL

In order to overcome this scenarios before Oracle 12c you could recover the entire database to a point in time recovery - however, you will also recover all the other objects to the same point in time which will result in a data loss.

A better option would be to perform a tablespace point-in-time-recovery (only available for the Enterprise Edition) which means that you can recovery the entire tablespace of that table to an auxiliary instance and then manually export and import the table to the target database.

The new feature in Oracle 12c performs all this automatically.

Prerequisites

In order to use this feaute you must have a backup of the tablespace/s for the selected table/tables because during the recover process Oracle will automatically perform point in time recovery using those tablespaces and then it will export the table/s from the auxiliary (temporary) instance and then it will import the data into the target (source) instance.

Demonstration:

In this demonstration I'm connected with user "pini", creating a table "emp" and inserts 1 row and then commit it:

SQL> show user;
USER is "PINI"
SQL> create table emp (id number, name varchar2(20));
Table created.
SQL> insert into emp values (1, 'DAVID');
1 row created.
SQL> commit;
Commit complete.

Now, I check what's the current SCN so I can use it later in the RMAN RECOVER command (RMAN supports point-in-time-recovery until SCN or TIME or SEQUENCE)

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
   25427549

Now I will drop it with the "purge" option (so the flashback drop feature is irrelevant):

SQL> drop table emp purge;
Table dropped.

The next step would be to connect to RMAN and execute the RECOVER TABLE command.

You should also specify the auxiliary destination which is the location of the auxiliary database files and also the Data Pump Export Dump File:

RMAN> recover table pini.emp until scn 25427549 auxiliary destination '/oravl01/recover';

Starting recover at 07-SEP-15
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='Fkyk'

initialization parameters used for automatic instance:
db_name=O12102NP
db_unique_name=Fkyk_pitr_O12102NP
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oravl01/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=200
db_create_file_dest=/oravl01/recover
log_archive_dest_1='location=/oravl01/recover'
#No auxiliary parameter file used
...
...
...
...
Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_Fkyk_kvas" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Fkyk_kvas":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "PINI"."EMP"                                5.476 KB       1 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_Fkyk_kvas" successfully completed at Mon Sep 7 14:13:05 2015 elapsed 0 00:00:11
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_temp_bytw2co8_.tmp deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/onlinelog/o1_mf_3_bytw4md4_.log deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/onlinelog/o1_mf_2_bytw4m1t_.log deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/onlinelog/o1_mf_1_bytw4lhq_.log deleted
auxiliary instance file /oravl01/recover/FKYK_PITR_O12102NP/datafile/o1_mf_users_bytw4gos_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_sysaux_bytvy598_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_undotbs1_bytvy590_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/datafile/o1_mf_system_bytvy59h_.dbf deleted
auxiliary instance file /oravl01/recover/O12102NP/controlfile/o1_mf_bytvxw4w_.ctl deleted
auxiliary instance file tspitr_Fkyk_10996.dmp deleted
Finished recover at 07-SEP-15

That's it!
The EMP table is now available in the source instance with the data which is relevant to the selected time period (in my case to SCN 25427549):

SQL> select * from emp;
        ID NAME
---------- --------------------
         1 DAVID

Few Notes:

• You can specify a different location for the dump file using the DATAPUMP DESTINATION clause
• During the last step of this process, i.e. during the import step, you may choose to import the table to a new table name and not to its original name using the REMAP TABLE clause
• During the last step of this process, i.e. during the import step, you may choose to import the table to a new tablespace and not to its original tablespace using the REMAP TABLESPACE clause
• If you prefer to have a dump file point in time export backup without actually importing it backup to the target (source) database, use the NOTABLEIMPORT clause

Conclusions

• This feature requires minimum work and effort from the DBA
• Due to the fact that as part of the recovery process RMAN needs to restore an auxiliary instance, and also export and import the relevant table/s, this whole process has an overhead (mainly in terms of I/O load), therefore If you can use other options for recovering the table (such as Flashback Table/Flashback Drop) you should use them and avoid using this feature. Also, table-recovery using this feature takes much longer than using the Flashback drop/Flashback table
• When you can't use Flashback drop/Flashback table to recover the table, you may find this feature very useful.
• This feature is available with the Enterprise Edition only