Re: suggestions for poor-man's datawarehouse performance problem

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 05 Aug 2004 00:29:48 GMT
Message-ID: <0cfQc.6645$AY5.2140@newssvr21.news.prodigy.com>

Ana C. Dent wrote:

> jhking <jhking_at_airmail.net> wrote in news:cd7ald$gk2_at_library1.airnews.net:
> 
> 

>>Environment Host: Windows 2k, Oracle 9.0.2.5. Remote Sites Windows 2k
>>Oracle 8.1.7.
>>Connection over somewhat flakey frame relay system.
>>My objective is to gather new/updated data from each remote site and
>>aggregate it on the host. The first issue is that for political reasons
>>I can't change anything on the remote sites (which kills replication or
>>triggers + queues). [...]

>
> Change Data Capture feature in 9i

If not CDC, then something similar.

Advanced Replication is still a good, mature tool for this purpose, and a business case could probably be made for allowing the routine setup required at the remote sites. But streams-based replication is the stated future direction, from what little I've heard.

One approach not yet mentioned is to mine the new/updated data from archived redo logs. For one thing, this minimizes any network problems (if one file doesn't make it across, just re-send until it does). If you had a single remote (primary) database and had no intention of including any disaster recovery needs in your scope, you could implement a logical standby database that would update one or more schemas with changes from the remote, on-demand or continuously. Some testing required for your environment, of course.

Ask how soon the remote databases can be brought up to a supported version of Oracle. Although Advanced Replication can work from 8i to 9i, logical standby requires the same major version on both ends.

--Mark Bole Received on Wed Aug 04 2004 - 19:29:48 CDT