This is the most common question for beginners or all of us. Database is living system, every second it has doing something. As a dba we need to check some informations to keep our db up and work 7×24 without any problems.

Here is the simple task lists

Daily Activity

  • Oracle Database instance is running or not
  • Database Listener is running or not.
  • Check any session blocking the other session
  • Check the alert log for an error
  • Check is there any dbms jobs running & check the status of the same
  • Check the Top session using more Physical I/O
  • Check the number of log switch per hour
  • How_much_redo_generated_per_hour.sql
  • Run the statpack report
  • Detect lock objects
  • Check the SQL query consuming lot of resources.
  • Check the usage of SGA
  • Display database sessions using rollback segments (recomended not mandatory)
  • State of all the DB Block Buffer (recomended not mandatory)

Weekly Activity

  • Check the objects fragmented
  • Check the Chaining & Migrated Rows
  • Check the size of tables & check weather it need to partition or not
  • Check for Block corruption
  • Check the tables without PK (recomended not mandatory)
  • Check the tables having no Indexes (recomended not mandatory)
  • Check the tables having more Indexes (recomended not mandatory)
  • Check the tables having FK but there is no Index (recomended not mandatory)
  • Check the objects having the more extents
  • Check the frequently pin objects & place them in separate tablespace & in cache Check the objects reload in memory many time
  • Check the free space at O/s Level
  • Check the CPU, Memory usage at O/s level define the threshold for the same.
  • Check the used & free Block at object level as well as on tablespaces.
  • Check the objects reaching to it’s Max extents
  • Check free Space in the tablespace
  • Check invalid objects of the database
  • Check open cursor not reaching to the max limit (recomended not mandatory)
  • Check locks not reaching to the max lock (recomended not mandatory)

Monthly Activity

  • Check the database size & compare it previous size to find the exact growth of the database
  • Check the Indexes which is not used yet
  • Tablespace need coalescing or not
  • Check the overall database statistics
  • Trend Analysis of objects with tablespace, last analyzed, no. of Rows, Growth in days & growth