Bekap oracle šema korišćenjem expdp (crontab-ready skripta)

EXPDP je standardni Oracle alat namenjen za bekap šema na bazi, određenih objekata ili cele baze (mada ja uvek bekapujem samo određene šeme). Lak je za korišćenje i ne opterećuje mnogo server a standardna sintaksa je: expdp dumpfile=XXX logfile=XXX.

U donjoj skripti se uz expdp nalaze i raznorazne provere tipa, postoji li direktorijum definisan u Oracle-u, ima li dovoljno prostora na lokaciji gde je taj direktorijum za bekap, da nije expdp već pokrenut, ima li grešaka u eksport logu, da nije server preopterećen pa da ga zaštitimo od dodatnog opterećenje – i sve uz imejl notifikacije. Ovo sam postavio ovako da bi skripta bila laka za crontab i obavestila admina o svim problemima ili ishodu eksporta – pa kome treba, neka promeni varijable, testira i koristi.

#!/bin/bash source /home/oracle/.bash_profile ######################################################################################### # Opis: Skripta za export nekih oracle shema putem expdp-a uz razne provere svega i svaceg # Autor: Kompjuteras.com, slobodni ste da skriptu koristite/menjate kako vam srcu drago # # Crontab primer: # 00  03  *  *  *  /oracle/skripte/ExportBaze.sh > /tmp/CrontabExportShema.log 2>&1 # ######################################################################################### ############################ VARIJABLE ################################### #################### Podesava korisnik obavezno ########################### IME_SERVERA - Ime servera u human-friendly formatu IME_SERVERA="KOMPJUTERAS - `ip addr | grep 'state UP' -A2 | grep 'inet ' | tail -n1 | awk '{print $2}' | cut -f1  -d'/'`" # Oracle direktorijum koji se koristi za export (nije isto sto i linux dir) # Ako ne postoji u Oracleu dobicete upozorenje na mail ORACLE_EXPORT_DIREKTORIJUM="dir_za_export" # EXPORT_SEME - koje seme planirate da exportujete # Primer: EXPORT_SEME="'KORISNIK1','KORISNIK2'" - ce bekapovati ta 2 korisnika # Primer: EXPORT_SEME="'KORISNIK1'" - ce bekapovati samo usera KORISNIK1 EXPORT_SEME="'KOMPJUTERAS','LOVE','MONIKA_BELUCHI'" # Koeficijent - procena velicine exporta puta on, security zbog prostora # Primer: Ako je koeficijent 3 a export 5GB - ako na bekap lokaciji nema # Minimum 3x5=15GB free - bekak nece biti izvrsen KOEFICIJENT_SLOBODNOG_PROSTORA_ZA_EXPORT=3 # Naming prefix - Naziv bekapa ce pocinjati njime i gaziti stare # Primer: EXPORT_NAMING_PREFIX="BEKAP_BAZE" - staticki bekap, cuvace max 1 kopiju # Primer: EXPORT_NAMING_PREFIX="`date +%u`_BEKAP_BAZE" - cuvace max 7 kopija # Primer: EXPORT_NAMING_PREFIX="`date +%d`_BEKAP_BAZE" - cuvace max 31 kopiju # Primer: EXPORT_NAMING_PREFIX="`date +%m%d`_BEKAP_BAZE" - cuvace max 356 kopija EXPORT_NAMING_PREFIX="`date +%u`_KOMPJUTERAS_EXPDP" # EMAIL_TO - kome na mail idu obavestenja o greskama ili uspesnosti # Ako treba vise mailova EMAIL_TO="email1@mail.com email2@mail.com email3@mail.com" EMAIL_TO="tvojmail@mail.kom" # DUMP_FAJL - kako ce se zvati export, log i html izvesta o tablespaces DUMP_FAJL="${EXPORT_NAMING_PREFIX}.dmp" ########################################################################### ########################### PROVERA BR 1 ################################## ###           Pokrece li skriptu Linux korisnik oracle                  ### ########################################################################### echo "-----------------------`date +%d.%m.%Y_%H:%M`-----------------------" if [ `whoami | grep oracle | wc -l` -ne 1 ] ; then echo "--------------------------------------- Pokrenite ovu skriptu kao oracle korisnik ---------------------------------------" exit 1 fi echo "EXPDP startovao oracle          : OK" ########################### PROVERA BR 2 ################################## ###           Postoji li Oracle export direktorijum                     ### ########################################################################### LINUX_EXPORT_LOKACIJA=`sqlplus -s '/ as sysdba' << EOF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET PAGES 0 SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE UPPER (DIRECTORY_NAME) = UPPER('${ORACLE_EXPORT_DIREKTORIJUM}'); EXIT EOF ` if [ `echo ${LINUX_EXPORT_LOKACIJA} | sed '/^$/d' | wc -l` -ne 1 ] ; then echo "Nije setovan Oracle Direktorijum (${ORACLE_EXPORT_DIREKTORIJUM}). Proveri to! " | mail -s "${IME_SERVERA} export - Fizicki nema foldera za export" ${EMAIL_TO} echo "Linux expdp lokacija            : FAILED" exit 1 fi echo "Linux expdp lokacija            : OK (${LINUX_EXPORT_LOKACIJA})" ########################### PROVERA BR 3 ################################## ###           Jesu li svi korisnici za koje radis export tu             ### ########################################################################### for i in `echo $EXPORT_SEME | tr  "','" " " | tr -s " "` ; do EXPORT_USER_COUNT=`sqlplus -s '/ as sysdba' << EOF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET PAGES 0 select COUNT(USERNAME) from dba_users WHERE UPPER(USERNAME) = UPPER('${i}') ; EXIT EOF ` if [ `echo ${EXPORT_USER_COUNT} | sed '/^$/d'` -ne 1 ] ; then echo "Nedostaje export korisnik       : FAIL (Nema korisnika: ${i})" ; fi >> /tmp/.export_korisnici.log done # Provera if [ `cat /tmp/.export_korisnici.log | wc -l` -gt 0 ] ; then cat /tmp/.export_korisnici.log rm -f /tmp/.export_korisnici.log exit 201 else rm -f /tmp/.export_korisnici.log echo "Svi korisnici za export su tu   : OK" fi ########################### PROVERA BR 4 ################################## ###    Postoji li fizicki taj folder na Linuxu i da li je dostupan      ### ########################################################################### if [ ! -d "${LINUX_EXPORT_LOKACIJA}" ] ; then echo "Nedostaje folder za export na OS (folder: ${LINUX_EXPORT_LOKACIJA}). Proveri to! " | mail -s "${IME_SERVERA} export - Fizicki nema foldera za export" ${EMAIL_TO} echo "Folder za export definisan      : FAILED" exit 1 fi echo "Folder za export definisan      : OK" # Za slucaj da se desi na je backup lokacija zaglupljena # Desava se to ako je cfs/nfs u pitanju pa ni df ne moze da se izvrsi df > /tmp/DostupnostParticije.log 2>/dev/null & sleep 10 if [ `cat /tmp/DostupnostParticije.log | wc -l 2>/dev/null` -lt 3 ] ; then echo "Ne mogu prici bekap lokaciji (ne radi komanda: df ${LINUX_EXPORT_LOKACIJA}). Nešto nije u redu sa ${LINUX_EXPORT_LOKACIJA}, proveri to, izlazim iz procesa backupa. Ako je Windows share u pitanju probajte kao root: umount -a -t cifs -l ; mount -a Ako je NFS share u pitanju probajte kao root    : umount -a -t nfs -l  ; mount -a To ce uraditi umount nfs ili cifsa i odmah mountovati " | mail -s "${IME_SERVERA} export - Lokalni ili network storage u problemu (df ne radi)" ${EMAIL_TO} echo "Status HDD skladista dostupan   : FAILED" exit 1 fi echo "Status HDD skladista dostupan   : OK" ########################### PROVERA BR 5 ################################## ###           Ima li dovoljno mesta za bekap na lokaciji                ### ########################################################################### VELICINA_EXPORTA_SEMA=`sqlplus -s '/ as sysdba' << EOF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET PAGES 0 SELECT DECODE (ROUND (SUM (bytes) / 1024 / 1024 / 1024, 0) * ${KOEFICIJENT_SLOBODNOG_PROSTORA_ZA_EXPORT}, 0, ${KOEFICIJENT_SLOBODNOG_PROSTORA_ZA_EXPORT}, ROUND (SUM (bytes) / 1024 / 1024 / 1024, 0) * ${KOEFICIJENT_SLOBODNOG_PROSTORA_ZA_EXPORT}) AS NEED_GB FROM dba_segments WHERE owner in (${EXPORT_SEME}); EXIT EOF ` VELICINA_EXPORTA_SEMA=`echo ${VELICINA_EXPORTA_SEMA} | tr -d '[:blank:]'` # Fix za blank space SLOBODNO_PROSTORA_NA_DISKU_ZA_EXPORT=`df -P -BG -h ${LINUX_EXPORT_LOKACIJA} | awk '/[0-9]%/{print $(NF-2)}' | sed 's/[^0-9]*//g'` if [ ${SLOBODNO_PROSTORA_NA_DISKU_ZA_EXPORT} -le ${VELICINA_EXPORTA_SEMA} ] ; then echo " ----------------------------------------------------------------- Export size (puta ${KOEFICIJENT_SLOBODNOG_PROSTORA_ZA_EXPORT}): ${VELICINA_EXPORTA_SEMA} GB Slododnog mesta: `df -P -BG -h ${LINUX_EXPORT_LOKACIJA} | awk '/[0-9]%/{print $(NF-2)}'` GB ----------------------------------------------------------------- OBUSTAVLJEN EXPORT - Proverite prostor na ${LINUX_EXPORT_LOKACIJA} ----------------------------------------------------------------- `df -P -BG -h ${LINUX_EXPORT_LOKACIJA}` ----------------------------------------------------------------- " | mail -s "${IME_SERVERA} export - Nema mesta za export" ${EMAIL_TO} echo "Potrebno VS Slobodno prostora   : FAIL (${VELICINA_EXPORTA_SEMA}GB VS ${SLOBODNO_PROSTORA_NA_DISKU_ZA_EXPORT}GB)" echo "Nema dovoljno mesta za export" exit 1 fi echo "Potrebno VS Slobodno prostora   : OK (${VELICINA_EXPORTA_SEMA}GB VS ${SLOBODNO_PROSTORA_NA_DISKU_ZA_EXPORT}GB)" ########################### PROVERA BR 6 ################################## ###           Da nije mozda neki expdp vec pokrenut                     ### ########################################################################### if [ $( ls -l /proc/`pidof expdp`/exe 2>/dev/null | grep ${ORACLE_HOME} | wc -l ) -gt 0 ] ; then echo " ----------------------------------------------------------------- `ps aux | grep expdp | grep -v grep` ----------------------------------------------------------------- " | mail -s "${IME_SERVERA} export - pokusaj dupliciranja exporta" ${EMAIL_TO} echo "EXPDP nema zaostalih procesa    : FAIL" exit 1 fi echo "EXPDP nema zaostalih procesa    : OK"  ########################### PROVERA BR 7 ################################## ### Ako je load na serveru preveliki obustavi expdp cisto da ne         ### ###          Preveliki: load 3x veci od broja CPU-ova                   ### ########################################################################### BROJ_PROCESORA="`cat /proc/cpuinfo | grep vendor_id | wc -l`" MAX_LOAD=`echo $(( ${BROJ_PROCESORA} * 3 ))` if [ `uptime | awk '{print $NF}' | cut -f1 -d.` -gt ${MAX_LOAD} ]; then echo " ----------------------------------------------------------------- Load presao ogranicenje od ${MAX_LOAD}: (`uptime`) ----------------------------------------------------------------- " | mail -s "${IME_SERVERA} export - Load je preveliki (veci od ${MAX_LOAD}). Prekidam export baze" ${EMAIL_TO} echo "LOAD na serveru je u redu       : FAIL" exit 1 fi echo "LOAD na serveru je u redu       : OK" ########################################################################### ###   Brisanje starih logova i bekapa sa istim imenom, ako postoje      ### ########################################################################### if [ -s ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.gz ] ; then rm -f ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.gz fi if [ -s ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.log ] ; then rm -f ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.log fi echo "Obrisani stari bekapi           : OK" # Generisanje html-a sa info o tablespaceovima sqlplus -S -M "HTML ON TABLE 'BORDER="1" CELLSPACING="0"'" '/ as sysdba ' < ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}_tablespaces.html 2>/dev/null set pagesize 32000 set feedback on SELECT CURRENT_DATE, tablespace_name, FILE_NAME, ((bytes)/1024/1024) DATAFILE_SIZE_MB, AUTOEXTENSIBLE AUTO_EXTEND FROM dba_data_files ORDER BY 1, 2, 3; exit 0 / EOF echo "Napravljen tablespaces report   : OK" echo "EXPDP moze da pocne             : OK" echo "--------------------------------------------------------------" ########################################################################### ###                      Export sema - napokon                          ### ########################################################################### START_EXPORT_VREME=`date +"%d.%m.%Y %H:%M"` expdp \'/ as sysdba\' directory=${ORACLE_EXPORT_DIREKTORIJUM} schemas=${EXPORT_SEME} flashback_time=systimestamp dumpfile=${DUMP_FAJL} logfile=${DUMP_FAJL}.log gzip -f "${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}" ################## Export procedura  ################## DONE_EXPORT_VREME=`date +"%d.%m.%Y %H:%M"` echo "----------------------------------" echo "EXPDP poceo u  : ${START_EXPORT_VREME}" echo "EXPDP zavrsio u: ${DONE_EXPORT_VREME}" echo "----------------------------------" ########################################################################### ###           Provera logova i email obavestenja o ishodu               ### ########################################################################### if [ -n "`grep "successfully completed at" ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.log`"  ]; then echo "${IME_SERVERA} - Export uspesno zavrsen ------------------------------------------------------- ${START_EXPORT_VREME} - Pocetak exporta ${DONE_EXPORT_VREME} - Kraj exporta ------------------------------------------------------- `du -sh ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}*` " | mail -s "${IME_SERVERA} export - Uspesno zavrsen" -a ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.log ${EMAIL_TO} else echo "${IME_SERVERA} - Export ima greske #################### GRESKE ########################### `grep 'ORA-' ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.log` ####################################################### ${START_EXPORT_VREME} - Pocetak exporta ${DONE_EXPORT_VREME} - Kraj exporta ------------------------------------------------------- `du -sh ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}*` ------------------------------------------------------- " | mail -s "${IME_SERVERA} export - Postoje greske u export logu!!!" -a ${LINUX_EXPORT_LOKACIJA}/${DUMP_FAJL}.log ${EMAIL_TO} fi exit 0