Versions Compared

Old Version 12

changes.mady.by.user Jakub Sejba

Saved on

compared with

New Version 13

changes.mady.by.user Jakub Sejba

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

 Adding new tables

Once the 2-way replication has been configured and is running, new objects can be added to the replication. 
New tables can be replicated with the command:

Section
Column
width5%

 

Column
width95%
Panel
bgColorCCC
dbvrep> PREPARE TABLE schema.table_name

Ensure that the appropriate privileges are given to the Dbvisit Replicate owner on both the source and target. The privileges are:

Section
Column
width5%

 

Column
width95%
Panel
bgColorCCC
SQL> grant select, update, insert, delete to dbvrep;

If a DDL replication was enabled during the Setup Wizard (Step 2 - Replication pairs), the Dbvisit Replicate owner on the target database already has SELECT/INSERT/UPDATE/DELETE ANY privileges. No other grants should be necessary.

Loading data before table is added to replication

When a new table is added to the replication, it is usually necessary to ensure that the historical data is in the target table before the replication starts.
For 2-way replication this process is slightly more complicated. One cannot simply take a source data as of given SCN and move it to the target server, as these data would be replicated back to the source as soon as the apply process is resumed (which would cause replication conflicts on the source server). It is necessary to label the data for the apply process to recognize it and ignore it.
To ensure data is synchronized correctly and there is no gap between loading the historical data and starting the replication, follow these steps:

...

It is necessary to pause the apply process for both replication directions. To do so choose one replication, pause the first apply process and then choose the second replication a pause the second apply process:

 

Panel
bgColorCCC
dbvrep> choose show
Process MINE chosen, it is a MINE.
Process APPLY chosen, it is a APPLY. 
 
dbvrep> pause apply
Apply requested to pause. 

dbvrep> choose replication mine1
Process type MINE set to: mine1.
Process type APPLY set to: APPLY1.

dbvrep> choose show
Process mine1 chosen, it is a MINE.
Process APPLY1 chosen, it is a APPLY.

dbvrep> pause apply1
Apply requested to pause. 

dbvrep> list status
MINE IS running. Currently at plog 10441 and SCN 43299785 (03/14/2013 11:50:59).
APPLY is PAUSED. Currently at plog 10441 and SCN 43299607 (01/01/1988 00:00:00).
MINE1 IS running. Currently at plog 3298 and SCN 43252417 (03/14/2013 11:50:59).
APPLY1 is PAUSED. Currently at plog 3298 and SCN 43252398 (03/14/2013 11:50:49).

2. Prepare the new table

Dbvisit Replicate will give the SCN of where the table will be replicated from.

 

Panel
bgColorCCC
dbvrep>
dbvrep> choose show
Process MINE chosen, it is a MINE.
Process APPLY chosen, it
is a APPLY
is a APPLY.
dbvrep>
dbvrep> prepare table SCHEMA_RECREATE.schema_recreate_tbl
Connecting to running apply [SCHEMA_RECREATE.schema_recreate_tbl]: [Apply table removed (0 metadata record(s)). Apply table added (1 metadata record(s)).]
Connecting to
running mine 
running mine [SCHEMA_RECREATE.schema_recreate_tbl]: [Table (un)prepared (4 internal records).]
Table SCHEMA_RECREATE.schema_recreate_tbl instantiated at SCN 43348595
dbvrep>
dbvrep> choose replication MINE1
Process type MINE set to: MINE1.
Process type APPLY set to: APPLY1.
dbvrep>
dbvrep>
show choose
show choose
dbvrep>
dbvrep> choose show
Process MINE1 chosen, it is a MINE.
Process APPLY1 chosen, it
is a APPLY
is a APPLY.
dbvrep>
dbvrep> prepare table SCHEMA_RECREATE.schema_recreate_tbl
Connecting to running apply [SCHEMA_RECREATE.schema_recreate_tbl]: [Apply table removed (0 metadata record(s)). Apply table added (1 metadata record(s)).]
Connecting to
running mine 
running mine [SCHEMA_RECREATE.schema_recreate_tbl]: [Table (un)prepared (4 internal records).]
Table SCHEMA_RECREATE.schema_recreate_tbl instantiated at SCN 43298074
dbvrep>
dbvrep>

3. Synchronize data

Use the SCN from step 2 to load the historical data (Example use datapump with flashback_scn=xxx)

 

Panel
bgColorCCC
dbvrep>
dbvrep> show apply_database
APPLY1.APPLY_DATABASE = UNITSRC
APPLY.APPLY_DATABASE = UNITTRG
APPLY1.APPLY_DATABASE_DBID = 433515465
*.APPLY_DATABASE =
APPLY.APPLY_DATABASE_DBID = 742136697
*.APPLY_DATABASE_DBID =

 

 

 

Panel
bgColorCCC
SET TRANSACTION NAME 'DBREPL_DB_UNITSRC_XID_UNITSRC';
insert into
insert into SCHEMA_RECREATE.schema_recreate_tbl (id, member)
select id
select id, member from SCHEMA_RECREATE.schema_recreate_tbl@unittrg
as of scn 43298074;
as of scn 43298074;

 

 

Panel
bgColorCCC
SET TRANSACTION NAME 'DBREPL_DB_UNITTRG_XID_UNITTRG';
insert into
insert into SCHEMA_RECREATE.schema_recreate_tbl (id, member)
select id
select id, member from SCHEMA_RECREATE.schema_recreate_tbl@unitsrc
as of scn 43348595
as of scn 43348595;

4. Resume the replication

 

 

Panel
bgColorCCC
dbvrep>
dbvrep> choose replication MINE
Process type MINE set to: MINE.
Process type APPLY set to: APPLY.
dbvrep>
dbvrep> resume apply
Apply requested
Apply requested to resume.
dbvrep>
dbvrep> choose replication MINE1
Process type MINE set to: MINE1.
Process type APPLY set to: APPLY1.
dbvrep>
dbvrep> resume apply1
Apply requested
Apply requested to resume.

 

Note that the table needs to exist on the target side before the apply process can be resumed. Using a datapump in step 3 is a good choice, since the import creates table automatically if it does not exist.

...

Removing tables & schemas