Five DBA best practices

I have read a very good post of Arup Nanda and wish to share with you all. The post was regarding the best practices of DBA in day to day life. The practices are :

1. ) Multiple Oracle Homes : 

My   favorite  best  practice   is  the  one  about  multiple  Oracle  Homes. Here  it  how  it g oes. 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 i nstall 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
4.                 In case of problems:
5.                 Shut down the database
6.                 Roll back the patch
7.                 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
6.                 In case of problems:
7.                 Shut down the database
8.                 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 a nything. 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 o raaap  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  a s sysdba.

The  common  practice  is  to  use  the  client  oftware  in  the  same  user  as  the database  software  owner; but s tarting  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.

For more click here 

Enjoy   :-)