Re: Ideas to Capture DB transactions?

Jeffery Cann wrote:
>
> Greetings.
>
> We have a 30 GB Oracle warehouse (8.0.6). We would like to capture DML
> operations, i.e., transactions, as new data is inserted, updated, or
> deleted in the warehouse. The warehouse stores raw data. When data is
> extracted, we apply business logic to the raw data. (We do this
> because of business requirements, so we cannot do traditional DSS
> roll-ups within the warehouse)
>
> The captured transactions would be used by a program to generate
> transactional product files - i.e., files that contain only the latest
> changes to the data in the warehouse. Note that the information we need
> to capture is not the data elements of a given record. The file
> creation process only needs the meta data to identify the transactions
> to extract.
>
> We came up with 3 ways to implement this in Oracle:
>
> 1. Put triggers on all tables in the warehouse. As DML ops occur,
> trigger fires and captures transaction in log table or Oracle Adv Queue.
> The benefit of this is that the trigger captures all transactions,
> regardless of the source. The downside is that triggers are expensive
> -- our initial tests show that a transaction captured by a trigger and
> then inserted into a log table doubles the transaction time (vs no
> trigger).
>
> 2. Scan the timestamp fields in the warehouse tables and generate a
> list of transactions. Currently, the timestamps are for a given record,
> not attributes within a record. Thus, we could only identify which
> records have a new timestamp. We would not know if record was inserted
> or updated. (We need to know whether a record was Ins or Upd for the
> output files). Also, the warehouse is not designed for logical deletes,
> so once a record is deleted, it is gone. Finally, we are concerned that
> scanning a 30 GB warehouse will be slow now and slower as it grows.
>
> 3. Scan the redo log files to capture the transactions. Oracle created
> the Log Miner interface, but their documentation does not recommend
> using it for our purpose. The benefit of using the redo log files is
> that Oracle already captures all DML ops, so why capture them twice
> using triggers? We are also looking into 3rd party software, such as
> BMC's Resolve LogMaster. The main issue with 3rd party software is that
> most of it is used for replication or recovery. We are not replicating
> the transactions that we want to capture, so replication tools (even if
> they parse the redo log files) are not a good fit.
>
> I would like to know if others have systems that capture transactions
> into a warehouse to be used for another purpose. If yes, what
> implementation (triggers, scan, 3rd party, other?) did you choose? How
> well has it worked? What would you change? What's the size of your
> db/warehouse? Do you have other suggestions?
>
> Thanks
> Jeffery Cann
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

You could stick a snapshot log on each table...This will use a (claimed to be quicker) internal trigger ...

Alternatively (with some design mods), have a sequence column on every table which you just populate with the nextval from a sequence whenever a DML occurs (via triggers)...Then you can extract your deltas in batch.

Possibly check out the use of deferred transactions (a replication facility) to see if this can help...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse