Re: Extracting only changed data

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:Ue6dnWkOOJYLWtDZRVnyug_at_bt.com...
>
>
> You might consider setting up a 10g database
> and using Change Data Capture (or a full blown
> Streams implementation) on the 9i database
> to get data into the 10g database.
>
> If you use one of the asynchronous methods,
> the 9i instance can read it's redo log file and forward
> the changes to the 10g database through the
> streams mechanisms in near real time.
>
> Mark Rittman has a recent blog about this (in the
> context of BI), and references a 'cookbook' on
> OTN that demonstrates setting up an option
> known as
> asynchronous distributed hotlog change data capture.
>
> The capture on the source database is 'invisible' to
> the application, although the overhead could be a
> few percent. In the cookbook, the work you would
> have to do at the target end is not specified - and the
> way that the standard CDC mechanism operates at the
> target is not the most efficient strategy for dealing with
> your problem; but it's a little easier than doing the whole
> think manually through streams.
>
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
>
> <joel-hammond-smith_at_hotmail.com> wrote in message
> news:1145936026.246473.240940_at_v46g2000cwv.googlegroups.com...
>>
>> We have a large (100's gigs) OLTP (v9) database which is propreitary to
>> a vendor. We would like to extract data near real time for reporting
>> when the system becomes unavailable.
>>
>> Ideally we would extract on the inserted or updated data versus pulling
>> it out all out.
>>
>> We cannot add insert/update triggers to tables but is there an
>> alternative which fulfills the same requirement.
>>
>> I would imagine it to be a 'virtual trigger' which doesnt screw up the
>> vendor schema - but tells us when data is changing in a table.
>>
>> Wishful thinking perhaps....?
>>
>> Joel
>>
>
>

Just remembered: There's a bug with 9.2 to 10g. There is a patch, but only for a couple of platforms for 9.2.0.6, and only 4 or 5 platforms for 9.2.0.7. I think it's supposed to be fixed in 9.2.0.8

Bug number 4285404 will identify the safe platforms.

You also have to work out a realistic way of getting the original table into the copy database (transportable tablespaces seems like a good idea - but needs a short period when the original is read-only). And the performance of the simple CDC apply (at the target database) isn't great.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html