Copy Audit and unified audit across databases

The client had a special request: copy the audit data to another database – once -, for compliance reasons.

The Audit data does not let it copy so easily. Before was usually in SYS schema, now it belongs to a special AUDSYS user. None allows to export data by default.

To copy the audit data from one database to another, there is special expdp argument: “include=audit_trails”. This only works in full export mode:

expdp system@server.domain.ch\pdb_service \
  DIRECTORY=expdp \
  DUMPFILE=audit_data_$(date +%Y%m%d_%H%M).dmp  \
  FULL=Y \
  INCLUDE=AUDIT_TRAILS 

On the new database, I’ve imported to a new user “old_audit” and specific tablespace, which I created before the import

SYS@DB1.PDB1> create tablespace unified_audit_ts;
SYS@DB1.PDB1> alter user system quota unlimited on unified_audit_ts;

SYS@DB1.PDB1> create user old_audit no authentication 
    default tablespace unified_audit_ts 
    quota unlimited on unified_audit_ts;

At the end I’ve imported the data, using remap_schema and remove the segment attributes, so all go to the new default tablespace:

impdp system@server.domain.ch\new_pdb \
  DIRECTORY=expdp 
  DUMPFILE=$(ls -tr audit_data*.dmp | tail -n1) 
  REMAP_SCHEMA=sys:old_audit 
  REMAP_SCHEMA=audsys:old_audit 
  TRANSFORM=SEGMENT_ATTRIBUTES:N