Scenario 2 – Auditing RMAN and Oracle Data Pump Operations

In this scenario, we’ll explore how to audit Oracle Data Pump and Recovery Manager (RMAN) operations within your Oracle database environment.

Oracle Data Pump Auditing

1. Create Data Pump Audit Policy: Begin by creating an audit policy specifically for Oracle Data Pump export operations:

SQL> CREATE AUDIT POLICY DP_POL ACTIONS COMPONENT=datapump EXPORT;

 

2. Enable Audit Policy: Once the audit policy is created, enable it:

SQL> AUDIT POLICY DP_POL;

 

3. Verify Policy Status: Confirm that the audit policy is enabled:

SQL> COL USER_NAME FORMAT A10

SQL> COL POLICY_NAME FORMAT A10

SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME LIKE ‘%DP%’;

 

4. Perform Export Operation: Execute an export operation using Oracle Data Pump. This step is typically performed at the operating system level.

$ rm /u01/app/oracle/admin/orcl/dpdump/HR_tables.dmp
$ expdp system/oracle_4U dumpfile=HR_tables tables=HR.EMPLOYEES

4. View Audit Data: After performing the export operation, access the resulting audit data:

SQL> SET PAGES 100

SQL> SELECT DBUSERNAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1 FROM UNIFIED_AUDIT_TRAIL WHERE DP_TEXT_PARAMETERS1 IS NOT NULL;

 

5. Flush Audit Trail: If the audited data is still in memory, flush the unified audit trail to disk:

        SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

6. View Audit Data Again: Once the audit data is flushed to disk, view it again:

         SQL> SELECT DBUSERNAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1 FROM                                      UNIFIED_AUDIT_TRAIL WHERE DP_TEXT_PARAMETERS1 IS NOT NULL;

RMAN Auditing

For RMAN backup, restore, and recover operations are audited by default. You don’t need to create a separate audit policy.

1. Perform Backup Operation: Execute a backup operation using RMAN:

$ rman target /

RMAN> BACKUP TABLESPACE USERS;

RMAN> EXIT;

3. Restore and Recover Operation: Remove the USERS tablespace datafile, then restore and recover it using RMAN:

$ sqlplus / as sysdba

 

SQL> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
SQL> EXIT;

$ rman target /

 

RMAN> RESTORE TABLESPACE USERS;
RMAN> RECOVER TABLESPACE USERS;
RMAN> EXIT;

 

 

4. View Audit Data: Access the resulting audit data for RMAN operations:

$ sqlplus / as sysdba

SQL> ALTER TABLESPACE USERS ONLINE;

SQL> SELECT DBUSERNAME, RMAN_OPERATION FROM UNIFIED_AUDIT_TRAIL WHERE RMAN_OPERATION IS NOT NULL;

5. Flush Audit Trail: If necessary, flush the unified audit trail to disk:

SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

 

6. View Audit Data Again: Once the audit data is flushed to disk, view it again:

SQL> SELECT DBUSERNAME, RMAN_OPERATION FROM UNIFIED_AUDIT_TRAIL WHERE RMAN_OPERATION IS NOT NULL;

By following these steps, you can effectively audit Oracle Data Pump and RMAN operations within your Oracle database environment.