managing multiple instances
In the world of the working Oracle DBA you may be
managing dozens of production instances at the same time.
We are in a time of massive server consolidation,
age of mainframe computing.
Back in the 1980's, the
mainframe was the rule, and now that the one-server-one-application
paradigm has proven too expensive (increased staff), they are now moving
back to the centralized architectures of their ancestors.
The early 21st century is seeing the
2nd age of mainframe
computing, a change away from the minicomputer hardware
architectures of past decades. Instead of small, independent
servers, the major hardware vendors are pushing large servers with
transparent sharing of hardware resources:
HP - With scalability up to 64
processors, the HP Integrity Superdome Server.
Sun - The largest Sun Fire
server has 72 processors, 144 threads, and runs at 1.8 GHz, and the
new "Galaxy" servers will be even more powerful.
IBM - IBM offers the IBM System
x3950, that allows processors to be added (up to 32 CPU's).
This allows Oracle shops to scale-up within the same server.
UNISYS - The UNISYS ES-7000
series offers a 32 CPU server capable of running dozens of large
There are also compelling performance
benefits to server consolidation.
||Left is an
example of a server consolidation where a set of three IBM P590
Regatta class servers (each with 18 CPU's and 128 gig of RAM)
have replaced several hundred minicomputers.
The age of
one server, one database is rapidly disappearing.
Using IBM's virtualization
tools, Oracle instances can be partitioned into logical
partitions (LPAR's), while sill maintaining the ability for
critical tasks to use all CPU resources for super-fast Oracle
Tips for managing multiple instance environments
Almost all working Oracle experts interact with their database
with OS Commands, SQL*Plus scripts and shell scripts, and only newbie's
and the incompetent use Oracle Enterprise Manager GUI.
OEM makes it easy for a neophyte to "pretend" to be
an experienced DBA. Since
it takes several years of full-time work to be familiar with the
command-line syntax, OEM has been a great favorite of posers.
Anytime I work with a DBA who can only administer a
database from OEM, I know that they are likely to belong to one of these
Raw beginners - OEM is a great
tool for beginners who have not yet learned how the command line
interface is infinitely more flexible than the limited possibilities
offered in OEM.
Posers - There is a lot of
fraud and fake resumes in the Oracle industry, and GUI tools are a
top-off that you are dealing with someone who has a very limited
background in managing large computer systems.
Non-techies - Oracle managers
with a non-technical background (no degree in Information Systems or
Computer science) love to use OEM because it allows them to do tasks
that would normally be far beyond their ability level..
seasoned DBA knows that a firm knowledge of
shell scripting, OS commands and native SQL and DDL are
indispensible for managing multiple instances.
Over the years, I've been able to effective manager
more-and more databases.
Back in the 1990's it was a chore to manage over two dozen databases,
while today, with the help of automated scripts and proactive tuning, a
DBA might have to manage over 50 instances.
Because real DBAs always use UNIX/Linux command
line interfaces, it is a challenge to always know where you are.
It's not uncommon for an Oracle DBA to make a mistake and issue
the right command to the wrong database.
It's become critical that a busy DBA have crystal
clear way to know which instance they are using!
Common techniques for customizing your command
Customize the prompts - You can customize the OS and SQL*Plus
prompt to identify the system:
prompts - Using shell scripts you can make red colored
background, warnings to the DBA that they are connected to a
production database, See Oracle colored
command line backgrounds.
conventions - Some shops will have a different schema owner name
in production, as a safeguard against accidental mix-ups.
I always put the server name, SID and file
directory path in my command line prompt.
Compare these two command line prompts:
This way, always know exactly where I am, especially when I have a
dozen command line sessions running.
profanity in schema naming conventions
Connor MacDonald has
this tip, a great way to enforce Oracle standards, while stressing
the importance of knowing which instance you are connected-to:
"Believe it or not, humour works very well...I worked at place where all production hostnames were prefixed with "bfc". I assumed for many months this an acronym relating to the company name in some way.
When I finally casually asked - someone said: bfc = "be f...ing careful", and that had been decided upon not flippantly, but at an executive meeting, for its psychological impact - because every time you saw it, you paused for a chuckle, and that pause also triggered greater care..."
This is tongue in cheek perhaps, but it's another
example of using naming conventions and best practices for managing
large numbers of instances.
16 July 2015
First, let me say that I read these pages with interest, and on
occasion they have been of help - so, thank you for that, I appreciate
I consider myself a seasoned DBA, been working with Oracle for quite
some time. I'm also the only DBA.
OEM grid control has
been nothing less than a huge lifesaver for me. I have written
thousands of lines of SQL, Perl, Shell, awk, and sed code, in order to
help me administer my databases. I still use this code on a daily
basis. Be that as it may, it is custom written code, which I know
about, but no one else does, and to be honest, if I haven't seen the
code in a few months, I have to reacquaint myself with it.
grid control provides a (mostly) stable interface and sets of
customizable controls, which I'm still learning about (there's a lot in
there). It also means that I don't have to spend my valuable time
writing reports and HTML code -- it does it for me. Moreover, I
have set up accounts for my operators and directors, they can log on and
look at whatever they want to -- they just can't touch.
also quick to let me know if there are any issues across all of my
servers, instances, application servers, etc, all by looking at a single
screen or two.
I am first to knowledge that previous versions of
OEM were less than desirable -- but the product has matured (finally).
It still has multiple issues on the fringes -- probably opened up at
least 20 SRs against the product -- but for 98% of the time, it *frees*
me, so that I can perform administrative tasks I never had time for in
Grid control is not perfect, but it's a lifesaver for
me, and my organization.
Thanks for your time,
Oracle Database Administrator