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 > dblst #echo #echo "Databases listed in clusterware" #cat dblst #echo export NEW_ORACLE_SID=FYTEST1; . ~/.profile > /dev/null sqlplus -s "/ as sysdba" < /dev/null sqlplus -s "/ as sysdba" < /dev/null sqlplus -s "/ as sysdba" < /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 > /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 > $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" >> $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) < $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