How to configure SharePlex to write to a sql file target (4298302)

How to configure SharePlex to replicate to a sql file on target.

This article provides a step by step approach to configuring SharePlex to replicate to a sql target. In other words, the changes occurring on the source in the form of SQLs are applied to a text file on the target and not applied to the target database as would normally be the case with regular replication. This helps in recording the SQLs that would otherwise have been applied to the target database for the purpose of audit. It is possible to configure Shareplex to log the SQLs to a text file as well as apply them to the target database. It is assumed that SharePlex is only replicating to a sql file on target.

The following table named sha86.table1 is created on source:

SQL> desc sha86.table1;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 NUM_COL                                            NUMBER

The following is the config file:

datasource:o.source_SID

#source tables      target tables           routing map

sha86.table1    !file   target_server_name

Note that the source table name is specified but the target table name is not since it is same. Also note the !file that shows that the config is for replicating sql and there is no target sid but target server name called  target_server_name is specified. In our example the source sid is called source_SID.

One will need to  activate the config file on source and then run the following commands on target and then bounce Post:

sp_ctrl (target_server_name:port_number)> target x.file set format record=sql

sp_ctrl (target_server_name:port_number)> target x.file set sql legacy=yes

sp_ctrl (target_server_name:port_number)> stop post

sp_ctrl (target_server_name:port_number)> start post

From now on, if any DML is committed on source for that table, it will be written to target under /vardir/opx directory.

The Post queue on target shows up as below:

sp_ctrl (target_server_name:port_number)> qstatus

Queues Statistics for target_server_name

  Name:  target_server_name (o.source_SID-x.file) (Post queue)

    Number of messages:          0 (Age         0 min; Size          1 mb)

    Backlog (messages):          0 (Age         0 min)

Here are some sample DML executed on source table:

SQL> insert into sha86.table1 values (10);

1 row created.

SQL> insert into sha86.table1 values (11);

1 row created.

SQL> commit;

And here is the sql file created by SharePlex under the opx subdirectory under /vardir on target:

bash-3.00$ ls -ltr

.

.

-rw-rw-r--   1 oracle   spadmin      158 Jul  7 16:55 current_source_server_name.sql

The file has the name of the source server embedded in its name.

bash-3.00$ view current_ source_server_name.sql

"current_ source_server_name.sql" [Read only] 4 lines, 158 characters

-- 0000000001 7-35565 07072016125948 07072016165519

INSERT INTO SHA86.TABLE1 (NUM_COL) VALUES('10');

INSERT INTO SHA86.TABLE1 (NUM_COL) VALUES('11');

COMMIT;