DBA best practices

Hi DBA’s,

I would like to share a nice presentation which has been given by Mr.Arup Nanda ,The President of Proligence and As a recognition of his outstanding accomplishments and contributions to the user community, Oracle awarded Arup the most coveted DBA of the Year Award at Oracle open World in September 2003.

  The following five essential best practices were derived from his years of experience working with Oracle systems large and small.

#1: Multiple Oracle Homes

My favorite best practice is the one about multiple Oracle Homes. Here it how it goes. When applying a patch or a patchset, I recommend against applying to the existing Oracle Home. Instead, I suggest creating a new Oracle Home, and apply the patches there. I create the first Oracle Home at /app/oracle/db_1, for instance. When a patch comes out, I install the whole Oracle software in a different home — /app/oracle/db_2 — and then apply the patch there. During the process of installation and patch application, the database is up and running, as it runs off the home /db_1. When the outage window comes, all I have to do is to shut down Oracle; change Oracle Home to db_2 and bring the database up. If there is a problem, I can reset the Oracle Home back to the old one.

So, here is the conventional approach:
1.Shut down the database
2.Apply patch to the Oracle Home
3.Start the database In case of problems
4.Shut down the database
5.Roll back the patch
6.Start the database

Steps 2 and 6 could take as much as three hours depending on the amount of patching. The database is down during these times.

 In the new approach:
1.Install new Oracle Home
2.Apply the patch to the new Home
3.Shut down the database
4.Change Oracle Home to the new location
5.Start the database
7.In case of problems
8.Shut down the database Change Oracle Home to the old one
9.Start the database

The database is down only during steps 4 and 8, which takes a couple of minutes at the most, not hours.

 So, here are the advantages:

1.The downtime is significantly reduced, to one 60th of the original time. 2.The risk is reduced significantly, as there is no rolling back a patch; you just go to the older version.
3.You can perform a “diff” on these two homes to see what changed. You can see the differences across multiple homes as well.
4.You can take several databases running on the same server to the new Oracle Home one by one.
5.You can see the various Oracle Homes and what patch level they are on using the inventory.

The only negative part is the space consumption — you need space for two Oracle Homes. But considering a typical Oracle Home takes about 4 GB or less, this aspect of the suggestion is trivial.

 #2: Set audit trail to DB

Set the audit trail to DB by placing the parameter AUDIT_TRAIL = DB in the initialization parameter file during the database creation. Setting this parameter does not start the auditing, because an explicit AUDIT command must be given on the object. But the parameter must be set to a value other than FALSE (the default) for the command to take effect. Being a non-dynamic parameter, the database must be bounced to change the value of AUDIT_TRAIL. To save the trouble and avoid an outage, always set the value to DB, even if you never intend to audit anything. It does not break anything and you will always be ready to audit when the time comes.

#3: Don’t use .log

Don’t use .log as the extension of redo logs. Someone may run a script to remove all the log files assuming they are redundant and you will end up losing the online redo logs as well, forcing a database recovery. Instead, name them with extension “redo” or “rdo.”

#4: Preview RMAN Restore

 Preview RMAN Restore to identify all the various backup pieces that will be used in the recovery process without doing an actual recovery. This eliminates any surprises from missing pieces during an actual recovery process.

#5: Create a new Oracle user for clients running on the same server as the DB

The Oracle Database server software also contains the client piece, which allows the clients to connect to the database on the same server. But as a best practice do not use the same user or the software; use a new one. For instance, if “oracle” is the user to install Oracle software, create a new user called, say, “oraapp” and install the client-only software using that user. The user “oraapp” should not be part of the dba or the oinstall group; so this user can’t log on to the database as sysdba. Create a new group called “appgrp” and assign the user oraaap to this group. All the application users on the box should also be part of the appgrp group. This way they can use the sqlplus, sqlldr and other executables on the server, but be able to connect as sysdba. The common practice is to use the client software in the same user as the database software owner; but starting with 10.2, Oracle has changed the security policy that takes away the global execution permissions from the Oracle Home. So the only option is to let app users be part of the dba group or change the permissions on Oracle Home — both make the database extremely vulnerable.


5 thoughts on “DBA best practices

  1. For tip #1, (I’m running on AIX) I have current HOME (#1) and new HOME (#2). When I switch .env to point to #2, what items do I need to update in #2? In #2, I ran installation plus patchset plus CPUs.

    The only things I can think of:
    dbs folder
    network/admin folder (tnsnames and listener.ora)

    Anything else that needs to be changed?



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s