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
http://durga-kar.blogspot.in/2014/04/dba-checklist-activities-of-oracle-dba.html
