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.

DBA Activities

DBA BEST PRACTICES

One question that comes up frequently for new DBAs or under trainee DBAs are 1. what is my job? 2. when I take over a new system what should I do first? This page gives you a slight review of dba best practices, advice on what to do in certain situations. Actions are on the left, commands on the right.
What should I do today?
1. Review the alert log	Check for any errors.
2. Determine if there is block level corruption	conn / as sysdba SELECT * FROM v$database_block_corruption;
3. Verify the status of last night's backup	conn / as sysdba SELECT * FROM v$backup_corruption;
4. Look for newly invalidated objects	conn / as sysdba SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;
5. Are there any hung resumable sessions	SELECT user_id, session_id, status, suspend_time, error_number FROM dba_resumable;
6. Are there any blocked sessions	SELECT ( SELECT username FROM gv$session WHERE sid=a.sid) blocker, a.sid, ' is blocking ', ( SELECT username FROM gv$session WHERE sid=b.sid) blockee, b.sid FROM gv$lock a, gv$lock b WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;
What should I do in a new environment/system?
1. Determine the actual version of Oracle	conn / as sysdba SELECT * FROM v$version; Also run the demos under DBMS_UTILITY.DB_VERSION and DBMS_UTILITY.PORT_STRING
2. Determine what components are installed	conn / as sysdba col comp_name format a40 SELECT comp_name, version, status FROM dba_registry;
3. Determine if there are any invalid objects in the DB	conn / as sysdba SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;
4. Is the instance part of a RAC cluster	Run the demo code under DBMS_UTILITY.ACTIVE_INSTANCES
5. Is there Data Guard replication running?	conn / as sysdba SELECT protection_mode, protection_level, remote_archive, database_role, dataguard_broker, guard_status FROM v$database;
6. Is the database in ARCHIVE LOG mode?	conn / as sysdba SELECT log_mode FROM v$database;
6. Are FLASHBACK LOGS being collected?	conn / as sysdba SELECT flashback_on FROM v$database;
7. Is there supplemental logging in place?	conn / as sysdba SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database;
8. Where are the control files?	conn / as sysdba SELECT name FROM v$controlfile;
9. Where are the log files?	conn / as sysdba SELECT l.group#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, lf.status LOGFILE_STATUS FROM v$log l, v$logfile lf WHERE l.group# = lf.group# ORDER BY 1,3;
10. What are the initialization parameters?	conn / as sysdba CREATE PFILE='/home/oracle/initparams.txt' FROM memory;
11. Capture database information	conn / as sysdba SELECT dbid, name, open_mode, database_role, platform_name, FROM v$instance;
12. Capture instance information	conn / as sysdba SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state FROM v$instance;
13. Are default passwords in use?	conn / as sysdba SELECT d.username, u.account_status FROM dba_users_with_defpwd d, dba_users u WHERE d.username = u.username AND account_status = 'OPEN' ORDER BY 2,1; If default passwords are in either lock the accounts for change them: ALTER USER <user_name> ACCOUNT LOCK; ALTER USER <user_name> IDENTTIFIED BY <new_password>;
14. Is BLOCK CHANGE TRACKING enabled?	conn / as sysdba SELECT filename, status, bytes FROM v$block_change_tracking;
15. What features are being used?	Run the demo code under DBMS_FEATURE_USAGE_REPORT
16. What profiles exist and are in use?	conn / as sysdba SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; SELECT profile, resource_name, limit FROM dba_profiles ORDER BY 1,2; SELECT username, profile FROM dba_users WHERE account_status = 'OPEN' ORDER BY 1;
17. Are case sensitive passwords enabled?	SELECT name, value FROM gv$parameter WHERE (name LIKE '%sensitive%');
18. Is FORCE LOGGING enabled?	SELECT force_logging FROM v$database;
19. Is ADVANCED REWRITE in use?	SELECT owner, name FROM dba_rewrite_equivalences;