Replication for datawarehouse

[Roshan]

Oracle Database 12.1.0.2

Hi Erman,

I am planning to make replication of table partitions from a source database to a target database for my BI database.

The partitions will be monthly partitions and each one will be loaded for their respective month, for example September's partitions will be loaded on 15th September on a target database.

The number of rows for 1 partition will be around 750,000,000.

I tried to create a view on source and insert a table's monthly partition into that view. Then I used DB link on target to fetch the partitioned data from that view. It is taking lots of time and is too slow.

I guess materialized view also will be slow.

Is there a way I can replicate the table's partition from source to target?

Many thanks,

Roshan

[ErmanArslansOracleBlog]

You can use partition-based datapmump export and imports ( expdp/impdp)

expdp blablabla tables=owner.tablename:partitionname
impdp blablabla tables=owner.tablename:partitionname  (if the table is already exists , use table_exists_action=append)

[ErmanArslansOracleBlog]

you can also use parallel=16 or 32 (according to your system) for increasing the speed of your impdp-expdp operations.

[Roshan]

Thanks Erman. Is there a way I can export partitions for only last 2 months?

[ErmanArslansOracleBlog]

Didn't you configured your partitions to be monthly?
How are they configured?

if they are already configured your partitions monthly, then you don't need to do anything. You will export your last 2 partitions and you are ok.

If you don't have that partitioning configuration, then you can use expdp's "QUERY" parameter.

[Roshan]

ok.

I created a cript to make the export run once in a month

archicom>cat exportPART.sh
d=$(date "+%d%m%Y")
read n < fileMonth.txt
echo $n
n=$(printf "%02d" "$((${n} + 1))")
echo $n
echo $(n+1)
expdp archicom/archi2017 dumpfile=dump${d}.dmp tables=MOBILE_DATA:MOBILE_DATA_2018_${n} DIRECTORY=BIPART

It's ok now :)

Many thanks,
Regards,

Roshan

[ErmanArslansOracleBlog]

Good.