Month: March 2017

Simulating an Oracle error into the alert log

In an Oracle AWS RDS instance we do not have direct access to the server and hence we could not use our regular CRON scripts. I was testing an Oracle Stored procedure to replace this functionality and needed to simulate an error in the alert log. I found the below web site

Simulating ORA-errors

(nice site with a good explanation)

and used these commands:

alter session set events '942 incident(SIMULATED_ERROR)';
drop table tablethatdoesnotexist;
alter session set events '942 trace name context off';

The alert log contained:

ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT], [942], [SIMULATED_ERROR], [], [], [], [], [], [], [], [], []
ORA-00942: table or view does not exist

This was exactly what I needed for my testing.

Unix script to monitor the alert log for errors using x$dbgalertext

A simple script to monitor the alert log for errors via x$dbgalertext rather than the text error log. This script assumes a four node RAC. The script runs on each of the four nodes via CRON. The instance name is passed in as a parameter.

Call with
Check_alert_log.ksh instance_name1/2/3/4

The script will mail out the last 24 hours of the alert log at the 15:00 hour. All other executions will only look back 90 minutes and mail only if an error is found.

#!/bin/ksh
#
#-----------------------------------------------------------------------------------------
#
# Name       : Monitor_alert_log.ksh
# Purpose    : check the alert log
#
#-----------------------------------------------------------------------------------------
export PATH=$PATH:Oracle_Home/bin
export ORACLE_BASE=/opt/oracle/product
export ORACLE_HOME=Oracle_Home

function run_sql
{
	echo "Entering function run_sql `date`"	
	sqlplus -s / as sysdba << EOF > $ReportFile
	
	  set pagesize 100
	  set linesize 200
	  set tab off
	  set wrap off
	            
        col mt              for a16 head 'Time (MT)'
        col gmt             for a16 head 'Time (GMT)'
        col problem_key     for a10 head 'Error'
        col message_text    for a40 head 'Message Text' wrap
		select        TO_CHAR(new_time(originating_timestamp,'GMT','MDT'), 'MM-DD-YYYY HH24:MI') as MT
		             ,TO_CHAR(originating_timestamp,'MM-DD-YYYY HH24:MI') as GMT
		             ,problem_key
		             ,MESSAGE_TEXT
		from          x\$dbgalertext a
		where         originating_timestamp > sysdate - interval '$look_back' minute
		  and         MESSAGE_TEXT LIKE '%ORA-%'
		order by      originating_timestamp desc
		;
    exit
EOF

	echo "Exiting  function run_sql `date`"		
}


function decide_to_email
{
	egrep -i "no rows selected" $ReportFile > /dev/null
	if  [ $? -eq 0 ]
	then
	    if  [ $mandatory_mail == "Y" ]
	    then
	        echo 'Mailing out report based on time'
	    	mail -s "Alert Log monitoring $ORACLE_SID" "Email_id" < $ReportFile
	    fi
    else
	        echo 'Mailing out report based on errors found'
	   		mail -s "Alert Log monitoring $ORACLE_SID" "Email_id" < $ReportFile
	fi
}

#
## start of script
#
sid_parm=$1
export ORACLE_SID=$sid_parm
case $ORACLE_SID in
	 instance_name1)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name1/database_name_monitor_alert_log.report
 		;;
	 instance_name2)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name2/database_name_monitor_alert_log.report
 		;;
	 instance_name3)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name3/database_name_monitor_alert_log.report
 		;;
	 instance_name4)
		export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name4/database_name_monitor_alert_log.report
 		;;
esac

echo 'Oracle base is : ' $ORACLE_BASE
echo 'Oracle home is : ' $ORACLE_HOME
echo 'Oracle SID is  : ' $ORACLE_SID
echo 'Path is        : ' $PATH
echo 'Report file is : ' $ReportFile

#
## If the hour is "15" then look back 24 hours and mail out unconditionally 
# 
TimeOfDay=`date +"%H"` if  [ $TimeOfDay == 15 ] 
	then
		mandatory_mail="Y"
		look_back=1440
	else
		mandatory_mail="N"
		look_back=90
fi		
echo 'Mandatory mail is set to   : ' $mandatory_mail
echo 'Look back period is set to : ' $look_back

#
## Call routine to run SQLs
#
echo 'Script started at ' `date`
run_sql

#
## Decide if an email is required or not # decide_to_email

#
## End of script
#
echo 'Script ended   at ' `date`

Current Date U.S. Timezone Daylight Savings and Standard time SQL

The following SQL will return a formatted DB2 version and a literal U.S. Timezone value for the current date.  The CURRENT TIMEZONE value changes between Daylight Saving time and Standard time so the SQL takes that into consideration based on the current DST start on Second Sunday of March and end on First Sunday in November.

SELECT STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),4,2),L,'0')  
       ||'.'||                                                 
       STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),6,2),L,'0')  
       AS DB2VER                                               
      ,CASE CURRENT TIMEZONE                                   
            WHEN -40000 THEN 'US/Eastern'                      
            WHEN -50000 THEN 'US/Central'                      
            WHEN -60000 THEN 'US/Mountain'                     
            WHEN -70000 THEN 'US/Pacific'                      
        END DB2TZ                                              
  FROM SYSIBM.SYSDUMMY1                                        
 WHERE CURRENT DATE BETWEEN                                    
          DATE(YEAR(CURRENT DATE)||'-03-08') +                 
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-03-08'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS                   
       AND                                                     
          DATE(DATE(YEAR(CURRENT DATE)||'-11-01') +            
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-11-01'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS - 1 DAY)          
UNION ALL                                                      
SELECT STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),4,2),L,'0')  
       ||'.'||                                                 
       STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),6,2),L,'0')  
       AS DB2VER                                               
      ,CASE CURRENT TIMEZONE                                   
            WHEN -50000 THEN 'US/Eastern'                      
            WHEN -60000 THEN 'US/Central'                      
            WHEN -70000 THEN 'US/Mountain'                     
            WHEN -80000 THEN 'US/Pacific'                      
        END DB2TZ                                              
  FROM SYSIBM.SYSDUMMY1                                        
 WHERE CURRENT DATE NOT BETWEEN                                
          DATE(YEAR(CURRENT DATE)||'-03-08') +                 
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-03-08'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS                   
       AND                                                     
          DATE(DATE(YEAR(CURRENT DATE)||'-11-01') +            
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-11-01'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS - 1 DAY)          

 

 

Use Rexx to create more flexible IF logic in JCL

Sometimes in JCL, there is a need to have a more flexible logic to see condition codes. Examples include:  day of week, day of month, hour, datacenter, etc.  The attached Rexx program gives a format to build IF logic based on anything.  We have also used it set condition codes if a task/job is running or if a task/job is not running.

REX Set JCL CC based on REXX IF (eg. day, hour, etc)

Author: Jim Poole
Mainframe database administrator at a large telecommunications company. Over 40 years experience.