Contents of this Post
Daily DBA Checklist
Daily Night DBA Checklist
Weekly DBA Checklist
Weekly Tuning DBA Checklist
Monthly DBA Checklist
Quarterly DBA Checklist
One Time Activity DBA Checklist
- Daily DBA Checklist
- Daily Night DBA Checklist
- Weekly DBA Checklist
- Weekly Tuning DBA Checklist
- Monthly DBA Checklist
- Quarterly DBA Checklist
- 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.
