Oracle 19c – How to exclude AUDSYS schema data in a Datapump full export – it’s all about the filter

Martin Berger

22.11.2022 – Update with Inputs from Roy Swonger, thank you Roy

 

Why do we have AUDSYS data in our Oracle Data Pump full export?

In a customer project, I was asked why there is always data of schema owner AUDSYS included in an Oracle Data Pump full export. The AUDSYS schema contains Unified Audit Trail Records and therefore the data is included when the feature is enabled.

The demo case is a 19.15 Container Database with a PDB. The demo schema HR is loaded and there is a Unified Auditing Policy which gathers information whenever data from the schema HR are selected.

SQL> SELECT parameter, value FROM v$option WHERE parameter = 'Unified Auditing';

PARAMETER VALUE
-------------------- --------------------
Unified Auditing TRUE

In the Data Pump log file, you find entries like this:

. . "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0" 50.95 KB 0 Zeilen exportiert 
. . "AUDSYS"."AUD$UNIFIED":"SYS_P221" 23.04 MB 10666 Zeilen exportiert

Let’s try to exclude it.

1st Attempt – Exclude the schema when using expdp with a SCHEMA FILTER

$ expdp system/*****@//ci-datapump-test/pdbhr01.kestenholz.net full=y dumpfile=exp_FULL_PDBHR01_%U.dmp logfile=exp_FULL_PDBHR01.log directory=nfs EXCLUDE=SCHEMA:\"IN \(\'AUDSYS\'\)\"

Result: AUDSYS is not excluded, the filter did not work and was ignored. No error message occurred.

grep -E '"AUDSYS"' exp_FULL_PDBHR01.log
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0" 0 KB 0 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P221" 23.04 MB 10666 rows

2nd Attempt – Exclude the schema when using expdp with EXCLUDE=AUDIT_TRAILS

$ expdp system/*****@//ci-datapump-test/pdbhr01.kestenholz.net full=y dumpfile=exp_FULL_PDBHR01_%U.dmp logfile=exp_FULL_PDBHR01.log directory=nfs EXCLUDE=AUDIT_TRAILS

Result: No more AUDSYS data in the Data Pump export – let’s do the same with the API.

3rd Attempt – Exclude the schema when using API and a METADATA FILTER

dbms_datapump.metadata_filter(handle => l_datapump_handle, name => ‘EXCLUDE_PATH_EXPR’, value => ‘IN(”AUDIT_TRAILS”)’);

The PL/SQL Block:

declare
l_datapump_handle NUMBER; -- Data Pump job handle
l_datapump_dir VARCHAR2(20) := 'NFS'; -- Data Pump Directory
l_status varchar2(200); -- Data Pump Status

begin
l_datapump_handle := dbms_datapump.open(operation => 'EXPORT', -- operation = EXPORT, IMPORT, SQL_FILE
job_mode =>'FULL', -- job_mode = FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE
job_name => 'PDBHR01 FULL EXPORT JOB RUN 002', -- job_name = NULL (default) or: job name (max 30 chars)
version => 'latest'); -- version = COMPATIBLE (default), LATEST (dbversion), a value (11.0.0 or 12)

dbms_datapump.add_file(handle => l_datapump_handle,filename => 'exp_FULL_PDBHR01_%U.dmp',directory => l_datapump_dir);

dbms_datapump.add_file(handle => l_datapump_handle,filename => 'exp_FULL_PDBHR01.log' ,directory => l_datapump_dir ,filetype => DBMS_DATAPUMP.ku$_file_type_log_file);

dbms_datapump.metadata_filter(handle => l_datapump_handle, name => 'EXCLUDE_PATH_EXPR', value => 'IN(''AUDIT_TRAILS'')');

dbms_datapump.set_parameter(l_datapump_handle,'CLIENT_COMMAND','Full Consistent Data Pump Export of PDBHR01 Schema HR with PARALLEL 8');

dbms_datapump.set_parameter(l_datapump_handle,'FLASHBACK_TIME','SYSTIMESTAMP');

dbms_datapump.set_parallel(l_datapump_handle,8);

dbms_datapump.start_job(handle => l_datapump_handle);

dbms_datapump.wait_for_job(handle => l_datapump_handle,
job_state => l_status );

dbms_output.put_line( l_status );

end;
/

Result: No more AUDSYS data in the Data Pump export: Check!

Additional Information

Views where all EXPORT PATHS are listed:

  • DATAPUMP_PATHMAP
  • DATAPUMP_PATHS
  • <TABLE | SCHEMA | DATABASE>_EXPORT_OBJECTS

YouTube – Oracle Data Pump Internals by Roy Swonger

https://www.youtube.com/watch?v=XyVXaM9JXEA-

Summary

It’s all about the right filter. Do you have another idea how to exclude AUDSYS data? Let me know. And watch the video about the internals!