Conde Bond Post

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

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!

😉

EM12c ConfigStateMgr:798 errors (storage_reporting_data) ~ Resolució d’Incidents

The Metalink Oracle Support associate these error messages [EM12c ConfigStateMgr:798 errors (storage_reporting_data) ] to bug 13547855 which in most cases are related to Oracle Cloud Control agents deployment into targets. Applying the procedure described in the Oracle Support document referenced can be set. However, not always like this.
You can download the english version of this post here: EM12c ConfigStateMgr798errors_storage_reporting_data – Troubleshooting

En l’entrada del bloc http://www.snapdba.com/2013/02/em12c-configstatemgr-798-errors-storage_reporting_data/ s’associa aquets missatges d’error al bug 13547855 que en la majoria dels casos estan relacionats amb el deslligament d’agents Oracle Cloud Control en un entorn. Aplicant el procediment descrit en el document de Suport d’Oracle al que fa referència es poden fixar.

De tota manera, no sempre és així, i malgrat seguir les instruccions per fixar-lo, la consola segueix reportant insistentment els missatges d’error. Si revisem el fitxer de registre on es notifica l’errada veiem:

# cat /u01/app/12cagent/agent_inst/sysman/log/gcagent_errors.log | grep "2013-09-09" 2013-09-09 13:53:19,563 [201607:GC.Executor.75083 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_keys)] ERROR - null 2013-09-09 13:53:19,564 [201607:GC.Executor.75083 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_keys)] ERROR - host:srva0d1:host_storage:storage_reporting_keys 2013-09-09 13:53:19,564 [201607:GC.Executor.75083 (host:srva0d1:host_storage)] ERROR - Critical error: 2013-09-09 13:53:19,701 [201607:GC.Executor.75083 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_alias)] ERROR - null 2013-09-09 13:53:19,702 [201607:GC.Executor.75083 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_alias)] ERROR - host:srva0d1:host_storage:storage_reporting_alias 2013-09-09 13:53:19,702 [201607:GC.Executor.75083 (host:srva0d1:host_storage)] ERROR - Critical error: 2013-09-09 13:53:20,346 [201731:oracle.dfw.impl.incident.DiagnosticsDataExtractorImpl - Incident Dump Executor (created: Mon Sep 09 13:53:19 CEST 2013)] ERROR - Result set exceeded max flood control level 2013-09-09 13:53:20,567 [201731:oracle.dfw.impl.incident.DiagnosticsDataExtractorImpl - Incident Dump Executor (created: Mon Sep 09 13:53:19 CEST 2013)] ERROR - Result set exceeded max flood control level

Darrera del vessament que provoca el resultat obtingut per la funció del programa que executa la consulta (que en una altra entrada de bloc us explicaré com fixar modificant el codi del programa perl responsable de l’incident) pot haver-hi altres errades. Per tant, seguirem el procediment descrit i comprovarem el contingut del fitxer de registre amb les errades de l’agent relacionades amb l’emmagatzematge de dades:

# cat $AGENT_HOME/agent_inst/sysman/log/gcagent_errors.log | grep storage_reporting_data 2013-09-04 13:53:19,368 [101557:GC.Executor.39108 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - null 2013-09-04 13:53:19,373 [101557:GC.Executor.39108 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - host:srva0d1:host_storage:storage_reporting_data 2013-09-05 13:53:19,554 [121731:GC.Executor.46652 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - null 2013-09-05 13:53:19,559 [121731:GC.Executor.46652 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - host:srva0d1:host_storage:storage_reporting_data 2013-09-07 13:53:19,332 [161745:GC.Executor.60863 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - null 2013-09-07 13:53:19,337 [161745:GC.Executor.60863 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - host:srva0d1:host_storage:storage_reporting_data

Si analitzem la naturalesa del missatge podem deduïr que l’agent Oracle Cloud Control no està obtenint dades d’un destí definit en la seva configuració de l’emmagatzematge:

2013-09-09 13:53:19,408 [201607:GC.Executor.75083 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - null 2013-09-09 13:53:19,412 [201607:GC.Executor.75083 (host:srva0d1:host_storage) (host:srva0d1:host_storage:storage_reporting_data)] ERROR - host:srva0d1:host_storage:storage_reporting_data

En el nostre cas, recentment s’havia fet un canvi en la configuració dels discs del servidor causant de l’incident, consultant el contingut del fitxer que es mostra tot seguit:

# cat $AGENT_HOME/agent_inst/sysman/emd/state/configstate/host/$(hostname –s)/ storage_reporting_issues.xml <DC CN="" TS="2013-09-05T13:53:20.008+02:00" TI="TARGET_GUID=D5215C2D28F0768DE043428814AC10B0" TN="srva0d1" TT="host"> RS TS="2013-09-05T13:53:20.005+02:00" MN="storage_reporting_issues"> RH> <CI NM="type">ERROR</CI> <CI NM="message_counter">10</CI> <CI NM="message_id">ERROR_INST_MAPPING</CI> <CI NM="message_params">Filesystem /dev/mapper/vg00-LV04_var</CI> <CI NM="action_id">ACTION_INST_RESOLV_ISSUE</CI> <CI NM="action_params"/> </RH>

Confirmem de que es tracta d’una errada de lectura o en l’accés a un volum, el /dev/sdd. Ho comprovem des del sistema d’arxius amb l’usuari root:

# fdisk /dev/sdd Unable to read /dev/sdd # /opt/mpp/lsvdev | grep sdd # pvscan /dev/sdd: read failed after 0 of 4096 at 0: Input/output error /dev/sdd: read failed after 0 of 4096 at 107374116864: Input/output error dev/sdd: read failed after 0 of 4096 at 107374174208: Input/output error /dev/sdd: read failed after 0 of 4096 at 4096: Input/output error PV /dev/sdb VG vg01 lvm2 [1.82 TB / 837.71 GB free] PV /dev/sda2 VG vg00 lvm2 [135.84 GB / 30.84 GB free] Total: 2 [1.95 TB] / in use: 2 [1.95 TB] / in no VG: 0 [0 ]

I efectivament, nosaltres no tenim accés al volum /sdd/ . Nosaltres hem de fixar aquest problema en la configuració del sistema operatiu o bé substituint el disc si està en mal estat.

login as: root root@ srva0d1 password: Last login: Thu Sep 12 07:50:14 2013 from 172.24.24.164 [root@ srva0d1 ~]# pvscan PV /dev/sdb VG vg01 lvm2 [1.82 TB / 837.71 GB free] PV /dev/sda2 VG vg00 lvm2 [135.84 GB / 30.84 GB free] Total: 2 [1.95 TB] / in use: 2 [1.95 TB] / in no VG: 0 [0 ]

Una vegada fet això, i seguint el procediment descrit en el document de Suport Oracle els missatges desapareixeran de la consola em12c .

Pots descarregar aquesta entrada aquí: EM12c ConfigStateMgr798errors_storage_reporting_data – resolucio incidents

Un cop de mà en matemàtiques a pares i mares de primer d’eso (I)

Encara que s’aparta un xic del que acostumo a escriure, aquesta entrada tracta de donar un cop de mà en matemàtiques a aquells p/mares que de sobte, es troben amb la necessitat d’explicar allò que fa molts anys varen estudiar (i que possiblement han oblidat) als seus fills/es que comencen l’eso. I es troben amb el problema de resoldre i explicar un problema…

El problema en qüestió ens diu que: la Carla i la Mercè viuen a la mateixa casa i van a la mateixa escola caminant. La Carla triga 40 minuts i la Mercè en triga 60. Si la Mercè surt 10 minuts abans que la Carla, quan es trobaran les dues?

Per resoldre un problema hem d’entendre l’enunciat i què ens estan demanant que resolguem.

Sovint, no acabem de veure, què és el que ens estan demanant… Així que no ens estressem i fem servir el que a algun/es de nosaltres ens deien quan érem petits/tes, el conte de la vella…

Què diu realmente l’enunciat?

Si la Carla triga 40 minuts en arribar al escola, i la Mercè 60 minuts, és que la Carla camina més de pressa. Vol dir que es desplaça a més velocitat.

Nota: La velocitat s’expressa com l’espai recorregut en un temps donat, això en termes d’equacions s’escriu així (velocitat = espai dividit pel temps):

v= e / t

on v és la velocitat, e l’espai i t el temps.

Analitzant el nostre problema, deduïm que les nostres tres variables són:

  • La velocitat de la Carla és més gran que la de la Mercè.

  • Si les dues es troben, és que ambdues han recorregut el mateix espai.

  • Quan es trobin, la Carla haurà trigat 10 minuts menys que la Mercè.

Parem compte que el problema no ens dona cap valor de l’espai que hi ha de casa a l’escola, i que ens demanen “quan” es trobaran… Temps al temps!

És a dir, quan de temps trigaran en trobar-se, no a on, ni com, ni per què…😛

Si ens fixem en l’enunciat veiem que ens donen un patró de temps: 10 minuts.

Anem a pams, i comptem amb els dits si cal…😉

Resolució per deducció, el conte de la vella

Resolem-ho amb el conte de la vella, il·lustrant l’espai recorregut per cadascuna d’elles mentre van a escola en una unitat de temps determinada (el patró):

Per anar a l’escola la Mercè necessita 6 unitats de 10 minuts (6 x 10 = 60).

imatge_01

Per anar a l’escola la Carla necessita 4 unitats de 10 minuts (4 x 10 = 40).imatge_02

Vejam quin espai recorre cadascuna, cada 10 minuts… En els primers 10 minuts la Mercè ha recorregut aquesta distància, la Carla és a casa…imatge_03

En els segons 10 minuts, la Mercè i la Carla han recorregut aquestes distàncies…imatge_04

En els tercers 10 minuts, la Mercè i la Carla han recorregut aquestes distàncies…

imatge_05

És a dir, que la Carla enxampa la Mercè als 30 minuts de que la Mercè hagi sortit de casa… I curiosament, va per una mare en concret, és efectivament a mig camí…😀

Com ho traduïm en una equació?

Es tracta de plantejar la solució del problema amb una equació de primer grau (primer d’eso), amb una única incògnita: o el temps de la Carla; o el temps de la Mercè.

L’espai no el sabem, i de fet, l’únic que ens interessa és que serà el mateix per a les dues, com hem vist abans. Sino fos així no es trobarien… Oooooi?

La velocitat tampoc la sabem, però sí que podem expressar-la en funció d’una d’elles en termes relatius, és a dir quantes vegades l’una és més ràpida que l’altra…

Resolem-ho en funció del temps que trigarà la Mercè… Per tant, la velocitat de la Carla, l’expresarem en funció de la velocitat de la Mercè.

1. Deduïm la velocitat relativa de la Carla, respecte a la de la Mercè.

Velocitat de la Carla = espai fins l’escola / 40 minuts

Vc = e/40

Velocitat de la Mercè = espai fins l’escola / 60 minuts

Vm = e/60

L’espai que hi ha de casa a l’escola e, no el sabem, però és el mateix per a les dues…

Per aïllar-lo, passem el que divideix a la dreta de l’equació, multiplicant a l’esquerra:

40 Vc = e

60 Vm = e

Igualem les dues equacions, car l’espai és el mateix:

40 Vc = 60 Vm

Aïllem la velocitat de la Carla passant el 40 que multiplica a l’esquerra, dividint a la dreta:

Vc = 60 / 40 Vm

Dividim per 10 a dalt i abaix a la dreta de l’equació (treiem un zero, o els que hagi…), d’això en diem simplificar:

Vc = 6/4 Vm

I encara, podem dividir per 2 a dalt i abaix (simplificar) a la dreta de l’equació:

Vc = 3/2 Vm

Nota: Tenim doncs, que la velocitat de la Carla és una vegada i mitja (3/2) més gran que la de la Mercè. Repasseu les fraccions, és un consell d’amic.

2. Expressem l’espai recorregut per les dues (que és el mateix) en una sola equació, a l’esquerra la Carla i a la dreta la Mercè, recordant que l’espai és la velocitat multiplicada pel temps (el que divideix, passa multiplicant a l’altre cantó de l’equació):

e= v t

L’espai que haurà recorregut la Carla (trigarà 10 minuts menys ), en funció de la Mercè serà:

e = 3/2 Vm (Tm - 10)

L’espai que haurà recorregut la Mercè:

e = Vm Tm

Nota: Vm és la velocitat de la Mercè i Tm és el temps que triga la Mercè.

Igualem els espais… i ja tenim la nostra equació de primer grau… encara que no ho sembli😉

3/2 Vm (Tm - 10) = Vm Tm

Resolem l’equació de primer grau…

Aïllem el Tm de la dreta, el Vm que multiplica passa a l’esquerra dividint…

3/2 Vm (Tm - 10) / Vm = Tm

Si dividim i multipliquemem per Vm, ho podem eliminar… (si multipliquem per 2 i dividim per 2 una quantitat, la que sigui, obtenim la mateixa quantitat, si ho fem pel logaritme neperià de mil dos cent vint-i-set també, això va a missa que diria l’avi que ere de lletres “pures”…):

3/2 (Tm – 10) = Tm

Resolem el parèntesi de l’esquerra, multiplicant a dalt per 3 i dividint abaix per 2 (ho poso entre parèntesi per no fer servir més d’una línia, aneu-vos acostumant…):

(3Tm – 30) / 2 = Tm

Passem el 2 que deivideix a l’esquerra, multiplicant a la dreta:

3Tm – 30 = 2Tm

 

Aïllem tots els Tm a l’esquerra de l’equació: el que suma passa a l’altre cantó restant; i el que resta, passa sumant a l’altre cantó…

3Tm – 2Tm = 30

Finalment tenim que (3Tm–2Tm=Tm), 3 pomes menys 2 pomes = poma, no barregeu “pomes i peres”😉 :

Tm = 30

És a dir, que 30 minuts després de que hagi surtit de casa la Mercè, es troba amb la Carla…

El problema que us plantejo ara, és el següent: quant de temps ha de sortir abans la Mercè per a que ambdues arribin al mateix temps a escola…😉

Tant de bó, us hagi estat útil.

I recordeu que a Catalunya som pioners en continguts educatius penjats a internet, així que cerqueu… que ben segur que en trobareu i de ben útils… i sobretot, comenceu la recerca per xtec.cat

Aquí podeu trobar un document pdf per si us el voleu descarregar.

Recordar-vos que per elaborar tots aquests continguts faig servir programari lliure, en concret Libre Office Versió 3.6.4.3 i que tant de bó el féssiu servir per tal d’aprimar la factura que es paga per programaris ofimàtics que no s’ho valen.

Seguir

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