Oracle Applications R12.2.x| Exadata | OCI|Oracle GRC|ECC: April 2024

Tuesday, April 30, 2024

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

Monday, April 29, 2024

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

Please note the SQL_ID cannot be a SQL_ID of a PL/SQL package. This will produce no useful results.
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.
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)

Saturday, April 27, 2024

BI Applications - Oct 2023 CPU

Oct 2023 CPU

FINAL ACTION PLAN:
====================
- Perform pre-health checks (ckHealth)

- Shutdown services on luframfobidl313 and luframfobidl314 (stopBI)

- Take a count/list of invalid objects
- Apply DB patches using OCI console

- Take backup
      export ORACLE_HOME=$MW_HOME
      cd $ORACLE_HOME/..
      tar czvf 122_preOct2023CPU_`hostname`.tar.gz 122
      Remove old zip files from previous CPUs

which java
/u02/hhsfobidev/local/jdk/bin/java -version
cd /u02/ramfobidev/local
mv jdk jdk.1.8.0_381
tar -xvf /ood_repository/Patches/jdk-8u391-linux-x64.tar
mv jdk1.8.0_391 jdk
tar czvf jdk.1.8.0_381_`hostname`.tar.gz jdk.1.8.0_381

      cd /u02/ramfobidev
      tar czvf oraInventory_preOct2023.tar.gz oraInventory
      Remove old zip files from previous CPUs
- Apply Patches on application tier nodes

Stage patch zip files in /u01/stage/CPU/Oct2023

    * Apply PSU patches
      export ORACLE_HOME=$MW_HOME
      mkdir -p /u02/stage/CPU/Oct2023 ; cd /u02/stage/CPU/Oct2023
      unzip -o /ood_repository/Patches/p34703053_122130_Generic.zip

     * 35261722 WLS STACK PATCH BUNDLE 12.2.1.3.230405

cd WLS_SPB_12.2.1.3.221014/tools/spbat/generic/SPBAT
./spbat.sh -phase precheck -oracle_home $ORACLE_HOME -invPtrLoc $ORACLE_HOME/oraInst.loc
date; time ./spbat.sh -phase apply -oracle_home $ORACLE_HOME

     * OBI Bundle Patch 12.2.1.4.220628
        export ORACLE_HOME=$MW_HOME
cd /u02/stage/CPU/Oct2023/
        unzip -o /ood_repository/Patches/p35907889_122140_Linux-x86-64.zip
        cd OBI_SPB_12.2.1.4.231013/tools/spbat/generic/SPBAT
date;./spbat.sh -phase precheck -oracle_home $ORACLE_HOME;date
date; time ./spbat.sh -phase apply -oracle_home $ORACLE_HOME;date
cd ..

     * DATABASE RELEASE UPDATE 19.21.0.0.0
mkdir -p /u02/stage/CPU/Oct2023 ; cd /u02/stage/CPU/Oct2023
unzip -o /ood_repository/Patches/p35643107_190000_Linux-x86-64.zip
cd 35643107
        $ORACLE_HOME/OPatch/opatch lspatches
$ORACLE_HOME/OPatch/opatch apply -silent -invPtrLoc $ORACLE_HOME/oraInst.loc
cd ..
- Start all the MT services(startBI)
- Perform post-health checks (ckHealth)

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

cd $FMW_HOME/utils/bsu

./bsu.sh -report -bea_home=$FMW_HOME -output_file=bsu_patch_report.log

perl $FND_TOP/patch/115/bin/TXKScript.pl

-script=$FND_TOP/patch/115/bin/txkInventory.pl

-txktop=$APPLTMP

-contextfile=$CONTEXT_FILE

-appspass=$APPS_PWD

-outfile=$APPLTMP/Report_Inventory.html

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

Friday, April 26, 2024

ADOP

Patch Analysis Output

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 fnd_imp_psmaster_vl a, fnd_application_vl app

WHERE a.bug_no = 'XXXXXXXX'

AND app.application_short_name = a.app_short_name

ORDER BY DECODE (a.typeid,

'not applied',

'Unchanged File',

'upgrade',

'Changed File',

'new',

'New') ASC

screen

date;time adop phase=prepare,apply,finalize,cutover patches=36374309 workers=16 cm_wait=5;date

history |grep adop

frmcmp_batch.sh userid=apps/P3RF_T5Qh_a3ZRrP module=HHSPDFVW.fmb module_type=FORM batch=no compile_all=yes

date;time adop phase=cleanup;date

date;time adop phase=fs_clone force=yes

cd $ADOP_LOG_HOME

Tuesday, April 9, 2024

GRC Forms Compilation:

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LARMAIL.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LARMAIL.fmx module_type=form compile_all=special>/tmp/trb/LARMAIL.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAFCONST.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAFCONST.fmx module_type=form compile_all=special>/tmp/trb/LAAFCONST.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAINSTALL.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAINSTALL.fmx module_type=form compile_all=special>/tmp/trb/LAINSTALL.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAG.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAG.fmx module_type=form compile_all=special>/tmp/trb/LAAG.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LABRLIBS.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LABRLIBS.fmx module_type=form compile_all=special>/tmp/trb/LABRLIBS.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAADDFUNC.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAADDFUNC.fmx module_type=form compile_all=special>/tmp/trb/LAADDFUNC.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAPROMPTS.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAPROMPTS.fmx module_type=form compile_all=special>/tmp/trb/LAPROMPTS.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAACCESS.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAACCESS.fmx module_type=form compile_all=special>/tmp/trb/LAAACCESS.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAFACCOBJ.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAFACCOBJ.fmx module_type=form compile_all=special>/tmp/trb/LAAFACCOBJ.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAFNCWU.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAFNCWU.fmx module_type=form compile_all=special>/tmp/trb/LAAFNCWU.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAPPSAUDIT.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAPPSAUDIT.fmx module_type=form compile_all=special>/tmp/trb/LAAPPSAUDIT.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAASCAUS.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAASCAUS.fmx module_type=form compile_all=special>/tmp/trb/LAASCAUS.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LABIZ.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LABIZ.fmx module_type=form compile_all=special>/tmp/trb/LABIZ.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LACHGWIZ.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LACHGWIZ.fmx module_type=form compile_all=special>/tmp/trb/LACHGWIZ.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAMIG.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAG.fmx module_type=form compile_all=special>/tmp/trb/LAAG.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAPPSAUDITONLINE.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAPPSAUDITONLINE.fmx module_type=form compile_all=special>/tmp/trb/LAAPPSAUDITONLINE.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAUDIT.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAUDIT.fmx module_type=form compile_all=special>/tmp/trb/LAAUDIT.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LACRSTAT.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LACRSTAT.fmx module_type=form compile_all=special>/tmp/trb/LACRSTAT.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAEELM.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAEELM.fmx module_type=form compile_all=special>/tmp/trb/LAAEELM.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAFLOW.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAFLOW.fmx module_type=form compile_all=special>/tmp/trb/LAAFLOW.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAPEN.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAPEN.fmx module_type=form compile_all=special>/tmp/trb/LAAPEN.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAPPSAUDITREPORT.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAPPSAUDITREPORT.fmx module_type=form compile_all=special>/tmp/trb/LAAPPSAUDITREPORT.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LAAXCLNS.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LAAXCLNS.fmx module_type=form compile_all=special>/tmp/trb/LAAXCLNS.log

frmcmp_batch.sh module=/u01/install/APPS/fs2/EBSapps/appl/au/12.0.0/forms/US/LACHGREASON.fmb userid=APPS/$PWD output_file=/u01/install/APPS/fs2/EBSapps/appl/xxlaapps/12.0.0/forms/US/LACHGREASON.fmx module_type=form compile_all=special>/tmp/trb/LACHGREASON.log

Friday, April 5, 2024

Kill all processes of FNDLIBR.

check : ps -fu $USER

List:

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

Kill:

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

If you have multiple instances on the same server, you can use the below command to kill processes only of a particular user.

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

a) Concurrent Request Log:

select logfile_name from fnd_concurrent_requests where request_id = <request_id>;

b) Concurrent Output file:

select outfile_name from fnd_concurrent_requests where request_id = <request_id>;

c) Concurrent Manager Worker Log:

select logfile_name from fnd_concurrent_processes where concurrent_process_id=(select controlling_manager

from fnd_concurrent_requests where request_id=<request_id>);

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

Q1 CPU - ECC

[oracle@ ~]$ export PT=/ood_repository/Patches

[oracle@ ~]$ export ORACLE_HOME=/u01/app/Oracle/Middleware

[oracle@ ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH:

[oracle@ ~]$ $ORACLE_HOME/OPatch/opatch lsinv | grep -i applied

Patch 36178550 : applied on Wed Mar 27 22:41:49 EDT 2024

Patch 36155700 : applied on Wed Mar 27 22:41:42 EDT 2024

Patch 36086980 : applied on Wed Mar 27 22:40:18 EDT 2024

Patch 35965629 : applied on Wed Mar 27 22:40:07 EDT 2024

Patch 35922290 : applied on Wed Mar 27 22:39:54 EDT 2024

Patch 1221420 : applied on Wed Mar 27 22:39:30 EDT 2024

Patch 35474754 : applied on Wed Sep 27 23:08:17 EDT 2023

Patch 34341032 : applied on Sat Dec 10 08:19:55 EST 2022

Patch 33093748 : applied on Mon Oct 10 01:05:58 EDT 2022

Patch 32720458 : applied on Mon Oct 10 01:04:45 EDT 2022

Middle Tier:

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

java version "1.7.0_391"