Domain design for distributed databases

From: Sarah Satterthwaite <ssatterthwaite_at_cswcasa.com>
Date: Thu, 3 Mar 2005 14:50:34 -0500
Message-ID: <4FF77D01E13B214590F4FA3E2C3021D0014367C7@pecos.csw.alewife.net>

We are looking at implementing updatable snapshots at a remote site and this has raised a number of issues about how our environment is currently set up.

Environment:

Oracle 8.1.7.4 on Windows servers (with one VMS 7.3.4 still in the mix, but not involved in the replication).

	All the databases are in the .WORLD domain.
	Global naming needs to be enforced for the replication

implementation.

I am trying to decide whether we should implement multiple domains. The db servers are located at 3 different sites (only 2 of which will be involved in the replication). It appears to me that Oracle's assumptions in the way they have implemented database links and global names includes the expectation that the work is pretty well isolated within a domain. If a remote object is in the same domain, you can ignore the domain portion of the database link and let it default. If it is in a different domain you have to code the full global name someplace, if not in the code, hiding it in a view or synonym to implement the location transparency.

Some characteristics of our primary application could make maintaining all this messy:

	Table names are built dynamically based on data values. 
	The set of tables that exists slowly changes over time as the data
values change.
	There are lots of tables (~8000 in the largest database, only a

small subset of which will be replicated).

After experimenting some, a single domain is looking better and better. But will I regret it later (when it will be even harder to change)?

I am looking for guidelines on how to design the domains. What should be grouped together and why? What should be separated?

I hope some of you have wisdom to share and/or pointers to resources. Thanks!

Sarah

Sarah Satterthwaite
Database Administrator
Fiserv CSW, Inc
125 Cambridge Park Drive
Cambridge, MA 02140

Email: sarah_at_cswv.com

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 03 2005 - 14:53:52 CST