Thursday, November 12, 2020
Wednesday, November 11, 2020
SQLT installation.
FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1)
1. Download sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
2. mv the zip to $ORACLE_HOME
3. unzip -o sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
4. /apps2/oracle/product/12.1.0/sqlt
oracle@suthakar$ls -tlr
total 120
drwxr-x---. 3 oracle oinstall 4096 Jun 5 10:37 input
drwxr-x---. 2 oracle oinstall 4096 Jun 5 10:37 doc
drwxr-x---. 2 oracle oinstall 4096 Jun 5 10:38 run
drwxr-x---. 2 oracle oinstall 4096 Jun 5 10:38 install
drwxr-x---. 7 oracle oinstall 4096 Jun 5 10:38 utl
-rw-r-----. 1 oracle oinstall 58466 Jun 5 10:41 sqlt_instructions.html
-rw-r-----. 1 oracle oinstall 39368 Jun 5 10:43 sqlt_instructions.txt
oracle@suthakar$cd install
oracle@suthakar$pwd
/apps2/oracle/product/12.1.0/sqlt/install
oracle@suthakar$
oracle@suthakar$sqlplus '/as sysdba'
SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 11 21:43:55 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> !ls -ltr sqdrop.sql
-rw-r-----. 1 oracle oinstall 1365 Oct 30 2014 sqdrop.sql
SQL> sta sqdrop.sql
SQL> SET ECHO OFF;
SQDUSR completed.
SQDROP completed.
SQL>
Execute installation script sqlt/install/sqcreate.sql connected as SYS
SQL> !ls -ltr sqdrop.sql
-rw-r-----. 1 oracle oinstall 1365 Oct 30 2014 sqdrop.sql
SQL> sta sqdrop.sql
... uninstalling SQLT, please wait
TADOBJ completed.
SQDOLD completed. Ignore errors from this script
SQDOBJ completed. Ignore errors from this script
SQL>
SQL> DECLARE
2 my_count INTEGER;
29 END LOOP;
30 END IF;
31 END;
32 /
Cannot drop procedure sys.sqlt$_trca$_dir_set. ORA-04043: object
SQLT$_TRCA$_DIR_SET does not exist
Ignore errors from here until @@@@@ marker as this is to test for NATIVE PLSQL Code Type
@@@@ marker . You may ignore prior errors about NATIVE PLSQL Code Type
old 1: ALTER SESSION SET PLSQL_CODE_TYPE = &&plsql_code_type
new 1: ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE
Session altered.
Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.
Optional Connect Identifier (ie: @PROD): @DEV
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN: APPS
Re-enter password:
PL/SQL procedure successfully completed.
The next step is to choose the tablespaces to be used by SQLTXPLAIN
The Tablespace name is case sensitive.
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?
Type YES or NO [Default NO]: YES
... please wait
TABLESPACE FREE_SPACE_MB
------------------------------ -------------
APPS_TS_ARCHIVE 96
APPS_TS_TX_DATA 456
APPS_TS_NOLOGGING 655
APPS_TS_INTERFACE 728
APPS_TS_MEDIA 1250
APPS_TS_QUEUES 2084
OWB 2323
APPS_TS_SEED 4616
APPS_TS_TX_IDX 11173
APPS_TS_SUMMARY 12029
10 rows selected.
Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: APPS_TS_SUMMARY
PL/SQL procedure successfully completed.
... please wait
TABLESPACE
------------------------------
TEMP
Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP
PL/SQL procedure successfully completed.
The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.
Main application user of SQLT: XXXX
PL/SQL procedure successfully completed.
SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:
"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses
Oracle Pack license [T]: N
PL/SQL procedure successfully completed.
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
SQL>SQL> grant SQLT_USER_ROLE to APPS;
Grant succeeded.
SQL>
Sunday, August 30, 2020
EBS WF Background Process SQL
Monitor the progress on what is happening with the WF Background Processes by running these:
1. Show what is exactly on the Background Process Deferred Queue
ready for processing
select
w.user_data.itemtype "Item Type", w.user_data.itemkey "Item
Key",
decode(w.state, 0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(w.state)) State,
w.priority, w.ENQ_TIME, w.DEQ_TIME, w.msgid
from wf_deferred_table_m w
where w.user_data.itemtype = '&item_type';
2. Monitor the Queue by Item Type ie APPS<itemtype>
select corrid,
user_data user_data
from wf_deferred_table_m
where state = 0
and corrid = '&Corrid'
order by priority, enq_time;
3. Monitor the Deferred Queue to see exactly what's coming off
next
SELECT
wfdtm.corrid, wfdtm.user_data.ITEMTYPE ITEM_TYPE,
wfdtm.user_data.ITEMKEY ITEM_KEY, wfdtm.enq_time,
DECODE(wfdtm.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
TO_CHAR(SUBSTR(wfdtm.state,1,12))) State
FROM wf_deferred_table_m wfdtm
WHERE wfdtm.state = 0
ORDER BY wfdtm.priority, wfdtm.enq_time;
4. Show what frequency the FNDWFBG request is going to be
resubmitted and its parameters:
select
r.REQUEST_ID, r.REQUESTED_BY, r.PHASE_CODE, p.USER_CONCURRENT_PROGRAM_NAME,
r.ARGUMENT_TEXT "Arguments",
Nvl(Substr(R.Argument_Text,0,Instr(R.Argument_Text,',')-1),'All Items')
Item_Type,
Substr(R.Argument_Text,Instr(R.Argument_Text,',')+1,Instr(R.Argument_Text,',',1,2)-Instr(R.Argument_Text,',')-1)
Min_Threshold,
Substr(R.Argument_Text,Instr(R.Argument_Text,',',1,2)+1,(Instr(R.Argument_Text,',',1,3)-1)-(Instr(R.Argument_Text,',',1,2)))
Max_Threshold,
Substr(R.Argument_Text,Instr(R.Argument_Text,',',1,3)+1,(Instr(R.Argument_Text,',',1,4)-1)-(Instr(R.Argument_Text,',',1,3)))
Deferred,
substr(r.ARGUMENT_TEXT,instr(r.ARGUMENT_TEXT,',',1,4)+1,(instr(r.ARGUMENT_TEXT,',',1,5)-1)-(instr(r.ARGUMENT_TEXT,',',1,4)))
TIMEOUT,
substr(r.ARGUMENT_TEXT,instr(r.ARGUMENT_TEXT,',',1,5)+1) STUCK,
r.RESUBMIT_INTERVAL EVERY, r.RESUBMIT_INTERVAL_UNIT_CODE SO_OFTEN,
r.RESUBMIT_END_DATE
FROM fnd_concurrent_requests r, FND_CONCURRENT_PROGRAMS_TL p
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
and p.USER_CONCURRENT_PROGRAM_NAME LIKE 'Workflow%Background%'
AND p.LANGUAGE = 'US'
and r.ACTUAL_COMPLETION_DATE is null
and r.PHASE_CODE in ('P','R');
Friday, August 28, 2020
Output Post Processor (OPP) Fails
R12 E-Business Suite Output Post Processor (OPP) Fails To Pick Up Concurrent Requests With Error 'Unable to find an Output Post Processor service to post-process request nnnnn' (Doc ID 2215371.1)
SELECT fcp.concurrent_process_id FROM apps.fnd_concurrent_queues fcq, apps.fnd_concurrent_processes fcp WHERE concurrent_queue_name = 'FNDCPOPP'AND fcq.concurrent_queue_id = fcp.concurrent_queue_idAND fcq.application_id = fcp.queue_application_id AND fcp.process_status_code = 'A'
Thursday, August 27, 2020
Oracle EBS workflow sql handy for your job
select w.user_data.itemtype "Item Type", w.enq_time,w.user_data.itemkey "Item Key" from apps.wf_deferred_table_m w order by w.enq_time desc
SELECT component_name, component_status FROM apps.fnd_svc_componentsWHERE component_type = 'WF_MAILER';
select message_type, count(1) from apps.wf_notifications where status='OPEN' and mail_status='MAIL' group by message_type;
select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';
select component_status from apps.fnd_svc_components where component_id = (select component_id from apps.fnd_svc_components where component_name = 'Workflow Notification Mailer');
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.phase_code = 'R'
select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not Running',fcp.OS_PROCESS_ID) PROCID, fcq.MAX_PROCESSES TARGET, fcq.RUNNING_PROCESSES ACTUAL, fcq.ENABLED_FLAG ENABLED, fsc.COMPONENT_NAME, fsc.STARTUP_MODE, fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES fcp, apps.fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID and fcs.SERVICE_HANDLE = 'FNDCPGSC' and fsc.COMPONENT_STATUS = 'RUNNING' and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+) and fcq.application_id = fcp.queue_application_id(+) and fcp.process_status_code(+) = 'A'order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE
Sunday, July 19, 2020
Oracle EBS CPU July 2020 Patches
Doc : Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (July 2020) (Doc ID 2679563.1)
Application Patch
Application Patch
| ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JUL 2020 | |
| Update | 31198342 |
|---|---|
| Product | Oracle Applications Utilities |
| Release | R12 |
| Platform | Oracle Solaris on SPARC (64-bit) |
| Built | JUL-14-2020 03:37:24 |
| Preparation Tasks The tasks in this section can be completed without taking any Application services or users offline. |
Required Code levels [required]This patch requires additional code levels to have been successfully applied to your system before it can be applied. These code levels, as at the time that this patch was built, are listed below. Please check 'My Oracle Support' to determine if any of these have been superseded
|
5. Use SQL*Plus to run the script:
UNIX:
$ sqlplus /nolog
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql
|
| Apply The PatchFor 12.0.X / 12.1.X / pre-upgrade patches (using adpatch), you must shut down all Application tier services before performing the tasks in this section. For 12.2.X patches (using adop), you can perform the tasks in this section without shutting down the Application tier services. |
Apply patch [required]Apply the patch with ADOP: adop phase=apply patches=31198342 |
Based on on the system
Footnote 1: Additional Patches to be Applied After the CPU Patch
DB Patch
| Oracle Database 19.0.0.0 | Combo OJVM Release Update 19.8.0.0.200714 and Database Release Update 19.8.0.0.200714 Patch 31326362 | See Document 2664876.1, Critical Patch Update (CPU) Program July 2020 Availability Document (PAD). Note that patches listed for this product are yet to be fully tested with Oracle E-Business Suite. |
| Oracle Database 12.1.0.2 | OJVM PSU 12.1.0.2.200714 and Database PSU 12.1.0.2.200714 Combo Patch 31326396 | See Document 2664876.1, Critical Patch Update (CPU) Program July 2020 Availability Document (PAD). |
| Oracle WebLogic Server 10.3.6.0 | PSU 10.3.6.0.200714 (Patch 31178492) | See Document 2664876.1, Critical Patch Update (CPU) Program July 2020 Availability Document (PAD). When applying the Oracle WebLogic Server PSU, perform the following steps in case of conflicts:
|
| Oracle Fusion Middleware 11.1.1.9 - Web Tier Home | Security Patch Update CPUJul2020 for OSS Patch 31304503 | See Document 2664876.1, Critical Patch Update (CPU) Program July 2020 Availability Document (PAD). We recommend that you enable TLS by following Document 1367293.1, Enabling TLS in Oracle E-Business Suite Release 12.2, to increase security and to ensure that your application will continue to work after this patch is applied.
Note: In addition to following the instructions to enable TLS found in Document 1367293.1, you will need to follow the instructions in Document 2555355.1, Prerequisite Steps to Configure Oracle Fusion Middleware 11.1.1.9 Components for Oracle E-Business Suite Release 12.2 Before Applying the July 2019 and Later FMW OSS Security Patch, to update the Oracle Fusion Middleware 11.1.1.9 default certificates.
|
| Oracle Java (Java SE [JRE] Plug-in) | Oracle Java Runtime Environment (JRE) 8 Update 261 Patch 31311324 Oracle JRE 7 Update 271 Patch 31311349 | See Document 2682801.1, Critical Patch Update (CPU) July 2020 for Oracle Java SE. You should also review Document 393931.1, Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12, for information about the Java versions to use and the procedures to follow. Review the requirements in Document 1591073.1, Enhanced Jar Signing for Oracle E-Business Suite, to ensure compatibility with the latest Java CPU. |
| Oracle Java (JDK) | Oracle JDK 7 Update 271 Patch 31311341 | See Document 2682801.1, Critical Patch Update (CPU) July 2020 for Oracle Java SE. You should also review the following My Oracle Support knowledge documents to uptake the latest JDK:
|
| Oracle Java (Java Web Start) | Oracle JRE 8 Update 261 Patch 31311324 | See Document 2188898.1, Using Java Web Start with Oracle E-Business Suite. |
Footnote 2: WLS Patch Conflicts and Corresponding Overlay Patches
The following table lists known patch conflicts and corresponding overlay patches to apply. If you encounter conflicts with any other patches, refer to Document 1306505.1, Announcing Oracle WebLogic Server PSUs (Patch Set Updates).
| Previous Overlay (SU) Patch Associated with Conflicting WLS Patch | Current Overlay (SU) Patch to Apply |
|---|---|
| K25M, XIDD, RVBS, NJVF, K4AL, A5H6, QMJP, G9L4, UNUT, Z9PC, 6TP5, CW6U, CF5U, PSVV, S5C9, 47D2, D446, WT48, 9KCT, JJJF, CQ63, AYDX, 45ET , HMR4, TR6Y, CJXI, SLVI, WUH8, 159X, KRDE, X6TC, EV8H, WQKK, 96N5, R1EY, F5QI, ISBJ, PG5J, 1UWC, 7F9C, M3C7, EQDE, HJ2T, ZH8H, ZF83, 7P2C, EN8U, 9XLF, 7E7M, ZHMT, LLU8, IIYG, IJG4, 9UBP, 4R4W, IVAB, SUPD, BWKV, XGXM | There is no need for an overlay, as it has been included in the current PSU. |
| FC8V | 13845626 version 10.3.6.0.200714 (DTN2) |
Table 3: Security Patches for Technology Stack Components With No New Patch in This CPU
| Oracle Product | Latest Security Patches | My Oracle Support Knowledge Document References and Supplementary Comments |
|---|---|---|
| Oracle Database 19.0.0.0 | Combo OJVM Release Update 19.7.0.0.200414 and Database Release Update 19.7.0.0.200414 Patch 30783543 | See Document 2633852.1, Critical Patch Update (CPU) Program April 2020 Availability Document (PAD). Note that patches listed for this product are yet to be fully tested with Oracle E-Business Suite. |
| Oracle Fusion Middleware 11.1.1.9 - Web Tier Home | Security Patch Update CPUApr2020 for OHS Patch 31047338 | See Document 2633852.1, Critical Patch Update (CPU) Program April 2020 Availability Document (PAD). |
| Oracle Fusion Middleware 11.1.1.9 - Oracle Common Home | Security Patch Update CPUOct2019 Patch 30368663 | See Document 2568292.1, Critical Patch Update (CPU) Program October 2019 Availability Document (PAD). Only apply the OUI portion of this patch. Follow the README under the oui directory to apply the patch. |
| Oracle Fusion Middleware 11.1.1.7 - Web Tier Home | Security Patch Update CPUJan2018 for OHS Patch 27197885 Merge Patch 19849290 | See Document 2325393.1, Patch Set Update and Critical Patch Update January 2018 Availability Document. The merge patch is required for the Oracle Fusion Middleware component Oracle Security Service (OSS). |
| Oracle Fusion Middleware 11.1.1.7 - Oracle Common Home | Security Patch Update CPUJan2018 Patch 27251436 | See Document 2325393.1, Patch Set Update and Critical Patch Update January 2018 Availability Document. Only apply the OUI portion of this patch. Follow the README under the oui directory to apply the patch. |
| Database 11.1.0.7 client patches for FMW 11.1.1.7 or FMW 11.1.1.9 | PSU Patch 22290164 for UNIX Bundle Patch 22607089 for Windows 32-Bit Bundle Patch 22607090 for Windows x64 | See Document 2074802.1, Patch Set Update and Critical Patch Update January 2016 Availability Document. |
Thursday, June 11, 2020
How to generate RSA Key
rsa - an old algorithm based on the difficulty of factoring
large numbers. A key size of at least 2048 bits is recommended for
RSA; 4096 bits is better. RSA is getting old and significant advances
are being made in factoring. Choosing a different algorithm may be
advisable. It is quite possible the RSA algorithm will become
practically breakable in the foreseeable future. All SSH clients
support this algorithm@suthakar$pwd
/home/oracle/.ssh
@suthakar$ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
cd:3c:69:fa:b3:68:a6:6a:e0:63:eb:df:a5:4c:8c:4d oracle@suthakar.prince
The key's randomart image is:
+--[ RSA 2048]----+
| |
| |
| |
| + . |
| E S B |
| . = o . |
| . .. + o |
| + .+ ooo. |
| oo=o.=+. oo |
+-----------------+
@suthakar$pwd
@suthakar$ls -ltr
total 12
-rw-r--r--. 1 oracle oinstall 392 Feb 5 2018 known_hosts_org
-rw-r--r--. 1 oracle oinstall 404 Jun 11 21:52 id_rsa.pub
-rw-------. 1 oracle oinstall 1675 Jun 11 21:52 id_rsa
@suthakar$
you will see publickey and private key.
how to check the keylength
ssh-keygen -lf id_rsa.pub
2048 cd:3c:69:fa:b3:68:a6:6a:e0:63:eb:df:a5:4c:8c:4d id_rsa.pub (RSA)
@suthakar$
cat id_rsa.pub >>authorized_keys
@suthakar$ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 392 Feb 5 2018 known_hosts_org
-rw-r--r--. 1 oracle oinstall 404 Jun 11 21:52 id_rsa.pub
-rw-------. 1 oracle oinstall 1675 Jun 11 21:52 id_rsa
-rw-r--r--. 1 oracle oinstall 404 Jun 11 22:03 authorized_keys
scp authorized_keys username@xxxx :/user/home/oracle/.ssh/
16399723 ==> ABORT - Used to create uabort.drv file.
Patch Wizard/Applied Patches shows patches which
haven't been applied. (Doc ID 2196963.1)
Wednesday, June 10, 2020
Sunday, April 19, 2020
OAM Integration with EBS
Integrating Oracle E-Business Suite Release 12.2 with Oracle Access Manager 11gR2 (11.1.2) using Oracle E-Business Suite AccessGate (Doc ID 1576425.1)
Friday, April 17, 2020
Oracle Access Manager 11g R2PS3 (OAM 11.1.2.3) Configured with SHA2 Cert is not Certified
Oracle Access Manager 11g R2PS3 (OAM 11.1.2.3) Configured with SHA2 Cert is not Certified / Compatible with 10g WebGate, 11g R2PS1 and 11g R2PS2 (32 bit) WebGates (Doc ID 2289428.1)
EBS R12 CPU April 2020
Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (April 2020) (Doc ID 2650675.1)
Tuesday, March 17, 2020
Thursday, February 6, 2020
Sunday, February 2, 2020
Tuesday, January 21, 2020
Oracle January 2020 CPU
1. Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (January 2020) (Doc ID 2613782.1)
- Document 1147107.1, Database Patch Set Update Overlay Patches Required for Use with PSUs and Oracle E-Business Suite.
- Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes.
- Document 1392527.1, Database Patches Required by Oracle E-Business Suite on Oracle Engineered Systems: Exadata Database Machines and SuperClusters.
Jan 2020 webLogic patch is not released yet.
|
Friday, January 10, 2020
How to deploy JDK/JRE on EBSR12.2
- Document 393931.1, Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12.
- Document 1530033.1, Using the latest JDK 7.0 update with Oracle E-Business Suite Release 12.2.
Thursday, January 9, 2020
ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR OCT 2019 Patch Number 30077283
Prerequisite Patches
The following should be installed in the order shown before installing this patch.
|
How to apply Latest and AD TXK Release update
Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)
Subscribe to:
Posts (Atom)
