Oracle DBA
alireza, May 02, 2024 - 9:46 pm UTC
Hi,
regarding to your solution i have a related question:
I EXP the AUD$ from other test db and want to import it into AUD$ (with ONLA_DATA param).
Because the target test db has an empty AUD$ and i need data in it to make the test. and we are dealing with PDB
However i get an error by IMPDB:
$ impdp dba_admin/password@aidinfrdb89:1521/PDBTEST parfile=IMP_table_archive_audit.par
Import: Release 19.0.0.0.0 - Production on Thu May 2 13:52:43 2024
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39166: Object SYS.AUD$ was not found or could not be exported or imported.
This is my IMPDP parfile:
DIRECTORY=DIR_DUMP
dumpfile=expdp_audit_test.dmp
LOGFILE=expdp_audit_test.log
JOB_NAME=impdp_audit
CLUSTER=N
remap_tablespace=AUDIT_AUD_TEST:SYSTEM
content=data_only
TABLES=('SYS.AUD$')
Any advice on this? Is it actually allowed to use IMPDP for the AUD$ (in the target db) ?
Thanks!
Alireza
May 07, 2024 - 3:37 am UTC
I would copy AUD$ to a "standard" table in a normal schema then export/import that, and then do an insert-select on the target
Oracle DBA
alireza, January 24, 2025 - 10:11 pm UTC
Hi Connor,
by the steps you recommended - please tell me by step 4) if any further action needed ? or oracle would consider the "new" AUD$" as the original table? i am asking because once i did the test, and started to cleanup the new AUD$ (via DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL....) i got an error "Oracle error -46267 Message = ORA-46267: Insufficient space in 'AUDIT_AUD_2023' tablespace, cannot complete operation"
Note: the TBS AUDIT_AUD_2023 is the OLD one , while the new TBS is "AUDIT_AUD_2025" - so obviously the DMBS reognize still the OLD TBS.
With other words , is it necessary (as Step 5) to DROP the old AUD$ (BORKEN_AUD)
like:
exec dbms_pdb.exec_as_oracle_script('drop table "SYS"."BORKEN_AUD" cascade constraints PURGE');
1) create table BACKUP_AUD$ as select * from AUD$;
2) run your move.
3) If all successful, simply drop the backup table
4)If not, then rename AUD$ to BROKEN_AUD$ and rename BACKUP_AUD$ to AUD$
In that way, you still have the old table to use when talking to Support etc, and everything stayed in the database.
February 14, 2025 - 5:27 am UTC
Notice the original question was for a particular need - they were having trouble with DBMS_AUDIT_MGMT, and hence I gave them an alternate along with: "please get the blessing of Oracle Support before proceeding".
For normal cleanup operations I would recommend using DBMS_AUDIT_MGMT, in particular SET_AUDIT_TRAIL_LOCATION