Audit Commands IN ORACLE 12C DATA PUMP

In Oracle 12c, Now we can also audit oracle data pump backups job by creating an audit policy just like a user/schema auditing.
With the help of this feature keeping eye on your data pump backups is easy now.

Syntax:
CREATE AUDIT POLICY Data_Pump_Policy_name
ACTIONS COMPONENT=DATAPUMP [EXPORT | IMPORT | ALL];

When this policy is applied to a user, their data pump jobs will appear in the audit trail.
The following policy audits all data pump operations. The policy is applied to the SYSTEM user.

[oracle@12c ~]$ sqlplus “/ as sysdba”

SQL> CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;

Audit policy created.

 

SQL> AUDIT POLICY audit_dp_all_policy BY system;

Audit succeeded.
// Run the following data pump command to backup emp table of SYSTEM schema:

[oracle@12c test_dir]$ expdp system/manager tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log
Export: Release 12.1.0.1.0 – Production on Mon May 5 12:39:52 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported “SYSTEM”.”EMP” 245.4 KB 16384 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/test_dir/emp.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon May 5 12:40:47 2014 elapsed 0 00:00:40
—x—

// Again run above data pump command:

[oracle@12c test_dir]$ expdp system/manager tables=emp directory=test_dir dumpfile=emp1.dmp logfile=expdp_emp1.log
Export: Release 12.1.0.1.0 – Production on Mon May 5 12:42:32 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** tables=emp directory=test_dir dumpfile=emp1.dmp logfile=expdp_emp1.log
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported “SYSTEM”.”EMP” 245.4 KB 16384 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/test_dir/emp1.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon May 5 12:43:19 2014 elapsed 0 00:00:40
—x—

 

// Checking the audit trail shows the data pump job was audited.

// Execute following PL/SQL procedure (as sysdba) to Flush audit information to disk:

SQL> EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.

 

// Now issue the following query to “unified_audit_trail” dictionary view for Datapump audit information: ( as sysdba )
SQL> set linesize 200
SQL> column event_timestamp FORMAT A30
SQL> column dp_text_parameters1 FORMAT A30
SQL> column dp_boolean_parameters1 FORMAT A30
SQL>
SQL>
SQL> SELECT event_timestamp, dp_text_parameters1, dp_boolean_parameters1
FROM unified_audit_trail
WHERE audit_type = ‘Datapump’;

 

Audit Commands IN ORACLE 12C DATA PUMP

Audit Commands IN ORACLE 12C DATA PUMP

 

By above we can audit data pump in oracle 12c.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply