RS (Oracle Apps DBA Blog)

Set the env for oracle user.

1. Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/oms; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:/opt/IBMJava2-131/bin:$ORACLE_HOME/bin:$PATH; export PATH
PATH=$PATH:$ORACLE_HOME/dcm/bin:$ORACLE_HOME/opmn/bin; export PATH
PATH=$PATH:$ORACLE_HOME/Apache/Apache/bin; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

2. DISPLAY=:0.0; export DISPLAY

3. ./runInstaller

4. The installation will automatically install an agent in the agent home.

5. Agent installation on another node to connect with this 10g enterprise manager.

6. start and stop services.

To stop all the Grid Control components on a host, follow these steps:

Stop the OMS.

$PROMPT> $ORACLE_HOME/bin/emctl stop oms

Stop the Application Server Control Console, which is used to manage the Oracle Application Server instance used to deploy the Management Service.

$PROMPT> $ORACLE_HOME/bin/emctl stop iasconsole

Stop all the application server components, such as Oracle HTTP Server, OracleAS Web Cache.

$PROMPT> $ORACLE_HOME/opmn/bin/opmnctl stopall

Wait for four to five minutes to ensure that all the OPMN processes are stopped and TCP ports are released.

Change directory to the home directory for the Oracle Management Agent and stop the Management Agent.

$PROMPT> AGENT_HOME/bin/emctl stop agent
----------------------------------------------------------------------------------

To start all the Grid Control components on a host, follow these steps:

$PROMPT> $ORACLE_HOME/bin/emctl start oms

$PROMPT> $ORACLE_HOME/bin/emctl start iasconsole

$PROMPT> $ORACLE_HOME/opmn/bin/opmnctl startall

$PROMPT> AGENT_HOME/bin/emctl start agent

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

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}'

RS (Oracle Apps DBA Blog)

Tuesday, 3 June 2008

Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i

Upgrading Developer 6i with Oracle Apps 11i (Patchset 18 4948577)

Saturday, 31 May 2008

Good Metalinks Notes Configuring Oracle Applications Release 11i with 10g R2 RAC and ASM

Friday, 30 May 2008

HOW TO RESET SYSADMIN PASSWORD

Thursday, 29 May 2008

10g GRID CONTROL ENTERPRISE MANAGER INSTALLATION.

APPLICATION FORM METRIC SERVER CLIENT ADDITION IN 11i

SOME USEFUL APPS DBA COMMANDS

About Me

Blog Archive