Dawn,
I work for Wireless Generation, we make software
For hand held reading assessments for schools, so, I feel your
pain when it comes to dealing with schools :)
> 1) could we put them all into one instance and use row level security
to give access to each school?
You have 6 instances on 5 machines.
Why have you done this? For performance
as to not load one box?
Or for privacy cuz schools want their data
Separate?
The answer to that question will ultimately decide
what your best config would be.
If you've done it for security (privacy of data amongst schools),
I'd convert those instances to 30 schemas on one instance.
30 instances of oracle running on one box,
that would be out of the question.
30 schemas on one instance is easy to maintain
and there's various method for hiding data
amongst schemas/accounts.
Now, if on the other hand, you have the 5/6 configuration for
performance,
Then I'd prolly not move everything onto one
Box. Instead I'd prolly pick one of the approaches below:
1. You've discovered that the reporting db's only
Need a handful of the tables from production db.
I would modify the configuration a bit. Instead of
6 instances on 5 boxes, I would have 1 instance on each box
and 6 schemas in each instance.
then, rather than cloning over the whole prod instance
to each box every night, simply make mviews of the
tables you are interested in then do nightly fast refreshes.
So this method is cool cuz, you still have the separation of
Instances, so, performance should be about the same,
But it's less work (now only maintaining 6 instances
Not 30, but still have the logical separation of 30 schemas).
Plus, since you don't need all the tables from production,
You no longer need to clone and move a ton of data back
and forth. Simply create an mview over a db link for the
Tables you're interested in on a per schema/instance
Basis, then fast refresh at night.
2. the other option is to move all 30 instances
into 30 schemas on one instance. I imagine one
box would be too loaded so you'd want to use rac
(which is absurdly expensive and is the only reason
we are not doing rac).
As a matter of fact, we are in the same exact situation as you.
We have a prod db and reporting instances.
Depending on the reporting instance we clone either via rman
or exp/imp.
Now, because we don't need all of the data on all of the reporting
Instances we've decided to use mviews.
It's so much better and easier to maintain.
So, like I said, we are using the mview approach for "syncing"
The reporting db's with the production db and I think it's great.
It's less work and less moving pieces to deal with.
We've also been playing with CDC as well because we will
need some reporting instances that are not readonly, but we
haven't implemented it yet.
Good luck,
Anthony
-----Original Message-----
From: Dawn Syverson
[mailto:oracledba-ezmlmshield-x90813452.[Email address protected]
Sent: Thursday, July 28, 2005 10:52 AM
To: LazyDBA Discussion
Subject: where to begin
I've been given the task of researching the best possible configuration
for running 30 databases on one machine in terms of performance, etc.
What we have now is 30 database instances with 6 instances running per
machine on 5 different machines. Each instance is a replica of a
production instance for our state-wide student records system and
contains around 1625 tables with millions of rows. We have decided that
the replica (which is used for reporting and ad hoc queries) really only
needs to contain about 350 of those 1625 tables because the others
aren't used for reporting so we were hoping to move them all onto one
box.
I'm at a loss for where to begin to do the research on what the best
configuration for this set up would be - some of the questions I have
are
1) could we put them all into one instance and use row level security to
give access to each school?
2) should we up them in one instance but have one schema per school?
3) should we have 30 instances?
4) would streaming in 10g (which I know nothing about) be of benefit
etc, etc.
Does anyone have a suggestion for how one would begin to get answers to
such questions (I probably have 100 which I will spare you by not
listing here).
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page