We're here to help!

Adding new table to replication without locking the table

Follow
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:
  • SET APPLY.INSTANTIATE_SCN NOW_NOLOCK
  • 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:
    • SQL> ALTER TABLE QA.TABLES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
  • #prepare the tables (we use OFFLINE as neither MINE nor APPLY is running; with OFFLINE we won't wait on network timeout)
    dbvrep> PREPARE OFFLINE table qa.tables
    Table QA.TABLES instantiated at SCN 54004817

    #load instantiation: process all PREPARE commands queued so far
    dbvrep> PROCESS LOCK CLEAR SCN
  • Import data to target database using SCN marked with RED:
  • impdp SYSTEM/manager@TARGET table_exists_action=TRUNCATE network_link=source directory=DATA_PUMP_DIR flashback_scn=54004817 tables=QA.TABLES logfile=QA_TABLES.log JOB_NAME=DP_JOB_0001
  • 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)

Have more questions? Submit a request

Comments

  • Avatar
    grace.creedon

    Can you please clarify thestep:
    'Bring back the parameter INSTANTIATE_SCN to NOW. SET APPLY.INSTANTIATE_SCN = NOW'

    If APPLY.INSTANTIATE_SCN was previosly set to NOW_NOLOCK, do we need to set it to NOW before restarting mine and apply ?