We want to replicate from multiple sources to a single target table which has extra field to identify the source. It illustrates the practical use of metadata so as to replicate in a data warehousing environment. The example just serves as a template and the same principles can be extended to other situations.
We want to replicate the source tables from various to a target table. It is different from CDC (Change History replication) and is not a consolidated replication either. This is how we configured and tested it:
I have alvsupl18 as source and alvsupl19 as target. I created the following table on source and target respectively:
Source:
SQL> create table test (num_col number);
Table created.
Target:
SQL> create table test (num_col number, shareplex_source_id varchar2 (10));
Table created.
The field named shareplex_source_id is the default name for the source system.
I then created and activated the following config on source where I configured a named Post queue called queue_east that will house all data coming from source named east:
sp_ctrl (alvsupl18:8648)> view config config1
datasource:o.ORA11GR2
paul86248.test paul86248.test alvsupl19:queue_east@o.ORA11GR2
I directed Post to use the metadata named east for the data that originates from source for our table test and routes thru the named queue queue_east by issuing the following commands on the target sp_ctrl:
sp_ctrl (alvsupl19:8648)> target o.ORA11GR2 queue queue_east set metadata source
sp_ctrl (alvsupl19:8648)> target o.ORA11GR2 queue queue_east set source east
sp_ctrl (alvsupl19:8648)> stop post
sp_ctrl (alvsupl19:8648)> start post
From this point on, any data that is inserted on source for the table named test will get a value “east” for the field named shareplex_source_id as the following SQL sessions show:
Source:
SQL> insert into test values (2);
1 row created.
SQL> commit;
Commit complete.
Target:
SQL> select * from test;
NUM_COL SHAREPLEX_
---------- ----------
2 east
Likewise, if I have other sources sending data to this same target table, I will have to issue additional commands for other named Post queues to have the field shareplex_source_id populated appropriately. Say, for example:
sp_ctrl (alvsupl19:8648)> target o.ORA11GR2 queue queue_west set metadata source
sp_ctrl (alvsupl19:8648)> target o.ORA11GR2 queue queue_east set source west
The set metadata command comes to our aid in all this.
