SCRIPT TO CHECK FOR AVAILABLE/USED SPACE-ASM
========================================

set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from
v$asm_diskgroup;

SCRIPT TO DELETE ARCHIVELOGS FROM ASM STORAGE
==============================================
#!/bin/bash
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
asmcmd -p « EOF
rm -rf ORADATA/AVIS10/*.arc
EOF

MONITOR SPACE USED IN ASM DISK GROUPS
======================================
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'

break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/

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

Unable to start MT services using the script adstrtal.sh

Issue

$ adstrtal.sh
You are running adstrtal.sh version 120.13.12000000.4
Enter the APPS username: apps
Enter the APPS password:
adstrtal.sh: Database connection could not be established. Either the database is down or the APPS
credentials supplied are wrong.
USAGE:  adstrtal.sh <appsusername/appspassword>
adstrtal.sh: exiting with status 1

Solution:

Workaround
-->apps password is correct and DB is up and running,but wrong entries in adstrtall.sh
-->Added a hash to the following :

#sqlplus -s /nolog > /dev/null 2>&1 <<EOF
#whenever sqlerror exit failure
#connect apps/apps
#exit success
#EOF

-->Now MT services are started properly

How to check whether the update/delete/drop statement is running or not Example

The below script is running from long time,so by using the below steps we can check the progress of the statement

update mtl_material_transactions
set costed_flag = 'N',
transaction_group_id = NULL,
transaction_set_id = NULL
where costed_flag = 'E' or costed_flag = 'N';

On DB node run the below command to check the session SID

col module for a20
select inst_id,sid,module,process,status from gv$session where  status ='ACTIVE'  and username ='APPS' ;

Example

SQL> col module for a20
select inst_id,sid,module,process,status from gv$session where  status ='ACTIVE'  and username ='APPS' ;SQL>

INST_ID        SID    MODULE               PROCESS      STATUS

1             2046    FNDGSCST                   5564         ACTIVE
1             2073    WSHINTERFACE         17385        ACTIVE
1             2096    SQL*Plus                       31927        ACTIVE

1       2096 SQL*Plus             31927        ACTIVE  ==>this is the session for the running statement

Execute the below command to confirm the running statement with the SID from above command

SQL> select SQL_TEXT from v$sqltext where ADDRESS in (select SQL_ADDRESS from v$session where SID=2096) order by piece;

SQL_TEXT
----------------------------------------------------------------
update mtl_material_transactions set costed_flag = 'N', transact
ion_group_id = NULL, transaction_set_id = NULL where costed_flag
= 'E' or costed_flag = 'N'

SQL>

Execute the below command to check the session is active or not

REM checking Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

Example

SQL> REM checking Timing details, Client PID of associated oracle SID
REM ============================================================
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ........................
SQL> Details of SID / SPID / Client PID
SQL> ==================================
SQL>    ......................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMAN  AME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));  
Enter value for sid: 2096
Enter value for spid:
Enter value for clientpid:

Session  Id.............................................: 2096
Serial Num..............................................: 26063
User Name ..............................................: APPS
Session Status .........................................: ACTIVE
Client Process Id on Client Machine ....................: *31927*
Server Process ID ......................................: 3498
Sql_Address ............................................: 000000023C9B9FC0
Sql_hash_value .........................................: 3476221953
Schema Name ..... ......................................: APPS
Program  ...............................................: sqlplus@test15 (TNS V1-V3)
Module .................................................: SQL*Plus
Action .................................................:
Terminal ...............................................: pts/8
Client Machine .........................................: test15
LAST_CALL_ET ...........................................: 2689
S.LAST_CALL_ET/3600 ....................................: .746944444444444444444444444444444444444
SQL>

Session Status is Active

How to findout all locked objects?

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM   dba_objects a,
v$locked_object b
WHERE  a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON