how to disable one object in shareplex replication ?

I’ll  try in rubytw first. Please review my steps. thanks.

Only on target.

1. set param SP_OPO_DISABLE_OBJECT_NUM 45663

start post queue QWIP

2. check post queue if normal

3. stop post queue QWIP

4. set param SP_OPO_DISABLE_OBJECT_NUM 0

5. start post queue QWIP

6. repair that table

reset param SP_OPO_DEBUG_FLAG queue QOE

===

      RUBYTW post queue down(fixing)

    Riley, please provide the action plan which was done by Quest Support Anu, thanks

what quest did:

1. remove some problematic message using qview

   (command like: oseek, seekback, oread, rrls...etc)

2. enter ./sp_ctrl, set param eterto disable issued table

   sp_ctrl> set param SP_OPO_DISABLE_OBJECT_NUM 45663(this is object id from source)

  

3. start post and see if backlog is decreasing.

Becasue we did not really understand the meaning of those qview's commands. So maybe we should ask quest's help if issue happens again.

But as quest said that is horizontal partition bug, and has already provided one-off patch to us.

We will try to apply it on source shareplex to prevent this issue.  Thanks.

====

The procedure for disabling a single object is as follows:

SQL>select owner,object_id from dba_objects where object_name='RPT03_RESULT'

Obtain the Oracle object_id of the object from the SOURCE system, then on the target configure SP_OPO_DISABLE_OBJECT_NUM as:

sp_ctrl> set param SP_OPO_DISABLE_OBJECT_NUM

sp_ctrl> stop post

sp_ctrl> start post

To set this back to normal:

sp_ctrl> set param SP_OPO_DISABLE_OBJECT_NUM 0

sp_ctrl> stop post

sp_ctrl> start post

remove none use shareplex queue file (in this case. QMTL is the problem shareplex queue)

Regarding the procedure. It's still the same, but I just add detail steps for steps 8.

1. run qstatus and check and make sure backlog is zero for QMTL export queue then stop export  and shutdown sp_cop sp_ctrl> qstatus ' make sure all backlog is 0 sp_ctrl> flush o. sp_ctrl> stop capture sp_ctrl> stop read sp_ctrl> stop export

(targets) sp_ctrl> show  ' check target post queues are stopped due to flush sp_ctrl> shutdown (on source: backup all vardir files and shareplex schema) (on middle: shutdown shareplex and backup all vardir files) (on target: shutdown shareplex and backup all vardir files and shareplex schema) (on targer: sp_ctrl> stop post  ' don't apply anything to target in case there is really something wrong, the user can restore all those backup files and schema data)

2. Move QMTL queue files to a directory

$ cd /rim

$ mkdir QMTL

$ ls -l *QMTL* | wc -l  ' note how many files will be moved

$ mv *QMTL* QMTL

3. cd QMTL and check and verify all files moved here are related to QMTL export files, they should have +X in it.

This includes .q, .sq and all related queue data files associated with this export queue QMTL

$ cd /rim/QMTL

$ ls -l QMTL/*QMTL* | wc -l  ' make sure file count is the same

4. start sp_cop, it should not take long

$ cd /bin

$ ksh

$ nohup ./sp_cop &

$ exit

5. check to see if you still see this queue user for wrong ip

$ cd /bin

$ ./qview -i

qview> qinit

qview> list

' It should be gone

Since we are removing the QMTL export queues on source, we have to remove the associated down stream queues on intermediate server and target server.

6. Go to intermediate server and run qstatus and check to see if all messages are drained, then shutdown sp_cop over there and go to qview

sp_ctrl> shutdown

$ ./qview -i

qview> qsetup

qview> deleteq x

' it will prompt you for export queue name and delete the related export queue related to QMTL

qview> exit

7. Go to each target server and run qstatus and check to see if all messages are drained

sp_ctrl> shutdown

$ ./qview -i

qview> qsetup

qview> deleteq p

' it will prompt you for each post queue and enter n for all other post queues and enter y for the QMTL related post queues

qview> exit

backup table first

On each target, sqlplus splex/splex

truncate table shareplex_trans;

then start sp_cop there middle and target

$ cd /bin

$ ksh

$ nohup ./sp_cop &

$ exit

8. start export on source server and you should see a new export queue QMTL created and it should not have a big number of messages. When there are new messages coming, the queues for downstream servers will get created.

sp_ctrl> qstatus ' to check export queue backlog size

sp_ctrl>start export

sp_ctrl>start read

sp_ctrl>start capture

sp_ctrl> qstatus

9. If everything goes fine, apply the patch on source. First download the patch "sp_ordr-ONEOFF-ST97622"

$ cd /bin

$ ./sp_ctrl

sp_ctrl> stop read

sp_ctrl> exit

$ cd /.app-modules

$ mv sp_ordr sp_ordr.orig

$ mv sp_ordr-ONEOFF-ST97622 sp_ordr

' keep same permission

$ cd ../bin

$ ./sp_ctrl

sp_ctrl> start read

sp_ctrl> exit

10. Re-enable the previously disabled object that had invalid length issue and see if this one-off help resolve the issue.

sp_ctrl> stop post

sp_ctrl> reset param SP_OPO_DISABLE_OBJECT_NUM

sp_ctrl> reset param SP_OPO_DISABLE_OBJECT_NUM queue QWIP

sp_ctrl> start post

how to disable DDL replication on Shareplex7 ?

list param all capture
if you see live. mean it is a live parameter.


====
1.For shareplex 7.x,  "alter table move" DDL is replicated to target by default.
From source,  
sp_ctrl> set SP_OCT_REPLICATE_DDL from 3 to 0, can disable the DDL replication.

2.All three "alter table" ddls  has replicated to target,   two of them got error due to parallel execution.
cd /log
grep ORA-  event*
grep -i alter *ddl*

3.You can set the following parameter in target, to keep post running run when DDL error.
Sp_ctrl>set param SP_OPO_STOP_ON_DDL_ERR 0