Creating a Reporting Instance

[Casey Dyke]

Hiya folks,

Task at hand is to create a reporting instance from an v8.0.4 beast (in
structure, not size) originating from ISAM.  We need to replicate the
main transaction tables (30 in all) + reference tables.  All will
receive I, U and D activity.  Seems to me quite an easy solution to use
simple snapshots/snapshot logs and be done with it.  
A colleague, however, questioned why we need snapshot logs and
interference w/the PROD instance.  Fair enough I guess.  The reason
behind the logs was easy to refute, but the whether we have other
non-intrusive methods didn't roll off my tongue as well as I would have
liked it to have.  Some options I came up with:

- Transportable tablespaces
- Log Miner
- Shareplex
- cloning
- RO standby 

...

The question I have is what other options have others tried/implemented?
And, for those of you w/DW experience (which I obviously do not have) -
how do you handle the incremental transactions after initial population?
Is it accepted practice to use triggers to feed scratch tables for ETL
processes?  What comes into play when restrictions on the prod instance
are in place (ie: the config cannot change)?  What sort of comparison
routines are standard for an OLTP-Datamart/DW setup?

Hope that all makes sense.  My situation is not particularly complex,
but I've no doubt others can provide handy insight.

Cheers,

Casey ...
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Casey Dyke
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Creating a Reporting Instance

[Casey Dyke]

Whoops - rereading this and I notice I state All will receive I, U and
D activity.  One responder questioned how I could do this w/RO
snapshots.  Obviously can't.  The point I meant to make is I have to
manage an initial population of data and then the changes.  Fast refresh
would be my preference - hence the logs.  So, the method I'm leaning
toward is simple RO snapshots w/fast refresh.  No updateable snapshots
required.  

There is overhead (an extra  insert at least) on the PROD instance
associated w/snapshot logs, likewise triggers if I use them.  My
question is simply what do others do to avoid this overhead (if
anything) when handling incremental DML?  I can only assume that in very
high transaction environments, the logs/trigger approach would reduce
commit time and, in turn, application performance.  Does this present
concern to some?

Any and all comments much appreciated ... and thanks Mark for pointing
out my err ...

:-)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Casey Dyke
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).