This list of procedures should be performed on a daily basis to check the status of your Oracle databases:

1. Verify all instances are up

  • Make sure the database is available. Log in to each instance and run daily reports or test scripts. Some sites may wish to automate this. Optional implementation: use Oracle Enterprise Manager's 'probe' event.

2. Look for any new alert log entries

  • Connect to each managed system.
     
  • Use 'telnet' or comparable program.
     
  • For each managed instance, go to the background dump destination, usually $ORACLE_BASE/<SID>/bdump. Make sure to look under each managed database's SID.
     
  • At the prompt, use the Unix 'tail' command to see the alert_<SID>.log, or otherwise examine the most recent entries in the file.
     
  • If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The Database Recovery Log is a text file you should create and maintain; you can record any problems you find and any actions you take there for future reference.

3. Verify DBSNMP is running

  • Log on to each managed machine to check for the 'dbsnmp' process.

    For Unix: at the command line, type:

    ps –ef | grep dbsnmp

    There should be two dbsnmp processes running. If not, restart DBSNMP.

4. Verify success of database backup

5. Verify success of database archiving to tape

6. Verify enough resources for acceptable performance

  1. Verify free space in tablespaces.

    For each instance, make sure that enough free space exists in each tablespace to handle the day's expected growth. When incoming data is stable and the average daily growth can be calculated, your minimum free space should at least equal the amount of data growth you expect during the time it will take to order, receive, and install additional disks.

    1. Go to each instance, run free.sql to check free mb in tablespaces. Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct..
       
    2. Go to each instance, run space.sql to check percentage free in tablespaces. Compare to the minimum percent free for that tablespace. Note any low-space conditions and correct.
  2. Verify rollback segment.

    1. To obtain the current status of each ONLINE or FULL rollback segment (by ID, not by name), query on the V$ROLLSTAT view.
       
    2. Status should be ONLINE, not OFFLINE or FULL, except in those cases in which you have a special rollback segment for large batch jobs whose normal status is OFFLINE..
       
    3. Optional: for each database you may have a list of rollback segment names and their expected statuses.
       
    4. For storage parameters and names of all rollback segments, query on DBA_ROLLBACK_SEGS. This view's STATUS field is less accurate than V$ROLLSTAT, however, since it lacks the PENDING OFFLINE and FULL statuses; it shows these as OFFLINE anNLINE, respectively.

  3. Identify bad growth projections.

    1. To gather daily sizing information, run daily_01.sql
       
    2. To check current extents, run nr_extents.sql
       
    3. Query current table sizing information
       
    4. Query current index sizing information
       
    5. Query growth trends

    Look for segments in the database that are running out of resources (e.g., extents) or growing at an excessive rate. You may need to adjust the storage parameters of these segments. For example, if any object has reached 200 as the number of current extents, upgrade the MAX_EXTENTS parameter in the INIT.ORA file to a value of UNLIMITED.

  4. Space-bound objects' next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database performance. If we get such objects, we first need to investigate the situation. Then we can use ALTER TABLESPACE <tablespace> COALESCE. Or add another datafile.

    Run spacebound.sql. If all is well, zero rows will be returned.

  5. Be sure to review contention for CPU, memory, network, and disk resources.

7. As a final daily requirement, keep improving your overall DBA skills by spending at least one hour a day reading your DBA manuals.