Backup size for particular days

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

for SID in `ps -ef | grep pmon | grep -Ev "grep|ASM" | awk '{print $NF}' | cut -f3 -d"_"`
do
SID=${SID%[12]}
cd /s<your backup area>/${SID}/rman
echo -e "\nEnter the dates below: e.g 23 24 25 26"
read ANS
echo -e "\nEnter the Month below: e.g Nov Dec"
read ANX
for z in ${ANS}
do
if [ ${#z} = 1 ]
then
z=" ${z}"
fi
> /tmp/sed.out
X=0
for i in `ls -al | grep "${ANX} ${z}" | awk '{print $NF}'`
do
A=`du -ak ${i} | awk '{print $1}' `
for x in ${A}
do
X=`echo "scale=2;"${x}+${X} | bc -l`
done
done
Y=`echo "scale=2;"${X}/1024 | bc -l`
echo "Total for ${ANX} ${z}= : "${Y}"M"
done

RMAN - aid for identifying archived logs and controlfiles from database dictionary views

-- ------------------------------------
--
-- Find archivelog details details
--
-- The "SET_COUNT" is used in the disk file name e.g.31538 = ora_MY_DB12_ARC_piopvbq5_1_1_832548677_31538_1
--  The SEQUENCE# is used to restore archivelog e.g. RESTORE ARCHIVELOG from logseq 16414 until logseq 16434 thread=2;
--
--
-- ------------------------------------

break on thread# skip 1 on Date on SEQUENCE#
col "Date" format a25

select
-- 'RESTORE ARCHIVELOG from logseq '||min(a.SEQUENCE#)||' until logseq '||max(a.SEQUENCE#)||' thread='||a.thread#||';' ARCH_LOG_FILE
a.SEQUENCE#
-- , a.thread#
, to_char(a.first_time, 'DD-MON-YYYY HH24:MI') "Date"
, c.SET_COUNT, a.thread#
from v$log_history a, v$archived_log b,  V$BACKUP_REDOLOG c
where a.sequence# = b.sequence#
and b.sequence# = c.sequence#
and a.NEXT_CHANGE# = b.NEXT_CHANGE#
and b.NEXT_CHANGE# = c.NEXT_CHANGE#
and a.thread#=b.thread#
and a.first_time between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
order by a.thread#, 1
/

-- ------------------------------------
--
-- Find archivelog details details
--
-- RMAN syntax generater for restoring archivelog files
--
-- ------------------------------------
col ARCH_LOG_FILE format a120

select
'rman<<END'||chr(10)||
'connect target /'||chr(10)||
'connect catalog MY_DB1/password@CATDB'||chr(10)||
'run'||chr(10)||
'{'||chr(10)||
' allocate channel ch00 type ''sbt_tape'';'||chr(10)||
' SEND ''NB_ORA_SERV=bkup-mstr_srvr.svr.emea.sdunnings.net,NB_ORA_CLIENT=my-client.svr.emea.sdunnings.net'';'||chr(10)||
' SET ARCHIVELOG DESTINATION TO ''/share/dbbkup/oracle/MY_DB1/rman/.SED'';' ARCH_LOG_FILE
from dual
union all
select
'RESTORE ARCHIVELOG from logseq '||min(a.SEQUENCE#)||' until logseq '||max(a.SEQUENCE#)||' thread='||a.thread#||';' ARCH_LOG_FILE
from v$log_history a, v$archived_log b,  V$BACKUP_REDOLOG c
where a.sequence# = b.sequence#
and b.sequence# = c.sequence#
and a.NEXT_CHANGE# = b.NEXT_CHANGE#
and b.NEXT_CHANGE# = c.NEXT_CHANGE#
and c.SET_COUNT in
(select
distinct c.SET_COUNT
from v$log_history a, v$archived_log b,  V$BACKUP_REDOLOG c
where a.sequence# = b.sequence#
and b.sequence# = c.sequence#
and a.NEXT_CHANGE# = b.NEXT_CHANGE#
and b.NEXT_CHANGE# = c.NEXT_CHANGE#
and a.thread#=b.thread#
and a.first_time between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
)
group by a.thread#
union all
select
'}'||chr(10)||
'END' ARCH_LOG_FILE
from dual
-- order by a.THREAD#
/


-- ------------------------------------
--
-- Find controlfile details
--
--
--
--
--
-- ------------------------------------

col START_TIME format a25
col COMPLETION_TIME format a25
col HANDLE format a25
col DEVICE_TYPE format a8
col controlfile_included format a7 heading 'Control|File|Inc'
col INCREMENTAL_LEVEL format 999 heading 'Inc|Level' wrap

select
  -- d.bs_key,
  d.backup_type, d.controlfile_included , d.incremental_level, d.pieces,
  to_char(d.start_time, 'DD-MON-RRRR HH24:MI:SS') start_time,
  -- to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time
  -- d.elapsed_seconds
  d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes,
  -- , s.input_file_scan_only,
  z.HANDLE,
  z.TAG,
  q.INPUT_TYPE
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
  join V$BACKUP_PIECE z on z.set_stamp = d.set_stamp and z.set_count = d.set_count
  join v$rman_backup_job_details q on q.SESSION_STAMP=d.SESSION_STAMP
where d.start_time
between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
and d.controlfile_included != 'NO'
and z.HANDLE is not null
order by d.start_time
/

col FILESIZE_DISPLAY format a10

select BTYPE
, ID2 "BACKUPSET SET_COUNT"
,to_char(CHECKPOINT_TIME,'DD-MON-RRRR HH24:MI:SS') "Date"
, FILESIZE_DISPLAY
from V$BACKUP_CONTROLFILE_DETAILS
where CHECKPOINT_TIME
between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
/

-- ------------------------------------
--
-- Find controlfile details details
--
-- RMAN syntax generater for restoring controlfile files
--
-- ------------------------------------

select
'rman<<END'||chr(10)||
'connect target /'||chr(10)||
'connect catalog MY_DB1/password@CATDB'||chr(10)||
'run'||chr(10)||
'{'||chr(10)||
' allocate channel ch00 type ''sbt_tape'';'||chr(10)||
' SEND ''NB_ORA_SERV=bkup-mstr_srvr.svr.emea.sdunnings.net,NB_ORA_CLIENT=my-client.svr.emea.sdunnings.net'';' CNTRLFILE
from dual
union all
select
-- z.HANDLE CNTRLFILE
'RESTORE CONTOLFILE to ''/share/dbbkup/oracle/MY_DB1/rman/.SED/control.tmp'' from '''||z.HANDLE||''' ;' CNTRLFILE
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
  join V$BACKUP_PIECE z on z.set_stamp = d.set_stamp and z.set_count = d.set_count
  join v$rman_backup_job_details q on q.SESSION_STAMP=d.SESSION_STAMP
where d.start_time
between
to_date('23-NOV-2013 08:00:00','DD-MON-RRRR HH24:MI:SS') and to_date('23-NOV-2013 14:00:00','DD-MON-RRRR HH24:MI:SS')
and d.controlfile_included != 'NO'
and z.HANDLE is not null
union all
select
'}'||chr(10)||
'END' CNTRLFILE
from dual
-- order by a.THREAD#
/

Electrical Resistance Calculator

for z in "Electrical Resistance Calc"
do
echo -e "\n\nEnter ressistor value 1: "
read R1
echo -e "Enter ressistor value 2: "
read R2
VALA=`echo ${R1}*${R2} | bc -l`
VALB=`echo ${R1}+${R2} | bc -l`
VALC=`echo "scale=2;${VALA}/${VALB}" | bc -l`
echo -e "Total resistance in series is: ${VALB}"
echo -e "Total resistance in parallel is: ${VALC}\n\n"
done

Build scripts 1 - directories

for z in SED
do
echo -e "\nEnter Primary database name: "
read DB1
echo -e "\nEnter Standby database name: "
read DB2
for DB_NAME in ${DB1} ${DB2}
do
cd ~
mkdir -p admin/${DB_NAME}/
cd admin/${DB_NAME}/
mkdir -p diag pfile spfile exp audit scripts logs
done
cd ~
du -ah admin/
done

Dates of Week for remaining current year - Run as "bash sed_date_checker.bsh"

#!/bin/bash
# function weekof()
weekof()
{
    local week=$1 year=$2
    local week_num_of_Mon_1 week_day_of_Mon_1
    local first_Mon
    local date_fmt="+%a %b %d %Y"
    local mon sun

    week_num_of_Mon_1=$(date -d $year-01-01 +%W)
    week_day_of_Mon_1=$(date -d $year-01-01 +%u)

    if ((week_num_of_Mon_1)); then
        first_Mon=$year-01-01
    else
        first_Mon=$year-01-$((01 + (7 - week_day_of_Mon_1 + 1) ))
    fi

    mon=$(date -d "$first_Mon +$((week - 1)) week" "$date_fmt")
    sun=$(date -d "$first_Mon +$((week - 1)) week + 6 day" "$date_fmt")
    # echo "\"$mon\" - \"$sun\""
    echo -e "\n\nFor week no: ${week}"
    echo -e "\t""\"$mon\" - \"$sun\""
    # echo -e "\n"
}

for z in SED
do
CW=`date '+%W'`
YR=`date '+%Y'`
MW=52
CNT=${CW}
MAX=`expr ${MW} + 1`
while [ ${CNT} -lt ${MAX} ]
do
# echo $CNT
weekof ${CNT} ${YR}
CNT=`expr ${CNT} + 1`
done
echo -e "\n"
done

Bash script - Week of Year

function weekof()
{
    local week=$1 year=$2
    local week_num_of_Mon_1 week_day_of_Mon_1
    local first_Mon
    local date_fmt="+%a %b %d %Y"
    local mon sun

    week_num_of_Mon_1=$(date -d $year-01-01 +%W)
    week_day_of_Mon_1=$(date -d $year-01-01 +%u)

    if ((week_num_of_Mon_1)); then
        first_Mon=$year-01-01
    else
        first_Mon=$year-01-$((01 + (7 - week_day_of_Mon_1 + 1) ))
    fi

    mon=$(date -d "$first_Mon +$((week - 1)) week" "$date_fmt")
    sun=$(date -d "$first_Mon +$((week - 1)) week + 6 day" "$date_fmt")
    # echo "\"$mon\" - \"$sun\""
    echo -e "\n\nFor week no: ${week}"
    echo -e "\t""\"$mon\" - \"$sun\""
    echo -e "\n\n"
}
weekof $1 $2
#
# Run as  bash sed_dt_test.bsh 32 2013
#

ORA-15186: ASMLIB error function

########################################
#
#
#  ORA-15186: ASMLIB error function = [asm_open], error = [1], mesg = [Operation not permitted]
#
#
#  Thanks to: arul selvaraj
#  http://arulselvaraj.blogspot.co.uk/2011/02/ora-15186-asmlib-error-function-asmopen.html
#
#
########################################

for i in /etc/sysconfig/oracleasm
do
ls -al ${i} | grep "^l"
if [ $? != 0 ]
then
 echo -e "\nIssue with file ${i}- should be a link to oracleasm-_dev_oracleasm\n\n"
else
 echo -e "Correct - file is a link"
 ls -al ${i}
 SO=`grep ORACLEASM_SCANORDER /etc/sysconfig/oracleasm-_dev_oracleasm`
 echo -e "\nScan order - ${SO}"
 echo -e "\n\n"
fi
done

Space Usage Calculator

##########################################
#
# Space usage calculator
#
#
#
#
##########################################

for i in 1
do
  echo -e "\n\nSpace Calc "
  echo -e "Enter TOTAL size: \c"
  read X
  echo -e "Enter USED size:  \c"
  read Y
  B=`echo "scale=2;"${Y}/${X}*100 | bc -l`
  echo -e "Space Usage: ${B}%\n"
  echo -e "Enter required USED %:  \c"
  read A
  C=`echo "scale=2;"${X}/100*${A} | bc -l`
  D=`echo "scale=2;"${Y}-${C} | bc -l`
  echo -e "Required increase: ${D} for ${A}%\n\n"
done

Bigley Logic

for z in BIGLEY BIGLEYDR
do
FIL=OracleFlag_${z}
if [ -f ${FIL} ]
then
 #echo "Flag set"
 TS=`ls -altr ${FIL} | awk '{print $6, $7, $8}'`
 echo -e "\n\nFlag set for: "${z}" on ${TS} \n\t\tso this database will not be started\n\n"
else
echo -e "\n\n--> Flag NOT set for ${z} <-- "
echo -e "\nWill start database ${z}\n\n"
 STA=`ps -ef | grep smon | grep -Ev "grep|ASM|agent|bin" | awk '{print $NF}' | cut -f3 -d"_"`
 # echo $STA
 if [ ${STA} = ${z} ]
  then
  echo -e "\nDatabase ${STA} is already running!!\n"
  else
  echo -e "Starting database ${STA}"
  fi
fi
done

Script to read a text file and count the occurrences of a full stop.

##########################
#
# Script to read a text file and count the occurrences of a full stop.
#
#
#
##########################

for z in SED
do
A=0
B=0
C=0
while read -r a
do
# echo ${a}
if [[ ${a} == *.* ]]
then
# echo "True"
A=`expr ${A} + 1`
 echo ${a} | sed 's/\./\.\n/g' | sed 's/^ //g'
 B=`echo ${a} | tr -d -C "." | wc -c`
 C=`expr ${B} + ${C}`
 echo -e "Current count of full stops: "${B}
 echo -e "Running count of full stops: "${C}
else
echo -e "\t\t--> This line does not contain a full stop"
fi
done</tmp/sed1    ## Text file here!!
# echo -e "Total count: ${A}"
echo -e "Total count of full stops: ${C}\n\n"
done

DB Parameter change generation script

-- ---------------------------------
--
-- DB Parameter change generation script
--
--
-- ---------------------------------

col name format a30 word_wrapped
col description format a60 word_wrapped
def param = &&1
set verify off

select decode(ISSES_MODIFIABLE,'FALSE','Cannot be changed','TRUE','Can be changed') "Session",
   decode(ISSYS_MODIFIABLE,'FALSE','Cannot be changed','TRUE','Can be changed','IMMEDIATE','Can be changed immdeiate','DEFERRED','Can be changed deferred') "System",
   'ALTER SYSTEM SET '||name||' = '||value||' scope='||
decode(ISSYS_MODIFIABLE,'FALSE','spfile','IMMEDIATE','both or memory',ISSYS_MODIFIABLE)||' sid=''*'';' value
from v$parameter
where name like ('%&param%')
or value like ('%&param%')
/

undef param
undef 1
set verify on

dbms_file_transfer.copy_file example - generation script

-- ---------------------------------------
--
-- dbms_file_transfer.copy_file example for a Data Guard broker file
--
-- NOTE: The Data Guard configuration must be disabled to allow this to happen
-- and also stop DMON process by doing alter system set dg_broker_start='false';
--
-- ---------------------------------------

set serveroutput on format wra

declare
  uname    varchar2(50);
  bfile    varchar2(50);
   cursor C1 is
    select lower(value) uname from v$parameter where name = 'db_unique_name';
   cursor C2 is
    select SUBSTR( value, INSTR( value, '/', -1 ) + 1, LENGTH( value )) bfile from v$parameter where name =

'dg_broker_config_file1';
begin
for x in C1 loop
 dbms_output.new_line;
 dbms_output.put_line('CREATE OR REPLACE DIRECTORY SOURCE_DIR AS ''+DATA01/'||x.uname||''';');
 dbms_output.new_line;
end loop;
for y in C2 loop
 dbms_output.put_line('BEGIN');
 dbms_output.put_line('dbms_file_transfer.copy_file(source_directory_object =>');
 dbms_output.put_line('''SOURCE_DIR'', source_file_name => '''||y.bfile||''',');
 dbms_output.put_line('destination_directory_object => ''DP_DUMP'',');
 dbms_output.put_line('destination_file_name => ''/tmp/'||y.bfile||''');');
 dbms_output.put_line('END;');
 dbms_output.new_line;
 dbms_output.put_line('drop directory SOURCE_DIR;');
 dbms_output.new_line;
end loop;
end;
/

Oracle Inventory Check Script for Linux

###########################################
#
#
# Oracle Inventory Check Script for Linux
#
#
#
###########################################



for z in `cat /etc/oraInst.loc | grep loc | cut -f2 -d"="`
do
 echo -e "\n########################################################################\nThe inventory is located in: "${z}"\n"
 A="${z}/ContentsXML/inventory.xml"
 VER=`cat ${A} | grep -E "SAVED_WIT" | cut -f2 -d">" | cut -f1 -d"<"`
 HME=`cat ${A} | grep -E "HOME NAME" | awk '{print $2,"--> ", $3}' | sed 's/NAME=//g'`
 echo -e "Oracle version in inventory = "${VER}
 echo -e "For HOMES = "
 for y in `cat ${A} | grep -E "HOME NAME" | awk '{print $2}' | sed 's/NAME=//g' | sed 's/LOC/Located at/g' | sed 's/"//g' | tr '[:lower:]' '[:upper:]'`
 do
 echo -e "\t${y}"
 cat ${A} | grep -i ${y} | awk '{print "\t\t--> ", $3}' | sed 's/NAME=//g' | sed 's/LOC/Located at/g' | sed 's/"//g'
 done
 echo -e "\n########################################################################\n"
done

SQL - INSTR & SUBSTR example

-- -----------------------------
--
-- Reverse search using "/" as a separator.
--
-- -----------------------------

col List format a35

select
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 5)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 4)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 3)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 2)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 1)+1,length('This/is/a/separation/list')) List
 from dual
/

-- -----------------------------
--
-- Find how many occurrences of "/" appear
--
-- -----------------------------

variable main_str varchar2(30)
variable srch_str varchar2(20)
exec :main_str := 'This/is/a/separation/list';
exec :srch_str := '/';

select (length(:main_str) - nvl(length(replace(:main_str,:srch_str,'')),0))/
          length(:srch_str) cnt
from dual;

select
substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)) List
 from dual
/

-- -----------------------------
--
-- Find how many occurrences of "/" appear
--
-- -----------------------------
col cnt heading "How many occurrances of /"

select
substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str) cnt
 from dual
/

RMAN Backup Details By Day

-- ----------------------------------------------
--
-- Script to run against actual database to look at the backup piece handle by tag for a particular day
--
--
--
-- ----------------------------------------------


col handle format a87
col STRT format a20
col "Inc Level" format a8
col STATUS format a10
col DEVICE_TYPE format a8
col NP noprint
col HN format a25



select
 A.RECID NP,
 to_char(A.START_TIME,'DD-MON-YY HH24:MI:SS')Strt
 , decode(B.INCREMENTAL_LEVEL,'0','FULL',B.INCREMENTAL_LEVEL) "Inc Level"
 , decode(A.STATUS,'A','Available','D','Deleted','X','Expired',A.status) STATUS
--  , SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)) HN
-- ,SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))
 , SUBSTR(SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)),instr((SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))),'_',-1,6)+1,12) HN
 , A.DEVICE_TYPE
 , DECODE(B.BACKUP_TYPE,'D','full backup','L','archived redo log','I','I,incremental level 1') B_TYPE
 , A.tag
 , round(A.BYTES/1048576,2) MB
 from v$backup_piece A,  v$backup_set B
 where a.SET_STAMP = b.SET_STAMP
 and a.SET_COUNT = b.SET_COUNT
 --
 -- Enter DATE below e.g. 02-OCT-13
 --
 and to_char(A.START_TIME,'DD-MON-YY')='&Date'
--  and A.DEVICE_TYPE = 'DISK'
order by SUBSTR(SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)),instr((SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))),'_',-1,6)+1,12) --A.RECID, A.COMPLETION_TIME, A.START_TIME
/

Kill blocking sessions

-- ------------
-- Kill blocking sessions RAC aware
-- ------------

set serveroutput on

declare
blockers   VARCHAR2 (1000);
BEGIN
FOR x IN (SELECT lka.SID sessid, ses.serial# serial,
      lka.inst_id instance_id
      FROM gv$lock lka, gv$lock lkb, gv$session ses
      WHERE (lka.id1, lka.id2) IN
      (SELECT id1, id2
      FROM gv$lock
      WHERE request = 0
      INTERSECT
      SELECT id1, id2
      FROM gv$lock
      WHERE lmode = 0)
      AND lka.id1 = lkb.id1
      AND lka.id2 = lkb.id2
      AND lka.request = 0
      AND lkb.lmode = 0
      AND lka.SID = ses.SID
      AND lka.inst_id = ses.inst_id)
LOOP
blockers :=
'ALTER SYSTEM KILL SESSION '''
|| x.sessid
|| ','
|| x.serial
|| ',@'
|| x.instance_id
|| '''';
DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put_line ('Looking for blocking sessions:');
DBMS_OUTPUT.put_line (blockers);
END LOOP;
END;
/

11g RAC KILL remote sessions

-- -------------------------------------
--
-- 11g RAC KILL remote sessions
--
-- -------------------------------------

column username format a12
column Kill format a55
column "Log on" format a25



select  s.inst_id "Inst",
 s.username
, to_char(s.LOGON_TIME, 'MM-MON-RRRR HH24:MI:SS') "Log on"
, 'alter system kill session '''||s.sid||','||s.serial#||',@'||s.inst_id||''' IMMEDIATE;' "Kill"
FROM  gv$session s
WHERE  s.username IS NOT NULL
AND s.username NOT IN ('DBSNMP', 'SYSMAN', 'SYS','PUBLIC')
ORDER BY s.username, s.inst_id, s.LOGON_TIME, substr(s.program,18,10)
/

Import Data pump Generation script

#############################################
# Import Data pump Generation script
#############################################

for i in 1
do

PWD=`pwd`
DPAREX=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select DIRECTORY_NAME
 from dba_directories
where DIRECTORY_PATH like '${PWD}%'
/
END`
echo -e "The datapump directory is called: = \t${DPARE}\n\n"

if [ -z ${DPAREX} ]
  then echo -e "Incorrect directory - change\n\tABORTING!!!"
else
  echo "Correct directory"
  DPARE=`echo ${DPAREX}`
  export DPARE
  echo ${DPARE}
  echo "Enter schema: "
  read SCHEMA
  TO_SCHEMA=${SCHEMA}
  echo "Enter remap schema from: (press return if not required)"
  read FROM_SCHEMA
  export ORACLE_OWNER=${LOGNAME}
  echo "Enter remap tablespace from: (press return if not required)"
  read F_TSPACE
  echo "Enter remap tablespace to: (press return if not required)"
  read T_TSPACE
  # DT=`date '+%Y%d%m%H%M'`
  DT=`date '+%Y%m%d_%H%M'`
  EXPDF=`ls expdp*.dmp | cut -f-4 -d"_" | uniq`
  TRUNCF=`ls expdp*.dmp | cut -f-4 -d"_" | uniq | cut -f1-3 -d"_" | uniq`
  FBDT=`date "+\"TO_TIMESTAMP('%d/%m/%Y %H:%M:%S','DD-MM-YYYY HH24:MI:SS')\""`
  PCNT=`ls expdp*.dmp | cut -f-4 -d"_" | uniq | wc -l`
  #
  # Test Script
  #
  echo -e "  nohup impdp \"/\" \\"
  if [[ -z ${FROM_SCHEMA} ]]
  then
  echo -e "  schemas=${SCHEMA} \\"
  else
  echo -e "  remap_schema=${FROM_SCHEMA}:${TO_SCHEMA} \\"
  fi
  if [[ -n ${F_TSPACE} ]]
  then
  echo -e "  remap_tablespace=${F_TSPACE}:${T_TSPACE} \\"
  fi
  echo -e "  directory=${DPARE} \\
  dumpfile=${TRUNCF}_%U.dmp \\
  logfile=impdp${SCHEMA}_${DT}.log \\
  JOB_NAME=IMP_${SCHEMA}_${DT} \\
  EXCLUDE=INDEX_STATISTICS \\
  EXCLUDE=TABLE_STATISTICS \\
  EXCLUDE=STATISTICS \\
  TABLE_EXISTS_ACTION=TRUNCATE or REPLACE \\
  CONTENT=DATA_ONLY or METADATA_ONLY \\
  PARALLEL=${PCNT} \\
  metrics=y &"
  #
fi
echo -e "\n\n"
done

Count database processes on Linux

Count database processes on Linux:

while true
do
for z in `ps -ef | grep smon | grep -v "grep" | awk '{print $NF}' | cut -f3 -d"_" | sort`
do
CNT=0
for i in `ps -ef | grep ${z} | grep -v "grep" | awk '{print $2}'`
do
A=`ls -al /proc/${i}/status`
if [ $? = 0 ]
then
        if [ -z ${A} ]
        then
        echo "Look at: ${A}"
        #ls -al ${A}
        fi
else
 echo "Nothing to see here..."
 echo ${i}
fi
B=`ls -al /proc/${i}/smaps`
if [ $? = 0 ]
then
        if [ -z ${B} ]
        then
        echo "Look at: ${B}"
        #ls -al ${B}
        fi
else
 echo "Nothing to see here..."
 echo ${i}
fi
CNT=`expr ${CNT} + 1`
done
echo "Process count for ${z} is: "${CNT}
done
sleep 20
done

Change DOMAIN name.

Change DOMAIN name in a RAC environment:



for i in `srvctl config | grep ${ORACLE_SID}`
do
CD=`srvctl config database -d ${i} | grep Domain`
echo -e "\n\nCurrent domain is: "${CD}
echo -e "To change this
srvctl modify database -d ${i} -m <your domain name>
and on the database...
alter system set db_domain='<your domain name>' scope=spfile;
\n\n
"
done


A database reboot will be needed to apply the SPFILE changes.

Archivelog Purges

A simple script to help me purge older archive logs when required:

############################
# Archivelog Purges
############################

for i in 1
do
echo -e "\nEnter number of days to retain: "
read ANS
rman <<END
connect target /
run {
allocate channel D1 type disk;
delete noprompt archivelog until time 'SYSDATE -${ANS}';
}
exit
END
done