How to recreate a replicated table on target seamlessly if it was accidently dropped on target (4296928)

How to recreate a replicated table on target seamlessly if it was accidently dropped on target

Is it possible to recreate a replicated table on target seamlessly if it was accidently dropped on target?  

The target table in replication has accidently been dropped.

The “copy” command of SharePlex can resolve this problem.

Run the “copy” command from source to recreate it on target. This will happen in seamless manner without having to quiet the source table. The copy will create the target table as well as sync it. Here are the steps:

Start launcher process on target sp_ctrl as:

sp_ctrl>start launcher

Then start copy on the source sp_ctrl as:

sp_ctrl>copy OWNER.TABLE_NAME     (assuming that the source and target tables are identical and the source table is replicating to one target only)

Check the status of the copy by periodically issuing the following on source sp_ctrl:

sp_ctrl>copy status

Once it completes, the following type of output will show up:

Job ID    : 3

PID       : 27055

Host      : alvsupu15

Started   : 15-DEC-16 16:43:30

Job Type  : Copy

Status    : Done - 1 object completed

There will be a warning message in the copy client log on target which is named as sync_clt_n*  (where n denotes the job id of the copy) but the log will also indicate that it was able to import the table into the target successfully:

10301 15/12:40:37 WARNING:SyncTableHandler:0: Table OWNER.TABLE_NAME  does not exist

10301 15/12:40:43 INFO:SyncApplication:0: Starting SYNC...

10301 15/12:40:48 INFO:SyncImportThread:1: Export is now complete

10301 15/12:40:59 INFO:SyncLogMonitor:1: Importing table "TABLE_NAME"

10301 15/12:40:59 INFO:SyncLogMonitor:1: Done importing 80 rows

10301 15/12:40:59 INFO:SyncLogMonitor:1: Import process complete: success = 1, warnings = 1

Copy is designed to recover from situation where target table became non-existent for some reason though earlier it existed and is still a part of replication.

Caution: Copy is not intended to be used in two way replication as the actions of import on target can loop back to the source and cause data integrity issues on source.

Repair cannot be used in this situation as it will error out with ORA-0942 unless a table is first created on target and repair is run on it. But copy does away with the extra step of having to create the table manually on target. The use of copy as illustrated above differs from the command “copy force” that is used to sync the objects from source to target which are not in replication.