Adding new table to replication without locking the table

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

Problem Description

No-lock instantiation option

One of the newest features introduced by Dbvisit Replicate 2.7 was instantiation method without locking the instantiated tables. Regarding instantiation methods and no-lock option, please read following article: http://blog.dbvisit.com/when-no-locks-are-a-good-thing/

You can choose no-lock option during setup wizard. Here are the steps needed to be done in order to add new table without locking them.

Steps Performed

  • Check list obsolete redo, in order to be sure, that you can shutdown the mine process:

  • dbvrep> list obsolete redo Thread 1 last obsolete sequence#: 3050 at 12.11.2016 01:46:38 (29 minutes and 22 seconds ago)

  • If the time of restart is fine, then shutdown both mine and apply processes:

  • dbvrep> shutdown all Are you sure you want to shutdown? (Yes/No) [No] yes

  • Check, what is your instantiation method:

  • dbvrep> show INSTANTIATE_SCN *.INSTANTIATE_SCN = NOW APPLY.INSTANTIATE_SCN = NOW_NOLOCK

  • If it is different than NOW_NOLOCK, please run following command:

  • Now run following set of commands in order to prepare the table without any locks (all commands marked blue are mandatory):

  • Enable supplemental logging on table you wish to prepare:

  • Import data to target database using SCN marked with RED:

  • Bring back the parameter INSTANTIATE_SCN to NOW. SET APPLY.INSTANTIATE_SCN = NOW

  • Startup the MINE and APPLY processes - run the *run*sh scripts on source and target database.

 Similarly, you can prepare also schemas instead of tables.

NOTE: When preparing table with nolock option, it is not always guaranteed, that transactions writing to the table at the exact time when table is prepared, will be replicated correctly to TARGET, which can result in inconsistent data. Therefore it is advised, that tables are always prepared in the conventional way (with locking the table)