DBA Checklist (DBA Activities)

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 intervals 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 housekeeping script logs
• Daily Tablespace utilization and growth.
• The rebuilding of Indexes, if a bulk load of data is inserted.
• Check the temporary tablespace/files.
• Check locked and expired users in the 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 the 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 the audit log or table for new audit entries.
• Monitor daily failed login attempts in the 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.
• Analyze 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 tablespaces. If so, then move all indexes to its Index Tablespace.
• All index data files should not be in the 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 logs to historical location.
• Archive or delete trace files from the diagnostic destination.
• Archive and compress audio files to a separate location.
• Archive or delete the listener log file.
• Check the number of log switches 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 needs 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
• 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 the 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 analyzed, no. of Rows, Growth in days & growth in KB

Quarterly DBA Checklist

• Patching
• Database Reorganization
• Check the quota of non-system tables in the 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 a possible solution.
• Check database start-up time(if not 24X7)
• Check the location and size of the control file
• Check the redo log file size and use separate location of redo log members.
• Prepare the Backup strategy and test all the recovery scenario.