Oracle Metadata Generator (and svn uploader)

UPDATED!!
(See comments)

My first (public) script for Oracle :-) [beers]
This script will (hopefully) do:

List all the objects on the database for each SID/SCHEMA given
Dump the metadata of each object in a different file
Upload everything to the svn

If you’re asking why? you must deal more with programmers :-P
This will add a second line of defense to the expdp CONTENT=METADATA_ONLY classic script.

The script runs standalone on the server which is running the instance (using OS credentials).

Script code:

#!/bin/bash

# Get ALL METADATA script

########################################################################
#
# VARIABLES
#
########################################################################

# colors
LIGHTGREEN="\033[1;32m"
LIGHTRED="\033[1;31m"
WHITE="\033[0;37m"
RESET="\033[0;00m"

# time things
NOW=$(date +%Y%m%d-%H%M)
TODAY=$(date +%Y%m%d)


SCHEMA[0]="SCHEMANAME"
#SCHEMA[1]="SCHEMANAME2"

DBSID[0]="SID01"
# DBSID[1]="SID02"

OUTPUTPATH[0]="/backup/${DBSID[0]}/metadata/${SCHEMA[0]}"


SVNADDRESS[0]="svn://1.1.1.1/trunk/Oracle/Metadata/${DBSID[0]}/${SCHEMA[0]}"

# Parameters file
ENVFILE[0]="/home/oracle/entorn_${DBSID[0]}"

# AUTOCONFIG, DO NOT TOUCH
declare -a OBJECTTYPE

# OBJECT TYPE FOR QUERYING THE DBA_OBJECTS TABLE
OBJECTTYPE[0]="TABLE"
OBJECTTYPE[1]="VIEW"
OBJECTTYPE[2]="INDEX"
OBJECTTYPE[3]="PACKAGE"
OBJECTTYPE[4]="PROCEDURE"
OBJECTTYPE[5]="FUNCTION"
OBJECTTYPE[6]="TRIGGER"
OBJECTTYPE[7]="TYPE"
OBJECTTYPE[8]="SEQUENCE"
OBJECTTYPE[9]="MATERIALIZED VIEW"
OBJECTTYPE[10]="SYNONYM"
OBJECTTYPE[11]="DIRECTORY"
OBJECTTYPE[12]="EDITION"
OBJECTTYPE[13]="SCHEDULE"
OBJECTTYPE[14]="QUEUE"

# OBJECT NAME FOR GET_DDL FUNCTION
OBJECTTYPENAME[0]="TABLE"
OBJECTTYPENAME[1]="VIEW"
OBJECTTYPENAME[2]="INDEX"
OBJECTTYPENAME[3]="PACKAGE"
OBJECTTYPENAME[4]="PROCEDURE"
OBJECTTYPENAME[5]="FUNCTION"
OBJECTTYPENAME[6]="TRIGGER"
OBJECTTYPENAME[7]="TYPE"
OBJECTTYPENAME[8]="SEQUENCE"
OBJECTTYPENAME[9]="MATERIALIZED VIEW"
OBJECTTYPENAME[10]="SYNONYM"
OBJECTTYPENAME[11]="DIRECTORY"
OBJECTTYPENAME[12]="EDITION"
OBJECTTYPENAME[13]="SCHEDULE"
OBJECTTYPENAME[14]="AQ_QUEUE"


AUXFILE=/tmp/auxfile.${NOW}
AUXFILE2=/tmp/auxfile2.${NOW}

SQLGENERATOR="$(dirname $0)/logs/$(basename $0 .sh)_loader_${NOW}.sql"

REPORTLOG="$(dirname $0)/logs/$(basename $0 .sh)_${NOW}.log"
ERRFILE="$(dirname $0)/logs/$(basename $0 .sh)_${NOW}.err"

REPORTADDRESS="dodger@ciberterminal.net"
REPORTTHIS=""

export BASE_PATH=${PATH}
export DB_HOME="/u01/app/oracle/product/11.2.0/db"

########################################################################
#
# / VARIABLES
#
########################################################################


########################################################################
#
# FUNCTIONS
#
########################################################################

usage()
{
    printf "%s${LIGHTRED}USAGE:${RESET}
    $0 sync|nosync|help
    
    
    Will output the whole metadata structure of the SCHEMA(s) ${LIGHTGREEN}${SCHEMA[@]}${RESET}
    In the directories (respectibely):
    $(for ((i=0; i<=${#SCHEMA[@]}; i++)) ; do echo "${LIGHTGREEN}${OUTPUTPATH[$i]}${RESET}" ; done)
    
    If nosync is specified then the data is not uploaded to the svn\n"
    # VERY INITIAL CHECKS
}


logreport()
{
    local LOGTHIS="$*"
    echo "[$(date)] $(hostname) : ${LOGTHIS}" >> ${REPORTLOG}
}

check_db_locally()
{
# If the db is not running locally, skip the rest
DBSTATUS=$(sqlplus '/ as sysdba' <<EOF
set HEAD OFF
select STATUS from V\$INSTANCE
/
exit
EOF
)
    [[ "${DBSTATUS}" =~ ORA-01034 ]] && return 1
    return 0
}

exec_query()
{
    local QUERY="$*"
sqlplus / as sysdba 1>&2 >/dev/null <<EOF
SET LINESIZE 9999
SET ECHO OFF
SET HEAD OFF
SET FEEDBACK  OFF
SET HEADING   OFF
SET COLSEP ";"
spool ${AUXFILE} ;
${QUERY}
spool off
exit
EOF
    cat ${AUXFILE} | egrep -v "^SYS(TEM|)@.*>|^$" > ${AUXFILE2}
    cat ${AUXFILE2} > ${AUXFILE}
    cat ${AUXFILE}
}

upload_to_svn()
{
    local let DBINDEX=$1
    local let RES=0

    
    [ -d "${OUTPUTPATH[$DBINDEX]}-tmp" ] && rm -fr "${OUTPUTPATH[$DBINDEX]}-tmp"
    mkdir "${OUTPUTPATH[$DBINDEX]}-tmp"
    svn co "${SVNADDRESS[$DBINDEX]}" "${OUTPUTPATH[$DBINDEX]}-tmp"
    
    # Deleting old files from SVN
    cd ${OUTPUTPATH[$DBINDEX]}-tmp
    for DELETETHIS in $(find ./ -type f -name "*sql") ; do
        if [ ! -f ${OUTPUTPATH[$DBINDEX]}/${DELETETHIS} ] ; then
            svn rm --force ${DELETETHIS}
        fi
    done
    cd ${OLDPWD}
    # /Deleting
    cp -pfr ${OUTPUTPATH[$DBINDEX]}/ ${OUTPUTPATH[$DBINDEX]}-tmp/
    svn add --force ${OUTPUTPATH[$DBINDEX]}-tmp/*
    svn ci -m "Import ${NOW}" ${OUTPUTPATH[$DBINDEX]}-tmp/
    let RES=$?
    return ${RES}
}

########################################################################
#
# / FUNCTIONS
#
########################################################################

########################################################################
#
# MAIN
#
########################################################################

echo "col TXT FORMAT A9000"  >> ${SQLGENERATOR}

# exec > ${REPORTLOG}
# exec 2> ${ERRFILE}

OPS="${1,,}"

[[ ! "${OPS}" =~ ^(help|sync|nosync)$ ]] && usage && exit 0



for ((i=0; i<${#SCHEMA[@]} ; i++)); do
    OBJNAME=""

    if [ -f ${ENVFILE[$i]} ] ; then
        . ${ENVFILE[$i]}
    else 
        echo "Parameters file does not exists" 
        exit 2
    fi
    # MANDATORY!!!! DO-NOT-DELETE
    check_db_locally
    # If the database is not running locally, skip it!!!
    [ $? -ne 0 ] && logreport "${DBSID[$i]} Not Running locally." && continue
    # MANDATORY!!!!

    > ${SQLGENERATOR}
    for ((j=0; j<${#OBJECTTYPE[@]}; j++)); do
        QUERY="select DISTINCT OBJECT_NAME from DBA_OBJECTS where OBJECT_TYPE in ( '${OBJECTTYPE[$j]}' ) and owner = '${SCHEMA[$i]}' AND TEMPORARY = 'N' ORDER BY 1;"
        [ ! -d ${OUTPUTPATH[$i]}/${OBJECTTYPE[$j]// /_} ] && mkdir -p ${OUTPUTPATH[$i]}/${OBJECTTYPE[$j]// /_}
        exec_query "${QUERY}" | while read LINE ; do
            OBJNAME="$(echo "${LINE}" | awk -F\; '{gsub(/ +$/,"",$1);printf $1}')"
            printf "%s spool ${OUTPUTPATH[$i]}/${OBJECTTYPE[$j]// /_}/${OBJNAME//\$/#}.sql\n" >> ${SQLGENERATOR}
            printf "%s select DBMS_METADATA.GET_DDL('${OBJECTTYPENAME[$j]}','${OBJNAME}','${SCHEMA[$i]}') || ';' TXT FROM DUAL ;\n" >> ${SQLGENERATOR}
            printf "%s spool off\n" >> ${SQLGENERATOR}
        done
    done    
    rm -f ${AUXFILE2} ${AUXFILE}


    sqlplus '/ as sysdba' 1>&2 >/dev/null <<EOF
SET LINESIZE 9000
SET ECHO OFF
SET HEAD OFF
SET FEEDBACK  OFF
SET HEADING   OFF
spool ${AUXFILE} ;
@${SQLGENERATOR}
spool off
exit
EOF
    RES=$?
    rm -f ${AUXFILE2} ${AUXFILE} ${SQLGENERATOR}
    if [ ${RES} -eq 0 ] ; then
        REPORTTHIS="${REPORTTHIS}$(date) : [OK] Generating SQL metadata of ${DBSID[$i]}:${SCHEMA[$i]}\n"
        
        if [[ "${OPS}" = "sync" ]] ; then
            upload_to_svn_new ${i}
            RES=$?
            if [ ${RES} -eq 0 ] ; then
                REPORTTHIS="${REPORTTHIS}$(date) : [OK] Uploading metadata of ${DBSID[$i]}:${SCHEMA[$i]} to SVN\n"
            else
                REPORTTHIS="${REPORTTHIS}$(date) : [FAIL] Uploading metadata of ${DBSID[$i]}:${SCHEMA[$i]} to SVN\n"
            fi
        fi
    else
        REPORTTHIS="$(date) : [FAIL] Generating SQL metadata of ${DBSID[$i]}:${SCHEMA[$i]}\n"
    fi

done



if [[ "${REPORTTHIS}" ]] ; then
    if [[ "${REPORTTHIS}" =~ .*FAIL.* ]] ; then
        printf "%s${REPORTTHIS}" | mail -s "$(hostname) : Oracle incredible Metadata script report [FAIL]" -a ${ERRFILE} -a ${REPORTLOG} ${REPORTADDRESS}
    else
        printf "%s${REPORTTHIS}" | mail -s "$(hostname) : Oracle incredible Metadata script report [OK]" ${REPORTADDRESS}
    fi
fi

exit ${EXITCODE}

########################################################################
#
# / MAIN
#
########################################################################

Main variables:

SCHEMA : ARRAY with the schemas to look into
DBSID : DB SID for the SCHEMA of the same index
OUTPUTPATH : Output path for the sql scripts
SVNADDRESS : SVN server (and address) where the script will upload the scripts.
ENVFILE : ARRAY with the location of the parameters file for each instance
OBJECTTYPE : ARRAY with the list of object classes to dump
OBJECTTYPENAME : ARRAY with the list of object NAME (corresponding with the OBJECTTYPE array), sometimes the name and the for GET_DDL is different.
REPORTADDRESS : The script will send info here :-)
DB_HOME : Your DB_HOME :-P

The script will create 1 sql file for each object with the following pattern for the name:
${OUTPUTPATH}/${OBJECTTYPE}/.sql

You can disable the upload to svn by commenting out the lines indicated on the script :-)
cheers

3 thoughts on “Oracle Metadata Generator (and svn uploader)”

EvilGenius says:

May 17, 2013 at 12:23 pm

Great job! Congratulations!

Just a little possible “improvement”:

Why don’t you query the DBA_TYPES to fill up the array of object_types.

Like that, user-defined types would be included too!

Regards.
dodger says:

May 18, 2013 at 7:31 pm

I’ll do :-)
I’ve also detected a “feature” like devolpers call to their bugs :-P
I’ll update the script next bussines day!
dodger says:

May 27, 2013 at 5:29 pm

Well, I’ve corrected some errors in the script :-)
Now the svn part works as it should :-P
The part of the DBA_TYPES (I think it should be DBA_OBJECTS), I first tryed to generate the array of objects types dynamically but there are a lot of unused objects… So I decided to make a list and that’s all.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 thoughts on “Oracle Metadata Generator (and svn uploader)”

Leave a Reply