TO CHECK THE LOCKS FOR A PARTICULAR CONCURRENT PROGRAM OR REQUEST

select * from v$session where process in(select os_process_id from fnd_concurrent_requests where request_id='<Req Id>')

from this u will get SID
take that sid and find object_id's from below command
select * from v$locked_objects where session_id='SID'
now take all the object_id's and check any other session is locking those object_ids apart from the above SID...
select * from v$locked_objects where object_id =''
if any session is locking check the status of that session from v$session.
if that is inactive kill that session ... to free up the resource

TO CHECK THE NO.OF.USERS LOGGED IN

select count (*) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);

TO CHECK THE TIMELINE OF THE REQUEST :

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');

TO COPY A FILE WITH THE SAME TIMESTAMP:

cp -p file_name file_name1

HOW TO GET SQL_ID FOR A PARTICULAR REQUEST

col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
select /*+ ordered */
fcr.request_id,
fcp.user_concurrent_program_name
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.SQL_ID
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    request_id='&req_id';

HISTORY OF CONCURRENT REQUESTS WHICH ARE ERROR OUT

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
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 a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;

To Check session locks

set pages 1000;
set lines 160;
set feedback off;
col BLOCKER for a20
col BLOCKEE for a20
col module for a20
col program for a20
select
       (select username from gv$session where sid=a.sid and inst_id=a.inst_id) blocker,a.sid,
               (select module from gv$session where sid=a.sid and inst_id=a.inst_id) "Program",
                (select inst_id from gv$session where sid=a.sid and inst_id=a.inst_id) "Instance",
                 (select status from gv$session where sid=a.sid and inst_id=a.inst_id) "Status",
                  (select last_call_et/3600 from gv$session where sid=a.sid and inst_id=a.inst_id) "Inactive Since",
                    'is blocking',
       (select username from gv$session where sid=b.sid and inst_id=b.inst_id) blockee,b.sid,
               (select module from gv$session where sid=b.sid and inst_id=b.inst_id) "Program",
                (select inst_id from gv$session where sid=b.sid and inst_id=b.inst_id) "Instance",
                 (select status from gv$session where sid=b.sid and inst_id=b.inst_id) "Status" ,
                  (select last_call_et/3600 from gv$session where sid=b.sid and inst_id=b.inst_id) "Inactive Since"
from gv$lock a,gv$lock b
where a.block=1
and b.request > 0
and a.id1=b.id1
and a.id2=b.id2
/

Temp tablespace space check.

set pages 1000;
set lines 120;
set feedback off;
SELECT   A.tablespace_name tablespace, D.mb_total,
        SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
        D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
        (
        SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
        FROM     v$tablespace B, v$tempfile C
        WHERE    B.ts#= C.ts#
        GROUP BY B.name, C.block_size
        ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total
/

Tablespace Space Check

set pages 1000;
set lines 120;
set feedback off;
col totsiz  format 999,999,990 justify c heading 'Total|(MB)'
col avasiz  format 999,999,990 justify c heading 'Free|(MB)'
col pctusd  format         990 justify c heading 'Pct|Used'
col maxsiz  format 999,999,990 justify c heading 'MaxExtend|(MB)'
col "Total Free" format 999,999,990 justify c heading 'MaxFree|(MB)'
col "Actual pctfree" format         990 justify c heading 'MaxPct|Free'
comp sum of totsiz avasiz "Total Free" on report
break on report

select
 total.tablespace_name,
 round(total.bytes/1024/1024)                            totsiz,
 round(nvl(sum(free.bytes)/1024/1024,0))                 avasiz,
 round((1-nvl(sum(free.bytes),0)/total.bytes)*100)  pctusd ,
 round(decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024)                           maxsiz,
 round(nvl(sum(free.bytes)/1024/1024,0) + (decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024 - total.bytes/1024/1024)) "Total free",
 round((nvl(sum(free.bytes)/1024/1024,0) + (decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024 - total.bytes/1024/1024))*100/(decode(total.maxbytes,0,total.bytes,total.maxbytes)/1024/1024)) "Actual pctfree"
from
 (select sum(bytes) bytes,tablespace_name,sum(decode(maxbytes,0,bytes,maxbytes)) maxbytes from dba_data_files  group by tablespace_name)  total,
 (select sum(bytes) bytes,tablespace_name from dba_free_space  group by tablespace_name ) free where
 total.tablespace_name = free.tablespace_name(+)
group by
 total.tablespace_name,
 total.bytes,
 decode(total.maxbytes,0,total.bytes,total.maxbytes)
/

TO FIND THE URL IN R-12

DO IN MT-Middle Tiear NODE....

$ grep -i login $CONTEXT_FILE

To install RPM Packages in Linux

rpm -ivh to intall a rpm package
syntax
[root@nettech root]#rpm -ivh packagename.rpm
rpm stands for 'redhat package manager'
-i stands for install
-v stands for verbose mode
-h stands for with hash sign(#)

BOUNCE APACHE AND CLEAR CACHE ON R-12

1. Stop Apache

2. Clear cache

$ cd $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/
$ ls
application-deployments  config  persistence  tldcache
$ cd persistence
$ ls
oacore_default_group_1  oacore_default_group_2
$ cd ..
$ ls
application-deployments  config  persistence  tldcache
bash-3.00$ mv persistence persistence.1
bash-3.00$ mkdir -m 755 persistence

3. Start Apache

Script to find who has changed the sysadmin Password

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

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

To check the Size of RAM in Unix

HP-UX

swapinfo -tm
Linux

free -m
free -g

AIX

lsdev -C|grep mem

Solaris

prtconf|grep -i mem

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

set pages 1000
set line 132
set head on
select  r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%'
and  r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Script to disable all scheduled requests - After cloning

update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and (status_code = 'I' OR status_code = 'Q');
and requested_start_date >= SYSDATE
and hold_flag = 'N';