Versions Compared

Version Old Version 24 New Version Current
Changes made by

Arjen Visser

Arjen Visser

Saved on

Key

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

...

...

...

...

...

...

Prepare command - Adding new tables

Once the 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 No Format
bgColorCCC
dbvrep>
dbvrep> PREPARE
TABLE 
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 No Format
bgColorCCC
SQL> grant select, update, insert, delete on schema.table_name 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.

...

In one-to-many replication, you have to choose which replication process you want the new table to belong to before preparing the table for replication.

Section
Column
width5%

 

Column
width95%
Panel No Format
bgColorCCC
dbvrep> CHOOSE REPLICATION APPLY

dbvrep> PREPARE TABLE
TABLE schema
schema.table_name
 

 
dbvrep> CHOOSE REPLICATION APPLY1

dbvrep> PREPARE
TABLE schema
TABLE schema.table_name

Loading the 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. This is also called data instantiation. To ensure there is no gap between loading the historical data and starting the replication, we recommend the following steps:

  1. Pause the apply APPLY process (PAUSE APPLY)
  2. PREPARE the new table. Dbvisit Replicate will give the SCN of where the table will be replicated from. 
  3. Use the SCN from step 2 to load the historical data (Example use datapump with flashback_scn=xxx)
  4. Resume the replication (RESUME APPLY)

 

Info

The PREPARE commands add supplemental logging to the table if it was not there yet. This can sometimes result in the ORA-01466 error while performing step 3.

If this happens, we recommend using the internal commands to prepare a new table. In following example, scott.emp table is prepared. It first adds the supplemental logging, then waits for the SCN to advance then it does the actual PREPARE: 

dbvrep>ENGINE SUPPLEMENTAL LOGGING TABLE SCOTT.EMP ENABLE PRIMARY KEY
dbvrep>ENGINE WAIT_SCN_FLIP
dbvrep>PREPARE TABLE SCOTT.EMP

...

Note

The table needs to exist on the target side before the

...

APPLY process can be resumed. Using datapump in step 3 is a good choice, since the import creates the table automatically if it does not exist.

Note

impdp does not actually compare the SCNs, but timestamp converted using dbms_flashback to SCN and the last_ddl time in dba_objects.

 

Example: 

http://support.dbvisit.com/entries/24422248-Synching-up-one-table-when-it-gets-out-of-sync

...

To add a new schema to the replication run the following command:

Panelnoformat
bgColorCCC
dbvrep>dbvrep> PREPARE SCHEMA SCHEMA schema

All the tables within the schema including the historical data must be initialized in the same way as described above for the single table preparation. Follow those steps to ensure there is no data missing on the target side before the replication is started. 

...

To manually remove a table from the replication:

Section
Column
width5%

 

Column
width95%
panel No Format
bgColorCCC
dbvrep>
dbvrep> UNPREPARE
TABLE 
TABLE schema.table_name

 

Use UNPREPARE SCHEMA command to manually remove the whole schema.

...