WORKFLOW RELATED :

to set the overrides from backend:

update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address');

TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER :

--Set the override address to  'xyz@abc.com' , using the following script:
--Login to the application server..
cd $FND_TOP/sql

@afsvcpup.sql

--Check the workflow services using the below query.
set pagesize 400
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

How to get active session ------------

select sid,serial#,module,machine,last_login_time from v$session where status='ACTIVE'

TO FIND OUT LONGOPS SESSION :===

SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
 TRUNC(elapsed_seconds, 5) "ET (s)", TO_CHAR(start_time, 'HH24:MI:SS') start_time,
 ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
 FROM v$session_longops a, v$session b
 WHERE a.sid = b.sid AND b.username not IN ('SYS', 'SYSTEM') AND totalwork > 0
 ORDER BY elapsed_seconds;

TO GET INVALID OBJECTS:

select owner,object_name,object_type from dba_objects where status='INVALID'

TO FIND OUT SESSION FROM CONCURRENT REQ ID :

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id in ('27222682','27223376');

TO FIND TABLESPACE AGAINST TABLE :

select table_name,tablespace_name from all_tables where table_name = 'EMP';

to get blocking session in oracle

SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;
 
 SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
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 O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 alter system kill session '5031,2459';

++++++++++++++++++++ to find session details from concurrent req +++++++++++++++

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =26916095;

Hello

Hello everyone

We are going to start the blog to understand the DBA terminology and solutions for any issue regarding database and ERP (oracle Apps )  .
Here we will learn how to approach to troubleshot a problem related to db and ERP in different version released by Oracle.

As we know we have a different version in database and application (ERP). There are below listed versions used in different organisation as per their requirements.

DB version:

oracle 9i  ---   i stand for internate
oracle 10g  ------ G stand for Grid
oracle 11g
oracle 12c    ----- C stand for cloud

Application version:

oracle Apps 11.5.9
oracle Apps 11.5.10.2 ---- This is lattest version of 11i architecture
oracle Apps 12.0.4  ------ initial version for r12 Apps architecture
oracle Apps 12.1.1
oracle Apps 12.1.3
oracle Apps 12.2.3
oracle Apps 12.2.4  ------latest version for r12