OS Level Command
select owner, table_name, num_rows, LAST_ANALYZED
From dba_tables
where table_name in (
‘AP_INVOICES_ALL’, ‘AP_INVOICE_DISTRIBUTIONS_ALL’, ‘AR_PAYMENT_SCHEDULES_ALL’,
‘RA_CUSTOMER_TRX_ALL’, ‘RA_CUSTOMER_TRX_LINES_ALL’ ,
‘HZ_PARTIES’, ‘HZ_CUST_ACCOUNTS’,
‘AS_SALES_LEADS’, ‘AS_ACCESSES_ALL_ALL’,
‘BOM_STRUCTURES_B’, ‘BOM_COMPONENTS_B’,
‘CS_INCIDENTS_ALL_B’,
‘FA_ADJUSTMENTS’, ‘FA_DEPRN_DETAIL’, ‘FA_DEPRN_SUMMARY’,
‘FND_USERS’,
‘GL_JE_HEADERS’, ‘GL_JE_LINES’,
‘MRP_GROSS_REQUIREMENTS’, ‘MRP_RECOMMENDATIONS’, ‘MRP_FULL_PEGGING’,
‘MRP_BOM_COMPONENTS’, ‘MTL_MATERIAL_TRANSACTIONS’,
‘MTL_TRANSACTION_ACCOUNTS’, ‘MTL_SYSTEM_ITEMS_B’,
‘HR_ORGANIZATION_INFORMATION’, ‘HR_OPERATING_UNITS’,
‘MTL_PARAMETERS’,
‘OE_ORDER_HEADERS_ALL’, ‘OE_ORDER_LINES_ALL’,
‘PO_HEADERS_ALL’, ‘PO_LINES_ALL’, ‘PO_VENDORS’,
‘WF_ITEM_ACTIVITY_STATUSES’, ‘WF_ITEM_ATRIBUTE_VALUES’,
‘WF_NOTIFICATIONS’, ‘WF_NOTIFICATION_ATTRIBUTES’ ,
‘WSH_DELIVERY_DETAILS’ , ‘WSH_DELIVERY_ASSIGNMENTS’,
‘WSH_NEW_DELIVERIES’, ‘WSH_DELIVERY_LEGS’,
‘WSH_TRIP_STOPS’, ‘WSH_TRIPS’ )
order by table_name ;
COL APPLICATION_NAME FORMAT A60
COL SHT_NAME FORMAT A10
col PATCH_LEVEL FORMAT A20
SELECT A.APPLICATION_SHORT_NAME SHT_NAME,T.APPLICATION_NAME, I.STATUS,
NVL(I.PATCH_LEVEL, ‘n/a’) PATCH_LEVEL, I.DB_STATUS
FROM FND_PRODUCT_INSTALLATIONS I,
FND_APPLICATION A,
FND_APPLICATION_TL T
WHERE A.APPLICATION_ID = I.APPLICATION_ID
AND A.APPLICATION_ID = T.APPLICATION_ID
AND T.LANGUAGE = USERENV(‘LANG’)
ORDER BY 1 ;
set pagesize 30
select NVL(tablespace_name,’** Total **’) “Tablespace Name”,
sum(“allocated”) “Allocated(M)”,
sum(“used”) “Used(M)”,
sum(“free”) “Free(M)”,
sum(df_cnt) “#(File)”
from
(
select a.tablespace_name, trunc(b.assigned/1048576) “allocated”,
trunc((b.assigned-a.free)/1048576) “used”,
trunc(a.free/1048576) “free”,
df_cnt
from
(
select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name ) a,
(
select tablespace_name, sum(bytes) assigned, count(*) df_cnt
from dba_data_files
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
UNION ALL
SELECT tablespace_name||'[TMP]’, trunc(sum(bytes)/1048576), null, null, count(*) df_cnt
from dba_temp_files
group by tablespace_name
)
group by rollup(tablespace_name) ;
du -a .| sort -n -r |head -n 10
** my session details **
select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1);
**GRid agent bounce **
emctl status agent
emctl stop agent
emctl clearstate agent
emctl start agent
emctl status agent
******To check top 5 process consuming more cpu*****
ps axo pcpu,comm,pid,ruser,time,etime,args | sort -nr | head -n 5
**** To check top 5 process consuming more memory ****
ps auxf | sort -nr -k 4 | head -5
*********************************
To kill LIBR process
ps -ef | grep applmgr |awk ‘{print “kill -9 ” $2 }’
To kill LOCAL-NO process—
kill -9 `ps -ef |grep LOCAL=NO | grep gcasq | awk ‘{print $2}’`
crs_stat -t
crsctl check crs
srvctl start nodeapps -n hostname
srvctl start instance -d utyeb05 -i utyeb051
SSO-Degistations issue:
[applmgr@hostname bin]$ ldapbind -h -p 389 -D “cn=orcladmin” -w
bind successful
ls -ltr | grep ‘Aug 28’ |grep -v ‘.Z’ |awk ‘{print “gzip ” $9 ” & ” }’ > Aug28.sh
select INSTANCE_NUMBER,SQL_ID,USER_ID,MODULE,ACTION from DBA_HIST_ACTIVE_SESS_HISTORY where MODULE=’&Module’;
select CONCURRENT_QUEUE_NAME,NODE_NAME,TARGET_NODE,NODE_NAME2 from fnd_concurrent_queues;
select ‘drop database link ‘||DB_LINK||’;’ from dba_db_links;
update fnd_concurrent_queues set NODE_NAME=’hostnameRS06′, NODE_NAME2=’hostnameRS06′,TARGET_NODE=’hostnameRS06′ where ENABLED_FLAG=’Y’;
srvctl status database -d utyeb05
ALTER SESSION SET NLS_DATE_FORMAT =’DD-MON-YY HH24:MI:SS’;
select sysdate from dual;
set lines 300
select INST_ID,HOST_NAME,INSTANCE_NAME,TO_CHAR(STARTUP_TIME, ‘DD-MON-RR HH24:MI:SS’) FROM gv$instance;
col “Conc_Node” for a20
col “Conc_Node” for a10
col “Form_Node” for a10
col “Web_Node” for a10
col Admin_Node for a10
col DB_Node for a10
select NODE_NAME,
decode(SUPPORT_CP,’Y’,’Yes’,’No’) “Conc_Node”,
decode(SUPPORT_FORMS,’Y’,’Yes’,’No’) “Form_Node”,
decode(SUPPORT_WEB,’Y’,’Yes’,’No’) “Web_Node”,
decode(SUPPORT_ADMIN,’Y’,’Yes’,’No’) “Admin_Node”,
decode(SUPPORT_DB,’Y’,’Yes’,’No’) “DB_Node”,
decode(STATUS,’Y’,’Enabled’,’Not Enabled’) “Status”
from apps.fnd_nodes order by 1;
************************************************************************************************************************
*** Users are not able to view responsiblity getting error like “There are no active responsibilities available for this user.” ***
CONCSUB APPS/appsdev SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND FNDFMRTC PROGRAM_NAME='”Synchronize WF LOCAL Tables”‘
bounce apache.
OR
Please run below con. and see its helpful
– “Sync responsibility role data into the WF table.”
– “Synchronize WF LOCAL tables”
– “Workflow Directory Services User/Role Validation”
************************************************************************************************************************
EXPLAIN PLAN for SQL_ID
select * from table (dbms_xplan.display_awr(‘c53c4b8d0yxhz’));
************************************************************************************************************************
to find the version of package from database;
select text from dba_source where name like ‘ECS_AR_EDI810_OUTINT_PKG’ and line < 10
************************************************************************************************************************
IF opmn services is not coming up then do clean shutdown of all application services then remove following directories.
rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*
then start opmn services and check services.
************************************************************************************************************************
SELECT distinct frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name =’&user_name’
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;
************************************************************************************************************************
SIZE of the database
select a.data_size+b.temp_size+c.redo_size “total_size”
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes)/1024/1024/1024,0) temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c;
************************************************************************************************************************
***************** Track redo generation by day in GB *****************
select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024/1024)) “REDO PER DAY (MB)”
from v$archived_log
group by trunc(completion_time)
order by 1;
************************************************************************************************************************
—– To Check java heap size per process ——
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = ‘FNDCPOPP’);
***************** Scheduled Tasks That Failed**************************
COL log_id FORMAT 9999 HEADING ‘Log#’
COL log_date FORMAT A40 HEADING ‘Log Date’
COL owner FORMAT A06 HEADING ‘Owner’
COL job_name FORMAT A30 HEADING ‘Job’
COL status FORMAT A10 HEADING ‘Status’
COL actual_start_date FORMAT A50 HEADING ‘Actual|Start|Date’
COL error# FORMAT 999999 HEADING ‘Error|Nbr’
TTITLE ‘Scheduled Tasks That Failed:’
select
log_date,
job_name,
status,
req_start_date,
actual_start_date,
run_duration
from
dba_scheduler_job_run_details
where
— job_name = ‘MYJOB’
status <> ‘SUCCEEDED’
order by
actual_start_date;
******************************************
select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj
Standby Database ************************
run {
allocate channel d1 device type disk format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbybak’;
allocate channel d2 device type disk format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbybak’;
backup as compressed backupset incremental level 0 format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbybak’ database include current controlfile for standby;
sql “alter system archive log current”;
backup archivelog all format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbyarch’ ;
}
run
{
duplicate target database for standby dorecover;
}
Put the standby database in managed recovery mode and open the primary database with the new protection mode enabled:
SQL> — [Connect to the standby database]
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
The protection modes run in the order from highest (most data protection) to the lowest (least data protection):
Protection , Maximum Availablity and Maximum performance.
alter system set log_archive_dest_1=’LOCATION=E:\rman’;
*********************Block Corruption**********************
DECLARE num_corrupt INT;
CURSOR cur_table IS
SELECT table_name
FROM dba_tables d
WHERE d.owner=’EXPRESS’;
BEGIN
FOR rec_table IN cur_Table
LOOP
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => ‘EXPRESS’,
OBJECT_NAME => rec_Table.table_name,
REPAIR_TABLE_NAME => ‘REPAIR_TABLE’,
CORRUPT_COUNT => num_corrupt);
IF num_corrupt > 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Table Name : ‘||rec_table.table_name||’ number corrupt: ‘ || TO_CHAR (num_corrupt));
END IF;
END LOOP;
END;
select owner, segment_name, segment_type from dba_extents where file_id = 60 and 25759 between block_id and block_id + blocks -1;
***********************************************************************************************
##Script to monitor>>>>>>>
while true
do
sqlplus -s “/ as sysdba” <set linesize 140
set pagesize 9999
col message for a60
select sum(bytes)/1024/1024/1024 as “TOTAL UNDO SIZE” from dba_data_files where tablespace_name=’UNDOTBS1′;
#select tablespace_name,round(sum(bytes)/1024/1024) as “SPACE IN MB” from dba_free_space where tablespace_name=’ARW_DATA’ group by tablespace_name order by 1;
col “Name” format a10
select a.tbl “Name”,a.tsz “Total Size”,b.fsz “Free Space”,
round((1-(b.fsz/a.tsz))*100) “Pct Used”,round((b.fsz/a.tsz)*100) “Pct Free” from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name = ‘DATA’ group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name = ‘DATA’ group by tablespace_name) b
Where a.tbl=b.tblsp;
col file_name for a60
select file_name,bytes/1024/1024/1024 from dba_data_files
where tablespace_name =’DATA’;
#echo “Temp Space”
select a.tablespace_name “TBL”, a.”Total SIze”, b.”USED”,
round((b.”USED”/a.”Total SIze”)*100) “%USED”,
round((1-(b.”USED”/a.”Total SIze”))*100) “%FREE” from
(select tablespace_name, sum(bytes)/1024/1024 “Total SIze” from dba_temp_files
where tablespace_name=’TEMP’ group by tablespace_name) a,
(select tablespace_name, (total_blocks*(select value from v$parameter where name
=’db_block_size’))/1024/1024 “USED” from v$sort_segment) b
where a.tablespace_name=b.tablespace_name;
SELECT SUM(BYTES_FREE)/1024/1024 as “SPACE IN MB TEMP” ,tablespace_name FROM V\$TEMP_SPACE_HEADER group by tablespace_name;
select sid,message,time_remaining/60 from v$session_longops where time_remaining>0;
!df -g /iu147/u05/oradata/ecsuat2/arch
Eof
sleep 15
clear
done
#!/bin/sh
for file in *.fmb
do
c
done
[applmgr@hostnamerh157 bin]$ cat a_patch.sh
export ORACLE_SID=`echo $TWO_TASK | tr [:lower:] [:upper:]`
export PATCH_NO=$1
adpatch logfile=${PATCH_NO}_${ORACLE_SID}.log patchtop=$PWD driver=u${PATCH_NO}.drv
**********Basic commands to check system statistics *************
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
Prtconf command will show how much Physical Memory
Swap command will provide us with the usage of swap space including the RAM.
Df command will indicate how much space is free in each mount point and also
provides information about swap space(s).
Top command wil provide the above information plus information about top CPU
consuming processes, CPU usage in the system, etc.
Vmstat will provide information about process run queue, memory usage,
paging and swapping and CPU % usage in the server.
Iostat provides IO usage by Disk, CPU % usage, etc. depending on the options
used.
Mpstat will provide CPU usage stats for a Solaris server with 1 or more CPUs.
So, the below OS commands also will give the same output related to this command outputs.
HP-Unix 11.0:
============
$ 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
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
Linux
======
$ dmesg | grep Memory
$ vmstat 5 100
$ /usr/bin/top
to set backspace
#############
stty erase {backspace}
this will set backspace.
###################
to change owner
###################
chown oracle:dba oracle
owner=oracle group=dba directory=oracle
##############################
to know CPU info
#################
psrinfo
uname -X
###################
to know Oracle Version(64 bit/32 bit):
#######################
go to $RACLE_HOME/bin..
then $file oracle or $file sqlplus–>
[gemsora13s:/gemsora13s/u01/app/oracle/product/9206/bin]
stgcpq:oracle>file sqlplus
sqlplus: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped
to create/modify a file’s date/timestamp:
##########################################
touch -m 01092200 dam
(create a file with timestamp jan 09 22:00 hrs
To copy a file with the same timestamp:
#######################################
cp -p dam dam1
To set LD_library_path:
#######################
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/gemsora15d/u01/app/oracle/product/10.1.0.4/dbee_1/lib: or
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:.
To change the os users settings:
################################
conn as root,
DEFAULT:oracle>cat /etc/passwd|grep oracle
oracle:x:1001:200:Oracle Client Software Owner:/export/home/oracle:/bin/ksh
+@users_dba_oracle:x:::::
vi /etc/passwd
then the shell from sh to ksh,chanhe ora_home,etc..
oracle:x:1001:200:Oracle Client Software Owner:/export/home/oracle:/bin/ksh
osuser:encripted pwd:uid:gid:mail send id:home dir:default shell
To find the contents of the Binary File
########################################
strings -a exp_file.dmp|grep “CREATE USER”
strings -a exp_file.dmp|grep “TABLE”
To change the server settings(database level)
##############################################
In every Oracle Home ($ORACLE_HOME/sqlplus/admin) there is a glogin.sql which takes care of the sql session settings for all dataabses in that particlar home.
To set these settings at the server level for all homes
go to cd $SQLPATH
[gemsora6:/export/home/oracle/aps7/sql]
edev:oracle>ls -ltr login*
-rwxr-xr-x 1 oracle dba 1016 Jan 17 04:06 login.sql_jan17
-rwxr-xr-x 1 oracle dba 1017 Jan 17 04:09 login.sql
Here u have a login.sql file which takes care of these settings.
Make whatever changes u want in this file and run the .profile again.
If you login to the database now, you will have new session settings.
Grep usage
################################################
grep dbaodc `crontab -l|grep -v ^#|awk ‘{print $6}’`
grep -i “keyword” *
list all the lines in the files where keyword in present
grep -il “odcebiz” */*/*
liosts all the files which have odcebiz keyword in them
expr
##################################
export a=5
devec2:oracle>b=`expr $a – 1`
[oradb5d:/gemsora5/u01/app/oracle/admin/local/scripts]
devec2:oracle>echo $b
4
[oradb5d:/gemsora5/u01/app/oracle/admin/local/scripts]
devec2:oracle>tail -`expr $a – 1` res_con.sql
END;
/
exit
###############################################
prstat -a —will give the processes running.
ps -ef — processes
ipcs – report inter-process communication facilities status (semaphorees)
ipcs -b == the last col gives the no of semaphores.
ipcrm – remove a message queue, semaphore set, or shared memory ID
sysresv — The chances in this sort of case is sometimes that the semaphores are not cleared we can check that using “sysresv” to ensure whether semaphores are cleared after database is brought down.
to set line numbers in vi editor
###########################
:set nu
###########################
to see all child processes
##########################
ptree -a
##########################
To replace gzip by mv
:%s/gzip/mv/g 2,1 All
##########################
to remove spaces at the end of lines
#####################################
put the file into vi..
:%s/ //g
to append something at the begining
####################################
to add spce at the begining..
:%s/^/ /g
:%s/^/dam/g
to append something at the end
###############################
:%s/$/ /g
:%s/$/dam/g
to know JDK version
#####################
olt2:oracle>which jdk
no jdk in /olcdbd/u01/app/oracle/product/9.2.0/bin /opt/bin /bin /usr/bin /usr/sbin /usr/ccs/bin /usr/ucb /etc /usr/local/bin /usr/openwin/bin .
[olcdbd:/home/evuri]
olt2:oracle>java -version
java version “1.2.2”
Solaris VM (build Solaris_JDK_1.2.2_10, native threads, sunwjit)
FNDCPASS apps/p1lgr1m 0 Y system/milwaukee USER SYSADMIN SYSADMIN
give the command in source server
cd /p04/oracle/product/
tar cf – * | remsh srvr13-other ‘(cd /p04/oracle/product/; tar xf – )’
————————————
ln -s $FND_TOP/bin/fndcpesr gems_swe_payment_concat
—————————————-
to know the version of patch
go to $APPL_TOP
find . -name “filename”
strings -a ./po/11.5.0/lib/rvsrq.o |grep ‘$Header’
—————————————-
product password like ar ,ap
custom passwords like Gems_
———————————
to know the sum of bytes
ls -ltr /preprod/u*/oradata/preprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
601.858
356
ls -ltr /glprod/u*/oradata/glprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
2000.87
1473
——————————————
dba file share location.
\\Uswaufs04medge\dba\GLPROD\GLPROD_DOCS
—————————————-
to remove some files from srvr date
find * -mtime +3 -exec \rm {} \;
————————————————-
cat nar|awk ‘{print “mv ” $2 ” /u92/oracle/itest2/bdump/.”}’>kum.sh
————————————————–
grep -i yes gltab
——————————————-
prodcopy—web server67
glprod—web servers
srvr21, srvr22, srvr37, srvr38, srvr39, srvr72, srvr73,
The web servers for PRODERP are SRVR65, SRVR63, SRVR64, SRVR75 and SRVR76
==================================================================
execute the below command
To list
/usr/openv/netbackup/bin/bplist -C srvr92.it.med.ge.com -B -b -k srvr92_manual_db -l -R -s 04/18/2004 ‘/lpadev9/u*/oradata/lpatest/*’
to restore
/usr/openv/netbackup/bin/bprestore -w -C srvr92.it.med.ge.com -B -K -s /04/18/2004 -c srvr92_manual_db -L /tmp/lpatest_restore.log ‘/lpadev9/u*/oradata/lpatest/*’ &
————————————————————
I need a relink of the following objects in $CZ_TOP/bin on srvr23 (devl3c1)
libczjni.so
libcz.so
libczlce.so
aderlink.sh force=y “cz file_name”
——————————————————————-
/mitshr/appsora/prod/scripts/scrape_database—-tablespace monitoring.
===================================================================
to know how many files copied from source to target.
o=0
while test true
do
n=`ls -lt /preprod/u*/oradata/preprod/*.*|wc -l`
s=`ls -lt /preprod/u*/oradata/preprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
d=`expr ${s} – ${o}`
echo “$n—>${s}Mb—>${d}Mb”
o=${s}
sleep 60
done
while test true
do
n=`ls -lt /preprod/u*/oradata/preprod/*.*|wc -l`
s=`ls -lt /preprod/u*/oradata/preprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
d=`expr ${s} – ${o}`
echo “$n—>${s}Mb—>${d}Mb”
o=${s}
sleep 60
done
———————————————————————————
to remove files on given date
ls -lt |grep “Oct 8″|awk ‘{print “rm -rf “$9}’>t1.sh
chmod 777 t1.sh
df -k .
./t1.sh
df -k .
ls -ltr|more
———————————————————
CPU utilization for the database wise:
/usr/ucb/ps -auxwww|grep prdscv | grep -i “local=no”|awk ‘{s += $3} END {print float(s)}’
—————————————————————————-
cat cr_database.sql|grep -v redo|grep -v tempx|grep “.dbf”|wc -l
—————————————————————–
to split the big file
split -b 1024m devl1c1_ora_4758.trc /u92/oracle/devl1c1/TRACE/devl1c1_ora_4758_
————————————————————-
to compile the forms
f60gen module=$AU_TOP/resource/CUSTOM.pll userid=apps/readmen0w output_file=$AU_TOP/resource/CUSTOM.plx module_type=LIBRARY compile_all=YES
———————————————————————–
Refer Metalink Note: 50538.1 for more details.
——————————————————–
to list the files from tape
/usr/openv/netbackup/bin/bplist -l -R -B -C srvr77.it.med.ge.com -k srvr77_manual_db -s 07/19/2004 -e 07/19/2004 -l ‘/cwtest/u*/oradata/cwdev/*’ |more
————————————————————————–
rsh srvr31c “ps -ef|grep reorg”;rsh srvr31b “ps -ef|grep reorg”;rsh srvr31a “ps -ef|grep reorg”
————————————————-
to know the duplicate files in same file.
cat n1|sort|uniq -c|grep -v ” 2″
——————————————————–
To see the cpu usage on servers.
while true
do
Servers=”srvr28a srvr28b srvr28c”
for node in `echo ${Servers}`
do
echo $node—Utilization
rsh ${node} sar 1 1
done
sleep 60
done
————————————————————————
/opt/openssh/bin/ssh srvr93 hostname
/opt/openssh/bin/ssh -l applmgr srvr13 hostname
/opt/openssh/bin/ssh -l root srvr107 hostname
/opt/openssh/bin/ssh -l oracle srvr93 hostname
————————————————
Enabling SSH/ssh
—————-
/opt/openssh/bin/ssh-keygen -t rsa
1. ssh into srvr81 as the user that needs to have access from srvr23 and srvr28 and run the following command:
/opt/openssh/bin/ssh-keygen -t rsa
Here is a sample ouput of the command. Make sure the key is written to $HOME/.ssh/id_rsa ( this is the default). For the passphrase just hit a return (twice) to create an empty passphrase.
——————————————-
To change the owner ship for the dir or files
chown root:other
chown -R applmgr:dba applmgr
————————————————————————–
To tar and untar the file or dir
remsh erpora1s ‘(cd /tools/merant/; tar cf – * )’| tar xf –
tar cf – 8.1.7.4 | remsh srvr26 ‘(cd /pjmtest/u010/app/oracle/product/; tar xf – )’
tar cf – 8.1.6 | remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar xf – )’ &
remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar cf – * )’| tar xf – &
to do tar
tar -cvf naresh.tar *
to do untar
tar -xvf /u92/applmgr/erpora1s/naresh.tar
—————————————————-
to create dir
df -k /mmdev2/u* |awk ‘{ print $6 }’|grep -v Mount|while read line
do
mkdir -p $line/oradata/mmdev2
chown -R oracle:dba $line/oradata
done
—————————————————————-
ln -s /orashr/oracle/sql/dblinks/cr_db_link_for_devl3.sql dblinks.sql
ln -s /orashr/oracle/sql/passwords/gl_devl3_passwords.sql passwords.sql
———————————————————
${APPATH}adcmctl.sh $CMD $DBNAME $app_pwd >>$dblog/ora_app_$CMD_$DBNAME.log
$FND_TOP/bin/CONCSUB apps/adm1n SYSADMIN ‘System Administrator’ SYSADMIN CONCURRENT FND ABORT
——————————————————————————————–
lines=`grep -n shutdown alert_prodpnfm.log|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_prodpnfm.log|head -3
done
———————————————————-
restore command
/usr/openv/netbackup/bin/bprestore -s 08/15/2004 -L /tmp/restore.log -f
/export/home/oracle/filelst1 -R /export/home/oracle/renamelst1
—————————————————————-
to know the ORA-600 entries
lines=`grep -n ORA-00600 alert_glprod3.log|grep 729|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_glprod3.log|head -3
done
—————————————–
to know the bcsync refresh
10/18/2009
To generate dbc file
jre oracle.apps.fnd.security.AdminAppServer apps/adm1n@gltest ADD FNDNAM=apps GWYUID=applsyspub/pub TWO_TASK=gltest1 SECURE_PATH=$FND_TOP/secure GUEST_USER_PWD=guest/guest APPS_JDBC_DRIVER_TYPE=THIN DB_HOST=srvr28a.med.ge.com DB_PORT=1521 FND_MAX_JDBC_CONNECTIONS=50 SERVER_ADDRESS=3.20.66.33 SERVER_DESCRIPTION=srvr28a.med.ge.com
To Remove the Files in Specific Date
ls -ltr |grep “May “|awk ‘{print “rm -rf “$9}’ > sha.sh;chmod 755 sha.sh;sh sha.sh &
================================================================================
to know the sum of bytes
ls -ltr /preprod/u*/oradata/preprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
167.517
ls -ltr /preprod/u*/oradata/preprod/*.dbf|wc -l
####### For GLPROD######
ls -ltr /glprod/u*/oradata/glprod/*.dbf|wc -l
ls -ltr /glprod/u*/oradata/glprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
==================================================================================================
—————————————-
to remove some files from srvr date
find * -mtime +3 -exec \rm {} \;
————————————————-
cat nar|awk ‘{print “mv ” $2 ” /u92/oracle/itest2/bdump/.”}’kum.sh
————————————————–
grep -i yes gltab
——————————————-
prodcopy—web server67
glprod—web servers
srvr21, srvr22, srvr37, srvr38, srvr39, srvr72, srvr73,
The web servers for PRODERP are SRVR65, SRVR63, SRVR64, SRVR75 and SRVR76
========================================================================================
to know how many files copied from source to target.
o=0
while test true
do
n=`ls -lt /preprod/u*/oradata/preprod/*.*|wc -l`
s=`ls -lt /preprod/u*/oradata/preprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
d=`expr ${s} – ${o}`
echo “$n—${s}Mb—${d}Mb”
o=${s}
sleep 60
done
while test true
do
n=`ls -lt /glprod/u*/oradata/glprod//*.*|wc -l`
s=`ls -lt /glprod//u*/oradata/glprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
d=`expr ${s} – ${o}`
echo “$n—${s}Mb—${d}Mb”
o=${s}
sleep 60
done
bounce the listeners
cd $dbbin/listener_stop.sh -l listener_name
cd $dbbin
ls -lis*
listener_start/stop.sh -l listener_name
CPU utilization for the database wise:
/usr/ucb/ps -auxwww|grep prdscv | grep -i “local=no”|awk ‘{s += $3} END {print float(s)}’
—————————————————————————-
cat cr_database.sql|grep -v redo|grep -v tempx|grep “.dbf”|wc -l
—————————————————————–
to split the big file
split -b 1024m devl1c1_ora_4758.trc /u92/oracle/devl1c1/TRACE/devl1c1_ora_4758_
restore command
/usr/openv/netbackup/bin/bprestore -s 08/15/2004 -L /tmp/restore.log -f
/export/home/oracle/filelst1 -R /export/home/oracle/renamelst1
—————————————————————-
to remove files on given date
ls -lt |grep “Apr 20″|awk ‘{print “rm -rf “$9}’t1.sh
chmod 777 t1.sh
df -k .
./t1.sh
df -k .
ls -ltr|more
find . -size 524288000c
———————————————————
CPU utilization for the database wise:
/usr/ucb/ps -auxwww|grep prdscv | grep -i “local=no”|awk ‘{s += $3} END {print float(s)}’
—————————————————————————-
cat cr_database.sql|grep -v redo|grep -v tempx|grep “.dbf”|wc -l
—————————————————————–
to split the big file
split -b 1024m devl1c1_ora_4758.trc /u92/oracle/devl1c1/TRACE/devl1c1_ora_4758_
————————————————————-
to compile the forms
f60gen module=$AU_TOP/resource/CSCCCCRC.pll userid=apps/obel1x1 output_file=$AU_TOP/forms/US/CSCCCCRC.fmb module_type=LIBRARY compile_all=YES
———————————————————————–
Refer Metalink Note: 50538.1 for more details.
——————————————————–
L ist the files from tape
/usr/openv/netbackup/bin/bplist -l -R -B -C srvr77.it.med.ge.com -k srvr77_manual_db -s 07/19/2004 -e 07/19/2004 -l ‘/cwtest/u*/oradata/cwdev/*’ |more
————————————————————————–
rsh srvr31c “ps -ef|grep reorg”;rsh srvr31b “ps -ef|grep reorg”;rsh srvr31a “ps -ef|grep reorg”
————————————————-
to know the duplicate files in same file.
cat n1|sort|uniq -c|grep -v ” 2″
——————————————————–
To see the cpu usage on servers.
while true
do
Servers=”srvr28a srvr28b srvr28c”1:05 PM
for node in `echo ${Servers}`
do
echo $node—Utilization
rsh ${node} sar 1 1
done
sleep 60
done
————————————————————————
/opt/openssh/bin/ssh srvr93 hostname
/opt/openssh/bin/ssh -l applmgr srvr13 hostname
/opt/openssh/bin/ssh -l root srvr107 hostname
/opt/openssh/bin/ssh -l oracle srvr93 hostname
————————————————
Enabling SSH/ssh
—————-
/opt/openssh/bin/ssh-keygen -t rsa
1. ssh into srvr81 as the user that needs to have access from srvr23 and srvr28 and run the following command:
/opt/openssh/bin/ssh-keygen -t rsa
Here is a sample ouput of the command. Make sure the key is written to $HOME/.ssh/id_rsa ( this is the default). For the passphrase just hit a return (twice) to create an empty passphrase.
——————————————-
To change the owner ship for the dir or files
chown root:other
chown -R applmgr:dba applmgr
————————————————————————–
To tar and untar the file or dir
remsh erpora1s ‘(cd /tools/merant/; tar cf – * )’| tar xf –
tar cf – 8.1.7.4 | remsh srvr26 ‘(cd /pjmtest/u010/app/oracle/product/; tar xf – )’
tar cf – 8.1.6 | remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar xf – )’ &
remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar cf – * )’| tar xf – &
to do tar
tar -cvf naresh.tar *
to do untar
tar -xvf /u92/applmgr/erpora1s/naresh.tar
—————————————————-
to create dir
df -k /mmdev2/u* |awk ‘{ print $6 }’|grep -v Mount|while read line
do
mkdir -p $line/oradata/mmdev2
chown -R oracle:dba $line/oradata
done
—————————————————————-
ln -s /orashr/oracle/sql/dblinks/cr_db_link_for_devl3.sql dblinks.sql
ln -s /orashr/oracle/sql/passwords/gl_devl3_passwords.sql passwords.sql
———————————————————
${APPATH}adcmctl.sh $CMD $DBNAME $app_pwd $dblog/ora_app_$CMD_$DBNAME.log
$FND_TOP/bin/CONCSUB apps/adm1n SYSADMIN ‘System Administrator’ SYSADMIN CONCURRENT FND ABORT
——————————————————————————————–
lines=`grep -n shutdown alert_prodpnfm.log|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_prodpnfm.log|head -3
done
———————————————————-
restore command
/usr/openv/netbackup/bin/bprestore -s 08/15/2004 -L /tmp/restore.log -f
/export/home/oracle/filelst1 -R /export/home/oracle/renamelst1
—————————————————————-
to know the ORA-600 entries
lines=`grep -n ORA-00600 alert_glprod3.log|grep 729|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_glprod3.log|head -3
done
—————————————–
to know the bcsync refresh
—————-
alter tablespace RINGX begin backup;
/usr/openv/netbackup/bin/bpbackup -w -c srvr108_manual_db -L /tmp/failed_tlsprd1.log -f /tmp/failed_tlsprd1
alter tablespace RINGX end backup;
****************************************************************************************
$AD_TOP/bin/adconfig.sh contextfile=/u02/app/applmgr/115/devl3c1/admin/devl3c1_srvr16.xml appspass=adm1n
select to_char(last_analyzed,’dd-mon-yy’),table_name,partition_name from
dba_tab_partitions where table_name=’GL_BALANCES’
group by to_char(last_analyzed,’dd-mon-yy’),partition_name,table_name ;
ln -s /u01/app/oracle/admin/mmdev3/pfile/initmmdev3.ora
select a.PROFILE_OPTION_NAME,a.SQL_VALIDATION,b.LEVEL_ID,b.LEVEL_VALUE,
b.LAST_UPDATE_LOGIN,b.PROFILE_OPTION_VALUE
from (select * from fnd_profile_options) a,
(select * from fnd_profile_option_values) b
where a.APPLICATION_ID=b.APPLICATION_ID
and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME like ‘%FORMS%’;
/orashr/oracle/scripts/shell/refresh_f5_setup.sh -d itestc1
/orashr/oracle/scripts/shell/update_wf.sh itestc1
SELECT * FROM SO_JOB_QUEUE
WHERE so_queue=’ORACLE’
AND so_status_name < ‘HOLD’
ORDER BY so_start_date;
SELECT SUM(PINS) “EXECUTIONS”,
SUM(RELOADS) “CACHE MISSES WHILE EXECUTING”
FROM V$LIBRARYCACHE;
appsora_ctl.sh -d prodcopy -s start -a
block_id file_id
printf “%d\n” 0x2f400b8a
select dbms_utility.data_block_address_file(792726410),
dbms_utility.data_block_address_block(792726410)
from dual;
select segment_name from dba_extents where file_id=189 and block_id=2954;
*******************************************************************
sar command
rac_swait.sql
rac_locks.sql
rac_libcachelock.sql
————————-
select ‘ ‘||username|| ‘ alter system kill session ”’||sid||’,’||serial#||”’;’
from v$session
where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and status=’ACTIVE’;
————————————
—————————
v$nls_parameters
stats$snapshot
select (SNAP_ID) from stats$snapshot;
alter database backup controlfile to ‘/${ORACLE_SID}/u010/oradata/${ORACLE_SID}_ctl.ctl’;
create table fnd_concurrent_queues_bkp as select *from fnd_concurrent_queues;
alter tablespace users default storage(pctincrease 0);
select next_extent,initial_extent,extents,max_extents,tablespace_name from dba_segments
where owner=’WSH’ and segment_name=’MIT_WSH_TRIP_STOPS’ and segment_type=’INDEX’;
alter index WSH.MIT_WSH_TRIP_STOPS storage(maxextents 4000);
alter table PO.CHV_SCHEDULE_ITEMS storage(maxextents 1000);
alter index GL.GL_POSTING_INTERIM_N1 storage(maxextents 500);
alter system set undo_retention=10800 ;
SELECT ‘ALTER INDEX PWMARDEV.’||INDEX_NAME||’ REBUILD TABLESPACE INDX01;’FROM DBA_INDEXES
WHERE OWNER=’PWMARDEV’ AND TABLESPACE_NAME=’DATA01′;
col USERNAME for a12
col OSUSER for a12
select s.sid, s.serial#, s.username, s.status, p.spid , s.osuser, s.terminal
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = 938194;
====================================================================
document 215187.1
SQL TRACE
1) create the tablespace
create tablespace SQLT datafile ‘/gltest/u042/oradata/gltest/sqlt01.dbf’
size 100M
extent management local uniform size 1m;
2) go to the below dir
cd /u92/oracle/SQLT
# sqlplus apps/run the below sql
SQL START SQLTCREATE.SQL;
==============================================================================
select APPLICATIONS_SYSTEM_NAME from applsys.FND_PRODUCT_GROUPS;
Update FND_PRODUCT_GROUPS
Set APPLICATIONS_SYSTEM_NAME=’DEV1ERPC’;
———————————————————-
To know the patch applied or not in database
select bug_number from ad_bugs where creation_date to_date(’31-Jul-12′,’dd-mon-yy’);
————————————————
To compile invalid objects
select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile ;’
from dba_objects
where owner in (‘APPS’,’APPS_MRC’)
and status=’INVALID’ ;
when locked objects occurred
select
lpad(ses.sid,4,’ ‘)||’ ‘||
lpad(proc.spid,6,’ ‘)||’ ‘||
— rpad(lck.OS_USER_NAME,8,’ ‘)||’ ‘||
— decode(lck.LOCKED_MODE,3,’Share ‘,5,’Exclusive’,’Unknown ‘)||’ ‘||
lpad(nvl(ses.client_info,0),3,’0′)||’ ‘||
rpad(obj.object_name,30,’ ‘)||’ ‘||
— rpad(ses.PROGRAM,30,’ ‘)||’ ‘||
–rpad(machine,8,’ ‘)||’ ‘||
Substr(ses.status,1,1)||’ ‘||
substr(wait.state,1,7)||’ ‘||
decode(ses.module,NULL,ses.program,ses.action||’*’||ses.module)
from V$LOCKED_OBJECT lck,
all_objects obj,
v$session ses,
v$session_wait wait,
v$process Proc
where lck.object_id = obj.object_id
and ses.sid = lck.session_id
and ses.sid = wait.sid
and ses.paddr = proc.addr
and obj.object_name = ‘FND_CONC_REQ_STAT’
order by obj.object_name,ses.client_info;
select sid,serial#,status,sql_hash_value from v$session where sid=221;
alter system kill session ‘221, ‘;
——————————————————————————————-
we can know any body is accessing the table
select a.sid,a.serial#,c.object_name
from all_objects c , v$lock b , v$session a
where c.object_name in
(‘GEMS_FUT_TP_WA_TB’)
and c.object_type = ‘TABLE’
and c.object_id = b.id1
and b.sid = a.sid;
———————————————————————————————-
*********RBS*******************
col segment_name format a8
col tablespace_name format a13
col usn format 99s
col status format a8
col “Max” format 9999999999
col “Exts” format 99999
col xacts format 99999
col curext format 99999
col “Size” format 99999
col “Opt” format 99999
select a.segment_name,a.tablespace_name,b.usn,b.extents “Exts”,
a.max_extents “Max”,b.xacts,b.status,round(b.rssize/1024/1024) “Size” ,
b.curext,b.optsize/1024/1024 “Opt”
from dba_rollback_segs a, v$rollstat b
where a.segment_id=b.usn and a.tablespace_name like ‘RBS%’ order by b.curext;
———————————————————————————————-
alter rollback segment R05 shrink;
———————————————————————————————-
To see lob columns are there or not
select data_type from dba_tab_columns where data_type like ‘LO%’ and
table_name in
—————————————————————–
To create histograms
analyze table mrp.mrp_sr_assignments estimate statistics for columns ASSIGNMENT_TYPE size 10;
Analyze table ONT.oe_order_lines_all compute statistics for columns flow_status_code size 14;
=================================================================================
to know which session is using
col USERNAME for a12
col STATUS for a8
col OSUSER for a10
col TERMINAL for a10
col SPID for a10
select s.sid, s.serial#, s.username, s.status, p.spid , s.osuser, s.terminal,s. LOGON_TIME, s.module,s.hashvalue
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = 2547930;
25271;
/tmp/temp_check_gltest1.sql
===============================================================================
tempx usage
select a.tablespace_name “TBL”, a.”Total SIze”, b.”USED”,
round((b.”USED”/a.”Total SIze”)*100) “%USED”,
round((1-(b.”USED”/a.”Total SIze”))*100) “%FREE” from
(select tablespace_name, sum(bytes)/1024/1024 “Total SIze” from dba_temp_files
where tablespace_name=’TEMP’ group by tablespace_name) a,
(select tablespace_name, (total_blocks*(select value from v$parameter where name
=’db_block_size’))/1024/1024 “USED” from v$sort_segment) b
where a.tablespace_name=b.tablespace_name;
select TABLESPACE_NAME,BYTES_USED/1024/1024,BLOCKS_FREE/1024/1024 from v$temp_space_Header;
select TABLESPACE_NAME,file_name, sum(bytes)/1024/1024/1024 from dba_temp_files
group by tablespace_name,file_name;
select tablespace_name from dba_tablespaces
where tablespace_name like ‘TEM%’;
*********Sort Space Usage by Session
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
——————————————————————–
to put the tablespaces in backup mode
select distinct ‘alter tablespace ‘||tablespace_name||’ begin backup ;’ from dba_data_files
select FILE#,STATUS from v$backup where status=’ACTIVE’;
select distinct ‘alter tablespace ‘||tablespace_name||’ end backup ;’
from dba_data_files
where file_id in
(select FILE# from v$backup where status=’ACTIVE’);
alter system switch logfile;
————————————————————
select distinct status from v$backup;
select inst_id, machine, count(*)
from gv$session
where program like ‘f60runm%’
group by inst_id, machine;
alter system flush shared_pool;
—————————————-
break on sid
select a.sid,b.SQL_TEXT
from v$session a, v$sqltext b
where a.SQL_ADDRESS=b.address
and a.status=’ACTIVE’ and a.username is not null
order by sid,piece;
———————————————-
select num_rows from dba_tables
where table_name=’GEMS_TRAN_PRICE_ERR_TB’;
select last_analyzed,owner from dba_tables
where table_name in
(‘GEMS_FUT_TP_WA_TB’,’GEMS_TRAN_PRICE_TB’,’GEMS_TRAN_PRICE_ERR_TB’);
————————————————————-
select ‘ ‘||username|| ‘ alter system kill session ”’||sid||’,’||serial#||”’;’
from v$session
where username not in (‘SYSTEM’,’SYS’,’APPS’,’OPS$ORACLE’,’APPLSYS’) and status=’ACTIVE’;
—————————————————————–
To know spid of sessions marked as killed:———-
SELECT spid
FROM v$process
WHERE NOT EXISTS
( SELECT 1 FROM v$session WHERE paddr = addr);
tempx usage.sql
set pagesize 1000
set linesize 120
c
col username format a8 truncated
col machine format a12 truncated
col program format a30 truncated
col since format a18
col status format a2 truncated
col sid format 9999
break on report
compute sum of mbytes on report
select s.username, s.sid, s.machine, s.program,
to_char(s.logon_time,’DD-MON-RR HH24:MI:SS’) since,
s.status, round((su.blocks*8192)/(1024*1024),1) Mbytes
from v$session s,v$sort_usage su
where s.saddr=su.session_addr
order by Mbytes asc
/
SELECT spid
FROM v$process
WHERE NOT EXISTS
( SELECT 1 FROM v$session WHERE paddr = addr);
select table_name
from dict
where table_name like ‘%ROLL%’;
rbs—–
SELECT r.name “RB NAME “, p.pid “ORACLE PID”,
p.spid “SYSTEM PID “, NVL (p.username, ‘NO TRANSACTION’), p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = ‘TX’
AND l.lmode(+) = 6
ORDER BY r.name;
col name for a8
col sid for 99999
col serial# for 999999
col username for a32
col sql_text for a60
col addr for a18
set lines 180
ttitle ROLLBACK_SEGMENT_SQL
select a.name, b.xacts,c.sid,c.serial#,c.username,d.sql_text, e.addr
from v$rollname a, v$rollstat b, v$session c, v$sqlarea d, v$transaction e
where a.usn = b.usn and
b.usn=e.xidusn
and c.taddr=e.addr
and c.sql_address = d.address and
c.sql_hash_value = d.hash_value
order by a.name,c.sid
/
analyze table mrp.MRP_FORECAST_DATES validate structure cascade;
analyze table mrp.MRP_GROSS_REQUIREMENTS validate structure cascade;
If you see the ORA-600 (510) error again, please run the following query:
select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and w.event = ‘latch free’
and w.p2 = l.latch#
and l.name like ‘library%’;
column ssid format 9999 heading SID
column opname format a15 Heading Operation
column target format a14 Heading Target
column es format 999.9 Heading “Time|Ran”
column tr format 999.90 Heading “Time|Left”
column pct format 990 Heading “PCT”
column RATE Heading “I/O |Rate/m” just right
select
sid ssid,
substr(OPNAME,1,15) opname,
target,
trunc((sofar/totalwork)*100) pct,
to_char(60*sofar*8192/(24*60*(last_update_time – start_time))/1024/1024/60, ‘9999.0’)||’M’ Rate,
elapsed_seconds/60 es,
time_remaining/60 tr
from v$session_longops
where time_remaining 0 and sid=659
order by start_time
/
col “logon” for a16
col username for a12
col spid for 99999
col osuser for a10
col machine for a12
col sid for 999
col program for a30
select a.username,a.sid,a.process ,a.serial#,b.spid,to_char(a.logon_time,’dd:mm:yyyy hh24:mi’)
“logon”, a.status,a.osuser,a.machine,a.program from Gv$session a, Gv$process b
where a.paddr=b.addr and a.username is not null and a.osuser like ‘%apwx%’;
AND A.USERNAME NOT LIKE ‘%BPA%’ AND A.USERNAME NOT LIKE ‘GEMS’
AND A.USERNAME NOT LIKE ‘%MOM%’;
break on sid
select a.sid,b.SQL_TEXT
from gv$session a, gv$sqltext b
where a.SQL_ADDRESS=b.address
and a.status=’ACTIVE’ and a.username is not null and a.sid=154
order by sid,piece;
9955
FROM apps.ra_customers cust ,
ar.hz_party_sites s,
ar.hz_cust_acct_sites_all cs,
ar.hz_locations addr,
ar.hz_cust_accounts ca ,
ar.hz_cust_site_uses_all u
set echo off
set feed off
set head off
set feed off
col REFERENCED_NAME for a35
SELECT REFERENCED_NAME,REFERENCED_TYPE FROM DBA_DEPENDENCIES
WHERE NAME=’GEMS_AR_GP_GNARMAST’ and referenced_type=’TABLE’;
SELECT r.name “RB NAME “, p.pid “ORACLE PID”,
p.spid “SYSTEM PID “, NVL (p.username, ‘NO TRANSACTION’), p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = ‘TX’
AND l.lmode(+) = 6
ORDER BY r.name;
—————————————-
TO KNOW THE VERSION OF A DB
select trunc( (&_O_RELEASE/100000000)) || ‘.’ ||
trunc( mod((&_O_RELEASE/1000000),100)) || ‘.’ ||
trunc( mod((&_O_RELEASE/10000),100)) || ‘.’ ||
trunc( mod((&_O_RELEASE/100),100))
from dual;
select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile ;’
from dba_objects
where owner in (‘APPS’,’APPS_MRC’)
and status=’INVALID’ ;
=========================================================================================
title ‘Rollback Segment Users’
set linesize 80
col usn format 99 heading ‘ID’
col name format a15 heading ‘SEGMENT’
col schemaname format a12 heading ‘USERNAME’
col pid format 999 heading ‘PID’
col sid format 999 heading ‘SID’
col osuser format a10 heading ‘OSUSER’
col spid format 99999 heading ‘OSPID’
select r.usn, r.name, s.schemaname, s.sid, p.pid, s.osuser, p.spid,USED_UBLK
from v$transaction t, v$session s, v$process p, v$rollname r
where t.xidusn = r.usn
and t.addr = s.taddr
and s.paddr = p.addr
and s.sid=209
order by 1;
/
===========================================================================================
select s.sid, s.username,s.program, p.spid , s.osuser from v$session s, v$process p
where s.paddr = p.addr and s.program like ‘f%run%’;
select SQL_STATEMENT_NAME,ACTIVE_FLAG from bom.Bom_Delete_Sql_Statements
where SQL_STATEMENT_NAME in
(‘ITM_CON51′,’BIL_CON18′,’CMP_CON5′,’ITM_CON44′,’ITM_CON45’);
===========================================================================================
select FILE#,STATUS from v$backup where status=’ACTIVE’;
select distinct ‘alter tablespace ‘||tablespace_name||’ end backup ;’
from dba_data_files
where file_id in
(select FILE# from v$backup where status=’ACTIVE’);
INVALIDS COUNT
select owner, count(*),OBJECT_NAME,OBJECT_TYPE from dba_objects where status=’INVALID’
group by owner,OBJECT_NAME,OBJECT_TYPE ;
select owner, count(*) from dba_objects where status=’INVALID’
and owner in (‘APPS’,’APPSVIEW’,’APPS_MRC’,’APPLSYS’,’MOMI41′,’BPA’) group by owner
TABLESPACE
col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name =’APPS_TS_TX_DATA’;
alter tablespace GPPSD1 add datafile ‘/itestc1/u053/oradata/itestc1/statd09.dbf’
size 2048064k;
alter database datafile ‘/gltest/u053/oradata/gltest/gppsd103.dbf’ resize 2048064k;
ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE ‘/j2_gcasq/d001/a_txn_data07.dbf’ SIZE 6G;
!ls -ltr /itestc1/u*/oradata/itestc1/statd09.dbf
!ls -ltr /gltest/u*/oradata/gltest/podm108.dbf
col file_name for a60
select file_name,bytes/1024/1024 from dba_temp_files
where tablespace_name=’TEMPX’;
select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name=’RBS1′;
select * from dba_tablespaces
where tablespace_name=’EC’;
set lines 200
col “Name” format a20
select a.tbl “Name”,a.tsz “Total Size”,b.fsz “Free Space”,
round((1-(b.fsz/a.tsz))*100) “Pct Used”,round((b.fsz/a.tsz)*100) “Pct Free” from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name = ‘APPS_TS_TX_IDX’ group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name = ‘APPS_TS_TX_IDX’ group by tablespace_name) b
Where a.tbl=b.tblsp;
col file_name for a60
select file_name,bytes/1024/1024/1024 from dba_data_files
where tablespace_name =’APPS_TS_TX_IDX’;
select owner,tablespace_name,segment_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name=’XXSLS_GBL_ORDER_HISTORY_MV’ GROUP BY owner,segment_name, tablespace_name;
**************List All the Tablespaces free space *******************
select tsu.tablespace_name, ceil(tsu.used_mb) “size MB”
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) “free MB”
, decode(100 – ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 – ceil(tsf.free_mb/tsu.used_mb*100)) “% used”
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ‘ **TEMP**’
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
select a.tablespace_name “TBL”, a.”Total SIze”, b.”USED”,
round((b.”USED”/a.”Total SIze”)*100) “%USED”,
round((1-(b.”USED”/a.”Total SIze”))*100) “%FREE” from
(select tablespace_name, sum(bytes)/1024/1024 “Total SIze” from dba_temp_files
where tablespace_name=’TEMP1′ group by tablespace_name) a,
(select tablespace_name, (total_blocks*(select value from v$parameter where name
=’db_block_size’))/1024/1024 “USED” from v$sort_segment) b
where a.tablespace_name=b.tablespace_name;
col file_name format a60;
col tablespace_name format a15;
select file_name,sum(bytes)/1024/1024 sum from
dba_data_files where tablespace_name=’PRECISE_I3_PWOR_IND’ group by file_name
order by file_name;
GATHER STATS
execute DBMS_STATS.GATHER_TABLE_STATS(ownname=’${tab_owner}’,tabname=’${tab_name}’,estimate_percent=${gat_per},cascade=true);
exec fnd_stats.gather_table_stats(‘MRP’,’MRP_RELIEF_INTERFACE’,20,1,”,’NOBACKUP’);
execute FND_STATS.GATHER_TABLE_STATS(ownname =’PO’,tabname = ‘PO_ACCRUAL_RECONCILE_TEMP_ALL’,percent =’20’,degree =’0′);
execute FND_STATS.GATHER_INDEX_STATS(ownname=’ENG’,indname=’ENG_REVISED_ITEMS_N3′,percent=20,BACKUP_FLAG=’NOBACKUP’);
execute DBMS_STATS.GATHER_TABLE_STATS(ownname=’${tab_owner}’,tabname=’${tab_name}’,estimate_percent=${gat_per},cascade=true);
execute DBMS_STATS.GATHER_TABLE_STATS(ownname=’MM’,tabname=’GEMS_ITEM_TB’,partname=’CST_PMAX’,estimate_percent=20,cascade=true);
execute DBMS_STATS.GATHER_INDEX_STATS(ownname=’MM’,indname=’ITEM_PK’,degree=20);
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.gather_schema_stats(‘DP’, estimate_percent => 10);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);
select ‘exec dbms_stats.unlock_table_stats (‘ ||”’SYSTEM”’||’,”’||table_name||”” || ‘);’ from dba_tables where owner=’SYSTEM’
select ‘exec dbms_stats.delete_table_stats(OWNNAME=>’ ||”’SYSTEM”’||’,TABNAME=>”’||table_name||”” || ‘);’ from dba_tables where owner=’SYSTEM’;
select ‘exec dbms_stats.gather_table_stats(OWNNAME=>’ ||”’SYSTEM”’||’,TABNAME=>”’||table_name||”” ||’,’||’ESTIMATE_PERCENT=>100,DEGREE=>4,CASCADE=>TRUE ‘||’);’ from dba_tables where owner=’SYSTEM’;
ROLLBACK SEGMENTS
col segment_name format a8
col tablespace_name format a13
col usn format 99
col status format a8
col “Max” format 9999999999
col “Exts” format 99999
col xacts format 99999
col curext format 99999
col “Size” format 99999
col “Opt” format 99999
select a.segment_name,a.tablespace_name,b.usn,b.extents “Exts”,
a.max_extents “Max”,b.xacts,b.status,round(b.rssize/1024/1024) “Size” ,
b.curext,b.optsize/1024/1024 “Opt”
from dba_rollback_segs a, v$rollstat b
where a.segment_id=b.usn and a.tablespace_name=’RBS1′ order by b.curext;
alter rollback segment R03 shrink
Database link
col db_link for a26
col owner for a10
col host for a20
col created for a10
col username for a10
select * from dba_db_links;
drop public database link GL_APPS.MED.GE.COM;
create public database link GL_PREPRO.MED.GE.COM
connect to PREPRO identified by aur0ra
using ‘itest.med.ge.com’;
select count(*) from tab@GL_PREPRO.MED.GE.COM;
select name from v$database@GL_APPS.MED.GE.COM;
MISSING FILES
select * from v$datafile where name = ‘%MISS%’;
SELECT count(file#) FROM v$datafile_header WHERE recover =’YES’ OR error is not null;
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit
INDEX REBUILD
alter index owner.index_name rebuild;
/u92/oracle/outage/prod/itestc1/reorgs1/gl_bal_global_ind.sql
Database invalid sessions
set pagesize 100
col sid for a30
col machine for a25
col osuser for a10
col program for a20
select sid, machine,osuser,program, to_char(logon_time,’ddth hh24 mi’ ) “login_time” from v$session
where username =’APPS’
and status = ‘f’
order by to_char(logon_time,’ddth hh24 mi’);
patch application particulars
col BUG_ID for a10
col APPLICATION_SHORT_NAME for a10
col BUG_NUMBER for a10
col CREATION_DATE for a10
col ARU_RELEASE_NAME for a10
col SUCCESS_FLAG for a10
col CREATED_BY for a10
col LAST_UPDATE_DATE for a10
col LAST_UPDATED_BY for a10
col BUG_STATUS for a10
select * from ad_bugs where to_char(CREATION_DATE,’mm_dd_yy’)=’05_10_12′ ;
select BUG_ID||’ ‘||APPLICATION_SHORT_NAME||’ ‘||BUG_NUMBER||’ ‘||to_char(CREATION_DATE,’mm-dd-yy:hh24:mi:ss’)||’ ‘||BUG_STATUS||’ ‘||SUCCESS_FLAG from ad_bugs where to_char(CREATION_DATE,’mm_dd_yy’)=’07_16_05′ ;
Admin mount point filled
In future if you get any alert reg admin mount point on any apps related server , pls do following steps to
1) Find the trace files which are having more than 300m
Go to admin mount point and execute following command
find . -size +300000000c —
2) if you get any output(files) from step one check the trace files whether ther are generating from front end or from backend – to chk the same follow as below
If the file naming convention is like devl3c1_ora_4273_501022571.trc ( here u will see an extra field with usre login name(pink-bold one) then this is generating from frond end.
NOTE: if its not from front end then the trace file format is devl3c1_ora_9081.trc.
Using above file u can contact anyone from apps admin team and chk with them whether we can remove these trace files or not or aks them to do the needfull then they will take care of that.
Greping to a file
ls -ltr|grep “Apr 19″|awk ‘{print “rm -rf “$9}’RM1.SH
finding process which are running
For knowing the sessions presently running
select SID,SERIAL#,PADDR,USERNAME,STATUS,SCHEMANAME,OSUSER,PROGRAM,TYPE,SQL_HASH_VALUE,MODULE,ACTION,LOGON_TIME from v$session where SCHEMANAME=’APPS’;
To know what sql statement it is runnig
select b.SQL_TEXT from v$sqlarea b, v$session a
where b.HASH_VALUE=a.SQL_HASH_VALUE
and a.sid=’138′;
To know whether it is running or struck
select * from v$sess_io where sid=’138′;
select trunc(last_analyzed),owner, count(*) from dba_tables
where owner=’PERFSTAT’
group by trunc(last_analyzed),owner;
select trunc(last_analyzed),count(*) from dba_tables where trunc(last_analyzed) in(’12-JUN-05′,’13-JUN-05′,’14-JUN-05′) group by trunc(last_analyzed);
select trunc(last_analyzed),count(*) from dba_tables group by trunc(last_analyzed);
select table_name,trunc(last_analyzed) from dba_tables where trunc(last_analyzed) in(’21-JUl-05′);
select index_name,trunc(last_analyzed) from dba_indexes where trunc(last_analyzed) in(’21-JUl-05′);
select patition_name,table_name,trunc(last_analyzed) from dba_tab_partitions where trunc(last_analyzed) in(’21-JUl-05′);
ROLE CREATION
select * from ROLE_SYS_PRIVS where role in (‘GE_PST’,’GE_DEV’,’GE_VIEW_ONLY’);
create role GE_VIEW_ONLY
grant CREATE SESSION,SELECT ANY TABLE to GE_VIEW_ONLY;
ls -lrt | sed -n ‘1,100p’|awk ‘{print “rm -rf ” $9 }’ — first 20
ls -lrt|egrep “Nov 18″| awk ‘{print “rm -rf ” $9 }’ > oct.sh;chmod 755 oct.sh; sh oct.sh &
ls -lrt|egrep “Nov 18″| awk ‘{print “gzip ” $9 ” & “}’
ps -ef|grep oracle$ORACLE_SID|grep LOCAL=NO|awk ‘{print “kill -9 ” $2 }’ > kill.sh;chmod 755 kill.sh;sh kill.sh &
find . -mtime +3 -type f -exec rm {} \;
ls -lrt |grep “Jun 30” |awk ‘{print “gzip ” $9 ” & ” }’ > Jun30.sh;chmod 755 Jun30.sh;sh Jun30.sh
FNDCPASS apps/appsdev 0 Y system/sygc321 USER SYSADMIN sygc321
grep password $IAS_ORACLE_HOME/Apache/modplsql/cfg/*app|head -1|awk ‘{print $3}’
grep password $IAS_CONFIG_HOME/Apache/modplsql/cfg/*app|head -1|awk ‘{print $3}’
select node_name,SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN from fnd_nodes;
select text from dba_source where name=’XXSCP_GBL_SO_OPTIMISE_PKG’; – Package code
sqlplus -s apps/appsdev @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
sqlplus -s apps/appsdev @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
admrgpch -s /rh157/u04/Unity_Patches/cmppat/sour -d /rh157/u04/Unity_Patches/cmppat/desti -merge_name CMPPAT
select fnd_profile.value(‘APPS_MAINTENANCE_MODE’) from dual;
adpatch options=hotpatch,nocompiledb,nocompilejsp
adpatch options=nocompiledb,nocompilejsp,nomaintainmrc,noautoconfig defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt patchtop=$APPL_TOP/patches/7184125 driver=u7184125.drv logfile=adpatch_7184125.log workers=32
select bug_number from ad_bugs where bug_number in (‘6328412’, ‘7340487’, ‘7347275’);
select bug_number from ad_bugs where bug_number = &patchno;
How to know that if the patch is applied successfully
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID,
A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS
from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRIVERS C, AD_APPLIED_PATCHES D
where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID
and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID
and A.PATCH_DRIVER_ID
in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = ‘10059638’)) ORDER BY 3;
PATCH_NAME APPLICATIONS_SYSTEM_NAME NAME DRIVER_FILE_NAME PATCH_DRIVER_ID PATCH_RUN_ID SESSION_ID PATCH_TOP START_DATE END_DATE S FAILURE_COMMENTS
———— ————————- ———- —————— ————— ———— ———- ———————————————————————————
8265428 utyeb03 hostnamerh212 u8265428.drv 100500 107491 164030 /dbautyshare/Unity_Patches/8265428 18-JUN-12 18-JUN-12 Y
Merged patches are applied for which language
select a.PATCH_DRIVER_ID,DRIVER_FILE_NAME,c.bug_id,d.language
from ad_patch_drivers a,AD_COMPRISING_PATCHES b, ad_bugs
c,AD_PATCH_DRIVER_LANGS d
where c.bug_number = ‘&no’
and c.bug_id = b.bug_id
and a.PATCH_DRIVER_ID = b.patch_driver_id
and a.patch_driver_id = d.patch_driver_id;
pathces applies in last no of days
select substr(bug_number,1,7) patch,
B.LAST_UPDATE_DATE,
substr(aru_release_name,0,10)
from ad_bugs B,
ad_applied_patches AP
where substr(AP.PATCH_NAME,1,7) = substr(B.bug_number,1,7)
and B.creation_date > sysdate-&days
order by bug_number
/
select ‘alter system kill session ”’||sid||’,’||serial#||”’;’ from v$session where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and status!=’ACTIVE’;
admrgpch -s /home/applmgr/patch10g/source -d /home/applmgr/patch10g/dest -merge_name ramesh
Oracle Application Tier consists of following services, which you can run on one or more machines
– Root Service (OPMN)
– WebEntry Point Services (OHS)
– Web Application Services(OC4J- oacore, oafm, forms)
– Batch Processing Services(CM, apps listener, fullfillment)
– Other Services (MWA, Metric client/server)
Jserver(jre)
Forms Server(f60srv)
Metric Server(d2ls)
Metric Client(d2lc)
Report Server(rwm60)
Concurrent Server(FNDLIBR)
The Oracle AWR report is the evolution of the STATSPACK report in Oracle 10g, and is invoked as follows:
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql
$ORACLE_HOME/rdbms/admin/awrinput.sql
Workflow Notification
update wf_resources set text=’*’ where name=’WF_ADMIN_ROLE’;
SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;
select B.status, B.RULE_FUNCTION
from wf_events A, wf_event_subscriptions B
Where A.GUID=B.EVENT_FILTER_GUID and
A.name=’&event’ and
B.RULE_FUNCTION = ‘&subscription’;
SET VERIFY OFF;
SET SERVEROUTPUT ON SIZE 1000000;
declare
l_readyc NUMBER;
l_waitc NUMBER;
l_processedc NUMBER;
l_expiredc NUMBER;
l_undeliverablec NUMBER;
l_errorc NUMBER;
begin
WF_QUEUE.getCntMsgSt
(p_agent => ‘WF_DEFERRED’,
p_ready => l_readyc,
p_wait => l_waitc,
p_processed => l_processedc,
p_expired => l_expiredc,
p_undeliverable => l_undeliverablec,
p_error => l_errorc);
dbms_output.put_line(‘WF_DEFERRED messages ready:’ || l_readyc || ‘, waiting:’ || l_waitc || ‘, expired:’|| l_expiredc || ‘, undeliverable:’ || l_undeliverablec || ‘, processed:’ || l_processedc);
end;
/
set verify on
Invalids
select OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME,STATUS from all_objects where object_name like ‘OE_VERIFY_PAYMENT_PUB%’ order by LAST_DDL_TIME asc;
select count(*) from dba_objects where status=’INVALID’;
exec sys.utl_recomp.recomp_parallel(8);
set lines 200
set pages 400
col OBJECT_NAME for a34
select owner,object_name,object_type,status from dba_objects where status=’INVALID’;
select owner,object_name,object_type,status from dba_objects where object_name=’XXCRM_GBL_PCN_ARC_UTL_PKG’;
select object_name, object_type from all_objects where object_name = ‘DBMS_SUPPORT’ and object_type like ‘PACKAGE%’ ;
*********************************************************
col “DB User” format a10
col “OS User” format a14
col “Logon time” format a25
set verify off pagesize 100
select
a.username “DB User”,
a.osuser “OS User”,
a.sid “SID”,a.serial#,
to_char(a.LOGON_TIME,’DD-MON-YYYY HH24:MI:SS’) “Logon time”,
a.status “Status”
–a.sid,
–a.serial#
from v$session a,
v$process b
where a.paddr = b.addr
and a.username is not null
–and round(abs(((sysdate)-(logon_time))*24)) 50
and status=’ACTIVE’
order by 4 desc;
z03845
47arvada
select value from v$parameter where NAME=’utl_file_dir’;
exec FND_FILE.PUT_LINE(FND_FILE.LOG, ‘THIS IS A TEST’);
To change Apps Password using FNDCPASS
FNDCPASS apps/
— adding TEMP FILE
alter tablespace TEMP add tempfile ‘/ofp/oratemp/ebis/temp07.dbf’ size 1800m;
patchset level
SELECT B.APPLICATION_NAME, A.APPLICATION_ID,
PATCH_LEVEL FROM FND_PRODUCT_INSTALLATIONS A, FND_APPLICATION_TL B
WHERE A.APPLICATION_ID= B.APPLICATION_ID;
— firing “awk” command
ls -l | grep ‘Apr’ | awk ‘{print $9}’ | xargs -i rm {} &
ls -l | grep ‘Dec 17’ | awk ‘{print $9}’ | xargs -i rm {}
ls -l |awk ‘{print “ln -sf “$11″ ” $9}’
For log files newer than 5 days
find /opt/app/logs/ -name *.log -mtime -5 -exec ls -tl {} \;
find /opt/app/logs/ -name *.log -mtime -5 -exec rm -f {} \;
— to generate forms (run this from “applmgr”)
f60gen module=/oraapps/testappl/au/11.5.0/forms/US/RCVTXERT.fmb userid=APPS/
— patch log file
$APPL_TOP/admin/
— listing directory
ls -F | grep /
ls -l | sort +0
tkprof tracefile outputfile explain=system/passwd sys=no
Saeed : tkprof Saeed : if you just type tkprof it will show you the usage page.
Saeed : [oraprd@IRODBP01] $ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAIN.
print=integer List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of d
f60gen…
strings -a binary
it will show u the SQL it is running
To Check how the roll back segment is being utilized
select b.sid,a.message,opname,start_time pct_complete,
to_char(start_time+(sysdate-start_time) /(sofar/totalwork),’dd-mon-yy:hh:mi:ss’) Est_completion_time
from v$session_longops a, v$session b where a.serial#=b.serial#
and (sofar/totalwork)*100 < 100
Generating jar files
adjava -mx512m -nojit oracle.apps.ad.jri.adjmx @/applprod/prodappl/admin/DEV/out/genjars.cmd
— To bring the tape offline
mt -f /dev/rmt/0 offline
lock table
Performance Scripts
col PROGRAM for a20
col EVENT for a20
col USERNAME for a8
col WAIT_CLASS for a10
set lines 200
select inst_id, SID, serial#, BLOCKING_SESSION,rpad(WAIT_CLASS,8)
WAIT_CLASS,SECONDS_IN_WAIT,PROGRAM, MODULE , EVENT ,username
from gv$session where BLOCKING_SESSION is not null order by BLOCKING_SESSION,PROGRAM;
select sid,blocking_session,username,sql_hash_value,logon_time from v$session where blocking_session is not null and sid=&sid;
select sid,serial#,sql_hash_value,logon_time from v$session where sid in (select SESSION_ID from v$LOCKED_OBJECT);
select ‘alter system kill session ”’||sid||’,’||serial#||”’;’ from v$session where sid in (select SESSION_ID from v$LOCKED_OBJECT);
alter system kill session ‘1156,8202’;
select LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE from dba_2pc_pending;
COMMIT FORCE ‘ASCEB05.rolta.COM.32e48fdf.40.35.306125’;
select sid,blocking_session,event,status,machine,username,sql_hash_value from v$session where sql_hash_value!=0 and status=’ACTIVE’;
select sql_hash_value from v$session where sid=4301;
select SQL_TEXT from v$sqlarea where HASH_VALUE=380700310 order by piece;
select used_urec from v$transaction where addr = (select taddr from v$session where sid = 4077);
select SECONDS_IN_WAIT from v$session where sql_hash_value=380700310;
**Objects Locked**
select s.osuser “O/S-User”, s.username “Ora-User”, s.sid “Session-ID”, s.serial# “Serial”, s.process “Process-ID”, s.status
“Status”,l.name “Obj Locked”, l.mode_held “Lock Mode” from gv$session s,dba_dml_locks l,gv$process p where l.session_id = s.sid and p.addr = s.paddr;
select object_id from dba_objects where object_name=’&tablename’;
select * from v$locked_object where object_id=&id number ;
col OBJECT_NAME for a45
select owner,object_name,object_type ,status from dba_objects where object_name=
select sid from v$access where owner=’&table_owner’ and object=’&table_name’;
select owner,object_name,object_type,status from dba_objects where object_name=’&object_name’;
select sid, serial#, command, taddr,status from v$session where sid=&session_id;
module,terminal,TO_CHAR(s.logon_time, ‘DD-MON-RR HH24:MI:SS’)
select ‘kill -9’||p.spid from v$session s ,v$process p where s.PADDR=p.addr and s.sid in (select sid from v$session)
alter system kill session ”;
Concurrent Managers
CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN CONCURRENT FND DEACTIVATE or ABORT
select v.request_id,s.sid, s.serial#, s.event, p.spid from apps.fnd_conc_requests_form_v v,
v$process p, v$session s where v.request_id = ‘896967’ and v.oracle_process_id = p.spid(+) and p.addr = s.paddr(+)
select oracle_process_id from apps.fnd_concurrent_Requests where request_id in (‘3112884′,’3111837′,’3110635′,’3111142’);
select REQUEST_ID,OS_PROCESS_ID from fnd_concurrent_requests where OS_PROCESS_ID=3087;
select sql_hash_value from gv$session where sid=4181
select sql_fulltext,rows_processed from gv$sql where sql_id=’3hbxr52kk5u42′;
select SQL_TEXT from v$sqlarea where HASH_VALUE=3295793396;
select used_urec from v$transaction where addr = (select taddr from v$session where sid = 3899);
select SECONDS_IN_WAIT from v$session where sql_hash_value=2405550420
select last_analyzed from dba_tables where table_name=’MSC_ST_SALES_ORDERS’
If the Concurrent Request is there at the front end, but there is no process running at the O/S level, then run the following query
select a.CONCURRENT_REQUEST_ID ,b.NODE_NAME,b.LOGFILE_NAME from fnd_conc_pp_actions a,fnd_concurrent_processes b
where a.PROCESSOR_ID=b.CONCURRENT_PROCESS_ID and a.ACTION_TYPE=6 and CONCURRENT_REQUEST_ID=3922386;
select r.request_id, s.sid, s.serial#, g.concurrent_program_name
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_programs g,
gv$session s
where r.controlling_manager=p.concurrent_process_id
and q.application_id=p.queue_application_id
and q.concurrent_queue_id=p.concurrent_queue_id
and qt.application_id=q.application_id
and qt.concurrent_queue_id=q.concurrent_queue_id
and r.phase_code=’R’
and qt.language in (‘US’)
and p.session_id=s.audsid
and g.concurrent_program_id=r.concurrent_program_id
and r.request_id in(6114233);
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1, request ;
select p.spid, s.inst_id,s.sql_hash_value from gv$session s,gv$process p where s.paddr=p.addr and s.sid=3435;
select used_urec from gv$transaction where addr = (select taddr from gv$session where sid = 3435);
select p.spid, s.inst_id from gv$session s,gv$process p where s.paddr=p.addr and s.sid=4065;
select inst_id,sql_id from gv$session where sid=&sid;
Tunning
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
select * from table(dbms_xplan.display_awr(‘&sqlid’));
ALTER SESSION SET sql_trace = TRUE;
EXECUTE SYS.dbms_system.set_sql_trace_in_session (1694, 47200, TRUE);
update fnd_concurrent_requests
set status_code = ‘X’, phase_code=’C’
where request_id =5655240;
select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,
s.status,logon_time,last_call_et from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct where
oracle_process_id=p.spid and s.paddr=p.addr and ct.concurrent_program_id=c.concurrent_program_id and request_id=&creq_id
/
OPP log/out files
SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &request_id;
—- give this query…
—- it will ask u the req id…
—- give the req id…
—- it will give the details about what it is doing ….
—- take the sid from that output…..
—- and run the following query
select sql_text from v$sqlarea where hash_value = (select sql_hash_value from v$session where sid =&sid)
and provide the sid….
Index rebuild
alter session set sort_area_size=100000000;
alter session set db_file_multiblock_read_count=128;
alter session set sort_multiblock_read_count=128;
select distinct status from dba_indexes;
select distinct status from dba_ind_partitions;
select distinct status from dba_ind_subpartitions;
select owner,TABLE_NAME,INDEX_NAME,status from dba_indexes where owner=’MSC’ and status=’UNUSABLE’;
select ‘alter index ‘||owner||’.’||index_name||’ rebuild;’ from dba_indexes where blevel>2;
select ‘alter index ‘ || owner ||’.’ ||index_name || ‘ rebuild online ;’ from dba_indexes where table_name=’MDP_MATRIX’ and owner=’DP’ and BLEVEL>1;
select TABLE_OWNER,PARTITION_NAME,COUNT(*) from dba_tab_partitions where partition_name IN(‘P_2008_05′,’P_2008_06’) GROUP BY TABLE_OWNER,PARTITION_NAME ORDER BY TABLE_OWNER;
select TABLE_OWNER,PARTITION_NAME,COUNT(*) from dba_tab_partitions where partition_name IN(‘P_2009_07′,’P_2009_08’) GROUP BY TABLE_OWNER,PARTITION_NAME ORDER BY TABLE_OWNER;
select ‘ALTER INDEX ‘||INDEX_OWNER||’.’||INDEX_NAME||’ REBUILD PARTITION ‘||PARTITION_NAME ||’;’ from dba_ind_partitions where INDEX_NAME in (‘XIE1MAILING_STATUS_TBL’,’XIE2MAILING_STATUS_TBL’,’XIE3MAILING_STATUS_TBL’,’XIE4MAILING_STATUS_TBL’,’IDX_TM_MAILING_STATUS_TBL’) and PARTITION_NAME=’
select ‘ALTER INDEX ‘||INDEX_OWNER||’.’||INDEX_NAME||’ REBUILD PARTITION ‘||PARTITION_NAME ||’;’ from dba_ind_partitions where INDEX_NAME in (‘XIE1SEND_STATUS_TBL’,’XIE2SEND_STATUS_TBL’,’XIE3SEND_STATUS_TBL’) and PARTITION_NAME in (‘P_2008_04′,’P_2008_01’,
2 ‘P_2008_03’);
SPID_SID_SERIAL
SELECT sess.sid, sess.serial#, pro.spid, sess.program, sess.username,sess.event
sess.module, sess.terminal, to_char(sess.logon_time,’Mon-ddth HH24:MI:SS ‘)LogOn_Time,
osuser, sess.status, sess.process FROM v$process pro, v$session sess WHERE
pro.spid in (‘&spid’) and pro.addr = sess.paddr
/
select spid from gv$process where addr in (select paddr from gv$session where sid=&sid);
select s.INST_ID,spid,s.status,s.module,s.terminal,TO_CHAR(s.logon_time, ‘DD-MON-RR HH24:MI:SS’) from gv$session s,gv$process p where paddr=addr and sid=3962;
whoami
select s.sid,s.status,s.module,s.terminal,TO_CHAR(s.logon_time, ‘DD-MON-RR HH24:MI:SS’) from v$process p, v$session s, v$sess_io i where i.sid = s.sid and s.paddr = p.addr and s.audsid = userenv(‘sessionid’);
select nvl(ss.USERNAME,’ORACLE PROC’) username, se.SID,VALUE cpu_usage,SS.SQL_HASH_VALUE,SS.LOGON_TIME
from v$session ss, v$sesstat se, v$statname sn where se.STATISTIC# = sn.STATISTIC#
and NAME like ‘%CPU used by this session%’ and se.SID = ss.SID
and ss.username=’APPS’ AND STATUS=’INACTIVE’ order by VALUE,SS.LOGON_TIME desc;
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,v$process c,v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid AND a.request_id = 1971141 — AND a.phase_code = ‘C’;
select ‘alter system kill session ”’||sid||’,’||serial#||”’;’ from v$session where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and to_char(logon_time,’hh’)>’01’ and status!=’ACTIVE’;
select sid,serial#,logon_time,sql_hash_value from v$session where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and status!=’ACTIVE’;
select owner,table_name,last_analyzed from dba_tables where table_name in (‘WF_ITEM_ACTIVITY_STATUSES’,’WF_NOTIFICATIONS’);
set pages 300
set lines 300
select sid,serial#,username,status,to_char(logon_time,’hh:mi:ss’),module,sql_hash_value from v$session where to_char(logon_time,’hh’)>’01’ and status=’INACTIVE’;
select ‘alter system kill session ”’||sid||’,’||serial#||”’;’ from v$session where to_char(logon_time,’hh’)>’02’ and status=’INACTIVE’;
select SQL_TEXT from v$sqlarea where HASH_VALUE=3272871235 order by piece;
select distinct o.object_name, sh.username||'(‘||sh.sid||’)’ “Holder”, sw.username||'(‘||sw.sid||’)’ “Waiter”,
decode(lh.lmode, 1, ‘null’, 2,
‘row share’, 3, ‘row exclusive’, 4, ‘share’,
5, ‘share row exclusive’ , 6, ‘exclusive’) “Lock Type”
from all_objects o, gv$session sw, gv$lock lw, gv$session sh, gv$lock lh
where lh.id1 = o.object_id
and lh.id1 = lw.id1
and sh.sid = lh.sid
and sw.sid = lw.sid
and sh.lockwait is null
and sw.lockwait is not null
and lh.type = ‘TM’
and lw.type = ‘TM’;
wait events for a session
library cache lock
================
SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event like ‘library cache%’
AND state = ‘WAITING’;
select
distinct
ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine,
ob.kglnaown obj_owner, ob.kglnaobj obj_name
,pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req
, w.state, w.event, w.wait_Time, w.seconds_in_Wait
— lk.kglnaobj, lk.user_name, lk.kgllksnm,
–,lk.kgllkhdl,lk.kglhdpar
–,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,
–,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl
from
x$kglpn pn, x$kglob ob,x$ksuse ses
, v$session_wait w
where pn.kglpnhdl in
(select kglpnhdl from x$kglpn where kglpnreq >0 )
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc
/
select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait, b.username, b.osuser,
b.machine, b.program
from v$session_event a, v$session b
where time_waited > 0
and a.sid = b.sid
and a.sid = 1511
order by time_waited;
Shared Pool
SELECT SUM(A.BYTES)/(1024*1024)SHARED_POOL_USED,
MAX(B.VALUE)/(1024*1024) SHARED_POOL_SIZE,
(MAX(B.VALUE)/(1024*1024)) – (SUM(A.BYTES)/(1024*1024)) SHARED_POOL_AVAIL,
((SUM(A.BYTES)/(1024*1024))/(MAX(B.VALUE)/(1024*1024)))*100 PCT_SHARED_POOL_AVL
FROM V$SGASTAT A, V$PARAMETER B
WHERE A.POOL=’shared pool’
AND A.NAME NOT IN (‘free memory’)
AND B.NAME=’shared_pool_size’;
High Disk Reads
select sql_text, executions, disk_reads, buffer_gets
from v$sqlarea
where decode(executions,0,disk_reads,disk_reads/executions) >
(select avg(decode(executions,0,disk_reads,disk_reads/executions))
+ stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea)
and parsing_user_id !=3
order by disk_reads desc;
Details regarding sessions which are causing high waits and cpu usage.
SELECT NVL(s.username, ‘(oracle)’) AS username,
s.sid, s.serial#, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM v$session_wait sw,
v$session s WHERE s.sid = sw.sid
and sw.event not in (‘SQL*Net message from client’, ‘SQL*Net message to client’)
and sw.event not like ‘Streams AQ%’
ORDER BY sw.seconds_in_wait DESC;
select nvl(ss.USERNAME,’ORACLE PROC’) username,
ss.SID, ss.serial#,ss.sql_hash_value,sa.executions,VALUE cpu_usage from v$session ss,
v$sesstat se, v$sqlarea sa, v$statname sn where se.STATISTIC# = sn.STATISTIC#
and NAME like ‘%CPU used by this session%’
and se.SID = ss.SID
and ss.sql_hash_value = sa.hash_value
and value > 0
order by VALUE desc;
******************************************************
col ssid form A6
col lmode form 999 heading “loc”
col request form 999 heading “req”
col name form A30
break on id1 on sid
select a.sid, a.type, a.id1, a.id2,
decode(a.lmode, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’, ‘?’),
decode(a.request, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’, ‘?’),
a.block
from v$lock a
where a.id1 in ( select id1 from v$lock where request < 0 )
order by a.id1, a.request, a.sid
/
****************************************************************************
Finding Locks:
rem ********************************************************************
rem * Filename : locks.sql – Version 1.0
rem * Description : Information about sessions waiting for locks
rem * Usage : start locks.sql
rem ********************************************************************
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1, request ;
select p.spid, s.inst_id from gv$session s,gv$process p where s.paddr=p.addr and s.sid=3769;
2/11/2010
col ssid form A6
col lmode form 999 heading “loc”
col request form 999 heading “req”
col name form A30
break on id1 on sid
select a.sid, a.type, a.id1, a.id2,
decode(a.lmode, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’, ‘?’),
decode(a.request, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’, ‘?’),
a.block
from gv$lock a
where a.id1 in ( select id1 from v$lock where request < 0 )
order by a.id1, a.request, a.sid
/
find /rh175/u03/app/applmgr/cmpcrpdv/inst/apps/cmpcrpdv_hostnamerh175/logs/appl/conc/log -name “*.req” -mtime +30 -exec rm {} \;
DBlinks
SELECT
‘create ‘||DECODE(U.NAME,’PUBLIC’,’public ‘)||’database link ‘||CHR(10)
||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.’)|| L.NAME||chr(10)
||’connect to ‘ || L.USERID || ‘ identified by ”appsdev’
||L.PASSWORD||”’ using ”’ || L.host || ””
||chr(10)||’;’ TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
REM START ESBLOG XXGFN_GBL_APPS_TO_SOA.rolta.COM
CREATE DATABASE LINK “XXGFN_GBL_ APPS_TO_SOA.rolta.COM”
CONNECT TO “ESBLOG” IDENTIFIED BY VALUES ’05C50A870C878511BC07617BC776B3C3DB’
henSING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostnameiu145.rolta.com) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ecstesti) ) )’;
REM END ESBLOG XXGFN_GBL_ APPS_TO_SOA.rolta.COM
XXGFN_GBL_SOA_TO_APPS.rolta.COM
CREATE DATABASE LINK “XXGFN_GBL_SOA_TO_APPS.rolta.COM”
CONNECT TO “APPS” IDENTIFIED BY VALUES ‘0509DD0DC7FDD8F7FAE2BFE85A3988F65FF571A2EF34C71579’
USING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostnameiu93.rolta.com) (PORT = 1522) ) ) (CONNECT_DATA = (SERVICE_NAME = AIBMUT1D) ) ) ‘;
REM END ESBLOG XXGFN_GBL_SOA_TO_APPS.rolta.COM
CREATE DATABASE LINK “APS_TO_EBS.rolta.COM”
CONNECT TO “XXITF_USER” IDENTIFIED BY XXITF_USER
USING ‘hostnameiu202.rolta.com:1621/gccap’;
Oracle: using recycling bin
Information about the objects in recycle bin:
set lines 400
select object_name, original_name, type, can_undrop , can_purge , droptime from recyclebin
or
show recyclebin
Restore
flashback table mytable to before drop;
Clear recycle bin
purge recyclebin;
To save the flashback information for the last 30 minutes:
ALTER SYSTEM SET UNDO_RETENTION = 1800;
—
SELECT * FROM some_table AS OF TIMESTAMP (SYSDATE – INTERVAL ‘5‘ MINUTE);
SELECT * FROM some_table AS OF TIMESTAMP (‘2008-01-18 06:31:58’, ‘YYYY-MM-DD HH24:MI:SSS’);
SELECT * FROM some_table AS OF SCN 364583948;
— dbms_flashback could be also used
EXECUTE dbms_flashback.enable_at_time (‘18-JAN-08 11:00:00‘);
Source text of the package
select text
from dba_source
where upper(name) like upper(‘&which_object’)
order by line ;
Use $ORACLE_HOME/bin/wrap utility to encrypt the package (there is no unwrap)
(Well, actually, there is unwrap – just look in the search machine for the words unwrap10 or rewrap…)
Source text of the views
set long 5000
col text for a80
select text from dba_views where view_name = upper(‘&which_view’);
select query from DBA_mviews where mview_name = upper(‘&which_view’);
SELECT view_definition FROM v$fixed_view_definition WHERE view_name=’&which_view’;
Source text of the synonym
select TABLE_OWNER || ‘.’ || TABLE_NAME || decode ( db_link , null , ” , ‘@’ || db_link ) SYNONYM_OBJECT
from dba_synonyms
where SYNONYM_NAME = upper(‘&which_synonym’);
Source text of the trigger
select
‘create or replace trigger “‘ || trigger_name || ‘”‘
|| chr(10)|| decode( substr( trigger_type, 1, 1 ), ‘A’, ‘AFTER’, ‘B’, ‘BEFORE’, ‘I’, ‘INSTEAD OF’ ) || chr(10) ||
triggering_event || chr(10) || ‘ON “‘ || table_owner || ‘”.”‘ || table_name
|| ‘”‘ || chr(10) || decode( instr( trigger_type, ‘EACH ROW’ ), 0, null, ‘FOR EACH ROW’ ) || chr(10) ,
trigger_body
from dba_triggers
where trigger_name = upper(‘&which_trigger’) and owner=upper(‘&trigger_owner’);
Export to the compressed file
s/etc/mknod my_own_pipe p
gzip -c -9 < my_own_pipe > mydump.gz &
exp username/password …… file=my_own_pipe
rm -f my_own_pipe
Import from a compressed file:
/etc/mknod my_own_pipe p
uncompress < mydump.Z > my_own_pipe &
imp username/password ……. file=my_own_pipe
rm -f my_own_pipe
set lines 400
col DIRECTORY_PATH for a65
select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
create directory EXPORT_DP as ‘/j2_utyas03/d001/backup’;
expdp SCHEMAS=XXITF_USER directory=DUMP_DIR dumpfile=expdpXXITF_USER.dmp logfile=expdpXXITF_USER.log PARALLEL=2
expdp scott/tiger@db10g tables=EMP,DEPT directory=DUMP_DIR dumpfile=expdpXXITF_OWNER.dmp logfile=expdpXXITF_OWNER.log
impdp SCHEMAS=XXITF_USER directory=DUMP_DIR dumpfile=expdpXXITF_USER.dmp logfile=impdpXXITF_USER.log REMAP_SCHEMA=XXITF_USER:XXITF_USER PARALLEL=2
select * from dba_datapump_jobs;
NOT working …expdp system/sygc321 SCHEMAS=DP DUMPFILE=DATAPUMP:DP_user%U.dmp,DATAPUMP:DP_user2%U.dmp,DATAPUMP:DP_user3%U.dmp,DATAPUMP:DP_user4%U.dmp LOGFILE=DATAPUMP:DP_user.log FILESIZE=12g PARALLEL=8 JOB_NAME=DP_user1
http://coskan.wordpress.com/2007/03/21/datapump-restart-capability-for-ora-39095/
Export> status
************************************************
set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl(‘TABLESPACE’,’TEMP’,user) from dual;
set pagesize 0
set linesize 980
set long 90000
SELECT DBMS_METADATA.GET_DDL(‘MATERIALIZED_VIEW’,’XXITF_CARRIER_MV’,’XXITF_OWNER’) FROM dual;
set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl(‘USER’, ‘XXGFN_USER’) || ‘/’ usercreate
from dba_users;
select ‘exec dbms_mview.refresh(‘||””||'”‘||owner||'”‘||’.’||'”‘||MVIEW_NAME||'”‘||””||’,’||””||’F’||””||’);’ from dba_mviews WHERE MVIEW_NAME IN (select name from dba_refresh_children where job=365);
CREATE USER A00424
IDENTIFIED BY welcome1
PASSWORD EXPIRE
DEFAULT TABLESPACE USER_DATA
TEMPORARY TABLESPACE TEMP2
PROFILE DEFAULT
ACCOUNT UNLOCK;
— 1 Role for Z01734
GRANT CONNECT TO A00424;
GRANT ARW_APPS_READ TO A00424;
ALTER USER A00424 DEFAULT ROLE ALL;
ls -lrt //nfs01/u01/app/oraecsqual/ecsqualappl/Migrations/Jan15|awk ‘{print “cp /nfs01/u01/app/oraecsqual/ecsqualappl/Migrations/Jan15/” $9 “/*.lst /nfs01/u01/app/oraecsqual/ecsqualappl/Migrations/Jan15/logs/”}’>copy_to_logs.ksh
ksh copy_to_logs.ksh
RMAN
nohup rman target rman_dba/backup rcvcat rman/rman@rmant cmdfile=utyca02_archive_bkup.sh msglog=utyca02_archive_bkup.log &
rman rman_unity/rman_unity@rmanp auxiliary / target rman_dba/backup@gcebp — Production..
catalog backuppiece ‘C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\RMAN_TEST\ORA_TEST_699129410_S53_S1’;
RUN {
ALLOCATE CHANNEL ch00 DEVICE TYPE DISK FORMAT;
ALLOCATE CHANNEL ch01 TYPE ‘SBT_TAPE’;
ALLOCATE CHANNEL ch02 TYPE ‘SBT_TAPE’;
SEND ‘NB_ORA_POLICY=UNITY_CRPDEV_CMPCRPDV_RMAN_HOT_DB’;
#SEND ‘NB_ORA_POLICY=UNITY_CMPGOLD_RMAN_HOT_DB_IU03_VTL’;
#SEND ‘NB_ORA_SCHED=Default-Application-Backup’;
#SEND ‘NB_ORA_SCHED=Hot_full’;
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
crosscheck archivelog all;
backup archivelog all delete input ;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
}
alter system set log_archive_dest_1=’location=/iu166/u02/oradata/arch/cmpcrpdv/db/apps_st/data’;
list backup of archivelog from sequence 3025 until sequence 246;
run{
backup archivelog from sequence 138 until sequence 142 delete input;
}
restore archivelog from sequence 212 until sequence 213;
set lines 200
col OPNAME for a45
SELECT sid,opname,context,sofar,totalwork,time_remaining/60,round(sofar/totalwork*100,1) “%DONE”
FROM v$session_longops
WHERE opname LIKE ‘gthr%’
AND totalwork <> 0
AND sofar <> totalwork
/
set lines 300
col filename for a55
select FILENAME,STATUS,ELAPSED_TIME,OPEN_TIME,CLOSE_TIME,BYTES/1024/1024,EFFECTIVE_BYTES_PER_SECOND/1024/1024 from V$BACKUP_SYNC_IO
where sid in (select sid from v$session where PROGRAM like ‘%rman%’);
col opname for a40
col units for a10
set lines 600
SELECT sid ,opname,sofar,totalwork,units,elapsed_seconds,time_remaining/60
FROM v$session_longops
WHERE sofar != totalwork;
rman rcvcat rman/rman@rmant auxiliary / target rman_dba/backup@utyeb05 cmdfile rman_duplicate_utyeb06.rman msglog rman_duplicate_utyeb06.log
oracle utyeb06 ->cat rman_duplicate_utyeb06.rman
resync catalog;
run {
set until time “to_date(’26-05-2009 22:22:00′,’dd-mm-yyyy hh24:mi:ss’)”;
#set until scn 61513786203 ;
ALLOCATE auxiliary CHANNEL ch00 TYPE ‘SBT_TAPE’;
ALLOCATE auxiliary CHANNEL ch01 TYPE ‘SBT_TAPE’;
SEND ‘NB_ORA_SERV=hostnamesu64, NB_ORA_CLIENT=hostnameiu161’;
DUPLICATE TARGET DATABASE TO utyeb06;
}
list backup summary;
crosscheck backup;
delete expired backup;
******************************************************************************************************************************
fetchcp.bat 1234 >c:\mks_only\extract_1234.txt 2>&1
lsattr -El aio0 12/23/2008
run{
allocate channel 1 disk type disk fomat;
allocate channel 2 disk type disk fomat;
backup archivelog all delete input
release channel 1;
release channel 2;
}
ls -lrt | sed -n ‘1,20p’|awk ‘{print “rm -rf ” $9 }’
ls -l | grep ‘Jan 19 ‘ | awk ‘{print $9}’ | xargs -i rm -rf {}
ls -lrt|egrep ‘Jan|Feb’
rm `ls -lrt|egrep ‘Jan|Feb’`
REM START ESBLOG XXGFN_GBL_APPS_TO_SOA.rolta.COM
CREATE DATABASE LINK “XXGFN_GBL_ APPS_TO_SOA.rolta.COM”
CONNECT TO “ESBLOG” IDENTIFIED BY VALUES ’05C50A870C878511BC07617BC776B3C3DB’
USING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostnameiu145.rolta.com) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ecstesti) ) )’;
REM END ESBLOG XXGFN_GBL_ APPS_TO_SOA.rolta.COM
ps -ef|grep oracle$ORACLE_SID|grep LOCAL=NO|awk ‘{print “kill -9 ” $2 }’ > kill.sh;chmod 755 kill.sh;sh kill.sh &
opmnctl start
opmnctl startproc process-type=HTTP_Server
opmnctl startproc process-type=oc4j_soa
FNDLOAD apps/ppassprod213 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct user.ldt FND_USER USER_NAME=’A39499′
FNDLOAD apps/apgc321 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct user.ldt
set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl(‘USER’, username) || ‘/’ usercreate
from dba_users where username=’XXCNV’;
Metalink Notes…..
2. Run the command:
cd $FND_TOP/patch/115/bin
3. Run the command:
perl ojspCompile.pl –compile –flush -p 10
perl ojspCompile.pl –compile –fast -quiet -p 20 *** for faster compilation..
We saw a recurrence of the OACORE process taking CPU on hostnamers10 (SIT1 middle tier). Please do the following if you see the issue again. $ps -ef |grep
$ kill -3
$ adopmnctl.sh debug > ~/knj/.log
$ cp /rs10/u02/applmgr/utyeb05/inst/apps/utyeb05_hostnamers10/logs/ora/10.1.3/opmn/default_group~oacore~default_group~1.log ~/knj/oacore_.log
– Stop all Oracle applications services
– Delete the following:
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*
– Start up the application services
If you still get the same error, then please run AutoConfig. Let us see whether it completes successfully or not.
Note: 387859.1 – Using AutoConfig to Manage System Configurations in Oracle Applications Release 12
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=387859.1
If the above does not help, then I believe we need to enable debug to get more details about the error.
Note: 422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=422419.1
745711.1 – Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12
$FND_TOP/patch/115/bin/ojspCompile.pl ojspCompile.conf –compile –flush -p 20
OracleMetaLink Note 215268.1 contains complete syntax and definition
frmcmp_batch ARIINR.fmb userid=apps/av0tar321 module_type=form compile_all=Yes
if `ps -ef|grep 17440|grep -v grep|wc -l` = 2
then
echo “not completed”
else
echo “Completed”
if
while true
do
ps -ef|grep 17440|grep -v grep
sleep 15
clear
done
scp filename login@hostname:filename
if [ $? -ne 0 ]
then
echo “Error Occured While SCP”
else
echo “———————————————————”
echo “successful transmission “
echo “———————————————————”
fi
pspendurkar (2:58:33 PM): a39057
pspendurkar (2:58:36 PM): password is India123
# —————————————————-
# ENV Setup
# —————————————————-
set -o vi
export PATH=.:$PATH:/rh157/u04/Unity_SW/datAvail/bin
export ORACLE_PATH=.:$ORACLE_PATH:/rh157/u04/Unity_SW/datAvail/dba_sql
export scripts=’cd $ADMIN_SCRIPTS_HOME’
MATERIALIZED VIEW
select mview_name,owner from dba_mviews;
SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’,’TEMP’) from dual;
DROP MATERIALIZED VIEW emp_data;
frmcmp module=ODPN.pll userid=apps/apps@ module_type=library compile_all=yes
TKPROF returns “could not open trace file”
Doc ID: 300439.1
gunzip linux_grid_control_agentdwn_10_1_0_2.cpio.gz
cpio -idmv < linux_grid_control_agentdwn_10_1_0_2.cpio
unzip p3731593_10103_LINUX.zip
**************OPatch ********************
export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
opatch apply -local
opatch apply -invPtrLoc /rs10/u03/utyeb05/apps/tech_st/10.1.2/oraInst.loc
$ORACLE_HOME/OPatch/opatch lsinventory | grep -i 5998987
OPatch analysis for patch 7196863
opatch query -all
./runInstaller -silent -attachHome -noClusterEnabled -invPtrLoc ./etc/oraInst.loc ORACLE_HOME=”/u01/oracle/
product/10.2.0/prodracdb” ORACLE_HOME_NAME=”OraDb10g_home1″
RAC
$ORA_CRS_HOME/bin/crsctl stop crs
————————————————————————–
crsctl enable crs – enables startup for all CRS daemons
crsctl disable crs – disables startup for all CRS daemons
crsctl start crs – starts all CRS daemons.
crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
————————————————————————–
crs_start and crs_stop
New feature introduced in 10g R2
The crs_start and crs_stop command can be used to start and stop CRS managed resources
$ crs_start resource-name -all (all resource)
$ crs_stop resource-name -all (all resource)
crs_relocate
change where resource is active$ORA_CRS_HOME/bin/crsctl stop crs
crs_stat -t
crs_start ora.hostnameiu60.ons -f
export CRS_HOME=/racp01/u02/oracle/crs/10.2.0
export PATH=$CRS_HOME/bin:$PATH:
————————————————————————–
crsctl enable crs – enables startup for all CRS daemons
crsctl disable crs – disables startup for all CRS daemons
crsctl start crs – starts all CRS daemons.
crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
********ASM***
resize–qiomkfile -r 8199M dat384cl1.dbf
qiomkfile -r 2051M idx842bfip1.dbf
qiomkfile -r 4102M dlscl1_cl2_tsc08_03.dbf ==> 4097M
qiomkfile -r 8199M dat384cl1.dbf ==> 8194M
add==> qiomkfile -h -s 8199M idx110dbshare3.dbf
qiomkfile -h -s 5005m dat310citi.dbf
4. Add datafile into database, the size is 5000m, (not 2001m)
********SWB******
opmnctl restartproc process-type=OC4J_swb
opmnctl restartproc process-type=OC4J_swb_ui
opmnctl @cluster status -l
opmnctl @cluster status -app |grep esb
opmnctl @cluster status -app |grep asc
opmnctl @cluster status -app |grep orabpel
opmnctl @cluster status -app |grep default
Cleaning temporary tablespace
Run ICXDLTMP.sql
Location in Release12 $FND_TOP/patch/115/sql/ICXDLTMP.sql
Running ICXDLTMP.sql is equivalent to running “Purge data from temporary table” concurrent request.
–specify a file with extension htm or html
set markup html on spool on
spool c:\ex1.html
–your query
select * from dept;
–end the html file
spool off
–set back to normal output
set markup html off spool off
******************************Grid Agent*******http://download.oracle.com/docs/html/B12013_03/emctl.htm
emctl status agent
emctl stop agent
emctl clearstate agent
emctl start agent
Listing the Targets on a Managed Host
./emctl config agent listtargets
Stop an immediate blackout emctl stop blackout
Set an immediate blackout for all targets on a host emctl start blackout [-nodeLevel] [-d ]
The -nodeLevel option is used to specify a blackout for all the targets on the host; in other words, all the targets that the Management Agent is monitoring, including the Management Agent host itself. The -nodeLevel option must follow the blackout name. If you specify any targets after the -nodeLevel option, the list is ignored.
Check the status of a blackout emctl status blackout [[:]]….
emctl stop blackout utyeb02
#To start an immediate blackout called “bk3” for database “db1” for 30 minutes:
emctl start blackout bk3 db1 -d 30
#To start an immediate blackout called “bk3” for database “db2” for five hours:
emctl start blackout bk db2 -d 5:00
Grid server
Start All
/u01/app/oracle/product/10.2.0/db10g/bin/dbstart
/u01/app/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl startall
/u01/app/oracle/product/10.2.0/agent10g/bin/emctl start agent
Stop All
/u01/app/oracle/product/10.2.0/agent10g/bin/emctl stop agent
/u01/app/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl stopall
/u01/app/oracle/product/10.2.0/db10g/bin/dbshut
List all indexed columns for a given table
break on index_name skip 1
col index_name format a30
col uniuenes format a12
col column_name format a30
prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_name
from user_indexes i, user_ind_columns c
where i.index_name = c.index_name
and i.table_name = upper(‘&1’)
order by c.index_name, c.column_position
/
Enable trace for a user session:
SQLPLUS> exec sys.dbms_system.set_sql_trace_in_session(SID, SERIAL, true);
Use tkprof to analyze the trace file:
tkprof [TRACE FILE] [OUTPUT FILE] explain=/ sort=prsela,exeela,fchela
table=ops\$oracle.plan_table
select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait, b.username, b.osuser,
b.machine, b.program
from v$session_event a, v$session b
where time_waited > 0
and a.sid = b.sid
and a.sid = 283
order by time_waited;
ls -lrt |egrep ‘Nov|Oct’
Debug or Trace Option Enabled in Profiles
SELECT A.CONCURRENT_PROGRAM_NAME “Program Name”,
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) “User Program Name”,
SUBSTR(B.USER_NAME,1,15) “Last Updated By”,
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE=’Y’
AND A.LAST_UPDATED_BY=B.USER_ID;
Run this script to see if any Debug or Trace profile options have been set to Y, meaning that they are enabled. Some profile options may be required to be set to Y, but others should be N.
select distinct
a.application_short_name app_short,
user_profile_option_name optname,
decode(level_id,
10001,’SITE’,
10002,’APP : ‘||a2.application_short_name,
10003,’RESP: ‘||r.responsibility_key,
10004,’USER: ‘||u.user_name,
‘Unknown’) d_level,
profile_option_value optval,
v.last_update_date updated
from fnd_profile_options_vl o,
fnd_profile_option_values v,
fnd_application a,
fnd_application a2,
fnd_responsibility r,
fnd_user u
where (
o.user_profile_option_name like ‘%Debug%’ or
o.user_profile_option_name like ‘%DEBUG%’ or
o.user_profile_option_name like ‘%Trace%’ or
o.user_profile_option_name like ‘%TRACE%’
)
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
— Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = ‘Y’
order by 2,1,3,4;
O/S level observation
[oracle@r0469 appl]$ . APPSVIS_r0469.env
[oracle@r0469 appl]$ cd $ADMIN_SCRIPTS_HOME
[oracle@r0469 scripts]$ pwd
Bouncing apache or Application….
[oracle@r0469 ~]$ cd /oracle/VIS/inst/apps/VIS_r0469/admin/scripts
[oracle@r0469 scripts]$ ./adapcctl.sh stop
You are running adapcctl.sh version 120.7.12010000.2
Stopping OPMN managed Oracle HTTP Server (OHS) instance …
adapcctl.sh: exiting with status 0
adapcctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adapcctl.txt for more information …
[oracle@r0469 scripts]$ ./adapcctl.sh start
You are running adapcctl.sh version 120.7.12010000.2
Starting OPMN managed Oracle HTTP Server (OHS) instance …
adapcctl.sh: exiting with status 0
adapcctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adapcctl.txt for more information …
In 11i the apache start and stop command, also bounce the JVM running on JSERV and clear caches by doings as below :
[oracle@r0469 ~]$ rm -rf $COMMON_TOP/_page/*
[oracle@r0469 ~]$ rm -rf $IAS_ORACLE_HOME/apache/modplsql/_page/*
But in R12 the command to stop and start apache only takes action on Apache web server and not on the OC4J container that executes the JVM.
$INST_TOP/admin/scripts/adoacorectl.sh
Therefore when making modification to JAVA class in R12 for the web application, in order to have that change recognized, it is needed to bounce OC4J using adoacorectl.sh script.
REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
select ‘Number of user sessions : ‘ || count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != ‘Y’ and PSEUDO_FLAG = ‘N’ and (last_connect + decode(FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’)/60)/24) > sysdate and counter < limit_connects;
REM
REM END OF SQL
REM
HOW TO DETERMINE “ACTIVE FORMS USERS” FOR FORMSGROUP
Check the number of f60webmx processes on the Middle Tier server.
For example:
ps -ef | grep f60webx | wc -l
Oracle Tuning And Diagnostics Script — Active Session Info
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece
Trace SQL Query Average Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s
WHERE s.sql_id=’4n01r8z5hgfru’
Get The Detail Explain Plan Using SQL ID
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‘dtdqt19kfv6yx’))
Session Elapsed Processing Time
SELECT s.sid, s.username, s.module,
round(t.VALUE/1000000,2) “Elapsed Processing Time (Sec)”
FROM v$sess_time_model t, v$session s
WHERE t.sid = s.sid
AND t.stat_name = ‘DB time’
AND s.username IS NOT NULL
AND t.VALUE/1000000 >= ’1′ –running more than 1 second
ORDER BY round(t.VALUE/1000000,2) DESC
Session Elapsed Processing Time Statistic By SID
SELECT a.sid, b.username, a.stat_name, ROUND((a.VALUE/1000000),2) “Time (Sec)”
FROM v$sess_time_model a, v$session b
WHERE a.sid = b.sid
AND b.sid = ’194′
ORDER BY ROUND((a.VALUE/1000000),2) DESC
Use Longops To Check The Estimation Query Runtime
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,
last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,
elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,
round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> ’0′
Detect Blocking Session
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’
Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece
RAC Active Table Locking
SELECT b.sid, b.serial#, a.spi0A
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’
Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece
RAC Active Table Locking
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text,
b.state, c.sql_text,b.logon_time,
b.STATUS, e.owner, e.object_name “Table Lock”
FROM gv$process a, gv$session b, gv$sqltext c, gv$locked_object d, dba_objects e
WHERE a.addr=b.paddr
AND b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece
Monitor Highest SQL Wait Time Using Active Session History (ASH)
SELECT h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text,
SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Monitor Highest Object Wait Time Using Active Session History (ASH)
SELECT o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, dba_objects o, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
GROUP BY o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Monitor Highest Event Wait Time Using Active Session History (ASH)
SELECT h.event “Wait Event”, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.event_id = e.event_id
AND e.wait_class <> ‘Idle’
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Database Time Model Statistic
SELECT wait_class, NAME, ROUND (time_secs, 2) “Time (Sec)”,
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
(SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event
AND n.wait_class <> ‘Idle’
AND time_waited > 0
UNION
SELECT
‘CPU’,
‘Server CPU’,
SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN (‘background cpu time’, ‘DB CPU’))
ORDER BY time_secs DESC;
Monitor I/O On Data Files
SELECT vfs.file#, dbf.file_name, dbf.tablespace_name, dbf.bytes, vfs.phyrds/vfs.phywrts,
vfs.phyblkrd/vfs.phyblkwrt, vfs.readtim, vfs.writetim
FROM v$filestat vfs, dba_data_files dbf
WHERE vfs.file# = dbf.file_id
I/O Stats For Data Files & Temp Files
SELECT file_no,
filetype_name,
small_sync_read_reqs “Synch Single Block Read Reqs”,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
round(small_sync_read_latency/1000,2) “Single Block Read Latency (s)”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
async_io “Asynch I/O Availability”
FROM v$iostat_file
WHERE filetype_id IN (2,6) –data file and temp file
I/O Stats By Functionality
SELECT function_name,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
number_of_wait “I/O Waits”,
round(wait_time/1000,2) “Total Wait Time (ms)”
FROM v$iostat_function
ORDER BY function_name
Temporary Tablespace Usage By SID
SELECT tu.username, s.sid, s.serial#, s.sql_id, s.sql_address, tu.segtype,
tu.extents, tu.blocks, SQL.sql_text
FROM v$tempseg_usage tu, v$session s, v$sql SQL
WHERE tu.session_addr = s.addr
AND tu.session_num = s.serial#
AND s.sql_id = SQL.sql_id
AND s.sql_address = SQL.address
Monitor Overall Oracle Tablespace
SELECT d.STATUS “Status”,
d.tablespace_name “Name”,
d.contents “Type”,
d.extent_management “Extent Management”,
d.initial_extent “Initial Extent”,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900′) “Size (M)”,
TO_CHAR(NVL(a.bytes – NVL(f.bytes, 0), 0)/1024/1024,’99,999,999.999′) “Used (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.bytes * 100, 0), ’990.00′) “Used %”,
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),’99,999,990.900′) “MaxSize (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.maxbytes * 100, 0), ’990.00′) “Used % of Max”
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,’NO’,bytes,’YES’,maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;
Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name,
‘physical reads’, VALUE,0))/
(SUM(DECODE(name, ‘db block gets’, VALUE,0))+
(SUM(DECODE(name, ‘consistent gets’, VALUE, 0))))))*100),2)
|| ‘%’ “Buffer Cache Hit Ratio”
FROM v$sysstat –Use gv$sysstat if running on RAC environment
Library Cache Hit Ratio
SELECT SUM(pins) “Total Pins”, SUM(reloads) “Total Reloads”,
SUM(reloads)/SUM(pins) *100 libcache
FROM v$librarycache –Use v$librarycache if running on RAC environment
DB Session Memory Usage
SELECT se.sid,n.name, MAX(se.VALUE) maxmem
FROM v$sesstat se, v$statname n
WHERE n.statistic# = se.statistic#
AND n.name IN (‘session pga memory’,’session pga memory max’,
‘session uga memory’,’session uga memory max’)
GROUP BY n.name, se.sid
ORDER BY MAX(se.VALUE)
select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;
select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space
group by tablespace_name;
Query to List all the responsibilities attached to a User
Based on a request from one of the reader here is the query which he was looking for.
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv(‘LANG’);
Oracle Database Links (DB Link)
A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema.
There are 3 types of DB links. They are as follows:
1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users.
2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.
3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.
Types of Logins:
In dblink we can use 2 types of login. They are as follows:
1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
======
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING
Code: (Text)
Create public database link daslink connect to current_user using ‘ORCL’
2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
======
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING
Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘ORCL’
Note: To create the public DBLINK the user must have create public database link system privileges.
oracle2test =
Begin
fnd_program.delete_executable(‘program short name’,’schema’);
commit;
End;
Option 1:
SQL> select * from icx_parameters;
Option 2:
SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME =’APPS_FRAMEWORK_AGENT’)
AND LEVEL_VALUE=0;
Script to display status of all the Concurrent Managers
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date “Change Date”,
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) “Created By”,
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) “Last Update By”
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
SELECT
Concurrent Program Monitoring Scripts
——— Terminating requests———-
Fnd_Concurrent_Processes P,
Fnd_User
user_concurrent_program_name conc_program,
AND R.Concurrent_Program_ID = P.Concurrent_Program_ID )
SQL to identify the query which takes long time
f.user_name
,a.request_id “Req Id”
,a.concurrent_program_id “Prg Id”
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id “OS”
,vs.sid
,vs.serial# “Serial#”
,vp.spid
,TO_CHAR(request_date,’DD-MON-YY hh24:mi:ss’) request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 “Time”
,c.concurrent_program_name||’ – ‘||c2.user_concurrent_program_name “Program”
FROM ASYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN (‘I’,’P’,’R’,’T’)
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = ‘US’
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where
upper(fct.user_concurrent_program_name) = upper(‘concurrent program’)
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = ‘CP_EXECUTION_OD_CODE’
‘Trace id: ‘||oracle_Process_id,
‘Trace Flag: ‘||req.enable_trace,
‘Trace Name: ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,
‘Status : ‘||decode(phase_code,’R’,’Running’) ||’-‘||decode(status_code,’R’,’Normal’),
‘SID Serial: ‘||ses.sid||’,’|| ses.serial#, ‘Module : ‘||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’
and dbnm.name=’db_name’
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
eg: ‘directory’/ora_3465_act.trc.
In the above directory, do ‘ls -l’ for ‘*3465*trc’ and any latest file that you see with 3465 as a part of the file name would be the trace file.
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
ALTER SYSTEM KILL SESSION ‘SID,SERIALl#’;
fr.responsibility_name
FROM fnd_user fu,
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date – 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date – 1), SYSDATE))
AND user_name like ‘OPERATIONS’ — for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name
Script to display status of all the Concurrent Managers
Shahzad Khan's 