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):
  • #no-lock instantiation; LOCK will just obtain single SCN for export
    dbvrep> PROCESS LOCK tables QA.tables
    Oldest active transaction SCN: 54004817 (no active transaction)
    No locking done, SCN will be moved to past to cater for in-flight transactions.

    #single-scn instantiation: unlock all tables and schemas, but keep the SCN
    dbvrep> PROCESS LOCK RELEASE LOCKS

    #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 53989380

    #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=53989380 tables=QA.TABLES logfile=QA_TABLES.log JOB_NAME=DP_JOB_0001
  • 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