Toad World Blog

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

Nov 28, 2018 9: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

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

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

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

 

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

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

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

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_IMPCALLOUT/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.054 KB      36 rows

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

. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows

. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P181"           84.87 KB      77 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"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows

. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 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:04:18 2018 elapsed 0 00:01:17

 

[oracle@db18 ~]$

 

It can be seen that the data of the schema AUDSYS was exported automatically without requiring any additional change in the export command; this is the new feature introduced in Oracle Database 18c.

The data is also imported normally; no changes are required in the import command as can be seen below:

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

 

Import: Release 18.0.0.0.0 - Production on Wed Nov 7 17:28:45 2018

Version 18.3.0.0.0

 

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

 

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

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

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

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_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

. . imported "SYS"."KU$_EXPORT_USER_MAP"                 6.054 KB      36 rows

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/TABLE_DATA

. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.531 KB      14 rows

. . imported "AUDSYS"."AMGT$DP$AUD$UNIFIED":"AUD_UNIFIED_P0"      0 KB       0 rows

. . imported "AUDSYS"."AMGT$DP$AUD$UNIFIED":"SYS_P181"   84.87 KB      77 rows

. . imported "SYS"."AMGT$DP$AUD$"                            0 KB       0 rows

. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"             0 KB       0 rows

. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"               0 KB       0 rows

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT"       5.953 KB       2 rows

. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT"              0 KB       0 rows

Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Nov 7 17:28:59 2018 elapsed 0 00:00:13

 

[oracle@db18 ~]$

 

Conclusion

Export and Import Data Pump was improved in Oracle Database 18c to help DBAs to export and import unified audit trail data across different environments. This data can be used for archiving or reporting. There are no changes required in the export/import commands to export the unified audit data; it is done by default.

 

 

 

 

 

Tags: Oracle GDPR

Deiby Gomez

Written by Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Certifications:

  • Oracle Database Cloud Administrator Certified Associate
  • Oracle Database Cloud Administrator Certified Professional
  • Oracle Database Cloud Service Operations Certified Associate
  • Oracle Certified Expert, Oracle Database 12c Maximum Availability Architecture
  • Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration
  • Oracle Database 12c Maximum Availability Architecture Certified Master
  • Oracle Database 12c: Data Guard Administrator
  • Oracle Database 12c Administrator Certified Master (OCM 12c)
  • Oracle Real Application Clusters 12c Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Master (OCM 11g)
  • Oracle Database 12c Administrator Certified Professional (OCP 12c)
  • Oracle RAC 11g and Grid Infraestructure Administrator
  • Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
  • Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert
  • Oracle Database 11g Administrator Certified Professional (OCP 11g)
  • Oracle Linux Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Associate