The issue of out of sync of a schema occurs from time to time. The most common reasons being, the whole schema is taken out of replication due to problems with some of its tables. At other times, a new schema is added to replication when the target schema is just a cold image of the source with the premise that the target schema will be synchronized later.
There are two convenient ways to sync up schema, each offering its own characteristic advantage:
A: using flashback_scn export utility in Oracle or “copy using” command in SharePlex
This should not be a two way replication otherwise some additional steps need to be carefully executed and SharePlex Support can be involved for details.
This method is preferred if the schema is large in terms of the # and size of the tables. The flashback_scn approach is a bit involved but the “copy using” is very easy to implement unless this is a two-way replication. Both of these use Oracle Export/Import utility. The flashback_scn is faster than “copy using” as the former uses datapump export utility whereas the latter uses conventional export utility.
A1: Here are the steps involved for flashback_scn method:
Prerequisite: The schema should be replicating via a dedicated named Post queue that does not route any other schema thru it. The Additional Information section describes how to implement this if the schema does not already replicate via named Post queue.
First issue a “flush” from the Source to the target (i.e. on source at sp_ctrl> flush o.sourcesid queue <queue_name>).
1. Begin an Export of the Source schema with flashback_scn option in database mode.
2. After the Export is finished on the Source, and after Post stops due to flush on the Target, compare the Source tables to the Target tables to make sure both are the same structure (no DDL changes have occurred).
3. Import the data into the Target schema.
4. Disable any triggers or constraints (except PK or U type) on target tables.
5. Run reconcile on the Post queue using SCN option and use the SCN as was used in step 2 but reconcile up to SCN+1
6. Start Post on target.
A2: Here are the steps involved in “copy using”
1. On source create a config file containing the schema to be sync’d by using “create config” command in sp_ctrl. The config file does not need to have named queue notation. It can also have wildcard if that is convenient due to sheer number of tables in that schema. It should conform to the syntax of a config file, namely datasource, source and target tables and target route. Let us call it config_file_name.
2. On target start the launcher process as:
sp_ctrl>start launcher
3. On source sp_ctrl issue the command:
sp_ctrl> copy using config_file_name
4. On source sp_ctrl monitor the progress of “copy using” by:
sp_ctrl>copy status
OR
sp_ctrl>job status
Once the copy finishes, you can stop the launcher if you wish by issuing the following on target sp_ctrl:
sp_ctrl>stop launcher
B: If deploying the SharePlex command “repair using”
The “repair using” command also uses the config file containing the schema to be sync’d as does the “copy using”. Let us assume that we create the config file named config_file_name that has the tables from that schema. You can then sync the target schema by:
1.On source sp_ctrl issue the “repair using” command as below:
sp_ctrl>repair using config_file_name
2. Monitor its progress by:
sp_ctrl>compare status
OR
sp_ctrl>job status
If the schema contains large # of tables with large # of rows, then “repair using” is not the ideal choice. The options in para A above are preferred approach. On the other hand, this “repair using” does not involve any restriction of routing the schema thru a dedicated queue. Neither does it require any special steps if the two way replication is involved, something that we need to worry about if using options in para A above.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center