DBA Checklist (Activities of Oracle DBA)

DBA Checklist (Activities of Oracle DBA)

Contents of this Post

1.             Daily DBA Checklist

2.             Daily Night DBA Checklist

3.             Weekly DBA Checklist

4.             Weekly Tuning DBA Checklist

5.             Monthly DBA Checklist

6.             Quarterly DBA Checklist

7.             One Time Activity DBA Checklist

Daily DBA Checklist

·                     Health check of the Database Instance and Listener.

·                     Monitor Alert log file and/or check Alert log in regular interval to solve the ORA errors.

·                     Check any session blocking the other session and oracle locks. Clear locks

·                     Check long running UNIX process

·                     Ensure that there are no DBMS_JOBS with the status of failed or broken. Also last refresh times of all running jobs should be current.

·                     Check all CRONTAB house keeping script logs

·                     Daily Tablespace utilization and growth.

·                     Rebuilding of Indexes, if bulk load of data is inserted.

·                     Check the temporary tablespace/files.

·                     Check locked and expired user in database and unlock/reset/inform to business users. Monitor user account GRACE period.

·                     Check the UNDO tablespace and retention.

·                     Monitor the Unix /tmp and /var location

·                     Monitor the UTL_FILE location.

·                     Monitor all Database file system or drive.

·                     Monitor Archive Log location.

·                     Verify success of database archiving to tape

·                     Monitoring Backups.

·                     Monitoring the log files, backups, database space usage and the use of system resources.

·                     Monitoring Production Database Performance

·                     Find high CPU/Memory/Physical IO consuming processes and trace the SQL/From/Report running behind the database and update to application team/users.

·                     Check OEM Agent is running Or not in each node.

·                     Verify DBSNMP is running

·                     Verify success of database backup

·                     Daily RMAN(Incremental & Cumulative)/Data Pump export backups after business hours.

·                     User Management. User Profile monitoring.

·                     Check Invalid objects and recompile.

·                     Check and monitor audit log or table for new audit entry.

·                     Monitor daily failed login attempt in database and update to respective end uses.

·                     Backup your CRONTAB or Windows job scheduler

·                     Most Important - read DBA manuals for one hour daily.

·                     Most Important - Check your oracle license and do not run/execute/create anything beyond the oracle license policy.

Daily Night DBA Checklist

·                     Look for objects that break rules (Check for Huge NEXT_EXTENT or MAX_EXTENT)

·                     Check the objects reaching to it’s Max extents

·                     Note, All tables should have unique primary keys, so check missing/disabled PK and

·                     Check for Block corruption

Weekly DBA Checklist

·                     Database growth comparison.

·                     Identify bad growth projections.

·                     Monitor weekly report of RMAN full database backup and incremental backups.

·                     Manage weekly cold backup during maintenance windows.

·                     Analyse database and schemas to gather statistics

·                     Check index monitoring usage to validate the index usage which is not used yet. Drop/Mark unusable the unused indexes with proper change management.

·                     Make sure all indexes should use INDEXES tablespace and should not use DATA/Other tablespace. If so, then move all indexes to its Index Tablespace.

·                     All index data file should not be in same file system where the DATA tablespace or SYSTEM/SYSAUX/UNDO/TEMP datafiles are. Move and manage this to fix the IO performance.

·                     Look in SQL*Net logs for errors, issues (Both in Client side & Server side)

·                     Archive and compress all Alert Logs and application log to history location.

·                     Archive or delete trace files from diagnostic destination.

·                     Archive and compress audit files to a separate location.

·                     Archive or delete listener log file.

·                     Check the number of log switch per hour/day/week. Generate analysis report and resize you file system/drive. 

·                     Check how much redo generated per hour

·                     Check free quota limited available of each user

·                     Truncate the listener.log file in the $ORACLE_HOME/network/log, if the listener log has increased to a size > than 500 MB. Ensure the space is released, otherwise 'reload' listener.

Weekly Tuning DBA Checklist

·                     Check the Chaining & Migrated Rows

·                     Check the size of tables & check weather it need to partition or not

·                     Check the objects having the more extents

·                     Check the tables having FK but there is no Index

·                     Check the tables having no Indexes and tables having more Indexes

·                     Check the frequently pin objects & place them in separate tablespace & in cache

·                     Check the objects reload in memory many time

·                     Check open cursor not reaching to the max limit

·                     Check locks not reaching to the max lock

·                     Check I/O of each data file

Monthly DBA Checklist

·                     Index Rebuild.

·                     Tablespace reorganization.

·                     Bounce critical database once a month (If no cold backup configured)

·                     Look for Harmful Growth Rates

·                     Review database file activity.  Compare to past output to identify trends that could lead to possible contention.

·                     Investigate fragmentation (e.g. row chaining, etc.).

·                     Check location of data file also check auto extendable or not

·                     Check default tablespace & temporary tablespace of each user

·                     Check the Extents of each object and compare if any object extent are overridden which is define at tablespace level

·                     Tablespace need coalescing

·                     Check the overall database statistics

·                     Trend analysis of objects with tablespace, last analysed, no. of Rows, Growth in days & growth in KB

Quarterly DBA Checklist

·                     Patching

·                     Database Reorganization

·                     Check the quota of non-system tables in system tablespace.

·                     Bounce most critical database once a month (If no cold backup configured)

·                     Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management

One Time Activity DBA Checklist

·                     Database user creation with required privileges

·                     Make the portal of Oracle Predefined error with possible solution.

·                     Check database start-up time(if not 24X7)

·                     Check location of control file

·                     Check location of log file

·                     Prepare the Backup strategy and test all the recovery scenario