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.
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.
[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.
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 ~]$
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.
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:
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.