Conde Bond Post

Sólo existe un tipo de conocimiento, aquel que se transmite.

Recommended for you: Automatically post your Facebook Page status messages on your LinkedIn

Recommended for you: Automatically update your Android wallpaper to NASA’s image of the day

Recommended for you: Get an email if there will be rain in your area tomorrow

Recommended for you: Automatically keep your Facebook and Twitter profile pictures in sync

Recommended for you: Save photos you’re tagged in on Facebook to Dropbox

Sonet de Nadal ~ Vostra absència

Vostra absència

Balbs mots brollen en vostra absència,bon_nadal_fills_meus
bruts gargots de ploma desafinada,
erms brots que assedegats, en l’albada
es ruixen de nou de vostra essència.

És la primera llum, acaronada
pel bes dolç de qui amb persistència
tramet mar enllà i amb impaciència
el comfort d’una càlida abraçada.

Quan sons de plany d’una amarga tristesa
per creurem lluny, us assetgin el cor
no oblideu la joia de la infantesa

que és meua força per contenir el plor
menjar vital que em nodreix de vivesa
i qui mante flamant, viu vostre record.

 

Automate your Administration Tasks ~ Basic Shellscripting for DBAs

We start a serie of posts related to basic shellscripting for DBA’s, this skill is very important, since many operations your specific work environments that you do manually, you can automate significantly reduce the time you spend on this, even lead these tasks with less skills or different areas coworkers.

If you build scripts with a uniform structure, you provide a resource for others to be complete or improve your work easily. This series of posts is not intended as a guide for application development, but rather to assist you decide which is the best method to accomplish this task if you are not accustomed to using shell.

This series of posts is not intended as a guide for application development, but rather to assist you decide which is the best method for you to accomplish this task.

RECOVER ENVIRONMENT

First Approach

Our starting point is the preparation of a Recovery Plan, which we intend to create automatic backups recovery processes that we run on a scheduled via crontab , scheduler or other solution stored in an RMAN catalog. Similarly we use packages to store compactly procedures and functions in the database, in this case we will use “libraries” of shell functions to build our own shell programs… Thus, we get simplify development, organize your code in an efficient way and reuse functionalities.

In our first approach initially we use three libraries:

  • one related to managing files, directories, etc in the file system .
  • one for RMAN functionalities.
  • last one for reconfiguration of the recovered target database.

As we have said before we have to build our code uniformly for easy understanding and maintenance.

Development Guide

A simple method to begin the development of functionalities is to create the structure that we are going to develop. We divide the code into sections :

Header

Here, we include the name of the function or main program, a brief description and history of changes.Header : Here, we include the name of the function or main program, a brief description and history of changes introduced.

#!/bin/bash
# ########################################################################### #
# File : getValidBck # Author : XPA Wavecorpuscle.com #
# Description : This program queries the Recovery Catalog Database looking #
# for a Valid Backup of the Database Provided by parameter #
# from the Number of Days provided also by parameter. #
# If no days provided the function uses Sysdate -2 default #
# value.
# ########################################################################### #
# Version : 1.0 # 2014-09-10 # First Release #
# ########################################################################### #

Global variables

For example where it is located within the filesystem where temporary files or registry if needed or generated by the program are stored. When we develop the libraries will be a unique section at the begin.

. $HOME/.bash_profile
PRG=$(basename $0)
SYSTM=$(date +%Y%m%d%H%M%S)

Usage Function

Experience has shown me that every program needs a little help or user guide, do this while you develop is very useful for creating documentation “on the fly” then latter it will be very necessary.

# ########################################################################### #
function getValidBckUsage {
fnt=“getValidBckUsage”
echo


getValidBck


USAGE:


getValidBckUsage <Db Name> <Days>


Where :


<Db Name> Is the mandatory Backed up Database Name


<Days> Is the optional number of days we sustract to Sysdate.


This function retrieves from the Repository Database the Last Valid
Backup of the <Db Name> provided from Sysdate minus <Days> provided.


The function returns the string needed for RMAN functions that uses the
UNTIL DATE clause if valid backup found.


If no <Days> Provided the function uses the default 2 value.


This function uses the Applcation Global Variables


RCVUSR As Recovery Catalog Database User


RCVPWD As Recovery Catalog Database User Password


RCVSID As Recovery Catalog Database SID.


NOTE: You need to set this Variables via export before call the function
from shell if you don’t use the library version



# Check if the Usage function is properly executed.
if [ $? -ne 0 ] ; then echo “$fnt ERROR : Wrong Execution” ; fi
}

Validation

We check if it helps to use the program call is requested and if you have provided all the parameters and this has been done correctly.

# ########################################################################### #
function getValidBck {
fnt=”getValidBck”
retval=0
PRINTOUT=””
ORADBSOURCE=””
DAYS=””
# Checking if Asked for Help
if [[ $(echo $1 | tr ‘[:lower:]’ ‘[:upper:]’ | grep -E “\-H|HELP|\/\?|USAGE” | wc -l ) -gt 0 ]] ; then
getValidBckUsage ; exit 0
fi

# Parameters Validation
if [[ -z $1 ]] ; then
PRINTOUT=“$fnt ERROR : No Database Provided”
getValidBckUsage ; exit 10
else
ORADBSOURCE=$1
fi
DAYS=$2
DBDAYS=${DAYS:-2} # Default Value is Sysdate -2 Days
# Is number validation for DAYS Parameter
if [[ -z $(echo $DBDAYS | tr -d “[0-9]”) ]] ; then

 

Body

In this section will include the code that executes our program

DBQUERY=$(echo “‘”$ORADBSOURCE”‘”)
PRINTOUT=“$fnt INFO : Looking for $DBQUERY SYSDATE – $DBDAYS Valid Backups”
untilDate=$($ORACLE_HOME/bin/sqlplus -S $RCVUSR/$RCVPWD@RCVSID << EOS
SET VERIFY OFF HEADING OFF ECHO OFF
SET TRIMSPOOL ON FEEDBACK OFF
SET LINES 30 PAGES 0
SELECT ””||SUBSTR(MAX(AB.STARTTIME),1,14)||’00:00”’ AS DTIME
FROM (
select DECODE(A.STATUS,’COMPLETED’,’OK’,’ERROR’) AS STATUS,
A.DB_NAME as “Database”,
TO_CHAR(A.END_TIME,’DAY’) AS “Day”,
TO_CHAR(A.START_TIME,’DD/MM/YYYY HH24:MI:SS’) AS STARTTIME,
A.INPUT_TYPE AS “Input”,
A.OUTPUT_DEVICE_TYPE AS “Output”,
A.INPUT_BYTES_DISPLAY AS “DatabaseSize”,
A.OUTPUT_BYTES_DISPLAY
from RC_RMAN_BACKUP_JOB_DETAILS A,
(SELECT DB_NAME,INPUT_TYPE, OUTPUT_DEVICE_TYPE, MAX(START_TIME) AS START_TIME
FROM RC_RMAN_BACKUP_JOB_DETAILS
WHERE START_TIME > SYSDATE -${DBDAYS}
AND DB_NAME=${DBQUERY}
GROUP BY DB_NAME,INPUT_TYPE, OUTPUT_DEVICE_TYPE
) B
where A.DB_NAME = B.DB_NAME and A.INPUT_TYPE = B.INPUT_TYPE
and A.OUTPUT_DEVICE_TYPE= B.OUTPUT_DEVICE_TYPE and A.START_TIME = B.START_TIME
) AB
WHERE AB.Status = ‘OK’
;
EXIT;
EOS
)

if [[ $? -eq 0 ]] ; then
if [[ -z $untilDate ]] || [[ $(echo $untilDate | grep -E “TNS-|ORA-|SP2-” | wc -l) -gt 0 ]] ; then
PRINTOUT=“$fnt ERROR : $untilDate accessing to the Repository Database or No Data Found”
retval=600
else
PRINTOUT=“UNTIL DATE = $untilDate”
fi
else

PRINTOUT=“$fnt ERROR : $untilDate accessing to the Repository Database”
retval=600
fi
else

PRINTOUT=“$fnt ERROR : Parameter Days is Not a Number”
retval=10
fi
}

Finally In our case scenario, we establish a criterion for all functions return a code, and an error/log/value message.

# ########################################################################### #
getValidBck $1 $2
echo “$PRG returns $retval”
echo “$PRINTOUT”

This is a good practice to integrate them later in our program and establish control of errors in the execution flow.

Mapping (Virtual) SCSI Devices with ASM Content

Recently, we had to change the storage of a vm host in which we had an Oracle database server (single instance) with ASM.
This simple script allows to know which virtual SCSI devices contain ASM disks and also displays the linux volume.

for isd in $(find /sys/bus/scsi/devices/  | sort)
  do
    if [ -n $isd ] ; then
      if [[ $isd != "/sys/bus/scsi/devices/" ]] ; then 
        ppidv=$(echo $isd | cut -d"/" -f6 | awk -F":" '{print "SCSI ("$1":"$2") Hardk disk "$3}') # Pretty Print
        idv=$(ls -l /dev/$(ls -d $(echo $isd | sed -e "s/\:/\\\:/g")/block* 2>/dev/null | awk -F":" '{print $5}' )[0-9] | awk '{print $10}' 2>/dev/null)
        if [[ -n  $idv ]] ; then
          tmpV=$(/usr/sbin/oracleasm querydisk $idv 2>/dev/null | grep " with the label");
          if [[ $(echo "$tmpV" | grep "is not marked as an ASM disk" | wc -l ) -eq 0 ]] ; then
            if [[ ! -z $tmpV ]] ; then
              tmpStr1=$(fdisk -l | grep "Disk $(echo "$tmpV" | awk '{print $2}' | tr -d "[0-9]" | tr -d '"')" | awk '{print $3" "$4}' | tr -d ",")
              tmpStr2=$(echo "ASM Device Found : "$(echo "$tmpV" | awk '{print $2"       "$11}') | tr -d '"')
              if [[ $(echo $ppidv | grep "SCSI (0:0)" | wc -l ) -eq 0 ]]; then
                TAB="$(printf '\t')"
                echo "$ppidv $TAB $tmpStr1 $TAB $tmpStr2 " 
              fi
            fi
          fi
        fi
      fi
      idv="";        pdv="";       tmpV="";
      tmpStr1="";    tmpStr2=""
    fi
done

The output looks like this…

SCSI (1:0) Hardk disk 0     109.4 GB    ASM Device Found : /dev/sdd1 ASM_DB_DAT00
SCSI (1:0) Hardk disk 1     109.4 GB    ASM Device Found : /dev/sde1 ASM_DB_DAT01
SCSI (1:0) Hardk disk 10    109.4 GB    ASM Device Found : /dev/sdm1 ASM_DB_DAT09
SCSI (1:0) Hardk disk 11    109.4 GB    ASM Device Found : /dev/sdn1 ASM_DB_DAT10
SCSI (1:0) Hardk disk 12    109.4 GB    ASM Device Found : /dev/sdo1 ASM_DB_DAT11
SCSI (1:0) Hardk disk 13    109.4 GB    ASM Device Found : /dev/sdp1 ASM_DB_DAT12
SCSI (1:0) Hardk disk 14    109.4 GB    ASM Device Found : /dev/sdq1 ASM_DB_DAT13
SCSI (1:0) Hardk disk 15    109.4 GB    ASM Device Found : /dev/sdr1 ASM_DB_DAT14
SCSI (1:0) Hardk disk 2     109.4 GB    ASM Device Found : /dev/sdf1 ASM_DB_DAT02
SCSI (1:0) Hardk disk 3     109.4 GB    ASM Device Found : /dev/sdg1 ASM_DB_DAT03
SCSI (1:0) Hardk disk 4     109.4 GB    ASM Device Found : /dev/sdh1 ASM_DB_DAT04
SCSI (1:0) Hardk disk 5     109.4 GB    ASM Device Found : /dev/sdi1 ASM_DB_DAT05
SCSI (1:0) Hardk disk 6     109.4 GB    ASM Device Found : /dev/sdj1 ASM_DB_DAT06
SCSI (1:0) Hardk disk 8     109.4 GB    ASM Device Found : /dev/sdk1 ASM_DB_DAT07
SCSI (1:0) Hardk disk 9     109.4 GB    ASM Device Found : /dev/sdl1 ASM_DB_DAT08
SCSI (2:0) Hardk disk 0     38.3 GB     ASM Device Found : /dev/sds1 ASM_DB_TMP001
SCSI (2:0) Hardk disk 1     109.4 GB    ASM Device Found : /dev/sdt1 ASM_DB_YTS00
SCSI (2:0) Hardk disk 10    109.4 GB    ASM Device Found : /dev/sdab1 ASM_DB_YTS08
SCSI (2:0) Hardk disk 11    109.4 GB    ASM Device Found : /dev/sdac1 ASM_DB_YTS09
SCSI (2:0) Hardk disk 12    10.7 GB     ASM Device Found : /dev/sdad1 ASM_DB_LOG02
SCSI (2:0) Hardk disk 13    12.8 GB     ASM Device Found : /dev/sdae1 ASM_DB_LOG03
SCSI (2:0) Hardk disk 14    109.4 GB    ASM Device Found : /dev/sdaf1 ASM_DB_FSH00
SCSI (2:0) Hardk disk 15    109.4 GB    ASM Device Found : /dev/sdag1 ASM_DB_FSH01
SCSI (2:0) Hardk disk 2     109.4 GB    ASM Device Found : /dev/sdu1 ASM_DB_YOT01
SCSI (2:0) Hardk disk 3     109.4 GB    ASM Device Found : /dev/sdv1 ASM_DB_YOT02
SCSI (2:0) Hardk disk 4     109.4 GB    ASM Device Found : /dev/sdw1 ASM_DB_YOT03
SCSI (2:0) Hardk disk 5     109.4 GB    ASM Device Found : /dev/sdx1 ASM_DB_Y0T04
SCSI (2:0) Hardk disk 6     109.4 GB    ASM Device Found : /dev/sdy1 ASM_DB_YOT05
SCSI (2:0) Hardk disk 8     109.4 GB    ASM Device Found : /dev/sdz1 ASM_DB_YOT06
SCSI (2:0) Hardk disk 9     109.4 GB    ASM Device Found : /dev/sdaa1 ASM_DB_YOT07
SCSI (3:0) Hardk disk 1     12.8 GB     ASM Device Found : /dev/sdai1 ASM_DB_CTL01
SCSI (3:0) Hardk disk 10    109.4 GB    ASM Device Found : /dev/sdaq1 ASM_DB_DAT18
SCSI (3:0) Hardk disk 11    109.4 GB    ASM Device Found : /dev/sdar1 ASM_DB_DAT17
SCSI (3:0) Hardk disk 12    109.4 GB    ASM Device Found : /dev/sdas1 ASM_DB_DAT16
SCSI (3:0) Hardk disk 14    96.4 GB     ASM Device Found : /dev/sdau1 ASM_DB_DAT15
SCSI (3:0) Hardk disk 2     12.8 GB     ASM Device Found : /dev/sdaj1 ASM_DB_CTL02
SCSI (3:0) Hardk disk 3     12.8 GB     ASM Device Found : /dev/sdak1 ASM_DB_LOG00
SCSI (3:0) Hardk disk 4     12.8 GB     ASM Device Found : /dev/sdal1 ASM_DB_LOG01
SCSI (3:0) Hardk disk 5     12.8 GB     ASM Device Found : /dev/sdam1 ASM_DB_LOG02
SCSI (3:0) Hardk disk 6     12.8 GB     ASM Device Found : /dev/sdan1 ASM_DB_LOG03
SCSI (3:0) Hardk disk 8     12.8 GB     ASM Device Found : /dev/sdao1 ASM_DB_LOG00
SCSI (3:0) Hardk disk 9     12.8 GB     ASM Device Found : /dev/sdap1 ASM_DB_LOG01

I hope it’s helpful.

MySQL 5.6 Testing Optimization InnoDB Tables

After reading an experiment published about optimizing InnoDB, I tried to check the result during the MySQL Administration Training at Oracle I attendthis week…

We create the table a in order to test the trick for InnoDB tables optimizatio provided by Peter in this post (1) .

CREATE TABLE `a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c` char(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1

At mysqlprompt we create a function for generate random string (2)

DROP function if exists genstring;
delimiter $$
CREATE FUNCTION genstring(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
set @var:='';
while(in_strlen>0) do
set @var:=concat(@var,ELT(1+FLOOR(RAND() * 26),

'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'));
set in_strlen:=in_strlen-1;
end while;
RETURN @var;
END $$

we create a procedure to populate the table a

DROP PROCEDURE IF EXISTs InsertTableA;
DELIMITER $$
CREATE PROCEDURE InsertTableA ( IN pRecs INT)
BEGIN
SET @count=1; SET @max=pRecs;
WHILE  @count < @max  DO
SELECT genstring(ROUND((RAND() * 63)+1)) INTO @cins;
INSERT INTO a (C) VALUES (@cins);
SET @count = @count +1;
END WHILE;
END;
$$

and next we put some records to the table

CALL InsertTableA (60067);

First, we execute the direct optimization of the a InnoDB Table…

mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|    60067 |
+----------+
1 row in set (0.03 sec)
mysql>  optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2.34 sec)

And the other optimization approach as shown:

mysql> alter table a drop key c;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.96 sec)

mysql> alter table a add key(c);
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

The second optimization runs faster than the previous one, no doubt. And you see how changes the table files as shown:

[root@EDLVC2R10P5 test]# ls -tlar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 15:06 a.frm
-rw-rw---- 1 mysql mysql 22020096 Jul 24 16:44 a.ibd

[root@EDLVC2R10P5 test]# ls -tlar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 16:52 a.frm
-rw-rw---- 1 mysql mysql 14680064 Jul 24 16:53 a.ibd

We load more data in the table in order to reproduce one more realistic test scenario:

mysql> CALL InsertTableA (100230);
Query OK, 0 rows affected (8 min 38.92 sec)

mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|   160296 |
+----------+
mysql> optimize table a;
1 row in set (0.07 sec)
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (5.05 sec)

And no change in the file size in ther first optimization execution:

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 16:52 a.frm
-rw-rw---- 1 mysql mysql 46137344 Jul 24 17:10 a.ibd

The second optimization approach:

alter table a drop key c;
mysql> alter table a drop key c;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2.23 sec)

mysql> alter table a add key(c);
Query OK, 0 rows affected (4.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

And the size is half more or less prior the new optimization execution:

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql     8580 Jul 24 17:15 a.frm
-rw-rw---- 1 mysql mysql 28311552 Jul 24 17:15 a.ibd

In this new scenario we see that time execution of both optimizations are very similar.

We carry on loading more data on the table:

mysql> CALL InsertTableA (426793);
Query OK, 0 rows affected (38 min 25.12 sec)

After the data load we review the file size:

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql      8580 Jul 24 17:15 a.frm
-rw-rw---- 1 mysql mysql 134217728 Jul 24 17:57 a.ibd

And now we repeat both optimizations processes

mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|   587088 |
+----------+
1 row in set (0.28 sec)
mysql> optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (15.36 sec)

Note that the file size is increased after the optimization execution…

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql      8580 Jul 25 07:32 a.frm
-rw-rw---- 1 mysql mysql 142606336 Jul 25 07:32 a.ibd

And now, we execute the second optimization approach:

mysql> alter table a drop key c;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (7.17 sec)

mysql> alter table a add key(c);
Query OK, 0 rows affected (11.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

After review the file size wee see that no changes occurs, respect the original file size, prior the optimization execution.

[root@EDLVC2R10P5 test]# ls -ltar a.*
-rw-rw---- 1 mysql mysql      8580 Jul 25 07:35 a.frm
-rw-rw---- 1 mysql mysql 104857600 Jul 25 07:35 a.ibd

What really happens? I will continue writing about this…

NOTES:
Thinking about running OPTIMIZE on your Innodb Table ? Stop!
http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/

MySQL Generate Random String, Email Address, URL
http://ready2gosoft.com/php/mysql-generate-random-string.html

Monitoring Oracle Database for no DBAs

Here one shoddy bash script for basic basic monitoring and detect oracle databse errors:

#!/bin/bash
shopt -s extglob
oracle_unqname=$(echo $ORACLE_UNQNAME | tr '[:upper:]' '[:lower:]')
#echo "ORACLE_UNQNAME : $oracle_unqname # ORACLE_SID : $ORACLE_SID"
PINDATETIME=$(date +%Y%m%d_%H%M%S)
ORAERRORLOG="/scripts/logs/"$PINDATETIME"_ora_errors_track.log"
ERRORMSG=""
NAGIOSRET=0
SEARCHPATH=$ORACLE_BASE"/diag/rdbms/"$oracle_unqname"/"$ORACLE_SID"/trace/"$ORACLE_SID"*_ora*.trc"
case $# in
  0 ) # We find in all trace files
      for oras in $(ls -ltar $SEARCHPATH | awk '{print $9}' 2>/dev/null)
      do
      # echo "File : $oras " ;
      if [ $(cat $oras |grep "ORA-" | wc -l) -gt 0 ] ; then
        echo "" >> $ORAERRORLOG
        echo "ORA Errors Found in File $oras" >> $ORAERRORLOG
        cat $oras | grep "ORA-" -B7 -A2 >> $ORAERRORLOG
        if [ $(echo "$ERRORMSG" | wc -l ) -gt 0 ] ; then
          NEWMSG="ORA Errors Found in File $oras"
          ERRORMSG="$ERRORMSG"$'\n'"$NEWMSG"
          #echo "ERRORMSG : $ERRORMSG"
        fi
        echo "------------------------------------------------------------------" >> $ORAERRORLOG
      fi
      done
  ;;
  * ) # We find in the past n days files
      case $1 in
     # if [[ $1 = *[:digit:]]* ]]; then
        ( +([0-9]) ) # If is nummber
        dateCompute=$(echo "$1 days ago")
        echo "$dateCompute"
        dateSearch=$(echo $(date -d "$dateCompute") | awk '{print $2" "$3}')
        echo "$dateSearch"
        for oras in $(ls -ltar $SEARCHPATH | grep "$dateSearch" |awk '{print $9}' 2>/dev/null)
          do
        # echo "File : $oras " ;
          if [ $(cat $oras |grep "ORA-" | wc -l) -gt 0 ] ; then
            echo "" >> $ORAERRORLOG
            echo "ORA Errors Found in File $oras" >> $ORAERRORLOG
            cat $oras | grep "ORA-" -B7 -A2 >> $ORAERRORLOG
            if [ $(echo "$ERRORMSG" | wc -l ) -gt 0 ] ; then
              NEWMSG="ORA Errors Found in File $oras"
              ERRORMSG="$ERRORMSG"$'\n'"$NEWMSG"
            #echo "ERRORMSG : $ERRORMSG"
            fi
            echo "------------------------------------------------------------------" >> $ORAERRORLOG
          fi
        done
        ;;
        * ) # Not a Number
      #else
        ERRORMSG="ERROR : Invalid Parameter"
        NAGIOSRET=4
      #fi
        ;;
      esac
  ;;
esac

if [ -z "$ERRORMSG" ] ; then
  ERRORMSG="No ORA Errors Found in Trace Files"
  NAGIOSRET=0
else
  if [ $NAGIOSRET -gt 0 ]; then
     echo "$ERRORMSG"
  else
    NAGIOSRET=3
  fi
fi
#echo " "
exit $NAGIOSRET

I promise to update the script tha.., tha, that’s all folks!

😉

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.