Secrets for 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, the 2nd 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 Oracle applications

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 parallel query.

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 groups:

• 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..

A seasoned DBA knows that a firm knowledge of shell scripting, OS commands and native SQL and DDL are indispensible for managing multiple instances.

What's your location?

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 prompt include:

• Customize the prompts - You can customize the OS and SQL*Plus prompt to identify the system: 

• Color 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.

• Naming 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:

root>

root: myserver:mySID:/u01/app/oracle>

This way, always know exactly where I am, especially when I have a dozen command line sessions running.

Using 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.

Related reading:

Reader comments:

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 the effort.

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.
 
OEM 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.
 
Grid is 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 the past.
 
Grid control is not perfect, but it's a lifesaver for me, and my organization. 
 
Thanks for your time,
 
Stephen Windsor
Oracle Database Administrator
Wesleyan University

---