DBA survival BLOG

DBA stuff and Oracle Data Guard

Bash tips & tricks [ep. 6]: Check the exit code

Posted on March 23, 2016 by Ludovico

This is the sixth epidose of a small series.

Description:

Every command in a script may fail due to external reasons. Bash programming is not functional programming! 🙂

After running a command, make sure that you check the exit code and either raise a warning or exit with an error, depending on how a failure can impact the execution of the script.

BAD:

The worst example is not to check the exit code at all:

#!/bin/bash -l

recover -a -f -c ${NWCLIENT} -d ${DEST_FILE_PATH} $BASEBCK_FILENAME
# what if recover fails?

do_something_with_recovered_files

#!/bin/bash -l

recover -a -f -c ${NWCLIENT} -d ${DEST_FILE_PATH} $BASEBCK_FILENAME

# what if recover fails?

do_something_with_recovered_files

Next one is better, but you may have a lot of additional code to type:

#!/bin/bash -l

recover -a -f -c ${NWCLIENT} -d ${DEST_FILE_PATH} $BASEBCK_FILENAME

#---------
# the following piece of code is frequently copied&pasted 
ERR=$?
if [ $ERR -ne 0 ] ; then
    # I've got an error with the recovery
    eerror "The recovery failed with exit code $ERR"
    Log_Close
    exit $ERR
else
    eok "The recovery succeeded."
fi
#---------

do_something_with_recovered_files

#!/bin/bash -l

recover -a -f -c ${NWCLIENT} -d ${DEST_FILE_PATH} $BASEBCK_FILENAME

#---------

# the following piece of code is frequently copied&pasted

ERR=$?

if [ $ERR -ne 0 ] ; then

# I've got an error with the recovery

eerror "The recovery failed with exit code $ERR"

Log_Close

exit $ERR

else

eok "The recovery succeeded."

#---------

do_something_with_recovered_files

Again, Log_Close, eok, eerror, etc are functions defined using the previous Bash Tips & Tricks in this series.

GOOD:

Define once the check functions that you will use after every command:

# F_check_warn will eventually raise a warning but let the script continue
function F_check_warn() {
        EXITCODE=$1
        shift
        if [ $EXITCODE -eq 0 ] ; then
                eok $@ succeded with exit code $EXITCODE
        else
                ewarn $@ failed with exit code $EXITCODE. The script will continue.
        fi
        # return the same code so other checks can follow this one inside the script
        return $EXITCODE
}

# F_check_warn will eventually raise an error and exit
function F_check_exit() {
        EXITCODE=$1
        shift
        if [ $EXITCODE -eq 0 ] ; then
                eok $@ succeded with exit code $EXITCODE
        else
                eerror $@ failed with exit code $EXITCODE. The script will exit.
                Log_Close
                exit $EXITCODE
        fi
}

CMD="recover -a -f -c ${NWCLIENT} -d ${DEST_FILE_PATH} $BASEBCK_FILENAME"
enotify "Recover command: $CMD"
eval $CMD
F_check_exit $? "Recovery from networker"

do_something_with_the_recovered_files
F_check_warn $? "Non-blocking operation with recovered files"

# F_check_warn will eventually raise a warning but let the script continue

function F_check_warn() {

EXITCODE=$1

shift

if [ $EXITCODE -eq 0 ] ; then

eok $@ succeded with exit code $EXITCODE

else

ewarn $@ failed with exit code $EXITCODE. The script will continue.

# return the same code so other checks can follow this one inside the script

return $EXITCODE

}

# F_check_warn will eventually raise an error and exit

function F_check_exit() {

EXITCODE=$1

shift

if [ $EXITCODE -eq 0 ] ; then

eok $@ succeded with exit code $EXITCODE

else

eerror $@ failed with exit code $EXITCODE. The script will exit.

Log_Close

exit $EXITCODE

}

CMD="recover -a -f -c ${NWCLIENT} -d ${DEST_FILE_PATH} $BASEBCK_FILENAME"

enotify "Recover command: $CMD"

eval $CMD

F_check_exit $? "Recovery from networker"

do_something_with_the_recovered_files

F_check_warn $? "Non-blocking operation with recovered files"

Bash tips & tricks [ep. 5]: Write the output to a logfile

Posted on March 22, 2016 by Ludovico

This is the fifth epidose of a small series.

Description:

Logging the output of the scripts to a file is very important. There are several ways to achieve it, I will just show one of my favorites.

BAD:

You can log badly either from the script to a log file:

#!/bin/bash -l

TODAY=`date +"%Y%m%d"
LOGDIR='/path/to/log'
OUTPUT="${LOGDIR}/output_${TODAY}.log"

# create the empty file or overwrite the existing one
> $OUTPUT

echo "Writing to the logfile" | tee -a $OUTPUT
command | tee -a $OUTPUT

echo "ops, this message and command will not be logged"
command
exit $?

#!/bin/bash -l

TODAY=`date +"%Y%m%d"

LOGDIR='/path/to/log'

OUTPUT="${LOGDIR}/output_${TODAY}.log"

# create the empty file or overwrite the existing one

> $OUTPUT

echo "Writing to the logfile" | tee -a $OUTPUT

command | tee -a $OUTPUT

echo "ops, this message and command will not be logged"

command

exit $?

or by redirecting badly the standard output of the script:

$ crontab -l
0 * * * * /path/to/script.sh > /path/to/always_the_same_log.out 2>&1

1 2	$ crontab -l 0 * * * * /path/to/script.sh > /path/to/always_the_same_log.out 2>&1

GOOD:

My favorite solution is to automatically open a pipe that will receive from the standard output and redirect to the logfile. With this solution, I can programmatically define my logfile name inside the script (based on the script name and input parameters for example) and forget about redirecting the output everytime that I run a command.

export LOGDIR=/path/to/logfiles
export DATE=`date +"%Y%m%d"`
export DATETIME=`date +"%Y%m%d_%H%M%S"`

ScriptName=`basename $0`
Job=`basename $0 .sh`"_whatever_I_want"
JobClass=`basename $0 .sh`

function Log_Open() {
        if [ $NO_JOB_LOGGING ] ; then
                einfo "Not logging to a logfile because -Z option specified." #(*)
        else
                [[ -d $LOGDIR/$JobClass ]] || mkdir -p $LOGDIR/$JobClass
                Pipe=${LOGDIR}/$JobClass/${Job}_${DATETIME}.pipe
                mkfifo -m 700 $Pipe
                LOGFILE=${LOGDIR}/$JobClass/${Job}_${DATETIME}.log
                exec 3>&1
                tee ${LOGFILE} <$Pipe >&3 &
                teepid=$!
                exec 1>$Pipe
                PIPE_OPENED=1
                enotify Logging to $LOGFILE  # (*)
                [ $SUDO_USER ] && enotify "Sudo user: $SUDO_USER" #(*)
        fi
}

function Log_Close() {
        if [ ${PIPE_OPENED} ] ; then
                exec 1<&3
                sleep 0.2
                ps --pid $teepid >/dev/null
                if [ $? -eq 0 ] ; then
                        # a wait $teepid whould be better but some
                        # commands leave file descriptors open
                        sleep 1
                        kill  $teepid
                fi
                rm $Pipe
                unset PIPE_OPENED
        fi
}

OPTIND=1
while getopts ":Z" opt ; do
        case $opt in
                Z)
                        NO_JOB_LOGGING="true"
                        ;;
        esac
done

Log_Open
echo "whatever I execute here will be logged to $LOGFILE"
command
Log_Close

export LOGDIR=/path/to/logfiles

export DATE=`date +"%Y%m%d"`

export DATETIME=`date +"%Y%m%d_%H%M%S"`

ScriptName=`basename $0`

Job=`basename $0 .sh`"_whatever_I_want"

JobClass=`basename $0 .sh`

function Log_Open() {

if [ $NO_JOB_LOGGING ] ; then

einfo "Not logging to a logfile because -Z option specified." #(*)

else

[[ -d $LOGDIR/$JobClass ]] || mkdir -p $LOGDIR/$JobClass

Pipe=${LOGDIR}/$JobClass/${Job}_${DATETIME}.pipe

mkfifo -m 700 $Pipe

LOGFILE=${LOGDIR}/$JobClass/${Job}_${DATETIME}.log

exec 3>&1

tee ${LOGFILE} <$Pipe >&3 &

teepid=$!

exec 1>$Pipe

PIPE_OPENED=1

enotify Logging to $LOGFILE # (*)

[ $SUDO_USER ] && enotify "Sudo user: $SUDO_USER" #(*)

}

function Log_Close() {

if [ ${PIPE_OPENED} ] ; then

exec 1<&3

sleep 0.2

ps --pid $teepid >/dev/null

if [ $? -eq 0 ] ; then

# a wait $teepid whould be better but some

# commands leave file descriptors open

sleep 1

kill $teepid

rm $Pipe

unset PIPE_OPENED

}

OPTIND=1

while getopts ":Z" opt ; do

case $opt in

NO_JOB_LOGGING="true"

;;

esac

done

Log_Open

echo "whatever I execute here will be logged to $LOGFILE"

command

Log_Close

(*) the functions edebug, einfo, etc, have to be created using the guidelines I have used in this post: Bash tips & tricks [ep. 4]: Use logging levels

The -Z parameter can be used to intentionally avoid logging.

Again, all this stuff (function definitions and variables) should be put in a global include file.

If I execute it:

# [ ludo@testsrv:/scripts [21:10:17] [not set env:"not set"] 0 ] #
# sudo -u oracle ./myscript.sh
2016-03-16 21:10:20 - Logging to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log
2016-03-16 21:10:20 - Sudo user: ludo
whatever I execute here will be logged to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log

# [ ludo@testsrv:/scripts [21:10:20] [not set env:"not set"] 0 ] #
# sudo -u oracle ./myscript.sh -Z
2016-03-16 21:15:18 - INFO ---- Not logging to a logfile because -Z option specified.
whatever I execute here will be logged to

# [ ludo@testsrv:/scripts [21:10:20] [not set env:"not set"] 0 ] #
# cat /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log
2016-03-16 21:10:20 - Logging to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log
2016-03-16 21:10:20 - Sudo user: ludo
whatever I execute here will be logged to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log

# [ ludo@testsrv:/scripts [21:10:17] [not set env:"not set"] 0 ] #

# sudo -u oracle ./myscript.sh

2016-03-16 21:10:20 - Logging to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log

2016-03-16 21:10:20 - Sudo user: ludo

whatever I execute here will be logged to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log

# [ ludo@testsrv:/scripts [21:10:20] [not set env:"not set"] 0 ] #

# sudo -u oracle ./myscript.sh -Z

2016-03-16 21:15:18 - INFO ---- Not logging to a logfile because -Z option specified.

whatever I execute here will be logged to

# [ ludo@testsrv:/scripts [21:10:20] [not set env:"not set"] 0 ] #

# cat /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log

2016-03-16 21:10:20 - Logging to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log

2016-03-16 21:10:20 - Sudo user: ludo

whatever I execute here will be logged to /path/to/logfiles/myscript/myscript_whatever_I_want_20160316_211020.log

Bash tips & tricks [ep. 4]: Use logging levels

Posted on March 21, 2016 by Ludovico

This is the fourth epidose of a small series.

Description:

Support different logging levels natively in your scripts so that your code will be more stable and maintainable.

BAD:

#!/bin/bash -l
...
# for debug only, comment out when OK
echo $a 
do_something $a

# echo $? # sometimes does not work?

#!/bin/bash -l

...

# for debug only, comment out when OK

echo $a

do_something $a

# echo $? # sometimes does not work?

GOOD:

Nothing to invent, there are already a few blog posts around about the best practices for log messages. I personally like the one from Michael Wayne Goodman:

http://www.goodmami.org/2011/07/04/Simple-logging-in-BASH-scripts.html

I have reused his code in my scripts with very few modifications to fit my needs:

### verbosity levels
silent_lvl=0
crt_lvl=1
err_lvl=2
wrn_lvl=3
ntf_lvl=4
inf_lvl=5
dbg_lvl=6

## esilent prints output even in silent mode
function esilent () { verb_lvl=$silent_lvl elog "$@" ;}
function enotify () { verb_lvl=$ntf_lvl elog "$@" ;}
function eok ()    { verb_lvl=$ntf_lvl elog "SUCCESS - $@" ;}
function ewarn ()  { verb_lvl=$wrn_lvl elog "${colylw}WARNING${colrst} - $@" ;}
function einfo ()  { verb_lvl=$inf_lvl elog "${colwht}INFO${colrst} ---- $@" ;}
function edebug () { verb_lvl=$dbg_lvl elog "${colgrn}DEBUG${colrst} --- $@" ;}
function eerror () { verb_lvl=$err_lvl elog "${colred}ERROR${colrst} --- $@" ;}
function ecrit ()  { verb_lvl=$crt_lvl elog "${colpur}FATAL${colrst} --- $@" ;}
function edumpvar () { for var in $@ ; do edebug "$var=${!var}" ; done }
function elog() {
        if [ $verbosity -ge $verb_lvl ]; then
                datestring=`date +"%Y-%m-%d %H:%M:%S"`
                echo -e "$datestring - $@"
        fi
}

### verbosity levels

silent_lvl=0

crt_lvl=1

err_lvl=2

wrn_lvl=3

ntf_lvl=4

inf_lvl=5

dbg_lvl=6

## esilent prints output even in silent mode

function esilent () { verb_lvl=$silent_lvl elog "$@" ;}

function enotify () { verb_lvl=$ntf_lvl elog "$@" ;}

function eok () { verb_lvl=$ntf_lvl elog "SUCCESS - $@" ;}

function ewarn () { verb_lvl=$wrn_lvl elog "${colylw}WARNING${colrst} - $@" ;}

function einfo () { verb_lvl=$inf_lvl elog "${colwht}INFO${colrst} ---- $@" ;}

function edebug () { verb_lvl=$dbg_lvl elog "${colgrn}DEBUG${colrst} --- $@" ;}

function eerror () { verb_lvl=$err_lvl elog "${colred}ERROR${colrst} --- $@" ;}

function ecrit () { verb_lvl=$crt_lvl elog "${colpur}FATAL${colrst} --- $@" ;}

function edumpvar () { for var in $@ ; do edebug "$var=${!var}" ; done }

function elog() {

if [ $verbosity -ge $verb_lvl ]; then

datestring=`date +"%Y-%m-%d %H:%M:%S"`

echo -e "$datestring - $@"

}

The edumpvar is handy to have the status of several variables at once:

#!/bin/bash -l
# code
#...

verbosity=6

edumpvar ORACLE_SID ORACLE_HOME

<output>
2016-03-15 23:06:10 - DEBUG --- ORACLE_SID=orcl12c
2016-03-15 23:06:10 - DEBUG --- ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
</output>

#!/bin/bash -l

# code

#...

verbosity=6

edumpvar ORACLE_SID ORACLE_HOME

2016-03-15 23:06:10 - DEBUG --- ORACLE_SID=orcl12c

2016-03-15 23:06:10 - DEBUG --- ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

</output>

If you couple the verbosity level with input parameters you can have something quite clever (e.g. -s for silent, -V for verbose, -G for debug). I’m putting everything into one single snippet just as example, but as you can imagine, you should seriously put all the fixed variables and functions inside an external file that you will systematically include in your scripts:

#!/bin/bash -l

colblk='\033[0;30m' # Black - Regular
colred='\033[0;31m' # Red
colgrn='\033[0;32m' # Green
colylw='\033[0;33m' # Yellow
colpur='\033[0;35m' # Purple
colrst='\033[0m'    # Text Reset

verbosity=4

### verbosity levels
silent_lvl=0
crt_lvl=1
err_lvl=2
wrn_lvl=3
ntf_lvl=4
inf_lvl=5
dbg_lvl=6

## esilent prints output even in silent mode
function esilent () { verb_lvl=$silent_lvl elog "$@" ;}
function enotify () { verb_lvl=$ntf_lvl elog "$@" ;}
function eok ()    { verb_lvl=$ntf_lvl elog "SUCCESS - $@" ;}
function ewarn ()  { verb_lvl=$wrn_lvl elog "${colylw}WARNING${colrst} - $@" ;}
function einfo ()  { verb_lvl=$inf_lvl elog "${colwht}INFO${colrst} ---- $@" ;}
function edebug () { verb_lvl=$dbg_lvl elog "${colgrn}DEBUG${colrst} --- $@" ;}
function eerror () { verb_lvl=$err_lvl elog "${colred}ERROR${colrst} --- $@" ;}
function ecrit ()  { verb_lvl=$crt_lvl elog "${colpur}FATAL${colrst} --- $@" ;}
function edumpvar () { for var in $@ ; do edebug "$var=${!var}" ; done }
function elog() {
        if [ $verbosity -ge $verb_lvl ]; then
                datestring=`date +"%Y-%m-%d %H:%M:%S"`
                echo -e "$datestring - $@"
        fi
}

OPTIND=1
while getopts ":sVG" opt ; do
        case $opt in
        s)
                verbosity=$silent_lvl
                edebug "-s specified: Silent mode"
                ;;
        V)
                verbosity=$inf_lvl
                edebug "-V specified: Verbose mode"
                ;;
        G)
                verbosity=$dbg_lvl
                edebug "-G specified: Debug mode"
                ;;
        esac
done

ewarn "this is a warning"
eerror "this is an error"
einfo "this is an information"
edebug "debugging"
ecrit "CRITICAL MESSAGE!"
edumpvar ORACLE_SID

#!/bin/bash -l

colblk='\033[0;30m' # Black - Regular

colred='\033[0;31m' # Red

colgrn='\033[0;32m' # Green

colylw='\033[0;33m' # Yellow

colpur='\033[0;35m' # Purple

colrst='\033[0m' # Text Reset

verbosity=4

### verbosity levels

silent_lvl=0

crt_lvl=1

err_lvl=2

wrn_lvl=3

ntf_lvl=4

inf_lvl=5

dbg_lvl=6

## esilent prints output even in silent mode

function esilent () { verb_lvl=$silent_lvl elog "$@" ;}

function enotify () { verb_lvl=$ntf_lvl elog "$@" ;}

function eok () { verb_lvl=$ntf_lvl elog "SUCCESS - $@" ;}

function ewarn () { verb_lvl=$wrn_lvl elog "${colylw}WARNING${colrst} - $@" ;}

function einfo () { verb_lvl=$inf_lvl elog "${colwht}INFO${colrst} ---- $@" ;}

function edebug () { verb_lvl=$dbg_lvl elog "${colgrn}DEBUG${colrst} --- $@" ;}

function eerror () { verb_lvl=$err_lvl elog "${colred}ERROR${colrst} --- $@" ;}

function ecrit () { verb_lvl=$crt_lvl elog "${colpur}FATAL${colrst} --- $@" ;}

function edumpvar () { for var in $@ ; do edebug "$var=${!var}" ; done }

function elog() {

if [ $verbosity -ge $verb_lvl ]; then

datestring=`date +"%Y-%m-%d %H:%M:%S"`

echo -e "$datestring - $@"

}

OPTIND=1

while getopts ":sVG" opt ; do

case $opt in

verbosity=$silent_lvl

edebug "-s specified: Silent mode"

;;

verbosity=$inf_lvl

edebug "-V specified: Verbose mode"

;;

verbosity=$dbg_lvl

edebug "-G specified: Debug mode"

;;

esac

done

ewarn "this is a warning"

eerror "this is an error"

einfo "this is an information"

edebug "debugging"

ecrit "CRITICAL MESSAGE!"

edumpvar ORACLE_SID

Example:

$ example.sh -s

1	$ example.sh -s

$ example.sh

1	$ example.sh

$ example.sh -V

1	$ example.sh -V

$ example.sh -G

1	$ example.sh -G

It does not take into account the output file. That will be part of the next tip 🙂

Bash tips & tricks [ep. 3]: Colour your terminal!

Posted on March 18, 2016 by Ludovico

This is the third epidose of a small series.

Description:

The days of monochrome green-on-black screens are over, in a remote shell terminal you can have something fancier!

BAD:

GOOD:

Define a series of variables as shortcuts for color escape codes, there are plenty of examples on internet.

        colblk='\033[0;30m' # Black - Regular
        colred='\033[0;31m' # Red
        colgrn='\033[0;32m' # Green
        colylw='\033[0;33m' # Yellow
        colblu='\033[0;34m' # Blue
        colpur='\033[0;35m' # Purple
        colcyn='\033[0;36m' # Cyan
        colwht='\033[0;37m' # White
        colbblk='\033[1;30m' # Black - Bold
        colbred='\033[1;31m' # Red
        colbgrn='\033[1;32m' # Green
        colbylw='\033[1;33m' # Yellow
        colbblu='\033[1;34m' # Blue
        colbpur='\033[1;35m' # Purple
        colbcyn='\033[1;36m' # Cyan
        colbwht='\033[1;37m' # White
        colublk='\033[4;30m' # Black - Underline
        colured='\033[4;31m' # Red
        colugrn='\033[4;32m' # Green
        coluylw='\033[4;33m' # Yellow
        colublu='\033[4;34m' # Blue
        colupur='\033[4;35m' # Purple
        colucyn='\033[4;36m' # Cyan
        coluwht='\033[4;37m' # White
        colbgblk='\033[40m'   # Black - Background
        colbgred='\033[41m'   # Red
        colbggrn='\033[42m'   # Green
        colbgylw='\033[43m'   # Yellow
        colbgblu='\033[44m'   # Blue
        colbgpur='\033[45m'   # Purple
        colbgcyn='\033[46m'   # Cyan
        colbgwht='\033[47m'   # White
        colrst='\033[0m'    # Text Reset

colblk='\033[0;30m' # Black - Regular

colred='\033[0;31m' # Red

colgrn='\033[0;32m' # Green

colylw='\033[0;33m' # Yellow

colblu='\033[0;34m' # Blue

colpur='\033[0;35m' # Purple

colcyn='\033[0;36m' # Cyan

colwht='\033[0;37m' # White

colbblk='\033[1;30m' # Black - Bold

colbred='\033[1;31m' # Red

colbgrn='\033[1;32m' # Green

colbylw='\033[1;33m' # Yellow

colbblu='\033[1;34m' # Blue

colbpur='\033[1;35m' # Purple

colbcyn='\033[1;36m' # Cyan

colbwht='\033[1;37m' # White

colublk='\033[4;30m' # Black - Underline

colured='\033[4;31m' # Red

colugrn='\033[4;32m' # Green

coluylw='\033[4;33m' # Yellow

colublu='\033[4;34m' # Blue

colupur='\033[4;35m' # Purple

colucyn='\033[4;36m' # Cyan

coluwht='\033[4;37m' # White

colbgblk='\033[40m' # Black - Background

colbgred='\033[41m' # Red

colbggrn='\033[42m' # Green

colbgylw='\033[43m' # Yellow

colbgblu='\033[44m' # Blue

colbgpur='\033[45m' # Purple

colbgcyn='\033[46m' # Cyan

colbgwht='\033[47m' # White

colrst='\033[0m' # Text Reset

Use them whenever you need to highlight the output of a script, and eventually integrate them in a smart prompt (like the one I’ve blogged about sometimes ago).

The echo builtin command requires -e in order to make the colours work. When reading files, cat works, less requires -r. vi may work with some hacking, but it’s not worth to spend too much time, IMHO.

Bash tips & tricks [ep. 2]: Have a smart environment for personal accounts

Posted on March 17, 2016 by Ludovico

This is the second epidose of a small series.

Description:

The main technical account (oracle here) usually has the smart environment, with aliases, scripts avilable at fingertips, correct environment variables and functions.

When working with personal accounts, it may be boring to set the new environment at each login, copy it from a golden copy or reinvent the wheel everytime.

BAD:

Login: ludo
Password:

-bash-4.1$  env
HOSTNAME=testsrv
TERM=xterm
SHELL=/bin/bash
SSH_CLIENT=w.x.y.z 65373 22
OLDPWD=/home/ludo
SSH_TTY=/dev/pts/0
USER=ludo
LS_COLORS=...
MAIL=/var/spool/mail/ludo
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
PWD=/home/ludo
LANG=en_US.UTF-8
HISTCONTROL=ignoredups
SHLVL=1
HOME=/home/ludo
LOGNAME=ludo
LESSOPEN=||/usr/bin/lesspipe.sh %s
_=/bin/env

-bash-4.1$ typeset -f | grep '()'
_module ()
    COMPREPLY=();
_module_avail ()
_module_long_arg_list ()
_module_not_yet_loaded ()
module ()

-bash-4.1$ vi .bash_profile
... damn, let's make this environment smarter
...

Password:

-bash-4.1$ env

HOSTNAME=testsrv

TERM=xterm

SHELL=/bin/bash

SSH_CLIENT=w.x.y.z 65373 22

OLDPWD=/home/ludo

SSH_TTY=/dev/pts/0

USER=ludo

LS_COLORS=...

MAIL=/var/spool/mail/ludo

PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

PWD=/home/ludo

LANG=en_US.UTF-8

HISTCONTROL=ignoredups

SHLVL=1

HOME=/home/ludo

LOGNAME=ludo

LESSOPEN=||/usr/bin/lesspipe.sh %s

_=/bin/env

-bash-4.1$ typeset -f | grep '()'

_module ()

COMPREPLY=();

_module_avail ()

_module_long_arg_list ()

_module_not_yet_loaded ()

module ()

-bash-4.1$ vi .bash_profile

... damn, let's make this environment smarter

...

GOOD:

Distribute a standard .bash_profile that calls a central profile script valid for all the users:

# [ ludo@testsrv:/home/ludo [15:53:18] [12.1.0.2 env:orcl12c] 0 ] #
# cat .bash_profile
# .bash_profile

#################################################
# WARNING: This script is controlled by puppet.
# If you need to override or add something
# please use ~/.bash_profile_local
#################################################

if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi

# load oracle common environment
. /u01/app/oracle/scripts/sbin/ora_profile

[ -f $HOME/.bash_profile_local ] && . $HOME/.bash_profile_local

# [ ludo@testsrv:/home/ludo [15:53:21] [12.1.0.2 env:orcl12c] 0 ] #
#

# [ ludo@testsrv:/home/ludo [15:53:18] [12.1.0.2 env:orcl12c] 0 ] #

# cat .bash_profile

# .bash_profile

#################################################

# WARNING: This script is controlled by puppet.

# If you need to override or add something

# please use ~/.bash_profile_local

#################################################

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# load oracle common environment

. /u01/app/oracle/scripts/sbin/ora_profile

[ -f $HOME/.bash_profile_local ] && . $HOME/.bash_profile_local

# [ ludo@testsrv:/home/ludo [15:53:21] [12.1.0.2 env:orcl12c] 0 ] #

Make your common environment as smart as possible. If any commands need to be run differently depending on the user (oracle or not oracle), just use a simple if:

if [ $USER != "oracle" ] ; then
        alias vioratab='sudoedit -u oracle $ORATAB'
else
        alias vioratab='vi $ORATAB'
fi

if [ $USER != "oracle" ] ; then

alias vioratab='sudoedit -u oracle $ORATAB'

else

alias vioratab='vi $ORATAB'

The goal of course is to avoid as many types as you can, and let all your colleagues profit of the smart environment.

Bash tips & tricks [ep. 1]: Deal with personal accounts and file permissions

Posted on March 16, 2016 by Ludovico

This is the first episode of a mini series of Bash tips for Linux (in case you are wondering, yes, they are respectively my favorite shell and my favorite OS 😉 ).

Episode 1: Deal with personal accounts and file permissions
Episode 2: Have a smart environment for personal accounts
Epidode 3: Colour your terminal!
Episode 4: Use logging levels
Episode 5: Write the output to a logfile
Episode 6: Check the exit code
Episode 7: Cleanup on EXIT with a trap

Description:

Nowadays it is mandatory at many companies to log in on Linux servers with a personal account (either integrated with LDAP, kerberos or whatelse) to comply with strict auditing rules.

I need to be sure that I have an environment where my modifications do not conflict with my colleagues environment.

BAD:

-bash-4.1$ id
uid=20928(ludo) gid=200(dba) groups=200(dba)
-bash-4.1$ ls -lia
total 8
8196 drwxrwxr-x   2 oracle dba  4096 Mar 15 15:14 .
   2 drwxrwxrwt. 14 root   root 4096 Mar 15 15:15 ..
-bash-4.1$ vi script.sh
... edit here...
-bash-4.1$ ls -l
total 4
-rw-r--r-- 1 ludo  dba 8 Mar 15 15:15 script.sh
-bash-4.1$

-bash-4.1$ id

uid=20928(ludo) gid=200(dba) groups=200(dba)

-bash-4.1$ ls -lia

total 8

8196 drwxrwxr-x 2 oracle dba 4096 Mar 15 15:14 .

2 drwxrwxrwt. 14 root root 4096 Mar 15 15:15 ..

-bash-4.1$ vi script.sh

... edit here...

-bash-4.1$ ls -l

total 4

-rw-r--r-- 1 ludo dba 8 Mar 15 15:15 script.sh

-bash-4.1$

the script has been created by me, but my colleagues may need to modify it! So I need to change the ownership:

$ chown oracle:dba script.sh
chown: changing ownership of `script.sh': Operation not permitted
$

$ chown oracle:dba script.sh

chown: changing ownership of `script.sh': Operation not permitted

But I can only change the permissions:

$ chmod 775 script.sh
$

1 2	$ chmod 775 script.sh $

If I really want to change the owner, I have to ask to someone that has root privileges or delete the file with my account and create it with the correct one (oracle or something else).

GOOD:

Set the setgid bit at the directory level
Define an alias for my favorite editor that use sudoedit instead:

$ chmod 2751 .
$ ls -lia
total 4
8196 drwxr-s--x 2 oracle dba  4096 Mar 15 15:26 .
$ alias vi='SUDO_EDITOR=/usr/bin/vim sudoedit -u oracle '
$ vi script.sh
[sudo] password for ludo:
... edit here ...
$ ls -l script.sh
total 8
-rw-r--r-- 1 oracle dba 6 Mar 15 15:24 script.sh
$

$ chmod 2751 .

$ ls -lia

total 4

8196 drwxr-s--x 2 oracle dba 4096 Mar 15 15:26 .

$ alias vi='SUDO_EDITOR=/usr/bin/vim sudoedit -u oracle '

$ vi script.sh

[sudo] password for ludo:

... edit here ...

$ ls -l script.sh

total 8

-rw-r--r-- 1 oracle dba 6 Mar 15 15:24 script.sh

In case I need to modify other files with MY account, I can either use the full path (/usr/bin/vim) or define another alias:

alias vime="/usr/bin/vim"

1	alias vime="/usr/bin/vim"

How cold incremental recovery saved me once

Posted on March 15, 2016 by Ludovico

UPDATE: In the original version I was missing a few keywords: “incremental level 0” for the base backup and “resetlogs” at the database open. Thanks Gregorz for your comments.

Sorry for this “memories” post, but the technical solution at the end is worth the read, I hope 😉

Back in 2010, I was in charge of a quite complex project and faced some difficulties that led me to recover a database in a different manner. A few years have passed, but I used again the same procedure many times with full satisfaction… I think it’s worth to publish it now.

But first, let me introduce the project details and the problem.

Scope of the project

Transport a >1TB RAC database from AIX 5 on P6 to AIX 6 on P7, from a third-party datacenter in southern Italy to our main datacenter in northern Italy.
The Database featured >1000 datafiles and a huge table (800GB) partitioned by range and sub-partitioned by list (or the opposite, can’t remember).

Challenges

For budget containement, the project owner asked to avoid the use of HACMP (and thus, avoid the use of shared JFS2). I decided then to take the risk and migrate from JFS2 to ASM.

In order to avoid a few platform-related ASM bugs, I also had to upgrade from Oracle 10.2.0.3 to Oracle 10.2.0.4.

Constraints

I had no access to the source database that was 800km far from our datacenter, and I was granted only to ask for RMAN backups.

The total time of service disruption accepted was quite short (<30 minutes) considering the size and the distance of the database, and there was no direct connectivity between the sites (for political reasons).

Globally, the network throughput for sharing files over ftp was very poor.

First solution

This kind of move was very common to me, and because I was not grated to ask for a temporary Data Guard configuration, the easy solution for me was to ask:

1 – one RMAN ONLINE full backup physically sent on disk

2 – many RMAN archive backups sent over network (via ftp)

Then, on my side, restore the full backup, recover the archives sent over time and, at the date X, ask a final archive backup, ask to close the db and send the online redo logs to do a complete recovery on my side, then startup open upgrade.

Problem

I did a first “dry run” open resetlogs in order to test the procedure and make it faster, and also asked to test the application pointing to the destination database.

The very bad surprise was that the source database was doing a huge amount of nologging inserts leading to monster index corruptions after the recovery on the destination database.

ORA-26040: Data block was loaded using the NOLOGGING option

1	ORA-26040: Data block was loaded using the NOLOGGING option

According to the current database maintainer, setting the force logging on the source database was NOT an option because the SAN was not able to cope with the high redo rates.

Solution

By knowing the Oracle recovery mechanisms, I have proposed to the remote maintainer to change the recovery strategy, despite this solution was not clearly stated in the Oracle documentation:

1 – Take a first online incremental backup from the begin scn of the base full backup (thank God block change tracking was in place) and send it physically over disk

2 – Take other smaller online incremental backups, send them over ftp and apply them on the destination with “noredo”

3 – At the date X, shutdown the source, mount it and take a last incremental in mount state

4 – recover noredo the last incremental and open resetlogs the database.

According to the documentation, the “cold incremental strategy” applies if you take “cold full backups”. But from a technical point of view, taking a cold incremental and recovering it on top of a fuzzy online backup this is 100% equivalent of taking a full consistent backup in mount state.
Because all the blocks are consistent to a specific SCN, there are no fuzzy datafiles: they are recovered from incremental taken from a mounted database! This allows to do incremental recovery and open the databases without applying any single archived log and by shutting down the database only once.

Technical steps

First, take a full ONLINE backup on the source:

-- SOURCE
SQL> alter database backup controlfile to '/tmp/source/ludo.cf' reuse;

Database altered.

SQL> exit
$ rman target /
RMAN> backup incremental level 0 database as compressed backupset format '/tmp/source/%U';

-- SOURCE

SQL> alter database backup controlfile to '/tmp/source/ludo.cf' reuse;

Database altered.

SQL> exit

$ rman target /

RMAN> backup incremental level 0 database as compressed backupset format '/tmp/source/%U';

# SOURCE
scp -rp /tmp/source/ destsrv:/tmp/dest/
ludo.cf              100% |*************************************| 40944 KB    00:00
...

# SOURCE

scp -rp /tmp/source/ destsrv:/tmp/dest/

ludo.cf 100% |*************************************| 40944 KB 00:00

...

Then restore it on the destination (with no recovery):

# DEST
RMAN> restore controlfile from '/tmp/ludo.cf';

Starting restore at 11-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1058 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/.../control01.ctl
output file name=/.../control02.ctl
Finished restore at 11-AUG-15

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> catalog start with '/tmp/dest/';
...
RMAN> run
2> {
3> set newname for database to '+DATA';
4>
5> restore database;
6> }
...
Finished restore at 11-AUG-15
RMAN>

# DEST

RMAN> restore controlfile from '/tmp/ludo.cf';

Starting restore at 11-AUG-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1058 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/.../control01.ctl

output file name=/.../control02.ctl

Finished restore at 11-AUG-15

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

RMAN> catalog start with '/tmp/dest/';

...

RMAN> run

2> {

3> set newname for database to '+DATA';

5> restore database;

6> }

...

Finished restore at 11-AUG-15

RMAN>

Then, run a COLD incremental backup on the source:

-- SOURCE
SQL> shutdown immediate;
...
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
...
Database mounted.
SQL> exit
$ rman target /
RMAN>  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 
2> CUMULATIVE DATABASE format '/tmp/source/incr%U';
...
Finished backup at 11-AUG-15
RMAN> exit
$ scp -rp /tmp/source/incr* destsrv:/tmp/dest/

-- SOURCE

SQL> shutdown immediate;

...

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

...

Database mounted.

SQL> exit

$ rman target /

RMAN> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1

2> CUMULATIVE DATABASE format '/tmp/source/incr%U';

...

Finished backup at 11-AUG-15

RMAN> exit

$ scp -rp /tmp/source/incr* destsrv:/tmp/dest/

And run the incremental recovery on the source (without redo):

# DEST
RMAN> catalog start with '/tmp/dest/incr';
...
RMAN> run {
2> recover database noredo;
3> }
...
channel ORA_DISK_1: starting incremental datafile backup set restore
...
Finished recover at 11-AUG-15
RMAN> exit
$ sqlplus / as sysdba
...
SQL> alter database disable block change tracking;
Database altered.
SQL> alter database flashback off;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> create restore point PREUPG guarantee flashback database;
Restore point created.
SQL> -- open resetlogs can be avoided if I copy the online redo logs
SQL> alter database open resetlogs upgrade;
Database altered.
...
-- run catupgrd here

# DEST

RMAN> catalog start with '/tmp/dest/incr';

...

RMAN> run {

2> recover database noredo;

3> }

...

channel ORA_DISK_1: starting incremental datafile backup set restore

...

Finished recover at 11-AUG-15

RMAN> exit

$ sqlplus / as sysdba

...

SQL> alter database disable block change tracking;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> create restore point PREUPG guarantee flashback database;

Restore point created.

SQL> -- open resetlogs can be avoided if I copy the online redo logs

SQL> alter database open resetlogs upgrade;

Database altered.

...

-- run catupgrd here

That’s all!

This solution gave me the opportunity to move physically the whole >1TB nologging database from one region to another one with a minimal service disruption and without touching at all the source database.

I used it many times later on, even for bigger databases and on several platforms (yes, also Windows, sigh), it works like a charm.

HTH

—

Ludovico

Getting the DBID and Incarnation from the RMAN Catalog

Posted on February 15, 2016 by Ludovico

Using the RMAN catalog is an option. There is a long discussion between DBAs on whether should you use the catalog or not.

But because I like (a lot) the RMAN catalog and I generally use it, I assume that most of you do it 😉

When you want to restore from the RMAN catalog, you need to get the DBID of the database you want to restore and, sometimes, also the incarnation key.

The DBID is used to identify the database you want to restore. The DBID is different for every newly created / duplicated database, but beware that if you duplicate your database manually (using restore/recover), you actually need to change your DBID using the nid tool, otherwise you will end up by having more than one database registered in the catalog with the very same DBID. This is evil! The DB_NAME is also something that you may want to make sure is unique within your database farm.

The Incarnation Key changes whenever you do an “open resetlogs”, following for example a flashback database, an incomplete recovery, or just a “open resetlogs” without any specific need.

In the image, you can see that you may want to restore to a point in time after the open resetlogs (blue incarnation) or before it (red incarnation). Depending on which one you need to restore, you may need to use the command RESET DATABASE TO INCARNATION.

https://docs.oracle.com/database/121/RCMRF/rcmsynta2007.htm#RCMRF148

If you have a dynamic and big environment, you probably script your restores procedures, that’s why getting the DBID and incarnation key using the RMAN commands may be more complex than just querying the catalog using sqlplus.

How do I get the history of my database incarnations?

You can get it easily for all your databases using the handy hierarchical queries on the RMAN catalog (db names and ids are obfuscated for obvious reasons):

SQL> SELECT lpad(' ',2*(level-1))
  || TO_CHAR(DBINC_KEY) AS DBINC_KEY,
  db_key,
  db_name,
  TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),
  dbinc_status
FROM rman.dbinc
  START WITH PARENT_DBINC_KEY IS NULL
  CONNECT BY prior DBINC_KEY   = PARENT_DBINC_KEY ;

DBINC_KEY                     DB_KEY DB_NAME    TO_CHAR(RESET_TIME, DBINC_ST
------------------------- ---------- ---------- ------------------- --------
356247416                  356247380 A9EE272A   2011-09-24 18:22:58 PARENT
  356247387                356247380 A9EE272A   2012-10-24 08:41:41 PARENT
    1149458631             356247380 A9EE272A   2014-10-10 08:30:57 CURRENT
360319357                  360319322 F5FD787F   2011-10-14 15:39:19 PARENT
  360319323                360319322 F5FD787F   2012-11-08 18:57:26 PARENT
    547928008              360319322 F5FD787F   2013-09-10 10:57:44 PARENT
      576592237            360319322 F5FD787F   2013-11-20 14:54:05 ORPHAN
      576613820            360319322 F5FD787F   2013-11-20 15:57:03 ORPHAN
      584503796            360319322 F5FD787F   2013-11-27 13:57:53 CURRENT
364099232                  364099231 25E64A7F   2012-11-20 08:01:49 PARENT
  415031968                364099231 25E64A7F   2013-02-15 12:16:15 PARENT
    456099512              364099231 25E64A7F   2013-05-03 12:19:52 CURRENT
366065362                  366065336 3AE45141   2011-09-24 18:22:58 PARENT
  366065337                366065336 3AE45141   2012-11-26 17:14:14 CURRENT
394067322                  394067321 C34FFA7E   2013-01-10 17:18:11 CURRENT
402469086                  402469073 D164DDB8   2011-09-24 18:22:58 PARENT
  402469074                402469073 D164DDB8   2013-01-29 11:20:19 CURRENT
410147332                  410147283 27984513   2011-09-24 18:22:58 PARENT
  410147284                410147283 27984513   2013-02-08 11:12:38 CURRENT
...
...

SQL> SELECT lpad(' ',2*(level-1))

|| TO_CHAR(DBINC_KEY) AS DBINC_KEY,

db_key,

db_name,

TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),

dbinc_status

FROM rman.dbinc

START WITH PARENT_DBINC_KEY IS NULL

CONNECT BY prior DBINC_KEY = PARENT_DBINC_KEY ;

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

------------------------- ---------- ---------- ------------------- --------

356247416 356247380 A9EE272A 2011-09-24 18:22:58 PARENT

356247387 356247380 A9EE272A 2012-10-24 08:41:41 PARENT

1149458631 356247380 A9EE272A 2014-10-10 08:30:57 CURRENT

360319357 360319322 F5FD787F 2011-10-14 15:39:19 PARENT

360319323 360319322 F5FD787F 2012-11-08 18:57:26 PARENT

547928008 360319322 F5FD787F 2013-09-10 10:57:44 PARENT

576592237 360319322 F5FD787F 2013-11-20 14:54:05 ORPHAN

576613820 360319322 F5FD787F 2013-11-20 15:57:03 ORPHAN

584503796 360319322 F5FD787F 2013-11-27 13:57:53 CURRENT

364099232 364099231 25E64A7F 2012-11-20 08:01:49 PARENT

415031968 364099231 25E64A7F 2013-02-15 12:16:15 PARENT

456099512 364099231 25E64A7F 2013-05-03 12:19:52 CURRENT

366065362 366065336 3AE45141 2011-09-24 18:22:58 PARENT

366065337 366065336 3AE45141 2012-11-26 17:14:14 CURRENT

394067322 394067321 C34FFA7E 2013-01-10 17:18:11 CURRENT

402469086 402469073 D164DDB8 2011-09-24 18:22:58 PARENT

402469074 402469073 D164DDB8 2013-01-29 11:20:19 CURRENT

410147332 410147283 27984513 2011-09-24 18:22:58 PARENT

410147284 410147283 27984513 2013-02-08 11:12:38 CURRENT

...

What about getting the correct DBID/DBINC_KEY pair for a specific database/time?

You can get the time windows for each incarnation using the lead() analytical function:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( 
  dbinc.db_key   =db.db_key)
  )
select * from (
SELECT DBINC_KEY,
  db_name,
  db_id,
  reset_time,
  nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
FROM dbids
)
ORDER BY db_name ,
  reset_time ;  

DBINC_KEY                 DBNAME          DB_ID RESET_TIME          NEXTRESET
------------------------- ---------- ---------- ------------------- -------------------
1173852671                1DF63C30   2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01
1173852635                1DF63C30   2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13
1244346785                1DF63C30   2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13
1281775847                1DF63C30   2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13
1233975755                1DF63C30   2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13
1220896058                1DF63C30   2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00
1188550385                1DF63C30   2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00
1220896028                1DF63C30   2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53
1233975725                1DF63C30   2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34
1244346755                1DF63C30   2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03
1281775817                1DF63C30   2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49
1201139592                1D0776F6   2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50
1201139578                1D0776F6   2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07
1213295265                1D0776F6   2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07
1256000477                1D0776F6   2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07
1235940868                1D0776F6   2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38
1213295230                1D0776F6   2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34
1235940852                1D0776F6   2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52
1256000461                1D0776F6   2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49
1173653066                2D828C2C   1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04
1173653052                2D828C2C   1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07
1247872446                2D828C2C   1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07
1218354231                2D828C2C   1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07
1278227063                2D828C2C   1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07
1219084145                2D828C2C   1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07
1228714578                2D828C2C   1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07
1211451469                2D828C2C   1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07
1235422982                2D828C2C   1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07
1228713810                2D828C2C   1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07
1240749487                2D828C2C   1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07
1255743496                2D828C2C   1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08
1211451453                2D828C2C   1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20
1218354215                2D828C2C   1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19
1219084129                2D828C2C   1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40
1228713794                2D828C2C   1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27
1228714562                2D828C2C   1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58
1235422966                2D828C2C   1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29
1240749471                2D828C2C   1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55
1247872430                2D828C2C   1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27
1255743480                2D828C2C   1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29
1278227047                2D828C2C   1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON (

dbinc.db_key =db.db_key)

)

select * from (

SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

ORDER BY db_name ,

reset_time ;

DBINC_KEY DBNAME DB_ID RESET_TIME NEXTRESET

------------------------- ---------- ---------- ------------------- -------------------

1173852671 1DF63C30 2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01

1173852635 1DF63C30 2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13

1244346785 1DF63C30 2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13

1281775847 1DF63C30 2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13

1233975755 1DF63C30 2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13

1220896058 1DF63C30 2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00

1188550385 1DF63C30 2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00

1220896028 1DF63C30 2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53

1233975725 1DF63C30 2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34

1244346755 1DF63C30 2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03

1281775817 1DF63C30 2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49

1201139592 1D0776F6 2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50

1201139578 1D0776F6 2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07

1213295265 1D0776F6 2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07

1256000477 1D0776F6 2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07

1235940868 1D0776F6 2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38

1213295230 1D0776F6 2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34

1235940852 1D0776F6 2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52

1256000461 1D0776F6 2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49

1173653066 2D828C2C 1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04

1173653052 2D828C2C 1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07

1247872446 2D828C2C 1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07

1218354231 2D828C2C 1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07

1278227063 2D828C2C 1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07

1219084145 2D828C2C 1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07

1228714578 2D828C2C 1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07

1211451469 2D828C2C 1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07

1235422982 2D828C2C 1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07

1228713810 2D828C2C 1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07

1240749487 2D828C2C 1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07

1255743496 2D828C2C 1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08

1211451453 2D828C2C 1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20

1218354215 2D828C2C 1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19

1219084129 2D828C2C 1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40

1228713794 2D828C2C 1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27

1228714562 2D828C2C 1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58

1235422966 2D828C2C 1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29

1240749471 2D828C2C 1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55

1247872430 2D828C2C 1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27

1255743480 2D828C2C 1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29

1278227047 2D828C2C 1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

With this query, you can see that every incarnation has a reset time and a “next reset time”.

It’s easy then to get exactly what you need by adding a couple of where clauses:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY
    --AND
    dbinc.db_key =db.db_key)
  )
SELECT *
FROM
  (SELECT DBINC_KEY,
    db_name,
    db_id,
    reset_time,
    NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
  FROM dbids
  )
WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset
AND db_name='1465419F'
ORDER BY db_name ,
  reset_time ; 

DBINC_KEY                 DB_NAME         DB_ID RESET_TIME          NEXT_RESET
------------------------- ---------- ---------- ------------------- -------------------
1256014297                1465419F   1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY

--AND

dbinc.db_key =db.db_key)

)

SELECT *

FROM

(SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset

AND db_name='1465419F'

ORDER BY db_name ,

reset_time ;

DBINC_KEY DB_NAME DB_ID RESET_TIME NEXT_RESET

------------------------- ---------- ---------- ------------------- -------------------

1256014297 1465419F 1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

So, if I need to restore the database 1465419F until time 2016-01-20 00:00:00, i need to set DBID=1048383773 and reset the database to incarnation 1256014297.

Cheers

—

Ludo

2015 in numbers…

Posted on January 31, 2016 by Ludovico

I am spending good times since I have moved in Switzerland 3 years ago. 2015 has been as good as 2014. Now that January ends, I am officially late in publishing this information, but it’s MY blog, so who cares? 😛

A few numbers:

25 blog posts (+1)
~52000 page views (+40%), ~43000 visits (+48%)
Speaker at 2 major conferences (#C15LV, #UKOUG_TECH15)
Speaker at 2 Trivadis internal conferences
Speaker at 1 local user group event
Delegate of EOUC at DOAG 2015
A total of 14 public speeches (same as 2014)
I’ve been elected RAC SIG Vice President
2 RAC Attack workshops organized
1 roundtable as organizer, 1 panel
2 T-shirt designed as gifts for 2 RAC Attack workshops
2 articles published
Launched the RAC SIG website and the new session agenda
Countless new friends and/or contacts

I hope that 2016 will be as good as 2015

Configuring the MySQL Database Plug-In for Oracle Enterprise Manager 12c

Posted on January 31, 2016 by Ludovico

I have blogged in the past about MySQL Enterprise Monitor 3.0 and I was quite happy at the very beginning, but after a while I have to admit that I was missing many of the Oracle Enterprise Manager 12c features.

In particular, MEM 3.0 does not have a usable database. In MEM all the tables are crypted and it is not possible to list, for example, all the targets monitored, nor it is possible via API or REST web services because MEM 3.0 lacks these features.

What makes EM12c a GREAT product comparing to MEM, are many features like blackouts, a usable command line interface (emcli), integrated reporting, scheduler, automatic groups… the list would be just huge.

Luckily, Oracle has officially released a MySQL plugin for EM12c, provided that the EM is at least in version 12.1.0.4.

So I’ve upgraded (a while ago) my customer’s EM12c to 12.1.0.5. and decided to try the plugin.

The first step is to download the last version of plugin for MySQL.

I can verify that you have the last version by going to

Setup -> Extensibility -> Self-Update -> Plugins:

The agent has been downloaded, but in order to make it available on the targets, I first need to deploy it on the management servers (2 OMSes in my case):