Πώς μπορούμε να λαμβάνουμε email κάθε φορά που Tablespace χρειάζεται Datafile σε βάση δεδομένων της Oracle
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Στις βάσεις δεδομένων της Oracle οι πίνακες αποθηκεύονται σε λογικές οντότητες που ονομάζονται Tablespaces. Το κάθε Tablespace απότελείται από φυσικά αρχεία που ονομάζονται Datafiles. Στην Oracle όμως το κάθε datafile μπορεί να έχει μέγιστο μέγεθος έως 32GB, λόγο αυτού αν δεν έχουμε φτιάξει αρκετά datafiles ενδέχεται ενώ έχουμε χώρο στον δίσκο να μην έχει χώρο ένα tablespace και ακολούθως οι πίνακες που περιέχει.
Σε αυτό το άρθρο θα δούμε έναν τρόπο ώστε όταν ένα tablespace εκτιμάται με την χρήση του ότι πλησιάζει μερικές μέρες πριν το όριο του να μας ενημερώσει με ένα warning email. Αυτό θα περιέχει το όνομα του server και του instance, μαζί με το όνομα του tablespace, τον χώρο που του απομένει και την εντολή που χρειάζεται να εκτελέσουμε ώστε να δημιουργηθεί το datafile στο tablespace αυτό.
Ωστόσο θα δούμε ότι με το παρακάτω script μπορούμε να αυτοματοποιήσουμε τελείως την διαδικασία και να μας φτιάξει μόνο του ακόμα και το datafile.
Τα βήματα
Σε προηγούμενο άρθρο είχαμε δει πως μπορούμε να ρυθμίσουμε έναν Linux server (Oracle Linux βασισμένο σε RHEL συγκεκριμένα) ώστε να λαμβάνουμε email κάθε φορά που εμφανίζεται ένα error στο Alert log του instance της βάσης δεδομένων. Αντίστοιχα τώρα θα δούμε τα βήματα για να λαμβάνουμε email όταν ένα tablespace χρειαστεί σύντομα datafile.
Δημιουργία SMTP relay με gmail της Google
Αν έχουμε δικό μας relay στον exchange server με ρυθμισμένο το smtp που θα στέλνει τα emails, αυτό το βήμα δεν το χρειαζόμαστε.
Θα δούμε τώρα όμως τι κάνουμε σε περίπτωση που θέλουμε να στέλνουμε τα emails μέσω Gmail.
Για αρχή θα πρέπει να συνδεθούμε στο gmail account security.
Εκεί θα πρέπει να ενεργοποιήσουμε το 2 factor authentication και έπειτα να δημιουργήσουμε application password.
Αφού πάρουμε τον κωδικό από το gmail, πάμε στον server μας και προσθέτουμε στο mail configuration το email μας μαζί με τον κωδικό που πήραμε:
[oracle@dp-gr ~]$ sudo vi /etc/mail.rc
account gmail {
set smtp-use-starttls
set ssl-verify=ignore
set smtp-auth=login
set smtp=smtp://smtp.gmail.com:587
set from="info@dataplatform.gr"
set smtp-auth-user=dataplatform.gr@gmail.com
set smtp-auth-password="edw_vazoume_to_app_password"
set ssl-verify=ignore
set nss-config-dir=/etc/pki/nssdb
}
Είμαστε έτοιμοι να κάνουμε δοκιμή ότι μπορούμε να στείλουμε email:
[oracle@dp-gr ~]$ echo "Test" | /usr/bin/mail -A gmail -s "dokimastiko" ToEmailMas@outlook.com
Πώς θα βρίσκουμε το Tablespace που θα χρειαστεί σύντομα Datafile
Φτιάχνουμε το παρακάτω sql query που δημιουργεί το warning μήνυμα για το tablespace, όταν με ανάλογη χρήση η εκτίμηση είναι ότι θα χρειαστέι datafile σε λιγότερες από 20 ημέρες ή κάποιο φτάσει σχεδόν στο μηδέν:
[oracle@dp-gr ~]$ vi check_tablespaces.sqlset linesize 7000;
set heading off;
set feedback off;
set echo off;
set pagesize 0;
SELECT 'Warning: Low Tablespace space on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance)||' to tablespace '|| TABLESPACE_NAME ||'. Space is enough for estimated ' || to_char(round(DAYS_EST_W_EXT,2),'999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') || ' days!!! There is left free with extends '||to_char(round(FREE_LEFT_MB_W_EXT,2),'999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.')|| 'MB. You can execute this command as sysdba user to create the new datafile: ' || FILE_CREATION
FROM(SELECT T.TABLESPACE_NAME,
(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) FREE_LEFT_MB_W_EXT,
(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) / decode(max(t2.MB_PER_DAY),0,0.01,max(t2.MB_PER_DAY)) days_est_w_ext,
(select 'ALTER tablespace ' || d.tablespace_name || ' add datafile ''' || d.file_name ||'_adf_' || md.file_id ||'.dbf'' size 1g autoextend on next 64m maxsize 32767m;'
FROM (select tablespace_name,ROW_NUMBER() OVER (PARTITION BY tablespace_name order by file_id) rn, substr(file_name,0,(length(file_name)-4)) file_name from dba_data_files ) D,
(select (max(file_id)+1) file_id from dba_data_files) MD
where tablespace_name = T.TABLESPACE_NAME and RN=1
) FILE_CREATION
FROM (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES / 1048576) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID) F,
(SELECT FILE_ID,
FILE_NAME,
BYTES / 1048576 TOTALSPACE,
TABLESPACE_NAME,
DECODE(MAXBYTES, 0, BYTES, MAXBYTES) / 1024 / 1024 AS F_MAXSIZE
FROM DBA_DATA_FILES) T,
(select name, case when count(*)=1 then null else case when sum(used_mb - p_used_mb) >=0 then sum(used_mb - p_used_mb) / (count(*) - 1) else 0 end end mb_per_day
from (select SN.begin_interval_time,
name,
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2) USED_MB,
nvl(LAG(round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) OVER(PARTITION by TS.name ORDER BY
Sn.begin_interval_time),
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) P_USED_MB
from V$TABLESPACE TS,
DBA_HIST_TBSPC_SPACE_USAGE TH,
DBA_HIST_SNAPSHOT SN,
DBA_TABLESPACES TSO
where TS.ts# = TH.tablespace_id
and TH.snap_id = SN.snap_id
and SN.instance_number = 1
and TS.NAME = TSO.tablespace_name
and to_char(SN.begin_interval_time, 'hh24:mi') = '07:00'
order by 2, 1)
group by name) T2
WHERE T.FILE_ID = F.FILE_ID(+)
AND T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME = T2.NAME(+)
GROUP BY T.TABLESPACE_NAME
)
WHERE
(
ROWNUM=1
AND TO_NUMBER(DAYS_EST_W_EXT) < 20 --change this if you need to change the days estimation threshold
AND FREE_LEFT_MB_W_EXT<10240 --minimum free mb left threshold
)
OR
(
ROWNUM=1
AND FREE_LEFT_MB_W_EXT<128 --ignore estimation threshold if space is very low
)
;
/*
select ' ' from dual;
select 'The below command was executed:' from dual;
spool create_dbf.txt
SELECT FILE_CREATION
FROM(SELECT T.TABLESPACE_NAME,
to_char(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0)), '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') FREE_LEFT_MB_W_EXT,
(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) / decode(max(t2.MB_PER_DAY),0,0.01,max(t2.MB_PER_DAY)) days_est_w_ext,
(select 'ALTER tablespace ' || d.tablespace_name || ' add datafile ''' || d.file_name ||'_adf_' || md.file_id ||'.dbf'' size 128mb autoextend on next 64m maxsize 32767m;'
FROM (select tablespace_name,ROW_NUMBER() OVER (PARTITION BY tablespace_name order by file_id) rn, substr(file_name,0,(length(file_name)-4)) file_name from dba_data_files ) D,
(select (max(file_id)+1) file_id from dba_data_files) MD
where tablespace_name = T.TABLESPACE_NAME and RN=1
) FILE_CREATION
FROM (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES / 1048576) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID) F,
(SELECT FILE_ID,
FILE_NAME,
BYTES / 1048576 TOTALSPACE,
TABLESPACE_NAME,
DECODE(MAXBYTES, 0, BYTES, MAXBYTES) / 1024 / 1024 AS F_MAXSIZE
FROM DBA_DATA_FILES) T,
(select name, case when count(*)=1 then null else case when sum(used_mb - p_used_mb) >=0 then sum(used_mb - p_used_mb) / (count(*) - 1) else 0 end end mb_per_day
from (select SN.begin_interval_time,
name,
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2) USED_MB,
nvl(LAG(round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) OVER(PARTITION by TS.name ORDER BY
Sn.begin_interval_time),
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) P_USED_MB
from V$TABLESPACE TS,
DBA_HIST_TBSPC_SPACE_USAGE TH,
DBA_HIST_SNAPSHOT SN,
DBA_TABLESPACES TSO
where TS.ts# = TH.tablespace_id
and TH.snap_id = SN.snap_id
and SN.instance_number = 1
and TS.NAME = TSO.tablespace_name
and to_char(SN.begin_interval_time, 'hh24:mi') = '07:00'
order by 2, 1)
group by name) T2
WHERE T.FILE_ID = F.FILE_ID(+)
AND T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME = T2.NAME(+)
GROUP BY T.TABLESPACE_NAME
)
WHERE
(
ROWNUM=1
AND TO_NUMBER(DAYS_EST_W_EXT) < 20 --change this if you need to change the days estimation threshold
AND FREE_LEFT_MB_W_EXT<10240 --minimum free mb left threshold
)
OR
(
ROWNUM=1
AND FREE_LEFT_MB_W_EXT<128 --ignore estimation threshold if space is very low
)
;
spool off;
@create_dbf.txt
*/
quit;
/
*Ο κώδικας που είναι σε σχόλιο /* έως */ είναι για να αυτοματοποιήσουμε την διαδικασία. Θα αναφερθούμε αναλυτικά παρακάτω.
Πώς στέλνουμε με email όταν χρειαστεί σύντομα ένα Tablespace Datafile
Τώρα θα δημιουργήσουμε ένα εκτελέσιμο το οποίο θα τρέχει το παραπάνω query εξάγοντας τα αποτελέσματα σε ένα text file. Σε περίπτωση που το αρχείο είναι πάνω από μία γραμμή, τότε θα στείλει email με τον τίτλο “Oracle DB Alert ORCL” στα email που έχουμε ορίσει έχοντας ως κείμενο ένα warning που περιέχει το όνομα του server και του instance, μαζί με το όνομα του tablespace, τον χώρο που του απομένει και την εντολή που χρειάζεται να εκτελέσουμε ώστε να δημιουργηθεί το datafile στο tablespace αυτό.
Ο λόγος που η διαδικασία ολοκληρώνεται μόνο αν το αρχείο περιέχει πάνω από μία γραμμή, είναι ώστε να μην στέλνει email στη περίπτωση που δεν υπάρχει κάποιο tablespace που χρειάζεται datafile και το text file είναι κενό.
Οπότε δημιουργούμε το εκτελέσιμο με τον παρακάτω κώδικα:
[oracle@dp-gr ~]$ vi check_tablespaces.sh#!/bin/bash
sqlplus -S "/as sysdba" @/home/oracle/check_tablespaces.sql > /home/oracle/check_tablespaces.log
cnt=$(wc -l </home/opc/check_tablespaces.log)
echo $cnt
if [ $cnt -ge "1" ]
then
/usr/bin/mail -A gmail -s "Oracle DB Tablespace Warning on ORCL" \
-r info@dataplatform.gr \
dataplatform.gr@gmail.com \
< /home/opc/check_tablespaces.log
fi
Μπορούμε εναλλακτικά να το τρέχουμε απομακρυσμένα σε άλλο μηχάνημα με τον χρήστη SYS μέσω ezconnect:
#!/bin/bash
sqlplus -S "sys/Kwdikos@hostname:1521/ORCL as sysdba" @/home/oracle/check_tablespaces.sql > /home/oracle/check_tablespaces.log
cnt=$(wc -l </home/opc/check_tablespaces.log)
echo $cnt
if [ $cnt -ge "1" ]
then
/usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
-r info@dataplatform.gr \
dataplatform.gr@gmail.com \
< /home/opc/check_tablespaces.log
fi
Δεν ξεχνάμε να έχουμε δώσει δικαίωμα execute πριν την εκτέλεση του:
[oracle@dp-gr ~]$ chmod +x check_tablespaces.sh
[oracle@dp-gr ~]$ ./check_tablespaces.shΠώς το κάνουμε schedule να γίνεται έλεγχος κάθε πρωί
Αφού και το εκτελέσιμό μας είναι έτοιμο μπορούμε να προσθέσουμε μια γραμμή στο crontab στην οποία θα ορίσουμε να τρέχει κάθε πρωί:
[oracle@dp-gr ~]$ crontab -e#crontab 00 08 * * * . ~/.bash_profile && (/home/oracle/check_tablespaces.sh >> /dev/null 2>&1)
Το αποτέλεσμα
Πλέον όταν το tablespace ξεπεράσει το threshold σε ημέρες που του έχουμε ορίσει θα μας έρχεται email.
Το email αυτό θα περιέχει ένα μύνημα του τύπου:
Warning: Low Tablespace space on ORCL@dp-gr to tablespace EXAMPLE. Space is enough for estimated 19 days!!! There is left free with extends 1.085,98MB. You can execute this command as sysdba user to create the new datafile: ALTER tablespace EXAMPLE add datafile '/home/oracle/oracle/app/oracle/oradata/orcl/example01_adf_7.dbf' size 1g autoextend on next 64m maxsize 32767m;*για το παράδειγμα είχα βάλει 34.000 ημέρες όπως φαίνεται στην παρακάτω εικόνα.
Πώς αυτοματοποιούμε τελείως την διαδικασία ώστε να δημιουργήσει το Datafile μόνο του
Αν στο ίδιο sql query αφαιρέσουμε την επισύμανση ώς σχολιο από τον κώδικα δηλαδή το /* και */ . Τότε κάτα την εκτέλεση του όχι μόνο θα μας ενημερώσει, αλλά θα δημιουργήσει και μόνο του το datafile στο tablespace το οποίο θα χρειαστεί σύντομα.
Έπειτα θα μας ενημερώσει με το παρακάτω email:
*για το παράδειγμα είχα βάλει 34.000 ημέρες όπως φαίνεται στην παρακάτω εικόνα.
