SOME USEFUL APPS DBA COMMANDS

PLEASE RUN THESE COMMANDS ON TEST FIRST THEN USE ON PRODUCTION.

Performance Monitoring
----------------------
select sid,serial#,program,status,module,last_call_et from v$session
where last_call_et>18000

select sid,sql_hash_value from v$session where sid in (select sid from v$session_wait where event like 'global cache cr request') order by 2

select sum(blocks*8192)/1024/1024/1024 from v$tempseg_usage

select name , lockid from dbms_lock_allocated

select table_name from dict where table_name like 'HZ%'

select * from gv$resource_limit

select name, kind, partition_name, sum(xnc) from v$cache_transfer
group by name, kind, partition_name order by 4

select JOB,WHAT,NEXT_DATE,BROKEN,INTERVAL from dba_jobs

Temp usage : -- select sum(blocks*8192)/1024/1024/1024 from gv$tempseg_usage

Kill all inactive old sessions
------------------------------
Select 'alter system kill session '''||sid||','||serial#||''';'
from V$session
where
status='INACTIVE'
and type <> 'BACKGROUND'
and SID>100
and last_call_et>18000

killing snipped sessions
------------------------------
--get spid

select p.spid from v$process p,v$session s where s.paddr=p.addr and
s.status='SNIPED'

kill -9 p.spid

select 'kill -9 ' || p.spid from v$process p,v$session s where s.paddr=p.addr and s.status='SNIPED'
and s.username='BOLBOL'


select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id

Kill inactive sessions which are more than 5 hrs old
----------------------------------------------------
select sid,serial#,program,status,module,last_call_et from v$session
where sid>50 and last_call_et>18000 and status='INACTIVE' and type <> 'BACKGROUND'

Long operations
-----------------
select inst_id,sid,serial#, (ELAPSED_SECONDS + TIME_REMAINING) TOTAL_TIME, TIME_REMAINING,
100 - ((TIME_REMAINING/(ELAPSED_SECONDS + TIME_REMAINING)) * 100) PERCENTAGE_COMPLETE
FROM gV$SESSION_LONGOPS WHERE TIME_REMAINING>0


Kill concurrent request processes
---------------------------------
Get oracle_session_id from FND_CONCURRENT_REQUESTS
get sid,serial# from v$session with ausid=oracle_session_id
alter system kill sid,serial#



Set SQL command prompt
-------------------------------------------------
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '


Kill processes
-----------------------------

kill -9 `ps -ef | grep 'LOCAL=NO' | awk '{ print $2 }'`

kill -9 `ps -ef | grep f60runm | awk '{ print $2 }'`

Waits
-----------------
select event,count(*) from v$session_wait group by event'


To see the applied patches
--------------------------
select count(*) from ad_bugs where BUG_NUMBER='&bug_no';
select count(*) from ad_applied_patches where PATCH_NAME='&patch_name';


To know the patch lebel of a product in APPS
==================================================

select a.application_name,decode(b.status,'I','Installed','S','Shared','N/A') STATUS,PATCH_LEVEL
from APPS.fnd_application_vl a,APPS.fnd_product_installations b
where a.application_id = b.application_id order by 2,1;


To see the free space in tablespaces
------------------------------------
select TABLESPACE_NAME,sum(BYTES)/1024/1024 from dba_free_space
group by TABLESPACE_NAME


To remove/move the file by date
----------------------------
ls -lrt *.gz |grep "Apr 20"|grep "Apr 21"| awk '{print "mv " $9 " copied" }'> mv.sh
ls -lrt *.gz |grep -v xxxxx | awk '{print "mv " $9 " /arch_prd1" }'> mv.sh

ls -l |grep "Jul 20" |awk '{print "rm "$9}' >rmglo.sh

ls -ltr|grep "Feb 7"|grep trc|awk '{print $9}'|xargs -I {} -t rm {}

ls -ltr|grep trc|awk '{print $9}'|xargs -I {} -t rm {}


To kill all FNDLIBR processes
-----------------------------
ps -ef|grep FNDLIBR|grep -v grep | awk '{print "kill -9 " $2}' > killfndlibr.sh


To get the list of files on size basis
--------------------------------------
ls -l |sort +4rn |more


TO copy the files remotely
----------------------------
nohup rcp -p -r oraprosp@kmc2prospsuncrege:/export/ora9.2.0.4_64/* . &


To find out the error in patch log file
------------------------------------------
egrep -i 'ORA-|FRM-|APP-|REP-|Err|Fail|Warn'


For Split
----------
$split -60 amt79_copy.sh amt79_x$ls -l amt79_xa? | awk '{print "nohup ./"$NF" > "$NF".out 2> "$NF".err &"}' > master_amt79_copy.sh$chmod a+x amt79_x*

$ls -l xa* | awk '{print "nohup ./"$NF" > "$NF".out 2> "$NF".err &"}' > master_frsbx_copy.sh



nohup ./xaa > xaa.out 2> xaa.err &
nohup ./xab > xab.out 2> xab.err &



To startup Workflow
--------------------
nohup /oracle/apps/R11isbx/sbxappl/fnd/11.5.0/bin apps/sysuat@uat 0 Y /oracle/apps/R11isbx/sbxappl/fnd/11.5.0/resource/wfmail.cfg &


To Check Work flow process
----------------------------
ps -ef |grep -i wfmail


To Check ULIMIT OutPut
-----------------------
/export/home/oracle: ulimit -a

To cancel running concurrent request
----------------------------------
sqltweof>update fnd_concurrent_requests set PHASE_CODE='C',STATUS_CODE='E' where request_id='3477766';


To find out port number which is in used
----------------------------------------
netstat -a



To test mailx
--------------
echo "Test mail" |mailx -s "test" mailid@mailserver.com

echo "Test Mail from OLM using mailx db node 1 " |mailx -s "Test Mail from OLM using mailx db node 1" rupam.bora@airtel.in
echo "Test Mail from OLM using mailx db node 2 " |mailx -s "Test Mail from OLM using mailx db node 2" rupam.bora@airtel.in

echo "Test Mail from OLM using mailx db node 1 " |mailx -s "Test Mail from OLM using mailx db node 1" crm.dba@airtel.in
echo "Test Mail from OLM using mailx db node 2 " |mailx -s "Test Mail from OLM using mailx db node 2" crm.dba@airtel.in


echo "Test Mail from OLM using mailx db node 1 " |mailx -s "Test Mail from OLM using mailx db node 1" bpsingh@in.ibm.com
echo "Test Mail from OLM using mailx db node 2 " |mailx -s "Test Mail from OLM using mailx db node 2" bpsingh@in.ibm.com

echo "Test Mail from OLM using mailx db node 1 " |mailx -s "Test Mail from OLM using mailx db node 1" manmoha1@in.ibm.com
echo "Test Mail from OLM using mailx db node 2 " |mailx -s "Test Mail from OLM using mailx db node 2" santkum2@in.ibm.com

If patch has been applied on DBTIER and u r getting prereq error on FOrms tier..
Then follow this command while applying patch
---------------------------------------------------
$adpatch options=novalidate,noprereq


To find Java Virtual Machine version :
--------------------------------------
$ java -version

To find OS Version
------------------
$ uname -a


To find Available disk space
-----------------------------
$ df -b


To find Number of CPUs
-----------------------------
$ uname -X

$lsdev -C |grep Process|wc -l

To find Physical RAM on server
-------------
$ prtconf |pg




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

To find the O/S level packages which are installed
=========================================================
pkginfo | grep -i





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


To know the operating system ...32 bit or 64 bit
------------------------------------------------

/export/home/frcrp: isainfo -v
64-bit sparcv9 applications
32-bit sparc applications
/export/home/frcrp: uname -a
SunOS gecmsn05 5.8 Generic_108528-15 sun4u sparc SUNW,Ultra-4
/export/home/frcrp: isainfo -vk
64-bit sparcv9 kernel modules


to extract CPIO file
-------------------------

cpio -idmcv <

In HP to know RAM details
=============================

vishnuvardhan_v: grep -i Physical /var/adm/syslog/syslog.log
vishnuvardhan_v: http://unixguide.net/cgi-bin/unixguide.cgi


# /usr/sbin/dmesg | grep "Physical:" (to know the RAM size on HP)


To know swap size in HP
=======================
# /usr/sbin/swapinfo -a

To know the O/S bit version
=============================

/bin/getconf KERNEL_BITS


prtdiag---location
---------------------------------

/usr/share/man/sman1m/prtdiag.1m
/usr/platform/SUNW,Sun-Fire-V240/sbin/prtdiag
/usr/platform/SUNW,Sun-Fire-V250/sbin/prtdiag
/usr/platform/sun4u/sbin/prtdiag

to know more about the printers
================================

/etc/lp/interfaces
/etc/lp/printers


to know the printer status
----------------------------


# lpstat -p

# lpstat -a

$ lp adstrtal.sh ....to test the pint from unix level
When multiple printers:
lp -d 2105nw_phaser text.out (note the space after the -d)
lpr -P2105nw_phaser text.out (note that there is no space after the -P)

To remove job 469 from the color printer, you would type:
cancel 2105nw_phaser-2241
lprm -P2105nw_phaser 2241

# lpstat -v ---to know the printer names and printer queue names


to know the particular user responsibilities
=============================================
select c.user_id,a.RESPONSIBILITY_ID, b.RESPONSIBILITY_NAME from FND_USER_RESPONSIBILITY a,FND_RESPONSIBILITY_TL b, fnd_user c where c.user_name='HACKLN' and c.user_id=a.user_id and a.RESPONSIBILITY_ID=b.RESPONSIBILITY_ID ;



to check last issued commonds(with reboot comnds also)
=======================================================

last|more



To check the server reboot time from the last reboot
====================================================
uptime


To compile invalid objects in the database.
==============================================
exec utl_recomp.recomp_parallel(4)


TO Unset two task
=====================
unset two_task


CronTab values
===============
minute (0-59),
hour (0-23),
day of the month (1-31),
month of the year (1-12),
day of the week (0-6 with 0=Sunday).


To know Jserv status
======================

http://hostname.domainname:portnumber/servlets/IsItWorking

http://gecmsn05.cf.capital.ge.com:8010/servlets/IsItWorking

To check mailx command
===========================

applprod (PROD)$ mailx -v tejinder.singla@ge.com
Subject: Hello
.
EOT
applprod (PROD)$ tejinder.singla@ge.com... Connecting to smtpmail.corporate.ge.com via relay...
tejinder.singla@ge.com... Deferred: Connection refused by smtpmail.corporate.ge.com

applprod (PROD)$
-------------------------------------------------------

sendmail -V



To move Datafile to another location
========================================

Because of space problems moved '/vg03s1/edwdata/edwdata08.dbf to '/vg03s0/edwdata/edwdata08.dbf


alter tablespace EDWDATA rename datafile '/vg03s1/edwdata/edwdata08.dbf' to '/vg03s0/edwdata/edwdata08.dbf';


Add A Datafile To An Existing Tablespace
========================================
ALTER TABLESPACE ADD DATAFILE '' SIZE K|M;

e.g. ALTER TABLESPACE tools ADD DATAFILE 'c:\oracle\oradata\orabase\tools02.tom' SIZE 20M;


ALTER DATABASE CREATE DATAFILE '' SIZE K|M AS '';

e.g. ALTER DATABASE CREATE DATAFILE 'c:\oracle\oradata\orabase\uwdata03.dbf' AS 'UWDATA';


To add datafile in RAW DEVICES
==================================

to add a datafile in a tablesapce in O&G(for raw devices)


ls -ltr ---to check the datafiles which are existing ..

ls -altr free* ----to check the raw devices which we have in that instance


mv (follow the naming structure with existing datafiles)


go to sql and add a datafile to a tablespace using alter tablespace command with the size of rawdevice file size



To know the package body from a procedure
=============================================

SELECT TEXT FROM ALL_SOURCE WHERE NAME LIKE 'GCFEXT_AP_AUOR2AB1_PKG'


To find the table size
========================

first method----to calculate table size

select sum(bytes)/1024/1024
from dba_segments
where SEGMENT_NAME='TABLE_NAME';


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

second method


analyze table my_table compute statistics;
or
analyze table my_table estimate statitics;

And then, execute the query
select blocks from user_tables where table_name='SIZE';


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
analyze table tabe_name compute statistics;
and then give
select num_rows,blocks ,empty_blocks as
empty,avg_space,chain_cnt,avg_row_len from dba_tables
where table_name='table_name';
now
blocks * db_block_size = table size

or

select segment_name Table_Name, sum(bytes) / 1024 / 1024 MB from user_segments
Where segment_type = 'TABLE' and Segment_Name like '&Table_search_string'
Group by segment_name;


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

To know particular user responsibility
=============================================


select c.user_id,a.RESPONSIBILITY_ID, b.RESPONSIBILITY_NAME from FND_USER_RESPONSIBILITY a,FND_RESPONSIBILITY_TL b, fnd_user c where c.user_name='HACKLN' and c.user_id=a.user_id and a.RESPONSIBILITY_ID=b.RESPONSIBILITY_ID ;


To find the any object version like *.pll,fmb and etc
========================================================

- go to the corresponding directory
- execute one of the following commands to get the version of the file
on all platforms (beginning with Oracle Applications v. 11.x) :
adident Header
on Unix :
strings -a | grep Header
on Windows (DOS box) :
find "Header"


To find a file under subdirectorys
=================================

$ ls -R|grep " " (here finding the space in the file/dir name)


1. To determine the physical RAM size, enter one of the following commands:
==========================================================================



Platform Command

AIX #/usr/sbin/lsattr -E -l sys0 -a realmem
HP-UX # grep "Physical:" /var/adm/syslog/syslog.log
Linux # grep MemTotal /proc/meminfo
Solaris # /usr/sbin/prtconf | grep "Memory size"
Tru64 UNIX # /bin/vmstat -P | grep "Total Physical Memory"



2. To determine the size of the configured swap space, enter one of the following commands:
===========================================================================================

Platform Command
AIX # /usr/sbin/lsps -a
HP-UX # /usr/sbin/swapinfo -a
Linux # grep SwapTotal /proc/meminfo
Solaris # /usr/sbin/swap -s
Tru64 UNIX # /sbin/swapon -s



To determine whether the Oracle Inventory group exists, enter the following command:
====================================================================================

AIX, Linux x86, or Linux Itanium:
# more /etc/oraInst.loc
Other operating systems:
# more /var/opt/oracle/oraInst.loc

Remove archives by number , archive format: arch_prd2_2_54375.arc.gz
==========================================================
echo "Enter instance no [1 or 2 ? ] "
read instno
echo "Enter archive no : "
read archno
echo "Deleting archives of instance " $instno " , below seq no : " $archno
for name in `ls *.gz`
do
ino=`echo $name|cut -c9-9`
if [ $ino -eq $instno ]
then
no=`echo $name|cut -c13-17`
if [ $no -lt $archno ]
then
echo " Removing file : " $name
#rm $name
fi
fi
done
#################################################################################

ps -aefo pid,pcpu,vsz,args|awk '{if ($2>1) print $1,$2,$3,$4}'