How to configure SharePlex to write to an SQL Flat File Target.
This article provides a step by step guide to configuring SharePlex to replicate to an SQL Flat File Target.
In other words, the changes occurring on the Source database in the form of SQLs are applied to a text file on the Target, and not applied to a Target database as would normally be the case with regular replication.
This helps in recording the SQLs that would otherwise have been applied to a Target database, for the purpose of audit.
It is possible to configure SharePlex to log the SQLs to both a text file and a Target database.
But in this article it is assumed that SharePlex is only replicating to an SQL Flat File on Target.
The following table named user1.table1 is created on Source:
sql> desc user1.table1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM_COL NUMBER
The following is the config file:
datasource:o.source_SID
#source tables target tables routing map
user1.table1 !file <target_server_name>
Note how the Source table name is specified but there is no Target table name specified.
The !file entry indicates that the configuration is for replicating to an SQL Flat File.
There is no Target SID but the Target server name called <target_server_name> is specified.
In the above example the Source SID is called source_SID.
You will need to verify and activate the config file on Source and then run the following commands on Target, and then restart the Post queue.
sp_ctrl> target x.file set format record=sql
sp_ctrl> target x.file set sql legacy=yes
sp_ctrl> target x.file set file location=<pathname> -- where <pathname> is the location of where you wish to direct the SQL Flat File output.
sp_ctrl> stop post
sp_ctrl> show -- repeat until the status of Post displays 'stopped by user'
sp_ctrl> start post
From here on, if any DML is committed on the Source database for the user1.table1 table, it will be written to Target under the default /vardir/opx directory, or the <pathname> directory if specified as in the example above.
The Post queue on Target will display as below:
sp_ctrl> 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 the Source table:
sql> insert into user1.table1 values (10);
1 row created.
sql> insert into user1.table1 values (11);
1 row created.
sql> commit;
And here is the SQL Flat File created by SharePlex under the default /vardir/opx (or the <pathname> directory) on Target:
linux> 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 it's name.
linux> view current_ source_server_name.sql
"current_ source_server_name.sql" [Read only] 4 lines, 158 characters
-- 0000000001 7-35565 07072016125948 07072016165519
INSERT INTO USER1.TABLE1 (NUM_COL) VALUES('10');
INSERT INTO USER1.TABLE1 (NUM_COL) VALUES('11');
COMMIT;
-- EOF 0000000009
