DW Setup

Thanks for the response, Don. RichG. also wondered why 2 instances. I should have included the fact these will be running on 2 different platforms as well. Apologies for the omission. Actually, I didn't include that fact because the hardware is still under discussion; we're talking to Sun about one machine that allows separate domains, each with its dedicated CPUs and memory, vs two separate machines. We are aware of the network issues, and the network folks are considering multiple channels. Procom is an Oracle-approved vendor, although I would have preferred a more popular vendor like Network Appliance or EMC, but that was management's decision ($$):

http://otn.oracle.com/deploy/availability/htdocs/vendors_nfs.html

Assuming the machine resources will be adequate, how does this setup sound?

• Original message by dgoulet_at_vicr.com Bill,

    Why use two instances? If you believe that the second instance can be protected against performance degradation during ETL processing your wrong. Both instances will be looking for the same resources (CPU & memory) and worse if your using net attached storage, they will end up competing for the same network card. OUCH!!! If you really want this type of setup, use two servers to shield one from the other. Otherwise use a single instance of Oracle which also gets away from having to have multiple copies of the data.

    Procom is not, I believe, an Oracle approved net app vendor, so beware when there's a problem. OTS may not be inclined to help you.

Dick Goulet

____________________Reply Separator____________________
Author: becker.bill_at_marshfieldclinic.org
Date:       10/14/2002 9:48 AM

Hello,

BACKGROUND:
We've been planning a 300GB datawarehouse architecture for Oracle 9.2 on Solaris, and have proposed the following: 1) 2 separate instances of Oracle 9.2,

• Instance A will be the staging instance, all ETL processing will take place here
• Instance B will be the query instance, all reporting activity will take place here 2) Once data has been transformed, copy the tablespace metadata from Instance A to Instance B using transportable tablespaces feature 3) The physical datafiles will be fast-copied from Instance A to Instance B using a vendor feature called Checkpoints (not Oracle's definition of checkpoints).

Point 3 needs further explanation: both of these instances will be connected to Network Attached Storage (NAS) from a vendor named Procom. They have a feature called Checkpoints, which quickly creates a read-only copy of a data volume (I believe this is similar to network appliance's snapshot feature, and EMC also has something like this, but the name escapes me). Checkpoints are very fast to create, and can result in a read-only copy of 200GB of data in 1 - 2 minutes. At present, we use them for backup purposes only, and they work well.

Instance A, the staging instance, will use the read-write Oracle datafiles located on the procom read-write volumes. Instance B, the query instance, will use datafiles located on a read-only procom volume, which also happens to be the checkpoint volume of the read-write volume used by Instance A. The checkpoint volume will be refreshed daily, from the staging volume, when the daily ETL stream has completed. The query instance datafiles will be dropped and re-created daily via the procom checkpoint, and the tablespace metadata will be plugged in using transportable tablespaces.

We have verified that Oracle works OK using plugged-in read only tablespaces located on a procom read-only checkpoint volume.

QUESTIONS:
(too much to hope for)

1) Is anyone else out there using this type of configuration with procom?

   If so, how well does this work? Any comments, problems?

(more realistic)

2) Is anyone else out there using a similar configuration with a comparable

   vendor feature like checkpoints? Any performance problems? Any comments, problems?

(more desperate)

3) Is anyone running a large Oracle data warehouse using primarily read-only

   tablespaces? Any comments, problems? How do you refresh them?

(last resort)

4) Does anyone care to comment on the above configuration? good idea...bad idea?

Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: becker.bill_at_marshfieldclinic.org

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).