The "show sync" shows table as out of sync after successfully running compare with repair option on an out of sync table. (4301601)

You sync up a table that was earlier out of sync by running compareor using other means. However, when you issue "show sync" from sp_ctrl, it still lists that table as out of sync.

Any "out of syncs" that are repaired out side of compare repair (manually) will not be removed or if Compare/Repair does not remove it (as it happens when it finds the table in sync), you need to manually remove the entry for out of sync table from the Status Database.

When you first observe out of sync tables by issuing "show sync" from target sp_ctrl, you sync up the tables using any means, one of which can be compare command from source Shareplex. When you run a compare with repair option on a table, it should fix the out of sync condition barring unforeseen conditions like a Shareplex bug. This can be verified by issuing another compare without the repair option and it will tell you that the table is in sync. However, when you issue "show sync" from sp_ctrl, it still lists that table as out of sync. The reason for this is as follows. Shareplex keeps track of out of sync encountered by logging an entry in a file named statusdb (short for Status Database) which resides on target /vardir/data directory. When the out of sync condition is corrected using other means, Shareplex does not automatically delete the out of sync entry. Rather, one has to do it manually as below:

1. Shareplex 6.0 and lower versions:

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. Shareplex 7.0 and up:

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.