[Collection scripts] : Additional scripts for data collection

HIDDEN CONTENT

dba_tab_modifications

v$bh

dbms_stats.GATHER_SYSTEM_STATS

dba_segments

#-- Majda monitoring
00 * * * * /home/ora102/majda/mod.ksh
#00,30 * * * * /home/ora102/majda/mon.ksh
0 * * * * /home/ora102/majda/system_stat_history.sh

#mod.sh

log=/home/ora102/majda/mod.log

> $log

exec >> $log

exec 2>> $log

. ~ora102/.bash_profile

snap_mod_tables()

{

sql_flush="exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;"

sql_sequence="set feed off pages 0\nselect majda.seq_modifications.nextval from dual;"

sequence=`echo -e $sql_sequence | sqlplus -s / as sysdba `

sql_insert="insert into majda.m_dba_tab_modifications select sysdate,a.*, $sequence from sys.dba_tab_modifications a;"

sql_purge="delete from majda.m_dba_tab_modifications where temps < sysdate-30;"

echo $sql_flush | sqlplus / as sysdba

echo $sql_insert | sqlplus / as sysdba

echo $sql_purge | sqlplus / as sysdba

}

snap_index_usage()

{

:

}

snap_vbh()

{

:

}

#------ MAIN --------#

snap_mod_tables

snap_index_usage

#mon.sh

log=/home/ora102/majda/mon.log

>> $log

exec >> $log

exec 2>> $log

. ~ora102/.bash_profile

snap_vbh()

{

echo $seq

seq="set feed off pages 0\nselect majda.seq_vbh.nextval from dual;"

sequence=`echo -e $seq | sqlplus -s / as sysdba`

sql1="insert into majda.m_vbh select $sequence,sysdate,a.* from v\$bh a;"

sql2="delete from majda.m_vbh where temps < sysdate-10;"

echo $sql1

echo "$sql1" | sqlplus -s / as sysdba

#echo $sql2

#echo "$sql2" | sqlplus -s / as sysdba

}

snap_object_usage()

{

tentatives=20

sql="insert into majda_monitoring select sysdate temps, object_name, decode(flags,0,'UNUSED','USED'), START_MONITORING,END_MONITORING

from sys.object_usage u, dba_objects o

where

o.object_id = u.obj#

and o.owner='AUTOPROD'

and owner='AUTOPROD'

and object_name in ( 'IND_ATTRLISTVALUEATTRPAN', 'IND_PJIDPANIDMAIL','ID_EMAIL_TYPE_ST','IND_EMSG_PRJ_MATPE_EML','SYS_C00104055','IND_UPLOADGROUPPANELIST','IND_UPLOADGROUPEMAIL','IDX1_DELIVERY1','PK_ATTRSTRINGVALUE','PK_ATTRDATEVALUE','PK_ATTR_INTVALUE')

;"

echo $sql

echo $sql | sqlplus -s / as sysdba

liste_indexes="IND_PJIDPANIDMAIL ID_EMAIL_TYPE_ST IND_EMSG_PRJ_MATPE_EML SYS_C00104055 IND_UPLOADGROUPPANELIST IND_UPLOADGROUPEMAIL IND_ATTRLISTVALUEATTRPAN IDX1_DELIVERY1 PK_ATTRSTRINGVALUE PK_ATTRDATEVALUE PK_ATTR_INTVALUE"

for ind in $liste_indexes

do

sql_nomon="set feed off\n alter index autoprod.$ind nomonitoring usage;"

sql_mon="set feed off\n alter index autoprod.$ind monitoring usage;"

#echo $sql_nomon

retour="ERROR"; tour=1

while [ -n "$retour" -a $tour -le $tentatives ]; do

echo $tour Fois $sql_nomon

retour=`echo -e "$sql_nomon" | sqlplus -s / as sysdba`

if [ -n "$retour" ]; then

echo "$retour"

sleep 5

fi

((tour=$tour+1))

done

retour="ERROR"; tour=1

while [ -n "$retour" -a $tour -le $tentatives ]; do

echo $tour Fois $sql_mon

retour=`echo -e "$sql_mon" | sqlplus -s / as sysdba`

if [ -n "$retour" ]; then

echo "$retour"

sleep 5

fi

((tour=$tour+1))

done

done

}

#------ MAIN --------#

snap_object_usage

##snap_vbh

#system_stat_history.sh

#!/bin/sh

#---------------------------------------------------------

# Collect system statistics for 2 weeks on the four nodes :

# * every 1hour,

# * during 20minutes

# * To find the right values

#---------------------------------------------------------

. ~ora102/.bash_profile

rep=/home/ora102/majda

export TNS_ADMIN=$rep

log=/home/ora102/majda/system_stat_history.log

>$log

exec >> $log

exec 2>> $log

i=1

liste_sids="tloral1 tloral2 tloral3 tloral4"

for sid in $liste_sids

do

sql="set serveroutput on\n

set verify off\n

declare \n

statid varchar2(30);\n

noeud number;

begin\n

select substr(instance_name,length(instance_name),1) into noeud from v\$instance ;

select 'N'||noeud||'_'||to_char(sysdate,'YYYYMMDDHH24MISS') into statid from v\$instance; \n

dbms_output.put_line(statid);\n

dbms_stats.GATHER_SYSTEM_STATS('interval',20,'AUX_STATS\$_HIS'||noeud,statid,'SYS');\n

end;\n

/"

##sql="select count(1) from sys.AUX_STATS\$_HIS$i;"

echo -e $sql | sqlplus -s majda/majda@$sid

((i=$i+1))

done

#---------------------------------------------------------

# system_stat_history.sql : pour lister les stats collecté

# -------

#col statid for a20

#col status for a12

#col dstop for a20

#col dstart for a20

#

#select partie1.statid, dstart, dstop, status,

# sreadtim,mreadtim,cpuspeed,mbrc,maxthr,slavethr

#FROM

#(SELECT statid, n1 AS sreadtim, n2 AS mreadtim, n3 AS cpuspeed, n11 AS mbrc,

# c2 as dstart, c3 as dstop, c1 as status

# from aux_stats$_his1

#where c4 = 'CPU_SERIO'

#) partie1,

#(SELECT statid,n1 AS maxthr, n2 AS slavethr

#FROM aux_stats$_his1

#WHERE c4 = 'PARIO') partie2

#where

#partie1.statid= partie2.statid

#order by partie1.statid

#;

#

#---------------------------------------------------------

seg.sh

--> select sysdate temps, s.* from dba_segments;