A few days back I posted a video how Unified Auditing is now the only support auditing option in 23ai, and gave you some easy steps on how to migrate your existing traditional audit settings to the equivalent audit policies under Unified Audit.
As the video also reassures – when you upgrade your database, your existing traditional audit command will still continue to work in 23c, but any attempt to change them or add new ones will be blocked.
A few people reached out to me with a concern about this message, namely:
“What if we are not using an upgrade strategy? What if we plan to simply DataPump from our existing version to version 23ai? Will out existing audit settings be rejected during import?”
I didn’t know the answer to this question, so I figured it was time for a test. On a fresh pluggable database running 21c, I put a few traditional audit commands in place.
SQL> audit select table by scott;
Audit succeeded.
SQL> audit select on hr.employees;
Audit succeeded.
SQL> audit alter table whenever successful;
Audit succeeded.
SQL> select owner, object_name from DBA_OBJ_AUDIT_OPTS where OWNER = 'HR';
OWNER OBJECT_NAME
------------------------------ --------------------------------
HR EMPLOYEES
SQL> select user_name, audit_option from DBA_STMT_AUDIT_OPTS;
USER_NAME AUDIT_OPTION
------------------------------ --------------------------------
ALTER TABLE
and then exported the entire PDB in the same fashion as a customer would do so in preparation for moving to version 23ai.
C:\>expdp system/admin@pdb21b full=y dumpfile=pdb21b.dmp directory=ctmp logfile=pdb21b.log
Export: Release 21.0.0.0.0 - Production on Wed Mar 27 10:28:47 2024
Version 21.13.0.0.0
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@pdb21b full=y dumpfile=pdb21b.dmp directory=ctmp logfile=pdb21b.log
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/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
[snip]
. . exported "SYS"."GV_$UNIFIED_AUDIT_TRAIL" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.914 KB 1 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 0 KB 0 rows
. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "SCOTT"."T" 5.240 MB 50000 rows
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
C:\TMP\PDB21B.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Mar 27 10:31:23 2024 elapsed 0 00:02:34
I then created a fresh version 23 database and attempted to import that dump file into the new version.
[oracle@db23oel8 admin]$ impdp system/admin@pdb23sa directory=sftmp full=y logfile=pdb21_to_23.log dumpfile=PDB21B.DMP
Import: Release 23.0.0.0.0 - Limited Availability on Tue Mar 26 22:32:26 2024
Version 23.4.0.23.11
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Limited Availability
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@pdb23sa directory=sftmp full=y logfile=pdb21_to_23.log dumpfile=PDB21B.DMP
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER/LABEL_SECURITY
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER/SEMANTIC
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER/SCHEDULER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER/WMSYS
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER/DATAPUMP
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/AUDIT_TRAILS
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/DATAPUMP
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/LABEL_SECURITY
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/NETWORK_ACL
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/PSTDY
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/SCHEDULER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/SEMANTIC
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/SMB
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/SQL_FIREWALL
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/TSDP
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER/WMSYS
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
Processing object type DATABASE_EXPORT/PROFILE
[snip]
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER/SQL_FIREWALL
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER/TSDP
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER/WMSYS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SCOTT"."T" 5.240 MB 50000 rows
. . imported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . imported "SCOTT"."EMP" 8.773 KB 14 rows
. . imported "HR"."LOCATIONS" 8.437 KB 23 rows
. . imported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . imported "HR"."JOBS" 7.109 KB 19 rows
. . imported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . imported "HR"."COUNTRIES" 6.367 KB 25 rows
. . imported "SCOTT"."DEPT" 6.023 KB 4 rows
. . imported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . imported "HR"."REGIONS" 5.546 KB 4 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/AUDIT_TRAILS
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/DATAPUMP
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/LABEL_SECURITY
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/NETWORK_ACL
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/PSTDY
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/SCHEDULER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/SEMANTIC
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/SMB
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/SQL_FIREWALL
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/TSDP
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER/WMSYS
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER/LABEL_SECURITY
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER/SEMANTIC
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER/SCHEDULER
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER/WMSYS
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER/DATAPUMP
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at Tue Mar 26 22:35:13 2024 elapsed 0 00:02:40
Beside the usual warnings about the data dictionary already existing, the import completed without incident. But of course the big question is – Were my traditional audit settings transferred over, or were they silently rejected?
SQL> connect system/admin@db23c
Connected.
SQL> select owner, object_name from DBA_OBJ_AUDIT_OPTS;
OWNER OBJECT_NAME
------------------------------ ------------------------------
HR EMPLOYEES
SQL> select user_name, audit_option from DBA_STMT_AUDIT_OPTS;
USER_NAME AUDIT_OPTION
------------------------------ ----------------------------------------
ALTER TABLE
SCOTT SELECT TABLE
So yes, the Database Security and DataPump teams have thought about this scenario. If you are using DataPump to upgrade your database to version 23ai, then your audit settings will come across as well without you needing to take any specific actions. Note that we still strongly recommend you look at converting to Unified Audit (as per the video) because you’ll get a much better auditing experience.
This does not mean that you can extract the audit commands from the DataPump dump file and run them yourself. For example, generating a SQL file from my original dump file reveals the generated audit commands that DataPump will use on import
[oracle@db23oel8 admin]$ impdp system/admin@pdb23sa directory=sftmp full=y logfile=pdb21_to_23.log dumpfile=PDB21B.DMP sqlfile=db23_import.sql
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
AUDIT SELECT ON "HR"."EMPLOYEES" BY ACCESS WHENEVER SUCCESSFUL;
AUDIT SELECT ON "HR"."EMPLOYEES" BY ACCESS WHENEVER NOT SUCCESSFUL;
But if I attempt to run these manually, I’ll get an error (as expected)
SQL> AUDIT SELECT ON "HR"."EMPLOYEES" BY ACCESS WHENEVER SUCCESSFUL;
AUDIT SELECT ON "HR"."EMPLOYEES" BY ACCESS WHENEVER SUCCESSFUL
*
ERROR at line 1:
ORA-46401: No new traditional AUDIT configuration is allowed. Traditional
auditing is desupported, and you should use unified auditing in its place.
Help: https://docs.oracle.com/error-help/db/ora-46401/
If you’re wondering how DataPump manages to overcome this restriction, well, that is due to some internal tweaks we can run during import to allow the commands to succeed. It won’t take you much effort to work out how this is done, but this is not a recommendation for you to start tinkering with internals to shoe horn old audit commands into 23ai. That’s a step down the road to not being supported.
However, if you do have a particular need for traditional audit (for example, maybe you have a 3rd party app that issues AUDIT commands), then get in touch with Support, and talk to them about how this might be managed in a supported way.
Learning is not a spectator sport 
Got some thoughts? Leave a comment