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 | ||
|---|---|---|
| ||
|
| width | 95% |
|---|
| bgColor | CCC |
|---|
dbvrep> PREPARE TABLETABLE
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 | ||
|---|---|---|
| ||
|
| width | 95% |
|---|
| bgColor | CCC |
|---|
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 APPLY process is resumed (which would cause replication conflicts on the source server). It is necessary to label the data for the apply 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:
| Note | |||||||||
|---|---|---|---|---|---|---|---|---|---|
In the following example we are using a 2-way configuration described by this table:
|
1. Pause the
...
APPLY processes
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:
| Panelnoformat | ||
|---|---|---|
| ||
dbvrep>dbvrep> choose show |
2. Prepare the new table
The new table has to be prepared on both sides. For each database, Dbvisit Replicate will give the SCN of where the table will be replicated from.
To prepare the table choose the first replication, run the PREPARE TABLE command, then choose the second replication and run the PREPARE TABLE command again:
| Panelnoformat | ||
|---|---|---|
| ||
dbvrep> choose show |
| No Format | ||
|---|---|---|
| ||
dbvrep> choose replication MINE1 |
3. Synchronize data
Use the
...
SCNs from step 2 to load the historical data
...
...
.
For 2-way replication you cannot simply move the historical data from the database A to B and vice versa, because Dbvisit Replicate would try to replicate this data back as soon as the APPLY processes are resumed. This would cause the data corruption and replication conflicts, as the data is already in both databases.
To synchronize the data properly, use the named Oracle transaction. Dbvisit Replicate engine will recognize the name of the transaction and will not replicate any change done by such transaction.
Run the following command to obtain an APPLY database name:
| No Format | ||
|---|---|---|
| ||
dbvrep> show apply_database |
...
To move the data from the database UNITTRG to the database UNITSRC set the transaction name to DBREPL_DB_UNITSRC_XID_xxx, where "xxx" is APPLY1.APPLY_DATABASE value obtained earlier.
Use the database link to select the data from the UNITTRG database using the SCN given by the PREPARE TABLE command. Notice the MINUS clause to omit a data common to both tables.
On UNITSRC database run:
| No Format | ||
|---|---|---|
| ||
SET TRANSACTION NAME 'DBREPL_DB_UNITSRC_XID_UNITSRC'; |
AS OF SCN 43298074
MINUS SELECT id, member
FROM SCHEMA_RECREATE.schema_recreate_tbl;
COMMIT; |
To move the data from UNITSRC to UNITTRG repeat the previous steps using the APPLY.APPLY_DATABASE value for the transaction name, the database link to UNITSRC and the SCN given by PREPARE TABLE.
On UNITTRG database run:
| No Format | ||
|---|---|---|
| ||
SET TRANSACTION NAME 'DBREPL_DB_UNITTRG_XID_UNITTRG'; |
4. Resume the replication
...
Resume both the APPLY and APPLY1 processes:
| No Format | ||
|---|---|---|
| ||
dbvrep> choose replication MINE |
...
Adding new schemas
To add a new schema to the 2-way replication, use the following command:
| No Format | ||
|---|---|---|
| ||
dbvrep> PREPARE SCHEMA schema_name |
Note that
...
Adding new schemas
...
for every table within the schema you have to synchronize the data as described above. These are the steps to follow:
Pause the APPLY processes for both replication directions .
Run the PREPARE SCHEMA command for both replications.
Synchronize the data for all tables (using transaction names as shown above)
Resume both APPLY processes
Removing tables & schemas
To manually remove a table from the replication, use the UNPREPARE TABLE command for both directions:
| No Format | ||
|---|---|---|
| ||
dbvrep> choose show
Process MINE1 chosen, it is a MINE.
Process apply1 chosen, it is a APPLY.
dbvrep> unprepare table SCHEMA_RECREATE.schema_recreate_tbl
Connecting to running mine [SCHEMA_RECREATE.SCHEMA_RECREATE_TBL]: [Table (un)prepared (4 internal records).]
Connecting to running apply:[Apply table removed (1 metadata record(s)). Apply table added (0 metadata record(s)).]
Table SCHEMA_RECREATE.schema_recreate_tbl processed.
dbvrep> choose replication MINE
Process type MINE set to: MINE.
Process type APPLY set to: APPLY.
dbvrep> unprepare table SCHEMA_RECREATE.schema_recreate_tbl
Connecting to running mine [SCHEMA_RECREATE.SCHEMA_RECREATE_TBL]: [Table (un)prepared (4 internal records).]
Connecting to running apply:[Apply table removed (1 metadata record(s)). Apply table added (0 metadata record(s)).]
Table SCHEMA_RECREATE.schema_recreate_tbl processed. |
Use UNPREPARE SCHEMA command to manually remove the whole schema.