SMON_SCN_TO_TIME_AUX - CLUSTER

How to Reorg the object SMON_SCN_TO_TIME_AUX . The object type is CLUSTER.

Please test the steps on test environment first.

Take Backup
export ORACLE_SID=dev
export ORACLE_HOME=/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1
rman target /
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
crosscheck archivelog all;
crosscheck backup of database;
delete expired backup of database ;
backup full tag full_db_bkp_dev format '/ora_DEV/PROD_STAGE/RMAN_DBF_%d_%T_%s_%p_%u.bkp' (database);
sql 'alter system archive log current';
backup format '/ora_DEV/PROD_STAGE/RMAN_ARCH_%d_%T_%s_%p_%u.bkp' archivelog all;
backup format '/ora_DEV/PROD_STAGE/RMAN_CTL_%d_%T_%s_%p_%u.bkp' current controlfile;
release channel ch1;
release channel ch2;
}

shutdown immediate

startup restrict

# Enable event 12500. Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table.

alter system set events '12500 trace name context forever, level 10';

# Truncate the cluster SMON_SCN_TO_TIME

truncate cluster SMON_SCN_TO_TIME_AUX;

Rebuild the indexes
alter index SMON_SCN_TO_TIME_IDX rebuild;
alter index SMON_SCN_TO_TIME_AUX_IDX rebuild;
alter index SMON_SCN_TIME_TIM_IDX rebuild;
alter index SMON_SCN_TIME_SCN_IDX rebuild;

# Analyze the table to confirm it is clean

analyze table SMON_SCN_TIME validate structure cascade;

# Disable the event 12500 so SMON can resume updating the SMON_SCN_TIME table.

alter system set events '12500 trace name context off';

# Disable restricted session:

alter system disable restricted session;

shutdown immediate

startup