Oracle streams woodoo magic - manual checkpoint movement

In some cases you may need to change position of current processing prosition without reinstantiation.
Common cases is restricted access to the source system, sloth or simple desire to correct manually some records in replicated tables without export/import of terrabytes of data.

Move capture process checkpoint back

This is a simplest operation. Oracle holds recovery position, point to continue recovery process after stop capturer, in the system.logmnr_restart_ckpt$ table, including history of processed checkpoints. All of you need to do:

Stop capture process

Under privileged user provide the following command

delete from system.logmnr_restart_ckpt$
where ckpt_scn>scn_number 
  and session#=capture_process_number;
commit;

Capture process "forget" his previously processed job and repeatedly mines the logs

Move capture process checkpoint forward

Consider this operation as opposed of previous: we need take capture process new recovery point. Simplest way is copy and correct exists record/
Its may need a few attempts, because i still have questions about cheap way to get a correct SCN and that is "spare", but it is not hinder to get results.

Stop capture process

insert into system.logmnr_restart_ckpt$
select session#, valid,

approx_required_scn_number, -- CKPT_SCN

xidusn, xidslt, xidsqn, session_num, serial_num, ckpt_info, flag, offset, client_data,
aprox_spare_num, -- SPARE1, i'm get chkp_scn minus 1
spare2 from system.logmnr_restart_ckpt$
where valid = 1 and 

session#=capture_process_number -- if you have more than 1 caprurer

and ckpt_scn=nnnnnn and session_num=nnnn -- any one record

order by ckpt_scn desc

If you have success, you can see it in the alert.log, capture process show recovery SCN lager than you have before. In other case, repeat the operation with another scn

Move apply process checkpoint

 stop apply process

clear unprocessed messages:

delete from sys.streams$_apply_progress;

delete from sys.streams$_apply_spill_msgs_part;

Set same SCN for all listed columns: 

COMMIT_SCN
OLDEST_SCN  
PROCESSED_SCN 
SPILL_LWM_SCN