Adding a Table in Replication Using PREPARE AS OF SCN Command

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

Problem Description

By default the PREPARE command adds a table/schema into the replication from the SCN where MINE is currently running. The as of SCN clause of the PREPARE command allows you to add a table into the replication from an SCN number you specify.

Prerequisites

  • MINE and APPLY must be on a SCN less than the SCN used in the PREPARE as of <scn> command. Therefore, pausing MINE & APPLY is required before executing the PREPARE as of <scn> command.

  • There must be no open transactions on tables you want to add in the replication. Therefore: Lock table(s) on source, capture SCN and release lock.

Steps Performed

  1. In the replication console execute pause MINE and pause APPLY commands.

  2. Lock table(s) on source, capture SCN and release lock.

  3. Execute prepare as of <SCN> table schema1.table1 command in console.

  4. Resume MINE.

  5. Import/export the table from source to target using flashback_scn=<SCN>.

  6. Resume APPLY.

Krupesh Desai October 29, 2014 12:26