Oracle Userfull scripts – Sabin Shrestha's Personal Site

Posted in Oracle

http://www.materialdreams.com/oracle/

http://www.orafaq.com/

http://www.orafaq.com/scripts/

http://www.idevelopment.info/data/Oracle/DBA_tips/Sun_Solaris/SUNSOLARIS_5.shtml

http://www.dbasupport.com/oracle/scripts/

Tracing the Sessions(s) of a Given User

In a case where you cannot (because you don’t have enough time, for example) query V$SESSION to initiate a trace for the session, and you have to trace a “fast” user session, (for example, initiated by clicking on a button on the GUI of an application), this code traces the session(s) of a given user by using a database “on logon” trigger.

CREATE OR REPLACE TRIGGER ON_LOGON_SCOTT
AFTER LOGON ON DATABASE
WHEN ( USER = 'SCOTT' )
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Remarks:

1) The “exception” block is of utmost importance to avoid trouble should something go wrong with the trigger.
2) The trigger can contain more conditions (for example: if sysdate between XXX and YYY)
3) Once the trigger is enabled, all new sessions of the user will be traced. To suspend this behavior, perform: ALTER TRIGGER ON_LOGON_SCOTT DISABLE;
4) When not necessary anymore, drop the trigger instead of keeping it “disabled” (somebody might re-enable it by mistake): DROP TRIGGER ON_LOGON_SCOTT;

==============================================

Tablespace Free (and more) Report

This script can be run on Oracle8i, Oracle9i and Oracle 10g databases to get a report/graph on tablespace free information, somewhat similar to the report from Oracle Enterprise Manager. The report provides a look at all the tablespaces in a database with information like “number of files in the TS”, “Size(MB)”, “Free(MB)”, “Tablespace extensible to”, and a graph of space usage. The output is sorted by “% Free”, which shows the tablespaces that need immediate attention at the top.

column "File Count" format 999999
column "Size(MB)" format 999,999,999.99
column "Free(MB)" format 999,999,999.99
column "Used(MB)" format 999,999,999.99
column "Max Ext(MB)" format 999,999,999.99
column "% Free" format 999.99
column "% Free Ext" format 999.99
column "Graph" format a11
column tablespace_name format a20

SELECT ts.tablespace_name,

"File Count",

TRUNC("SIZE(MB)", 2) "Size(MB)",

TRUNC(fr."FREE(MB)", 2) "Free(MB)",

TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",

df."MAX_EXT" "Max Ext(MB)",

(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",

RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"

-- ,((DECODE(df."MAX_EXT", 0, df."SIZE(MB)", df."MAX_EXT") - fr."FREE(MB)") / DECODE(df."MAX_EXT", 0, df."SIZE(MB)", df."MAX_EXT")) * 100 "% Free Ext"

FROM (SELECT tablespace_name,

SUM(bytes) / (1024 * 1024) "FREE(MB)"

FROM dba_free_space

GROUP BY tablespace_name) fr,

(SELECT tablespace_name,

SUM(bytes) / (1024 * 1024) "SIZE(MB)",

COUNT(*) "File Count",

SUM(maxbytes) / (1024 * 1024) "MAX_EXT"

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+)

ORDER BY "% Free"

/

=====================================================

Track and Monitor Resources

Running this query will produce a list of database statements and objects that are using the most resources.

Note: Execute as SYS user.


select V1.sid, V1.serial#, V2.USERNAME, V2.OSUSER, V1.opname, to_char(V1.start_t
ime, 'HH24:MI:SS') AS Started, (V1.SOFAR/V1.TOTALWORK)*100 AS Pct_completed
FROM V$SESSION_LONGOPS V1, V$SESSION V2
WHERE V1.SID= V2.SID AND V1.SERIAL#=V2.SERIAL#
AND (SOFAR/TOTALWORK)*100 < 100
AND TOTALWORK > 0

=====================================================

Get CPU Attribution

This tip is for use in Oracle Database 10g. The following query will allow you to see the CPU attribution of the first level plan and the CPU attribution of the subplans used by this plan. (The query assumes that your first level plan is named “PLAN…” and your subplan is named “SPLAN…”.) Note that Oracle also provides the “View Resource Plan” screen in Oracle Enterprise Manager Grid Control where you can see the CPU attribution that you specified for a plan.

set lines 100
break on "PLAN " skip 1
select substr(substr(SYS_CONNECT_BY_PATH(plan, '/'),
2,
instr(substr(SYS_CONNECT_BY_PATH(plan, '/'),2)||'/','/')-1),1,20) "PLAN ",
decode(level,1,group_or_subplan,'..'||group_or_subplan) "GROUPE/SUBPLAN",
decode(level,1,to_char(cpu_p1,'9999'),' ..') "CPU_1",
decode(level,1,to_char(cpu_p2,'9999'),' ..') "CPU_2",
decode(level,1,to_char(cpu_p3,'9999'),' ..') "CPU_3",
decode(level,1,' ',to_char(cpu_p1,'999999999999')) "SUBPLAN_CPU_1"
from dba_rsrc_plan_directives
start with plan like 'PLAN_%'
connect by prior group_or_subplan = plan
order by "PLAN ", decode(level,1,group_or_subplan,plan), level, "GROUPE/SUBPLAN";

==================================================

Number to Word

set echo on
set feedback on

/***************************************************************************/* */
/* Procedure: NumberToWords */
/* Description: This package provides a function NumberToWords converting numbers to their */
/* English equivalent and returns it as a string. */
/* */
/* Version: 1.0.0 */
/* */
/* Required: Oracle Server Version 7.3 or higher. */
/* */
/* Example: */
/* */
/* SELECT NumberToWords.NumberToWords(1234567890) FROM DUAL; */
/* */
/* Written by: Material Dreams */
/* EMail: info@materialdreams.com */
/* WWW: http://www.materialdreams.com/oracle */
/* */
/* License: This script can be freely distributed as long as this header will not be removed and */
/* improvements and changes to this script will be reported to the author. */
/* */
/* Copyright (c) 1995-2004 by Material Dreams. All Rights Reserved. */
/* */
/**********************************************************************************************************/

Convert Numbers into Words

This query converts numbers into their text equivalents.

select to_char(to_date(873,'J'), 'JSP') as converted_form from dual;

CONVERTED_FORM
---------------------------
EIGHT HUNDRED SEVENTY-THREE

J represents the Julian format. When the to_char function is applied to the result of to_date, it spells (SP) the word for the number passed to to_date.

/* Create the package header */
CREATE OR REPLACE
PACKAGE NumberToWords IS
FUNCTION NumberToWords(theValue IN NUMBER) RETURN VARCHAR2;
END NumberToWords;
/

/* Create the package body */
CREATE OR REPLACE
PACKAGE BODY NumberToWords IS

TYPE NXR IS RECORD (Name VARCHAR2(60), Value INTEGER);
TYPE NXT IS TABLE OF NXR INDEX BY BINARY_INTEGER;
TYPE DMT IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;

numarr NXT;
denom DMT;

FUNCTION CVT2(theValue IN INTEGER) RETURN VARCHAR2;
FUNCTION CVT3(theValue IN INTEGER) RETURN VARCHAR2;

FUNCTION NumberToWords(theValue IN NUMBER) RETURN VARCHAR2
IS
val NUMBER := theValue;
tri INTEGER := 0; — last tree digits
place INTEGER := 0; — which power of 10 we are on
neg BOOLEAN := FALSE; — sign holder
temp VARCHAR2(255);
word VARCHAR2(255);
phrase VARCHAR2(255);
BEGIN
— check for 0
IF (val = 0) THEN
word := ‘zero’;
RETURN word;
END IF;

— check for negative int
IF (val < 0) THEN
neg := TRUE;
val := -val;
END IF;

— what we do now is break it up into sets of three, and add the appropriate denominations to each
WHILE (val > 0) LOOP
phrase := NULL;
tri := MOD(val, 1000); — last tree digits
val := FLOOR(val / 1000); — base 10 shift by 3
IF (tri > 0) THEN
phrase := phrase || CVT3(tri) || ‘ ‘;
END IF;
IF ((place > 0) AND (tri > 0)) THEN
phrase := phrase || denom(place+1);
END IF;
place := place + 1;

— got the phrase, now put in the string
temp := word;
IF ((val > 0) AND (tri > 0)) THEN
word := ‘, ‘ || phrase;
ELSE
word := phrase;
END IF;
word := word || temp;
END LOOP;

— remember that minus sign
IF (neg) THEN
word := ‘negative ‘ || word;
END IF;

RETURN word;
END NumberToWords;

FUNCTION CVT2(theValue IN INTEGER) RETURN VARCHAR2
IS
v NUMBER := theValue;
i PLS_INTEGER := 0;
s VARCHAR2(80);
BEGIN
i := 1;
WHILE (numarr(i).Value <= v) LOOP
i := i + 1;
END LOOP;

s := numarr(i-1).Name;
v := v – numarr(i-1).Value;

IF (v > 0) THEN
s := s || ‘-‘ || numarr(v+1).Name;
END IF;

RETURN s;
END CVT2;

FUNCTION CVT3(theValue IN INTEGER) RETURN VARCHAR2
IS
v NUMBER := theValue;
r INTEGER;
m INTEGER;
s VARCHAR2(80);
BEGIN
m := MOD(v, 100);
r := FLOOR(v / 100);

IF (r > 0) THEN
s := s || numarr(r+1).Name;
s := s || ‘ hundred’;
IF (m > 0) THEN
s := s || ‘ ‘;
END IF;
END IF;

IF (m > 0) THEN
s := s || CVT2(m);
END IF;

RETURN s;
END CVT3;

BEGIN
— initialize the arrays
numarr(01).Name := ‘zero’; numarr(02).Name := ‘one’; numarr(03).Name := ‘two’; numarr(04).Name := ‘three’; numarr(05).Name := ‘four’; numarr(06).Name := ‘five’; numarr(07).Name := ‘six’; numarr(08).Name := ‘seven’; numarr(09).Name := ‘eight’; numarr(10).Name := ‘nine’; numarr(11).Name := ‘ten’; numarr(12).Name := ‘eleven’; numarr(13).Name := ‘twelve’; numarr(14).Name := ‘thirteen’; numarr(15).Name := ‘fourteen’; numarr(16).Name := ‘fifteen’; numarr(17).Name := ‘sixteen’; numarr(18).Name := ‘seventeen’; numarr(19).Name := ‘eighteen’; numarr(20).Name := ‘nineteen’; numarr(21).Name := ‘twenty’; numarr(22).Name := ‘thirty’; numarr(23).Name := ‘forty’; numarr(24).Name := ‘fifty’; numarr(25).Name := ‘sixty’; numarr(26).Name := ‘seventy’; numarr(27).Name := ‘eighty’; numarr(28).Name := ‘ninety’; numarr(29).Name := ”; numarr(1).Value := 0;
numarr(2).Value := 1;
numarr(3).Value := 2;
numarr(4).Value := 3;
numarr(5).Value := 4;
numarr(6).Value := 5;
numarr(7).Value := 6;
numarr(8).Value := 7;
numarr(9).Value := 8;
numarr(10).Value := 9;
numarr(11).Value := 10;
numarr(12).Value := 11;
numarr(13).Value := 12;
numarr(14).Value := 13;
numarr(15).Value := 14;
numarr(16).Value := 15;
numarr(17).Value := 16;
numarr(18).Value := 17;
numarr(19).Value := 18;
numarr(20).Value := 19;
numarr(21).Value := 20;
numarr(22).Value := 30;
numarr(23).Value := 40;
numarr(24).Value := 50;
numarr(25).Value := 60;
numarr(26).Value := 70;
numarr(27).Value := 80;
numarr(28).Value := 90;
numarr(29).Value := 999;

denom(01) := ”;
denom(02) := ‘thousand’;
denom(03) := ‘million’;
denom(04) := ‘billion’;
denom(05) := ‘trillion’;
denom(06) := ‘quadrillion’;
denom(07) := ‘quintillion’;
denom(08) := ‘sextillion’;
denom(10) := ‘septillion’;
denom(11) := ‘octillion’;
denom(12) := ‘nonillion’;
denom(13) := ‘decillion’;
denom(14) := ‘undecillion’;
denom(15) := ‘duodecillion’;
denom(16) := ‘tredecillion’;
denom(17) := ‘quattuordecillion’;
denom(18) := ‘sexdecillion’;
denom(19) := ‘septendecillion’;
denom(20) := ‘octodecillion’;
denom(21) := ‘novemdecillion’;
denom(22) := ‘vigintillion’;

END NumberToWords;
/

/* Example */
SELECT NumberToWords.NumberToWords(1234567890) FROM DUAL;
/
=====================================================

User Logon Validation Trigger

This trigger verifies whether a user logging into the database is (pre)authorized to connect to the database using the approved client machine, client software, etc.

-- connect as sys/*****

CREATE TABLE authorize_users_tab(
username VARCHAR2(30),
osuser VARCHAR2(30),
program VARCHAR2(64),
machine VARCHAR2(64)
);

ALTER TABLE authorize_users_tab
ADD CONSTRAINT user_prog_mach_uk
UNIQUE (username,osuser,program,machine);

-- Insert appropiate values into this table, so only the entered
-- users will be permitted to connect to that database, query
-- V$SESSION for correct info, data entered into the table are
-- case sensitive.

insert into authorize_users_tab values ('SCOTT','DOMAIN\OSUSER','SQLPLUSW.EXE','DOMAIN\MACHINE');
insert into authorize_users_tab values ('TESTUSER','DOMAIN\OSUSER','SQLPLUSW.EXE','DOMAIN\MACHINE');
commit;

CREATE OR REPLACE TRIGGER authorize_users_trig
AFTER LOGON ON DATABASE

-- Description: This trigger helps database administrators to
-- control users logging into the database using various client
-- tools and machines. List of USERNAME, OSUSER, PROGRAM and
-- MACHINE entered in table AUTHORIZE_USERS_TAB will only be
-- permitted to login into the database.
-- Ref Oracle Bug No.2628258 and Doc No.2628258.8 in Metalink for
-- using RTRIM(machine,CHR(0)) in the below code.
-- Caveat: When a user tries to connect via SQL*Plus and if the
-- trigger refuses connection, info about that session is shown
-- as INACTIVE in V$SESSION, unless that user closes the SQL*Plus
-- window/session.

DECLARE
p_count NUMBER(1);
BEGIN
SELECT COUNT(*) INTO p_count
FROM sys.authorize_users_tab
WHERE UPPER(username||osuser||program||RTRIM(machine,CHR(0)))
IN (
SELECT UPPER(username||osuser||program||RTRIM(machine,CHR(0)))
FROM v$session
! WHERE au dsid = USERENV('sessionid')
AND logon_time = (
SELECT MAX(logon_time)
FROM v$session
WHERE audsid = USERENV('sessionid')));
IF p_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '---> Sorry, Access Denied <---');
END IF;
END ;
/

=====================================================

Collecting Percentage of CPU Used by Consumer Group

This tip is for people who use Oracle Resource Manager in Oracle Database 10g to manage CPU usage by consumer group. Oracle provides the V$RSRC_CONSUMER_GROUP view where you can see a CONSUMED_CPU_TIME column that contains the cumulative amount of CPU time consumed by all sessions in the consumer group. This column is cumulative, however, so it does not tell you anything about a specific period of time. So you cannot use it to know which consumer group consumed the most CPU in the last 5 minutes. You cannot collect a history of the CPU used by each group for specific period of time either. So here is what you can do to get this information. First of all, you need to create a snapshot of the V$RSRC_CONSUMER_GROUP because you cannot use the “as of timestamp” clause on a V$ view. Refresh this snapshot every minute.

create materialized view MV_RSRC_CONSUMER_GROUP
refresh FORCE
start with sysdate
next sysdate + 1 / 1440
as
SELECT name, consumed_cpu_time from v$rsrc_consumer_group;

Then, to get the %CPU used by each group in the last 5 minutes, use this query:

select b.name,
100 * (decode(sign(b.consumed_cpu_time - a.consumed_cpu_time),
-1, b.consumed_cpu_time,
b.consumed_cpu_time - a.consumed_cpu_time) /
decode(sign(c.cpu_tot_current - d.cpu_tot_before),
-1, c.cpu_tot_current,
c.cpu_tot_current - d.cpu_tot_before)) "CPU"
from MV_RSRC_CONSUMER_GROUP as of timestamp (systimestamp - interval '5' minute) a,
MV_RSRC_CONSUMER_GROUP b,
(select sum(consumed_cpu_time) "CPU_TOT_CURRENT"
from MV_RSRC_CONSUMER_GROUP) c,
(select sum(consumed_cpu_time) "CPU_TOT_BEFORE"
from MV_RSRC_CONSUMER_GROUP as of timestamp (systi! mestamp - interval '5' minute)) d
where b.name = a.name(+);

You can then use that query as a “User-Defined SQL Metrics” in Grid Control to collect a history of the CPU percentage used by each consumer group! You could modify the query easily to get the amount of CPU used instead of a percentage if you prefer. This is really useful if you want to monitor your consumer group and keep the history for a column that is cumulative in the V$RSRC_CONSUMER_GROUP. Just include it in the materialized view and define a “User-Defined SQL Metric” in Grid Control to keep historical data.=====================================================

export ORACLE_BASE=/<mountpoint>/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.0.2
exprot TNS_ADMIN=$ORACLE_HOME/network/admin
exprot ORACLE_SBIN=$ORACLE_HOME/sbin
export PATH
export host=`hostname`
export email=$`

USAGE=”$0: incorect arguments, Usage: $0<email>”
if [ -z “$email” ]; then
echo “$USAGE”
mailx -s “$USAGE” sabinshresth@hotmail.com</dev/null
exit 1
elif [ `whoami` != ‘oracle ]; then
echo “$0 aborted user -where `whoami` is incorrect must be user oracle
mailx -s “$0 aborted – user -where `whoami` is incorrect must be user oracle” $email </dev/null
fi

echo “Executing $0 on $host”

err=”`tail -50 $ORACLE_ALERT/alert_$oracle_SIDlog | grep ORA-00600 |grep -v grep |wc -l`”
if [ $err -gt 0 ];
tail -50 $ORACLE_ALERT/alert_$oracle_SIDlog | grep ORA-00600 |grep -v grep> $ORACLE_SBIN/logs/ora600.log
sub=”Script $0 on $host detected ORA-0t00 for SID $ORACLE_SID”
echo $sub
mailx -s “$sub” $email < $ORACLE_SBIN/logs/ora600.log
exit 1
fi
exit 0

panic=95
screen=99
df -k | awk'{\
if (($1 !=”Filesystem”) && ($1! = “fd”) && ($1 != “/proc”)) \
{\
if($5 > scream) {print “scream !!! – Disk space on”,host,$1,”@”,%5 }\
else if ($5 > panic} { print “Panic – Disk space on”,host,$1,”@”,$5 }\
}\
}’ scream=$scream panic=$panic host=$host > /usr/local/sabin/log/diskspace.log

if [ -s /usr/local/sbin/log/diskspace.log ]; then
sub=”Script $0 on $host detected disk space limits exceeded !!!”
echo $sub
mail -s “$sub” $email</usr/local/sbin/logs/diskspace.log
exi 1
fi
exit 0

echo cleaning trace and transaton audit files for $1 upto 14 days ago
unalias rm
$find $ORACLE_BASE/admin/$1/bdump/*.trc -mtile +14 | xargs rm -f
$find $ORACLE_BASE/admin/$1/udump/*.trc -mtile +14 | xargs rm -f
$find $ORACLE_BASE/admin/$1/cdump/*.trc -mtile +14 | xargs rm -f
$find $ORACLE_BASE/rdbms/audit/*.aud -mtile +14 | xargs rm -f

alias rm ‘rm -i’

==================================
#!/bin/ksh
ALL_DATABASES=`cat /etc/oratab|grep -v “^#”|grep -v “N$”|cut -f1 -d: -s`
for DB in $ALL_DATABASES
do
unset TWO_TASK
export ORACLE_SID=$DB
export ORACLE_HOME=`grep “^${DB}:” /etc/oratab|cut -d: -f2 -s`
export PATH=$ORACLE_HOME/bin:$PATH
echo “—> Database $ORACLE_SID, using home $ORACLE_HOME”
sqlplus -s system/${DB}password @<<-EOF
select * from global_name;
exit;
EOF
done
——————–

sqlplus /NOLOG @conn.sql
# No messing with ps, no password leak, connect from witin conn.sql

or…

echo “Name : \c”
read Name
echo “Password : \c”
stty -echo
read Password
stty echo
echo ${Name}/${Password} | sqlplus

or…

USER=scott
PASSWORD=tiger
{ echo ${USER}/${PASSWORD}; cat ${your_script_name_here}; } | sqlplus
———————-
Sequent:
$ debug -c core $ORACLE_HOME/bin/sqlplus
debug> stack
debug> quit
———————-
Write a CRON job to:

1. Get the tablespace names from Oracle:
SELECT TABLESPACE_NAME FROM SYS.DBA_TABLESPACES;

2. Put the tablespaces in backup mode:
ALTER TABLESPACE … BEGIN BACKUP;

3. Get the database file names from Oracle:
SELECT NAME FROM SYS.V_$DATAFILE;
SELECT NAME FROM SYS.V_$CONTROLFILE;
SELECT MEMBER FROM SYS.V_$LOGFILE;

4. Backup the files using pax, tar, ufsdump, dd, cpio or whatever file copy command you fancy. Note that if your database is on a raw partition, you can only use dd. Some examples:

$ find . -depth -print | cpio -ocBv >/dev/tapedevice
$ dd if=/dev/raw_device of=/dev/tape_device BS=8k
$ pax -w -f archive.pax *

5. End backup mode for all the tablespaces.
ALTER TABLESPACE … END BACKUP;
————
One can select a value from a database column directly into a Unix environment variable. Look at the following shell script examples:

#!/bin/sh
VALUE=`sqlplus -silent user/password@instance <<END
set pagesize 0 feedback off verify off heading off echo off
select max(c1) from t1;
exit;
END`
if [ -z “$VALUE” ]; then
echo “No rows returned from database”
exit 0
else
echo $VALUE
fi

Second example, using the SQL*Plus EXIT status code (can only return integer values smaller than 256):

#!/bin/ksh
sqlplus -s >junk1 /nolog <<EOF
connect user/password@instance
column num_rows new_value num_rows format 9999
select count(*) num_rows
from table_name;
exit num_rows
EOF
echo “Number of rows are: $?”

Yet another example, only this time we will read multiple values from SQL*Plus into shell variables.

sqlplus -s /nolog |& # Open a pipe to SQL*Plus

print -p — ‘connect user/password@instance’
print -p — ‘set feed off pause off pages 0 head off veri off line 500’
print -p — ‘set term off time off’
print -p — “set sqlprompt ””

print -p — “select sysdate from dual;”
read -p SYSDATE

print -p — “select user from dual;”
read -p USER

print -p — “select global_name from global_name;”
read -p GLOBAL_NAME

print -p — exit

echo SYSDATE: $SYSDATE
echo USER: $USER
echo GLOBAL_NAME: $GLOBAL_NAME
——————————————
What is ORATAB and what is it used for?
The oratab file is normally located in the /etc or /var/opt/oracle directories. Originally ORATAB was used for SQL*Net V1. It contains comments in Unix-style leading pound signs (#), and data lines consisting of entries in the following format:

database_sid:oracle_home_dir:Y|N

==========================================
* SMON
The System Monitor carries out a crash recovery when a crashed insance is started up again. It also cleans temporary segments.
* PMON
The Process Monitor checks if a user process fails and if so, does all cleaning up of resources that the user process has aquired.
* DBWR
The Database Writer writes dirty blocks from the database buffer to the datafiles. How many DBWn Processes are started is determined by the initialization parameter DB_WRITER_PROCESSES. DBWR also writes the actual SCN with the Block.
* LGWR
The Log Writer writes the redo log buffer from the SGA to the online redo log file.
* MMAN
The memory manager
* MRP
Managed recovery process: the process that applies archived redo log to the standby database.
* RFS
The remote file server process on the standby database receives archived redo logs from the primary database.
* RECO
The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.
* CKPT
The Checkpoint Process reqularly initiates a checkpoint which uses DBWR to write all dirty blocks back to the datafiles, thus synchronizing the database. Since a Checkpoint records the current SCN, in a recovery only redo records with a SCN higher than that of the last checkpoint need to be applied.
* ARCH
The Archiver Process archives redo log files if ARCHIVELOG is enabled.
* Dnnn
The Dispatcher Process is used in a shared server environment.
* Snnn
The Shared Server Process is used in a shared server environment.
* LMON
The lock manager.
* LMD0
* QMNn
AQ Time Manager
* TRWR
Trace writer
* WMON
The wakeup monitor process.
* LCKnnn
Inter-instance locking process.
* SNPnnn
The snapshot process.
* MMON
New background process in Oracle 10g.
* DMON
The Data Guard Broker process.
* SNP
The snapshot process.

Background processes have v$session.type = ‘BACKGROUND’
cat /dev/null > /dev/ttyname kill –9 pid# (kill)
ps -ef|grep $ORACLE_SID| \
grep -v grep|awk ‘{print $2}’|xargs -i kill -9 {}

ipcs -pmb
ipcrm –m 24064 (release memory)

check processes
strace
truss -rall -wall -p <PID>
truss -p $ lsnrctl dbsnmp_start

echo pmon=$pmon + smon=$smon + dbwr=$dbwr + lgwr=$lgwr + chkpt=$ckpt + reco=$reco + arch=$arch + mman=$mman + mmon=$mmon
processes=`echo “$pmon+$smon+$dbwr+$lgwr+$ckpt+$reco+$arch” | bc`
echo $processes
if [ $ processes -eq 0 ]; then
echo Aborting – Database process ERROR
echo pmon=$pmon + smon=$smon + dbwr=$dbwr + lgwr=$lgwr + chkpt=$ckpt + reco=$reco + arch=$arch + mman=$mman + mmon=$mmon
mailx -s “Aborting -DAtabase process Error” $ email > /dev/null
exit 1
fi

check listener
listener=”`ps -eaf|grep lsnr|grep -v grep|wc -l`”
if [ $ processes -eq 1 ]; then
lsnr_SIDs=”`lsnrctl status |grep $2 | grep -v grep|wc -l`”
if [ $ processes -eq 0 ]; then
echo Aborting – listener configuration does not match SID parameter
mailx -s “Aborting -Listener Configuration does not match Error” $ email > /dev/null
exit 1
fi
else
i=0
while [ $listener -eq 0 ]; do
if [ $i -gt 1]; then
echo Aborting -listener is down -faile to restart
echo check $TNSADMIN/listenr.ora configuration
mailx -s “Aborting -listener is Down -failed to restart” $email > /dev/null
exit 1
fi
lsnrctl start
listener=”`ps -eaf|grep lsnr|grep -v grep|wc -l`”
i=`echo “$i+1″ |bc
done
fi

database state
$ORACLE_HOME/BIN/sqlplus $3<<!
connect sys;
set termout off echo off feed off trimspool on head off pages 0;
spool $ORALCE_SBIN/LOG/databseAlive.log;
select name from v$databse;
select open_mode from v$database;
spool off;
disconnect;
exit;
!

dbState=”`cat $ORACLE_SBIN/logs/databaseAlive.log|grep READ |grep -v grep|wc -l`”
if [ $dbState -eq 0 ]; then
echo Aborting – database for SID $e not open
mailx -s “Aborting -database for SID $e not open” $ email > /dev/null
exit 1
fi
=====================================================

Identify Tables That Need Space

This tip lets you identify tables that need more than a specified amount of space. This example uses a size of 1GB.

SELECT segment_type, SUBSTR (segment_name, 1, 30),
SUBSTR (tablespace_name, 1, 30),
SUM (TRUNC (BYTES / (1024 * 1024 * 1024), 2)) "size in GB"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
AND owner IN ('BO_USER', 'DWH_OWNER', 'OWBTARGET', 'SM_OWNER')
HAVING SUM (TRUNC (BYTES / (1024 * 1024 * 1024), 2)) > 1
GROUP BY segment_type,
SUBSTR (segment_name, 1, 30),
SUBSTR (tablespace_name, 1, 30)

=====================================================

Open Cursor Count

This script reports the session-wise open cursor count, which can be helpful information when tuning the open_cursor parameter (in environments where application changes are frequent).


select substr(a.sid,1,10) sid,
substr(nvl(b.program,machine),1,30) program,
count(*)
from v$open_cursor a, v$session b
where a.saddr=b.saddr
group by substr(a.sid,1,10),
substr(nvl(b.program,machine),1,30)
order by 3 desc ;

=====================================================

Get Information on Current Queries

This script provides information about the queries that are currently using temporary segments. (This script runs on Oracle Database 10g.)

col tablespace for a8
col username for a10
col status for a8
col program for a15
col sql_text for a30
col blocks for 9999999
set linesize 350
set pagesize 300

alter session set nls_date_format='DD/MM/YYYY HHAM:MI:SS';

select t.tablespace_name as tablespace,us.username,s.status,s.sid,s.serial#,s.program,sql.sql_text,us.blocks,s.logon_time
from v$tempseg_usage us, v$session s, v$sqlarea sql, dba_tablespaces t
where t.contents = 'TEMPORARY' and
us.tablespace=t.tablespace_name and
us.session_addr = s.saddr and
s.sql_address = sql.address
order by blocks
/

=====================================================

Track CPU Usage

This query monitors CPU usage by session, showing information about processes running. It can identify the whole proccess tree.


select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time", P.SPID "PROC",
name "Stat CPU", value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc

=====================================================

Putting files on a remote Server (Oracle/Unix)
Sometimes, when we are working with the Oracle database, we come across the need to put files on remote server which are to be picked up by some other process or we have to ftp file(s) for backups. One can transfer the file manually by using ftp either to put on the remote server or to get it from the remote server. Manual process has its own disadvantage as it requires human intervention. We can always automate the process and schedule it as a cronjob so that script gets invoked at specific time and file transfer takes place automatically. Following is the small shell script which automates the process of putting the file on the remote server. Cut and paste following text on a UNIX machine and save it as a shell file (.sh extension).

#!/bin/sh
ftp_server=”10.80.10.27″
exp_dir=”/home/oracle/decipher”

ftp -n ${ftp_server} << EOF
user ftpuser ftpuser\*
cd /customerdb/ora_bkup
lcd ${exp_dir}
bin
put decipher_backup.dmp
EOF

• First line indicates that we are using bourne shell. This is default for UNIX. Shell is nothing but the command interpreter. It reads command from the key board and executes it. When more than one command is put together in the file, it is called shell script. We can execute the shell script which in turn will execute all the commands within the script.
• Second and third lines, we are declaring variable and assigning value to it.
• Fourth line we are invoking ftp connection with –n option. When –n option is used, it turns off the automatic login and ftp client will not ask for userid and password to connect to remote server. If not, then it will try to login immediately and will prompt for userid and password. ‘<< ’ is a redirection syntax and indicates that anything defined after this will be considered as an input to the ftp command.
• On the fifth line, we are giving credentials to login to the remote server by providing userid and password.
• On the sixth line we are changing the directory where we want to put the file.
• Seventh line, we are using lcd command to change the directory on local machine and hence (lcd), from which we would like to transfer the file.
• On the eighth line, we are changing the transfer mode to binary. Default is ascii mode. If it is a zip file or export dump file, it is advisable to transfer it in binary mode otherwise during transfer it may get corrupted.
• In the ninth line, we are using put command to put the file on the remote server.
• 10th line indicates EOF, means redirection is over at this point. So if at all, any other command is used in the script after this point will not be considered as an input and will be executed individually.

In similar fashion, one can get the file from remote server using get command. Script can be enhanced further to check for the success or failure of ftp process as well.

Leave a Reply