Upgrading Jre plugin in Oracle Applications

Upgrading Jre plugin in Oracle Applications

Steps to follow

Step 1. Source the Environment

Source your environment as appropriate to your Oracle E-Business Suite release.

Oracle E-Business Suite 12.2.x Users

On the Application tier as the file system owner source your RUN file system.

Step 2. Download and Apply the Interoperability and Prerequisite Patches (If Required)

Historically, the JRE upgrade scripts (txkSetPlugin.*) worked with JRE plugin two digit point releases

for example, JRE 1.6.0_07 (JRE 6u7) and JRE 1.7.0_85 (JRE 7u85).

Step 2.1. Apply the JRE Interoperability Patch (If Required)

 Note: If you have previously applied the interop patch, skip this step and continue to Section 3: Upgrade and Configuration and run the JRE Upgrade Script.

Oracle E-Business Suite 12.2.x Users

Important patch

Apply interoperability Patch 21624242:R12.TXK.C.

Step 2.2. Download the JRE Plugin

Download the latest "JRE 8 " update (Do not download the "Server JRE" version) from the Java SE Downloads page or as a patch

 from My Oracle Support as listed in the JRE Parameter Settings table. If an earlier version of the JRE Plug-in is required, it can be downloaded as a patch from My Oracle Support as listed in the JRE Parameter Settingstable.

Click the JRE Download button for the appropriate Java version

Click the Accept License Agreement button

Download the Windows x86 Offline (32-bit) or Windows x64 (64-bit) version as required.

Step 2.2.1. Rename the JRE Plugin File

Rename the downloaded JRE Native Plugin file to j2se<jversion>.exe

Step 2.2.2. Place the Renamed JRE Plugin on the Web Application Tier

Place the renamed JRE Plugin file onto the web application tier in the directory as appropriate to your Oracle E-Business Suite release.

Oracle E-Business Suite 12.2.x Users

Move the j2se<jversion>.exe file to the web application tier and place it in the following directory in the RUN File System:

$[COMMON_TOP]/webapps/oacore/util/javaplugin

[COMMON_TOP] refers to the top level directory where the common utilities are installed. By default, this is the parent directory of $JAVA_TOP.

Step 3. Run the JRE Upgrade Script

Run the $FND_TOP/bin/txkSetPlugin.sh script against the web node of the application tier.

The txkSetPlugin.sh script updates the JRE version information in your AutoConfig context file and runs AutoConfig to incorporate the new values throughout your application. This script must be run using the following command:

$ txkSetPlugin.sh

The standard command using JRE 1.8.0_212 as an example would be:

$ txkSetPlugin.sh 18212

The script will prompt you for the following values if it does not find them automatically. If this is the case ensure you have correctly sourced your environment and run the script again:

Location of APPSORA.env file, if not present in the default location $APPL_TOP

Location of the AutoConfig Context File.

Password for the APPS user in the database (If the correct value is returned by the script you may press the return key at the prompt).

Note: This does not by-pass any of the usual in built security standards. Therefore you may still get warnings about out of date Java versions etc.

It will also not ignore the presence of a later Java family being installed on the same desktop. In this case if JRE 8 is also installed on the desktop, there will still be a preference to run using that version over the Java 7 version.

Step 4. Verify your upgrade

The following steps verify that you have successfully upgraded JRE.

Step 4.1. Enable the Java Console and Check Version

Enable the Java Console on your desktop client through the 'Java Control Panel' by setting:

'Control Panel' -> 'Java' (icon) -> 'Advanced' -> 'Java Console' -> 'Show Console'.

Step 4.2. Close All Browser Windows

Close all browser windows and wait until their processes have completed.

Step 4.3. Logon to Oracle E-Business Suite

Logon to the Oracle E-Business Suite and choose an Oracle Forms-based (professional user interface) responsibility.

For information on requirements and possible issues when trying to connect to a new or freshly upgraded Oracle E-Business Suite environment, please see Accessing Oracle E-Business Suite from a Desktop Client in the Appendices section.

Step 4.4. Check your Java Plug-in Version

The Java Console should appear and display lines describing the JRE version being used.

Note: If a higher version of JRE than the one just installed is shown, it may be because a higher version has previously been installed on the desktop. You can check the versions installed through the 'Java Control Panel':

Control Panel -> Java (icon) -> Java (tab) -> View (button)

Important steps

a)       Backup of APPLICATION TIER

b)      Backup context file

c)       Stop the services

d)      cd $COMMON_TOP/webapps/oacore/util/javaplugin/

e)      cd $FND_TOP/bin

f)        txkSetPlugin.sh 18212

g)       start the services

References:

Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12 (Doc ID 393931.1)

Oracle SQLT

Oracle SQLTXPLAIN (SQLT)

Reference -

OracleDBWR

SQLTXPLAIN :-

• SQLT is a set of packages and scripts that produces HTML-formatted reports, some SQL scripts and some text files.
• SQLTXPLAIN is tool to enable users to analyze and tune the performance of a single SQL statement.
• The entire collection of information is packaged in a zip file and often sent to Oracle Support, but you can look at these files yourself. There are just over a dozen packages and procedures (called “methods”) in SQLT. These packages and procedures collect different information based on your circumstances.
• These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.

Main steps :

1. Download SQLT
2. Install SQLT
3. Run SQLT report

Download SQLT tool :

• Login to My Oracle Support (http://support.oracle.com)
• Browse the DOCID  All About the SQLT Diagnostic Tool (Doc ID 215187.1)
• Click the Download SQLTXPLAIN of your database version.
• Unzip the SQLT (sqlt_10g_11g_12c_25_08_2018.zip) zip file.

Install SQLT tool :

• Unzip the zip file to suitable location.

[oracle@orcl:~ orcldemo] unzip sqlt_10g_11g_12c_25_08_2018.zip

• Connect as SYS user and ensure database is running

[oracle@orcl:install orcldemo] cd /home/oracle/sqlt/install
[oracle@orcl:install orcldemo] sqlplus / as sysdba

• Run the sqcreate.sql script
• Enter the connect identifier for the database
• Enter and confirm the password for SQLTXPLAIN user
• Select the tablespace  and temp tablespace where the SQLTXPLAIN to keep its packages and data
• Enter the username of the user in the database who will use SQLT packages to fix tuning problems. For us schema HARI that runs the problematic SQL,
• Enter “T”, “D” or “N.” This reflects your license level for the tuning and diagnostics packs

SQL> START sqcreate.sql
zip warning: Local Entry Flag does not match CD: 180825155053_00_sqdrop.log
adding: 181027005437_01_sqcreate.log (deflated 85%)

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)
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): @orcldemo

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN:
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
------------ -------------
T1           135
T2           135
T3           135
T4           199
NEW          299
USERS        32745

6 rows selected.

Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: USERS
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: HARI
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]:T

At last you will see the below message,

SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.

Some of the key steps performed at this stage include;

1.  Installing required packages to support SQLT.
2. Create SQLT schema objects.
3. Migrating relevant objects from old to new repository.
4. Taking snapshots of some existing data dictionary objects.

Run the SQLT report :

• Execute the below SQL statement and Get the SQL_ID

SQL> conn hari/hari;
Connected.
SQL> select count(*) from user_objects;

COUNT(*)
----------
10

SQL> select sql_id from v$sqlarea where sql_text like 'select count(*) from user_objects%';

SQL_ID
-------------
8x615vyks733p

SQLTXPLAIN Methods :-

SQLT provides 7 main methods that generate diagnostics details for one SQL statement: XTRACT, XECUTE, XTRXEC, XTRSBY, XPLAIN, XPREXT and XPREXC.

Mainly used methods in SQLT are:-

Select appropriate method to diagnose the SQL statement.

• XTRACT when SQL_ID is available
• XECUTE when detailed execution metrics are desired
• XTRXEC to get everything from XTRACT and XECUTE
• XPLAIN when XTRACT and XECUTE are not feasible
• XTRSBY when SQL executed on a read-only database

We must provide SQLT_USER_ROLE for user to run SQLT methods

• Grant SQLT_USER_ROLE after SQLT installation
• Optionally use sqlt/install/sqguser.sql

XTRACT :-

Use this method if you know the SQL_ID or the HASH_VALUE of the SQL to be analyzed, else use XECUTE method. The SQL_ID can be found on an AWR report, and the HASH_VALUE on any SQL Trace.

• Run the SQLT report from the HARI or respective user.

[oracle@orcl:~ orcldemo] cd sqlt/run/

[oracle@orcl:run orcldemo] sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 1 02:19:10 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn hari/hari;

SQL> @sqltxtract.sql 8x615vyks733p

PL/SQL procedure successfully completed.

Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:
SQLTXPLAIN password (required)

Enter value for 2: Oracle$123

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Value passed:
SQL_ID_OR_HASH_VALUE: "8x615vyks733p"

PL/SQL procedure successfully completed.

***
*** NOTE:
*** If you get error below it means SQLTXPLAIN is not installed:
*** PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared.
*** In such case look for errors in NN_*.log files created during install.
***

***
*** NOTE:
*** If running as SYS in 12c make sure to review sqlt_instructions.html first
***

SQLT_VERSION
----------------------------------------
SQLT version number: 12.2.180725
SQLT version date : 2018-07-25
Installation date : 2018-10-27/00:59:18

... please wait ...
adding: alert_orcldemo.log (deflated 90%)

NOTE:
You used the XTRACT method connected as HARI.

In case of a session disconnect please verify the following:
1. There are no errors in sqltxtract.log.
2. Your SQL 8x615vyks733p exists in memory or in AWR.
3. You connected as the application user that issued original SQL.
4. User HARI has been granted SQLT_USER_ROLE.

In case of errors ORA-03113, ORA-03114 or ORA-07445 please just
re-try this SQLT method. This tool handles some of the errors behind
a disconnect when executed a second time.

To actually diagnose the problem behind the disconnect, read ALERT
log and provide referenced traces to Support. After the root cause
of the disconnect is fixed then reset SQLT corresponding parameter.

To monitor progress, login into another session and execute:
SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;

... collecting diagnostics details, please wait ...

In case of a disconnect review log file in current directory
If running as SYS in 12c make sure to review sqlt_instructions.html first
... getting sqlt_s81018_sql_monitor_active_driver.sql out of sqlt repository ...
adding: sqlt_s81018_sql_monitor_active_driver.sql (deflated 47%)
... getting sqlt_s81018_remote_driver.sql out of sqlt repository ...
adding: sqlt_s81018_remote_driver.sql (deflated 47%)
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting sqlt_s81018_perfhub_driver.sql out of sqlt repository ...
... generating sqlt_s81018_perfhub_0001__.html ...
adding: sqlt_s81018_perfhub_driver.sql (deflated 52%)
... getting sqlt_s81018_main.html out of sqlt repository ...
... getting sqlt_s81018_lite.html out of sqlt repository ...
... getting sqlt_s81018_readme.html out of sqlt repository ...
... getting sqlt_s81018_readme.txt out of sqlt repository ...
... getting sqlt_s81018_metadata.sql out of sqlt repository ...
... getting sqlt_s81018_metadata1.sql out of sqlt repository ...
... getting sqlt_s81018_metadata2.sql out of sqlt repository ...
... getting sqlt_s81018_system_stats.sql out of sqlt repository ...
... getting sqlt_s81018_schema_stats.sql out of sqlt repository ...
... getting sqlt_s81018_set_cbo_env.sql out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting sqlt_s81018_sql_detail_active.html out of sqlt repository ...
... getting sqlt_s81018_10053_explain.trc out of sqlt repository ...
... getting sqlt_s81018_10053_i1_c0_extract.trc out of sqlt repository ...
... getting missing_file.txt out of sqlt repository ...
... getting sqlt_s81018_import.sh out of sqlt repository ...
... getting sqlt_s81018_export_parfile.txt out of sqlt repository ...
... getting sqlt_s81018_export_parfile2.txt out of sqlt repository ...
... getting plan.sql out of sqlt repository ...
... getting 10053.sql out of sqlt repository ...
... getting flush.sql out of sqlt repository ...
... getting sqlt_s81018_purge.sql out of sqlt repository ...
... getting sqlt_s81018_restore.sql out of sqlt repository ...
... getting sqlt_s81018_del_hgrm.sql out of sqlt repository ...
... getting tc.sql out of sqlt repository ...
... getting xpress.sh out of sqlt repository ...
... getting xpress.sql out of sqlt repository ...
... getting setup.sql out of sqlt repository ...
... getting readme.txt out of sqlt repository ...
... getting tc_pkg.sql out of sqlt repository ...
... getting sel.sql out of sqlt repository ...
... getting sel_aux.sql out of sqlt repository ...
... getting install.sh out of sqlt repository ...
... getting install.sql out of sqlt repository ...
... getting pack_tcx.sql out of sqlt repository ...
... getting sqlt_s81018_awrrpt_driver.sql out of sqlt repository ...
adding: sqlt_s81018_awrrpt_driver.sql (deflated 47%)
... getting sqlt_s81018_addmrpt_driver.sql out of sqlt repository ...
adding: sqlt_s81018_addmrpt_driver.sql (deflated 46%)
... getting sqlt_s81018_ashrpt_driver.sql out of sqlt repository ...
adding: sqlt_s81018_ashrpt_driver.sql (deflated 57%)
... getting sqlt_s81018_tcb_driver.sql out of sqlt repository ...
zip warning: name not matched: /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/README.txt
adding: sqlt_s81018_tcb_.trc (deflated 94%)
adding: sqlt_s81018_tcb_README.txt (deflated 57%)
adding: sqlt_s81018_tcb_dpexp.dmp (deflated 91%)
adding: sqlt_s81018_tcb_dpexp.log (deflated 70%)
adding: sqlt_s81018_tcb_dpexp.sql (deflated 72%)
adding: sqlt_s81018_tcb_dpimp.sql (deflated 67%)
adding: sqlt_s81018_tcb_main.xml (deflated 78%)
adding: sqlt_s81018_tcb_ol.xml (deflated 88%)
adding: sqlt_s81018_tcb_prmimp.sql (deflated 55%)
adding: sqlt_s81018_tcb_smrpt.html (deflated 48%)
adding: sqlt_s81018_tcb_sql.xml (deflated 26%)
adding: sqlt_s81018_tcb_ssimp.sql (deflated 66%)
adding: sqlt_s81018_tcb_ts.xml (deflated 32%)
adding: sqlt_s81018_tcb_xpl.txt (deflated 81%)
adding: sqlt_s81018_tcb_xplf.sql (deflated 52%)
adding: sqlt_s81018_tcb_xplo.sql (deflated 73%)
adding: sqlt_s81018_tcb_xpls.sql (deflated 55%)
adding: sqlt_s81018_tcb_driver.sql (deflated 53%)
... getting sqlt_s81018_xpand_sql_driver.sql out of sqlt repository ...
... getting sqlt_s81018_export_driver.sql out of sqlt repository ...

*******************************************************************
* Enter SQLTXPLAIN valid password to export SQLT repository *
* Notes: *
* 1. If you entered an incorrect password you will have to enter *
* now both USER and PASSWORD. The latter is case sensitive *
* 2. User is SQLTXPLAIN and not your application user. *
*******************************************************************

zip error: Nothing to do! (sqlt_s81018_tc.zip)
adding: sqlt_s81018_import.sh (deflated 35%)
zip warning: name not matched: sqlt_s81018_exp2.dmp

zip error: Nothing to do! (sqlt_s81018_tcx.zip)
adding: sqlt_s81018_exp.log (deflated 7%)
adding: sqlt_s81018_exp2.log (deflated 7%)
adding: sqlt_s81018_export_driver.sql (deflated 67%)
adding: sqlt_s81018_export_parfile.txt (deflated 73%)
adding: sqlt_s81018_export_parfile2.txt (deflated 34%)
... getting sqlt_s81018_tc_sql.sql out of sqlt repository ...
... getting q.sql out of sqlt repository ...
... getting sqlt_s81018_tc_script.sql out of sqlt repository ...
### tkprof commands below may error out with "could not open trace file". disregard error.

TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:18 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:21 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

### copy command below will error out on linux and unix. disregard error.
/bin/bash: copy: command not found
### tkprof commands below may error out with "could not open trace file". disregard error.

TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
could not open trace file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/S81018_SQLT_TRACE.trc
TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
could not open trace file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/S81018_SQLT_TRACE.trc
### tkprof commands below may error out with "could not open trace file". disregard error.
cat:
TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.


cat:
TKPROF: Release 12.2.0.1.0 - Development on Thu Nov 1 02:32:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.


*_ora_*_S81018_SQLT_TRACE*.trc: No such file or directory
*_ora_*_S81018_SQLT_TRACE*.trc: No such file or directory
adding: sqlt_s81018_sqlt_tkprof_nosort.txt (deflated 91%)
adding: sqlt_s81018_sqlt_tkprof_sort.txt (deflated 90%)
adding: sqlt_s81018_sqlt_tkprof_tnosort.txt (deflated 61%)
adding: sqlt_s81018_sqlt_tkprof_tsort.txt (deflated 61%)
updating: alert_orcldemo.log (deflated 90%)
adding: spfileorcldemo.ora (deflated 80%)
adding: opatch2018-05-24_23-47-33PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-48-11PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-48-15PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-48-18PM_1.log (deflated 72%)
adding: opatch2018-05-24_23-50-30PM_1.log (deflated 72%)
adding: opatch2018-05-25_00-25-44AM_1.log (deflated 72%)
adding: opatch2018-05-25_01-56-29AM_1.log (deflated 72%)
adding: opatch2018-05-25_02-44-45AM_1.log (deflated 72%)
adding: opatch2018-05-25_04-58-05AM_1.log (deflated 72%)
adding: opatch2018-05-25_22-00-14PM_1.log (deflated 72%)
adding: opatch2018-05-25_22-02-46PM_1.log (deflated 72%)
adding: opatch2018-05-25_22-02-50PM_1.log (deflated 72%)
adding: opatch2018-06-04_06-32-54AM_1.log (deflated 72%)
adding: opatch2018-06-06_09-56-59AM_1.log (deflated 72%)
adding: opatch2018-06-07_18-49-37PM_1.log (deflated 72%)
adding: opatch2018-06-07_20-27-51PM_1.log (deflated 72%)
adding: opatch2018-06-07_22-54-04PM_1.log (deflated 72%)
adding: opatch2018-06-08_02-27-30AM_1.log (deflated 72%)
adding: opatch2018-06-08_10-24-45AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-15-19AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-16-02AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-16-15AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-16-28AM_1.log (deflated 72%)
adding: opatch2018-06-26_00-19-03AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-43-04AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-44-01AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-49-05AM_1.log (deflated 72%)
adding: opatch2018-06-27_01-49-11AM_1.log (deflated 72%)
adding: opatch2018-09-18_21-16-12PM_1.log (deflated 72%)
adding: opatch2018-09-21_18-50-48PM_1.log (deflated 72%)
adding: opatch2018-09-22_00-27-46AM_1.log (deflated 74%)
adding: opatch2018-09-22_00-58-49AM_1.log (deflated 74%)
adding: opatch2018-09-22_01-25-35AM_1.log (deflated 86%)
adding: opatch2018-09-22_01-26-15AM_1.log (deflated 96%)
adding: opatch2018-09-22_01-50-06AM_1.log (deflated 72%)
adding: opatch2018-09-22_01-51-07AM_1.log (deflated 71%)
adding: opatch2018-10-24_22-33-26PM_1.log (deflated 72%)
adding: opatch_history.txt (deflated 94%)
### chmod command below will error out on windows. disregard error.
adding: sqlt_s81018_system_stats.sql (deflated 48%)
adding: sqlt_s81018_set_cbo_env.sql (deflated 78%)
adding: sqlt_s81018_metadata1.sql (deflated 65%)
adding: sqlt_s81018_metadata2.sql (deflated 66%)
adding: q.sql (deflated 19%)
adding: plan.sql (deflated 27%)
adding: 10053.sql (deflated 22%)
adding: flush.sql (deflated 5%)
adding: tc.sql (deflated 17%)
adding: sel.sql (deflated 40%)
adding: sel_aux.sql (deflated 34%)
adding: install.sql (deflated 58%)
adding: install.sh (deflated 10%)
adding: pack_tcx.sql (deflated 64%)
adding: sqlt_s81018_schema_stats.sql (deflated 56%)
### chmod command below will error out on windows. disregard error.
adding: sqlt_s81018_system_stats.sql (deflated 48%)
adding: sqlt_s81018_set_cbo_env.sql (deflated 78%)
adding: sqlt_s81018_metadata.sql (deflated 64%)
adding: sqlt_s81018_readme.txt (deflated 79%)
adding: q.sql (deflated 19%)
adding: plan.sql (deflated 27%)
adding: 10053.sql (deflated 22%)
adding: flush.sql (deflated 5%)
adding: tc.sql (deflated 17%)
adding: sel.sql (deflated 40%)
adding: sel_aux.sql (deflated 34%)
adding: xpress.sql (deflated 60%)
adding: xpress.sh (deflated 11%)
adding: setup.sql (deflated 43%)
adding: readme.txt (stored 0%)
adding: tc_pkg.sql (deflated 53%)
adding: sqlt_s81018_purge.sql (deflated 30%)
adding: sqlt_s81018_restore.sql (deflated 43%)
adding: sqlt_s81018_del_hgrm.sql (deflated 27%)
adding: sqlt_s81018_opatch.zip (stored 0%)
zip warning: sqlt_s81018_trc.zip not found or empty
adding: orcldemo_ora_4148_s81018_10053.trc (deflated 87%)
adding: orcldemo_ora_4148_s81018_10053_i1_c0.trc (deflated 88%)
adding: orcldemo_ora_4148_sqlt_s81018_tcb_.trc (deflated 94%)
adding: sqlt_s81018_tcb_.trc (deflated 94%)
adding: sqlt_s81018_10053_explain.trc (deflated 87%)
adding: sqlt_s81018_10053_i1_c0_extract.trc (deflated 88%)
zip warning: name not matched: sqltxtract2.log
adding: sqltxtract.log (deflated 81%)
adding: missing_file.txt (deflated 16%)
zip warning: name not matched: sqltxtract2.log
deleting: sqltxtract.log
deleting: missing_file.txt
zip warning: zip file empty
### ls commands below will error out on windows. disregard error.
### who command below will error out on windows. disregard error.
adding: sqlt_s81018_xpand_sql_driver.sql (stored 0%)
adding: sqlt_s81018_cell_state_begin.txt (stored 0%)
adding: sqlt_s81018_cell_state_begin_and_end.txt (stored 0%)
adding: sqlt_s81018_cell_state_end.txt (stored 0%)
adding: sqlt_s81018_xtract.log (deflated 78%)
adding: sqltxhost.log (deflated 56%)
adding: sqlt_s81018_10053_explain.trc (deflated 87%)
adding: sqlt_s81018_10053_i1_c0_extract.trc (deflated 88%)
adding: sqlt_s81018_cell_state.zip (stored 0%)
adding: sqlt_s81018_driver.zip (stored 0%)
adding: sqlt_s81018_lite.html (deflated 88%)
adding: sqlt_s81018_log.zip (stored 0%)
adding: sqlt_s81018_main.html (deflated 91%)
adding: sqlt_s81018_opatch.zip (stored 0%)
adding: sqlt_s81018_perfhub_0001__.html (deflated 28%)
adding: sqlt_s81018_readme.html (deflated 77%)
adding: sqlt_s81018_sql_detail_active.html (deflated 35%)
adding: sqlt_s81018_tc.zip (stored 0%)
adding: sqlt_s81018_tc_script.sql (deflated 19%)
adding: sqlt_s81018_tc_sql.sql (stored 0%)
adding: sqlt_s81018_tcb.zip (stored 0%)
adding: sqlt_s81018_tcx.zip (stored 0%)
adding: sqlt_s81018_trc.zip (stored 0%)
Archive: sqlt_s81018_xtract_8x615vyks733p.zip
Length Date Time Name
--------- ---------- ----- ----
801938 11-01-2018 02:31 sqlt_s81018_10053_explain.trc
639616 11-01-2018 02:31 sqlt_s81018_10053_i1_c0_extract.trc
610 11-01-2018 02:32 sqlt_s81018_cell_state.zip
7230 11-01-2018 02:32 sqlt_s81018_driver.zip
62342 11-01-2018 02:31 sqlt_s81018_lite.html
912413 11-01-2018 02:32 sqlt_s81018_log.zip
3882333 11-01-2018 02:31 sqlt_s81018_main.html
59911 11-01-2018 02:32 sqlt_s81018_opatch.zip
402939 11-01-2018 02:31 sqlt_s81018_perfhub_0001__.html
22274 11-01-2018 02:31 sqlt_s81018_readme.html
1962 11-01-2018 02:31 sqlt_s81018_sql_detail_active.html
122838 11-01-2018 02:32 sqlt_s81018_tc.zip
207 11-01-2018 02:32 sqlt_s81018_tc_script.sql
35 11-01-2018 02:32 sqlt_s81018_tc_sql.sql
170525 11-01-2018 02:31 sqlt_s81018_tcb.zip
59240 11-01-2018 02:32 sqlt_s81018_tcx.zip
626264 11-01-2018 02:32 sqlt_s81018_trc.zip
--------- -------
7772677 17 files

File sqlt_s81018_xtract_8x615vyks733p.zip for 8x615vyks733p has been created.
sqlt_s81018_sqldx
T
CSV
8x615vyks733p

Parameter 1:
Oracle Pack License (Tuning or Diagnostics) [T|D] (required)

Parameter 2:
Output Type (HTML or CSV or Both) [H|C|B] (required)

Parameter 3:
SQL_ID of the SQL to be analyzed (required)


Values passed:
License: "T"
Output : "CSV"
SQL_ID : "8x615vyks733p"


### ... getting SQL text ...


### ... getting signature ...


### ... getting tables ...


### ... generating dynamic script, please wait ...


sqlt_s81018_sqldx_8x615vyks733p_driver.sql file has been created.

###
### by sql_id
###
2018-11-01/02:32:49 DBA_HIST_SQLTEXT
2018-11-01/02:32:51 DBA_SQLSET_PLANS
2018-11-01/02:32:52 DBA_SQLSET_STATEMENTS
adding: sqlt_s81018_sqldx_8x615vyks733p_DBA_HIST_SQLTEXT.csv (deflated 98%)
adding: sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_STATEMENTS.csv (deflated 96%)
Archive: sqlt_s81018_sqldx_8x615vyks733p_csv.zip
Length Date Time Name
--------- ---------- ----- ----
12297 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_DBA_HIST_SQLTEXT.csv
779840 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_PLANS.csv
16590 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_DBA_SQLSET_STATEMENTS.csv
--------- -------
808727 3 files
adding: sqlt_s81018_sqldx_8x615vyks733p_csv.zip (stored 0%)
Archive: sqlt_s81018_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
7123 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_csv.zip
--------- -------
7123 1 file
###
### by exact signature
###
###
### by force signature
###
###
### by table
###
###
### by global
###
2018-11-01/02:32:52 DBA_HIST_SNAPSHOT
2018-11-01/02:32:52 GV$PARAMETER2
adding: sqlt_s81018_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s81018_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s81018_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
51119 11-01-2018 02:32 sqlt_s81018_sqldx_global_DBA_HIST_SNAPSHOT.csv
3698408 11-01-2018 02:32 sqlt_s81018_sqldx_global_GVsPARAMETER2.csv
--------- -------
3749527 2 files
adding: sqlt_s81018_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s81018_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
7123 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_csv.zip
31844 11-01-2018 02:32 sqlt_s81018_sqldx_global_csv.zip
--------- -------
38967 2 files

sqlt_s81018_sqldx_*.zip files have been created.
adding: sqlt_s81018_sqldx_8x615vyks733p_driver.sql (deflated 89%)
adding: sqldx.log (deflated 59%)

Archive: sqlt_s81018_sqldx_8x615vyks733p_log.zip
Length Date Time Name
--------- ---------- ----- ----
26423 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_driver.sql
2939 11-01-2018 02:32 sqldx.log
--------- -------
29362 2 files

adding: sqlt_s81018_sqldx_8x615vyks733p_log.zip (stored 0%)


SQLDX files have been created.

Archive: sqlt_s81018_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
7123 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_csv.zip
31844 11-01-2018 02:32 sqlt_s81018_sqldx_global_csv.zip
4534 11-01-2018 02:32 sqlt_s81018_sqldx_8x615vyks733p_log.zip
--------- -------
43501 3 files

adding: sqlt_s81018_sqldx.zip (stored 0%)

#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81018_purge.sql out of sqlt repository ...

SQLTXTRACT completed.

• Copy the different folder and Unzip the SQLT extract zip file

[oracle@orcl:run orcldemo] unzip sqlt_s81018_xtract_8x615vyks733p.zip
Archive: sqlt_s81018_xtract_8x615vyks733p.zip
inflating: sqlt_s81018_10053_explain.trc
inflating: sqlt_s81018_10053_i1_c0_extract.trc
extracting: sqlt_s81018_cell_state.zip
extracting: sqlt_s81018_driver.zip
inflating: sqlt_s81018_lite.html
extracting: sqlt_s81018_log.zip
inflating: sqlt_s81018_main.html
extracting: sqlt_s81018_opatch.zip
inflating: sqlt_s81018_perfhub_0001__.html
inflating: sqlt_s81018_readme.html
inflating: sqlt_s81018_sql_detail_active.html
extracting: sqlt_s81018_tc.zip
inflating: sqlt_s81018_tc_script.sql
extracting: sqlt_s81018_tc_sql.sql
extracting: sqlt_s81018_tcb.zip
extracting: sqlt_s81018_tcx.zip
extracting: sqlt_s81018_trc.zip
extracting: sqlt_s81018_sqldx.zip

• Open the sqlt_s81018_main.html report and start the performance analysis.

EXECUTE :-

• It will execute the SQL statements and get analyzed.Its major drawback is that if the SQL being analyzed takes long to execute, this method will also take long.
• This doesn’t take SQL_ID or Hash value as an input.Provide the SQL statement

SQL> start sqltxecute.sql user_objects.sql SQLTEXPLAIN

PL/SQL procedure successfully completed.

Parameter 1:
SCRIPT name which contains SQL and its binds (required)

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Value passed to sqltxecute:
SCRIPT_WITH_SQL: "user_objects.sql"

PL/SQL procedure successfully completed.

***
*** NOTE:
*** If you get error below it means SQLTXPLAIN is not installed:
*** PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared.
*** In such case look for errors in NN_*.log files created during install.
***

***
*** NOTE:
*** If running as SYS in 12c make sure to review sqlt_instructions.html first
***

SQLT_VERSION
----------------------------------------
SQLT version number: 12.2.180725
SQLT version date : 2018-07-25
Installation date : 2018-10-27/00:59:18

... please wait ...
adding: alert_orcldemo.log (deflated 93%)

NOTE:
You used the XECUTE method connected as HARI.

In case of a session disconnect please verify the following:
1. There are no errors in sqltxecute.log or sqltxecute2.log.
2. Your SQL contains token "^^unique_id" within a comment.
3. Your SQL ends with a semi-colon ";".
4. You connected as the application user that issued original SQL.
5. Script user_objects.sql can execute stand-alone connected as HARI
6. User HARI has been granted SQLT_USER_ROLE.

In case of errors ORA-03113, ORA-03114 or ORA-07445 please just
re-try this SQLT method. This tool handles some of the errors behind
a disconnect when executed a second time.

To actually diagnose the problem behind the disconnect, read ALERT
log and provide referenced traces to Support. After the root cause
of the disconnect is fixed then reset SQLT corresponding parameter.

... executing user_objects.sql ...

In case of a disconnect review sqltxecute2.log and user_objects_output_s81019.txt

To monitor progress, login into another session and execute:
SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;

... collecting diagnostics details, please wait ...

In case of a disconnect review log file in current directory
If running as SYS in 12c make sure to review sqlt_instructions.html first

File sqlt_s81019_xecute.zip for user_objects.sql has been created.
sqlt_s81019_sqldx
T
CSV
0d7hz8d1y5vw6

Parameter 1:
Oracle Pack License (Tuning or Diagnostics) [T|D] (required)

Parameter 2:
Output Type (HTML or CSV or Both) [H|C|B] (required)

Parameter 3:
SQL_ID of the SQL to be analyzed (required)

Values passed:
License: "T"
Output : "CSV"
SQL_ID : "0d7hz8d1y5vw6"

### ... getting SQL text ...

### ... getting signature ...

### ... getting tables ...

### ... generating dynamic script, please wait ...

sqlt_s81019_sqldx_0d7hz8d1y5vw6_driver.sql file has been created.

###
### by sql_id
###
2018-11-02/01:07:17 DBA_HIST_ACTIVE_SESS_HISTORY
2018-11-02/01:07:17 DBA_HIST_SQLTEXT
2018-11-02/01:07:17 DBA_SQLSET_PLANS
2018-11-02/01:07:18 DBA_SQLSET_STATEMENTS
2018-11-02/01:07:18 GV$ACTIVE_SESSION_HISTORY
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv (deflated 84%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv (deflated 99%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv (deflated 95%)
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv (deflated 83%)
Archive: sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
Length Date Time Name
--------- ---------- ----- ----
8088 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv
12297 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv
946940 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv
16590 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv
7740 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv
--------- -------
991655 5 files
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip (stored 0%)
Archive: sqlt_s81019_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
--------- -------
11492 1 file
###
### by exact signature
###
###
### by force signature
###
###
### by table
###
###
### by global
###
2018-11-02/01:07:18 DBA_HIST_SNAPSHOT
2018-11-02/01:07:18 GV$PARAMETER2
adding: sqlt_s81019_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s81019_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s81019_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
55335 11-02-2018 01:07 sqlt_s81019_sqldx_global_DBA_HIST_SNAPSHOT.csv
3698408 11-02-2018 01:07 sqlt_s81019_sqldx_global_GVsPARAMETER2.csv
--------- -------
3753743 2 files
adding: sqlt_s81019_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s81019_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
32118 11-02-2018 01:07 sqlt_s81019_sqldx_global_csv.zip
--------- -------
43610 2 files

sqlt_s81019_sqldx_*.zip files have been created.
adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_driver.sql (deflated 89%)
adding: sqldx.log (deflated 59%)

Archive: sqlt_s81019_sqldx_0d7hz8d1y5vw6_log.zip
Length Date Time Name
--------- ---------- ----- ----
28251 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_driver.sql
2928 11-02-2018 01:07 sqldx.log
--------- -------
31179 2 files

adding: sqlt_s81019_sqldx_0d7hz8d1y5vw6_log.zip (stored 0%)

SQLDX files have been created.

Archive: sqlt_s81019_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_csv.zip
32118 11-02-2018 01:07 sqlt_s81019_sqldx_global_csv.zip
4605 11-02-2018 01:07 sqlt_s81019_sqldx_0d7hz8d1y5vw6_log.zip
--------- -------
48215 3 files

adding: sqlt_s81019_sqldx.zip (stored 0%)

#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81019_purge.sql out of sqlt repository ...

SQLTXECUTE completed.

XTRXEC :-

• It is a combination feature of XTRACT and XECUTE for DBA. First XTARCT generates a script that contains extracted SQL and expensive plan found for requested statement. XTRXEC then executes the XECUTE phase using the script created before.
• This method only need SQL_ID/Hash value and sqltxplain_password. This method is most commonly and recommended method for SQL Performance related tuning issues.

SQL> START sqltxtrxec.sql 8x615vyks733p Oracle$123

PL/SQL procedure successfully completed.

Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Value passed to sqltxtrxec:
SQL_ID_OR_HASH_VALUE: "8x615vyks733p"
#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81021_purge.sql out of sqlt repository ...

SQLTXECUTE completed.
updating: sqlt_s81020_tc_script.sql (deflated 18%)

  adding: sqlt_s81020_xtract_8x615vyks733p.zip (stored 0%)
  adding: sqlt_s81021_xecute.zip (stored 0%)
  adding: sqltxtrxec.log (deflated 75%)

PL/SQL procedure successfully completed.

SQLTXTRXEC completed.

XPLAIN :-

This method is based on the EXPLAIN PLAN FOR command, therefore it is to bind variables referenced by your SQL statement. Use this method only if XTRACT or XECUTE are not possible.

SQL> START sqltxplain.sql /home/oracle/sqlt/run/user_objects.sql

PL/SQL procedure successfully completed.

WARNING:
You are using SQLT XPLAIN method.
If you were requested by Oracle Support to use
XTRACT or XECUTE, then do not use this XPLAIN method.

Be aware that XPLAIN method cannot perform bind peeking
thus you will get an EXPLAIN PLAN instead of actual
EXECUTION PLAN.

Replacing bind variables with literal values does not
guarantee the generated plan to be the same than the one
produced by XTRACT or XECUTE. Thus the plan generated by
XPLAIN might not be useful to progress your issue.

Parameter 1:
Name of file that contains SQL to be explained (required)

Paremeter 2:
SQLTXPLAIN password (required)

Enter value for 2: Oracle$123

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Value passed:
FILE_WITH_ONE_SQL: "/home/oracle/sqlt/run/user_objects.sql"

PL/SQL procedure successfully completed.

WARNING:
You are using SQLT XPLAIN method.

If you were requested by Oracle Support to use
XTRACT or XECUTE, then do not use this XPLAIN method.

Be aware that XPLAIN method cannot perform bind peeking
thus you will get an EXPLAIN PLAN instead of actual
EXECUTION PLAN.

Replacing bind variables with literal values does not
guarantee the generated plan to be the same than the one
produced by XTRACT or XECUTE. Thus the plan generated by
XPLAIN might not be useful to progress your issue.

***
*** NOTE:
*** If you get error below it means SQLTXPLAIN is not installed:
*** PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared.
*** In such case look for errors in NN_*.log files created during install.
***

***
*** NOTE:
*** If running as SYS in 12c make sure to review sqlt_instructions.html first
***

SQLT_VERSION
----------------------------------------
SQLT version number: 12.2.180725
SQLT version date : 2018-07-25
Installation date : 2018-10-27/00:59:18

... please wait ...
adding: alert_orcldemo.log (deflated 93%)

... reading file /home/oracle/sqlt/run/user_objects.sql ...

File sqlt_s81023_xplain.zip for /home/oracle/sqlt/run/user_objects.sql has been created.
sqlt_s81023_sqldx
T
CSV
0d7hz8d1y5vw6

Parameter 1:
Oracle Pack License (Tuning or Diagnostics) [T|D] (required)

Parameter 2:
Output Type (HTML or CSV or Both) [H|C|B] (required)

Parameter 3:
SQL_ID of the SQL to be analyzed (required)

Values passed:
License: "T"
Output : "CSV"
SQL_ID : "0d7hz8d1y5vw6"

### ... getting SQL text ...

### ... getting signature ...

### ... getting tables ...

### ... generating dynamic script, please wait ...

sqlt_s81023_sqldx_0d7hz8d1y5vw6_driver.sql file has been created.

###
### by sql_id
###
2018-11-02/02:37:57 DBA_HIST_ACTIVE_SESS_HISTORY
2018-11-02/02:37:57 DBA_HIST_SQLTEXT
2018-11-02/02:37:57 DBA_SQLSET_PLANS
2018-11-02/02:37:57 DBA_SQLSET_STATEMENTS
2018-11-02/02:37:57 GV$ACTIVE_SESSION_HISTORY
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv (deflated 84%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv (deflated 99%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv (deflated 99%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv (deflated 95%)
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv (deflated 83%)
Archive: sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
Length Date Time Name
--------- ---------- ----- ----
8088 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_ACTIVE_SESS_HISTORY.csv
12297 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_HIST_SQLTEXT.csv
946940 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_PLANS.csv
16590 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_DBA_SQLSET_STATEMENTS.csv
7740 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_GVsACTIVE_SESSION_HISTORY.csv
--------- -------
991655 5 files
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip (stored 0%)
Archive: sqlt_s81023_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
--------- -------
11492 1 file
###
### by exact signature
###
###
### by force signature
###
###
### by table
###
###
### by global
###
2018-11-02/02:37:57 DBA_HIST_SNAPSHOT
2018-11-02/02:37:57 GV$PARAMETER2
adding: sqlt_s81023_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 96%)
adding: sqlt_s81023_sqldx_global_GVsPARAMETER2.csv (deflated 99%)
Archive: sqlt_s81023_sqldx_global_csv.zip
Length Date Time Name
--------- ---------- ----- ----
45322 11-02-2018 02:37 sqlt_s81023_sqldx_global_DBA_HIST_SNAPSHOT.csv
3698408 11-02-2018 02:37 sqlt_s81023_sqldx_global_GVsPARAMETER2.csv
--------- -------
3743730 2 files
adding: sqlt_s81023_sqldx_global_csv.zip (stored 0%)
Archive: sqlt_s81023_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
31633 11-02-2018 02:37 sqlt_s81023_sqldx_global_csv.zip
--------- -------
43125 2 files

sqlt_s81023_sqldx_*.zip files have been created.
adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_driver.sql (deflated 89%)
adding: sqldx.log (deflated 59%)

Archive: sqlt_s81023_sqldx_0d7hz8d1y5vw6_log.zip
Length Date Time Name
--------- ---------- ----- ----
28250 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_driver.sql
2927 11-02-2018 02:37 sqldx.log
--------- -------
31177 2 files

adding: sqlt_s81023_sqldx_0d7hz8d1y5vw6_log.zip (stored 0%)

SQLDX files have been created.

Archive: sqlt_s81023_sqldx.zip
Length Date Time Name
--------- ---------- ----- ----
11492 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_csv.zip
31633 11-02-2018 02:37 sqlt_s81023_sqldx_global_csv.zip
4606 11-02-2018 02:37 sqlt_s81023_sqldx_0d7hz8d1y5vw6_log.zip
--------- -------
47731 3 files

adding: sqlt_s81023_sqldx.zip (stored 0%)

#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81023_purge.sql out of sqlt repository ...

SQLTXPLAIN completed.

XTRSBY Method :-

Use this method if you need to analyze a SQL executed on a Data Guard or stand-by read-only database. You need to know the SQL_ID or the Hashvalue of the SQL to be analyzed.

• Create on Primary database a link to read-only database connecting as any user that has access to the data dictionary. we need DBA privileges access to do it.

SQL> CREATE PUBLIC DATABASE LINK DEMO CONNECT TO HARI IDENTIFIED by hari USING '(DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=orcldemo.localdomain.com)(PORT=1521))(CONNECT_DATA=(SID = orcldemo)))';

Database link created.

• If the SQL is still in memory in the read-only database, then XTRSBY finds it and provides a set of diagnostics files, else XTRSBY errors out.
• XTRSBY need 3 parameters: the SQL _ID, the DB_LINK name, and the SQLTXPLAIN password
• To use this XTRSBY method, be sure SQLT has been installed on the Primary first, and replicated into the read-only database.

SQL>START sqltxtrsby.sql 0d7hz8d1y5vw6 Oracle$123 orcldemo

PL/SQL procedure successfully completed.

Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Parameter 3:
DBLINK to stand-by database (required)

Values passed to sqltxtrsby:
SQL_ID_OR_HASH_VALUE: "0d7hz8d1y5vw6"
DB_LINK : "@orcldemo"

PL/SQL procedure successfully completed.

The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s81024_purge.sql out of sqlt repository ...

SQLTXTRSBY completed.

EBSR12_CLONING

Oracle EBS R12.2 ( E-Business Suite ) Step by Step Cloning – Oracle EBS Clone

Reference -

Click on the below link .

EBSR12_CLONING_STEP

Explained EBS r12 cloning in very easy form .
Just follow the steps  from  the website for cloning.

EBS Cloning requests are done everytime for testing and development purposes and it is most common task of Apps DBA.

While cloning EBS, we will clone both Oracle database and EBS application. For this reason, it is necessary to carry out separate tasks on both sides. I’ll explain all tasks step by step sharing scripts and their outputs.

EBS Cloning steps are as follows.

1. Running Pre-Clone on the Source Apps Tier and DB Tier
2. Backup Full database and Archivelogs via RMAN
3. Copy Some Application Tier directories from Source to Target
4. Copy Oracle database Home and backups from Source to Target
5. Configure and start Oracle Instance on Target
6. Duplicate & Restore and Recover database and open clone database on Target
7. Run Post Clone Steps on Target Database Server ( DB Tier )
8. Run Post Clone Steps on Target Application Server ( Apps Tier )
9. Run Pre-Clone on the Target Apps Tier for Creating Patch edition
10. Copy EBSpps directory from Run edition to patch edition on Target Application server
11. Run Post Clone on Patch Edition of Target Application server
12. Start EBS Clone Environment