Oracle DBA Activity.

Daily Activity

1. Oracle Database instance is running or not
2. Database Listener is running or not.
3. Check any session blocking the other session
4. Check the alert log for an error
5. Check is there any dbms jobs running & check the status of the same
6. Check the Top session using more Physical I/O
7. Check the number of log switch per hour
8. How_much_redo_generated_per_hour.sql
9. Run the statpack report
10. Detect lock objects
11. Check the SQL query consuming lot of resources.
12. Check the usage of SGA
13. Display database sessions using rollback segments
14. State of all the DB Block Buffer

Weekly Activity

1. Check the objects fragmented
2. Check the Chaining & Migrated Rows
3. Check the size of tables & check weather it need to partition or not
4. Check for Block corruption
5. Check the tables without PK
6. Check the tables having no Indexes
7. Check the tables having more Indexes
8. Check the tables having FK but there is no Index
9. Check the objects having the more extents
10. Check the frequently pin objects & place them in separate tablespace & in cache
11. Check the objects reload in memory many time
12. Check the free space at O/s Level
13. Check the CPU, Memory usage at O/s level define the threshold for the same.
14. Check the used & free Block at object level as well as on tablespaces.
15. Check the objects reaching to it’s Max extents
16. Check free Space in the tablespace
17. Check invalid objects of the database
18. Check open cursor not reaching to the max limit
19. Check locks not reaching to the max lock
20. Check free quota limited available of each user
21. Check I/O of each data file

Monthly Activity

1. Check the database size & compare it previous size to find the exact growth of the database
2. Find Tablespace Status, segment management, initial & Max Extents and Extent Management
3. Check location of data file also check auto extendable or not
4. Check default tablespace & temporary tablespace of each user
5. Check the Indexes which is not used yet
6. Check the Extents of each object and compare if any object extent are overridden which is define at tablespace level
7. Tablespace need coalescing
8. Check the overall database statistics
9. Trend Analysis of objects with tablespace, last analyzed, no. of Rows, Growth in days & growth in KB

Nightly Activity

10. Analyzed the objects routinely.
11. Check the Index need to Rebuild
12. Check the tablespace for respective Tables & Indexes

One Time Activity

1. Database user creation with required privileges
2. Make the portal of Oracle Predefined error with possible solution.
3. Check database startup time(if not 24X7)
4. Check location of control file
5. Check location of log file
6. Prepare the Backup strategy and test all the recovery scenario

If we together build a comprehensive list of Oracle DBA daily/weekly/monthly routine tasks, then many Oracle new DBAs like me will benefit from it.