#!/bin/ksh
########################################################################################################
## SCRIPT DE COMPARAISON MULTI-TABLES A PARTIR DE FICHIER *.lst
#####################################################################################################
# Variables
############
export script=$(basename ${0})
export liste=""
export instance=""
export schema1=""
export schema2=""
export erreur=0
export filelog=""
# Usage
########
function usage
{
echo " Usage : [1;33m${script} <instance> <schema1> <schema2> <liste_table>[0m"
echo
echo " [1m<instance>[0m = SSID Oracle du couloir"
echo " [1m<schema1>[0m = schema source (OC0000, OC0001, OC9001....)"
echo " [1m<schema2>[0m = schema cible (OC0000, OC0001, OC9001....)"
echo " [1m<liste_table>[0m = fichier local contenant la liste de table a comparer ( ! extension = lst )"
}
# Affichage d'une erreur + sortie 1
####################################
function print_err
{
clear
echo "[1;31m\n ! ${1} !\n[0m"
usage
exit 1
}
# Affichage message d'information
##################################
function print_info
{
echo "\n `date '+%D %H:%M:%S'` ${script} : ${1}"
}
#######
# MAIN
#######
print_info "Tests des parametres et de la connexion"
echo "[\c"
# Test du nombre de paramètres
if [ ${#} -ne 4 ]
then
print_err "Nombre de parametres incorrect"
fi
echo ".\c"
# Tests sur la liste de table
if [ -r ${4} ]
then
# Test de l extension
if [ "`echo ${4} | awk -F. '{print $NF;}'`" != "lst" ]
then
print_err "Le fichier contenant la liste doit porter une extension '.lst'"
fi
else
print_err "Le 4eme parametre doit etre un fichier portant l'extension .lst"
fi
liste=${4}
echo ".\c"
# test de l'instance
if [ `tnsping ${1} >&-;echo $?` -ne 0 ]
then
print_err "L'instance <${1}> n'existe pas"
fi
instance=${1}
echo ".\c"
# test du schéma 1
sqlplus -S /nolog << EOF
connect db_export/export@${instance}
set feedback off
set verify off
set heading off
whenever sqlerror exit 1
variable ret_val number
DECLARE
BEGIN
:ret_val := 0;
if ('${2}' not in ('SYSTEM','SYS')) then
if UTILS.DATAFOUND('DBA_USERS','USERNAME', '${2}') <> 1 then
:ret_val:=1;
END IF;
END IF;
END;
/
disconnect
exit :ret_val;
EOF
if [ "$?" != "0" ]
then
print_err "Le schema <${2}> n'existe pas dans l'instance ${1}"
fi
schema1=${2}
echo ".\c"
# test du schéma 2
sqlplus -S /nolog << EOF
connect db_export/export@${instance}
set feedback off
set verify off
set heading off
whenever sqlerror exit 1
variable ret_val number
DECLARE
BEGIN
:ret_val := 0;
if ('${3}' not in ('SYSTEM','SYS')) then
if UTILS.DATAFOUND('DBA_USERS','USERNAME', '${3}') <> 1 then
:ret_val:=1;
END IF;
END IF;
END;
/
disconnect
exit :ret_val;
EOF
if [ "$?" != "0" ]
then
print_err "Le schema <${3}> n'existe pas dans l'instance ${1}"
fi
schema2=${3}
echo ".] \c"
print_info "Tests OK"
# Creation fichier de logs et en tete
filelog="./ISWO_comparaison_${instance}_${schema1}_${schema2}_`echo ${3} | awk -F. '{print $1;}'`_`date +%Y%m%d%H%M%S`.log"
print_info "Fichier de log : <${filelog}>"
echo "### Comparaison enter les schemas <${schema1}> et <${schema2}> sur l'instance ${instance} ###" >> ${filelog}
echo "##################################################################################" >> ${filelog}
echo >> ${filelog}
print_info "Fichier contenant la liste des tables : <${liste}>" | tee -a ${filelog}
# Création du fichier SQL à passer
for tab in $( cat ${liste})
do
# test de la présence de la table sur les deux instances.
sqlplus -S /nolog << EOF
connect db_export/export@${instance}
whenever sqlerror exit 1
set verify off
set serveroutput on
variable ret_val number
DECLARE
ma_var integer;
BEGIN
:ret_val := 0;
if ('${schema1}' not in ('SYSTEM','SYS')) then
select CASE WHEN EXISTS(SELECT TABLE_NAME,owner FROM DBA_TABLES WHERE owner='${schema1}' AND TABLE_NAME='${tab}' )THEN 1 ELSE 0 END INTO ma_var FROM dual;
IF ma_var <> 1 THEN
:ret_val:=1;
END IF ;
END IF;
END;
/
disconnect
exit :ret_val;
EOF
if [ "$?" != "0" ]
then
print_info "La table <${tab}> n'existe pas sous le schema ${schema1}."
erreur=$((${erreur}+1))
else
sqlplus -S /nolog << EOF1
connect db_export/export@${instance}
whenever sqlerror exit 1
set verify off
set serveroutput on
variable ret_val number
DECLARE
ma_var integer;
BEGIN
:ret_val := 0;
if ('${schem2}' not in ('SYSTEM','SYS')) then
select CASE WHEN EXISTS(SELECT TABLE_NAME,owner FROM DBA_TABLES WHERE owner='${schema2}' AND TABLE_NAME='${tab}' )THEN 1 ELSE 0 END INTO ma_var FROM dual;
IF ma_var <> 1 THEN
:ret_val:=1;
END IF ;
END IF;
END;
/
disconnect
exit :ret_val;
EOF1
if [ "$?" != "0" ]
then
print_info "La table <${tab}> n'existe pas sous le schema ${schema2}."
erreur=$((${erreur}+1))
else
# Si ok Rajout des lignes pour la comparaison de cette table sur les deux schemas
echo "select * from" >> compare_tmp.sql
echo "(" >> compare_tmp.sql
echo " select '${schema1}.${tab}' \"Row Source\", a.* from" >> compare_tmp.sql
echo " (" >> compare_tmp.sql
echo " select /*+ FULL(Tbl1) */ *" >> compare_tmp.sql
echo " from ${schema1}.${tab} Tbl1" >> compare_tmp.sql
echo " minus" >> compare_tmp.sql
echo " select /*+ FULL(Tbl2) */ *" >> compare_tmp.sql
echo " from ${schema2}.${tab} Tbl2" >> compare_tmp.sql
echo " ) A" >> compare_tmp.sql
echo " union all" >> compare_tmp.sql
echo " select '${schema2}.${tab}', b.* from" >> compare_tmp.sql
echo " (" >> compare_tmp.sql
echo " select /*+ FULL(Tbl2) */ *" >> compare_tmp.sql
echo " from ${schema2}.${tab}" >> compare_tmp.sql
echo " minus" >> compare_tmp.sql
echo " select /*+ FULL(Tbl1) */ *" >> compare_tmp.sql
echo " from ${schema1}.${tab}" >> compare_tmp.sql
echo " ) B" >> compare_tmp.sql
echo " )" >> compare_tmp.sql
echo "Order by 1 ;" >> compare_tmp.sql
echo "UNION" >> compare_tmp.sql
fi
fi
done
if [ ${erreur} -ne 0 ]
then
print_info "! Traitements KO : ${erreur} table(s) en erreur pendant la comparaison : voir les logs ci-dessus." | tee -a ${filelog}
rm compare_tmp.sql 2>&-
print_err "Traitements KO : erreur(s) dans la comparaison des tables. Voir le fichier de logs <${fic_res}>"
fi
# Ajustement fichier de comptage
sed '$d' compare_tmp.sql >> compare.sql
echo ";" >> compare.sql
rm compare_tmp.sql 2>&-
print_info "### Traitement termines : OK ###" | tee -a ${filelog}
exit 0