Recreate EVENT_LOG_TRANS with ORA-14280 (Doc ID 2454849.1)

Last updated on JULY 20, 2024

Applies to:

Goal

EXCHANGE SUBPARTITION to EVENT_LOG_TRANS can not be performed, the ILM during the archive period remains ONLINE, and the tablespace during the archive period is not displayed on the AVS console.

The ILM debugging information is output below.

===============================================================
--In the AVS environment
connect / as sysdba
alter system set events '46600 trace name context forever, level 8192';

connect avsys/<PASSWORD>
UPDATE ILM_DAEMON SET MONTH_COMPLETED=MONTH_COMPLETED-2;
commit;
exec ilm.job_handler;

- Please wait 15 minutes here and then do the following.
connect / as sysdba
alter system set events '46600 trace name context off';
===============================================================

---- XXX_j000_XXX.trc ----
*** 2018-09-21 11:09:53.748
Debug:ILM:move_parts: archiving part ILM_000000003789_000003_0570
Debug:ILM:execute_ddl_retry: ALTER TABLE AVSYS.EVENT_LOG EXCHANGE SUBPARTITION ILM_000000003789_000003_0570 WITH TABLE AVSYS.EVENT_LOG_TRANS UPDATE INDEXES
Error:ILM:execute_ddl_retry: Raising error within loop after ORA-14280
Error:ILM:execute_ddl_retry: Raising error ORA-14280 [ move_parts: EVENT_LOG --> EVENT_LOG_TRANS ]
Error:ILM:move_parts: Error - ORA-14280: all rows in table do not qualify for specified subpartition
Error:ILM:tablespace_offline: moving subpartitions, Error - ORA-14280: all rows in table do not qualify for specified subpartition
Error:ILM:handle_ilm: archiving tablespace: Error - ORA-20102: Error moving subpartitions tablespace AV_ILM_0571_0640 cannot be archived
Debug:ILM:can_eed_archive: tablespace=AV_ILM_0569_0638 **** return FALSE ****
Debug:ILM:job_handler: no ilm pending job
Debug:ILM:job_handler: done for month: 584back to sleep
Debug:ILM:Released ILM_ARCHIVING_LOCK
-----------------------------

In the ILM archive, the partition to be archived must be moved from "EVENT_LOG" to "EVENT_LOG_ARCH" in the Exchange subpartition.
ORA-14280 is occurring in the Exchange subpartition to "EVENT_LOG_TRANS".
Perhaps, it seems that some unnecessary data remains in "EVENT_LOG_TRANS".

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.