Arup Nanda has worked exclusively as an Oracle DBA for more than 13 years and won Oracle's DBA of the Year award in 2003. Based on his Collaborate '07 presentation titled "DBA Best Practices from the Field," this tip provides criteria for determining what makes a "best practice" as well as Nanda's top five best practices for the Oracle DBA.
In my view, a best practice can only be a best practice if it 1) can be justified as to why it's advantageous and 2) can be flexible to adapt to multiple situations. The following five essential best practices were derived from my 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:
- Shut down the database
- Apply patch to the Oracle Home
- Start the database
- In case of problems:
- Shut down the database
- Roll back the patch
- 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:
- Install new Oracle Home
- Apply the patch to the new Home
- Shut down the database
- Change Oracle Home to the new location
- Start the database
- In case of problems:
- Shut down the database
- Change Oracle Home to the old one
- 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:
- The downtime is significantly reduced, to one 60th of the original time.
- The risk is reduced significantly, as there is no rolling back a patch; you just go to the older version.
- You can perform a "diff" on these two homes to see what changed. You can see the differences across multiple homes as well.
- You can take several databases running on the same server to the new Oracle Home one by one.
- 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.
