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