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 sysdbaSQL> 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.