Toad World Blog

How to export & import Unified Audit Trail in Oracle 18c

Nov 28, 2018 8:27:55 AM by Deiby Gomez

Oracle Database 12c introduced the unified audit trail feature, which allows the capture of audit information from several sources, such as audit records (including SYS audit records) from unified audit policies and AUDIT settings, fine-grained audit records from the DBMS_FGA PL/SQL package, Oracle Database Real Application Security audit records, Oracle Recovery Manager audit records, Oracle Database Vault audit records, Oracle Label Security audit records, Oracle Data Mining records, Oracle Data Pump, and Oracle SQL*Loader Direct Load.

Before Oracle Database 18c, an export with Data Pump (expdp) didn’t export the data of schema AUDSYS by default. Starting in Oracle Database 18c unified audit trail data is by default exported with a full database or partial database export, and no change is necessary to make it work. This enables the DBA to perform exports of audit data for archiving, to move the data to other environments, or simply for reporting.

In the following example, an export will be performed in two environments: first in Oracle Database 12.2.0.1 and then in Oracle Database 18.3.0.0, and then we will compare the behavior of both operations to verify how this new feature works.

The first step is to enable the Unified Audit Trail functionality in both environments, 12c and 18c.

Enabling Unified Audit Trail

Checking if Unified Audit Trail is enabled:

SQL> select VALUE from V$OPTION where PARAMETER='Unified Auditing';

VALUE

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

FALSE

To enable Unified Audit Trail the database instance has to be shut down:

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Enabling Unified Audit Trail:

[oracle@db12c ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@db12c lib]$ make -f ins_rdbms.mk uniaud_on ioracle

[oracle@db12c lib]$

Starting the database instance up:

SQL> startup;

ORACLE instance started.

Total System Global Area 1644167168 bytes

Fixed Size 2925024 bytes

Variable Size 520097312 bytes

Database Buffers 1107296256 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

SQL>

Checking if Unified Audit Trail is enabled:

SQL> select VALUE from V$OPTION where PARAMETER='Unified Auditing';

VALUE

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

TRUE

Once Unified Audit Trail is enabled, an export with Data Pump will be executed in the 12c environment.

In the Oracle Database 12.2.0.1 environment

[oracle@db12c ~]$ expdp system/manager1 full=y directory=exports logfile=unified_audit_trail..dmp.log dumpfile=unified_audit_trail.dmp INCLUDE=AUDIT_TRAILS

Export: Release 12.1.0.2.0 - Production on Wed Nov 7 16:01:39 2018

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

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

and Unified Auditing options

Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=exports logfile=unified_audit_trail..dmp.log dumpfile=unified_audit_trail.dmp INCLUDE=AUDIT_TRAILS

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Total estimation using BLOCKS method: 704 KB

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER

Processing object type DATABASE_EXPORT/STATISTICS/MARKER

Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.093 KB 37 rows

. . exported "SYS"."AUD$" 0 KB 0 rows

. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows

. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows

. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows

. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows

. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

/home/oracle/unified_audit_trail.dmp

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Nov 7 16:02:23 2018 elapsed 0 00:00:43

[oracle@db12c ~]$

In the output, it can be seen that data of the schema AUDSYS was not exported.

Now the same operation will be executed in the 18c environment, without making any additional changes.

In the Oracle Database 18.3.0.0 environment

Exporting the data:

[oracle@db18 ~]$ expdp system/manager1 full=y directory=exports logfile=unified_audit_trail..dmp.log dumpfile=unified_audit_trail.dmp INCLUDE=AUDIT_TRAILS

Export: Release 18.0.0.0.0 - Production on Wed Nov 7 16:03:00 2018

Version 18.3.0.0.0

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production