ITRemote

Remote IT Services

This piece of code transforms the variable as its printed to lowercase

 

print $ORACLE_SID|tr '[A-Z]' '[a-z]'

 

 

In order to get set -o vi
This should be set at the .kshrc or .bashrc level

# M Culp
# .kshrc

# Source global definitions
if [ -f /etc/kshrc ]; then
. /etc/kshrc
fi

# use emacs editing mode by default
# set -o emacs
set -o vi

# User specific aliases and functions

For the bash folks here is the .bashrc file


# .bashrc

# User specific aliases and functions

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'

# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi

set -o vi



-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/longops.sql
-- Author       : Tim Hall
-- Description  : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @longops
-- Last Modified: 03/07/2003
-- -----------------------------------------------------------------------------------

COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid
,      s.serial#
,      s.machine
,      ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed
,      ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining
,      ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
  FROM v\$session s,
       v\$session_longops sl
 WHERE s.sid = sl.sid
   AND s.serial# = sl.serial#;

Top 5 


#!/bin/ksh
############################################################################
# Script Name..: top5.ksh
# Description..:
# Author.......: Michael Culp
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
spool top5.log

select day
,      event_name
,      Total_wait from ( select day
                         ,      event_name
                         ,      sum(event_time_waited) total_wait
                         ,      row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT   to_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy') day,s.begin_interval_time, m.*
FROM (SELECT ee.instance_number
      ,      ee.snap_id
      ,      ee.event_name
      ,      ROUND (ee.event_time_waited / 1000000) event_time_waited,
ee.total_waits,
ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
1
) pct,
ROUND ((ee.event_time_waited / ee.total_waits) / 1000
) avg_wait
FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
ee1.time_waited_micro
- ee2.time_waited_micro event_time_waited,
ee1.total_waits - ee2.total_waits total_waits
FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
ON ee1.snap_id = ee2.snap_id + 1
AND ee1.instance_number = ee2.instance_number
AND ee1.event_id = ee2.event_id
AND ee1.wait_class_id <> 2723168908
AND ee1.time_waited_micro - ee2.time_waited_micro > 0
UNION
SELECT st1.instance_number, st1.snap_id,
st1.stat_name event_name,
st1.VALUE - st2.VALUE event_time_waited,
1 total_waits
FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
ON st1.instance_number = st2.instance_number
AND st1.snap_id = st2.snap_id + 1
AND st1.stat_id = st2.stat_id
AND st1.stat_name = 'DB CPU'
AND st1.VALUE - st2.VALUE > 0
) ee
JOIN
(SELECT et1.instance_number, et1.snap_id,
et1.VALUE - et2.VALUE total_time_waited
FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
ON et1.snap_id = et2.snap_id + 1
AND et1.instance_number = et2.instance_number
AND et1.stat_id = et2.stat_id
AND et1.stat_name = 'DB time'
AND et1.VALUE - et2.VALUE > 0
) et
ON ee.instance_number = et.instance_number
AND ee.snap_id = et.snap_id
) m
JOIN
dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
)where rn < 6;

spool off
EOF

As root user create new file “oracle”
(init script for startup and shutdown the database) in /etc/init.d/ directory with following content:


#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script valid for 10g and 11g.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script# Source function library.

. /etc/rc.d/init.d/functions
ORACLE_OWNER=”oracle”
ORACLE_HOME=”/u01/app/oracle/11.2.0/db_1″
case “$1″ in

start)
echo -n $”Starting Oracle DB:”
su – $ORACLE_OWNER -c “$ORACLE_HOME/bin/dbstart $ORACLE_HOME”
echo “OK”
;;

stop)
echo -n $”Stopping Oracle DB:”
su – $ORACLE_OWNER -c “$ORACLE_HOME/bin/dbshut $ORACLE_HOME”
echo “OK”
;;

*)
echo $”Usage: $0 {start|stop}”
esac

Execute (as root) following commands (First script change the permissions, second script is configuring execution for specific runlevels):
chmod 750 /etc/init.d/oracle
chkconfig –add oracle –level 0356




function db_switch
{
###############################################################################
# function switches the database environments for any databases on the cluster
#
###############################################################################

#cd /var/opt/oracle/admin/scripts
#. ./env_generic

# export NEW_ORACLE_SID=ASM1+; . ~/.profile > /dev/null

echo
echo "Check database(s) in OCR"
echo ---------------------------------------
echo

gen_db_lst

#srvctl config &gt; dblst
#echo
#echo "Databases listed in clusterware"
#cat dblst
#echo

export NEW_ORACLE_SID=FYTEST1; . ~/.profile &gt; /dev/null

sqlplus -s "/ as sysdba" &lt; /dev/null

sqlplus -s "/ as sysdba" &lt; /dev/null

sqlplus -s "/ as sysdba" &lt; /dev/null
dashboard

# end of function
}

This is located in a common library


#!/usr/bin/ksh
# Purpose:    Search the alert log of $ORACLE_SID for error
# messages.
# If any error messages are found, send notification
#               (see Notes below).
#
# Prereqs:    $ORACLE_SID environment variable must be set or
#        passed as a positional parameter. Also, $ORATAB
#        environment variable must be set.
#
# Notes: If this script is invoked from a terminal session,
#        display the results to the screen (STDOUT); otherwise,
#        send a mail message to the specified mail accounts.
#
# Exit Status:  99 = Required input, $ORACLE_SID, not supplied.
#               90 = Invalid Oracle sid.
#               95 = init.ora file not found for input sid.
#               97 = Alert log file does not exist.
#
# Author: Michael Culp
# Date:   May 2002
#===============================================================
#        R E V I S I O N    H I S T O R Y
#===============================================================
# Who:
# Date:
# Comments:
################################################################

function error_notification
{
if [ "$TERMINAL" = "Y" ]; then
   echo "$ERROR_MSG"
else
   env|grep SEND_MAIL_TO|while read LINE
   do
      alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
      echo "$ERROR_MSG" | mail $(sendvar)
   done
fi
}

export SEND_MAIL_TO='mike.culp@gmail.com'
export SEND_MAIL_TO2='mike.culp@gmail.com'

SERVER=$(uname -n)
DATE=$(date +%c%t)
ERROR_MSG_FILE=/home/oracle/alertlog_message

# check to see if command is being executed interactively
/usr/bin/tty -s
STATUS=$?
if [ $STATUS = 0 ]
   then
   clear
   TERMINAL=Y
else
   TERMINAL=N
fi

# Test for required inputs.
# Positional parameters override env variables.

if [ ${1:-0} = 0 ]; then
   if [ ${ORACLE_SID:-0} = 0 ]; then
      ERROR_MSG=`echo "ORACLE_SID must be supplied as input to $0"`
      error_notification
      exit 99
   fi
else
   export ORACLE_SID=$1
fi

# Call the validate_sid function to verify ORACLE_SID is valid.
# The function also sets the ORACLE_HOME and ORACLE_BASE variables.

validate_sid
if [ "$VALID_SID" != "Y" ]; then
   ERROR_MSG=`echo "ORACLE_SID ($ORACLE_SID) is invalid. Script is $0"`
   error_notification
   exit 90
fi

INIT_PATH="$ORACLE_HOME"/dbs
INIT_FILE="$INIT_PATH"/init"$ORACLE_SID".ora

if [ ! -s "$INIT_FILE" ]; then
   ERROR_MSG=`echo "$INIT_FILE does not exist. Script is $0"`
   error_notification
   exit 95
fi

LOG_PATH="/"`awk -F= '/background_dump_dest/ { print $2 }' "$INIT_FILE" |tr -s " "|cut -f2-30 -d/`

ALERTLOG="$LOG_PATH"/alert_"$ORACLE_SID".log

# Create a list of valid Oracle error message prefixes by parsing
# the oerr script in $ORACLE_HOME/bin

/home/oracle/bin/awk_message_prefixes &gt; /tmp/ora_prefixes

# Archiver error message prefix is not included in the oerr
# script. Add an entry for this into the "ora_prefixes" file.

echo '^ARC[0-9]: Error' >> /tmp/ora_prefixes

# Search the alert.log for any occurrences of an Oracle error
# message prefix.

grep -bsf /tmp/ora_prefixes $ALERTLOG &gt; $ERROR_MSG_FILE
status=$?
if [ $status -eq 1 ]; then   # no matches found
   if [ $TERMINAL = 'Y' ]; then
   echo "Amazingly enough, there are NO errors in $ALERTLOG"
fi
elif [ $status -gt 1 ]; then
ERROR_MSG=`echo "$ALERTLOG does not exist."`
error_notification
exit 97
fi

# If there are any messages in the message file, send
# appropriate notifications.

if [ -s $ERROR_MSG_FILE ]; then
   echo "\nMessages from server $SERVER on $DATE" &gt;&gt; $ERROR_MSG_FILE
   if [ $TERMINAL = 'Y' ]; then
      cat $ERROR_MSG_FILE
   else
      env|grep SEND_MAIL_TO|while read LINE
      do
      alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
      mail $(sendvar) &lt; $ERROR_MSG_FILE
      done
   fi
fi

if [ -f $ERROR_MSG_FILE ]; then
rm $ERROR_MSG_FILE
fi
if [ -f /tmp/ora_prefixes ]; then
rm -f /tmp/ora_prefixes
fi

exit

 

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram