Sujeet APPS DBA

Enabling and Disabling Maintenance Mode

Maintenance mode is Enabled or Disabled from adadmin.
When you Enable or Disable 'Maintenance Mode', adadmin will execute the script:
$AD_TOP/patch/115/sql/adsetmmd.sql

sending the parameter 'ENABLE' or 'DISABLE' :

sqlplus <APPS_Schema name>/<APPS Password>@adsetmmd.sql ENABLE | DISABLE
ENABLE - Enable Maintenance Mode .
DISABLE - Disable Maintenance Mode.

When adsetmmd.sql runs, it sets the Profile Option 'Applications Maintenance Mode'
(APPS_MAINTENANCE_MODE) to 'MAINT' to Enable 'Maintenance Mode' and to 'NORMAL' to Disable it.

Query to monitor sql consuming maximum resources

SQL> select * from (select s.sid,s.serial#,s.osuser,s.username,p.spid,w.wait_time,w.SECONDS_IN_WAIT,w.event,w.state from
(select sid,wait_time,SECONDS_IN_WAIT,event,state from v$session_wait) w,
(select sid,serial#,osuser,username,paddr from v$session) s,(select spid,addr from v$process)p
where s.sid=w.sid and s.paddr=p.addr order by w.SECONDS_IN_WAIT desc) where rownum <11;

SID SERIAL# OSUSER USERNAME SPID WAIT_TIME SECONDS_IN_WAIT EVENT STATE

398 1 orachintels 10023 0 16911 VKTM Logical Idle Wait WAITING

You can use the v$sql view or v$active_session_history to get required results

SQL> SELECT * FROM

(SELECT

sql_fulltext,

sql_id,

child_number,

disk_reads,

executions,

first_load_time,

last_load_time

FROM v$sql

ORDER BY elapsed_time DESC)

WHERE ROWNUM < 10;

SQL_FULLTEXT

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

SQL_ID CHILD_NUMBER DISK_READS EXECUTIONS FIRST_LOAD_TIME

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

LAST_LOAD_TIME

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

begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;

05s9358mm6vrr 0 71995 1 2015-04-04/10:38:55

2015-04-04/10:38:55

select max(bytes) from dba_segments

43w0r9122v7jm 0 38355 1 2015-04-04/10:39:15

2015-04-04/10:39:15

Top 10 CPU Consumers in last 5 Min.

SQL> select * from

(

select session_id, session_serial#, count(*)

from v$active_session_history

where session_state= 'ON CPU' and

sample_time > sysdate - interval '5' minute

group by session_id, session_serial#

order by count(*) desc

)

where rownum <= 10;

SESSION_ID SESSION_SERIAL# COUNT(*)

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

282 5019 2

356 179 1

396 1 1

296 32812 1

301 29 1

What did that SID do?

SQL> select distinct sql_id, session_serial# from v$active_session_history

where sample_time >  sysdate - interval '5' minute

and session_id=&sid;

shows current statements for active sessions

select   p.username   pu , s.username su, s.status stat, s.sid ssid,   s.serial# sser , substr(p.spid,1,8)   spid, substr(sa.sql_text,1,2000) txt
from     v$process p , v$session s, v$sqlarea sa where    p.addr = s.paddr and s.username is not null and s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+)
and      s.status   = 'ACTIVE' order by 1,2,7 ;

PU SU STAT SSID SSER

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

SPID

--------

TXT

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

orachintels APPS ACTIVE 320 43

21475

BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;

orachintels APPS ACTIVE 305 5

21761

BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;

Find bind variable values used by SQL's

select s.sid,
s.username,
sq.sql_text,
s.sql_hash_value,
s.sql_id,
s.sql_child_number,
spc.name,
spc.value_string,
last_captured
from v$sql_bind_capture spc, v$session s,v$sql sq
where s.sql_hash_value = spc.hash_value
and s.sql_address = spc.address
and sq.sql_id=s.sql_id
and spc.was_captured='YES'
and s.type<>'BACKGROUND'
and s.status='ACTIVE';

Identifies Segments That Are Getting Close to Running Out Of Contiguous Free Space

select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest
from dba_segments s, dba_free_space f
where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent
having next_extent*2 >max(f.bytes);

How to check if oracle instance is leaking memory

Use session statistics (to measure)

The Oracle server maintains statistics that record the PGA and UGA memory consumption on a per session basis. If you suspect a memory leak then use SQL similar to that presented below to verify the amount of memory in use and re-issue it several times and monitor to confirm that memory is indeed growing.

Select sid, substr(name,1,30), value , value/(1024*1024) "MB"
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and name like '%a memory'
order by sid, name;

select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", c.serial#,c.status,c.username,c.schemaname,
c.osuser,c.machine,c.terminal,c.program,
c.module,state,logon_time
from v$statname a, v$sesstat b,v$session c
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=c.sid
and osuser!='oracle'
order by status,MB desc,sid, name;

If you want to the SQL also join v$sqlarea table-
This will give you how much memory is utilized by package/procedure and a simple/complex
SQL text

select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", s.serial#,s.sid ssid, s.status,s.username,s.schemaname,
s.osuser,s.machine,s.terminal,s.program,
s.module,state,logon_time,substr(p.spid,1,8) spid, substr(sa.sql_text,1,2000) txt
from v$statname a, v$sesstat b,v$session s,
v$process p ,v$sqlarea sa
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=s.sid
and osuser!='oracle'
and p.addr = s.paddr
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
order by status,MB desc,sid, name;

FIND LOCKED TABLES AND KILL SESSION LOCKING TABLES

select oracle_username || ' (' || s.osuser || ')' username , s.sid || ',' || s.serial# sess_id , owner || '.' || object_name object , object_type ,decode( l.block ,0, 'Not Blocking',1, 'Blocking', 2, 'Global') status , decode(v.locked_mode,0, 'None' , 1, 'Null' ,2, 'Row-S (SS)' , 3, 'Row-X (SX)' , 4, 'Share',5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v   , dba_objects d   , v$lock l   , v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
and OBJECT_name in ( 'TQUEUEDSANTERAINCCDR','TQUEUEDSANTERACDR')
order by oracle_username   , session_id ;

select   p.username   pu , s.username su, s.status stat, s.sid ssid,   s.serial# sser , substr(p.spid,1,8)   spid, substr(sa.sql_text,1,2000) txt
from     v$process p , v$session s, v$sqlarea sa where    p.addr = s.paddr and s.username is not null and s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+)
and s.sid in (select s.sid ifrom
from       v$locked_object v   , dba_objects d   , v$lock l   , v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
and OBJECT_name in ( 'TQUEUEDSANTERAINCCDR','TQUEUEDSANTERACDR')
)
order by 1,2,7 ;

Shows waiting sessions and the events they await

select sw.sid sid, p.spid  spid, s.username username
, s.osuser   osuser, sw.event event, s.machine machine
, s.program program, decode(sw.event,'db file sequential read', sw.p3, 'db file scattered read', sw.p3, null) blocks
from    v$session_wait sw,v$session  s, v$process p
where   s.paddr = p.addr
and     event     not in ('pipe get','client message')
and     sw.sid = s.sid

Identify which object is being waited for:

SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob WHERE kglhdadr='value of p1raw'

Who is pinning the object?
SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status,
kglpnmod "Mode", kglpnreq "Req"
FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND
kglpnhdl='value of p1raw';

Use the following script to generate SQL to kill off multiple sessions all waiting for the same library cache pin.

SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='value of p1raw';

Tablespace and File size

SELECT /*+ ordered */

d.tablespace_name tablespace

, d.file_name filename

, d.bytes filesize

, d.autoextensible autoextensible

, d.increment_by * e.value increment_by

, d.maxbytes maxbytes

FROM

sys.dba_data_files d

, v$datafile v

, (SELECT value

FROM v$parameter

WHERE name = 'db_block_size') e

WHERE

(d.file_name = v.name)

UNION

SELECT

d.tablespace_name tablespace

, d.file_name filename

, d.bytes filesize

, d.autoextensible autoextensible

, d.increment_by * e.value increment_by

, d.maxbytes maxbytes

FROM

sys.dba_temp_files d

, (SELECT value

FROM v$parameter

WHERE name = 'db_block_size') e

UNION

SELECT

'[ ONLINE REDO LOG ]'

, a.member

, b.bytes

, null

, TO_NUMBER(null)

FROM

v$logfile a

, v$log b

WHERE

a.group# = b.group#

UNION

SELECT

'[ CONTROL FILE ]'

, a.name

, TO_NUMBER(null)

, null

, TO_NUMBER(null)

FROM

v$controlfile a

ORDER BY 1,2;

FIND LOCKED SESSIONS

set lines 100 pages 999

col username format a20

col sess_id format a10

col object format a25

col mode_held format a10

select oracle_username || ' (' || s.osuser || ')' username

, s.sid || ',' || s.serial# sess_id

, owner || '.' || object_name object

, object_type

, decode( l.block

, 0, 'Not Blocking'

, 1, 'Blocking'

, 2, 'Global') status

, decode(v.locked_mode

, 0, 'None'

, 1, 'Null'

, 2, 'Row-S (SS)'

, 3, 'Row-X (SX)'

, 4, 'Share'

, 5, 'S/Row-X (SSX)'

, 6, 'Exclusive', TO_CHAR(lmode)) mode_held

from v$locked_object v

, dba_objects d

, v$lock l

, v$session s

where v.object_id = d.object_id

and v.object_id = l.id1

and v.session_id = s.sid

order by oracle_username

, session_id

Locked Object QUERY

set linesize 230
set pages 100
col username       format a20
col sess_id        format a10
col object format a45
col mode_held      format a10
select     oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, substr(p.spid,1,8)   spid
, decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
, decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
, dba_objects d
, v$lock l
, v$session s
, v$process p
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
and        p.addr = s.paddr
order by oracle_username
, session_id
/

select * from (select opname, target, sofar, totalwork,units, elapsed_seconds, message from v$session_longops
where sid = <sid> and serial# = <serialno> order by start_time desc)where rownum <=1;

select s.sid, s.serial#, p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.sid in (select SESSION_ID from v$locked_object);

select * from (select opname, target, sofar, totalwork,units, elapsed_seconds, message from v$session_longops order by start_time desc)
where rownum <=1;

Identify Segments That Are Getting Close To Their Max-Extent Values

select owner,tablespace_name,segment_name,bytes,extents,

max_extents from dba_segments where extents*2 > max_extents;

Library cache wating

select sid, event, p1raw, seconds_in_wait,wait_time
from v$session_wait
where event = 'library cache pin'
and state = 'WAITING';

SID,EVENT,P1RAW,SECONDS_IN_WAIT,WAIT_TIME
4962,library cache pin,070000031733BDF8,51,0

Identify which object is being waited for:
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob WHERE kglhdadr='070000031733BDF8';

Who is pinning the object?

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status,
kglpnmod "Mode", kglpnreq "Req"
FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND
kglpnhdl='070000031733BDF8';

Use the following script to generate SQL to kill off multiple sessions all waiting for the same library cache pin.

SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='070000031733BDF8'

what sessions (and what SQL statements) are using
sorting resources

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1))
sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128

select oracle_username || ' (' || s.osuser || ')' username , s.sid || ',' || s.serial# sess_id , owner || '.' || object_name object , object_type ,decode( l.block ,0, 'Not Blocking',1, 'Blocking', 2, 'Global') status , decode(v.locked_mode,0, 'None' , 1, 'Null' ,2, 'Row-S (SS)' , 3, 'Row-X (SX)' , 4, 'Share',5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v   , dba_objects d   , v$lock l   , v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username   , session_id ;

Display database locks and latches (with tables names)

select s.sid, s.serial#, decode(s.process, null,decode(substr(p.username,1,1), '?',upper(s.osuser), p.username),decode(p.username, 'ORACUSR ', upper(s.osuser), s.process)) process,
       nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username, decode(s.terminal, null, rtrim(p.terminal, chr(0)), upper(s.terminal)) terminal,decode(l.type,
                               'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ', 'UL', 'PLS USR LOCK', 'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE', 'CI', 'CROSS INST F', 'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ', 'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP', 'DR', 'RECO LOCK   ', 'DX', 'DISTRIB TRAN', 'FS', 'FILE SET    ', 'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE', 'IR', 'INSTCE RECVR', 'IS', 'GET STATE   ', 'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ', 'LS', 'LOG SWITCH ',
                      'MM', 'MOUNT DEF   ', 'MR', 'MEDIA RECVRY', 'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT', 'RT', 'REDO THREAD ', 'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ', 'SM', 'SMON LOCK   ', 'SN', 'SEQNO INSTCE', 'SQ', 'SEQNO ENQ   ', 'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ', 'TD', 'DLL ENQ     ', 'TE', 'EXTEND SEG ', 'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
                      'UN', 'USER NAME   ', 'WL', 'WRITE REDO ', 'TYPE='||l.type) type, decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX', 6, 'X', to_char(l.lmode) ) lmode, decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest, decode(l.type, 'MR', decode(u.name, null,'DICTIONARY OBJECT', u.name||'.'||o.name),
                      'TD', u.name||'.'||o.name, 'TM', u.name||'.'||o.name, 'RW', 'FILE#='||substr(l.id1,1,3)||   ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2, 'WL', 'REDO LOG FILE#='||l.id1, 'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'), 'ID1='||l.id1||' ID2='||l.id2) object
from   sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,sys.v_$process p
where s.paddr = p.addr(+)
and l.sid    = s.sid
and l.id1    = o.obj#(+)
and o.owner# = u.user#(+)
and l.type   <> 'MR'
UNION ALL /*** LATCH HOLDERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
       'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from   sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where h.pid = p.pid
and p.addr = s.paddr
UNION ALL    /*** LATCH WAITERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
       'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from   sys.v_$session s, sys.v_$process p, sys.v_$latch l
where latchwait is not null
and p.addr      = s.paddr
and p.latchwait = l.addr

How to check last update Password date?

SQL> select oracle_username, last_update_date, enabled_flag, read_only_flag from fnd_oracle_userid where oracle_username like 'AP%' order by 1;

ORACLE_USERNAME LAST_UPDA E R

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

AP 14-MAY-00 N A

APPLSYS 14-MAY-00 N E

APPLSYSPUB 14-MAY-00 N C

APPS 14-MAY-00 N U

How to check Database Profile status?

SQL> select profile from dba_users where username='APPS';

PROFILE

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

DEFAULT

How to know how many users connected to Oracle Applications

Use this SQL statement to count number of concurrent_users connected to Oracle apps:

SQL> select count(distinct d.user_name) 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');

COUNT(DISTINCTD.USER_NAME)

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

0

Use this SQL statement to count number of users connected to Oracle Apps in the past 1 hour.

SQL> select count(distinct user_id)"users" from icx_sessions where last_connect > sysdate-1/24 and user_id !='-1';

users

----------

2

Use this SQL statement to get number of users connected to Oracle Apps in the past 1 day.

SQL> select count(distinct user_id)"users" from icx_sessions where last_connect > sysdate-1 and user_id !='-1';

users

----------

3

SQL> select count(distinct user_id)"users" from icx_sessions where last_connect > sysdate-9 and user_id !='-1';

users

----------

3

Use this SQL statement to get number of users connected to Oracle Apps in the last 15 minutes.

SQL> select limit_time, limit_connects, to_char(last_connect,'DD-MON-RR HH:MI:SS') "Last connection time", user_id, disabled_flag from icx_sessions where last_connect > sysdate-1/96;

LIMIT_TIME LIMIT_CONNECTS Last connection ti USER_ID D

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

4 1000 27-MAR-15 06:29:12 6 N

4 1000 27-MAR-15 06:17:33 1131 Y

4 1000 27-MAR-15 06:25:54 1110 Y

4 1000 27-MAR-15 06:28:07 1131 N

Find out which users are logged in Oracle Applications

SQL> select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS') from apps.fnd_user where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

How can i count the total users connected to my R12 application.

[appl@sujeet ~]$ ps -ef | grep frmweb | wc -l

1

CHECKING APPLICATION USER password Validity

SQL> select fnd_web_sec.validate_login('sysadmin','welcome') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','WELCOME')

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

N

SQL> select fnd_web_sec.validate_login('sysadmin','chinlive123') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','CHINLIVE123')

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

Y

You can check the number of active users currently logged into the application using the following query.

select to_char(START_TIME,’DD-MON-YYYY’) Login_Time, count(*) cnt
from fnd_logins where START_TIME > (select to_date(’25-JAN-2008 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) from dual)
and login_type is not null
and end_time is null
group by to_char(START_TIME,’DD-MON-YYYY’);

LOGIN_TIME               CNT
—————– ———-
26-JAN-2008               26
25-JAN-2008              132
28-JAN-2008               13
27-JAN-2008               34

Also you can check the number of user session for the application using ICX_SESSIONS table.

Use below query for checking the number of user sessions.

select ((select sysdate from dual)),(select ‘ 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) from dual;

((SELECTSYSDATE (SELECT’USERSESSIONS:’||COUNT(DISTINCTSESSION_ID)HOW_MANY
————— ———————————————————
28-JAN-08 user sessions : 9

SQL> select limit_time, limit_connects, to_char(last_connect,'DD-MON-RR HH:MI:SS') "Last connection time", user_id, disabled_flag from icx_sessions where last_connect > sysdate-9;

LIMIT_TIME LIMIT_CONNECTS Last connection ti USER_ID D

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

4 1000 18-MAR-15 08:24:52 6 N

4 1000 20-MAR-15 04:55:42 1131 Y

4 1000 21-MAR-15 04:03:37 1216 N

SCRIPT TO KNOW “ACTIVE USERS” FOR OACOREGROUP

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 Run time

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

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

SQL> SELECT SUM(pins) "Total Pins",SUM(reloads) "Total Reloads", SUM(reloads)/SUM(pins) *100 libcache FROM v$librarycache;

Total Pins Total Reloads LIBCACHE

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

288397 939 .325592846

Use v$librarycache if running on RAC environment

DB Session Memory Usage

SQL> 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);

SID NAME MAXMEM

386 session uga memory max 173176

393 session uga memory max 173176

391 session uga memory max 173176

You can query this for how much free, used, total, and percentage filled space is available for each table-space

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;

TABLESPACE_NAME FREE USED TOTAL PERUSED

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

APPS_TS_ARCHIVE 2409.13 504.88 2914 17.33

APPS_TS_INTERFACE 4335.25 801.75 5137 15.61

APPS_TS_MEDIA 4211.13 1330.88 5542 24.01

APPS_TS_NOLOGGING 2071.13 40.88 2112 1.94

APPS_TS_QUEUES 4985.63 110.38 5096 2.17

APPS_TS_SEED 4474.38 2659.63 7134 37.28

APPS_TS_SUMMARY 4314.38 927.63 5242 17.7

APPS_TS_TOOLS 2546.88 1.13 2548 .04

APPS_TS_TX_DATA 10037.38 6194.75 16232.13 38.16

APPS_TS_TX_IDX 13039.38 7710.13 20749.5 37.16

APPS_UNDOTS1 9887.31 34.69 9922 .35

TABLESPACE_NAME FREE USED TOTAL PERUSED

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

CTXD 2041.33 24.67 2066 1.19

ODM 2049.44 10.56 2060 .51

OLAP 2063.94 1.06 2065 .05

OWAPUB 2057.85 .15 2058 .01

PORTAL 2147.46 .54 2148 .03

SYSAUX 3653.13 889 4542.13 19.57

SYSTEM 13847.88 11121.12 24969 44.54

Enabling and Disabling SELinux

Use the getenforce or sestatus commands to check the status of SELinux. The getenforce command returns Enforcing, Permissive, or Disabled.

The sestatus command returns the SELinux status and the SELinux policy being used:

$ sestatus

SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 24
Policy from config file:        targeted

Enabling/Disable SELinux

On systems with SELinux disabled, the SELINUX=disabled option is configured in

root$ vi /etc/selinux/config:

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#       targeted - Targeted processes are protected,
#       mls - Multi Level Security protection.

SELINUXTYPE=targeted

Also, the getenforce command returns Disabled:

$ getenforce
Disabled

How can u find custom top? where its located? How to find 
custom top in front end?

 Front Hand:-

System administrator responsibility-> Application-> register

OR

Back-end script:-

select * from fnd_profile_options fp,fnd_application fa where fa.application_short_name like 'XX%' and fa.application_id=fp.application_id;

To find out which Oracle e-business user is locking a table

The following query will help you identify the fnd user/ oracle e-business user that is locking a row/table.

SELECT objects.owner,
       objects.object_name,
       objects.object_type,
       user1.user_name         locking_fnd_user_name,
       login.start_time        locking_fnd_user_login_time,
       vs.module,
       vs.machine,
       vs.osuser,
       vlocked.oracle_username,
       vs.sid,
       vp.pid,
       vp.spid                 os_process,
       vs.serial#,
       vs.status,
       vs.saddr,
       vs.audsid,
       vs.process
FROM fnd_logins      login,
       fnd_user        user1,
       v$locked_object vlocked,
       v$process       vp,
       v$session       vs,
       dba_objects     objects
WHERE vs.sid = vlocked.session_id
   AND vlocked.object_id = objects.object_id
   AND vs.paddr = vp.addr
   AND vp.spid = login.process_spid(+)
   AND vp.pid = login.pid(+)
   AND login.user_id = user1.user_id(+)
--change the table name below
   AND objects.object_name LIKE '%' || upper('PO_HEADERS_ALL') || '%'
   AND nvl(vs.status,
           'XX') != 'KILLED';

How do I get a list of locked users in an Oracle database?

SELECT username, 
       account_status
  FROM dba_users;

select username,
       account_status 
  from dba_users 
 where lock_date is not null;

use following statement to unlock an account:

SQL> ALTER USER username ACCOUNT UNLOCK

How to find Active or Inactive users in r12

How to find R12 Active User list?

Login With Apps User
SQL> set line 1000
SQL> SELECT user_id, user_name, full_name
    FROM fnd_user fu, per_all_people_f papf
   WHERE papf.person_id = fu.employee_id
         AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
         AND SYSDATE BETWEEN papf.effective_start_date
                         AND NVL (papf.effective_end_date, SYSDATE ) ORDER BY 2;

USER_ID USER_NAME                                                                                            FULL_NAME
----------------------------------------------------------------------------------------------------       1130 ACCOUNT USER                                                                                         Tiwari, Mr. Kamlesh
      1170 CEO                                                                                                  D, Mr. Vasudevan
      1110 IT_SUPERUSER                                                                                Nabi, Mr. Gulzar
      1191 PREM KUMAR                                                                                           Lamchhanea, Mr. Prem Prasad
      1150 PROJECT USER                                                                                         Prasad, Mr. Ranjay
      1190 SANTOSH KUMAR                                                                                    Jha, Mr. Santosh Kumar
      1131 SCM USER                                                                                             Chawla, Mr. Sachin
      1214 TS.KRISHNA                                                                                           Krishna, Mr. T S
      1192 VIVEK KUMAR                                                                                          Gupta, Mr. Vivek Kumar

9 rows selected.

How to find inactive users in Oracle???

# enable session audit

sqlplus '/ as sysdba'
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> audit session;

# after a few months of normal database operation find the users who have not logged in.

SQL> select username, created from dba_users where account_status = 'OPEN' and created < sysdate -90 and not exists
(select distinct userid from aud$ where userid = username and LOGOFF$TIME > sysdate -90)
order by username;

USERNAME                       CREATED
------------------------------ ---------
ABM                            14-MAY-00
AHL                            30-MAY-02
AHM                            30-MAY-02
AK                             14-MAY-00
ALR                            14-MAY-00

Sql queries to check ACTIVE / INACTIVE Sessions

Total Count of sessions

select count(s.status) TOTAL_SESSIONS
from gv$session s;

Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR
select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 3600 and
s.status='INACTIVE';

COUNT OF ACTIVE SESSIONS

select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='ACTIVE';

TOTAL SESSIONS COUNT ORDERED BY PROGRAM

col program for a30
select s.program,count(s.program) Total_Sessions
from gv$session s, v$process p
where p.addr=s.paddr
group by s.program;

TOTAL COUNT OF SESSIONS ORDERED BY MODULE

col module for a30
prompt TOTAL SESSIONS
select s.module,count(s.sid) Total_Sessions
from gv$session s, v$process p
where p.addr=s.paddr
group by s.module;

TOTAL COUNT OF SESSIONS ORDERED BY ACTION

col action for a30
prompt TOTAL SESSIONS
select s.action,count(s.sid) Total_Sessions
from gv$session s, v$process p
where p.addr=s.paddr
group by s.action;

INACTIVE SESSIONS

prompt INACTIVE SESSIONS
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

INACTIVE

prompt INACTIVE SESSIONS
select count(s.status) INACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE';

INACTIVE PROGRAMS

col module for a40
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, v$process p
where p.addr=s.paddr and
s.status='INACTIVE';

INACTIVE PROGRAMS with disk reads

prompt INACTIVE SESSIONS
select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
GROUP BY S.PROGRAM;

INACTIVE SESSIONS COUNT WITH PROGRAM

col program for a30
prompt TOTAL INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr AND
s.status='INACTIVE'
group by s.program
order by 2 desc;

TOTAL INACTIVE SESSIONS MORE THAN 1HOUR

col program for a30
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,v$process p
where     p.addr=s.paddr AND
s.status='INACTIVE'
and s.last_call_et > (3600)
group by s.program
order by 2 desc;

TOTAL INACTIVE SESSIONS GROUP BY MODULE
col program for a60
COL MODULE FOR A30
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.module,count(s.module) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr AND
s.status='INACTIVE'
group by s.module;

INACTIVE SESSION DETAILS MORE THAN 1 HOUR

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (3600)
order by last_call_et;

INACTIVE PROGRAM --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
And s.program='&PROGRAM_NAME'
order by last_call_et;

INACTIVE MODULES --ANY--
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
And s.module like '%order_cleanup_hazmat_v3.sql'
order by last_call_et;

INACTIVE JDBC SESSIONS

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,
s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR

SELECT COUNT(P.SPID)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

FORMS
TOTAL FORM SESSIONS

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%');

FORMS SESSIONS DETAILS

col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600
order by spid;

col machine for a15
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
S.ACTION,s.process Client_Process,s.machine
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600;
order by 4;

INACTIVE FORMS SESSIONS DETAILS

col program for a15
col last_call_et for 999.99
select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
order by last_call_et desc;

UNIQUE SPID

select unique(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

COUNT FORMS

select COUNT(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

ZERO HASH VALUE

select COUNT(p.spid)
from gv$session s,gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
AND S.SQL_HASH_VALUE=0;

INACTIVE FORM BY NAME

select count(s.sid) from v$session S
where s.action like ('%&ACTION%')
AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%')
group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152
col spid for a10
col process_spid for a10
col user_name for a20
col form_name for a20
select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,
d.user_form_name "FORM_NAME"
from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,
apps.fnd_form_tl d
where
a.login_id=b.login_id
and c.user_name like 'JROMO'
and a.user_id=c.user_id
and trunc(b.start_time) >trunc(sysdate -11)
and trunc(b.end_time) is null
and b.form_id=d.form_id
and d.language='US';

INACTIVE FORM

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like ('FRM%')
and s.last_call_et > (3600*3)
order by last_call_et;

INACTIVE FORM SESSIONS

col cli_proc for a9
COL AUDSID FOR A6
COL PID FOR A6
COL SID FOR A5
COL FORM_NAME FOR A25
COL USER_NAME FOR A15
col last_call_et for 9999.99
SELECT
-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,vs.status,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600 last_call_et,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( vs.process, 1, 8 ) cli_proc,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id        = rf.form_id
AND ft.language       = USERENV('LANG')
), 1, 40 ) form_name
FROM apps.fnd_logins           fl,
gv$process            vp,
apps.fnd_login_resp_forms rf,
gv$session            vs
WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */
AND fl.login_type   = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid          = vp.pid
AND fl.login_id     = rf.login_id
AND rf.end_time    IS NULL
AND rf.audsid       = vs.audsid
and vs.status='INACTIVE'
ORDER BY
vs.process,
fl.process_spid;

ACTIVE

prompt ACTIVE SESSIONS
select count(s.status) ACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='ACTIVE';

MODULE

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
and s.MODULE like ('&MODULE_NAME_1HR%')
and s.last_call_et > ('&TIME_HRS' * 3600)
order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$process p
where
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S
where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0
AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.machine like ('%&CLIENT_MACHINE%') AND
p.addr=s.paddr and
s.status='INACTIVE';

GROUP BY program

col program for a60
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr AND
s.machine like ('%&CLIENT_MACHINE%') AND
s.status='INACTIVE'
group by s.program;

How to Add, Re-size, Drop Data-file all command (Data-file related all statement)

SQL> alter tablespace APPS_DATA add datafile '/idata1/QA/data1/a_txn_data47.dbf' size 10G;

SQL> ALTER DATABASE DATAFILE '/u01/oradb/db/apps_st/data/system09.dbf' RESIZE 800M;

SQL> alter tablespace drop datafile 'system09.dbf';

SQL> select file_name,status , online_status from dba_data_files;

SQL> alter database datafile '/u01/users02.dbf' offline;

SQL> ! rm -rf /u01/users02.dbf

SQL> col file_name format a45
SQL> col status format a10
SQL> select file_name,status , online_status from dba_data_files;
FILE_NAME                                     STATUS     ONLINE_
--------------------------------------------- ---------- -------
/u01/app/oracle/oradata/cdbs/users01.dbf      AVAILABLE ONLINE
/u01/app/oracle/oradata/cdbs/system01.dbf     AVAILABLE SYSTEM
/u01/users02.dbf                                AVAILABLE RECOVER
SQL> alter system switch logfile;

System altered.

SQL> ALTER DATABASE DATAFILE '/u01/oradb/db/apps_st/data/SYSTEM10012014e.dbf' AUTOEXTEND OFF;
Database altered.

SQL> ALTER DATABASE DATAFILE '/u01/oradb/db/apps_st/data/SYSTEM10012014e.dbf' AUTOEXTEND on;

Database altered.

SQL>select tablespace_name, file_name ,AUTOEXTENSIBLE,MAXBYTES,INCREMENT_BY from dba_data_files order by 1,2;

SQL> SELECT FILE_NAME, BYTES/(10241024), MAXBYTES/(10241024),AUTOEXTENSIBLE from DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

Table-spaces having less than 20% free space

SQL> select tablespace_name,(round(sum(Free_Space_MB),2)) "Free_Space_MB",(round(sum(Total_Space_MB),2)) "Total_MB",(round(100sum(Free_Space_MB)/sum(Total_Space_MB),2)) "% Free" from
(select a.tablespace_name,a.file_name,(((a.maxbytes-a.bytes) + nvl(b.Free_Space_B,0))/1024/1024) Free_Space_MB,(maxbytes/1024/1024) Total_Space_MB
from (select from dba_data_files where autoextensible='YES' and increment_by != 0 and maxbytes >= bytes) a left outer join
(select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id
UNION
select a.tablespace_name,a.file_name,(nvl(b.Free_Space_B,0)/1024/1024) Free_Space_MB,(bytes/1024/1024) Total_Space_MB
from (select * from dba_data_files where autoextensible='NO' or (autoextensible='YES' and maxbytes < bytes )) a left outer join (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id)
where tablespace_name in (select tablespace_name from dba_tablespaces where contents not in ('UNDO','TEMPORARY'))
group by tablespace_name having (round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) > 80;

How to change archive destination folder on oralce 11g

old Archive destination /u01/ora/db/tech_st/11.1.0/dbs/arch
New Archive destination /u01/Arch_BKP

Note:- Check your Database running with SPfile or Pfile.
If Database using SPfile than directly use below statement or restart Archive-log.

SQL> alter system set log_archive_dest='/u01/Arch_BKP' scope=spfile;

SQL> alter database noarchivelog;

Database altered

SQL> alter database archivelog;

Database altered.
SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/Arch_BKP
Oldest online log sequence     8
Next log sequence to archive   9
Current log sequence           9

Open Database:-
SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

How to run AWR, ADDM and ASH Reports

Run the following scripts from SQLPLUS

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

How to compile apps schema in Oracle applications R12

How to compile apps schema (or invalid objects in database) in Oracle applications R12

1. Connect to Database Tier

[ora@sujeet ~]$ cd $ORACLE_HOME/rdbms/admin

[ora@sujeet admin]$ sqlplus / as sysdba

Find how many Invalid objects. (Here 182 row select).

SQL> select object_name, owner, object_type from all_objects where status= 'INVALID';

182 rows selected.

Run below scripts:-

SQL> @utlrp.sql

Again check Invalid objects. (Here 0 row select).

SQL> select object_name, owner, object_type from all_objects where status= 'INVALID';

no rows selected

2. From application tier (using adadmin)
a) Login as application tier user.
b) Set environment variable.
c) Run “adadmin”
d) option 3 “compile/reload Applications Database Entities menu
e) option 1 “Compile Apps Schema”

3. From SQL (Individual objects)
a) Identity Invalid Object

SQL> select object_name, owner, object_type from all_objects where status= 'INVALID';
b) SQL> alter [object] [object_name] compile;

How to kill Inactive Session

SQL>spool on

SQL> spool /u01/db//session1.sql

SQL>select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';

SQL>spool off

SQL> @/u01/db//session1.sql

How to get Client IP address in oracle apps.

Connect with APPS User.
Run below Statement.

SQL> set line 1000
SQL> select SID, PROCESS, MACHINE, CLIENT_IDENTIFIER from v$session;

SID   PROCESS      MACHINE            CLIENT_IDENTIFIER
322        19943    Sujeet.oracle.com    Sujeet jha
262    19943         Sujeet.oracle.com             Sujeet jha

Login with Application User.
[appl@sujeet]$ ps -ef|grep 19943 <User latest Process id>
502      19943 6616 0 14:35 ?        00:00:34 /u02/apps/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,192.168.0.1
502      23747 23723 0 16:38 pts/2    00:00:00 grep 19943

How to compile Oracle Apps R12 Forms

1. Compile form  JAINRGCT.fmb using the following:-

   a) Set the APPL_TOP environment.

   b) Change directory to $AU_TOP/forms/US.

[applmgr@sujeet ]$ cd  $AU_TOP/forms/US

 
   c) Execute the following command from the operating system prompt:
   [applmgr@sujeet ]$ frmcmp_batch module=JAINRGCT.fmb output_file=$JA_TOP/forms/US/JAINRGCT.fmx

       userid=apps/<apps_pwd>

How to check OS level configuration (Disk space,Host name,RAM,OS version etc)

[ora@sujeet ~]$ hostname

[ora@sujeet ~]$ df -h
[ora@sujeet ~]$ free -m

[ora@sujeet ~]$ free -g
             total       used       free     shared    buffers     cached
Mem:            45         20         25          0          0         15
-/+ buffers/cache:          3         41
Swap:           15          0         15
[ora@sujeet~]$ uname
Linux
[ora@sujeet ~]$ uname -a
Linux sujeet.oracle.com 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:37:40
EST 2012 x86_64 x86_64 x86_64 GNU/Linux
[ora@sujeet ~]$ uname -v
#1 SMP Wed Feb 22 17:37:40 EST 2012
[ora@sujeet ~]$ uname -i
x86_64
[ora@sujeet ~]$ cat /proc/version
Linux version 2.6.32-300.10.1.el5uek (mockbuild@ca-build56.us.oracle.com)
(gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)       ) #1 SMP Wed Feb 22 17:37:40 EST 2012
[ora@sujeet ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
[ora@sujeet ~]$ cat /etc/issue
Oracle Linux Server release 5.8
Kernel \r on an \m
[ora@sujeet ~]$ cat /proc/cpuinfo
[ora@sujeet ~]$ cat /proc/meminfo
[ora@sujeet ~]$ grep "model name" /proc/cpuinfo
model name      : Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz
model name      : Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz

[ora@sujeet ~]$ cat /proc/version[ora@sujeet ~]$ uname -a

[ora@sujeet ~]$ uname -mrs
# free
# free -m
# free -mt
# free -gt
# lscpu
How do I find how long ago a Linux system was installed?
[root@sujeet ~]# ls -alct /|tail -1|awk '{print $6, $7, $8}'
Dec 19 2013

[root@sujeet ~]# tune2fs -l /dev/sda1
tune2fs 1.39 (29-May-2006)

Query to find all responsibilities of a user
Query to find all responsibilities of a user:- Here User_Name=SUJEET KUMAR.
-------------------------
SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
WHERE furg.user_id             = fu.user_id
   AND furg.responsibility_id   = frt.responsibility_id
   AND fr.responsibility_id     = frt.responsibility_id
   AND fa.application_id        = fat.application_id
   AND fr.application_id        = fat.application_id
   AND frt.language             = USERENV('LANG')
   AND UPPER(fu.user_name)      = UPPER('SUJEET KUMAR') -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY frt.responsibility_name;

How to find out which users are logged on to an Apps instance
FND_USER table stores the details of all the end users. If we give this query:
select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');
How to get list of Active ERP user in Oracle Apps R12.
SELECT user_id, user_name, full_name
    FROM fnd_user fu, per_all_people_f papf
   WHERE papf.person_id = fu.employee_id
         AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
         AND SYSDATE BETWEEN papf.effective_start_date
                         AND NVL (papf.effective_end_date, SYSDATE ) ORDER BY 2;

Data Guard Configuration status - Primary and Standby
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL> select db_unique_name from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------

PROD
SQL> select * from v$dataguard_status;
no rows selected

Check Apps Version
SQL> select RELEASE_NAME from fnd_product_groups;
RELEASE_NAME
--------------------------------------------------
12.1.3

How to check all apps node info?
SQL> select node_name, node_mode, support_cp, support_web, support_admin,support_forms from FND_NODES;
NODE_NAME N S S S S
------------------------------ - - - - -
AUTHENTICATION O N N N N
PROD

How to create User Read_only in oracle?

Hear User_id=sonu password=sona

Create User:-

SQL> create user sonu identified by sona;

User created.

Give Permission Read only:-

SQL> grant create session,select any dictionary,select any table to sonu;

Grant succeeded.

Now check with same user its connected or not:-

SQL> conn sonu/sona

Connected.

SQL> sho user

USER is "sonu"

Verified user Privileges:-

SQL> create table t(id number);

create table t(id number)

*ERROR at line 1:

ORA-01031: insufficient privileges

How to check User Privileges:-

select privilege 
from dba_sys_privs 
where grantee='SAMPLE' 
order by 1;

select * from USER_ROLE_PRIVS where USERNAME='sonu';
select * from USER_TAB_PRIVS where Grantee = 'sonu';
select * from USER_SYS_PRIVS where USERNAME = 'sonu';

TABLE Related Query

Base Table:-

tabs
dba_tables
all_tables
user_tables

Find Table Name:-

SQL> SELECT table_name from user_tables where table_name='GL_DAILY_OPEN_INT';

TABLE_NAME
------------------------------
GL_DAILY_OPEN_INT

Backup of same Table:-

SQL> create table GL_DAILY_OPEN_INT_bkp as select * from GL_DAILY_OPEN_INT;

Table created.

Drop Table:-

SQL> drop table GL_DAILY_OPEN_INT;

Table dropped.

How to find tables that have a specific column name?


In Oracle


SELECT owner, table_name, column_name
FROM dba_tab_columns
WHERE column_name like 'PASSWORD'
ORDER by table_name;

Example:


OWNER TABLE_NAME COLUMN_NAME
—————————— —————————— ——————————
SYS DBA_USERS PASSWORD


CREATE TABLE Syntax:-

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
);

How to set sort_area_size parameter

SQL> sho parameter sort

SQL > Alter system set sort_area_size=3145728 SCOPE=spfile; <3gb>

OR

SQL> alter session set sort_area_size=2000000000;

Session altered.

SQL> alter session set hash_area_size=2000000000;

Session altered.

How to set Database .env???

UNIX/Linux:

$ export ORACLE_SID=idea

$ export ORACLE_HOME=/home/app/oracle/oracle/product/10.2.0/idea

$ export PATH=$PATH:$ORACLE_HOME/bin

$ sqlplus / as sysdba

Windows:

C:>Set ORACLE_SID=idea

C:\>sqlplus "sys as sysdba"

How to check ASM Disk group??

SQL> select name from v$asm_diskgroup;

no rows selected

How to check your Database is in RAC mode or not???

SQL> select name, value from v$parameter where name='cluster_database';

NAME VALUE

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

cluster_database FALSE

You can tell if it is a cluster database by looking to see if the cluster database parameter is set

SQL> select name, value from v$parameter where name='cluster_database';

NAME VALUE
--------------------- ---------------------
cluster_database TRUE

You can tell how many instances are active by:-

SQL> SELECT * FROM V$ACTIVE_INSTANCES;

INST_NUMBER INST_NAME
----------- -----------------------

1 c1718-6-45:AXIOSS1

2 c1718-6-46:AXIOSS2

Oracle R12 Apps startup script

scripts name= Apps_startup.sh

. /oratest/TEST/db/tech_st/11.1.0/TEST_test.env
lsnrctl start TEST
. /$ORACLE_HOME/appsutil/scripts/TEST_test/addbctl.sh start

. /appltest/TEST/apps/apps_st/appl/APPSTEST_test.env
. /$INST_TOP/admin/scripts/adstrtal.sh apps/apps

How to find Oracle EBSR12 URL

SQL> select HOME_URL from icx_parameters;

HOME_URL
--------------------------------------------------------------------------------
http://ebs.demo.com/OA_HTML/AppsLogin

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

PROFILE_OPTION_VALUE
--------------------------------------------------------------------------------
http://ebs.globalerpdemo.com:80

Oracle R12 apps stop script

scripts name= Apps_startup.sh

. /appltest/TEST/apps/apps_st/appl/APPSTEST_idiora2.env
. /$INST_TOP/admin/scripts/adstpall.sh apps/apps

. /oratest/TEST/db/tech_st/11.1.0/TEST_test.env
lsnrctl stop TEST

. /$ORACLE_HOME/appsutil/scripts/TEST_test/addbctl.sh stop immediate

How to ADD DATA-FILE?

1. Need to check and verified host-name & Database name.
2. Need to check Mount point size (free space available or not)
3. Log-in with SQL
4. Check Data-file location and name.

sujeet $ hostname

sujeet

SQL> select name from V$database;

NAME
---------
idea

SQL> !df -g <AIX OS>
Filesystem GB blocks Free %Used Iused %Iused Mounted on

/dev/fslv03 204.00 55.73 73% 1159614 9% /u01
/dev/fslv04 204.00 50.03 76% 17 1% /u02
/dev/fslv05 470.00 202.19 57% 61 1% /u03
/dev/fslv06 600.00 154.14 75% 92 1% /u04

Sujeet $ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(257)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)

bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 16 06:36:35 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace SYSAUX add datafile '/u03/oracle/idea/sysaux12.dbf' size 20G autoextend on next 500M maxsize 30G;

Tablespace altered.

SQL> select file_name,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024,INCREMENT_BY/1024/1024 from dba_data_files where tablespace_name='SYSAUX';
FILE_NAME TABLESPACE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024 INCREMENT_BY/1024/1024
---------------------------------------- ------------------------------ --------------- --- ------------------ -------------------
/u04/oracle/idea/sysaux08.dbf SYSAUX 30720 YES 30720 .061035156
/u05/oracle/idea/sysaux09.dbf SYSAUX 10240 YES 10240 .061035156
/u04/oracle/idea/sysaux10.dbf SYSAUX 30720 YES 30720 .061035156
/u05/oracle/idea/sysaux11.dbf SYSAUX 20300 YES 30720 .012207031

FILE_NAME TABLESPACE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024 INCREMENT_BY/1024/1024
---------------------------------------- ------------------------------ --------------- --- ------------------ ----------------------
/u03/oracle/idea/sysaux12.dbf SYSAUX 20480 YES 30720 .061035156

12 rows selected.

How to check RAC Services Status, Start & STOP Command

A. Status of the services

[oracle@sujeet ~]$ srvctl status instance -d prod1 -i prod2

[oracle@sujeet ~]$ srvctl status scan_listener -i 1

[oracle@sujeet ~]$ srvctl status scan -i 1

[oracle@sujeet ~]$ srvctl status listener -n sujeet

[oracle@sujeet ~]$ srvctl status nodeapps -n sujeet

[root@sujeet ~]# cd /u01/app/11.2.0.3/grid/bin

[root@sujeet bin]# ./crsctl check crs

B. Stop the services

1. [oracle@sujeet ~]$ srvctl stop instance -d prod1 -i prod2

2. [oracle@sujeet ~]$ srvctl stop scan_listener -i 1

3. [oracle@sujeet~]$ srvctl stop scan -i 1

4. [oracle@sujeet ~]$ srvctl stop listener -n sujeet

5. [oracle@sujeet ~]$ srvctl stop nodeapps -n sujeet

6.     [root@sujeet ~]# cd /u01/app/11.2.0.3/grid/bin
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl check crs
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl stop crs

Note:- After the rebooting of the server check all the services status as mentioned in part A, services will come up automatically, if the services are not resumed in 10 minutes start the services as mentioned in part C

C. Starting the services

    [root@sujeet ~]# cd /u01/app/11.2.0.3/grid/bin
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl start crs
    [root@sujeet bin]#
    [root@sujeet bin]# ./crsctl check crs

Note:- after starting ‘./crsctl start crs’ wait till all the services comes ‘ONLINE’ then start other services

2. [oracle@sujeet ~]$ srvctl start nodeapps -n sujeet

3. [oracle@sujeet ~]$ srvctl start listener -n sujeet

4. [oracle@sujeet ~]$ srvctl start scan -i 1

5. [oracle@sujeet~]$ srvctl start scan_listener -i 1

6. [oracle@sujeet ~]$ srvctl start instance -d prod1 -i prod2


RMAN RESTORE AND RECOVERY steps

Full Database Restore
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Tablespace Restore (online)
$rman target / nocatalog
RMAN> sql ‘alter tablespace users offline’;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql ‘alter tablespace users online’;

Tablespace Restore (offline)
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
database opened

Restoring a Specific Datafile
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile ‘/oradata/DB1/dbf/users01.dbf’;
RMAN> recover datafile ‘/oradata/DB1/dbf/users01.dbf’;
RMAN> alter database open;
database opened

Control File Restoration
Prerequisite: In your rman backup directory determine the latest control file backup.
Default Format: c-nnnnnnnnnn-nnnnnnnn-nn
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup nomount;
RMAN> set dbid =235678
RMAN> restore controlfile from ‘/oradata/DB1/rman/c-235678-20060626-02′
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened
Database Point-In-Time-Recovery (PITR)
Also known as time-based incomplete recovery.
$rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> recover database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> alter database open resetlogs;
database opened
* Make sure you perform a full backup after this operation!

Restore to Another System
Prerequisites
Ideally ensure destination system configured exactly like source.
Same OS version and patch level.
Same drives (C:, D:, S: etc.).
CPU and RAM same or better.
The same version of Oracle is installed on the target system as the source.
Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.
Ensure the listener is running.
Copy RMAN backupset files to the destination system rman directory.
Procedure

Restore SPFILE and Control File
$rman target / nocatalog
RMAN> set dbid 273450560
RMAN> startup nomount;
Creates the file: %ORACLE_HOME%\database\hc_db1.dat
RMAN> restore spfile from ‘R:\rman\C-273450560-20080313-00′;
Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA
RMAN> startup force nomount
RMAN> restore controlfile from ‘R:\rman\C-273450560-20080313-00′;
RMAN> shutdown immediate
RMAN> exit

Restore and Recover the Data
$rman target / nocatalog
RMAN> startup mount;
RMAN> restore database;
For a large database this step may take some time.
RMAN> recover database;
If you do not have\need the very last log(s) you can disregard any error messages.
ORA-00310: archived log contains sequence 100; sequence 101 required…
RMAN> alter database open resetlogs;
database opened

Check Archive Mode States

SQL> select dest_name,status, destination from v$archive_dest;

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oratest/TEST/db/tech_st/11.1.0/dbs/arch
Oldest online log sequence 91
Next log sequence to archive 92
Current log sequence 92

How to enable Archive Mode?
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence 25
Current log sequence 27

SQL> alter system checkpoint;

System altered.

Now we shutdown the database and bring it backup in mount mode.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 511708752 bytes
Database Buffers 331350016 bytes
Redo Buffers 5132288 bytes
Database mounted.
SQL>

Lastly all that is needed it set archive log mode and open the database.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27

You can switch to the log file to see that an archive is written to archive log location.

SQL> alter system switch logfile;

System altered.

Archive log check:-

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

check Apply Archive Log:-

select PROCESS,STATUS,SEQUENCE#,thread# from v$managed_standby;

SQL> show parameters archive log

Check v$managed_standby status

SQL> select process, status, sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH WRITING 18723
ARCH CLOSING 18783
ARCH WRITING 18722
ARCH WRITING 18724

LNS WRITING 18784

How to create INDEX?

SQL> create index SG00200TI5 on UMSDATA.SG00200T (SOURCE_ID,SOURCE_CD) tablespace ums_data;

INDEX CREATED.

How to find INDEX Created or not?

Index name=SG00200TI5

SQL> select count(*) from user_indexes where index_name = ' SG00200TI5';

We can alter the index created by using this statement to start monitoring..

ALTER INDEX yourIndex_idx MONITORING USAGE;

Once, it is altered, you can query the table, v$object_usage for checking the result

SELECT table_name, index_name, monitoring, used FROM v$object_usage;

How to delete log file last 5 days before?

If Apps server have space issue at that time DBA release unwanted file and log.

$ cd $APPLCSF/$APPLLOG

find . -name '*.out' -mtime +5 | xargs rm -f

find . -name '*.req' -mtime +5 | xargs rm -f

find . -name '*.RTF' -mtime +5 | xargs rm -f

find . -name '*.xml' -mtime +5 | xargs rm -f
find . -name 'access_log*' -mtime +5 | xargs rm -f

find . -name 'error_log*' -mtime +5 | xargs rm -f

Check Mount Point space

$ df -h <Linux OS>

Check Database size

SQL> select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

DB Size in GB
-------------

85.7810669

Export Full Database Scripts

script name:- exp_TEST_idea.sh

#!/bin/bash

#Delete exports older than 3 days

find /u01/db_exports -name '*.dmp' -mtime +3 | xargs rm -f

find /u01/db_exports -name '*.log' -mtime +3 | xargs rm -f

export ORACLE_SID=test_idea

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH:.

fdate=`date +%Y%m%d`

expdp system/manager@TEST_idea directory=TEST_idea_EXP_DIR dumpfile=TEST_idea_$fdate.dmp logfile=TEST_idea_export_$fdate.log full=y

RMAN Backup Script

# Identify the backup location
export BackupLocation=/backup/Test

# Source the environment file, if any
. /home/oratest/TEST_db.env

find /backup/Test/RMAN -name '*.gz' -mtime +15 | xargs rm -f

# Run the RMAN backup script and spool the output
date
rman target / nocatalog log=$BackupLocation/LogRMAN << EOF
run {

allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
crosscheck backup;
delete noprompt expired backup;
backup database format '$BackupLocation/Test_`date +%F`_%U_DB.bkp';
backup current controlfile format '$BackupLocation/Test_`date +%F`_ControlFile.bkp';
backup archivelog from time 'sysdate-1' format '$BackupLocation/Test_`date +%F`_%U_ArchiveLog.bkp';
delete noprompt archivelog until time 'sysdate-7';
crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel t1;
release channel t2;
release channel t3;

}
exit;
EOF

# Compress the file
date
gzip -r $BackupLocation/Test_`date +%F`*

# Transfer the file
date
mv $BackupLocation/Test_`date +%F`* $BackupLocation/RMAN
date

echo "Backup Completed Successfully ..."

INSTANCE RELATED QUERIES

SQL> select instance_name,instance_number,status from v$instance;

INSTANCE_NAME INSTANCE_NUMBER STATUS
---------------- --------------- ------------
PROD 1 OPEN

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> desc fnd_product_groups

SQL> select release_name,last_update_date from fnd_product_groups;

RELEASE_NAME LAST_UPDA
-------------------------------------------------- ---------
12.1.3 15-JUL-12

SQL> select * from v$parameter

SQL> select name from v$parameter where name like '%out%'

How to check how may CPU Running?

CPU NO Count:-

[root@sujeet ~]# cat /proc/cpuinfo | grep "cpu cores" |uniq

cpu cores : 6

How to check OPEN CURSOR Parameter?

SQL> select sum(a.value) from v$sysstat a,v$statname b

where a.STATISTIC#=b.STATISTIC#

and b.name='opened cursors current';

SUM(A.VALUE)

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

76

SQL> show parameter open

NAME TYPE VALUE

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

open_cursors integer 600

open_links integer 4

open_links_per_instance integer 4

read_only_open_delayed boolean FALSE

session_max_open_files integer 10

How to find RAC all IP Address?

SCAN_IP_INFO:-

[oracle@sujeet ~]$ . idea1.env

[oracle@sujeet ~]$ srvctl config scan

SCAN name: sujeet-scan, Network: 1/10.110.30.0/255.255.255.0/eth0

SCAN VIP name: scan1, IP: /sujeet-scan/10.110.30.25

SCAN VIP name: scan2, IP: /sujeet-scan/10.110.30.26

SCAN VIP name: scan3, IP: /sujeet-scan/10.110.30.27

VIP Info:-

[oracle@sujeet ~]$ srvctl config nodeapps -a

Network exists: 1/10.110.30.0/255.255.255.0/eth0, type static

VIP exists: /ami-ms-db-a-vip/10.110.30.23/10.110.30.0/255.255.255.0/eth0, hosting node ami-ms-db-a

VIP exists: /ami-ms-db-b-vip/10.110.30.24/10.110.30.0/255.255.255.0/eth0, hosting node ami-ms-db-b

Private IP Info:-

SQL> select INST_ID,IP_ADDRESS from GV$CLUSTER_INTERCONNECTS;

INST_ID IP_ADDRESS

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

1 169.254.204.91

2 169.254.35.170

SQL> select IP_KSXPIA from X$KSXPIA where PUB_KSXPIA= 'N';

IP_KSXPIA

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

169.254.204.91

[oracle@sujeet ~]$ nslookup ami-ms-db-a.btutilities.com

Server: 10.110.4.244

Address: 10.110.4.240#53

Name: sujeet.oracle.com

Address: 10.110.30.22

Find sid using pid get from os prompt using top command

n show PROCESS id for all the active sessions

SQL> select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser
from V$process p,V$session s
where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%';

Find sid using pid get from os prompt using top command

Join paddr of v$session with addr of v$process to get the pid (pid column of v$process)

SQL> select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=&processid;

Show all connected users

select username,sid || ',' || serial# "ID",status,last_call_et "Last Activity"
from v$session
where username is not null
order by status desc, last_call_et desc

Time since last user activity

select username,floor(last_call_et / 60) "Minutes",status
from v$session
where username is not null
order by last_call_et

Sessions sorted by logon time

select      username,osuser,sid || ',' || serial# "ID",status,to_char(logon_time, 'hh24:mi dd/mm/yy') login_time,last_call_et
from     v$session
where     username is not null
order     by login_time

Show user info including os pid

select     s.sid || ',' || s.serial# "SID/SERIAL",s.username,s.osuser,p.spid "OS PID",s.program
from     v$session s,v$process p
Where     s.paddr = p.addr
order      by to_number(p.spid)

Check delivery status-(SCM)

SQL> select delivery_detail_id from apps.wsh_delivery_assignments where delivery_id=975926))

How to find OPERATING_UNIT

LEGAL_ENTITY_ID=23284

SQL> select distinct OPERATING_UNIT from gmf_xla_extract_headers where LEGAL_ENTITY_ID=23284;

Detail of OPERATING UNITS-

SQL> select * from hr_operating_units;

***************** Concurrent Request Related Scripts********

Currently long running requests

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select request_id,NAME,USER_NM,"START_DT","COMPLETION_DT","EX_TIME" EX_MINS,
CASE WHEN (PHASE_CODE) = 'C' THEN 'Completed'
WHEN (PHASE_CODE) = 'I' THEN 'Inactive'
WHEN (PHASE_CODE) = 'P' THEN 'Pending'
WHEN (PHASE_CODE) = 'R' THEN 'Running'
ELSE 'NODATA'
END AS PHASE,
CASE WHEN (STATUS_CODE) = 'A' THEN 'Waiting'
WHEN (STATUS_CODE) = 'B' THEN 'Resuming'
WHEN (STATUS_CODE) = 'C' THEN 'Normal'
WHEN (STATUS_CODE) = 'D' THEN 'Cancelled'
WHEN (STATUS_CODE) = 'E' THEN 'Error'
WHEN (STATUS_CODE) = 'F' THEN 'Scheduled'
WHEN (STATUS_CODE) = 'G' THEN 'Warning'
WHEN (STATUS_CODE) = 'H' THEN 'Hold'
WHEN (STATUS_CODE) = 'I' THEN 'Normal'
WHEN (STATUS_CODE) = 'M' THEN 'No Manager'
WHEN (STATUS_CODE) = 'Q' THEN 'Standby'
WHEN (STATUS_CODE) = 'R' THEN 'Normal'
WHEN (STATUS_CODE) = 'S' THEN 'Suspended'
WHEN (STATUS_CODE) = 'T' THEN 'Terminating'
WHEN (STATUS_CODE) = 'U' THEN 'Disabled'
WHEN (STATUS_CODE) = 'W' THEN 'Paused'
WHEN (STATUS_CODE) = 'X' THEN 'Terminated'
WHEN (STATUS_CODE) = 'Z' THEN 'Waiting'
ELSE 'NODATA'
END AS STATUS,
ARGUMENT_TEXT
from
(select distinct a.request_id,b.user_concurrent_program_name NAME,c.user_name USER_NM,round((sysdate-a.actual_start_date)*24*60,2) "EX_TIME",
a.actual_start_date "START_DT",a.actual_completion_date "COMPLETION_DT",a.ARGUMENT_TEXT,a.PHASE_CODE,a.STATUS_CODE
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c
where a.concurrent_program_id=b.concurrent_program_id
and a.REQUESTED_BY=c.USER_ID
and b.language='US'
and a.phase_code in ('R')
and trunc(a.actual_start_date) > trunc(sysdate-4) and a.requested_by=c.user_id order by "EX_TIME" desc ) ;

Check status for running request-

SQL> select ORACLE_PROCESS_ID,PHASE_CODE,STATUS_CODE from apps.fnd_concurrent_requests where request_id=' 11554091';

History of Completed request Name

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

col name for a30
col USER_NM for a15
col ARGUMENT_TEXT for a40
col START_DT for a20

select request_id,"START_DT","EX_TIME",PHASE_CODE,STATUS_CODE from
(select distinct (a.request_id),b.user_concurrent_program_name NAME,c.user_name USER_NM,round((a.actual_completion_date-a.actual_start_date)*24*60,2) "EX_TIME",
a.actual_start_date "START_DT",a.actual_completion_date "COMPLETION_DT",a.ARGUMENT_TEXT,a.PHASE_CODE,a.STATUS_CODE
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c
where a.concurrent_program_id=b.concurrent_program_id
and a.REQUESTED_BY=c.USER_ID
and a.phase_code='C'
and a.status_code='C'
and upper(b.user_concurrent_program_name) like upper('<request name>')
and trunc(a.actual_start_date) > trunc(sysdate-1000) order by "EX_TIME" desc ) where "EX_TIME" is not null and rownum < 10 ;

Running concurrent request Status:-

SQL> 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('&requestid');

To get sid/spid of running concurrent request

SQL> SELECT b.sid,b.serial#
FROM APPS.fnd_concurrent_requests A,
V$SESSION B
WHERE
A.oracle_session_id = B.audsid
AND A.request_id in ('req_id');

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

SQL> select a.sid,a.serial#,a.program,a.machine,module,sql_hash_value,a.username,b.spid from v$session a,v$process b
where b.addr=a.paddr and a.sid in ('<sid>') ;

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

SQL> select sql_text from v$sqlarea where hash_value='<sql_hash>' ;

History of Request with Name:-

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

col name for a30

col USER_NM for a15

col ARGUMENT_TEXT for a40

col START_DT for a20

select request_id,"START_DT","EX_TIME",PHASE_CODE,STATUS_CODE,NAME from

(select distinct (a.request_id),b.user_concurrent_program_name NAME,c.user_name USER_NM,round((a.actual_completion_date-a.actual_start_date)*24*60,2) "EX_TIME",

a.actual_start_date "START_DT",a.actual_completion_date "COMPLETION_DT",a.ARGUMENT_TEXT,a.PHASE_CODE,a.STATUS_CODE

from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c

where a.concurrent_program_id=b.concurrent_program_id

and a.REQUESTED_BY=c.USER_ID

and a.phase_code='C'

and a.status_code='C'

and upper(b.user_concurrent_program_name) like upper('IFFCO OM: HHT Sales Order and Returns Interface Program')

and trunc(a.actual_start_date) > trunc(sysdate-1000) order by "EX_TIME" desc ) where "EX_TIME" is not null and rownum < 10 ;

History of Request with Name & Argument:-

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

col name for a30

col USER_NM for a15

col ARGUMENT_TEXT for a40

col START_DT for a20

select request_id,"START_DT","EX_TIME",PHASE_CODE,STATUS_CODE,NAME,ARGUMENT_TEXT from

(select distinct (a.request_id),b.user_concurrent_program_name NAME,c.user_name USER_NM,round((a.actual_completion_date-a.actual_start_date)*24*60,2) "EX_TIME",

a.actual_start_date "START_DT",a.actual_completion_date "COMPLETION_DT",a.ARGUMENT_TEXT,a.PHASE_CODE,a.STATUS_CODE

from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c

where a.concurrent_program_id=b.concurrent_program_id

and a.REQUESTED_BY=c.USER_ID

and a.phase_code='C'

and a.status_code='C'

and upper(b.user_concurrent_program_name) like upper('IFFCO SCM : Non Moving % Slow Moving Stocks of FG Report')

and trunc(a.actual_start_date) > trunc(sysdate-1000) order by "EX_TIME" desc ) where "EX_TIME" is not null and rownum < 10 ;

Request id status:-

SQL> select request_id,"START_DT","EX_TIME",PHASE_CODE,STATUS_CODE,NAME,ARGUMENT_TEXT from

(select distinct (a.request_id),b.user_concurrent_program_name NAME,c.user_name USER_NM,round((a.actual_completion_date-a.actual_start_date)*24*60,2) "EX_TIME",

a.actual_start_date "START_DT",a.actual_completion_date "COMPLETION_DT",a.ARGUMENT_TEXT,a.PHASE_CODE,a.STATUS_CODE

from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c

where a.concurrent_program_id=b.concurrent_program_id

and a.REQUESTED_BY=c.USER_ID

--and a.phase_code='C'

--and a.status_code='C'

and a.request_id =9968844)

Number of daily concurrent requests.

SQL> SELECT trunc(REQUESTED_START_DATE), count(*)

FROM FND_CONCURRENT_REQUESTS

WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate

group by rollup(trunc(REQUESTED_START_DATE)) ;

****************TABLE Related Scripts***************

Check Table space

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

Query to report tablespaces having less than 20% free space left considering auto-extensible db-files:-

SQL> select tablespace_name,(round(sum(Free_Space_MB),2)) "Free_Space_MB",(round(sum(Total_Space_MB),2)) "Total_MB",(round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) "% Free" from
(select a.tablespace_name,a.file_name,(((a.maxbytes-a.bytes) + nvl(b.Free_Space_B,0))/1024/1024) Free_Space_MB,(maxbytes/1024/1024) Total_Space_MB
from (select * from dba_data_files where autoextensible='YES' and increment_by != 0 and maxbytes >= bytes) a left outer join
(select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id
UNION
select a.tablespace_name,a.file_name,(nvl(b.Free_Space_B,0)/1024/1024) Free_Space_MB,(bytes/1024/1024) Total_Space_MB
from (select * from dba_data_files where autoextensible='NO' or (autoextensible='YES' and maxbytes < bytes )) a left outer join (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id)
where tablespace_name in (select tablespace_name from dba_tablespaces where contents not in ('UNDO','TEMPORARY'))
group by tablespace_name having (round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) < 20;

DB size with Tablespace

set head on

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

Table Truncated

The below given table Truncated. Please check it once.

SQL> truncate table APPLSYS.FND_LOG_MESSAGES;

Table truncated.

ADD More SPACE

ALTER TABLESPACE temp2 ADD TEMPFILE '/sap_temp/IPLUAT/db/apps_st/data/temp2_02.dbf' SIZE 5000M;

Add tablespace on QA:-

Login SQL TO DB-User:-

ioratst/ ioratst

login ti sys as sysdba

Tablespace name- APPS_TS_TX_DATA

SQL> alter tablespace APPS_TS_TX_DATA add datafile '/idata1/IFFQAEBS1/data1/a_txn_data47.dbf' size 10G;

Tablespace name- APPS_TS_TX_IDX.

/idata1/IFFQAEBS1/data1/a_txn_ind19.dbf

SQL> select * from dba_data_files where tablespace_name like '%APPS_TS_TX_IDX'

SQL> select * from dba_data_files where tablespace_name like '%APPS_TS_TX_IDX' order by file_id;

SQL> alter tablespace APPS_TS_TX_IDX add datafile '/idata1/IFFQAEBS1/data1/a_txn_ind21.dbf' size 4G;

Tablespace altered.

SQL> Alter tablespace APPS_TS_TX_IDX add datafile '/idata/IFFQAEBS/data/a_txn_ind02.dbf' size 10G;

Tablespace altered.

Increase the Size of Tablespace

If you want to increase the size of tablespace, its so simple.

You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.

OR

For Example

ALTER DATABASE

DATAFILE ‘/u03/oradata/ userdata02. dbf’

RESIZE 200M;

If you don’t have any free space on that partition of disk then you can add another

datafile on the other partition for particular tablespace.

For example-

ALTER TABLESPACE app_data

ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’

SIZE 200M;

Now you can insert data with in this tablespace.

SQL> select * from dba_tablespaces;

SQL> select * from user_tablespaces;

SQL> select * from dba_data_files;

SQL> select * from dba_free_space;

SQL> desc dba_free_space;

Name Null? Type

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

TABLESPACE_NAME VARCHAR2(30)

FILE_ID NUMBER

BLOCK_ID NUMBER

BYTES NUMBER

BLOCKS NUMBER

RELATIVE_FNO NUMBER

Check each of the Data, Index and temporary table-spaces for extend and blocks Allocation details.

SQL> SET LINES 1000

SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS

FROM DBA_SEGMENTS;

SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS

FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=’STAR01D’;

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG

WHERE STATUS= 'CURRENT'

Table and tablespace location

Finding out who owns a table and what tablespace it is in is a pretty

common need of the DBA. In this query, we use the dba_tables view to

find the owner and tablespace name of the EMP table.

SQL> select owner, table_name, tablespace_name

2 from dba_tables

3 where table_name='EMP';

OWNER TABLE_NAME TABLESPACE_NAME

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

SCOTT EMP USERS

POLL EMP USERS

SQL> select tablespace_name from dba_tablespaces where tablespace_name='APPS_PS_SX_DATA';

TABLESPACE_NAME

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

APPS_PS_SX_DATA

As we can see from this query, we have two tables called EMP, owned by two different users (Scott and Poll). Both tables are contained in the USERS tablespace.

A good exercise for you might be to try to join this query with a view like DBA_EXTENTS and figure out just how big these tables are allocated.

Check location of DATA_FILE

SQL> select name from V$datafile;

NAME

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

/u01/sonu/data/system01.dbf

/u01/sonu/data/system02.dbf

/u01/sonu/data/system03.dbf

/u01/sonu/data/system04.dbf

/u01/sonu/data/system05.dbf

/u01/sonu/data/system06.dbf

/u01/sonu/data/system07.dbf

/u01/sonu/data/system08.dbf

/u01/sonu/data/system09.dbf

Check Data Transfer:-

select s.sid, s.username, s.osuser, to_char(sysdate-(last_call_et/24/60/60),'HH24:MI:SS') last_call, sq.address,

sq.disk_reads / decode(sq.executions,0,1,sq.executions) DiskReadperExec,

sq.buffer_gets / decode(sq.executions,0,1,sq.executions) buffer_gets,

sq.executions, sq.ROWS_PROCESSED,

decode(sq.executions,0,0,sq.CPU_TIME/sq.EXECUTIONS/1000000) cpu,

decode(sq.executions,0,0,sq.ELAPSED_TIME/sq.EXECUTIONS/1000000) elapsed,

decode(s.audsid,userenv('sessionid'),'show sessions',sq.sql_text) sql_text

from v$sql sq, v$session s

where s.status='ACTIVE'

--and sq.executions > 0

and s.sql_hash_value = sq.hash_value

and s.sql_Address = sq.address

/*and s.SQL_CHILD_NUMBER = sq.child_number*/

and s.user# = sq.parsing_user_id

order by elapsed ;

TABLE LIST

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 ;

Query to find locked tables in 11g.

Run following from sysdba:

select owner||'.'||object_name obj

,oracle_username||' ('||s.status||')' oruser

,os_user_name osuser

,machine computer

,l.process unix

,''''||s.sid||','||s.serial#||'''' ss

,r.name rs

,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time

from v$locked_object l

,dba_objects o

,v$session s

,v$transaction t

,v$rollname r

where l.object_id = o.object_id

and s.sid=l.session_id

and s.taddr=t.addr

and t.xidusn=r.usn

order by osuser, ss, obj

OR:-

To kill a perticular session :

alter system kill session '<>';

e.g.

alter system kill session '101,1120';

Steps for releasing lock on a table :< APPS>

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;

Step1:To verify the lock object Here is the import query:
---------------------------------------------------------------

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

Step 2: Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

System altered.

This is to get the pkg-body definition from database

select TEXT from dba_source where name=upper('xxiff_om_orders_conv_pkg') and owner='APPS' and LINE <10000 and TYPE='PACKAGE BODY';

This is to get the pkg definition from database

select TEXT from dba_source where name=upper('xxiff_om_orders_conv_pkg') and owner='APPS' and LINE <10000 and TYPE='PACKAGE BODY';

Before patching - check the invalids and copy paste the output in PAL ( invalids - tab)

set pages 199
set lines 199
col OBJECT_NAME for a30
select OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME, CREATED from dba_objects where status='INVALID';

Package Object Scripts:- tauseef scripts

set pagesize 0
set trimspool on
spool invp.lst
select 'Alter ' || decode(Object_type,'PACKAGE BODY','PACKAGE',Object_type )
||' '
||'"'||object_name||'"'
|| ' Compile '
||decode(Object_type,'PACKAGE BODY','Body;',';' )
from user_objects
WHERE STATUS = 'INVALID'
order by
decode(Object_type,
'PACKAGE BODY','8',
'PACKAGE','7','FUNCTION','6','PROCEDURE','5',
'VIEW','4','9'||Object_type )
/

spool off
@invp.lst

FNDOPP Manager Log file
===========================

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

Find TABLE with SPACE

SQL> select TABLESPACE_NAME,(TABLESPACE_SIZE)/(1024*1024*1024) SIZE_IN_GB,(ALLOCATED_SPACE)/(1024*1024*1024) ALLOCATED_IN_GB,(FREE_SPACE)/(1024*1024*1024) FREE_IN_GB FROM dba_temp_free_space;

TABLESPACE_NAME SIZE_IN_GB ALLOCATED_IN_GB FREE_IN_GB

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

TEMP1 12 1.81445313 11.0576172

TEMP2 40 .00390625 39.9960938

SQL> select tablespace_name,sum(BYTES/1024/1024) from dba_free_space where tablespace_name like '%UNDO%' group by tablespace_name;

TABLESPACE_NAME SUM(BYTES/1024/1024)

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

APPS_UNDOTS1 49004

SQL> select tablespace_name,sum(BYTES/1024/1024) from dba_free_space where tablespace_name like 'APPS_DEM1' group by tablespace_name;

TABLESPACE_NAME SUM(BYTES/1024/1024)

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

APPS_DEM1 44371

How to find FND-LOG with Concurrent Request id <10543095>

spool /tmp/10543095.xls <EXCEL format>

select log_sequence, module,message_text

from fnd_log_messages

where transaction_context_id in (select transaction_context_id

from fnd_log_transaction_context

where transaction_id = 10543095)

order by log_sequence;

How to Use Spool

SQL> SET LINESIZE 999 VERIFY OFF FEEDBACK OFF PAGESIZE 50000
SQL > SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF

SQL> spool /tmp/sonu.xls

Run scripts

SQL> spool off

*******************PATCH SCRIPTS***************************

Before patching

Also fire below statement which will be useful to check new invalids if any after patching

create table system.INVALIDS_pre_<first_patch_number> as

SQL> select * from dba_objects where status='INVALID';

SQL> SELECT owner || ', ' || object_type || ', ' || object_name FROM sys.dba_objects WHERE status = 'INVALID' ;

Database Opatch check applied or not

$ cd /DEV/db/tech_st/11.2.0/OPatch

$ opatch lsinventory | grep 13393357

Find Opatch Applied or not

SQL> select bug_number,creation_date from ad_bugs where bug_number='13147471';

After patching - check for new invalids

col OBJECT_NAME for a30
set pages 199 line 199
select object_name,object_type,owner from dba_objects where status = 'INVALID' and (object_name,object_type) not
in (select object_name,object_type from INVALIDS_pre_<first_patch_number> );

Query to findout all applied patches.

=====================================

set lines 199

set pages 199

col NAME for a30

select distinct d.name ,f.bug_number,f.LAST_UPDATE_DATE,g.language, apps_system_name, d.SERVER_TYPE_ADMIN_FLAG, d.SERVER_TYPE_FORMS_FLAG, d.SERVER_TYPE_NODE_FLAG,

d.SERVER_TYPE_WEB_FLAG from apps.ad_patch_runs a, apps.ad_patch_drivers b,apps.ad_appl_tops d, apps.AD_PATCH_RUN_BUGS e, apps.ad_bugs f ,apps.AD_PATCH_DRIVER_LANGS g

where a.PATCH_DRIVER_ID = b.PATCH_DRIVER_ID

and f.bug_number in ('&patch' )

and a.appl_top_id=d.appl_top_id

and a.PATCH_RUN_ID = e.PATCH_RUN_ID

and e.bug_id = f.bug_id

and b.PATCH_DRIVER_ID = g.PATCH_DRIVER_ID

order by f.bug_number,d.name,g.language;

INPUT PATCH NO--

PRODUCTION Instance check Patch Applied not-

SQL> select bug_number,creation_date,ARU_RELEASE_NAME,BASELINE_NAME from apps.ad_bugs where bug_number='9718630'

Patch no-9718630

Check file version:-

$ adident Header POSSAB.pls

POSSAB.pls:

$Header POSSAB.pls 120.17.12010000.6 2011/09/20 05:25:34 ramkandu ship $

*********************SESSION SCRIPTS*************************

Check session status:-

SQL> select status from v$session where sid=1570;

STATUS

--------

INACTIVE

1 row selected.

SQL> select status from v$session where status= ‘INACTIVE’;

How long the session is active or inactive

set lines 100 pages 999

col sid for 9999

col serial# for 999999

select sid,serial#,username

, floor(last_call_et / 60) "Minutes"

, status

from v$session

where username is not null

order by last_call_et

/

Session Info with time, user and status

set lines 100 pages 999

col sid for 9999

col serial# for 999999

select sid,serial#,username

, floor(last_call_et / 60) "Minutes"

, status

from v$session

where username is not null

Query to find out inactive session:

set lines 100 pages 999

col sid for 9999

col serial# for 999999

select inst_id,sid,serial#,username

, floor(last_call_et / 60) "Minutes"

, status

from gv$session

where username is not null and sid in ('564','1002','1003')

order by last_call_et

/

select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser
from V$process p,V$session s
where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%';

Kill session

SQL> ALTER SYSTEM KILL SESSION '5049,30635'

Check Invalid status

SQL> SELECT owner || ', ' || object_type || ', ' || object_name FROM sys.dba_objects WHERE status = 'INVALID' ;

SQL> SELECT count(*) FROM dba_objects WHERE status LIKE 'INVALID';

Session info last 60 Min

select sid,serial#,username

, floor(last_call_et / 60) "Minutes"

, status

from v$session;

Query to find out all applied patches.

set lines 199

set pages 199

col NAME for a30

select distinct d.name ,f.bug_number,f.LAST_UPDATE_DATE,g.language, apps_system_name, d.SERVER_TYPE_ADMIN_FLAG, d.SERVER_TYPE_FORMS_FLAG, d.SERVER_TYPE_NODE_FLAG,

d.SERVER_TYPE_WEB_FLAG from apps.ad_patch_runs a, apps.ad_patch_drivers b,apps.ad_appl_tops d, apps.AD_PATCH_RUN_BUGS e, apps.ad_bugs f ,apps.AD_PATCH_DRIVER_LANGS g

where a.PATCH_DRIVER_ID = b.PATCH_DRIVER_ID

and f.bug_number in ('&patch' )

and a.appl_top_id=d.appl_top_id

and a.PATCH_RUN_ID = e.PATCH_RUN_ID

and e.bug_id = f.bug_id

and b.PATCH_DRIVER_ID = g.PATCH_DRIVER_ID

order by f.bug_number,d.name,g.language;

Find out Trace file Location:-

Enter user-name: apps

Enter password: XXXX

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,value from V$PARAMETER where name='user_dump_dest';

NAME VALUE user_dump_dest

/appl/QAEBS/db/tech_st/11.2.0/admin/IFFQAEBS_itstedb1/diag/rdbms/iffqaebs/IF

FQAEBS/trace

Request id "8403488" has been completed. As per the below query i got the "trace file number"

Select oracle_process_id from fnd_concurrent_requests where request_id=8403488

Please retrieve the trace file Trace IDs are 14308 and 11165

Sysadmin Resp>Profile>System
For the user, set FND: Diagnostics profile to Yes

2. GL Resp>Journals screen>Query the journal and click on Line Drilldown

3. On the page that opens, go to Diagnostics (shown in top right hand side of the page)

4. Choose 'Set Trace level' > Go >Trace with Binds > Save

5. Note down the trace id. Close window

6. Redo Line Drilldown and then View Transaction

7. Choose No trace from same Diagnostics link

8. Retrieve the trace file and upload to the SR along with tkprof.

Blocking Session

select

blocking_session,

sid,

serial#,

wait_class,

seconds_in_wait

from

v$session

where

blocking_session is not NULL

order by

blocking_session;

no rows selected

Script to find out Profile option Value:

SELECT b.user_profile_option_name “Long Name”,

a.profile_option_name “Short Name”,

DECODE (

TO_CHAR (c.level_id),

’10001′, ‘Site’,

’10002′, ‘Application’,

’10003′, ‘Responsibility’,

’10004′, ‘User’,

‘Unknown’

) “Level”,

DECODE (

TO_CHAR (c.level_id),

’10001′, ‘Site’,

’10002′, NVL (h.application_short_name, TO_CHAR (c.level_value)),

’10003′, NVL (g.responsibility_name, TO_CHAR (c.level_value)),

’10004′, NVL (e.user_name, TO_CHAR (c.level_value)),

‘Unknown’

) “Level Value”,

c.profile_option_value “Profile Value”,

c.profile_option_id

“Profile ID”,

TO_CHAR (c.last_update_date, ‘DD-MON-YYYY HH24:MI’) “Updated Date”,

NVL (d.user_name, TO_CHAR (c.last_updated_by))

“Updated By”

FROM apps.fnd_profile_options a,

apps.fnd_profile_options_vl b,

apps.fnd_profile_option_values c,

apps.fnd_user d,

apps.fnd_user e,

apps.fnd_responsibility_vl g,

apps.fnd_application h

WHERE b.user_profile_option_name LIKE ‘<Profile Name>’

AND a.profile_option_name = b.profile_option_name

AND a.profile_option_id = c.profile_option_id

AND a.application_id = c.application_id

AND c.last_updated_by = d.user_id(+)

AND c.level_value = e.user_id(+)

AND c.level_value = g.responsibility_id(+)

AND c.level_value = h.application_id(+)

ORDER BY b.user_profile_option_name,

C.level_id,

DECODE (

TO_CHAR (C.level_id),

’10001′, ‘Site’,

’10002′, NVL (h.application_short_name, TO_CHAR (C.level_value)),

’10003′, NVL (g.responsibility_name, TO_CHAR (C.level_value)),

’10004′, NVL (e.user_name, TO_CHAR (C.level_value)),

‘Unknown’

);

Current Locking sessions :-

SQL> select object_name, object_type, object_id, status, to_char(last_ddl_time, 'DD-MON-YYYY HH24:MM:SS') from dba_objects;

SQL> select object_name, object_type, object_id, status, to_char(last_ddl_time, 'DD-MON-YYYY HH24:MM:SS') from dba_objects

where

upper(object_name) = 'I_OBJ#';

SQL> Select SESSION_ID,LOCKED_MODE,PROCESS from v$locked_object;

Count Session-

Select count(*) from v$session;

NLS_SET

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

col name for a30

col USER_NM for a15

col ARGUMENT_TEXT for a40

col START_DT for a20

select request_id,"START_DT","EX_TIME",PHASE_CODE,STATUS_CODE from

(select distinct (a.request_id),b.user_concurrent_program_name NAME,c.user_name USER_NM,round((a.actual_completion_date-a.actual_start_date)*24*60,2) "EX_TIME",

a.actual_start_date "START_DT",a.actual_completion_date "COMPLETION_DT",a.ARGUMENT_TEXT,a.PHASE_CODE,a.STATUS_CODE

from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c

where a.concurrent_program_id=b.concurrent_program_id

and a.REQUESTED_BY=c.USER_ID

and a.phase_code='C'

and a.status_code='C'

and upper(b.user_concurrent_program_name) like upper('<request name>')

and trunc(a.actual_start_date) > trunc(sysdate-1000) order by "EX_TIME" desc ) where "EX_TIME" is not null and rownum < 10 ;

To FIND the log file

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 = 9225124

If a user asks you to give output in excel format (from sqlplus) . Please use below code/syntax before you fires the actual sql script given by user.

Alternatively you can use sqldeveloper also. (export to csv/excel format)

SQL> connect apps/*****

SET LINESIZE 999 VERIFY OFF FEEDBACK OFF PAGESIZE 50000

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF

spool </xyz/abc/.xls

.

<query_sent _by_user>

.

Spool off;

How to check different version in Oracle Apps R12

Database Version (QA)

Sql> DESC V$VERSION;

Name Null? Type

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

BANNER VARCHAR2(80)

SQL> select banner from v$version;

Application Version-

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME

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

12.1.3

Web Server/Apache or Application Server in Apps 11i/R12

Note- Log in as Application user, set environment variable and run below query

$ cd $IAS_ORACLE_HOME/Apache/Apache/bin/ <Path>

$ pwd

/iappl/IFFQAEBS/apps/tech_st/10.1.3/Apache/Apache/bin

$ httpd -version

Server version: Oracle-Application-Server-10g/10.1.3.5.0 Oracle-HTTP-Server

Server built: Aug 1 2009 02:21:47

Forms & Report version in R12

$ $ORACLE_HOME/bin/rwrun | grep Release

REP-0503: You did not specify the name of a report.

Report Builder: Release 10.1.2.3.0 - Production on Mon Mar 18 08:15:44 2013

$ rwrun | grep Release

REP-0503: You did not specify the name of a report.

Report Builder: Release 10.1.2.3.0 - Production on Mon Mar 18 08:24:48 2013

$ pwd

/appl/QAEBS/apps/tech_st/10.1.2/bin

Oracle Jinitiator in11i/R12/12i

$ cd $ORACLE_HOME

$ Java –version

Java version "1.6.0.00"

Java(TM) SE Runtime Environment (build 1.6.0.00-jinteg_12_nov_2007_21_58-b00)

Java Hot Spot(TM) Server VM (build 1.6.0.00 jinteg:11.13.07-11:26 IA64, mixed mode)

$ pwd

$ cd /iappl/IFFQAEBS/apps/tech_st/10.1.2/j2ee/home

$ java -jar oc4j.jar -version

Oracle Application Server Containers for J2EE 10g (10.1.2.3.0) (build 080228.2305.2133)

OS Level Command:-

$ vmstat 5

procs memory page fault s cpu

r b w avm free re at pi po fr de sr in sy cs us sy id

1 0 0 5141230 1855571 0 0 6 106 0 0 14 3480 53960 1518 37 4 59

1 0 0 5141230 1855482 0 0 6 0 0 0 0 2393 5234 658 0 1 99

1 0 0 5206086 1855482 0 0 5 0 0 0 0 2414 4869 682 1 1 99

1 0 0 5206086 1855482 0 0 0 0 0 0 0 2379 4831 644 0 0 99

2 0 0 5277737 1855481 0 0 1 0 0 0 0 2375 4492 648 0 1 99

2 0 0 5277737 1855481 0 0 0 0 0 0 0 2377 4654 650 1 0 99

1 0 0 5341183 1855481 0 0 0 0 0 0 0 2383 4661 675 1 2 97

1 0 0 5341183 1855481 0 0 6 0 0 0 0 2384 5468 654 0 0 99

1 0 0 5250461 1855481 0 0 0 0 0 0 0 2410 13028 684 1 1 98

How to create ADDM Report

Login to OEM Tool>>

OEM HOME>>Performance>>Related link—Advisor Central>>Advisors>>ADDM>>

Open ADDM page.

Upload RDA Diagnostic for Payables application

Go to Oracle Diagnostics
Choose Application = Applications DBA
Run System Snapshot - RDA
Choose Payables responsibility
Choose Application Shortname = SQLAP (Oracle Payables)
Mask sensitive data = Yes
APPS Schema Username = APPS
APPS Schema Password = <APPS PASSWORD>

Upload RDA Diagnostic for Sub ledger Accounting application

Go to Oracle Diagnostics
Choose Application = Applications DBA
Run System Snapshot - RDA
Choose Payables responsiblity
Choose Application Shortname = XLA
Mask sensitive data = Yes
APPS Schema Username = APPS
APPS Schema Password = <APPS PASSWORD>

Check for Blocking Locks

set linesize 300
set pagesize 300
SELECT lh.sid Locking_Sid, lh.ctime/60 "Lock_Held (In Mins)", lw.inst_id Waiting_Inst,
lw.sid Waiter_Sid, decode ( lh.type, 'MR', 'Media_recovery', 'RT', 'Redo_thread', 'UN', 'User_name', 'TX',
'Transaction','TM', 'Dml', 'UL', 'PLSQL User_lock', 'DX', 'Distrted_Transaxion', 'CF', 'Control_file', 'IS',
'Instance_state', 'FS', 'File_set', 'IR', 'Instance_recovery', 'ST', 'Diskspace Transaction', 'IV', 'Libcache_invalidation',
'LS', 'LogStaartORswitch', 'RW', 'Row_wait', 'SQ', 'Sequence_no', 'TE', 'Extend_table', 'TT', 'Temp_table', 'Nothing-' )
Waiter_Lock_Type,decode ( lw.request, 0, 'None', 1, 'NoLock', 2, 'Row-Share', 3, 'Row-Exclusive', 4, 'Share-Table', 5,
'Share-Row-Exclusive', 6, 'Exclusive', 'Nothing-' ) Waiter_Mode_Req
FROM v$lock lw, v$lock lh
WHERE lh.id1=lw.id1 AND lh.id2=lw.id2
AND lh.request=0 AND lw.lmode=0
AND (lh.id1,lh.id2) in
( SELECT id1,id2 FROM gv$lock WHERE request=0 INTERSECT SELECT id1,id2 FROM gv$lock WHERE lmode=0 )
/

Inactive session:-

Select SID, SERIAL#, PROGRAM, MACHINE, MODULE, SQL-VALUE, USERNAME, STATUS FROM V$session

Where program=’oracle_HHT_SALES@iprdebd1 (TNS V1-V3)’ and status like ‘INACTIVE’;

Identify all transactions with large amounts.

SELECT h.ENTITY_CODE,
h.EVENT_CLASS_CODE,
h.EVENT_TYPE_CODE,
h.LEGAL_ENTITY_ID,
h.header_id, ABS(SUM(trans_amount))
FROM gmf_xla_extract_headers h,
gmf_xla_extract_lines l
WHERE h.header_id = l.header_id
AND h.transaction_date >= '01-apr-13'
GROUP BY h.ENTITY_CODE,
h.EVENT_CLASS_CODE,
h.EVENT_TYPE_CODE,
h.LEGAL_ENTITY_ID,
h.header_id
HAVING ABS(SUM(trans_amount)) > 10

header_id

SELECT * FROM gmf_xla_extract_headers
            WHERE header_id IN (42853652);

            SELECT * FROM gmf_xla_extract_lines
            WHERE header_id IN (42853652);

Check PDOR Concurrent manager services

set lines 199 pages 199
COLUMN request_id HEADING 'Request ID' FORMAT 99999999;
COLUMN strttime HEADING 'Start|Time' FORMAT A17;
COLUMN endtime HEADING 'End|Time' FORMAT A17;
COLUMN rtime HEADING 'Elapsed|(Min)' FORMAT 9990.99;
COLUMN QNAME for a30
select q.concurrent_queue_name || ' - ' || target_node qname,
q.running_processes actual,
q.max_processes target,
sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,
sum(decode(r.phase_code,'P',1,0)) pending,
nvl(sum(decode(r.status_code,'W',1,'H',1,'S',1,'A',1,'Z',1,0)),0) paused,
nvl(sum(decode(r.status_code,'F',1,'Q',1,'B',1,'T',0)),0) influx,
avg((nvl(r.actual_start_date,r.requested_start_date) - r.requested_start_date)*1440) avgqtime
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_queues q
where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes > 0
and ((r.phase_code in ('R','P','I') and upper('N') = 'Y') or
upper('N') != 'Y')
group by q.concurrent_queue_name || ' - ' || target_node, q.running_processes, q.max_processes;

If u see Actual=target for all concurrent manageres

Concurrent manager services--> Working

PATCHING RELATED

SQL> select * from dba_objects;

SQL> select count(*) from dba_objects where status ='INVALID';

SQL> select * from dba_objects where status='INVALID'

SQL> select count(*) from dba_objects where owner = 'APPS' and status ='INVALID';

SQL> select * from dba_objects where status='INVALID';

SQL> select bug_number,last_update_date from ad_bugs where bug_number='13374062'

SQL> select bug_number,last_update_date from ad_bugs where bug_number='13006289';

BUG_NUMBER LAST_UPDATE_DATE

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

13006289 05-MAR-14

SQL> select patch_name, last_update_date from ad_applied_patches where patch_name='13374062'

SQL> select release_name,last_update_date,creation_date from fnd_product_groups

SQL> select bug_number,last_update_date from ad_bugs where trunc(LAST_UPDATE_DATE) >= '13-JUN-2011'

SQL> select PATCH_NAME,last_update_date from ad_applied_patches where trunc(LAST_UPDATE_DATE) >='08-JUN-2011'

USER ACCOUNT RELATED

SQL> select * from dba_users where username='APPS';

SQL> select username,account_status from dba_users where username='RAC_ACCNT';

SQL> select user_id,user_name,session_number from fnd_user where user_name=''

SQL> select username,password,account_status,lock_date from dba_users where username='APPS'

TABLE SPACES and GRANTS Related

SQL> select * from dba_tablespaces

SQL> select * from dba_temp_files

SQL> select tablespace_name from dba_tablespaces where tablespace_name='READONLY';

SQL> select username,DEFAULT_TABLESPACE from dba_users where username='ROAPPS'

SQL> revoke select on HR.AME_ACTION_TYPES_TL from ROAPPS

DATABASE LOCKS

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

no rows selected

SQL> desc dba_locks

Name Null? Type

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

SESSION_ID NUMBER

LOCK_TYPE VARCHAR2(26)

MODE_HELD VARCHAR2(40)

MODE_REQUESTED VARCHAR2(40)

LOCK_ID1 VARCHAR2(40)

LOCK_ID2 VARCHAR2(40)

LAST_CONVERT NUMBER

BLOCKING_OTHERS VARCHAR2(40)

SQL> desc v$locked_objects ;

SQL> desc dba_blockers

SQL> select holding_session from dba_blockers

SQL> select session_id,lock_type from dba_locks

SQL> select * from dba_ddl_locks

SQL> select * from v$lock where block<>0;

SQL> select * from v$session where sid =820;

SQL> select * from V$sqltext where hash_value='3533547372' order by piece

For checking what SQL, CM program is executing

SQL> select * from fnd_concurrent_requests where request_id='639447';

we need to take oracle_process_id from above , then SPID is oracle process id from the above query

SQL> select * from v$process where spid='15229';

Take paddr value from the above query

SQL> select * from v$session where paddr='00000000DC48C2D0';

Take SQL_HASH_VALUE from the above query

SQL> select * from V$sqltext where hash_value='2556871103' order by piece;

COMPILE APPS SCHEMA

SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'APPS');

APP Users Responsibilities

SQL> Select fu.user_id

,fu.user_name

,frt.RESPONSIBILITY_NAME

,fur.start_date Start_date

,fur.end_date End_Date

From fnd_user fu

,apps.FND_USER_RESP_GROUPS_ALL fur

,FND_RESPONSIBILITY_TL frt

Where fu.user_name= 'VIKRAM_MEHTA'

and fu.user_id=fur.user_id

and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID

GATHER SCHEMA STATISTICS on TABLES

Check Gather Stats date:-

SELECT LAST_START_DATE
FROM DBA_SCHEDULER_JOBS
WHERE job_name='GATHER_STATS_JOB';

LAST_START_DATE
----------------------------------------
14-FEB-07 10.00.02.657598 PM -05:00

exec dbms_stats.gather_table_stats(SYS, 'GL_JE_LINES', cascade => TRUE);

exec sys.dbms_stats.gather_table_stats(GL, 'GL.GL_JE_LINES', cascade => TRUE);

execsys.dbms_stats.gather_table_stats('GL','GL_JE_LINES',ESTIMATE_PERCENT=>100,CASCADE=>True);

analyze table gl.gl_je_lines estimate statistics sample 100 percent for table for all indexed columns forall indexes

analyze table gl.gl_balances estimate statistics sample 100 percent for table for all indexed columns forall indexes

select table_name,column_name,num_distinct,last_analyzed,num_buckets from user_tab_cols wheretable_name like ('%GL_JE_LINES%');

select table_name,index_name,num_rows,last_analyzed from user_indexes where table_name like('%GL_JE_LINES%');

TEMPLATES

SQL> select * from xdo_lobs where LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'XXWINCEADR%'

select * from xdo_lobs where 1=1-- LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'PO_STANDARD_PO%'

OTHERS

SQL> select * from dba_ddl_locks

where name like 'XXWIN_HCM%'

SQL> select * from v$lock

SQL> select * from v$session_wait

SQL> select * from fnd_concurrent_requests

SQL> select request_id, request_date,logfile_name from fnd_concurrent_requests where request_id='744042'

SQL> select * from fnd_nodes

To see employee name for the persons column (To add employee to the user)

select * from per_all_people_f where FULL_NAME like '%vic%'

select * from apps.fnd_lookup_values where lookup_type='XXWIN_SFTP_DETAILS'

select * from v$recover_file

To SEE what parameters assigned for CUSTOM CONCURRENT PROGRAMS

select * from FND_DESCR_FLEX_COL_USAGE_VL

where END_USER_COLUMN_NAME = 'p_debug_flag'

select * from fnd_concurrent_programs where concurrent_program_id=55455

select * from xxwin_datafile_validation_tab where data_file_name like 'UKJNL%'
select * from dba_temp_files

Checks to be performed while Concurrent program is running.

1) You need to first get the oracle session id by running the below sql with appropriate request_id.

select * from fnd_concurrent_requests where request_id='656309'

2) Oracle session id will be the input to the below query to find out the sql_id and SID.

select * from gv$session where AUDSID='2659367'

3) To find out the latest sql running , you need to input sql_id from the above query into the belowquery.

select * from gv$sqltext where sql_id='3sxmjmxtz9mt8' order by piece

4) Once the records in the table XXWIN_HCM_EMP_MASTER_STG_TAB is populated completely ( total records should be 196601) it will start writing into the file. You can get this confirmed by running thequery in step 3 and the output should be below which means it writing to the file.

SELECT * FROM XXWIN_HCM_EMP_MASTER_STG_TAB WHERE EXTRACT_ID = :B

AND REQUEST_ID=:B1

5) To check the number of records processed, you need to run the below query.

select count(*) from xxwin.XXWIN_HCM_EMP_MASTER_STG_TAB where status not like 'P'

6) Keep an eye on the file created in the interface_home outgoing directory. The file size should be gradually increasing.

-rw-r--r-- 1 ortwncti dbtwncti 112573157 Nov 24 16:23 INTHCM1_20111124121142_00001.dat

select count(*) from PO_LINE_LOCATIONS_ALL

select count(*) from po_headers_interface

select count(*) from po_lines_interface

select count(*) from po_distributions_interface

select username,default_tablespace,account_status from dba_users

where default_tablespace='SYSAUX';

select * from DBA_HIST_WR_CONTROL;

select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

Select * from v$sysaux_occupants;

This is to find the work flow inbound and outbound service names and mailer user names.

select c.component_id, c.component_name, c.component_status,

p.parameter_id, p.parameter_name, v.parameter_value value

from fnd_svc_comp_param_vals_v v,

fnd_svc_comp_params_b p,

fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER', 'ACCOUNT', 'REPLYTO')

order by c.COMPONENT_ID;

To see the Login url from OS level.

grep -i login $CONTEXT_FILE

Important Quearies to get the SID from PID:

select * from v$session, v$process

where addr=paddr and

v$process.spid in ('1178','1129')

To Query users:

select user_name,creation_date

from fnd_user

where end_date is null

and user_name like '%\_%' escape '\'

select trunc(start_time) "Date", count( distinct user_id) "Number of users logged in",

count( distinct user_id)*100/2503 "% of total users"

from fnd_logins

where user_id not in (1162,0)

group by trunc(start_time)

Blocking Sessions Query

check for blocking sessions...

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 ;

To check lock on PO table’s and AP table’s

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action fromv$locked_object,dba_objects,v$session

where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id

and SESSION_ID=sid;

To Check that the program has to be run with which responsibility.

SELECT responsibility_name
FROM fnd_responsibility_tl a,
       fnd_responsibility c,
       fnd_request_group_units d,
       fnd_concurrent_programs_tl b
WHERE     a.responsibility_id = c.responsibility_id
       AND c.request_group_id = d.request_group_id
       AND b.concurrent_program_id = d.request_unit_id
       AND UPPER (b.USER_CONCURRENT_PROGRAM_NAME) =
              UPPER ('Qtel CI TO IRB Interface Program');

Revoke Exceptional Privileges from a DB User.

SELECT 'revoke insert,update,delete '||owner||'.'||object_name||' from xxwin;' FROM dba_objectsWHERE object_type IN ('TABLE') and owner in ('APPS');

To See responsibilities of a User:

Select fu.user_id

,fu.user_name

,frt.RESPONSIBILITY_NAME

From fnd_user fu

,FND_USER_RESP_GROUPS_ALL fur

,FND_RESPONSIBILITY_TL frt

Where fu.user_id= 1218

and fu.user_id=fur.user_id

and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID

To Find out the DB patch list:

$ cd $ORACLE_HOME/OPatch

$ optach lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc >> dbpatchlist.txt

$ vi patch.list

To find out size of the Database:

select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

Lock on Table:

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id

TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:

set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';

Lock on sessions:

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action from v$locked_object,dba_objects,v$session
where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id
and SESSION_ID=sid;

Find out SID from OS PID:

select * from v$session where paddr in (select addr from v$process where spid =31665)

To Check for the SYSTEM process Associted with Concurrent Request

select distinct s.inst_id,S.TYPE,substr(''''||S.SID||','||S.SERIAL#||'''',1,15) SI_SE#, p.spid,s.process client_process,
s.MACHINE, s.TERMINAL,P.PID, G.NAME,P.PROGRAM,s.module,S.USERNAME,q.module qmodule ,s.command,s.osuser,
q.hash_value, substr(q.sql_text,1,30) SQL_TEXT,w.EVENT,W.WAIT_TIME,w.SECONDS_IN_WAIT,
--,w.p1,w.p2,W.STATE,
s.logon_time,t.start_time,
t.used_ublk, t.log_io,t.phy_io,t.cr_change,q.EXECUTIONS,
q.PLAN_HASH_VALUE, q.CHILD_NUMBER,
.LOGON_TIME,s.last_call_et, s.status
9 from gV$SESSION S, gV$SQL Q, gV$PROCESS P, gV$BGPROCESS G, gv$session_wait w,gV$TRANSACTION T
where S.SQL_ADDRESS = Q.ADDRESS (+) and S.SQL_HASH_VALUE = Q.HASH_VALUE (+)
and S.PADDR = P.ADDR
and S.PADDR = G.PADDR (+)
and s.sid=w.sid (+)
AND S.SADDR = T.SES_ADDR (+)
and s.process in ('&client_process_list')

To Check Business Events Exist or not:

select guid from wf_events where name ='xops.oracle.apps.gmd.rerollandcutbacks'

select *

from WF_BPEL_QTAB

where q_name = 'xops.oracle.apps.gmd.rerollandcutbacks'

To find out SPID:

SELECT s.sid, p.spid, s.osuser, s.program FROM v$process p,

v$session s WHERE p.addr = s.paddr

alter system kill session ’134,47747′ immediate;

SQL> select username,sid,serial#,terminal from v$session;

WFUPLOAD and WFDOWNLOAD

WFLOAD apps/X4wNG4rg 0 Y DOWNLOAD XXWIN_POWFRQAG.wft XXWINPOR

WFLOAD apps/X4wNG4rg 0 Y UPLOAD XXWIN_POWFRQAG.wft XXWINPOR

WFLOAD apps/X4wNG4rg 0 Y FORCE XXWIN_POWFRQAG.wft

select * from fnd_application_tl

Solution For the REP 3000, Report Cache Errors.

1- Shutdown the Concurrent Manager.
2- Manually delete all the temporary files under the /Base/logs/ora/10.1.2/reports/cache
3- Startup up the Concurrent Manager.

To Fix this Issue permanently.

Intermittent Oracle Reports REP-0069: REP-57054: Error (Doc ID 1237834.1).

note 1237834.1 and 1322704.1 mentioned in the support SR and come up the below action plan.

Action Plan
========
1. Stop the MT services
2. Review the patch readme and apply the patch 11669923, 11776182
3. change the cachesize parameter value from 50 to 0 within the file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf also add the <property name="noVoidedOutputError" value="yes"/>
<property name="cacheSize" value="50"/>
to
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
4. Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to $FND_TOP/admin/template/custom (ensure the file is not there in the custom directory before copy the file.
5. Add <property name="noVoidedOutputError" value="yes"/> to the $FND_TOP/admin/template/custom/rwbuilder_conf_1012.tmp and also change the cachesize value to 0.

in this file under the <cache class="oracle.reports.cache.RWCache"> section
ie:
<cache class="oracle.reports.cache.RWCache">
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
<property name="cacheDir"
value="%s_logs_dir%/ora/10.1.2/reports/cache"/>


</cache>

6. Start the MT services

Huge Pages on the Server:

The host is up and huge page looks good now:

[root@auohswnct08 ~]# cat /proc/meminfo |grep -i huge
HugePages_Total: 8100
HugePages_Free: 6825
HugePages_Rsvd: 6750
Hugepagesize: 2048 kB
[root@auohswnct08 ~]# sysctl -a |grep -i huge
vm.hugetlb_shm_group = 0
vm.nr_hugepages = 8100

The host is hung in console after starting using xm create :

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

[root@auod09m2al141r23 27118_auohswnct08]# xm create -c vm.cfg
Using config file "./vm.cfg".

Started domain 27118_auohswnct08 (id=13)
[root@auod09m2al141r23 27118_auohswnct08]# xm console 27118_auohswnct08

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

We tried booting from the snapshot of Mar 24 - which is the only available snapshot in this DOM0 , but did not help.

Tried starting the host with the huge page entry commented out as the host was rebooted after the huge page changes, but no luck.

Checking further with Maint SA

Remove the hold of concurrent requests from back end:

SQL> @CMGR_del_hold

SQL> update apps.fnd_concurrent_requests

2 set hold_flag = 'N'

3 WHERE

4 nvl(hold_flag,'N') = 'Y' and phase_code = 'P' and

5 request_id not in (SELECT request_id FROM apps.tmp_program_on_hold) ;

Workflow Quries:

SELECT COUNT (*), machine, process

FROM gv$session

WHERE program LIKE 'JDBC%'

GROUP BY machine, process

ORDER BY 2 ASC

Workflow processed Query

select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE;

imap services are fine
==============

spawn telnet auohswnct09.oracleoutsourcing.com 143
Trying 141.146.174.10...
Connected to auohswnct09.oracleoutsourcing.com (141.146.174.10).
Escape character is '^]'.
* OK Dovecot ready.
1 login wfpwncti wfpwncti
1 OK Logged in.
1 select INBOX
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk \*)] Flags permitted.
* 1 EXISTS
* 0 RECENT
* OK [UNSEEN 1] First unseen.
* OK [UIDVALIDITY 1300614072] UIDs valid
* OK [UIDNEXT 172901] Predicted next UID
1 OK [READ-WRITE] Select completed.
1 select PROCESS
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 1229 EXISTS
* 1229 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 1376] Predicted next UID

1 OK [READ-WRITE] Select completed.
1 select DISCARD
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 214 EXISTS
* 214 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 407] Predicted next UID
1 OK [READ-WRITE] Select completed.
IMAP Validation Success

The messages are processed successfully
==========
SQL> select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE;

MSG_STATE COUNT(*)
---------------- ----------
PROCESSED 7650

No messages are stuck in out bound queue
=================
SQL> select count(*) from applsys.wf_notification_out where state=0;

COUNT(*)
----------
0

Workflow Quries for JDBC Connections:

select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1800
and status = 'ACTIVE'
order by last_call_et asc

select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1800
and s.status = 'ACTIVE'

To Find out the Report Builder Version through command line:

Findings
========
$ rwrun help=yes | grep Release
REP-0503: You did not specify the name of a report.
Report Builder: Release 10.1.2.3.0 - Production on Fri Nov 2 02:20:56 2012
$ pwd
/pwncti/product/1012/OPatch
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

OPatch succeeded.
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 11669923

Workflow notification mailer Useful Scripts.

In Which node Running Workflow Notification

SQL> select target_node from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%';

TARGET_NODE

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

SRI_FIN02

Current workflow mailer log file location

SQL> select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;

Workflow log_file path

$APPLCSF/$APPLLOG/FNDCPGSC*.txt

bash-4.2$ pwd

/u01/oracle/prod/inst/apps/PROD02/logs/appl/conc/log

bash-4.2$ ls -lrt FNDC*

-rw-r--r-- 1 oracle dba 28710170 Apr 16 08:52 FNDCPGSC167995.txt

-rw-r--r-- 1 oracle dba 779053450 Apr 16 08:52 FNDCPGSC167990.txt

-rw-r--r-- 1 oracle dba 58267057 Apr 16 08:52 FNDCPGSC167989.txt

-rw-r--r-- 1 oracle dba 344758 Apr 16 09:10 FNDCPGSC168589.txt

-rw-r--r-- 1 oracle dba 34715995 Apr 16 09:10 FNDCPGSC168588.txt

-rw-r--r-- 1 oracle dba 296340 Apr 16 09:10 FNDCPGSC168587.txt

bash-4.2$ tail -l FNDCPGSC167989.txt

Check workflow mailer service current status

bash-4.2$ sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Apr 16 10:17:16 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: apps
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';  2    3

RUNNING_PROCESSES
-----------------
                1


  Number of running processes should be greater than 0

Find current mailer status

  sqlplus apps/<apps password>
  select component_status
    from apps.fnd_svc_components
   where component_id = 
        (select component_id
           from apps.fnd_svc_components
          where component_name = 'Workflow Notification Mailer');

COMPONENT_STATUS ------------------------------ RUNNING

Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM

Stop notification mailer

  sqlplus apps/<apps password>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Stop Mailer
       --------------
       fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /

Start notification mailer

  sqlplus apps/<apps password>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Start Mailer
       --------------
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /

How to Upload & Download Workflow (wft) Files in R12

WFLOAD Utility :-

oracle has provided WFLOAD for workflow files. Below are the available options -

To upgrade - WFLOAD apps/pwd 0 Y UPGRADE filename.wft

To upload - WFLOAD apps/pwd 0 Y UPLOAD filename.wft

To force - WFLOAD apps/pwd 0 Y FORCE filename.wft

To download - WFLOAD apps/pwd 0 Y DOWNLOAD filename.wft

Workflow DOWNLOAD WFLOAD apps/$passwd 0 Y DOWNLOAD XXWFITEMTYPE.wft
UPGRADE WFLOAD apps/$pwd 0 Y UPGRADE xxwfitemtype.wft
FORCE WFLOAD apps/$pwd 0 Y FORCE xxwfitemtype.wft

Log location same place

Use pwd command and find log file in same location.

Location wft files are stored in server:-

$PRODUCT_TOP/patch/115/import/<LANG> directory.

Standard Concurrent Program – Run the program Workflow Definitions Loader using sysadmin responsibility (you can add this program to any of responsibility and run from there).

This program has 3 parameters as below

1 Mode – Upload/Download

2 file - /staging/sandeep/Project/PAPROWF.wft

(wft file name with Full Path. Make sure file is available at given path for UPLOAD Mode)

3 I Item Type – Item type Name for above file (This can be get from workflow builder. Open your wft file in workflow builder and create any process. The first attribute will be Item Type Name).

Oracle Password expiry notification

set pagesize 500

set linesize 200

set trimspool on

column "EXPIRE DATE" format a15

select username as "USER NAME", expiry_date as "EXPIRE DATE", account_status

from dba_users

where expiry_date < sysdate+3

and account_status IN ( 'OPEN', 'EXPIRED(GRACE)' )

order by account_status, expiry_date, username;

How to delete Trace file or .trm file before 5 days

$ cd /ora11/PROD/db/tech_st/11.1.0/admin/PROD/diag/rdbms/prod/PROD/trace

$ find . -name '*.trm' -mtime +5 | xargs rm -f

$ find . -name '*.trc' -mtime +5 | xargs rm -f

How to delete expired archive log files using rman?

The following commands will helpful to delete the expired archive log files using Oracle Recovery Manager(RMAN).Connect to the Rman prompt and try the bellow commands.

RMAN>list expired archivelog all;

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

RMAN>list expired archivelog all;

Voting Disk Backup and Recovery

Voting disk manages node membership information and it is used by Cluster synchronization services demon (CSSD).

Backing up Voting Disks:-
Run the below command to back up the voting disk.
$ dd if=voting_disk_name of=backup_file_name
or
$ dd if=voting_disk_name of=backup_file_name bs=4k

Recovering Voting Disks:-
Run the below command to recover a voting disk
$ dd if=backup_file_name of=voting_disk_name

You can change the Voting Disk Configuration dynamically after the installation.
Please note that you need to run the command as “root”.

Run the below command to add a voting disk:
# crsctl add css votedisk_path

You can have upto 32 Voting disks.

Run the following command to remove a voting disk:
# crsctl delete css votedisk_path

View status of OCR disks and Voting disks

sudo /app/oracle/product/crs/bin/ocrcheck

Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     306972
         Used space (kbytes)      :       5880
         Available space (kbytes) :     301092
         ID                       : 746041401
         Device/File Name         : /dev/oracle/disk500
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/oracle/disk501
                                    Device/File integrity check succeeded
          Cluster registry integrity check succeeded
          Logical corruption check succeeded

crsctl query css votedisk

0. 0 /dev/oracle/disk502

1. 0 /dev/oracle/disk503
2. 0 /dev/oracle/disk504
Located 3 voting disk(s).

Add Voting Disk :

# crsctl add css votedisk

To remove a voting disk:

# crsctl delete css votedisk

If Oracle Clusterware is down on all nodes, use the –force option:

# crsctl add css votedisk -force
# crsctl delete css votedisk -force

Backup Voting Disk :

$ crsctl query css votedisk
$ dd if= of= bs=4k

OCR BACKUP :

Oracle RAC Taking Backup For OCR By Default , You will Find It On :

cd $ORACLE_BASE/Crs/cdata/jfv_clus

Change the default automatic backup location:

# ocrconfig –backuploc /shared/bak
Back Up OCR Manually :

# ocrconfig –export file name

Recover OCR Using Physical Backups:

1.Locate a physical backup:

$ ocrconfig –showbackup

2.Review its contents:

# ocrdump –backupfile file_name

3.Stop Oracle Clusterwareon all nodes:

# crsctl stop crs

4.Restore the physical OCR backup:

# ocrconfig –restore <CRS HOME>/cdata/jfv_clus/day.ocr

5.Restart Oracle Clusterware on all nodes:

# crsctl start crs

6.Check OCR integrity:

$ cluvfy comp ocr -n all

Workflow	DOWNLOAD	WFLOAD apps/$passwd 0 Y DOWNLOAD XXWFITEMTYPE.wft
	UPGRADE	WFLOAD apps/$pwd 0 Y UPGRADE xxwfitemtype.wft
	FORCE	WFLOAD apps/$pwd 0 Y FORCE xxwfitemtype.wft

My scripts

Enabling and Disabling Maintenance Mode

Enabling and Disabling SELinux

Enabling/Disable SELinux

OR

Back-end script:-

select * from fnd_profile_options fp,fnd_application fa where fa.application_short_name like 'XX%' and fa.application_id=fp.application_id;

To find out which Oracle e-business user is locking a table

How do I restart Linux network service?

How do I get a list of locked users in an Oracle database?

SQL> alter tablespace APPS_DATA add datafile '/idata1/QA/data1/a_txn_data47.dbf' size 10G;

SQL> ALTER DATABASE DATAFILE '/u01/oradb/db/apps_st/data/system09.dbf' RESIZE 800M;

SQL> alter tablespace drop datafile 'system09.dbf';

SQL> select file_name,status , online_status from dba_data_files;

SQL> alter database datafile '/u01/users02.dbf' offline;

SQL> ! rm -rf /u01/users02.dbf

System altered.

SQL> ALTER DATABASE DATAFILE '/u01/oradb/db/apps_st/data/SYSTEM10012014e.dbf' AUTOEXTEND OFF;Database altered.

SQL> ALTER DATABASE DATAFILE '/u01/oradb/db/apps_st/data/SYSTEM10012014e.dbf' AUTOEXTEND on;

Database altered.

SQL> SELECT FILE_NAME, BYTES/(1024*1024), MAXBYTES/(1024*1024),AUTOEXTENSIBLE from DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

Table-spaces having less than 20% free space

How to run AWR, ADDM and ASH Reports Run the following scripts from SQLPLUS SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

How to run AWR, ADDM and ASH Reports

Run the following scripts from SQLPLUS

How to compile apps schema in Oracle applications R12

How to compile apps schema (or invalid objects in database) in Oracle applications R12

How to kill Inactive Session

SQL>spool on

SQL> spool /u01/db//session1.sql

SQL>select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';

SQL>spool off

SQL> @/u01/db//session1.sql

How to get Client IP address in oracle apps.

How to check OS level configuration (Disk space,Host name,RAM,OS version etc)

[ora@sujeet ~]$ hostname

[ora@sujeet ~]$ df -h[ora@sujeet ~]$ free -m

[ora@sujeet ~]$ cat /proc/version[ora@sujeet ~]$ uname -a

[ora@sujeet ~]$ uname -mrs# free# free -m# free -mt# free -gt# lscpuHow do I find how long ago a Linux system was installed?[root@sujeet ~]# ls -alct /|tail -1|awk '{print $6, $7, $8}'Dec 19 2013

[root@sujeet ~]# tune2fs -l /dev/sda1tune2fs 1.39 (29-May-2006)

PRODSQL> select * from v$dataguard_status;no rows selected

How to create User Read_only in oracle?

Hear User_id=sonu password=sona

Create User:-

SQL> create user sonu identified by sona;

User created.

Give Permission Read only:-

SQL> grant create session,select any dictionary,select any table to sonu;

Grant succeeded.

Now check with same user its connected or not:-

SQL> conn sonu/sona

Connected.

SQL> sho user

USER is "sonu"

Verified user Privileges:-

SQL> create table t(id number);

create table t(id number)

*ERROR at line 1:

ORA-01031: insufficient privileges

Find sid using pid get from os prompt using top command

Find sid using pid get from os prompt using top command

Query to find locked tables in 11g.

Steps for releasing lock on a table :< APPS>

How to check different version in Oracle Apps R12

How to Upload & Download Workflow (wft) Files in R12

Oracle Password expiry notification

How to delete expired archive log files using rman?

Voting Disk Backup and Recovery

View status of OCR disks and Voting disks

No comments:

Size of schema in Oracle database 23AI

SQL> ALTER DATABASE DATAFILE '/u01/oradb/db/apps_st/data/SYSTEM10012014e.dbf' AUTOEXTEND OFF;
Database altered.

SQL> SELECT FILE_NAME, BYTES/(10241024), MAXBYTES/(10241024),AUTOEXTENSIBLE from DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

How to run AWR, ADDM and ASH Reports

Run the following scripts from SQLPLUS

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

[ora@sujeet ~]$ df -h
[ora@sujeet ~]$ free -m

[ora@sujeet ~]$ uname -mrs
# free
# free -m
# free -mt
# free -gt
# lscpu
How do I find how long ago a Linux system was installed?
[root@sujeet ~]# ls -alct /|tail -1|awk '{print $6, $7, $8}'
Dec 19 2013

[root@sujeet ~]# tune2fs -l /dev/sda1
tune2fs 1.39 (29-May-2006)

PROD
SQL> select * from v$dataguard_status;
no rows selected