Want to sync data between oracle production and QA server

[cts123]

Hello,

I am not sure whether this is the right forum, but I need help. I have SAP production Oracle database running on Oracle 8i and having close to 1.5 TB of data. And I have similar server QAS ( in different location) which is holding same data but not sync.

The data growth is not huge, it's just close to 2 GB growth per month. While doing QAS refresh ( data sync with production server), every month we are doing Oracle Cold backup restore.

Just to reflect 2GB of new data in database in QAS server, we are doing full cold backup restore. Is there any process I can follow to reflect the new data in SAP QAS server, without full restore? Since data transfer through WAN is not possible ( more than 1 TB), we need man power to move the TSM tapes physically from one data center to other data center for QAS refresh.

Need some solution for quick data synchronization.

Thanks a lot.

Regards,
Sam

 

[SantaMufasa]

Sam,

Sounds like you need either Oracle Replication ("rich man's" solution) or database triggers (one of several "poor man's" solutions) to keep your data synchronised real time. Oracle Replication is a very robust solution that does so much for you without your needing to implement triggers (for example) on every affected table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cts123]

Thanks Mufasa.

Oracle Replication may not be the right suggestion for this moment. Of course it's rich solution and need investment.

How can we do through trigger process? Is that like, find out the list of tables in the database which got grown up ( data inserted, updated) take the export dump of those selected tables and do ftp that dump file to that QAS server and do import after truncate those tables in QAS server?

Regards,
Sam

 

[SantaMufasa]

CTS,

For the trigger option to work, you must first do a one-time source-table/clone-table synchronisation. Once synchronised, you implement database triggers that follow this construct:

Create or Replace TRIGGER <trigger name>
Before insert or update or delete on <source table name> FOR EACH ROW
BEGIN
	if inserting then
		INSERT INTO <remote clone table>@<db link>
                       values (<column list>);
	elsif deleting then
		DELETE <remote clone table>@<db link>
                       WHERE <pk_column> =
                        :new.<pk_column>;
	elsif updating then
                <check with SantaMufasa for good
                 update procedure>;
	end if;
end;
/

This is a pretty labour-intensive start-up investment since you must create a similar trigger for every table you want to synchronise, but it is worth it to avoid massive export, imports, time-critical data inconsistencies, et cetera.


Let us know your thoughts.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hoinz]

Hello Sam,

imho a word of warning is appropriate here:
As your Oracle database is for SAP, you should be aware that you are going to risk losing SAP support, at least for your QAS, and probably also for your production system.
SAP is quite restrictive about the usage of non-supported features. For more info have a look at SAP note 105047.

You cannot use trigger-based real-time replication either.

And by the way, as Dave already pointed out:
you must create a similar trigger for every table you want to synchronise
Do you know how many tables there are in your SAP system? Typically tens of thousands.
And if you are not going to synchronise them all: It's not an easy task to find out those that have to be replicated.

Sorry for bringing that bad news ...

 

[cts123]

I appreciate everyone's suggestion. Let me take up this topic with my Manager to make the final decision. It seems till that time, we have to follow the process of manual move of tapes to that location for QAS refresh.

Thanks again.

Regards,
Sam

 

[Thargy]

cts,

don't forget streams AQ. It would seem to be ideal for what you need, and would avoid the need for triggers all over the place.

Also, since you would be using queues, the systems would be decoupled. If you write to a target using triggers, what happens to the source if either communications or the target fail? Failed transactions? A hang? Anything else nasty?

I really recommend that whatever you do, you don't make production dependent on some other system being permanently available.

Regards

Tharg

Grinding away at things Oracular