Weekly script #2 pid_from_sid.sql

A fairly easy script today that isn’t really hard to work out for yourself but I find it useful to have this script kicking around for when you find high resource using Oracle server process and you want to identify the offending SQL.


set pages 1000;
set lines 200;
col username for a10;

select b.status,a.inst_id,a.PID "Ora PID",a.SPID "Server PID",
a.LATCHWAIT,b.Program, b.Username,b.Osuser,b.machine,
b.terminal,a.terminal,b.Process "Client Process"
from gv$process a,gv$session b where a.addr=b.paddr and b.sid=&SID
/

See you next week!

Le Paz
 

 

Advertisements

DAC automationUtils.sh Script

Hi Again,

Today I noticed that DAC doesn’t have a method to export DAC repository meta data using the Linux/Unix Shell.

DAC is a windows tool that can also run on Linux. Unfortunately for us Linux users this means that not all of the command line options are available as they come in the form of a .bat file wont work in out native shell.

I was looking for a way to export DAC meta data using the command line, I was fully aware that this task could be achieved using the DAC Client GUI but I wanted something that could be run on a schedule to take backups. After some reading it was revealed that this was possible in Windows but now Linux. I have now converted the automationUtils.bat to automationUtils.sh that can run in Linux and Solaris giving you all the functionality. See the completed script below

#!/bin/ksh
#set -x
#
# Author - Andrew Pazikas
# Date - 10-07-13
# Desc - Originaly a Windows tool automationUtils.bat converted to run as a shell script Linux
#

. ./dac_env.sh

JAVA_HOME=/usr/jdk/instances/jdk1.6.0;export JAVA_HOME;
JAVA=${JAVA_HOME}/bin/sparcv9/java;export JAVA

export SQLSERVERLIB=./lib/msbase.jar:./lib/mssqlserver.jar:./lib/msutil.jar:./lib/sqljdbc.jar
export ORACLELIB=./lib/ojdbc6.jar:./lib/ojdbc5.jar:./lib/ojdbc14.jar
export DB2LIB=./lib/db2java.zip
export TERADATALIB=./lib/teradata.jar:./lib/terajdbc4.jar:./lib/log4j.jar:./lib/tdgssjava.jar:./lib/tdgssconfig.jar:./lib
#export SQLSERVERLIB ; export ORACLELIB ; export DB2LIB ; export TERADATALIB

export DBLIBS=${SQLSERVERLIB}:${ORACLELIB}:${DB2LIB}:${TERADATALIB}
export DACLIB=./DAWSystem.jar:.:
export DACCLASSPATH=${DBLIBS}:${DACLIB}
#export DBLIBS ; export DACLIB ; export DACCLASSPATH;
$JAVA -Xmx1024m -cp $DACCLASSPATH com.siebel.etl.functional.AutomationUtils "$1" "$2" "$3"

DAC Restart Script

Hi,

Just thought I would share the script I wrote to restart your DAC server in the event of a crash, this can be helpful during the night as there is no need for a manual restart.

I used a cron job to check to see if the process is running every 5min.


#!/bin/ksh

#########################################################
 # NAME:email_dac_restart.sh #
 # AUTHOR:  #
 # DESC: Makes sure dac is running and restarts if not #
 # VERSION: 18/1/13 #
 # CHANGE LOG: #
 #########################################################

. ~/.profile

if ps -ef | grep '/usr/jdk/instances/jdk1.6.0/bin/sparcv9/java -server -Xmn500m -Xms2048m -Xmx204'
 then
 echo 'do nothing'
 ## tail -1 $DAC_HOME/nohup.out | mailx -s 'DAC Running TST1 ' your@email.com
 else
 cd $DAC_HOME
 nohup $DAC_HOME/startserver.sh &
 tail -200 $DAC_HOME/nohup.out | mailx -s 'DAC Restarted  ' your@email.com
 fi

exit
 EOF

Any improvements or comments are welcome