Oracle Applications R12.2.x| Exadata

Thursday, September 19, 2024

DOC IDs:

Query To Get Enabled Trace/Log/Debug Profile Options (Doc ID 559618.1)

Wednesday, August 14, 2024

HIST

SELECT SNAP_ID ,

DBID ,

INSTANCE_NUMBER ,

SAMPLE_ID ,

to_char(SAMPLE_TIME,'DD-MON-YYYY HH24:MI:SS') ,

SESSION_ID ,

SESSION_SERIAL# ,

USER_ID ,

SQL_ID ,

SQL_CHILD_NUMBER ,

SQL_PLAN_HASH_VALUE ,

FORCE_MATCHING_SIGNATURE ,

SQL_OPCODE ,

PLSQL_ENTRY_OBJECT_ID ,

PLSQL_ENTRY_SUBPROGRAM_ID ,

PLSQL_OBJECT_ID ,

PLSQL_SUBPROGRAM_ID ,

SERVICE_HASH ,

SESSION_TYPE ,

SESSION_STATE ,

QC_SESSION_ID ,

QC_INSTANCE_ID ,

BLOCKING_SESSION ,

BLOCKING_SESSION_STATUS ,

BLOCKING_SESSION_SERIAL# ,

EVENT ,

EVENT_ID ,

SEQ# ,

P1TEXT ,

P1 ,

P2TEXT ,

P2 ,

P3TEXT ,

P3 ,

WAIT_CLASS ,

WAIT_CLASS_ID ,

WAIT_TIME ,

TIME_WAITED ,

CURRENT_OBJ# ,

CURRENT_FILE# ,

CURRENT_BLOCK# ,

PROGRAM ,

MODULE ,

ACTION ,

CLIENT_ID ,

FLAGS

FROM dba_hist_active_sess_history WHERE sample_time BETWEEN TO_DATE('14-AUG-2024 16:00','DD-MON-YYYY HH24:MI')

AND TO_DATE('14-AUG-2024 20:00','DD-MON-YYYY HH24:MI')

and client_id='RAM';

SELECT A.REQUEST_ID, D.SID, D.SERIAL# , 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 = 129634975

AND A.PHASE_CODE = 'R';

select session_id, session_serial#, module, sql_id, sql_plan_hash_value, count(*)*10 runtime_sec, min(sample_time), max(sample_time)

from dba_hist_active_sess_history

where 1=1

and (session_id, session_serial#) in ((129615845,10613))

group by session_id, session_serial#, sql_id, sql_plan_hash_value, module

order by count(*)*10 desc

Friday, July 19, 2024

Concurrent Program - Quick Queries

Trace enable program details:

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",

SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",

SUBSTR(B.USER_NAME,1,15) "Last Updated By",

SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION

FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B

WHERE A.ENABLE_TRACE='Y'

AND A.LAST_UPDATED_BY=B.USER_ID;

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT
USER_CONCURRENT_PROGRAM_NAME,
RESPONSIBILITY_NAME,
REQUEST_DATE,
ACTUAL_START_DATE,
ACTUAL_COMPLETION_DATE,
ARGUMENT_TEXT,
REQUEST_ID,
PHASE_CODE,
STATUS_CODE,
LOGFILE_NAME,
OUTFILE_NAME,
OUTPUT_FILE_TYPE
FROM
FND_CONCURRENT_REQUESTS FCR,
FND_CONCURRENT_PROGRAMS_TL FCP,
FND_RESPONSIBILITY_TL FR,
FND_USER FU
WHERE
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FCR.REQUESTED_BY = FU.USER_ID and ACTUAL_START_DATE='05-OCT-2024 10:10:10'
ORDER BY REQUEST_DATE DESC;

SELECT distinct t.user_concurrent_program_name,

r.REQUEST_ID,u.USER_NAME,

to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",

to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime,

decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,

decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',

'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters"

FROM

apps.fnd_concurrent_requests r ,

apps.fnd_concurrent_programs p ,

apps.fnd_concurrent_programs_tl t,

apps.fnd_user u, apps.fnd_conc_req_summary_v v

WHERE

r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID

AND r.actual_start_date >= (sysdate-103)

--AND r.requested_by=22378

AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID

AND t.concurrent_program_id=r.concurrent_program_id

AND r.REQUESTED_BY=u.user_id

AND v.request_id=r.request_id

--AND r.request_id ='2260046' in ('13829387','13850423')

and t.user_concurrent_program_name like '%Purge%'

order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');

Kill all processes of FNDLIBR.

ps -ef |grep FNDLIBR | grep -v grep | awk '{print $2}' | xargs kill -9

SELECT a.request_id,

d.sid,

d.serial#,d.sql_id,

d.osuser,

c.spid AS db_spid,

d.inst_id AS rac_instance

FROM apps.fnd_concurrent_requests a

JOIN apps.fnd_concurrent_processes b ON a.controlling_manager = b.concurrent_process_id

JOIN gv$process c ON c.pid = b.oracle_process_id

JOIN gv$session d ON d.audsid = b.session_id

WHERE a.request_id in ('142555872','142555871');

SELECT concurrent_program_id,

user_concurrent_program_name

FROM fnd_concurrent_programs_tl

WHERE user_concurrent_program_name LIKE '%TAS%BETC%';

UPDATE fnd_concurrent_requests

SET phase_code = 'C', -- Completed

status_code = 'X', -- Terminated

completion_text = 'Forcefully terminated by Ram'

WHERE concurrent_program_id = 346365

AND phase_code = 'R';

COMMIT;

Wednesday, July 17, 2024

Patch

Pre Patch Analysis:

SELECT   a.bug_no,
          app.application_name,
           a.DIRECTORY,
           a.filename,
           a.old_version,
           a.new_version,
           DECODE (a.typeid,
                   'not applied',
                   'Unchanged File',
                   'upgrade',
                   'Changed File',
                   'new',
                   'New')
              change_type
    FROM   apps.fnd_imp_psmaster_vl a, apps.fnd_application_vl app
   WHERE   a.bug_no in ('35865350','36033961','36178678','36211820','36247239','36328493','36349505','36370240','36397384','36548655','36564407','36592636','36606890','36214033','36591466')
           AND app.application_short_name = a.app_short_name
ORDER BY   DECODE (a.typeid,
                   'not applied',
                   'Unchanged File',
                   'upgrade',
                   'Changed File',
                   'new',
                   'New') ASC;

srvctl commands:

srvctl status database -d $ORACLE_UNQNAME

srvctl stop database -d $ORACLE_UNQNAME

srvctl start database -d $ORACLE_UNQNAME

Tuesday, July 9, 2024

The Online Patching Readiness report has identified XXX tables in the YYY schemas that should have editioned views.

How should these be handled?

How To Check External Dependencies On Possible Obsolete Schemas With E-Business Suite (Doc ID 1995384.1)

The following SQL script can be used to output a list of Obsolete products.

select a.application_id, a.application_short_name, at.application_name
from apps.fnd_application a
, apps.fnd_application_tl at
where a.APPLICATION_ID = at.APPLICATION_ID
and language='US'
and application_name like '%(_bsolete)%'
order by APPLICATION_NAME;

-- show external dependencies on possibly obsolete schema
set pagesize 1000
set linesize 200
column owner format a15
column name format a30
column type format a18
column referenced_name format a30
column referenced_type format a18

select owner, name, type, referenced_name, referenced_type
from dba_dependencies
where referenced_owner = '<OBSOLETE_SCHEMA_NAME>'
 and owner <> referenced_owner
order by 1,2,3
/

select owner, name, type
from dba_dependencies
where referenced_owner = '<TARGET_OWNER>'
  and referenced_name  = '<TARGET_NAME>'
  and referenced_type   = '<TARGET_TYPE>'
order by 1,2,3
/

Thursday, May 16, 2024

Quick Tit Bits

Quick Tit Bits:

select inst_id,sid,serial# from gv$session

alter system kill session 'sid,serial,@inst_id' IMMEDIATE;

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where username like '%S%';

Stale Stats:

set linesize 200

--set heading off

set pagesize 200

break on owner

select distinct owner,table_name object_name,'TABLE' object_type, partition_name,stale_stats,LAST_ANALYZED

from all_tab_statistics

where STALE_STATS='YES'

AND stattype_locked is null

and owner in ('APPLSYS','GL','AP','AR','PO','PA','FV','FA','HR','UFMS','DOCVIEW','XLA','XXLAAPPS','MARKVIEW')

and table_name not like 'BIN%'

order by owner;

select distinct owner,index_name object_name,'INDEX' object_type, partition_name,stale_stats,LAST_ANALYZED

from all_ind_statistics

where STALE_STATS='YES'

AND stattype_locked is null

and owner in ('APPLSYS','GL','AP','AR','PO','PA','FV','FA','HR','UFMS','DOCVIEW','XLA','XXLAAPPS','MARKVIEW')

and index_name not like 'BIN%'

and index_name not in ('MVCN_ELM_UNPARSED_VALUE_NTI','MVCN_ELM_SUGGESTED_VALUE_NTI','MVCN_ELM_IMPORTED_VALUE_NTI','MVCN_SMG_MESSAGE_ATTRIBUTEI')

order by owner;

cd $FMW_HOME/utils/bsu

./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -view -status=applied

dbcli describe-component

dbcli list-jobs

oci db patch list by-database --database-id $DB_OCID --profile DEV --all

$ oci work-requests work-request list -c $COMP_OCID --profile DEV

opatch lsinv | grep -i applied

cat $FMW_HOME/wlserver_10.3/.product.properties | grep WLS_PRODUCT_VERSION

/dRAM1o/fmw/product/12214/wlserver/server/lib

[oracle@lufRAMfgrcdl011 lib]$ java -cp weblogic.jar weblogic.version

java -cp weblogic.jar weblogic.version -verbose

Middle Tier:

[oracle@luframfebstl011 ~]$ $COMMON_TOP/util/jdk64/bin/java -d64 -version

java version "1.7.0_391"