Mediator Resequencer Purging
Please find below steps on how to do Mediator Resequencer Purging –
Purge scripts now considered below table while executing loop/parallel purge
1. MEDIATOR_GROUP_STATUS
2. MEDIATOR_RESEQUENCER_MESSAGE
Messages in COMPLETED and ABORTED state only can be purged. Rest all cannot be purged.
Check current health of resequencer:
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) time, gs.status group_status, m.status msg_status, count(1), gs.component_dn
from mediator_group_status gs, mediator_resequencer_message m
where m.group_id = gs.group_id
and gs.status < = 3
and gs.component_status!=1
group by gs.status, m.status, gs.component_dn
order by gs.component_dn, gs.status;
PFB details of Group status and msg status which are purgeable/ non purgeable:
| Group status | Purgeable/ non pugeable |
| READY = 0 | Purgeable |
| LOCKED = 1 | NON Purgeable |
| ERROR = 3 | NON Purgeable |
| TIMED_OUT=4 | NON Purgeable |
| GROUP_ERROR=6 | NON Purgeable |
| MSG status | Purgeable/ non pugeable |
| READY = 0 | Purgeable |
| LOCKED = 1 | NON Purgeable |
| ERROR = 3 | NON Purgeable |
| COMPLETED = 2 | Purgeable |
| TIMED_OUT=4 | Purgeable |
| ABORTED=5 | Purgeable |
1. GRP_STATUS/MSG_STATUS = 0/0 – READY
These show the messages which are ready for processing and eligible to be Locked and processed by the resequencer. For a healthy system, this number would be quite low as the messages will be locked and processed continuously by the resequencer. When the messages arriving into the system have stopped, this count should drop to zero.
2. GRP_STATUS=0/MSG_STATUS=2 – PROCESSED
This count indicates the number of processed messages. This number will be seen to be growing over time. A Very high count (like > 1 million in the above example) indicates that the Resequencer purge is due and should be run soon to delete the processed messages.
3. GRP_STATUS=0/MSG_STATUS=5 – ABORTED
This count shows the number of message that are currently manually aborted by the administrator.
4. GRP_STATUS=1/MSG_STATUS=0 – LOCKED
This combination of states shows the messages within groups which are being currently processed.
5. GRP_STATUS=1/MSG_STATUS=2 – LOCKED
The count against this combination shows the number of messages which are processed for locked groups.
6. GRP_STATUS=3 – ERRORED
These show the messages against error’ed groups. These will need to be manually recovered from EM Console or the AIA Resubmission tool. They indicate messages which have failed processing due to various errors. If these messages can be recovered and processed successfully, in which case they transition to state GRP_STATUS=0/MSG_STATUS=2. If the errors are non recoverable, then they can be aborted from the EM Console and they move to GRP_STATUS=0/MSG_STATUS=5.