General DBA Best Practices

Day to Day

• Check Alert Logs & OS Event Logs for unusual events.
• Verify that all scheduled jobs have run successfully.
• Confirm that backups have been made and successfully saved to a secure location.
• Monitor disk space to ensure your database won't run out of disk space.
• Throughout the day, periodically monitor database performance using OEM Database control
• Use Enterprise Manager/Management to monitor and identify blocking issues.
• Keep a log of any changes you make to database, including documentation of any performance issues you identify and corrected.
• Create OEM alerts to notify you of potential problems, and have them emailed to you. Take actions as needed.
• Take some time to learn something new as a DBA to further your professional
• development.

Installation

• Go through the installation guide provided with the software, before installing.
• Always fully document installs so that your oracle server can easily be reproduced in an emergency.
• If possible, install and configure all of your Oracle server consistently, following an agreed-upon organization standard.
• For best performance of Database running under Windows, turn off any operating system services that aren't needed.
• For optimum performance, dedicate your physical servers to only running a oracle software, no other applications.
• For best I/O performance, locate the database files (.dbf) and log files (.log) on separate file system on your server to isolate potentially conflicting reads and writes.
• If temp file will be used heavily, also put it on its own separate file system.

Job Maintenance

• Avoid overlapping jobs on the same oracle server. Ideally, each job should run separately at different times.
• When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.
• Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers

Disaster Recovery

• You must create a disaster recovery plan and include every detail you will need to rebuild your servers.
• As your databases change over time, don't forget to update your disaster recovery plan.
• Write the disaster recovery plan so that any computer literate person will be able to read and follow it. Do not assume a DBA will be rebuilding the servers.
• Fully test your disaster recovery plan at least once a year.

Backup

• Whenever possible, perform a daily full backup of all databases.
• For all production databases, perform regular archive log backups, at least
  once an hour, depending on the archive logs generation per hour.
• Perform full backups during periods of low user activity in order to minimize the
  impact of backups on users.
• Periodically test backups to ensure that they are good and can be restored.
• Backup first to disk, then move to tape or some other form of backup media.

Performance Monitoring

• Regularly monitor your database for blocked transactions.
• Regularly monitor system performance using OEM Database Control. Use OEM for both real-time analysis and for historical/baseline analysis.
• If your Database Server total CPU utilization is consistently above 80% or more, you need more CPUs, faster CPUs, or you need to find a way to reduce the load on the current server.
• Don't run any applications on your oracle server, with the exception of necessary utilities as required.
• Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
• Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
• Remove indexes that are never used. Don't accidentally create redundant indexes.