Skip navigation

Hello everybody,

 

normally you have to use a SVN or similar tools to manage change on code. If this is the case with your data model as well, then you may find useful to be able to download a bunch of files directly to the server where you're going to apply them instead of going one by one. That's the intended use of this script.

 

#!/usr/bin/ksh
###########################
##
## File: svnget.sh
##
## Author: Jesus Sanchez (jsanchez.consultant@gmail.com)
##
## Changelog:
## 2014-10-10 Jesus Sanchez Created this script
## 2015-01-12 Jesus Sanchez Added directory support
##
######################################################################
###############
## FUNCTIONS ##
###############
#####################
# Utility Functions #
#####################
export LAUNCHDIR=$(pwd)
export SOURCEDIR=$( cd -P -- "$(dirname -- "$(command -v -- "$0")")" && pwd -P )
echo $SOURCEDIR
source $SOURCEDIR/utility_functions.sh
source $SOURCEDIR/oracle_utilities.sh
if [[ -d $SOURCEDIR/tmp ]]
then
mkdir -p $SOURCEDIR/tmp
fi
export TEMPDIR=$SOURCEDIR/tmp
#####################
## OTHER FUNCTIONS ##
#####################
##########################
#
# Function name: printUsage
#
# Description:
# This function prints the help about using this script
#
# Usage: printUsage
#
##############################################
function printUsage {
msgPrint -title HELP
msgPrint -blank
msgPrint -none "Usage: svnget.sh -d <DIRECTORY_PATH> | -f <FILE_NAME> | -s <LINK_TO_FILE>"
msgPrint -none "OPTIONS:"
msgPrint -none " d : Directory mode. Will download everything 1 level below the directory you provide"
msgPrint -none " f : File mode. Will read all links inside a file and ownload them all"
msgPrint -none " s : single link mode. Will download a single file pointed by the link you provide"
}
####################
## MAIN ALGORITHM ##
####################
# Variables
mode="SINGLE"
print -n "Please enter your SVN user: "
read SVNUSER;
print -n "Please enter your SVN password: "
stty -echo
read SVNPASS
stty echo
# Get arguments sorted out with getArgs (GA_OPTIONS + GA_VALUES)
. getArgs $*
# Process the arguments we got
counter=1
for option in ${GA_OPTIONS[*]}
do
msgPrint -debug "Main Algorithm 27" "Processing argument ${GA_OPTS[$counter]}"
case ${GA_OPTIONS[$counter]} in
("-d")
msgPrint -info "Running in directory mode"
mode="DIRECTORY"
directory=${GA_VALUES[$counter]};;
("-f")
msgPrint -info "Running in file mode!"
mode="FILE"
filename=${GA_VALUES[$counter]};;
("-s")
msgPrint -info "Running in single link mode!"
mode="SINGLE"
httplink=${GA_VALUES[$counter]};;
(*)
msgPrint -warning "UNKOWN argument, please check usage and try again"
msgPrint -separator
printUsage;;
esac
(( counter+=1 ))
done
# Separated for legibility
case $mode in
("DIRECTORY")
if [[ -z $directory ]]
then
msgPrint -critical "Can't continue, no directory specified"
exit 1
else
msgPrint -info "Attempting to download all files in $directory"
wget --user=$SVNUSER --password=$SVNPASS -q --no-parent --no-directories -r -l 1 $directory
if [[ $? -eq 0 ]]
then
msgPrint -blank
msgPrint -info "Directory download successful!"
else
msgPrint -error "Directory download failed!"
msgPrint -error "Please check your options and try again"
printUsage
exit 1
fi
fi
;;
("FILE")
if [[ -z $filename ]]
then
msgPrint -critical "Can't continue, no filename specified"
exit 1
else
msgPrint -info "Attempting to download all links in $filename"
wget --user=$SVNUSER --password=$SVNPASS --input-file=$filename
if [[ $? -eq 0 ]]
then
msgPrint -blank
msgPrint -info "File links download successful!"
else
msgPrint -error "File links download failed!"
msgPrint -error "Please check your options and try again"
printUsage
exit 1
fi
fi
;;
("SINGLE")
if [[ -z $httplink ]]
then
msgPrint -critical "Can't continue, no link specified"
exit 1
else
msgPrint -info "Attempting to download all files in $directory"
wget --user=$SVNUSER --password=$SVNPASS $httplink
if [[ $? -eq 0 ]]
then
msgPrint -blank
msgPrint -info "File links download successful!"
else
msgPrint -error "File links download failed!"
msgPrint -error "Please check your options and try again"
printUsage
exit 1
fi
fi
;;
(*)
msgPrint -critical "No idea how we got here!"
printUsage
;;
esac
exit 0

 

Enjoy!

Hello everyone,

 

sometimes you're required to check when was last used a database. For this, if you're auditing the logons (and you should), you can just run the following query to get the information.

 

column username format a30
column last_login format a25
with audit_records as (
  select username, NVL(TO_CHAR(MAX(logoff_time),'MM-DD-YYYY'),'No audit records found') "LAST_LOGIN"
  from sys.dba_audit_session
  group by username
  )
select du.username, ar.LAST_LOGIN
from sys.dba_users du
left outer join audit_records ar
on (du.username = ar.username)
order by 1;

 

I hope this help some of you out there.

Hello everyone,

 

Today I would like to share something with you. In my day to day work, I have encountered more than one place where DEV environments are spread through only some hosts. This normally leads to creating several databases on the same host and to create several listeners to be able to split communication through several ports.

 

Having this, after OS patches or any OS related activity that would require the database software to be shutdown, it's an usual case that people forget to start listeners.  For this I have this useful script:

 

#!/bin/ksh

 

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

##

##    File name: all_lsnr_check.sh

##    author: Jesus Alejandro Sanchez

##

##        This script allows you to scan the /etc/oratab file and look for all Oracle Homes defined

##    there to access the listener.ora file and scan it for all listener names. Once that is done,

##    The script will show you the status of each listener and wait for your input to START or STOP

##    the listener or just continue to the next one.

##

##     Pre-requisites: Set the Oracle environment

##

##    05/26/2014        Version 1.0        Jesus Alejandro Sanchez        First version of the working script

##

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

 

#    Look for all oracle homes in /etc/oratab

for CURRENT_HOME in `cat /etc/oratab | grep :/ | grep -v agent | cut -d":" -f2 | sort | uniq`

do

    echo " "

    echo " "

    echo "USING $CURRENT_HOME as the current oracle home"

    echo "==========================================================================="

    ORACLE_HOME=$CURRENT_HOME

    for CURRENT_LSNR in `cat $ORACLE_HOME/network/admin/listener.ora | grep LISTENER |grep -v SID | grep -v ADR |grep -v "#" |cut -d"=" -f1 | sort | uniq`

    do

        echo " "

        echo "CHECKING $CURRENT_LSNR"

        echo "-------------------------------------------"

        $ORACLE_HOME/bin/lsnrctl status $CURRENT_LSNR

        ACTION="read it";

        while [[ ! -z $ACTION ]]; do

            echo " "

            echo "********************************************"

            echo "Type START to start the current listener"

            echo "type STOP to stop the current listener"

            echo "or press <ENTER> to continue"

            read ACTION

            case $ACTION in

                START|start)

                    $ORACLE_HOME/bin/lsnrctl start $CURRENT_LSNR

                    $ORACLE_HOME/bin/lsnrctl status $CURRENT_LSNR

                    ;;

                STOP|stop)

                    $ORACLE_HOME/bin/lsnrctl stop $CURRENT_LSNR

                    $ORACLE_HOME/bin/lsnrctl status $CURRENT_LSNR

                    ;;

                *)

                    ACTION=

                    ;;

            esac

        done

    done

done

 

Enjoy.

 

Hope this helps in making your job easier.

Hello fellow DBAs and Oracle enthusiasts.

 

Today I'm going to share the script I use to "source" utility functions to the shell scripts I commonly use for my daily DBA tasks.

 

https://github.com/Silvarion/generic-shell-script/blob/master/utility_functions.sh

 

Let's add some explanation:

 

  • continueQuestion
    • This function is a simple "Continue? (y/n)" question. As this is used a lot in interactive scripts, seemed like a good idea for me to add it to the utility functions script.
  • debugPrint
    • For debugging, it adds the timestamp prior to the message. Allows you to keep track of what, where and when something is happening.
  • msgPrint
    • Allows you to format in several ways the output that you want to send to console/log file.

 

I hope you find this useful and makes your job better!

Hello everyone,

 

This is my first post on the oracle blogs section!

 

I really hope the posts I will publish here help some of you to have a more dynamic and comfortable work.

 

This time I'm publishing a simple KSH script that can allow you to apply a single command or a SQL script to all running databases in a single host.

 

#!/bin/ksh

 

 


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

##

## File: apply_to_all.ksh

##

## Author: Jesus Sanchez (jsanchez.consultant@gmail.com)

##

## Changelog:

## 2015-05-11     Jesus Sanchez     Created this script

##

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

 

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

## FUNCTIONS ##

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


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

# Utility Functions #

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

export LAUNCH_DIR=`pwd`

export SOURCE_DIR=$( cd -P -- "$(dirname -- "$(command -v -- "$0")")" && pwd -P )

source $SOURCE_DIR/utility_functions.ksh

export TEMPDIR=$LAUNCH_DIR

 


function printUsage

{

  print "USAGE: this script has exlusive modes for running."

  print "FILE MODE: PATH/TO/SCRIPT/apply_to_all_db.ksh -f <FILENAME>"

  print "COMMAND MODE: PATH/TO/SCRIPT/apply_to_all_db.ksh -c <DOUBLE-QUOTED COMMAND ENDED BY SEMICOLON>"

}


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

# Function: crawl

#

# Description: this function will look for pmon running processes,

#      gather instance names and "crawl" through them, running the

#      command/SQL file on each instance on the same host.

#

# Usage:

#

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

function crawl {

## Crawl through databases and execute command/file

  for DBNAME in `ps -ef | grep pmon | cut -d"_" -s -f3 | grep -v ASM`

  do

  . oraenv <<_EOORA_

  ${DBNAME}

_EOORA_

  sqlplus / as sysdba<<_EOSQL_

  select global_name from global_name;

  !echo "[INFO] About to execute: $1";

  $1

  exit

_EOSQL_

  done

  msgPrint -info "All done!!!"

}

 

 

## MAIN ALGORITHM ##


## Check parameters ##

if [[ -z $2 ]];

then

  printUsage

else

  if [[ $1 = "-f" ]];

  then

       msgPrint -info "Using file mode"

       crawl "@${2}"

  elif [[ $1 = "-c" ]];

  then

       msgPrint -info "Using command mode"

       crawl "${2}"

  else

       printUsage

  fi

fi


exit 0

Now let's explain each part:

  • Functions
    • Utility Functions: refer to this post to check the utility functions I normally use.
      • You'll see debugPrint and msgPrint in most of my shell scripts. Those are used to give a nice format to the message I'm sending to the console/log.
    • Crawl
      • This is the main function that receives the command/sql file to be run in every instance.
  • Main Algorythm
    • for DBNAME in `ps -ef | grep pmon | cut -d"_" -s -f3 | grep -v ASM
      • Here, we cycle through all the running instances in the host. Looking for pmon processes and getting only the INSTANCE name from it.
    • crawl "@${2}"
      • In the SQL script mode see how the argument passed to the crawl function is appended to the @ sign, so that we don't have to check for it when running inside the function.
    • crawl "${2}"
      • In the command mode we pass the argument inside double quotes so that it is considered a single argument by the function while running.


With some light modifications, we can get this same schema to work with any of the command line utilities from Oracle. But that's a future project of mine. I will try to add support for RMAN in the next few months as my work allows me to.


Hope that some of you find this post useful.


Quote of the post:

When I started Oracle, what I wanted to do was to create an environment where I would enjoy working.

Larry Ellison