Skip to Main Content
  • Questions

  • not able to export AUDSYS.AUD$UNIFIED:SYS_P23021 -- same table/partition was exported w/o issues yesterday by job using full=yes

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sudheer.

Asked: June 19, 2024 - 1:23 pm UTC

Last updated: June 25, 2024 - 4:23 am UTC

Version: 19.20.0.0

Viewed 1000+ times

You Asked

our Daily job expdp has full=yes and worked OK till last night.

last night the expdp log showed:

ORA-31693: Table data object
"AUDSYS"."AUD$UNIFIED":"SYS_P23021" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists


Attempt to manually backup the same using same user created to export full database - fails with:

expdp $pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=***** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD\$UNIFIED\:SYS_P23021

Export: Release 19.0.0.0.0 - Production on Wed Jun 19 09:03:27 2024
Version 19.20.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Starting "PCSBACKUP"."EXPDP_TBL": /********@gecdwp_pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=******** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD$UNIFIED:SYS_P23021
ORA-39166: Object AUDSYS.AUD$UNIFIED was not found or could not be exported or imported.
ORA-31655: no data or metadata objects selected for job
Job "PCSBACKUP"."EXPDP_TBL" completed with 2 error(s) at Wed Jun 19 09:04:08 2024 elapsed 0 00:00:39


tried different combos for table name:
AUDSYS.AUD\$UNIFIED:SYS_P23021 -- AND ABOVE - same error

expdp $pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=***** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD$UNIFIED:SYS_P23021

Export: Release 19.0.0.0.0 - Production on Wed Jun 19 09:00:37 2024
Version 19.20.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Starting "PCSBACKUP"."EXPDP_TBL": /********@gecdwp_pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=******** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD:SYS_P23021
ORA-39166: Object AUDSYS.AUD was not found or could not be exported or imported.
ORA-31655: no data or metadata objects selected for job
Job "PCSBACKUP"."EXPDP_TBL" completed with 2 error(s) at Wed Jun 19 09:01:19 2024 elapsed 0 00:00:41

The same partition :SYS_P23021 got export w/o exceptions in last job run - is only 1 GB or so.

please help us find correct way/syntax to backup this one table/partition that sits in AUDSYS schema.

we are using 19.20.0.0 version /patch.

and Connor said...

ORA-08103 happens when a partition of a table is no longer available. Here's an example of that in action



In your case, most likely automated maintenance has dropped that old audit partition whilst you were doing the full export and hence it is no longer there.

So double check that this partition actually still exists, but a fresh full export should be fine

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yes - seems partition was dropped auto maintenances - and was recreated -

Sudheer Godgeri, June 25, 2024 - 4:01 am UTC

Thanks - this worked later without changes - your suggestion is the only plausible cause that could have caused it.

Connor McDonald
June 25, 2024 - 4:23 am UTC

glad it worked out
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.