The normal practice is to run compare without "repair" option first, analyze the out of sync by examining the .sql file generated by compare and then run compare with "repair" option to fix the out of sync. (See Shareplex Reference Guide Chapter 1 titled "Using Shareplex Commands for more details). Another approach that is followed is manually repair the out of sync by applying the SQL statements that are listed in the .sql file via SQL*Plus. The resolution section covers the nuances of the latter approach.
N.A.
It is possible to follow the manual approach when correcting the sync problems on target tables, namely taking the SQL statements from the .sql file generated by compare and applying them manually via SQL*Plus or other tools subject to the following exceptions:
a. This approach will not work with tables containing CLOB, LOB, or LONG datatype.
b. If a long time elapsed after the compare was run, applying the SQL manually may fail if the contents of the affected rows changed during this period.
c. When running compare with repair option, you need not worry about stopping the Post, running the command alter session to set nls_date_format, or ensuring that the correct SQL is extracted from the .sql file since the compare command takes care of all this. Please be aware that running the SQLs incorrectly can introduce further out of sync then correcting them in the first place.
In brief the following steps are undertaken to manually correct the out of sync:
The compare command generates a .sql file in the target's $SP_SYS_VARDIR/log directory. This file can be identified by the owner and the name of the table as well as by the Unix PID of the compare process on source. View this file to get the SQL and apply it manually on the target system by:
1. Login to the target instance as the owner of the table that needs to be resynced .
sqlplus owner/password
SQL>
Or you can also login to the target instance as some other user such as system and prefix the table with its owner name when applying the sql, which will have both owner and table name.
2. Set the correct NLS_DATE_FORMAT before applying the sql
SQL> alter session set nls_date_format = "MMDDYYYYHH24MISS";
Session altered.
3. Apply SQL
for example:
SQL> DELETE from ..
It is always a good idea to run another compare without "repair" option after manually synchronizing the table so as to ensure that it worked, subject to how long it takes for this compare to finish.
