BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Real-Time Check for Oracle Problems

Oracle Tips by Burleson Consulting

The oracheck.run script is usually scheduled to run hourly in a production environment to report on any exception condition that may jeopardize the database. The script is quite sophisticated and contains four parameter files that control the level of reporting.

These are the parameter files for the script:

  • parm_mount_point_kb_free.oraThis file contains the threshold for any Oracle mount point. If you are using tablespaces with AUTOEXTEND ON, you must constantly monitor the UNIX mount points to prevent Oracle from hanging on a failure -to-extend problem.

  • parm_ts_free.oraThis file contains the threshold for reporting on full tablespaces.

  • parm_num_extents.oraThis file contains the number that the extents for a table or index may not exceed. For example, placing 600 in this file causes the Remote DBA to receive an e-mail message when any object exceeds 600 extents.

  • parm_alert_log.oraThis file contains alert log messages that should be reported to the Remote DBA. A common list for the file looks like this:

>cat parm_alert_log.ora
ORA-00600
ORA-1631
ORA-1650
ORA-1652
ORA-1653
ORA-00447
ORA-00603
ORA-01092
ORA-02050
ORA-1535

In addition to the parameter files, we have a Korn shell script called oracheck.run that controls the overall execution. The Korn shell script reports on anything that might cause the database to hang up or crash. The idea behind the script is to allow the Remote DBA to repair impending problems before the database crashes. Here are the checks performed by the script:
 

Alert log messages The script e-mails any alert log messages that are found in the alert log. The parameter file parm_alert_log.ora contains a list of alert log messages to be reported.

Low free space in archived redo log directory If the archived redo log directory becomes full, the Oracle database will hang up. This alert allows the Oracle Remote DBA to add space before the database hangs.

UNIX mount point space alert The script checks all datafile mount points in Oracle, including the UNIX Oracle home directory. Because most databases now use AUTOEXTEND ON, the Remote DBA must constantly be alert for file systems that may not be able to extend. If the free space in any mount point is less than that specified in parm_mount_point_kb_free.ora, an e-mail alert will be sent to the Remote DBA.

Object cannot extend This report alerts the Oracle Remote DBA whenever an Oracle table or index lacks room to take another extent. The alert is obsolete if you are using tablespaces with AUTOEXTEND ON, but many Remote DBAs still keep this alert because they want to monitor the growth of the database tables and indexes.

Tablespace < nn% free This report sends an e-mail alert whenever any tablespaces contain less space than specified by parm_ts_free.ora. Again, this alert is obsolete when using AUTOEXTEND ON, but many Remote DBAs still want to see the available space within each tablespace.

Object > nnn extents This report is very useful for reporting tables and indexes that experience unexpected growth. Whenever a table or index exceeds the number defined in parm_num_extents.ora, an e-mail alert is sent to the Remote DBA.

Here is an actual sample of the e-mail output from the oracheck.run script:

NON-EMERGENCY ORACLE ALERT. Mount point /home has less than 250000 K-Bytes free.

Next, letís look at a weekly object report that can tell the Remote DBA of the changes to the database in the past week.

Weekly Object Growth Report

The Weekly Object Growth report uses the STATSPACK extension tables for objects to prepare weekly growth reports. For details on collecting the statistics, see Chapter 10.

The rpt_object_stats.sql script is a very useful report that approximates the overall growth of the database over the past week. The Remote DBA can quickly compare table and index counts, and see the total growth for table and indexes over the past week. The report is often e-mailed to MIS managers. Letís take a closer look at each section.

Elapsed-Time Section

The first section of the report identifies the snapshots that are used in the comparison. The script identifies the most recent snapshot and compares it to the n Ė 1 snapshot.

SQL> @/export/home/oracle/obj_stat/rpt_object_stats
Connected.
'*********************************************'

Mon Jan 22                                                        page    1
                                 Object growth
                          Comparing last two snapshots

  Most recent date 2001-01-22

Mon Jan 22                                                        page    1
                                  Object growth
                          Comparing last two snapshots

  Older date 2001-01-08
                                                        
'*********************************************'

The next section shows the total counts of tables and indexes in the database. This is a very useful report for the Remote DBA to ensure that no new objects have migrated into the production environment. We also see the total bytes for all tables and indexes and the size change over the past week.

This report shows the total growth of tables and indexes for the past week.


Mon Jan 22                                                             page    1
                  Most recent database object counts and sizes

DB_NAME TAB_COUNT IDX_COUNT       TAB_BYTES        IDX_BYTES                
---------------- --------- ---------------- ----------------                
prodb12      451       674      330,219,520      242,204,672                
        -------- --------- ---------------- ----------------                
Total        451       674      330,219,520      242,204,672                 

Mon Jan 22                                                             page    1
                              Database size change
                    comparing the most recent snapshot dates

DB_NAME       OLD_BYTES        NEW_BYTES           CHANGE                   
--------- ------------- ---------------- ----------------                   
prodb12     467,419,136      572,424,192      105,005,056                   
          ------------- ---------------- ----------------                   
Total       467,419,136      572,424,192      105,005,056                 

The next section is primarily for the Remote DBA. It reports on any tables that contain excessive chained rows, where the table has no RAW, LONG RAW, or BLOB type columns. These row chaining problems can generally be resolved by a table reorganization.

Mon Jan 22                                                             page    1
                    comparing the most recent snapshot dates

Tables with > 10% chained rows and no LONG columns.              
Owner     Table        PCTFREE PCTUSED avg row    Rows  Chains  Pct  
--------- ------------ ------- ------- ------- ------- ------- ----  
OE        SO_OBJECTS        10      70   1,858      87      64  .74  
INV       MTL_ABC_COMP      10      70      73     367      44  .12  
APPLSYS   FND_PERFORMA      10      40  27,152      20      19  .99    
      

Next we see the chained rows for tables with LONG type column values. These tables cannot be repaired with reorganization, because the row length often exceeds the database block size. This is especially true when RAW data columns are stored inline, inside the actual table rows.

Mon Jan 22                                                             page    1
                    comparing the most recent snapshot dates

Tables with > 10% chained rows that contain LONG columns.                
Owner     Table        PCTFREE PCTUSED avg row   Rows       Chains  Pct  
--------- ------------ ------- ------- ------- ------ ------------ ----  
EUL_MWC   DIS_DOCS          10      40 23,912      9            9  100    

Next we see a report showing all tables with more than 200 extents, or tables that have extended over the past week. While excessive extents for a table is not a cause for concern, the report tells the Remote DBA about critical tables that are growing. The report helps the Remote DBA plan for tablespace growth.

Mon Jan 22                                                             page    1
                              Table extents report
                  Where extents > 200 or table extent changed
                        comparing most recent snapshots

DB         OWNER      TAB_NAME                     OLD_EXT    NEW_EXT     
---------- ---------- ------------------------- ---------- ----------     
prodb1     WOMP       REFERER_LOG                        2          1     
           WOMP       JANET_SITE_STATISTICS              1          2     
           WOMP       EC_CUSTOMER_SERVICE_ACTIONS        1          3     
           ORACLE     TOM_ENTRY                          2          5     
           WOMP       SEC_BROWSER_PROPERTIES             1          8     
           WOMP       SEC_SESSIONS                       1         10     
           WOMP       EC_USER_SESSIONS                   1         12     
           ORACLE     PAGE_IM3                           6         12     
           WOMP       EC_PRODUCTS_AUDIT                  5         14     
           ORACLE     SQLTEMPO                           1         17     
           ORACLE     PAGE_IMORP                        66        125     

Mon Jan 22                                                             page    1
                              Index extents report
                  Where extents > 200 or index extent changed
                          Comparing last two snapshots

DB         OWNER      IDX_NAME                     OLD_EXT    NEW_EXT     
---------- ---------- ------------------------- ---------- ----------     
prodb1     ORACLE     ISBN_TOC_SEQ_IDX                   1          2     
           WOMP       ROV_STAT_PAGE_TYPE_IDX             1          2     
           WOMP       SYS_C006210                        1          3     
           ORACLE     SYS_IL0000005970C00007$$           1          3     
           ORACLE     SEQ_KEY_IDX                        3          5   

Next, letís look at other miscellaneous reports that are helpful for the Remote DBA.

Trace Alert Report

Trace Alert is a great script for instantly notifying the Remote DBA and developers of the presence of trace files. In a production environment, the script can be used to alert the Remote DBA to production aborts, and it is also useful in development environments, where developers can be e-mailed their trace file dumps when a program aborts. This script is generally executed every five minutes.

The trace_alert.ksh script interrogates the Oracle datafile systems to find the locations of all trace and dump files. It then checks those directories and e-mails any trace files to the appropriate staff member. Letís take a close look at the steps in the script.

Set the Environment

The first part of the script ensures that a valid ORACLE_SID is passed to the script:

#!/bin/ksh

#******************************************************
# Exit if no first parameter $1 is passed to script
#******************************************************
if [ -z "$1" ]
then
   echo "Usage: trace_alert.ksh <ORACLE_SID>"
   exit 99
fi

#******************************************************
# First, we must set the environment . . . .
#******************************************************
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
ORACLE_BASE=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`
export ORACLE_BASE
export Remote DBA=$ORACLE_BASE/admin;
export Remote DBA
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/mon`
export MON

Get Environment Information

Next, we get the name of the database server and the current date:

#******************************************************
# Get the server name & date for the e-mail message
#******************************************************
SERVER=`uname -a|awk '{print $2}'`

MYDATE=`date +"%m/%d %H:%M"`


#******************************************************
# Remove the old file list
#******************************************************
rm -f /tmp/trace_list.lst
touch /tmp/trace_list.lst

Get the Names of Any Recent Trace or Dump Files

This section issues the UNIX find command to locate any Oracle trace or dump files that were created in the past day:

#******************************************************
# list the full-names of all possible dump files . . . .
#******************************************************
find $Remote DBA/$ORACLE_SID/bdump/*.trc   -mtime -1 -print >>  /tmp/trace_list.lst
find $Remote DBA/$ORACLE_SID/udump/*.trc   -mtime -1 -print >> /tmp/trace_list.lst
find $ORACLE_HOME/rdbms/log/*.trc   -mtime -1 -print >> /tmp/trace_list.lst

Exit Immediately If No Files Found

This section exits right away if there are no files to e-mail to the Remote DBA and developers:

#******************************************************
# Exit if no trace files are found
#******************************************************
NUM_TRACE=`cat /tmp/trace_list.lst|wc -l`
oracle_num=`expr $NUM_TRACE`
if [ $oracle_num -lt 1 ]
 then
 exit 0
fi

#echo $NUM_TRACE files found
#cat /tmp/trace_list.lst

E-mail the Trace Files

This section of the code extracts the first 100 lines of each trace and dump file and e-mails them to the Remote DBA and developer staff.

#******************************************************
# for each trace file found, send Remote DBA an e-mail message
#  and move the trace file to the /tmp directory
#******************************************************
cat /tmp/trace_list.lst|while read TRACE_FILE
do

   #***************************************************
   #  This gets the short file name at the end of the full path
   #***************************************************
   SHORT_TRACE_FILE_NAME=`echo $TRACE_FILE|awk -F"/" '{ print $NF }'`
   #***************************************************
   #  This gets the file location (bdump, udump, log)
   #***************************************************
   DUMP_LOC=`echo $TRACE_FILE|awk -F"/" '{ print $(NF-1) }'`

   #***************************************************
   # send an e-mail to the administrator
   #***************************************************

   head -100 $TRACE_FILE|\
   mailx -s "$ORACLE_SID Oracle trace file at $MYDATE."\
      don@remote-Remote DBA.net\
      terry@oracle.net\
      tzu@oracle.com

Move the Trace File

The final step is to move the trace or dump file from its current location to the UNIX /tmp directory. This keeps the dump file locations from getting clogged and ensures that the trace file is periodically deleted. This is because most UNIX administrators remove files from the /tmp directory after they are seven days old.

   #***************************************************
   # Move the trace file to the /tmp directory
   # This prevents multiple messages to the developers
   # and allows the script to run every minute
   #***************************************************

   cp $TRACE_FILE /tmp/${DUMP_LOC}_${SHORT_TRACE_FILE_NAME}
   rm Ėf $TRACE_FILE

Next, letís look at a generic alert script that can be used on nondatabase servers to e-mail alerts when a program on a web server aborts.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter