Database Important Scripts

Check the active process

ps -fu applmgr

Kill the Active process

kill -9 `ps -u applmgr -o "pid="`


Clear the whole content and remove all lines inside the file then :

Code:
cat /dev/null > your_file

Total TBS Size

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc
/

Free Space Size

select tablespace_name, bytes/1024/1024 from dba_free_space
/

Last Analyzed

select max(last_analyzed) from dba_tables
/

TEMP TBS Size

select file_name, sum(bytes)/(1024*1024) from dba_temp_files
group by file_name
/

select tablespace_name, sum(bytes)/(1024*1024) TEMPSIZE from dba_temp_files
group by tablespace_name
/

Script: Listing Memory Used By All Sessions

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 3
/

SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts

select 'INIT.ORA sort_area_size: 'value
from v$parameter
where name like 'sort_area_size'
/

select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')
/

This script lists all jobs that are currently running in the local database.

select
djr.sid sess,
djr.job jid,
dj.log_user subu,
dj.priv_user secd,
dj.what proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5) lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5) nrt,
djr.failures fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job
/

Lists all jobs that have been submitted to run in the local database job queue.

select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs
/

Compile

for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;
select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY','VIEW') AND STATUS ='INVALID';
spool off;

for running the script
@c:\dba\compile.sql

Section – A
This scripts is used to take a index rebuild

The following tables we have to recreate it, (FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)


1) This scripts is used to take a index rebuild on following tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)
(spool the following output)

select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE
a.TABLE_NAME in('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'NO'
union
select 'ALTER INDEX ' a.OWNER '.' b.INDEX_NAME ' REBUILD PARTITION 'b.partition_name' NOLOGGING;'
from dba_indexes a,dba_ind_partitions b
where a.indeX_name = b.index_name
and a.TABLE_NAME in ('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'YES'

2) ALTER TABLE APPLSYS.FND_CONCURRENT_REQUESTS move;
3) ALTER TABLE APPLSYS.FND_FILE_TEMP move;
3) run the index rebuild outuput scripts.
4) run the gather table statistics for mentioned tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)




TABLE

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'TABLE'
and (extents +1) >= max_extents;

ALTER TABLE .table STORAGE ( MAXEXTENTS x);

where x is greater than max_extents and lesser than unlimited
(2147483645);

ALTER TABLE .table STORAGE ( MAXEXTENTS UNLIMITED);



INDEX

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'INDEX' and
(extents +1) >= max_extents;



ALTER INDEX .index STORAGE ( MAXEXTENTS integer);

ALTER INDEX .index STORAGE ( MAXEXTENTS UNLIMITED);

Section B

The following script's output is used to run a index rebuild on weekly basis

select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE OWNER NOT IN ('SYS','SYSTEM')
AND a.INDEX_TYPE='NORMAL'
AND NOT EXISTS (SELECT INDEX_NAME FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND INDEX_NAME = a.INDEX_NAME)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME from dba_tables c
where TEMPORARY='Y'
and a.table_name = c.table_name)
ORDER BY OWNER,INDEX_NAME;
/



for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;

select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','VIEW') AND STATUS ='INVALID';
spool off;
for running the script
select 'alter ' 'PACKAGE ' OBJECT_NAME ' compile body' ';' from dba_objects where object_type in ('PACKAGE BODY') AND STATUS ='INVALID';
/



spool runts.sql
select 'alter database datafile '''file_name''''' autoextend on;' from dba_data_files;
/
@runts

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:


select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')

/


SESSION_WAITS

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
ORDER BY sw.seconds_in_wait DESC;
/


To select the username and the process status

select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224


selecta.requested_start_date,a.last_update_date,a.status_code,b.user_name ,a.argument_text from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224


To select the username,process,status,Terminal name using SID

select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
/


To see the currently updated archive log files

SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;

To find the BDUMP,UDUMP directory

select value from v$parameter where name = 'background_dump_dest'
select value from v$parameter where name = 'user_dump_dest'
select value from v$parameter where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest')
/


Identify the user and session ID for a UNIX process

This quick process identifies the Oracle user and session ID of a UNIX process that is using up a large amount of CPU. It can also be used to help find inefficient queries. This query is placed inside of a script that I pass the UNIX process ID to.

select s.sid, s.username, s.osuser, s.serial#
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &1;
/

Tracing an Oracle session by SID

This code accepts an Oracle session ID [SID] as a parameter and will show you what SQL statement is running in that session and what event the session is waiting for. You simply create a SQL file of the code and run it from the SQL prompt.

prompt Showing running sql statements ...........................

select addr from v$process where spid='8419'

select * from v$session where PADDR='00000003B29F9388'


select a.sid Current_SID, a.last_call_et ,b.sql_text
from v$session a
,v$sqltext b
where a.sid = 14
and a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by a.last_call_et,a.sid,b.piece;

prompt Showing what sql statement is doing.....................

select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,
d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
/

Check all active processes, the latest SQL, and the SQL hit ratio

select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;

Monitoring Oracle processes

select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr
order by s.status,1;

/


Displays concurrent requests that have run times longer than one hour (3600 seconds)

SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,phase_code,status_code,
printer,print_style,description,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-
MON-RRRR')
ORDER BY 2 desc
/

This script will map concurrent manager process information about current concurrent managers.

SELECT proc.concurrent_process_id concproc,
SUBSTR(proc.os_process_id,1,6) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(vproc.spid,1,10) svrproc,
DECODE(proc.process_status_code,'A','Active',
proc.process_status_code) cstat,
SUBSTR(concq.concurrent_queue_name,1,30) qnam,
-- SUBSTR(proc.logfile_name,1,20) lnam,
SUBSTR(proc.node_name,1,10) nnam,
SUBSTR(proc.db_name,1,8) dbnam,
SUBSTR(proc.db_instance,1,8) dbinst,
SUBSTR(vsess.username,1,10) dbuser
FROM fnd_concurrent_processes proc,
fnd_concurrent_queues concq,
v$process vproc,
v$session vsess
WHERE proc.process_status_code = 'A'
AND proc.queue_application_id = concq.application_id
AND proc.concurrent_queue_id = concq.concurrent_queue_id
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+)
ORDER BY proc.queue_application_id,
proc.concurrent_queue_id

Show currently running concurrent requests

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);
/

To find the CPU consumption

select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command
from
v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.paddr=p.addr
and se.sql_address=q.address
order by ss.value desc,ss.sid
/

Script to show problem tablespaces

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc


Oracle space monitoring scripts table space wise
This scripts gives warning indicator for all tablespaces that have less then 90% free space in them (with an asterisk in the last column).

select tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1 ;


Oracle space monitoring scripts (grand total table space)

select
sum(tot.bytes/(1024 *1024))"Total size",
sum(tot.bytes/(1024*1024)-sum(nvl(fre.bytes,0))/(1024*1024)) Used,
sum(sum(nvl(fre.bytes,0))/(1024*1024)) Free,
sum((1-sum(nvl(fre.bytes,0))/tot.bytes)*100) Pct
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
/


What's holding up the system?

Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:

Select
p.pid, s.sid, s.serial#,s.status, s.machine,s.osuser, p.spid, t.sql_text
From
v$session s,
v$sqltext t,
v$process p
Where
s.sql_address = t.address and
s.paddr = p.addr and
s.sql_hash_value = t.hash_value and
s.sid > 7 and
s.audsid != userenv ('SESSIONID')
Order By s.status,s.sid, s.osuser, s.process, t.piece ;
/

Script to display status of all the Concurrent Managers
select distinct Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
where Q.Application_Id = Queue_Application_ID
and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and Spid = Os_Process_ID
and Process_Status_Code not in ('K','S')
order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Get current SQL from SGA

select sql_text
from V$session s , V$sqltext t
where s.sql_address=t.address
and sid=
order by piece;

You can find the SID from V$session.

What SQL is running and who is running it?

select a.sid,a.serial#,a.username,b.sql_text
from v$session a,v$sqltext b
where a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by 1,2,b.piece;

---
select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
s.sid "Session ID",s.status "Status",
s.username "Username", RTRIM(s.osuser) "OS User",
b.spid "OS Process ID",s.machine "Machine Name",
s.program "Program",c.sql_text "SQL text"
from v$session s, v$session_longops l,v$process b,
(select address,sql_text from v$sqltext where piece=0) c
where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
group by s.sid,s.status,s.username,s.osuser,s.machine,
s.program,b.spid, b.pid, c.sql_text order by s.status,s.sid

TO FIND THE SORTING DETAILS

SELECT a.sid,a.value,b.name from
V$SESSTAT a, V$STATNAME b
WHERE a.statistic#=b.statistic#
AND b.name LIKE 'sort%'
ORDER BY 1;
/

Long running SQL statements

SELECT s.rows_processed, s.loads, s.executions, s.buffer_gets,
s.disk_reads, t.sql_text,s.module, s.ACTION
FROM v$sql /*area*/ s,
v$sqltext t
WHERE s.address = t.address
AND ((buffer_gets > 10000000) or
(disk_reads > 1000000) or
(executions > 1000000))
ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece
/
Move a table from one tablespace to another

There are many ways to move a table from one tablespace to another. For example, you can create a duplicate table with dup_tab as select * from original_tab; drop the original table and rename the duplicate table as the original one.

The second option is exp table, drop it from the database and import it back. The third option (which is the one I am most interested in) is as follows.

Suppose you have a dept table in owner scott in the system tablespace and you want to move in Test tablespace.

connect as sys

SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT SYSTEM

Elapsed: 00:00:00.50

You want to move DEPT table from system to say test tablespace.

SQL :> connect scott/tiger
Connected.
SQL :> alter table DEPT move tablespace TEST;

Table altered.


SQL :> connect
Enter user-name: sys
Enter password:
Connected.
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT TEST
/


To compile the procedure

Alter PROCEDURE JA_IN_BULK_PO_QUOTATION_TAXES compile

To compile the form

F60gen userid=apps/metroapps@dev module=

.fmb
output_file=/forms/US/form name.fmx
module_type=form batch=no compile_all=special

/

Run this sql statement to get package version :

select text from user_source where name='&package_name'
and text like '%$Header%';

prompt asks you the package name, in return it gives you two lines
corresponding to specifications and body creation files

You can also get pls version on database by running:

select name , text
from dba_source
where text like '%.pls%'
and line <>views

Sometimes version information is available in view definition.
Try the following sql statement :

col TEXT for a40 head "TEXT"
select VIEW_NAME, TEXT
from USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';

workflow

Run wfver.sql (see §5.e) to get version of workflow packages and views.




Finding active and inactive sessions :


set linesize 132
col started format a15
col osuser format a10
col username format a13
col sid format 99999
select d.physical_reads, p.spid,a.sid, a.serial#, a.username, a.osuser,
TO_CHAR(a.logon_time, 'MM/DD HH24:MI') started, a.sql_hash_value,status
from sys.v_$session a, sys.v_$process p, sys.v_$sess_io d
where a.sid = d.sid and a.paddr = p.addr and a.type <> 'BACKGROUND'
and a.status = 'INACTIVE'
order by a.username,a.logon_time




Select 'alter system kill session '''sid','serial#''';' from
V$session where status='INACTIVE';

select p.spid,s.status,s.username,s.machine,s.sid,s.serial#,s.program,
s.osuser,s.sql_address from v$process p,v$session s
where s.paddr = p.addr and s.sid in(&sid)



Finding Locks


select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc

Finding Blocking sessions :

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0
and l1.id1=l2.id1 and l1.id2=l2.id2

select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;



-- sessions with highest CPU consumption

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;


-- sessions with the highest time for a certain wait

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

-- sessions with highest DB Time usage

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;

db file scattered read
Systemwide Waits:
If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at where BLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).

It can also be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file scattered read' and total_waits>0 ORDER BY 3,2;

One can also look at:
Statements with high DISK_READS in Sessions with high table scans blocks gotten in

db file sequential read

Systemwide Waits:IO is a normal activity so you are really interested in unnecessary or slow IO activity. If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO" and "File IO" sections of the ESTAT or STATSPACK reports to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at .
It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits>0 ORDER BY 3,2;

One can also look at:
Statements with high DISK_READS in Sessions with high "physical reads" in

Undo Tablespace Check

set linesize 150
col username format a18
col sid format 99999
col object_name format a18
select s.username,s.sid,rn.name,rs.rssize/1024/1024 "UsedSize",rs.status,t.used_ublk,t.used_urec,do.object_name
from V$TRANSACTION t,V$SESSION s,V$ROLLNAME rn,V$ROLLSTAT rs,V$LOCKED_OBJECT lo,DBA_OBJECTS do
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
and t.xidusn = lo.xidusn(+)
and do.object_id = lo.object_id;

Temp Tablespace Check

prompt
prompt +----------------------------------------------------+
prompt TEMP TABLESPACE USAGE BY SESSION
prompt +----------------------------------------------------+

--Temp TS usage by each session:

select b.tablespace
,a.sid,
sum(round(((b.blocks*p.value)/1024/1024),2)) size_mb
from v$session a
,v$sort_usage b
,v$process c
,v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr and
a.paddr=c.addr
group by b.tablespace,a.sid
order by sum(round(((b.blocks*p.value)/1024/1024),2)) desc

Extract the DDL Scripts for the existing database links:


SELECT
'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)
DECODE(U.NAME,'PUBLIC',Null, U.NAME'.') L.NAMEchr(10)
'connect to ' L.USERID ' identified by '''
L.PASSWORD''' using ''' L.host ''''
chr(10)';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;



Please change the oracle directories appropriately:

select 'create or replace directory 'OWNER'.'DIRECTORY_NAME ' as ' ''''DIRECTORY_PATH''''';' from DBA_DIRECTORIES;

How to check database bit like 32bit or 64bit

SELECT distinct('This is a ' (length(addr)*4) '-bit database') "WordSize" FROM v$process;
select PLATFORM_ID, PLATFORM_NAME from v$database;


Check the PROFILE OPTIONS VALUE

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
‘10001′, ‘SITE’,
‘10002′, ‘APP’,
‘10003′, ‘RESP’,
‘10005′, ‘SERVER’,
‘10006′, ‘ORG’,
‘10004′, ‘USER’, ‘???’) "LEV",
decode(to_char(pov.level_id),
‘10001′, ”,
‘10002′, app.application_short_name,
‘10003′, rsp.responsibility_key,
‘10005′, svr.node_name,
‘10006′, org.name,
‘10004′, usr.user_name,
‘???’) "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE ‘%&&profile%’
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
AND decode(to_char(pov.level_id),
‘10001′, ”,
‘10002′, app.application_short_name,
‘10003′, rsp.responsibility_key,
‘10005′, svr.node_name,
‘10006′, org.name,
‘10004′, usr.user_name,
ORDER BY "NAME", pov.level_id, "VALUE";



***************


SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE 'ICX_FORMS_LAUNCHER'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value


*********

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE '%&&profile%'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
AND decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') LIKE '%&&username%'
ORDER BY "NAME", pov.level_id, "VALUE";

Find Concurrent manager for a particular concurrent request


select
c.user_name,
a.Request_id,
to_char(a.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start",
decode(a.status_code,
'A','Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'P', 'Scheduled',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') status_code,
b.user_concurrent_queue_name' - 'b.target_node "queue_name",
a.user_concurrent_program_name
from fnd_concurrent_worker_requests a,
fnd_concurrent_queues_vl b,
fnd_user c
where a.concurrent_queue_id=b.concurrent_queue_id
and a.phase_code='R'
and a.requested_by=c.user_id
and c.user_name='RMANI'
Order by 5;

 ****************************************************************

Oracle Applications Tablespace Model - OATM

Oracle Applications Tablespace Model - OATM

The Oracle Applications Tablespace Model was another long awaited feature that got introduced in 11.5.10.Prior to 11.5.10 by default each of the oracle applications product would have two dedicated tablespace holding the data element and the other for storing the index eg GLD (For General Ledger base tables) and GLX (For indexes relation to the General Ledger product).This easily resulted in some 300 odd tablespaces to manage apart from the system, temp and the rollback tablespaces.

In the new Oracle Applications Tablespace Model (OATM) all these product related tablespaces have been consolidated in two main tablespaces one for holding the base tables and the other for holding the related indexes. Apart from these two tablespace you have an additional ten tablespaces including system tablespace undo tablespace and the temporary tablespace. Thereby reducing the total number of tablespace in the OATM to twelve.

Apart from the obvious ease of management and administration with a reduced number of tablespace being involved the OATM also provides benefits like efficient space utilization. This is achieved by supporting locally managed tablespaces as opposed to the dictionary managed tablespace in the previous model.

OATM also supports uniform extent allocation and auto allocate extent management. In uniform extent management all the extents have the same size and result in less fragmentation. Auto allocate extent management allows the system to determine the extent sizes automatically.

OATM also provides additional benefits when implementing Real Application Clusters (RAC) in Oracle Applications.

Under the OATM the following twelve tablespaces are created as a default.
• APPS_TS_TX_DATA - This tablespace hold the translational tables of all Oracle Applications products. For example the GL_JE_HEADERS will be a part of APPS_TX_DATA.
• APPS_TS_TX_IDX - All the indexes on the product tables are kept under this tablespace.
• APPS_TS_SEED - The seeded data that is setup and reference data tables and indexes form this tablespace. For example your FND_DATABASES table would reside in the APPS_TS_SEED tablespace.
• APPS_TS_INTERFACE - All the interface tables are kept in this tablespace for example the GL_INTERFACE table.
• APPS_TS_SUMMARY - All objects that record summary information are grouped under this tablespace.
• APPS_TS_NOLOGGING - This tablespace contains the materialized views that are not used for summary purposes and other temporary
object that do not require redo log entries.
• APPS_TS_QUEUES - With the support for advanced queuing in Oracle Applications, the advanced queue tables and related objects form a part of this tablespace.
• APPS_TS_MEDIA - This tablespace holds multimedia objects like graphics sound recordings and spital data.
• APPS_TS_ARCHIVE - Tables that are obsolete in the current release of Oracle Applications 11i are stored here. These tables are preserved to maintain backward compatibility of custom programs or migration scripts.
• UNDO - The undo tablespace is used as automatic undo management is enabled by default in 11.5.10.This acts as a replacement to red log files.
• TEMP - The Temp tablespace is the default temporary tablespace for Oracle Applications.
• SYSTEM - This is the SYSTEM tablespace used by the Oracle Database.

For all new installation of 11.5.10 OATM is available as a default install. For prior applications system you can migrate your existing tables spaces to the oracle applications tablespace model.

For this purpose you have the Tablespace Migration Utility(TMU) which is available as a patch (3381489).

The TMU is a Perl based interactive utility that helps in migrating the oracle applications schemas to implement the OATM.

APPS DBA scripts

Find Workflow Notification Mailer

SQL> SELECT email_address,name, nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference)FROM wf_rolesWHERE name like upper('%xxx%');

SQL> select NOTIFICATION_ID,STATUS,MAIL_STATUS from WF_NOTIFICATIONS where TO_USER='UserName';

SQL> SELECT status,mail_status,MESSAGE_TYPE,MESSAGE_NAME,ITEM_KEY from WF_NOTIFICATIONS WHERE notification_id='2732013';

SQL> select mail_status,count(*) from WF_NOTIFICATIONS group by mail_status;

COLUMN CORRID FORMAT A55;
COLUMN STATE FORMAT A14;
SELECT CORRID,DECODE(STATE, 0, '0 = READY', 1, '1 = DELAYED', 2, '2 = PROCESSED', 3, '3 = EXCEPTION',TO_CHAR(STATE)) STATE, COUNT(*) COUNT
FROM WF_DEFERRED
GROUP BY CORRID, STATE;


SET LINES 150
COLUMN CORRID FORMAT A55;
COLUMN STATE FORMAT A14;
SELECT CORRID,DECODE(STATE, 0, '0 = READY', 1, '1 = DELAYED', 2, '2 = PROCESSED', 3, '3 =
EXCEPTION',TO_CHAR(STATE)) STATE, COUNT(*) COUNT
FROM WF_NOTIFICATION_OUT
GROUP BY CORRID, STATE;


COL COMPONENT_NAME FORMAT A60
COL COMPONENT_STATUS FORMAT A20
COL STARTUP_MODE FORMAT A20
SET LINES 120
SET PAGES 1000
SELECT COMPONENT_NAME, COMPONENT_STATUS, STARTUP_MODE FROM FND_SVC_COMPONENTS;


SET LINES 160
COL COMPONENT_NAME FORMAT A30
COL PARAMETER_NAME FORMAT A40
COL PARAMETER_VALUE FORMAT A40
SELECT A.COMPONENT_ID, A.COMPONENT_NAME, B.PARAMETER_ID, C.PARAMETER_NAME, B.PARAMETER_VALUE
FROM FND_SVC_COMPONENTS A, FND_SVC_COMP_PARAM_VALS B, FND_SVC_COMP_PARAMS_B C
WHERE A.COMPONENT_ID = B.COMPONENT_ID AND B.PARAMETER_ID = C.PARAMETER_ID
AND UPPER(A.COMPONENT_NAME) LIKE '%MAILER%'
ORDER BY A.COMPONENT_ID, A.COMPONENT_NAME, C.PARAMETER_NAME;

Diagnostic Script for Concurrent Manager

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - -

spool ccm.lst
prompt Step 1 Checking how many rows in FND_CONCURRENT_REQUEST.
select count(*) from fnd_concurrent_requests;
prompt
-----------------------------------------

prompt Step 2 Checking how many rows in FND_CONCURRENT_PROCESSES table.
select count(*) from fnd_concurrent_processes;
prompt
-----------------------------------------------


prompt Step 3 Checking sys.dual table which should have one and only one row.
select count(*) from sys.dual;

prompt If you have more than one row in sys.dual, please delete it

prompt sql> delete rownum from SYS.DUAL;Prompt rownum= the row number to delete
prompt
prompt
prompt
---------------------------------------------


prompt Step 4 Checking fnd_dual. There must be at lest one row:

select count(*) from fnd_dual;

prompt If there are no record selected,
prompt Update fnd_dual table to have at lest one record
prompt
----------------------------------------------

prompt Step 5 Checking the Internal Manager queue name "FNDICM" which should be=1

select concurrent_queue_id from fnd_concurrent_queues
where concurrent_queue_name='FNDICM';

prompt
----------------------------------------------

prompt Step 6 Checking for Active processes under the Internal Manager queue
prompt in fnd_concurrent_proceses table:
prompt

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='FNDICM'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
-----------------------------------------
prompt Step 7 Checking for Active processes under the Standard Manager queueprompt in fnd_concurrent_proceses table:
prompt

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='STANDARD'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
------------------------------------------
prompt Step 8 Checking for Active processes under the Conflict Manager queue
prompt in fnd_concurrent_proceses table:
prompt

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='FNDCRM'
and b.process_status_code='A'
order by b.process_status_code;
prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
---------------------------------------------------
prompt Step 9 Checking Actual and Target Processes for Internal Manager:

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDICM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
--------------------------------------------------------

prompt Step 10 Checking Actual and Target Processes for the Standard Manager:

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='STANDARD';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------
prompt Step 11 Checking Actual and Target Processes for Conflict Resolution Manager:

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDCRM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------

Prompt Step 12 Checking if the control_code set to 'N':
select control_code from fnd_concurrent_queues
where control_code='N';
prompt
prompt If any rows selected, please update the table fnd_concurrent_queues:prompt Update fnd_concurrent_queues set control_code = nullprompt where control_code ='N';PROMPT Update fnd_concurrent_queues set target_node = null;PROMPT commit;
prompt
prompt
--------------------------------

PROMPT Step 13 Checking terminated processes:PROMPT
select count (*) from fnd_concurrent_requests
where status_code='T';prompt
prompt If you have terminated processes run the following sql statement:
prompt
prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'T';
prompt
------------------------------------------


prompt Step 14 Checking pending requests:
select count(*) from fnd_concurrent_requests
where status_code='P';
prompt If any rows selected please run the following sql statement:

prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'P';
prompt
------------------------------------------------------
prompt Step 15 Checking Running processes:prompt
select count (*) from fnd_concurrent_requests
where status_code='R';
prompt
prompt If you have Running processes run the following sql statement
prompt SQL> Update fnd_concurrent_requestsprompt set status_code = 'E', phase_code = 'C'prompt where status_code = 'R';
prompt
------------------------------------------

prompt Step 16 Checking the PMON method, which should be set to LOCK:prompt
select profile_option_id , profile_option_value
from FND_PROFILE_OPTION_VALUES
where profile_option_id= (select profile_option_id
from FND_PROFILE_OPTIONS
where profile_option_name='CONC_PMON_METHOD');
prompt
prompt If the PROFILE_OPTION_VALUE was't LOCK please
prompt Reset PMON to LOCK by running afimpmon.sql script(The manager should be down)

prompt 1-At UNIX command prompt:

prompt 2-cd $FND_TOP/sql

prompt 3-Log into SQLPLUS as apps/
prompt SQL> @afimpmon.sqlprompt prompt1:dual
prompt prompt2:LOCK (LOCK MUST BE ALL UPPERCASE)

prompt For Oracle Applications Release 11.5, when you check the PMON
prompt Method you may get no rows selected which is normal,
prompt because in apps 11.5 the Pmon Method is hard coded to Lock at
prompt the Operating System level.
prompt
prompt
-------------------------------------------------------

prompt Step-17 Checking how many FNDLIBR processes are running:prompt -For Unix :From unix command prompt $ ps -ef grep -i fndlibr
prompt If you have any FNDLIBR processes running,please kill them before
prompt starting or shuting down the internal manager
prompt
prompt
prompt -For NT, through Task Manager, check the entries under the Processes tab
for FNDLIBR.exe processes.
prompt If there are any, Highlight and click [End Process] button to kill processes

prompt
----------------------------------------------------------

prompt Step-18 Checking how many "FND_%"invalid objects:
select substr(owner,1, 12) owner, substr(object_type,1,12) type,
substr(status,1,8) status, substr(object_name, 1, 25) name
from dba_objects
where object_name like 'FND_%'
and status='INVALID';

prompt If you have any invalied objects please see note#113947.1 via Metalink

prompt
--------------------------------------------------------------

prompt Step-19-How to find the PID in the O/S for request_id:prompt If you do not like to check this enter any number then click Enter to Exit

select r.request_id, p.os_process_id
from FND_CONCURRENT_REQUESTS r,FND_CONCURRENT_PROCESSES p
where r.controlling_manager = p.concurrent_process_id
and request_id=&request_id;

prompt
prompt Please upload the "ccm.lst" output to Support, Thanks.
prompt
spool off
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

Oracle Certification Path

Certification Path

STEP 1 - PRIOR CERTIFICATION

Oracle 10g Database Administrator OCP

or

Oracle 11g Database Administrator OCP

STEP 2 - PASS THIS EXAM

Oracle E-Business Suite R12: Install, Patch and Maintain Applications 1Z0-238

Oracle E-Business Suite R12 Applications Database Administrator Certified Professional

Oracle EBS R12: Install, Patch and Maintain Applications

Exam Number: 1Z0-238

Associated Certifications: Oracle EBS R12 Applications Database Administrator Certified Professional

Exam Price: Rs 10418.00

More on exam pricing

Duration:      90 minutes

Number of Questions: 75

              Passing Score: 63%

Preparing for your Installation:-

Advantages of Rapid Install

Technology Stack Components

Pre-requisites required (Platform Hardware and Softwares)

Staging Software

How Installation works

Performing an Installation:-

Types of Installation (Standard/Typical/Express)

Validation of Parameters passed

Restart / Troubleshooting fresh Install

Rapid Install progress monitoring

Finishing your Installation:-

Explain required post-installation tasks

Describe the client software configuration process

Describe installation-specific post-installation tasks

Sanity Checks after successful installation

Oracle Applications Components:-

Identify and describe the high-level technology stack components that make up the desktop, application and database tiers.

Understand how Oracle Applications uses the various technology stack components.

Identify the various oracle homes used by the Oracle Applications

Identify and describe the role of the various Applications Technology layer products

Identify the key changes in the R12 applications technology stack

Environment Files, Control Scripts and Languages:-

Describe the Oracle Applications main environment file and its key parameters

Identify other important environment files

Identify application tier server control scripts

Explain how languages are supported by Oracle Applications

Oracle Applications File System:-

Describe the Oracle Applications file system (both database tier and application tier)

Describe the product directory structure

Identify the important product directories (AD, AU)

Describe the role of the following directories:  APPL_TOP, COMMON_TOP, INST_TOP

List the directories that are new with Release 12

Oracle Applications Database:-

Describe the objects and schemas that exist in the Oracle Applications database

Describe the purpose of the APPS schema and Additional Schema

Impending Multiple Organization and Reporting Currencies

Overview of Database Features

Database Monitoring Features

Database Performance Features

Database Scalability and Business Intelligence Features

How to Start and Shutdown the database server and listener

Advanced Configuration Options:-

Understand the use of a shared application tier file system in Oracle Applications

Explain the benefits of using a staged APPL_TOP

Identify the main load balancing options and their key features

Understand the major factors in efficient network design for Oracle Applications

R12 Use Oracle Applications AD Utilities:-

Configuration and environment files

Identify the AD utilities

Understand the usage of command line arguments and flags

Describe parallel processing

Identify the location and usage of log and restart files. (adadmin.log and adwor0xxx.log)

Distributed AD.

AD Administration: Maintain Applications Files Tasks

Relink Oracle Applications programs

Create the main Applications environment file

Identify the appropriate servers for file system administration tasks

Maintain Snapshot Information

AD Administration: Compile/Reload Database Entities Tasks

Using Compile/Reload Database Entities Tasks

Alternative methods to compile/generate & reload objects

AD Administration:  Non-interactive Operation and Maintenance Mode

Create a defaults file and running AD Administration in non-interactive mode

Enable and Disable maintenance mode

Benefits of Running AD Administration in Non-interactive Mode

Defaults file available menu options

Special Utilities

Overview of AD Controller

Understand the worker status

Troubleshoot worker processes

Restart and shutdown a worker

AD Relink Utility

Describe the Upgrade Process

Review Worker Status & It's Flow

Autoconfig

Describe the Applications Context

Describe Autoconfig

Identify Scripts used by Autoconfig

Run Autoconfig to configure an Oracle Application System

Update Applications Context Parameters

Restore Applications Context Parameters

Roll Back an Autocofig Session

Run Autoconfig in Test Mode

Troubleshooting / Dependency while running Autoconfig

Benefits of Context Files

Autoconfig in OAM

AD Administration: Maintain Database Entities Tasks

Using Different Options Maintain Database entities task

Dual Table

License Manager

License Manager Utility

Register products

Register country-specific functionalities

Register languages

Identify licensed products and languages

License Manager Summary Report

Reporting Utilities

Overview of Command Line Reporting Utilities

AD Configuration Report

AD File Identification

AD Job Timing Report

Running AD Configuration

Configuration Utilities

Install a new off-cycle product

How to use AD Splicer

Convert the character set of Applications files

Patching and the AutoPatch Process

Overview of patches (includes the purpose, types of patches, naming convention)

Describe the elements of a patch

Distinguish between a patch, minipack, maintenance pack and consolidated updates

Describe how a patch is created

Overview of Autopatch and its features/ Options

Explain the steps AutoPatch goes through to apply a patch

How to apply a Patch (both on single node and multi node configuration

Downloading a Patch

Patch Wizard:-

Describe and access Patch wizard

Download the Patch Information Bundle

Define the stage directory

Define patch filters

Submit and view Patch Wizard requests

Analyze Patch Wizard results

Perform Patch Impact analysis

Running Auto Patch:-

Perform setup tasks prior to running AutoPatch

Register products

Understand and answer the autopatch prompts

Identify the location and usage of log files

Perform additional tasks after running AutoPatch

Troubleshoot autopatch failure and restart autopatch

OAM Timing Reports:-

Describe the features of OAM Timing Reports

Identify the key information in the Timing Detail reports

Track an in-progress patch session

Other Patching Topics:-

Describe the Autopatch modes

Run autopatch in different modes

Use AutoPatch command line options

Apply Java Patches

Merge patch drivers from multiple patches

Applied Patches Information:-

Describe the features of OAM Applied Patches Information

Explain the relationship between the autopatch mode and OAM applied patch information

Identify the Applied Patches reports

Cloning Oracle Applications:-

Describe situations that may require cloning

Explain the prerequisites for rapid Clone at OS Level (Software)

Explain the prerequisites for rapid Clone at Application level

Prepare the Source System Database Tier for Cloning

Prepare the Source System Application Tier for Cloning

Copy the Source System to the Target System

Configure the Target System

Check for and Update Instance-Specific Settings

Perform the Finishing Tasks

Advanced Cloning Options:-

Refresh a cloned target system

Clone a single-node system to a multi-node system

Clone a multi-node system to a multi-node system

Add a node to an existing system

Reduce the number of nodes on a multi-node system

AD Administration: Generate Applications Files Tasks:-

Setting up Environment

Generate message, form, report, graphics, and product jar files

Generate files options.

****************************************************************************