Ideas to Capture DB transactions?

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. Received on Wed Aug 02 2000 - 00:00:00 CDT