Move data between two oracle database servers

[sesal]

Hi All. I am a newbie DBA. i need help/guidlines in achieving a particular task in my place of work.

I have two Oracle 10g Database servers. the production server( server A) runs on AIX 5.3 while the other (server B)runs on windows server 2003.
Server A is online realtime transactional database which i want the data in a particular Table be moved to a table with similar structure on Server B(not online realtime transactional) leaving just a month old data on server A. that is, a check is made on server A every month to move data older than one month to server B and leaving data not older than a month in the table. i guess this involves data movement and deletion.

The idea that came to my mind is to use STREAMING(replication) but to my knowlgde the server B will be updated each time there is a change in the Table in Server A. and also the deletion part is also a problem for me. will replication solve this task?

i will appreciate any help and tips i can get in completing this task.

[davey23uk]

how automatic do you want this to be?

[PAVB]

It looks more like a monthly archive and purge process to me.

1- Archive part: Move OLD data from server A to server B
2- Purge part: Purge OLD data in server A

Would you consider partitioning affected table in server A?

Partitioning will allow you to just switch OLD partition to a table in server A then transfer that table to server B and do there whatever you have to do.

Please note that switching partition will take care in a single step of both archiving and purging. No delete, no reorg needed.

[sesal]

how automatic do you want this to be?

This should run automatically like a job at the end of evry month. Data older than a month will move to server B(archive) and data less than a month remains on the production server.

[sesal]

It looks more like a monthly archive and purge process to me.

1- Archive part: Move OLD data from server A to server B
2- Purge part: Purge OLD data in server A

Would you consider partitioning affected table in server A?

Partitioning will allow you to just switch OLD partition to a table in server A then transfer that table to server B and do there whatever you have to do.

Please note that switching partition will take care in a single step of both archiving and purging. No delete, no reorg needed.

Thanks PAVB. You are correct with the first part of your reply on the archiving. thats exactly my objective.

meanwhile on the second part about partition table, the table involve here is not a partioned table. My understanding of your comment here is that if i decide to partition the table,it will serve the purpose of archiving data older than a month into server B and then purge them from server A. my question now is will there be no negative impact on the table if i decide to partition it for this purpose? this is because the table is realtime online to card payment transactions. please pardon my limited knowledge on this issue.

[PAVB]

Thanks PAVB. You are correct with the first part of your reply on the archiving. thats exactly my objective.

meanwhile on the second part about partition table, the table involve here is not a partioned table. My understanding of your comment here is that if i decide to partition the table,it will serve the purpose of archiving data older than a month into server B and then purge them from server A. my question now is will there be no negative impact on the table if i decide to partition it for this purpose? this is because the table is realtime online to card payment transactions. please pardon my limited knowledge on this issue.

No worries.

As always happens... you have to test it.

A normal table is nothing but an Oracle segment.

A partitioned table is nothing but a collection of Oracle segments - one per partition - that show up as a single logical unit.

For a second lets imagine table is partitioned by range for some date - the same date you are planning to use for archive/purge purposes - in the YYYYMM format.

New rows will go to the newest partition while old rows will be in previous month partition.

Now imagine today is Aug 1st and you want to archive/purge June leaving July alive.

The process will be to switch June partition against a "staging" empty table of the same structure... kaboom! in a single and instantaneously operation you have June data transfered to the "staging" table while the June partition is now empty.

Now you can do whatever you want with the "staging" table, exp/imp into server B, create a dblink in server B pointing to server A and reading accross it, whatever you want.

In normal conditions you shouldn't see any performance degradation, moreover since you have to run no deletes on server A table the whole process is less taxing and it wouldn't require any table reorganization to take care of fragmentation.

Just my two cents.