Oracle Apps DBA: Apps Script

Query for Scheduled request start on prior run less than 5 mins

select cs.request_id,cs.concurrent_program_id,cs.program,cs.argument_text,cr.resubmit_interval_type_code,cs.requestor,resubmit_interval,resubmit_interval_unit_code from fnd_concurrent_requests cr,fnd_conc_req_summary_v cs where
cs.phase_code='P'
and cs.status_code in ('I','Q')
and
resubmit_interval_type_code='START' and cr.request_id=cs.request_id
and resubmit_interval_unit_code='MINUTES' and resubmit_interval<5

Monday, June 11, 2012

Script to find who has changed the sysadmin Password

The below script helps us to find the user_name who has changed the sysadmin password

SELECT substr(d.user_name,1,30) || '~'||substr(f.user_name,1,10)||'~'||substr(d.last_update_date,1,10) key,count(*) value
from apps.fnd_user d, apps.fnd_user f
WHERE
trunc(d.last_update_date) = trunc(sysdate)
and d.user_name='SYSADMIN'
and d.last_updated_by=f.user_id GROUP BY substr(d.user_name,1,30) || '~'||
substr(f.user_name,1,10)||'~'||substr(d.last_update_date,1,10);

! Happy that it helps you :) !.

Narasimha Rao

Monday, May 28, 2012

How to find all Form Personalization through script in Oracle APPS

SELECT FORM_ID, FORM_NAME, USER_FORM_NAME, DESCRIPTION
FROM
FND_FORM_VL
WHERE FORM_NAME IN
(SELECT FORM_NAME FROM FND_FORM_CUSTOM_RULES GROUP BY FORM_NAME)

Wednesday, April 25, 2012

Number of times Purge Concurrent Request execution was run in last 30 days

set pages 1000
set line 132
set head on
select r.requested_start_date,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like 'Purge Concurrent Request and/or Manager Data'
and r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Number of times gather schema stats was run in last 30 days

set pages 1000

set line 132

set head on

select r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text from

apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r

where p.concurrent_program_id = r.concurrent_program_id

and p.application_id = r.program_application_id

and p.user_concurrent_program_name like '%Gather Schema Statistics%'

and r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Concurrent Requests Average/Max/Min hours runtime details

set linesize 200

col username for a10

col status for a10

col phase for a10

col PNAME for a70

col request_id for 99999999999

col PNAME for a40

select

b.user_name username,

a.USER_CONCURRENT_PROGRAM_NAME as PNAME,

avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,

max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,

min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running

from

apps.fnd_conc_req_summary_v a,

apps.fnd_user b

where

phase_code = 'C' and status_code = 'C' and

a.REQUESTED_START_DATE > sysdate-30 and

upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and

a.REQUESTED_BY=b.user_id

group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;

Tuesday, April 24, 2012

OS commands Very useful for DBAs and OS files management

Finding OS Version and Bit

OS Version

uname -a

AIX <ServerName> 3 5 00C8E96B4C00

uname

AIX

oslevel -r

5300-06

OS Bit

lsconf|grep -i kernel

Kernel Type: 64-bit

prtconf

/usr/bin/isainfo –kv

getconf LONG_BIT

getconf -a | grep KERN

uname -m

kb/mb/gb conventions

1024 bytes = 1KB (4 letters)

1024 *1024 = 1048576 = 1MB (7 letters)

1024 *1024 * 1024 = 1073741824 = 1GB (10 letters)

To Check whether Sendmail is Enabled in a Unix Box

lssrc -a |grep sendmail

ps –ef|grep sendmail

Lists the no of processes grouped by individual user on a box

ps -ef|awk '{print $1}'|sort -n|uniq -c

Process count

ps -ef|awk '{print $1 }'|sort|uniq -c |sort -n

ps -ef|wc -l

ps -ef|grep oracle|wc -l

ps -x|wc –l

UNIX command examples

!!!CAUTION!!!

ALL ARE EXAMPLES ONLY

The Use of rm –f command must be thoroughly checked before including it in the find command syntax.

The Use of gzip –f command must be thoroughly checked before including it in the find command syntax.

Please understand the use of below commands before executing them in production.

Always edit the below find commands as per requirement.

We will not own the responsibility incase if the below commands are used wrongly by the buyer causing severe damage to the files.

These commands can cause severe damage to the files if used Wrongly in wrong situation.

These commands are examples only and should be used with Caution and buddy check.

Understand/Test & Edit the commands appropriately and use.

Unix commands

To find files from day a week ago

find ./ -name "*" $ -mtime -7 -a -mtime +5 $ -type f -exec ls -l {} \;

To specify displayed information

find . -type f -name "*.trc" -printf "%f %c %a %t \n"

To find files owned by specified user on desired mountpoint ( /var in this exam.)

find /var -xdev -user oracle -type f -exec ls -l {} \;

Files bigger than specified size in [k for kilobytes | c for bytes ]

find . -type f -size +1024k -exec ls -l {} \;

To compute size of files

ls -ltr * | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' | tail -1

Other commands syntax

uname -a -- To find the OS of the box

df -h -- To check total, used, avialable free space of all Filesystems

df -k

pwd -- Print name of current/working directory

To check u r in the correct folder or not , else use cd command and change the directory

df -h . -- To check total, used, avialable free space of the current path FS

df -k . (59419872 = around 59GB)

df -g .

df -g /opt/oracle/apps/admin/UKPROD

df -hP | grep /SID/oracle --To check total, used, avialable free space of the given path FS

df -kP | grep /SID/oracle (59419872 = around 59GB)

du -sh . --to check total size of current directory

du -sk . (output interms of KB)(23695680 = 23GB)

du -sh * --to list sizes of all files , folders

du -sk *

du -sh * | sort -n --to list sizes of all files , folders

du -sk * | sort -n

du -sk *|sort -n|tail --to list sizes of all files , folders (bigger 10 files)

du -sh *|sort -n|tail

du -ch PROD_df_LEVEL0_04-02-2007* --in the last line can find string* files total size

ls -l l779750[7-9].req

ls -lrt *LEVEL0*

ls -ltr *failed*

ls -ltr SID_arch* | head ; ls -ltr SID_arch* | tail

ls -ltrh

ls -lS -> list of files/folder SIZEwise sorting (to c bigger files, need to scroll)

ls -lSr ->list of files/folder SIZEwise reverse sorting (can c bigger files immediately)

ls -lt -> last accessed date wise sort (old files u can c immediately)

ls -ltr -> last accessed date wise reverse sort (to c old files, u need to scroll)

-- file/directory, owner of file, size, last accessed date, file name

ls -lSrh | tail -30 -> size wise list of 30 bigger files

ls -lSr |tail

ls -lt *TMP| grep " May 5"

fuser <filename> -- to find any process is accessing the file

lsof <filename> -- to find any process is accessing the file

Command to check size for a particular string for all platform

ls -ltr *LEVEL0* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1

ls -ltr SID_df_LEVEL0_18-10-2006* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1

find ./ -name "*.zip*" -mtime +60 -exec ls -ltr {} \; - searching for old patches

find ./ -name "*log*" -mtime +60 -exec ls -ltr {} \; - searching for old log files

find . -size +100000000c -xdev -exec ls -l {} \; - searching for morethan 100MB size files

find ./ -name "*" -size +30000k -exec ls -ltr {} \;

BECAREFUL OF USING THIS COMMANDS

=== ----------------------------------- ===

=== find /opt/oracle/apps/admin/SID/log -mtime +60 -type f -exec ls -l {} \; | wc -l ===

=== find ./ -name "*.trc" -mtime +60 -exec ls -ltr {} \; - listing 20 days old .trc files===

=== find ./ -name "error_log*" -mtime +10 -exec ls -ltr {} \; |wc -l ===

=== find ./ -name "*.trc" -mtime +10 -exec gzip -f {} \; - zipping 20 days old .trc files===

=== find ./ -name "*.trc*" -mtime +60 -exec ls -l {} \; - Purging 20 days old .trc files===

=== gzip -f `ls -lt *.trc | grep " Apr " | awk '{print $9}'` -zipping Apr month .trc files ===

=== gzip -f `ls -ltr | grep " Sep 29 10" | awk '{print $9}'` - zipiing sep 29th 10AM files(all)===

=== ls -l `ls -lt *.trc | grep " Jan " | awk '{print $9}'` -Purging Jan month .trc files ===

=== ls -l *trc `ls -l |grep " Aug "|awk '{print $9}'` ===

=== ===

=== ls -ltr *LEVEL0* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024}' |tail -1 --Calculate size

=== ls -ltr PROD_df_LEVEL1_27-09-2007* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1

===

=== find ./ -name "*trc" -size +300k -mtime +3 -exec ls -ltr {} \; ===

=== find ./ -name "*trc" -size +300k -mtime +3 -exec gzip {} \; ===

=== gzip -f `ls -l |grep " Sep " | awk '{print $9}'` ===

=== gzip -f `ls -l| grep " Sep 1"| awk '{print $9}'` ===

=== gzip <file name> ===

=== ===

=== rm <file name> Ex: rm ias.tar -- to remove single file ===

=== ls -l SID_df_LEVEL0_30-09-2006* -- to remove multiple files ===

=== ===

=== find ./ -name "*.*" -mtime +60 -exec ls -ltr {} \; - searching for old files ===

=== find . -mtime +15 -exec gzip -f {} \; -- zipping 15 days old files ===

=== ===

=== find . -mtime +30 -exec ls -l {} \; -- purge/delete 30 days old file ===

=== ===

=== ls -lt *log* | grep " Mar " | awk '{print $9}' ===

=== ===

=== ls -l `ls -lt *log* | grep " Feb " | awk '{print $9}'` ===

=== ls -l `ls -lt |grep " Jul " | awk '{print $9}'` ===

=== ===

=== find ./ -name "*.out" -mtime +60 -exec ls -ltr {} \; - searching for old files of extn .out===

=== ===

=== find . -size +100000000c -xdev -exec ls -l {} \; - searching for morethan 100MB size files ===

=== find ./ -name "*" -size +30000k -exec ls -ltr {} \; ===

=== ===

=== ls -ltr SID_df_LEVEL0_28-10-2006* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024 }' |tail -1 - total size of this failed LEVEL0 backup

=== ===

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

to purge files with directories inside use : ls -l

ls -lr oradata_beforePMP - DANGEROUS command - to purge along with folder forcefully

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

find . -size +50000000c -print|xargs ls -l

Finding Archivelogs applied lastly

select min(COMPLETION_TIME) last_appl from v$archived_log

where (THREAD#=1 and SEQUENCE#=28040)

or (THREAD#=2 and SEQUENCE#=24386)

or (THREAD#=3 and SEQUENCE#=24259)

LAST_APPL

---------------

08-jun-07 19:12

Removing Old Archivelog files of a date (Replace ls with rm command)

alter session set nls_date_format='dd-mon-rr hh24:mi';

set lines 180

set pagesize 9999

select 'ls -l ' || name

from v$archived_log

where applied = 'YES'

and to_char(COMPLETION_TIME,'rrrrmmdd') between '20070602' and '20070604';

'RM-F'||NAME

---------------------------------------------------

ls -l /SID/arch/SID1/SID_2_23688.arc

ls -l /SID/arch/SID1/SID_1_27359.arc

ls -l /SID/arch/SID1/SID_3_23578.arc

...

ls -l /SID/arch/SID1/SID_2_23992.arc

ls -l /SID/arch/SID1/SID_2_23993.arc

ls -l /SID/arch/SID1/SID_3_23883.arc

ls -l /SID/arch/SID1/SID_1_27663.arc

917 rows selected.

RMAN Sofar done

SELECT sid, serial#, context, sofar, totalwork,

round(sofar/totalwork*100,2) "% Complete"

FROM v$session_longops

WHERE opname LIKE 'RMAN%'

AND opname NOT LIKE '%aggregate%'

AND totalwork != 0

AND sofar <> totalwork

find /backup/sid -name "SID_arch_*" -mmin +6000 -exec ls -l {} \; | awk '{sum += $5} {print

"Size GB:" sum/1024/1024/1024 }' | tail -1

Size GB:10.8984

find /backup/sid -name "SID_arch_*" -mmin +6030 -exec ls -l {} \;

Examples (Throughly test yourself and understand before use)

df -hP|grep <codetree>

du -sh *

ls -ltr|tail

ls -lSr|tail

ls -ltr|head

find . -iname *out -mtime +30 -exec ls -l {} \;

find . -mtime +3 -exec gzip -f {} \;

find . -mtime +60 -exec ls -l {} \;

find . -size +100000000c -xdev -exec ls -lrth {} \;

du -sj --->to sum up sizes

nohup find /SID/oracle/product/102/admin/SID_Host/bdump -size +100000000c -name "*.trc*" -mtime +15 -xdev -exec ls -l {} \; &

find . -size +100000000c -name "*log*" -mtime +1 -xdev -exec gzip -f {} \;

find . -size +100000000c -xdev -exec ls -ltr {} \;

find / -size +100000000c -xdev -type f -exec ls -lh {} \;

find . ! -name . -prune -size +100000000c -xdev -exec ls -lrth {} \;

find . ! -name . -prune -size +100000000c -xdev -exec du -sh {} \;

find . ! -name . -prune -name "core-*" -mtime +30 -exec ls -l {} \;

find /tmp ! -name . -prune -name "*.t" -mtime +15 -exec ls -ltrh {} \;

find /tmp ! -name . -prune -name "*.t" -mtime +15 -exec ls -l {} \;

find /tmp -name "*.tmp.gz" -mtime +15 -exec ls -ltrh {} \;

find /tmp -name "*.t" -mtime +1 -exec gzip -f {} \;

find . ! -name . -prune -name "*.t" -mtime +3 -exec gzip -f {} \;

find . ! -name . -prune -name "*.tmp" -mtime +15 -exec ls -ltrh {} \;

find . ! -name . -prune -name "*.tmp" -mtime +15 -exec ls -l {} \;

find . ! -name . -prune -name "*.tmp" -mtime +3 -exec gzip -f {} \;

find / -xdev -size '+5000k'|xargs ls -lh | grep -v dev |grep aptrvrbi

find . -xdev -size '+5000k'|xargs ls -lh | grep -v dev |grep iasrvrbi

find /tmp -xdev -size '+5000k'|xargs ls -l | grep -v dev|head -500

find . -xdev -size '+50000k'|xargs ls -ltr | grep -v dev

find . -xdev -size '+5000k'|xargs ls -ltrh | grep -v dev

find . ! -name . -prune -xdev -size '+5000k'|xargs ls -lh | grep -v dev

find . ! -name . -prune -xdev -size '+5000k'|xargs ls -ltrh | grep -v dev

find . ! -name . -prune -name "*.tmp" -mtime +30 -exec ls -ltr {} \;

find . ! -name . -prune -name "*.tmp" -mtime +30 -exec ls -l {} \;

find . -mtime +30 -size +100000000c -xdev -exec ls -ltrh {} \;

find . -size +100000000c -xdev -exec ls -ltr {} \;

find . -size +10000000c -xdev -exec ls -ltrh {} \;

find . -size +10000000c -xdev -exec ls -ltr {} \;

find . -size +10000000c -xdev -exec du -sk {} \;

find / -xdev -size '+10000k'|xargs ls -ld | grep -v dev |grep SID --->10000k files

find ./ -name "o1*.out" -size +3000k -mtime +10 -exec gzip {} \;

du -sk *|sort -n|tail -15

*applcsf log/out/tmp

*product *iAS/Apache/Apache *806 network/admin/log

*APPLMGR *common/admin log/out

find /SID/applmgr/common/admin/log/SID_oradev -mtime +60 -type f -exec ls -l {} \;

nohup find /SID/applcsf/log/SID_ERP01 -mtime +30 -exec ls -l {} \; &

nohup find /SID/applcsf/tmp -mtime +7 -exec gzip -f{} \; &

nohup find /SID/applcsf/log -mtime +7 -exec gzip -f {} \; &

nohup find /SID/applcsf/out -mtime +7 -exec gzip -f {} \; &

nohup find /SID/applcsf/tmp -mtime +3 -exec gzip -f {} \; &

nohup find /SID/applcsf/log -mtime +3 -exec gzip -f {} \; &

nohup find /SID/applcsf/ -mtime +3 -exec gzip -f {} \; &

nohup find /SID/applcsf/out -mtime +7 -exec gzip -f {} \; &

nohup find /SID/applcsf/out/SID_Host -mtime +7 -exec gzip -f {} \; &

find . -mtime +30 -exec ls -l {} \; --> purging files more than 30days old

find . -mtime +10 -exec gzip -f {} \; --> zipping files more than 7days old

find /tmp -name "*.t" -mtime +3 -exec gzip -f {} \;

find ./ -name "*.tmp" -mtime +15 -exec ls -l {} \;

find ./ -name "*.trc" -mtime +1 -exec gzip -f {} \;

find . -name '*trw' -mmin +240 -exec gzip {} \;

find /SID/3rdparty/apps/jboss-4.0.4.GA/server/default/log -name "*.log.*" -mtime +30 -exec ls -l {} \;

find . -mtime +7 -exec ls -lrt {} \;

find ./ -name "*core*" -mtime +1 -exec ls -ltr {} \;

df -k | sort -n | tail

nohup find /SID/applcsf/log/SID_Host -mtime +30 -exec ls -l {} \; &

find . -name "*.t" -exec ls -l {} \;

find /tmp -name "*.t" -mtime +3 -exec gzip -f {} \;

find /tmp -name "*.t" -mtime +30 -exec ls -l {} \;

find /tmp -name "*.t" -mtime +15 -exec ls -ltrh {} \;

find /tmp -name "*.TMP" -mtime +30 -exec ls -l {} \;

find /tmp -name "O*.t" -mtime +30 -user USER -maxdepth 1 -exec ls -l {} \;

find /tmp -name "O*.t" -mtime +7 -user USER -exec gzip -f {} \;

find . -name "*.t.Z" -mtime +30 -exec ls -l {} \;

find . -name "*.t" -exec gzip -f {} \;

nohup find . -name "*log*" -mtime +60 -exec ls -l {} \; &

nohup find . -name "*log*" -mtime +7 -exec gzip -f {} \; &

nohup find . -name "*log*" -mtime +30 -exec ls -l {} \; &

find . -name "*log*" -mtime +10 -exec gzip -f {} \;

find ./ -name "Events*" -mtime +30 -exec ls -l {} \;

find ./ -name "Events*" -mtime +30 -exec ls -ltrh {} \;

find ./ -name "Events*" -mtime +30 -exec ls -l {} \;

find . -name "*default-web-access.log.txt*" -mtime +30 -exec gzip -f {} \;

find ./ -name "XWII_BASE_APPS_*" -exec ls -l {} \;

*** find . ! -name . -prune -name "*.t" -mtime +30 -exec ls -ltr {} \; --->To find ".t" files without descending sub directories...

find . ! -name . -prune -name "*.t" -mtime +7 -exec gzip -f {} \;

find . -name "Exaaa*" -mtime +10 -exec ls -l {} \;

find ./ -name "*.trw" -mtime +60 -exec ls -l {} \;

nohup find ./ -name "*.trc*" -mtime +3 -exec gzip -f {} \; &

nohup find ./ -name "*.trw" -mtime +3 -exec gzip -f {} \; &

find ./ -name "*.out*" -mtime +30 -exec ls -l {} \;

find ./ -name "*.out" -mtime +30 -exec ls -l {} \;

find ./ -name "*.trc" -mtime +0 -exec ls -l {} \;

nohup find ./ -name "*.trc" -mtime +0 -exec gzip -f {} \;

nohup find ./ -name "*.trc*" -mtime +60 -exec ls -l {} \; &

nohup find ./ -name "*trc" -mtime +3 -exec gzip -f {} \; &

find ./ -name "*trc" -mtime +30 -exec ls -l {} \;

find . ! -name . -prune -size +100000000c -xdev -mtime +3 -exec ls -lrth {} \;

ls -l *.trc |grep " Dec 7" | awk '{print $9}'

gzip -f `ls -l *.trc |grep " Dec 7" | awk '{print $9}`

find /SID/backup/RMAN -name "data_full_*" -mtime +0 -type f -exec ls -l {} \;

zip error_log_pls.zip error_log_pls; >error_log_pls

zip stuck_rec.dbg.zip stuck_rec.dbg; >stuck_rec.dbg

zip stuck_rec.dbg.zip stuck_rec.dbg >/SID/backup/stuck_rec.dbg.zip

zip apps_pbgd1i.log.zip apps_pbgd1i.log; >apps_pbgd1i.log

zip SID.zip SID >/SID/applcsf/mailtmp/appclo1i.zip

zip mod_jserv.log_25sep2008.zip mod_jserv.log; >mod_jserv.log

zip Events148.log.zip Events148.log; >Events148.log

ls -l `ls -ltr *.dbf | grep "Apr 3" | awk '{print $9}'`

ls -ltrh *.dbf | grep "Apr 3"

ls -ltr *p*.zip* | awk '{ sum += $5 } { print "Size in GB " sum/1024/1024/1024}' |tail -1

nohup find /SID/applcsf/log -mtime +30 -exec ls -l {} \; &

nohup find /SID/applcsf/out -mtime +30 -exec ls -l {} \; &

nohup find /SID/applcsf/tmp -mtime +30 -exec ls -l {} \; &

find /tmp $ -name '*.t' $ -mtime +3 -size +1000 -exec ls -l {} \;

find ./ -name "*.trc" -mtime +1 -exec gzip -f {} \;

find ./ -name "tmp_*" -mtime +30 -exec ls -ltr {} \;

Finding directory sizes

du -k |sort -nr |head -20

du -g |sort -nr |head -20

find ./ -name "*.trc" -mtime +7 -exec ls -ltr {} \;

find . -size +100000000c -xdev -exec gzip {} \;

find . -size +100000000c -xdev -type f -exec ls -lh {} \;

find /appl/oracle/admin/SID/udump -name "*.trc*" -mtime +90 -exec ls -ltrh {} \;

find <path> -type f -print|xargs ls –l

Eg:

find /appl/formsA/oracle/product/dev6.0/reports60/server/cache -type f -print|xargs ls -l

find /data/a01/SID -name "*.arc" -mtime +5 -exec rm {} \;

find . -mtime +730 -type f –print -exec tar -cvf /tempspace/repservr_cache_2years.tar . \;

find /tempspace -mtime +730 -type f –print -exec tar -cvf /tempspace/repservr_cache_2years.tar {} \;

find /data/a01/SID -name "*.arc*" -mtime +5 -exec rm {} \;

find /data2/prod_export -name "comp_export_SID *.log" -mtime +30 -exec rm {} \;

Linux

ps -eo pid,user,vsz,rss,s,comm | sort -n -k 3,4

ps -eo pid,user,vsz,rss,args | sort -n -k 3,4 | tail -20

prstat -s rss

sar -W

swapon -s

SunOS

prstat -s rss

swap -l

- returns dev(vice)/low/blocks/free in 512-bytes blocks

ps -eo pid,user,vsz,rss,s,comm | sort -n -k 3,4 | tail -20

AIX

ps -efl | sort -n -k 10,10 | tail -50

ps -eo pid,user,vsz,rss,s,cmd | sort -n -k 3,4 | tail -20

ps -eo pid,user,vsz,comm | sort -n -k 3,3 | tail -20

SIZE

ps -eo pid,user,vsz,rss,s,comm | sort -n -k 3,4

vmstat

swapon -s

swap

usr/sbin/lsps -a

Real memory

usr/sbin/lsattr -HE -l sys0 -a realmem

HP-UX

ps -efl | sort -n -k 10,10 | tail -50

swapinfo

PCH

V linuxu:

pridat sloupec swap, dat do souboru a seradit:

top -b -n 1 >top.txt

cat top.txt | egrep "M|G" | sort -r -k 5,5 | more

V SUNu

ps -efl|sort -rk 10,10| head

Desaty sloupec je pouzita pamet ve strankach. Prikaz pagesize vypise velikost stranky, vynasobit a je to.

prstat -s rss

top -o size

swapinfo -t

swap -s

vmstat 4 6

HP-UX

swapinfo

swapinfo -m --->Memory information (interms of MB)

vmstat -S

vmstat -s

sar -w 5 5

show parameter sga_max_size

free -m

ps -ef |wc -l

No of processors

cat /proc/cpuinfo| grep processor| wc -l

cat /proc/cpuinfo | grep processor

Linux

free -m

Sun

prstat -t

prstat -s rss

/usr/sbin/prtconf | grep "Memory size"

df -k|grep swap

sar -w 5 5

prstat -t prstat -u pdb2i25 -s size

top -o size

swap -s

free

swapon -s

vmstat 4 4

ps auxw |tail -10--hpunix

lsattr -E -l sys0 -a realmem --- ram on aix

lsps -s -- swap space on aix

vmstat

swap -l

prtconf | grep Mem

swap SUNOS:16106834.6

vmstat -p 3

mpstat

ps -eo pid,pcpu,args | sort +1n %cpu

ps -eo pid,vsz,args | sort +1n kilobytes of virtual memory

/usr/ucb/ps aux |more Output is sorted with highest users (processes) of CPU and memory at the top

free --->swap information in kbytes

free -m -->swap information in mbytes

free -g -->swap information in gbytes

Solaris

$ /usr/sbin/prtconf grep -i "Memory size"

$ swap -s

$ df -k

$ /usr/local/bin/top

$ vmstat 5 100

$ sar -u 2 100

$ iostat -D 2 100

$ mpstat 5 100

For example:

$ man vmstat

Here is some sample output from these commands:

$ prtconf grep -i "Memory size"

Memory size: 4096 Megabytes

$ swap -s

total: 7443040k bytes allocated + 997240k reserved = 8440280k used, 2777096k available

$ df -k

Filesystem kbytes used avail capacity Mounted on

/dev/dsk/c0t0d0s0 4034392 2171569 1822480 55% /

/proc 0 0 0 0% /proc

fd 0 0 0 0% /dev/fd

mnttab 0 0 0 0% /etc/mnttab

/dev/dsk/c0t0d0s3 493688 231339 212981 53% /var

swap 2798624 24 2798600 1% /var/run

swap 6164848 3366248 2798600 55% /tmp

/dev/vx/dsk/dcdg01/vol01

25165824 23188748 1970032 93% /u01

/dev/vx/dsk/dcdg01/vol02

33554432 30988976 2565456 93% /u02

...

$ top

last pid: 29570; load averages: 1.00, 0.99, 0.95 10:19:19

514 processes: 503 sleeping, 4 zombie, 6 stopped, 1 on cpu

CPU states: 16.5% idle, 17.9% user, 9.8% kernel, 55.8% iowait, 0.0% swap

Memory: 4096M real, 46M free, 4632M swap in use, 3563M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND

29543 usupport 1 35 0 2240K 1480K cpu2 0:00 0.64% top-3.5b8-sun4u

13638 usupport 11 48 0 346M 291M sleep 14:00 0.28% oracle

13432 usupport 1 58 0 387M 9352K sleep 3:56 0.17% oracle

29285 usupport 10 59 0 144M 5088K sleep 0:04 0.15% java

13422 usupport 11 58 0 391M 3968K sleep 1:10 0.07% oracle

6532 usupport 1 58 0 105M 4600K sleep 0:33 0.06% oracle

...

$ vmstat 5 100

procs memory page disk faults cpu

r b w swap free re mf pi po fr de sr f0 s1 s1 s1 in sy cs us sy id

0 1 72 5746176 222400 0 0 0 0 0 0 0 0 11 9 9 4294967196 0 0 -19 -6 -103

0 0 58 2750504 55120 346 1391 491 1171 3137 0 36770 0 37 39 5 1485 4150 2061 18 8 74

0 0 58 2765520 61208 170 272 827 523 1283 0 3904 0 36 40 2 1445 2132 1880 1 3 96

0 0 58 2751440 58232 450 1576 424 1027 3073 0 12989 0 22 26 3 1458 4372 2035 17 7 76

0 3 58 2752312 51272 770 1842 1248 1566 4556 0 19121 0 67 66 12 2390 4408 2533 13 11 75

...

$ iostat -c 2 100

cpu

us sy wt id

15 5 13 67

19 11 52 18

19 8 44 29

12 10 48 30

19 7 40 34

...

$ iostat -D 2 100

sd15 sd16 sd17 sd18

rps wps util rps wps util rps wps util rps wps util

7 4 9.0 6 3 8.6 5 3 8.1 0 0 0.0

4 22 16.5 8 41 37.9 0 0 0.7 0 0 0.0

19 34 37.0 20 24 37.0 12 2 10.8 0 0 0.0

20 20 29.4 24 37 51.3 3 2 5.3 0 0 0.0

28 20 40.8 24 20 42.3 1 0 1.7 0 0 0.0

...

$ mpstat 2 100

CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl

0 115 3 255 310 182 403 38 72 82 0 632 16 6 12 66

1 135 4 687 132 100 569 40 102 68 0 677 14 5 13 68

2 130 4 34 320 283 552 43 94 63 0 34 15 5 13 67

3 129 4 64 137 101 582 44 103 66 0 51 15 5 13 67

HP-UX 11.0:

top

Glance/GlancePlus

sam

/etc/swapinfo -t

/usr/sbin/swapinfo -t

ipcs -mop

Would it be safe to say that to view memory usage by user, execute the

following:

UNIX95= ps -e -o ruser,pid,vsz=Kbytes

...and to view shared memory usage, such as for Oracle processes, using the

following:

ipcs -bmop

$ grep Physical /var/adm/syslog/syslog.log

$ df -k

$ sar -w 2 100

$ sar -u 2 100

$ /bin/top

$ vmstat -n 5 100

$ iostat 2 100

$ top

For example:

$ grep Physical /var/adm/syslog/syslog.log

Nov 13 17:43:28 rmtdchp5 vmunix: Physical: 16777216 Kbytes, lockable: 13405388 Kbytes, available: 15381944 Kbytes

$ sar -w 1 100

HP-UX rmtdchp5 B.11.00 A 9000/800 12/20/02

14:47:20 swpin/s bswin/s swpot/s bswot/s pswch/s

14:47:21 0.00 0.0 0.00 0.0 1724

14:47:22 0.00 0.0 0.00 0.0 1458

14:47:23 0.00 0.0 0.00 0.0 1999

14:47:24 0.00 0.0 0.00 0.0 1846

...

$ sar -u 2 100 # This command generates CPU % usage information.

HP-UX rmtdchp5 B.11.00 A 9000/800 12/20/02

14:48:02 %usr %sys %wio %idle

14:48:04 20 2 1 77

14:48:06 1 1 0 98

...

$ iostat 2 100

device bps sps msps

c1t2d0 36 7.4 1.0

c2t2d0 32 5.6 1.0

c1t0d0 0 0.0 1.0

c2t0d0 0 0.0 1.0

...

AIX:

$ /usr/sbin/lsattr -E -l sys0 -a realmem

$ /usr/sbin/lsps -s

$ vmstat 5 100

$ iostat 2 100

$ /usr/local/bin/top # May not be installed by default in the server

For example:

$ /usr/sbin/lsattr -E -l sys0 -a realmem

realmem 33554432 Amount of usable physical memory in Kbytes False

NOTE: This is the total Physical + Swap memory in the system.

Use top or monitor command to get better breakup of the memory.

$ /usr/sbin/lsps -s

Total Paging Space Percent Used

30528MB 1%

Linux [RedHat 7.1 and RedHat AS 2.1]:

$ dmesg grep Memory

$ vmstat 5 100

$ /usr/bin/top

For example:

$ dmesg grep Memory

Memory: 1027812k/1048568k available (1500k kernel code, 20372k reserved, 103k d)$ /sbin/swapon -s

Tru64

$ vmstat -P grep -i "Total Physical Memory ="

$ /sbin/swapon -s

$ vmstat 5 100

For example

$ vmstat -P grep -i "Total Physical Memory ="

Total Physical Memory = 8192.00 M

$ /sbin/swapon -s

Swap partition /dev/disk/dsk1g (default swap):

Allocated space: 2072049 pages (15.81GB)

In-use space: 1 pages ( 0%)

Free space: 2072048 pages ( 99%)

Total swap allocation:

Allocated space: 2072049 pages (15.81GB)

Reserved space: 864624 pages ( 41%)

In-use space: 1 pages ( 0%)

Available space: 1207425 pages ( 58%)

Please take at least 10 snapshots of the "top" command to get an idea

aboud most OS resource comsuming processes in the server and the different

snapshot might contain a few different other processes and that will indicate

that the use of resouces are varying pretty quickly amound many processes.

AIX:

/usr/sbin/lsattr -E -l sys0 -a realmem

/usr/sbin/lsps -s

HP-UX:

grep Physical /var/adm/syslog/syslog.log

/usr/sbin/swapinfo -t

Linux:

cat /proc/meminfo | grep MemTotal

/sbin/swapon -s

Solaris:

/usr/sbin/prtconf | grep "Memory size"

/usr/sbin/swap -s

Tru64:

vmstat -P| grep -i "Total Physical Memory ="

/sbin/swapon -s

LONG BIT

getconf LONG_BIT

Huge Pages

grep -i huge /etc/sysctl.conf

Oracle Apps DBA

Pages

Friday 17 January 2014

Apps Script

Query for Scheduled request start on prior run less than 5 mins

Monday, June 11, 2012

Script to find who has changed the sysadmin Password

Monday, May 28, 2012

How to find all Form Personalization through script in Oracle APPS

Wednesday, April 25, 2012

Number of times Purge Concurrent Request execution was run in last 30 days

Number of times gather schema stats was run in last 30 days

Concurrent Requests Average/Max/Min hours runtime details

Tuesday, April 24, 2012

OS commands Very useful for DBAs and OS files management

No comments:

Post a Comment

About Me

Report Abuse

Link

Social Media Icons

Follow Us

Football

Football

Recent Posts

Popular Posts

Pages :

Categories

Followers

Search This Blog

Pages - Menu

Pages :

Popular Posts

Follow Us