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.

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 ?