Influencing MINE Restart Plog Sequence [Unsupported]

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

Problem Description

This Articles explains how to make MINE restart at a particular PLOG sequence. 

Warning

  • THIS METHOD SHOULD BE USED AT YOUR OWN RISK. DBVISIT DOES NOT ENDORSE THIS METHOD.

  • DBVISIT WILL NOT PROVIDE SUPPORT IF IT CAUSES CONFLICTS.

  • IT MAY DO UNREPAIRABLE DAMAGE TO YOUR REPLICATION.

  • ONLY USE THIS METHOD IF YOU ARE PREPARED TO RE-INSTANTIATE THE REPLICATION IF THIS METHOD FAILS.

Solution

When MINE is restarted, it will always go back to mining the redo sequence specified by dbvrep> list obsolete redo. In some cases it may have to go back very far due to a long running uncommitted database transaction.

  • Always ensure the output of dbvrep> list obsolete redo is used as input to any archive log backup script. This ensures the last obsolete redo is on disk in case MINE has to restart.

  • Always ensure database transactions are investigated if the output of dbvrep> list obsolete redo is an old sequence.

There is a method to OVERWRITE the archive log sequence that MINE will need when restarted. This method may MAY RESULT IN CONFLICTS as data may be skipped.

THIS METHOD SHOULD BE USED AT YOUR OWN RISK. DBVISIT DOES NOT ENDORSE THIS METHOD, NOR WILL DBVISIT PROVIDE SUPPORT IF IT CAUSES CONFLICTS.

The unendorsed method is:

  1. Shutdown MINE.

  2. Determine at which PLOG sequence the MINE should be restarted. THIS SHOULD BE LESS THAN THE CURRENT APPLY SEQUENCE. The further back the less chance of data being missed.

  3. Logon to the MINE database as the Dbvisit Repository owner. Default is dbvrep.

  4. Save the current values in table dbrsmine_plog_current for backup and recovery purposes in case this method needs to be rolled back. There should only be 1 row.

  5. Run the following SQL. Data from another table is used to set the exact SCN boundaries of the given plog sequence:

    update dbrsmine_plog_current set first_scn = (select start_scn from DBRSMINE_PLOG_HISTORY where DDC_ID = 1 and MINE_PROCESS_NAME = 'MINE' and SEQUENCE = &PLOG_SEQUENCE) ,last_scn = (select last_scn from DBRSMINE_PLOG_HISTORY where DDC_ID = 1 and MINE_PROCESS_NAME = 'MINE' and SEQUENCE = &PLOG_SEQUENCE) ,last_plog_seq = &PLOG_SEQUENCE where DDC_ID = 1 and MINE_PROCESS_NAME = 'MINE' ;

  6. When prompted type in the value for plog_sequence. It will prompt 3 times. Use the same value each time.

  7. Commit the transaction.

  8. Restart MINE.

MINE will now restart from the plog sequence that was entered.

Note: The columns being updated in table dbrsmine_plog_current have the following meaning:

first_scn #First SCN of this plog.last_scn #On MINE restart, how much back MINE has to go to parse transactions opened as of this plog start.last_plog_seq #On MINE restart, how much back MINE has to go to parse transactions opened as of this plog start (as plog sequence).

Arjen Visser October 12, 2014 13:28