| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Data Sourced from mainframe for Oracle application
Responses inline...
on 6/11/04 10:07 AM, Freeman Robert - IL at FREEMANR_at_tusc.com wrote:
> Hey fokls... looking for some feedback.
>
> I've got data sourced from other databases (e.g. DB2, etc..) that is comming
> into a central Oracle database. Right now, this data is fairly small, but
> the volume will be increasing quickly (amount of data and number of tables
> being replicated), and the number of applications that will be using this
> data will increase as well. Each of these applications has different data
> retention requirements (some, for example, only need the data for 10 days,
> other for 180, others for 365, and each only need subsets of the whole).
[TG]: Gee, this sounds suspiciously like a type of database that is commonly known as a "data warehouse". Is that what it is?
Let's look at how the data is being used. You mentioned that the data is "sourced" from other systems. This implies that the data in this central database will be "non-volatile" and static, as making changes to the data would cause it to be sourced in the new system. Is this accurate?
If the data is "non-volatile", then that implies that the applications running from this central database will be used for reports and analysis.
It walks like a duck, it quacks like a duck, it looks like a duck... :-)
The reason I'm making this point is because there is a huge body of literature that portrays solutions for data warehouse architecture, from operational data stores to data warehouses to data marts.
>
> Each application also has it's own application specific data as well of
> course.
[TG]: This doesn't mean that it's not a data warehouse.
Again, what type of usage will the end-users make of this new system?
Please stop me if I'm off course here -- this is sounding more and more like a DW, so I'm going to continue my comments on that assumption...
>
> So, I'm trying to decide what the best architectural option is here. The
> options as I see them are:
>
> 1. Have one big data store for the data, throw all the applications in it
> being careful to tune each one so they won't step on the other. Benefit:
> Data is only stored in one place, no physical duplication of data. This is
> not my prefered course of action, but others prefer this.
[TG]: Removing duplication is not the only purpose of consolidation. Permiting a "global view" of all of the data from the multiple sources is another purpose. The use of "virtual private database" features could also be employed to provide the separate applications with their separate "views" of the data.
List partitioning by some kind of SOURCE_ID key column allows VPD features to service each application with no degradation of performance due to interweaving the data from multiple sources in the same table.
>
> 2. Have multipule instances for the more critical applications, and
> duplicate (replicate) the data to those instances. Less important, and less
> performance impactive applications could sit on a single database, but those
> mission critical applications would sit on their own instances/disks/etc...
> the down side of this is duplication of data, and more disk space is
> required. Still from an administrative point of view this seems a better
> course of action.
[TG]: Breaking a database into multiple independent instances is often necessary because of differing availability requirements or version requirements from 3rd-party application software.
Duplication of data is not an inherently bad thing. I find it somewhat amusing that people will go to some lengths to avoid duplication of data, but then the IS department chooses to utilize mirror-splitting technology to help with backups and restores, increasing storage consumption exponentially.
>
> Any thoughts on this?
>
> Robert
[TG]: Something to keep in mind is that it is actually quite easy, using transportable tablespace features, to consolidate applications into a single database or, conversely, to segregate applications off into their own separate databases. The key is to plan this eventuality into the design of the tablespaces and "ownership" accounts in Oracle. Data common to all applications should be kept in separate tablespaces from data specific to each application, and data specific to each application should be kept in separate tablespaces from one another.
That way, if it becomes necessary to "break out" an application from a consolidate database, all that becomes necessary is to create the necessary "core" of the new database (i.e. SYSTEM, TEMP, and UNDO tablespaces) then transport the necessary copies of tablespaces to the new database core. And the same is true vice-versa.
So, as long as the tablespaces are designed this way, you can choose either route secure in the knowledge that a reversal of the decision is not difficult to implement. I'm not saying that it is trivial, but it is not difficult.
Hope this helps...
-Tim
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sat Jun 12 2004 - 02:32:24 CDT
 |
 |