All posts by Vincent

Oracle DBA OCP 10g, 9i, 8 Electronic music producer

Resources waits

REM # Creator: Vincent Fenoll – DBA Oracle Montreal

REM ################################################
REM # Created: 2005/01/01
REM # Name: resources_waits.sql
REM  ################################################
REM #
REM # Compatible: Oracle 7 8i 9i 10g 11g
REM#
REM ################################################
REM #
REM # Display importants waits events in our Oracle databases in Montreal
REM #
REM ################################################
column "Average wait cs" format 9999990.00 heading "Attente moyenne|en cent-secondes"
column "Time waited seconds" format 999,999,999,990.00 heading "Temps attendu|en secondes"
colum event format a35 heading "Evenement" truncate
select
  substr(e.event, 1, 40)  event,
  e.time_waited/100 "Time waited seconds",
  e.time_waited / decode(
    e.event,
    'latch free', e.total_waits,
    decode(
      e.total_waits - e.total_timeouts,
      0, 1,
      e.total_waits - e.total_timeouts
    )
  ) "Average wait cs"
from
  sys.v_$system_event  e,
  sys.v_$instance  i
where
  e.event = 'buffer busy waits' or
  e.event = 'enqueue' or
  e.event = 'free buffer waits' or
  e.event = 'global cache freelist wait' or
  e.event = 'latch free' or
  e.event = 'log buffer space' or
  e.event = 'parallel query qref latch' or
  e.event = 'pipe put' or
  e.event = 'write complete waits' or
  e.event like 'library cache%' or
  e.event like 'log file switch%' or
  e.event = 'log file sync' or
  ( e.event = 'row cache lock' and
    i.parallel = 'NO'
  )
order by "Time waited seconds" desc
/

cpu_apply_Mass.sh

#!/usr/bin/ksh
# SCRIPT: cpu_apply_Mass.sh
################################################
# Creator: Yoann Mainguy
# Update: Vincent Fenoll
# Created: 2009/01/01
# Compatible: 10g 11g
################################################
#
# Apply cpu patch on several Oracle databases
# using a for loop
#
################################################

for NO_SID in SID1 SID2 SID3
do
export ORACLE_SID=$NO_SID
export ORAENV_ASK=NO
. oraenv
echo ”
set echo off pages 1000 lines 1000
startup
select instance_name from v\$instance;
@?/rdbms/admin/catbundle cpu apply
@?/rdbms/admin/utlrp
” | sqlplus -s “/ as sysdba” > /tmp/$ORACLE_SID.log &
done

################################################
#
# Recompile views if needed
#
################################################

for NO_SID in SID1 SID2 SID3
do
export ORACLE_SID=$NO_SID
export ORAENV_ASK=NO
. oraenv

#VALUE=`sqlplus -s “/ as sysdba” < /tmp/RECOMP_$ORACLE_SID.log &
#else
# echo “Views already compiled for this database” > /tmp/RECOMP_$ORACLE_SID.log &
#fi
done

Duplicate db using Shell & RMAN

# Author: Vincent Fenoll another Oracle DBA Montreal

#!/usr/bin/ksh
# SCRIPT: duplicate_db_from_time.sh
################################################
#
# Created: 2009/01/01
# Compatible: Oracle 8i 9i 10g 11g
#
################################################
#
# Duplicate database using rman
#
################################################
#
# PRE-REQUISITES:
#    * Instance: NOMOUNT
#    * local variables setted
#    * Oracle variables setted: log_file_name_convert, db_file_name_convert
#
# EXAMPLE:    ./duplicate_db_from_time.sh sys/***@OLDBASE rman/***@rman "08-03-2009 06:16:49″ host_source
#
# Please modify with your value  :      duplicate target database to NEWBASE
#
################################################


if [ "$ORACLE_HOME" = "" ]; then
        echo "Erreur: ORACLE_HOME must be defined"
        exit 1
fi

if [ "$1" = "" -o "$2" = "" -o "$3" = "" -o "$4" = "" ]; then
        echo "Synt: $0 [connect_target] [connect_rmancatalog] [date=DD-MM-YYYY HH24:MI:SS] [server_source_name]"
        exit 1
fi

export NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"

$ORACLE_HOME/bin/rman target $1 rcvcat $2 auxiliary / << EOF run { set until time "to_date('$3', 'DD-MM-YYYY HH24:MI:SS')"; allocate auxiliary channel t1 type 'SBT_TAPE'; -- If using Networker send 'NSR_ENV=(NSR_SERVER=networkerxxx, NSR_DATA_VOLUME_POOL=FULLxxx, NSR_CLIENT=$4)'; set command id to 'rman_duplicate_from_time'; duplicate target database to NEWBASE NOFILENAMECHECK; release channel t1; } exit; EOF egrep "RMAN-|ORA-" /tmp/resto_rman_$$.log > /dev/null
  if [ $? -eq 0 ]
  then
    return 12
  fi

  return 0