Port Usage - Conflicts

 netstat -tunpl|grep <port#>

ps -ef|grep -i <Listen/Port#>

netstat -anp|grep <port#>

ECC RUP Patch Installation

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

PATCHING INSTRUCTIONS
==========================================================================
The patch is created for two purposes:

Purpose 1) Apply the RUP along with V12 Installation: Follow the steps I to III, before V12 installation/upgradation is started. Then follow the installation process as mentioned in the ECC Installation guide.

Purpose 2) Apply the RUP after V12 is installed: Follow the steps I to IV. The pre-requisite is that V12 ECC instance must be up and running.


STEPS FOR PATCHING PROCESS
==========================================================================

Step I) Copy the patch p37358526_R12_LINUX.zip on the ECC tier to /tmp directory

Step II) Extract the zip file to $ECC_BASE  where the V12 quickInstall is extracted. The zip file contains
     ecc.war
     applyPatch.sh 
     readme.txt
     p37358526_R12_LINUX.bsx

Step III) p37358526_R12_LINUX.bsx has latest changes for the above mentioned enhancements/bugs. Execute the following command to extract the bsx:

     sh p37358526_R12_LINUX.bsx

Step IV) The following step should be executed only if V12 is already installed. Follow either one of the options to apply the ecc.war from the patch:

     OPTION 1: 
          This requires execution of the script and consequently ECC Managed server will be restarted. Following are the steps to be performed to apply the patch:

          1.1) Source the env file at $ECC_BASE/Oracle/quickInstall/env/ecc.env
               source $ECC_BASE/Oracle/quickInstall/env/ecc.env

          1.2) Go to the directory where the zip was extracted and execute the applyPatch.sh file.

          1.3) This script accepts ECC weblogic domain admin credentials and ECC schema credential as arguments during script execution (Example: $./applyPatch.sh <ADMIN_PASSWORD> <ECC_SCHEMA_PASSWORD>) or accepted as user input.
                  1.4) As part of script execution:
                        a. ECC Managed server is stopped.
                        b. Current war is backed up and undeployed ; the latest war(extracted from the patch) is deployed.
                                c. ECC schema changes are done
                                d. zookeeper changes are done 
                        c. Managed server is started.
                  1.5) As part of the script execution:
                a. ECC Managed server is stopped.
                b. Current war is backed up and undeployed and the latest war (extracted from the patch) is deployed.
                d. Current zookeeper is backed up and upgrade to the latest zookeeper (extracted from the patch).
                c. ECC Managed server is started.
     OPTION 2 :
          This requires manual deployment of the latest ecc.war. Following are the steps to be performed to deploy the war:

          2.1) Backup the original ECC application war deployed from $ECC_BASE/Oracle/Middleware/user_projects/applications/ecc_domain/ecc.war
          2.2) Replace the current ecc.war in $ECC_BASE/Oracle/Middleware/user_projects/applications/ecc_domain/ecc.war with the new $ECC_BASE/ecc.war
          2.3) Login to the ECC weblogic domain admin console.
          2.4) Click on Lock & Edit.
          2.5) Navigate to "Deployments" section
          2.6) Select the checbox for "ecc" deployment and click on "Update".
          2.7) As we have already replaced the war with the new one in 2.2, click on "Finish" button
          2.8) Then, click on "Activate Changes" button.

ECC Issues:

 ECC - Not working Node 1 unable to to connect

Timezone Issue:

1. Source $ECC_BASE/Oracle/quickInstall/env/ecc.env
2. Open $ECC_BASE/Oracle/quickInstall/EccConfig.properties
3. Change the below value:

From:
TIMEZONE=EST

To:
TIMEZONE=America/New_York

4. Go to $ECC_BASE/Oracle/quickInstall/env/ecc/ecc-config.properties and update below values.
From:

# Timezone set to sync the ECC with the source system timezone
timezone.id=EST

To:

# Timezone set to sync the ECC with the source system timezone
timezone.id=America/New_York

5. Save the files and run the following commands to run the script:

cd $ECC_BASE/Oracle/quickInstall
./createEnvFile.sh

6. Restart eccManaged services

7. Relaunch Application and log in with sysadmin and now try to access ECC Developer
NOTE: It is always recommended to use the “long form” of the Timezone which is outlined in the Globalization Guide for Oracle Applications Release 12 ( Doc ID 393861.1 ) under the time zone which is applicable for ECC as well.

Reference: ECC Developer Page Is Blank After Performing New Installation And Console Log Errors"Uncaught RangeError: Invalid time zone specified: XXX" ( Doc ID 2950249.1 )

OHS

 $FMW_HOME/webtier/instance/EBS_web_OHS*

find . -name *.log

$FMW_HOME/webtier/instance/EBS_web_OHS*/diagnostics/logs/OHS/EBS_web/EBS_web.log

[oracle@luframfebstl312 <RUN> ]$ diff mod_wl_ohs.conf mod_wl_ohs.conf_bkp_11141258

[oracle@luframfebstl312 <RUN> ]$ pwd

/u01/install/APPS/fs1/FMW_Home/webtier/instances/EBS_web_OHS1/config/OHS/EBS_web

Y - Queries

 

-- Find concurrent request that had performance issue

select trunc((nvl(actual_completion_date, sysdate) - actual_start_date)*24*60, 2) runtime_minutes,frt.responsibility_name, fcpt.user_concurrent_program_name, fcpt.concurrent_program_name, fcr.actual_start_date, fcr.actual_completion_date, 

fcr.phase_code, fcr.status_code, fe.execution_file_name, fcr.argument_text, fcpt.executable_id

from fnd_concurrent_requests fcr

    ,apps.fnd_concurrent_programs_vl fcpt

    ,apps.fnd_responsibility_tl frt

    ,apps.fnd_executables fe

where fcpt.concurrent_program_id = fcr.concurrent_program_id

  and fe.executable_id = fcpt.executable_id

 -- and fcpt.user_concurrent_program_name like 'PSC GL Reference Update for 190  Type Invoices%'

--  and responsibility_name like '%PSC%'

  and fcr.responsibility_id = frt.responsibility_id

  and fcr.actual_start_date is not null

   and fcr.request_id='107472634'

--  and phase_code = 'R'

and request_date > sysdate - 90

order by fcr.actual_start_date desc 

718.03 PSC Grants Interface Grants PMS Synchronization Report (Un-reconciled) HHSGRTUN 8/3/2022 12:34:46 PM 8/4/2022 12:32:48 AM C C HHSGRTUN 2937, 3, 31-AUG-2022, , N 10469

-- Find sid/serial# based on start and end times

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

from dba_hist_active_sess_history

where 1=1

  and sample_time > sysdate - 60

  and sample_time BETWEEN TO_DATE('03-AUG-2022 08:27','DD-MON-YYYY HH24:MI')

AND  TO_DATE('03-AUG-2022 11:32','DD-MON-YYYY HH24:MI') 

--and session_id=1026 and session_serial#=22860

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

order by min(sample_time) desc

8972 31528 e:PSCCUST:cp:psccust/PSCTC190GLUPD 23680 20/JUN/2022 7:30:37.907 PM 20/JUN/2022 10:48:14.575 PM

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 ((5220,27142))

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

order by count(*)*10 desc

8972 31528 e:PSCCUST:cp:psccust/PSCTC190GLUPD 413kvqbrw070v 1309738701 23680 20/JUN/2022 7:30:37.907 PM 20/JUN/2022 10:48:14.575 PM

-- Look at history of sql plans for problem sql_id

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

from dba_hist_active_sess_history

where sql_id = 'a5tws7tsuc9zh'

group by session_id, session_serial#, sql_id, sql_plan_hash_value

order by min(sample_time) desc

select a.snap_id,begin_interval_time,end_interval_time,sql_id,plan_hash_value

from dba_hist_snapshot a, dba_hist_sqlstat b

where a.snap_id=b.snap_id

and a.dbid=b.dbid

and a.instance_number=b.instance_number

and b.sql_id='a5tws7tsuc9zh'

and a.begin_interval_time>sysdate-60

order by snap_id;

select 

  SQL_ID 

, PLAN_HASH_VALUE 

, sum(EXECUTIONS_DELTA) EXECUTIONS

, sum(ROWS_PROCESSED_DELTA) CROWS

, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS

, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS

from DBA_HIST_SQLSTAT 

where SQL_ID in (

'f1k7kq8an7ktn') 

group by SQL_ID , PLAN_HASH_VALUE

order by SQL_ID, CPU_MINS;

f1k7kq8an7ktn 287300685

select *

from dba_hist_sqlbind

where sql_id = '413kvqbrw070v'

-- Check for wait events

select NVL(a.event, 'ON CPU') AS event, a.session_id, a.session_serial#,

        COUNT(*)*10 AS total_wait_time, min(sample_time), max(sample_time)

from dba_hist_active_sess_history a

where 1=1

  and (session_id, session_serial#) in ((10408,34869))

group by NVL(a.event, 'ON CPU'),a.session_id, a.session_serial#

-- Get sql plans with runtimes for each plan 

WITH

p AS (

SELECT plan_hash_value

  FROM gv$sql_plan

 WHERE sql_id = :sql_id

   AND other_xml IS NOT NULL

 UNION

SELECT plan_hash_value

  FROM dba_hist_sql_plan

 WHERE sql_id = :sql_id

   AND other_xml IS NOT NULL ),

m AS (

SELECT plan_hash_value,

       SUM(elapsed_time)/SUM(executions) avg_et_secs

  FROM gv$sql

 WHERE sql_id = :sql_id

   AND executions > 0

 GROUP BY

       plan_hash_value ),

a AS (

SELECT plan_hash_value,

       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs

  FROM dba_hist_sqlstat

 WHERE sql_id = :sql_id

   AND executions_total > 0

 GROUP BY

       plan_hash_value )

SELECT p.plan_hash_value,

       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs

  FROM p, m, a

 WHERE p.plan_hash_value = m.plan_hash_value(+)

   AND p.plan_hash_value = a.plan_hash_value(+)

 ORDER BY

       avg_et_secs NULLS LAST;

SID of Concurrent Request:

select s.inst_id, fcr.request_id, fv.requestor, fv.Program cmgr_job,

p.PID,

p.SERIAL#,

p.USERNAME p_user,

p.SPID,

to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') Logon_Time,

s.program,

s.command,

s.sid,

s.serial#,

s.username,

s.process,

s.machine,

s.action,

s.module

from apps.fnd_concurrent_requests fcr,

apps.FND_CONC_REQ_SUMMARY_V fv,

gv$session s,

gv$process p

where fcr.request_id = &request_id

and p.SPID = fcr.oracle_process_id

and s.process = fcr.OS_PROCESS_ID

and s.inst_id = p.inst_id

and p.addr = s.paddr

and fv.request_id = fcr.request_id

;

pause try to lock the same row in a different session here

with lo as (select /*+ materialize */ sid, row_wait_obj# from v$session where event like 'enq%')

select lo.sid, do.object_name, do.object_type, do.object_id, do.data_object_id, lo.row_wait_obj# 

from dba_objects do, lo

where do.object_id=lo.row_wait_obj#

   or do.data_object_id=lo.row_wait_obj#;

Bind Variable:

select INST_ID,SQL_ID,POSITION,NAME,VALUE_STRING from GV$SQL_BIND_CAPTURE where sql_id='c73qbuzd38n1b' and inst_id=1 order by POSITION

select * from sys.dba_hist_sqlbind t where t.sql_id='c73qbuzd38n1b';

 SELECT sql_id,  b.name BIND_NAME,  b.value_string BIND_STRING   from gv$sql t

JOIN gv$sql_bind_capture b USING (sql_id) WHERE b.value_string IS NOT NULL

AND sql_id ='c73qbuzd38n1b';

select 

sn.END_INTERVAL_TIME,

sb.NAME,

sb.VALUE_STRING 

from 

DBA_HIST_SQLBIND sb,

DBA_HIST_SNAPSHOT sn

where 

sb.sql_id='c73qbuzd38n1b' and

sb.WAS_CAPTURED='YES' and

sn.snap_id=sb.snap_id

order by 

sb.snap_id,

sb.NAME;

Buffer Gets:

SELECT

      s.sid

     ,s.serial#

     ,s.sql_id

     ,sq.sorts

     ,sq.DISK_READS

     ,sq.BUFFER_GETS

     ,sq.ROWS_PROCESSED

     ,sq.SQLTYPE

     ,sq.SQL_TEXT

FROM gv$session s   

    , gv$sql sq

WHERE s.sql_id = sq.sql_id and s.sid=11351 and rownum<10 order by sq.sorts desc

restore point

 SQL> select NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE from V$RESTORE_POINT;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 SRAM2I                         READ WRITE NO

SQL> alter session set container=SRAM2I;

Session altered.

SQL> alter pluggable database SRAM2I CLOSE;

Pluggable database altered.

SQL> SQL>

SQL> flashback pluggable database SRAM2I to restore point RESTORE_POINT2_REQ0088666;

Flashback complete.

SQL> alter pluggable database SRAM2I open resetlogs;

Pluggable database altered.

SQL> shut immediate;

Pluggable Database closed.

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

znfodRAMfsd011  $ srvctl stop database -d SRAM2IC

znfodRAMfsd011  $ srvctl start database -d SRAM2IC

znfodRAMfsd011  $ srvctl start listener -l SRAM2IC

Tablespace

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

alter tablespace SOAPRD_SOAINFRA add datafile size 2G autoextend on maxsize 20G;

alter tablespace SOADE3_STB add datafile size 2G autoextend on maxsize 20G;

G-Invoicing - ECC

 

Installing Oracle Enterprise Command Center Framework, Release 12.2 (Latest Version) (Doc ID 2495053.1) Installing Oracle Enterprise Command Center Framework, Release 12.2 V12 (Doc ID 3017956.1)

G-Invoice:  G-Invoicing ECC Patch (Patch - 36861505, 36236796, 36393109, 35921608, 35921575, 36032737, 35921481, 35302496) - ECC Upgrade to V12

Sequence below

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

36236796

36393109

35921608

35921575

36032737

35921481

35302496

36861505

 July CU ECC Patch - 36861505
 July CU Servicing Patch - 36866950

• grep -i ecc_ $CONTEXT_FILE
• 
  
• To start and Stop ECC
• /dRAM4ei/applmgr/Oracle/quickInstall/bin/startAllEcc.sh
• /dRAM4ei/applmgr/Oracle/quickInstall/bin/stopAllEcc.sh
• 
  
• more $ECC_BASE/Oracle/quickInstall/env/ecc.env|grep -i ebs
• 
  
• more $ECC_BASE/Oracle/quickInstall/env/ecc.env|grep -i ebs
• 
  
• ps -fu $LOGNAME
• 
  
• IT:
• 
  
• [oracle@lufRAMfebsdl613 app]$ sqlplus ecc/INT_3CCCLE_SIT3M@\"$ECC_DB_CONNECTION\"
• 
  
• $ pwd
• /dRAM4ei/applmgr/Oracle/software/zookeeper/bin
• $ ls -ltr
• total 31
• -rwx------. 1 apdRAM4ei aadRAM4ei 1385 Mar  1  2022 zkTxnLogToolkit.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 6773 Mar  1  2022 zkServer.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 2696 Mar  1  2022 zkEnv.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 1534 Mar  1  2022 zkCli.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 1937 Mar  1  2022 zkCleanup.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei  232 Mar  1  2022 README.txta

Audit Queries

 Audit:

select aud.audit_user_name UPDATED_BY, aud.audit_timestamp UPDATE_DATE, 
    (select distinct user_concurrent_program_name from APPS.FND_CONCURRENT_PROGRAMS_VL where aud.concurrent_program_id = CONCURRENT_PROGRAM_ID and last_update_date > to_date('10/01/2021','MM/DD/YYYY')) as program_name,
    decode(aud.audit_transaction_type,'U','(~) Update','I','(+) Insertion','D','(-) Deletion') as audit_type,
    (select application_name from apps.fnd_application_vl where application_id = aud.application_id) as program_app,
    aud.concurrent_program_name, aud.executable_application_id, aud.executable_id, aud.request_set_flag,
    aud.enabled_flag, aud.run_alone_flag, aud.queue_control_flag, aud.argument_method_code, aud.queue_method_code, aud.execution_method_code, aud.execution_options, aud.output_print_style, aud.print_flag,
    aud.srs_flag, aud.required_style, aud.save_output_flag, aud.printer_name, aud.minimum_width, aud.minimum_length, aud.request_priority, aud.iprog_id, aud.restart, aud.nls_compliant, aud.output_file_type, aud.enable_trace,
    aud.cd_parameter, aud.increment_proc, aud.mls_executable_app_id, aud.mls_executable_id, aud.enable_time_statistics, aud.security_group_id, aud.activity_summarizer, aud.program_type
from apps.FND_CONCURRENT_PROGRAMS_A aud
where audit_timestamp >= to_date('10/01/2023','MM/DD/YYYY')
    and audit_user_name not in ('APPS','SYSADMIN','ANONYMOUS','APPSMGR','HHSOPERATOR','UPA_SOLUTION','ORACLE12.2.0','CONCURRENT MANAGER')
order by aud.audit_timestamp;

Add responsibilities

 ./addresp.sh GV3T_ZU_Q9KLSSD1 SEETHA  "Requesting Approver"

 more ./addresp.sh

APPSPWD=$1

username=$2

respname="$3"

if [[ -z ${username} ]]

then

  echo "Enter Username "

  read username

fi

if [[ -z ${respname} ]]

then

  echo "Enter responsibility like System Administrtor "

  read respname

fi

PDBSID=`echo "$ORACLE_SID" | cut -c 1-6`

HOST="`hostname`"

. $ORACLE_HOME/"$PDBSID"_"$HOST".env

sqlplus -s apps/$APPSPWD@$ORACLE_SID << EOF

set serveroutput on

declare

v_userid fnd_user.user_id%type;

v_respid fnd_responsibility_tl.responsibility_id%type;

v_applid fnd_responsibility_tl.application_id%type;

begin

select user_id

into v_userid

from fnd_user

where user_name='${username}';

dbms_output.put_line('userid for $username is ' || v_userid);

select application_id, responsibility_id

into v_applid, v_respid

from fnd_responsibility_tl

where responsibility_name like '${respname}';

dbms_output.put_line('Resp id, Application id  for ${respname}  are ' || v_respid ||','|| v_applid);

fnd_user_resp_groups_api.Upload_Assignment(v_userid,v_respid,v_applid,0,sysdate,sysdate+90,'Comments');

exception

when others then

  dbms_output.put_line(sqlerrm);

end;

/

EOF

Parallel Query Validation

 

select distinct  'alter system kill session ' || '''' || sid || ',' || serial# || ',@' || s.inst_id || ''';' alter_statement, s.SQL_ID, s.CLIENT_IDENTIFIER,cast(s.SQL_EXEC_START as TIMESTAMP), SQL_EXEC_START,SQL_TEXT FROM gv$session s, gv$sql q  where s.sql_id = q.sql_id  AND s.status = 'ACTIVE' AND s.username = 'BIS_RT' and lower(CLIENT_IDENTIFIER) like '%@%gov%';

SELECT s.inst_id, s.sid,
       s.serial#,
       s.username,
       s.client_identifier,
       s.status,
       s.machine,
       s.logon_time,
       ROUND((SYSDATE - s.logon_time) * 24 * 60, 2) AS minutes_running,
       'IHS - Document View Report' program,
       q.sql_id
FROM gv$session s
LEFT JOIN gv$sql q
ON s.sql_id = q.sql_id AND s.inst_id = q.inst_id
WHERE s.username is not null
AND s.status = 'ACTIVE'
AND s.username = 'BIS_RT'
AND s.program like '%nqsserver%'
AND q.sql_fulltext LIKE '%Document View Report%'
AND sql_fulltext LIKE '%+%Parallel%(%16%)%'
--AND s.logon_time < SYSDATE - 10/(24*60) -- More than 10 minutes
ORDER BY minutes_running DESC;

DOC IDs:

 DOC IDs:

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

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

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











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

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

 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
/

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"

Java(TM) SE Runtime Environment (build 1.7.0_391-b05)

Java HotSpot(TM) 64-Bit Server VM (build 24.391-b05, mixed mode)

[oracle@luframfebstl011 ~]$ $COMMON_TOP/util/jdk32/bin/java -d32 -version

java version "1.7.0_391"

Java(TM) SE Runtime Environment (build 1.7.0_391-b05)

Java HotSpot(TM) Server VM (build 24.391-b05, mixed mode)

[oracle@luframfebstl011 ~]$ $FMW_HOME/webtier/jdk/bin/java -d64 -version

java version "1.7.0_391"

Java(TM) SE Runtime Environment (build 1.7.0_391-b05)

Java HotSpot(TM) 64-Bit Server VM (build 24.391-b05, mixed mode)

[oracle@luframfebstl011 ~]$ $ORACLE_HOME/jdk/bin/java -d32 -version

java version "1.7.0_391"

Java(TM) SE Runtime Environment (build 1.7.0_391-b05)

Java HotSpot(TM) Server VM (build 24.391-b05, mixed mode)

[oracle@luframfebstl011 ~]$ java -version

java version "1.7.0_391"

Java(TM) SE Runtime Environment (build 1.7.0_391-b05)

Java HotSpot(TM) Server VM (build 24.391-b05, mixed mode)

alter tablespace SOADEV_SOAINFRA add datafile '+DATA' size 10G autoextend on next 512M maxsize 20G;

1,$s/^/###Ram_Task###/g  - Disable 

1,$s/^###Ram_Task###/g   - Enable 

How to identify the SQL_ID of a statement

 SELECT

    sql_id

  , hash_value

  , plan_hash_value

  , sql_text

FROM

  gv$sql

WHERE

  sql_text LIKE '%all_rows parallel%';

 

select inst_id, sql_id, plan_hash_value, substr(sql_text, 1 ,40) from gv$sql where sql_id = '1h6twft9b97sv';

 

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text

FROM gv$sql

WHERE sql_text LIKE '%INSERT INTO apps.hhs_cta_ufms_tbl%' and module like '%e:FV:cp:hhs/HHSCARSEXT%';

 

If SQL is no longer available in v$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT :

SELECT
    s.sql_id,
    s.plan_hash_value,
    t.sql_text,
    s.snap_id
FROM
    dba_hist_sqlstat   s,
    dba_hist_sqltext   t
WHERE s.dbid = t.dbid
AND   s.sql_id = t.sql_id
AND   sql_text LIKE 'SELECT /* TARGET SQL */%'
ORDER BY
    s.sql_id;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'8p9mw649qbzs1',format=>'ADVANCED'));

Trace - set max_dump_file_size=unlimited

 Trace 

ACTION PLAN

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

1. Execute SQL alter system set max_dump_file_size=unlimited; (If you dont set it to unlimited the trace could be truncated)

2. Perform steps to enable trace for the concurrent request:

a. In System Administrator responsibility Navigate to: Concurrent -> Program -> Define, query up the concurrent program "Federal Activity Attributes Creation" to trace. Make sure that the "enable trace" checkbox is checked.

3. Submit the Concurrent Request "Federal Activity Attributes Creation"

4. After the "Federal Activity Attributes Creation" request starts / wait for couple of hours (3-4 hours) and move further (DO NOT CANCEL THE REQUEST).

5. Execute tkprof on the trace files collected. (NOTE: TKPROF should be executed from DB node as DB Owner OS User).

a. Issue a below command to create a TKPROF of the trace file. This command sorts the results with the longest running queries first:

tkprof <filename.trc> <output_filename_SORT.txt> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)'

b. Additionally, please provide the following TKPROF that limits the results to the top ten queries:

tkprof <filename.trc> <output_filename_TOP10.txt> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10

c. Additionally, please provide the following TKPROF that shows all the sql statements executed for that concurrent request.

tkprof <filename.trc> <output_filename_ALL.txt> sys=no explain=apps/<password>

d. The above 3 commands should be executed on each and every trace file generated by the concurrent request.

6. Execute SQL alter system set max_dump_file_size=<ACTUAL VALUE>;

7. Upload a copy of the "Federal Activity Attributes Creation" request log and output.

8. Execute the following SQL statement and upload the output in an EXCEL SHEET:

SQL> select owner, table_name, status, num_rows, sample_size, last_analyzed from dba_tables where table_name like '%_GT' and num_rows is not null order by table_name;

SQL> select owner, table_name, status, num_rows, sample_size, last_analyzed from dba_tables where table_name = 'MO_GLOB_ORG_ACCESS_TMP' and num_rows is not null;

SQL> select * from v$parameter where name like 'optimizer%' order by name;

SQL> select SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, CREATOR, ORIGIN FROM dba_sql_plan_baselines;

9. Collect AWR Report for the below snaps when "Invoice Validation" was running and upload the report:

Begin Snap ID = <Start time of Federal Activity Attributes Creation>

End Snap ID = <Start time of Federal Activity Attributes Creation + 1 Hour>

10. Run SQLT 

# sqlplus / as sysdba
SQL> START sqlhc.sql "T" djkbyr8vkc64h

RESTRICTIONS

1. Please note the SQL_ID cannot be a SQL_ID of a PL/SQL package. This will produce no useful results.
2. This routine will run against ONE SQL_ID at a time, only. You cannot use a list of SQL_ID statements as a parameter list.
3. You cannot run this routine against ALL SQL statements run by a schema.

Ref:

Concurrent Processing - bde_request.sql - Process and Session info for one Concurrent Request (11.5) (Doc ID 187504.1)

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)