The first key architectural issue the DBA must decide is how many Oracle instances will form the data warehouse for the purpose of supporting business intelligence queries? In essence, the DBA must decide how he or she will partition the data across instances. In fact, the answer to this one question alone will do more to define the available software and hardware architectural options open to the DBA than anything else.
For example, putting the entire data warehouse all in one instance will probably require a mainframe-like platform, whereas separating subject areas across instances will permit the DBA to use lots of smaller servers. Of course, it's really how the business users need access to the data that drives this decision. If your users must have access to all the subject areas, then separation may in fact make using the warehouse less simple.
Let's agree on some terminology to assist this discussion. If we use the term "data warehouse," or "DW," let's take that to mean the entire scope of all the subject areas. If we use the term "data mart," or "DM," let's take that to mean a subset of all the subject areas. Using these terms, let's examine our Oracle architecture options.
For those building an enterprise data warehouse, the options are (shown in Figure 2-2):
Option 1? Entire DW in a single database, with a single instance, on a single server
Option 2? Entire DW in a single database, with multiple instances, on a single server
Option 3? Entire DW in a single database, with multiple instances, on multiple servers
Note that the second option does not make much sense, unless you have a very large database server with an OS that supports partitioning of the hardware. Also note that both the second and third options require the use of OPS or RAC (OPS/RAC).
For those with separate and distinct data marts, the options are (shown in Figure 2-3):
Option 1? All DMs in separate databases, with multiple instances, on a single server
Option 2? All DMs in separate databases, with multiple instances, on multiple servers
Note that the first option does not make much sense, unless you have a very large database server with an OS that supports partitioning of the hardware.
Of these database architectures, OPS/RAC is probably the least understood. In simple terms, OPS/RAC permits more than one instance (both the System Global Area [SGA] and processes) to connect to the same database (files). The instances can be on one or more heterogeneous servers; the only requirement is the ability to share one common file system.
OPS/RAC offers many potential advantages, including:
However, these advantages come with some serious costs, including:
Tougher to administer the OS
Requires use of RAW devices
Tougher to administer the database
Tougher to diagnose/tune the database
Tougher to backup/recover the database
Generates more network traffic (i.e., inter-instance pinging)
Limited maximum CPU power per DM or subject area
Smaller pool of OPS/RAC qualified OS and DBA candidates