The show sync issued from target sp_ctrl lists the table as out of sync even though copy command was able to sync the table.
General information.
The copy or append command can sync the target table but it does not remove the out of sync entry for the table from the statusdb (status database) file. So even after the target table is synchronized using copy/append, the out of sync entry for that table needs to be removed manually from statusdb file by issuing the command clear status <id>; on target sp_ctrl.
Please refer to Shareplex Reference Guide for details on using this command. Not doing so will show erroneous output for the table when issuing show sync from target sp_ctrl. Currently this is the only way to correct the statusdb entry.
This behavior is different from compare command with repair option. The compare takes care of the out of sync entry from the statusdb file.
The following para details how to remove the entry (since there is a marked difference in the process in Shareplex 7.0 and up and Shareplex 6.1 or lower):
1. identify the out of sync entry for that table (say table named TABLE1 schema OWNER) by:
sp_ctrl>show sync
This will list out the out of sync tables encountered so far and will include the table in question whose entry has an ID of 908.
sp_ctrl (sebprod-splx1:2100)> show sync
Out Of Sync Status Database sebprod-splx1
Status Database:
Status Id Date Log Level Count Clear? Condition
--------- --------------- ------------ ----- --------------------------
908 08-Oct-06 12:37 Error 238 Yes Out of syncstate
Reason: Table: "OWNER"."TABLE1" (sebprod)
912 08-Oct-06 12:37 Error 1275 Yes Out of syncstate
Reason: Table: "OWNER"."TABLE2" (sebprod)
913 08-Oct-06 03:58 Error 148 Yes Out of syncstate
Reason: Table: "OWNER"."TABLE3" (sebprod)
2. Since you have corrected the out of sync condition, you may want to clear the entry in Status Database by:
sp_ctrl (sebprod-splx1:2100)>clear status 908
This would remove the out of sync entry for that table and henceforth it will not show up as out of sync when you issue "show sync" from target sp_ctrl as below:
sp_ctrl (sebprod-splx1:2100)> show sync
Out Of Sync Status Database sebprod-splx1
Status Database:
Status Id Date Log Level Count Clear? Condition
--------- --------------- ------------ ----- --------------------------
912 08-Oct-06 12:37 Error 1275 Yes Out of syncstate
Reason: Table: "OWNER"."TABLE2" (sebprod)
913 08-Oct-06 03:58 Error 148 Yes Out of syncstate
Reason: Table: "OWNER"."TABLE3" (sebprod)
1. identify the out of sync entry for that table (say table named TABLE1 schema OWNER) by:
sp_ctrl>show sync
This will list out the out of sync tables encountered so far and will include the table in question:
sp_ctrl (suntrain6:5438)> show sync
Out Of Sync status for suntrain6
Count Details
----- ------------------------------------------------------------------------
-------
1 Table "OWNER"."TABLE1" out of sync for queue irvsupu16 since 28-Jan-11 19:50:23
.
.
2. You will need to determine the statusid of the out of sync entry which you want to remove. Issue “show statusdb detail” to get that info:
sp_ctrl (suntrain6:5438)> show statusdb detail
Status Database:
Status Id Date Level Count Clear? Details
--------- --------------- ------- ----- ------ --------------------
5 28-Jan-11 19:50 Error 1 Yes Table "OWNER"."TABLE1" out of sync for queue irvsupu16 since 28-Jan-11 19:50:23 MTPoster for o.quest10g-o.orcl queue irvsupu16
In our case the statusid for the out of sync entry is 5.
3. Since you have corrected the out of sync condition, you may want to clear the out of sync entry in Status Database by:
sp_ctrl (suntrain6:5438)> clear status 5
This would remove the out of sync entry for that table and henceforth it will not show up as out of sync when you issue "show sync" from target sp_ctrl as below:
sp_ctrl (suntrain6:5438)> show sync
Out Of Sync status for suntrain6
.
.
sp_ctrl (suntrain6:5438)>
Your action of removing the entry is based on the assumption that correct re-sync procedure is used for the table and there is no out of sync condition existing for it. Also, even though you have corrected the problem and removed the corresponding entry from Status Database, the problem can again creep up and log an entry in statusdb that will require another corrective action on your part.
Currently this is the way it works in Shareplex 7.5 or lower.
