Conde Bond Post

Sólo existe un tipo de conocimiento, aquel que se transmite.

Automate your Administration Tasks ~ Basic Shellscripting for DBAs


We start a serie of posts related to basic shellscripting for DBA’s, this skill is very important, since many operations your specific work environments that you do manually, you can automate significantly reduce the time you spend on this, even lead these tasks with less skills or different areas coworkers.

If you build scripts with a uniform structure, you provide a resource for others to be complete or improve your work easily. This series of posts is not intended as a guide for application development, but rather to assist you decide which is the best method to accomplish this task if you are not accustomed to using shell.

This series of posts is not intended as a guide for application development, but rather to assist you decide which is the best method for you to accomplish this task.

RECOVER ENVIRONMENT

First Approach

Our starting point is the preparation of a Recovery Plan, which we intend to create automatic backups recovery processes that we run on a scheduled via crontab , scheduler or other solution stored in an RMAN catalog. Similarly we use packages to store compactly procedures and functions in the database, in this case we will use “libraries” of shell functions to build our own shell programs… Thus, we get simplify development, organize your code in an efficient way and reuse functionalities.

In our first approach initially we use three libraries:

  • one related to managing files, directories, etc in the file system .
  • one for RMAN functionalities.
  • last one for reconfiguration of the recovered target database.

As we have said before we have to build our code uniformly for easy understanding and maintenance.

Development Guide

A simple method to begin the development of functionalities is to create the structure that we are going to develop. We divide the code into sections :

Header

Here, we include the name of the function or main program, a brief description and history of changes.Header : Here, we include the name of the function or main program, a brief description and history of changes introduced.

#!/bin/bash
# ########################################################################### #
# File : getValidBck # Author : XPA Wavecorpuscle.com #
# Description : This program queries the Recovery Catalog Database looking #
# for a Valid Backup of the Database Provided by parameter #
# from the Number of Days provided also by parameter. #
# If no days provided the function uses Sysdate -2 default #
# value.
# ########################################################################### #
# Version : 1.0 # 2014-09-10 # First Release #
# ########################################################################### #

Global variables

For example where it is located within the filesystem where temporary files or registry if needed or generated by the program are stored. When we develop the libraries will be a unique section at the begin.

. $HOME/.bash_profile
PRG=$(basename $0)
SYSTM=$(date +%Y%m%d%H%M%S)

Usage Function

Experience has shown me that every program needs a little help or user guide, do this while you develop is very useful for creating documentation “on the fly” then latter it will be very necessary.

# ########################################################################### #
function getValidBckUsage {
fnt=“getValidBckUsage”
echo


getValidBck


USAGE:


getValidBckUsage <Db Name> <Days>


Where :


<Db Name> Is the mandatory Backed up Database Name


<Days> Is the optional number of days we sustract to Sysdate.


This function retrieves from the Repository Database the Last Valid
Backup of the <Db Name> provided from Sysdate minus <Days> provided.


The function returns the string needed for RMAN functions that uses the
UNTIL DATE clause if valid backup found.


If no <Days> Provided the function uses the default 2 value.


This function uses the Applcation Global Variables


RCVUSR As Recovery Catalog Database User


RCVPWD As Recovery Catalog Database User Password


RCVSID As Recovery Catalog Database SID.


NOTE: You need to set this Variables via export before call the function
from shell if you don’t use the library version



# Check if the Usage function is properly executed.
if [ $? -ne 0 ] ; then echo “$fnt ERROR : Wrong Execution” ; fi
}

Validation

We check if it helps to use the program call is requested and if you have provided all the parameters and this has been done correctly.

# ########################################################################### #
function getValidBck {
fnt=”getValidBck”
retval=0
PRINTOUT=””
ORADBSOURCE=””
DAYS=””
# Checking if Asked for Help
if [[ $(echo $1 | tr ‘[:lower:]’ ‘[:upper:]’ | grep -E “\-H|HELP|\/\?|USAGE” | wc -l ) -gt 0 ]] ; then
getValidBckUsage ; exit 0
fi

# Parameters Validation
if [[ -z $1 ]] ; then
PRINTOUT=“$fnt ERROR : No Database Provided”
getValidBckUsage ; exit 10
else
ORADBSOURCE=$1
fi
DAYS=$2
DBDAYS=${DAYS:-2} # Default Value is Sysdate -2 Days
# Is number validation for DAYS Parameter
if [[ -z $(echo $DBDAYS | tr -d “[0-9]”) ]] ; then

 

Body

In this section will include the code that executes our program

DBQUERY=$(echo “‘”$ORADBSOURCE”‘”)
PRINTOUT=“$fnt INFO : Looking for $DBQUERY SYSDATE – $DBDAYS Valid Backups”
untilDate=$($ORACLE_HOME/bin/sqlplus -S $RCVUSR/$RCVPWD@RCVSID << EOS
SET VERIFY OFF HEADING OFF ECHO OFF
SET TRIMSPOOL ON FEEDBACK OFF
SET LINES 30 PAGES 0
SELECT ””||SUBSTR(MAX(AB.STARTTIME),1,14)||’00:00”’ AS DTIME
FROM (
select DECODE(A.STATUS,’COMPLETED’,’OK’,’ERROR’) AS STATUS,
A.DB_NAME as “Database”,
TO_CHAR(A.END_TIME,’DAY’) AS “Day”,
TO_CHAR(A.START_TIME,’DD/MM/YYYY HH24:MI:SS’) AS STARTTIME,
A.INPUT_TYPE AS “Input”,
A.OUTPUT_DEVICE_TYPE AS “Output”,
A.INPUT_BYTES_DISPLAY AS “DatabaseSize”,
A.OUTPUT_BYTES_DISPLAY
from RC_RMAN_BACKUP_JOB_DETAILS A,
(SELECT DB_NAME,INPUT_TYPE, OUTPUT_DEVICE_TYPE, MAX(START_TIME) AS START_TIME
FROM RC_RMAN_BACKUP_JOB_DETAILS
WHERE START_TIME > SYSDATE -${DBDAYS}
AND DB_NAME=${DBQUERY}
GROUP BY DB_NAME,INPUT_TYPE, OUTPUT_DEVICE_TYPE
) B
where A.DB_NAME = B.DB_NAME and A.INPUT_TYPE = B.INPUT_TYPE
and A.OUTPUT_DEVICE_TYPE= B.OUTPUT_DEVICE_TYPE and A.START_TIME = B.START_TIME
) AB
WHERE AB.Status = ‘OK’
;
EXIT;
EOS
)

if [[ $? -eq 0 ]] ; then
if [[ -z $untilDate ]] || [[ $(echo $untilDate | grep -E “TNS-|ORA-|SP2-” | wc -l) -gt 0 ]] ; then
PRINTOUT=“$fnt ERROR : $untilDate accessing to the Repository Database or No Data Found”
retval=600
else
PRINTOUT=“UNTIL DATE = $untilDate”
fi
else

PRINTOUT=“$fnt ERROR : $untilDate accessing to the Repository Database”
retval=600
fi
else

PRINTOUT=“$fnt ERROR : Parameter Days is Not a Number”
retval=10
fi
}

Finally In our case scenario, we establish a criterion for all functions return a code, and an error/log/value message.

# ########################################################################### #
getValidBck $1 $2
echo “$PRG returns $retval”
echo “$PRINTOUT”

This is a good practice to integrate them later in our program and establish control of errors in the execution flow.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: