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.
- 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 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.