Oracle Video Tutorial With Demo (Performance Tuning,GoldenGate,Rac,Dataguard)

Videos Library

I have uploaded the Performance Tuning,GoldenGate,RAC And Dataguard Videos for the below link to download.

https://mega.nz/#F!d00GTQQb!XKuAkQ6LQo7oi711-0ViiA

Documents Library

I have uploaded the Oracle DBA documents, interview questions and materials

Link to download

https://mega.nz/#F!7m5nlDaL

Encryption key

!gwbONpJRzF_b-H-DtnCxLQ

 

Oracle Trace File Analyzer Tips And Tricks

TFA

Trace File Analyzer is new utility which can be installed on the database nodes either stand alone or cluster nodes.

In order to collect the diagnostics of 8 node for example, then we have to review alert log, listener logs whatever on all the nodes that become very lengthy procedure and also not easy to merge the information based on the all nodes.

Hence Oracle introduced TFA – Trace file analyzer and this made easy job for DBA’s, TFA utility or bundle can be downloaded from MOS.

TFACTL INSTALLATION:

 

[root@test18c ~]# cd /u01/app/oracle/tfa/bin/
[root@test18c bin]# ./tfactl
tfactl> orachk
Using Orachk : /u01/app/oracle/tfa/test18c/tfa_home/ext/orachk/orachk


Running orachk
----------------------------------------------------------
PATH : /u01/app/oracle/tfa/test18c/tfa_home/ext/orachk
VERSION : 18.3.0_20180808
COLLECTIONS DATA LOCATION : /u01/app/oracle/tfa/repository/suptools/test18c/orachk/root
----------------------------------------------------------

List of running databases

1. test18c
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1]. 1
. .
. .

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . .
. . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status 
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
test18c No No Yes No No Yes test18c
-------------------------------------------------------------------------------------------------------


Copying plug-ins

. .
. . . . . .

*** Checking Best Practice Recommendations ( PASS / WARNING / FAIL ) ***


.

Collections and audit checks log file is
/u01/app/oracle/tfa/repository/suptools/test18c/orachk/root/orachk_test18c_test18c_111318_034400/log/orachk.log

============================================================
Node name - test18c
============================================================

Collecting - Database Parameters for test18c database
Collecting - Database Undocumented Parameters for test18c database
Collecting - RDBMS Feature Usage for test18c database
Collecting - CPU Information
Collecting - Disk I/O Scheduler on Linux
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - OS Packages
Collecting - Operating system release information and kernel version
Collecting - Patches for RDBMS Home
Collecting - Table of file system defaults
Collecting - number of semaphore operations per semop system call
Collecting - Disk Information
Collecting - Linux Operating system health check using vmpscan.sh
Collecting - Root user limits
Collecting - Verify no database server kernel out of memory errors


Data collections completed. Checking best practices on test18c.
------------------------------------------------------------

CRITICAL => Bash is vulnerable to code injection (CVE-2014-6271)
WARNING => Linux swap configuration does not meet recommendation
INFO => Important Storage Minimum Requirements for Grid & Database Homes
WARNING => Non-AWR Space consumption is greater than or equal to 50% of total SYSAUX space. for test18c
INFO => Most recent ADR incidents for /u01/app/oracle/product/18.0.0/dbhome_1
INFO => Oracle GoldenGate failure prevention best practices
INFO => user_dump_dest has trace files older than 30 days for test18c
FAIL => Database parameter DB_BLOCK_CHECKSUM is not set to recommended value on test18c instance
FAIL => Database parameter DB_LOST_WRITE_PROTECT is not set to recommended value on test18c instance
WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for test18c
INFO => Operational Best Practices
INFO => Database Consolidation Best Practices
INFO => Computer failure prevention best practices
INFO => Data corruption prevention best practices
INFO => Logical corruption prevention best practices
INFO => Database/Cluster/Site failure prevention best practices
INFO => Client failover operational best practices
WARNING => Duplicate objects were found in the SYS and SYSTEM schemas for test18c
WARNING => Oracle clusterware is not being used
WARNING => RAC Application Cluster is not being used for database high availability on test18c instance
WARNING => DISK_ASYNCH_IO is NOT set to recommended value for test18c
FAIL => Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for test18c
FAIL => Flashback on PRIMARY is not configured for test18c
INFO => Database failure prevention best practices
WARNING => fast_start_mttr_target has NOT been changed from default on test18c instance
WARNING => Database Archivelog Mode should be set to ARCHIVELOG for test18c
FAIL => Primary database is not protected with Data Guard (standby database) for real-time data protection and availability for test18c
FAIL => Active Data Guard is not configured for test18c
INFO => Parallel Execution Health-Checks and Diagnostics Reports for test18c
INFO => Oracle recovery manager(rman) best practices
WARNING => Linux Disk I/O Scheduler should be configured to Deadline
WARNING => Consider investigating changes to the schema objects such as DDLs or new object creation for test18c
WARNING => Consider investigating the frequency of SGA resize operations and take corrective action for test18c
Best Practice checking completed. Checking recommended patches on test18c
--------------------------------------------------------------------------------
Collecting patch inventory on ORACLE_HOME /u01/app/oracle/product/18.0.0/dbhome_1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1 Recommended RDBMS patches for 180000 from /u01/app/oracle/product/18.0.0/dbhome_1 on test18c
--------------------------------------------------------------------------------
Patch# RDBMS ASM type Patch-Description 
--------------------------------------------------------------------------------
28090523 yes merge DATABASE RELEASE UPDATE 18.3.0.0.0
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
RDBMS homes patches summary report
--------------------------------------------------------------------------------
Total patches Applied on RDBMS Applied on ASM ORACLE_HOME
--------------------------------------------------------------------------------
1 1 0 /u01/app/oracle/product/18.0.0/dbhome_1
--------------------------------------------------------------------------------

------------------------------------------------------------
Detailed report (html) - /u01/app/oracle/tfa/repository/suptools/test18c/orachk/root/orachk_test18c_test18c_111318_034400/orachk_test18c_test18c_111318_034400.html

UPLOAD [if required] - /u01/app/oracle/tfa/repository/suptools/test18c/orachk/root/orachk_test18c_test18c_111318_034400.zip


tfactl>

 

Starting from 12.2 this bundle is included with RDBMS software and again this is optional when we run the root.sh script, still we can skip this if it’s not required.

we will see how to initiate the TFA when we run the root.sh of RDBMS.

If we crack the log file then we can see very much detailed information such as scanned trace directories, number of hosts and with the help preview.

 

 

tfactl> summary
LOGFILE LOCATION : /u01/app/oracle/tfa/repository/suptools/test18c/summary/root/20181113030332/log/summary_command_20181113030332_test18c_4674.log

Component Specific Summary collection :
- Collecting ACFS details ... Done.
- Collecting DATABASE details ... Done.
- Collecting PATCH details ... Done.
- Collecting LISTENER details ... Done.
- Collecting NETWORK details ... Done.
- Collecting OS details ... Done.
- Collecting TFA details ... Done.
- Collecting SUMMARY details ... Done.

Prepare Clusterwide Summary Overview ... 
tfactl>
tfactl>

Example of Collecting Diagnostic Data

We can access the list of commands using the “tfactl <command> -help“, in this example we will collect sample diagnostic data using TFA.

 

How to check whether TFA is running or not?

This is simple by grepping the word “tfa” from host level as

 

MENU:



 

Basic TFACTL commands include:

tfactl start: Starts the Oracle Trace File Analyzer daemon on the local node.

tfactl stop: Stops the Oracle Trace File Analyzer daemon on the local node.

tfactl enable: Enables automatic restart of the Oracle Trace File Analyzer daemon after a failure or system reboot.

tfactl disable: Stops any running Oracle Trace File Analyzer daemon and disables automatic restart.

tfactl uninstall: Removes Oracle Trace File Analyzer from the local node.

tfactl syncnodes: Generates and copies Oracle Trace File Analyzer certificates from one Oracle Trace File Analyzer node to other nodes.

tfactl restrictprotocol: Restricts the use of certain protocols.

tfactl status: Checks the status of an Oracle Trace File Analyzer process. The output is same as tfactl print status.

tfactl diagnosetfa: Use the tfactl diagnosa tfa command to collect Oracle Trace File Analyzer diagnostic data from the local node to help diagnose issues with Oracle Trace File Analyzer.

tfactl host: Use the tfactl host command to add hosts to, or remove hosts from the Oracle Trace File Analyzer configuration.

tfactl set: Use the tfactl set command to enable or disable, or modify various Oracle Trace File Analyzer functions.

tfactl access: Use the tfactl access command to allow non-root users to have controlled access to Oracle Trace File Analyzer and to run diagnostic collections.

OSWatcher: http://www.oracledbwr.com/tuning/analyze-oracle-server-diagnostic-information-using-oswatcher-tool/

OraChk : http://www.oracledbwr.com/tuning/health-checks-for-the-oracle-stack-using-orachk/

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:       https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:       https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page:  https://www.facebook.com/dbahariprasath/?
Twitter:        https://twitter.com/hariprasathdba

Oracle Tuning-Analyze SQL with SQL Tuning Advisor

SQL TUNING ADVISOR :-

  • The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.
  • The output of the SQL Tuning Advisor is in the form of an recommendations, along with a rationale for each recommendation and its expected benefit.The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
  • You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.
  • Find the problematic SQL_ID from v$session you would like to analyze. Usually the AWR has the top SQL_IDs column.

In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

sqlplus / as sysdba
GRANT ADVISOR TO HARI;
CONN HARI/hari;
Steps to tune the problematic SQL_ID using SQL TUNING ADVISOR :-

Create Tuning Task :

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '43x11xxhxy1j7',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning task :

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/

Monitor the task executing using below query:

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ='my_sql_tuning_task_1';

TASK_NAME                      STATUS
------------------------------ -----------
my_sql_tuning_task_1           COMPLETED

Check the status is completed for the task and we can get recommendations of the advisor.

Report Tuning task :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1')

-----------------------------------------------------------------------
GENERAL INFORMATION SECTION
-----------------------------------------------------------------------

Tuning Task Name                  : my_sql_tuning_task_1

Tuning Task Owner                 : SYS

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 11/10/2018 19:47:27

Completed at                      : 11/10/2018 19:47:54
--------------------------------------------------------------------
SQL_ID : 43x11xxhxy1j7
SQL_staement : SELECT * FROM HARI.EMP

Number of SQL Profile Findings    : 1
--------------------------------------------------------------------
FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.94%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_1',replace => TRUE);

To get detailed information :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;

Drop SQL Tuning task :

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'my_sql_tuning_task_1');
END;
/
Another method for adding new task using SQL TUNING ADVISOR :-
Check the PLAN_HASH_VALUE got changed for the specific statement and get SNAP_ID to create a tuning task.
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
and executions_delta > 0
order by 1, 2, 3
/

Enter value for sql_id: 483wz173punyb

SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ------ ------------------------------ ------------- --------
15694 1 10-NOV-18 01.00.04.047 AM 483wz173punyb 2391860790 1 4,586.818 33,924,912.0
15695 1 10-NOV-18 02.00.18.928 AM 483wz173punyb 2 1,488.867 0,064,449.0
15696 1 10-NOV-18 03.00.03.192 AM 483wz173punyb 2 1,053.459 8,780,977.0
Create a tuning task for the specific statement from AWR snapshots:-
Create,Execute and Report the task from given AWR snapshot IDs.
Create Task,

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 1868,
end_snap => 1894,
sql_id => '483wz173punyb',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => '483wz173punyb_tuning_task',
description => 'Tuning task for statement 483wz173punyb in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execute Task,

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '483wz173punyb_tuning_task');

Report task,

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('483wz173punyb_tuning_task') AS recommendations FROM dual;
Interrupt Tuning task :
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '483wz173punyb_tuning_task');
Resume Tuning task :
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '483wz173punyb_tuning_task');
Cancel Tuning task :
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => '483wz173punyb_tuning_task');
Reset Tuning task :
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => '483wz173punyb_tuning_task');
Catch Me On:- Hariprasath Rajaram

LinkedIn:   https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page:  https://www.facebook.com/dbahariprasath/?
Twitter:        https://twitter.com/hariprasathdba

 

Oracle SQLTXPLAIN (SQLT) Tips And Tricks

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 :

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.

XECUTE :-

  • 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.

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:   https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page:  https://www.facebook.com/dbahariprasath/?
Twitter:        https://twitter.com/hariprasathdba

 

Oracle Oradebug Utility Tips And Tricks

 Oradebug

Introduction

Oracle utility is called oradebug. This tool is primarily used by Oracle worldwide customer support .With oradebug utility you can literally see the database engine.

The oradebug is especially useful when things go very bad – e.g. the database just stops, hangs, or the database keeps crashing with the ORA-0600 error!

To run this tool you must have administrator privileges.
Among the many useful things that can be done with oradebug are:
– enabling/disabling the SQL tracing for another user’s session.
– suspending intensive processes
– finding information about shared memory and semaphores
– closing the trace file so that new one can be generated
– manipulating and dumping internal structures
– wake up processes etc.

 

HELP command

The ORADEBUG HELP command lists the commands available within ORADEBUG

These vary by release and platform. Commands appearing in this help do not necessarily work for the release/platform on which the database is running

 

 

Background Processes List

SELECT b.name, p.pid FROM gv$bgprocess b, gv$process p WHERE b.paddr = p.addr ORDER BY 1;

Dispatcher Processes List

SELECT d.name, p.pid FROM gv$dispatcher d, gv$process p WHERE d.paddr = p.addr;

Job Queue Process List

SELECT s.paddr, s.sid, j.job FROM gv$session s, dba_jobs_running j WHERE s.sid = j.sid;

SELECT pid FROM gv$process  WHERE addr = ’46’;

Parallel Execution Slave  Processes List

SELECT pid, server_name, status FROM gv$px_process;

Shared Server Processes List

SELECT s.name, p.pid FROM gv$shared_server s, gv$process p WHERE s.paddr = p.addr;

SGA Variables List

SELECT ksmfsnam FROM x$ksmfsv WHERE ksmfsnam LIKE ‘%\_’ ESCAPE ‘\’;

Switches

CALL

Invoke function with arguments

oradebug call [-t count] <func> [arg1]…[argn]ora

SQL> oradebug call ksmget_sgamaxalloc
CLOSE_TRACE

Close the trace file

oradebug close_trace

SQL> oradebug close_trace
CORE

Dump core without crashing process

oradebug core

SQL> oradebug core
DUMPSGA

Dump fixed SGA

oradebug dumpsga [<bytes>]

SQL> oradebug dumpsga 

c:\oracle\diag\rdbms\orabase\orabase\trace\orabase_ora_2120.trc
DUMPTYPE

Print/dump an address with type info

oradebug dumptype <address> <type> <count>
TBD
DUMPVAR

Print/dump a fixed PGA/SGA/UGA variable

oradebug dumpvar <pga|sga|uga> <name> [level]

SQL> oradebug setmypid

SQL> oradebug dumpvar SGA kcbnbh
EVENT

Set trace event in process

oradebug EVENT <event> TRACE NAME CONTEXT FOREVER, LEVEL <level>

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
FFBEGIN

Flash Freeze the Instance

oradebug ffbegin

SQL> oradebug ffbegin
FFDEREGISTER

FF deregister instance from cluster

oradebug ffderegistger

SQL> oradebug ffderegister
FFRESUMEINST

Resume a flash frozen instance

oradebug ffresumeinst

SQL> oradebug ffresumeinst
FFSTATUS

Flash freeze status of instance

oradebug ffstatus

SQL> oradebug ffstatus

FFTERMINST

Call exit and terminate instance

oradebug ffterminst

SQL> oradebug ffterminst
FLUSH

Flush pending writes to trace file

oradebug flush

SQL> oradebug flush
HANGANALYZE

Analyze system hang for stand-alone

oradebug hanganalzye [level] [syslevel]

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug tracefile_name

SQL> oradebug hanganalyze 4

Analyze system hang for RAC

SQL> oradebug setmypid

SQL> oradebug -g def hanganalyze 1

SQL> oradebug flush
HELP

Describe one or all commands

oradebug help [<command>]

SQL> oradebug help

SQL> oradebug help flush
IPC

Dump IPC information

oradebug ipc

SQL> oradebug setmypid
Statement processed.

SQL> oradebug ipc
Information written to trace file C:\ORACLE\diag\rdbms\orabase\orabase\trace\orabase_ora_10988.trc
IPC_CHECKSUM

Enable/Disable IPC Checksumming

oradebug ipc_checksum<light/medium/full>

SQL> oradebug ipc_checksum full
IPC_TRACE

Modify IPC trace flags

oradebug ipc_trace<module> <trace_flags> <trace_level>
TBD
LKDEBUG

Invoke global enqueue service debugger

oradebug lkdebug

SQL> oradebug lkdebug
MAPCOWSGA

Map SGA as COW

oradebug mapcowsga <SGA dump dir>

SQL> oradebug mapcowsga "c:\temp"
NSDBX

Invoke CGS name-service debugger

oradebug nsdbx

SQL> oradebug nsdbx
PDUMP

Invoke named dump periodically

PDUMP [interval=<interval>] [ndumps=<count>] <dump_name> <lvl> [addr]
TBD
PEEK

Print/Dump memory

oradebug peek <addr> <len> [level]

SQL> oradebug peek oradebug peek 0x075731F8 12
POKE

Modify memory. Never perform this function on a production database!

oradebug poke <addr> <len> <value>

SQL> oradebug poke 0x20005F0C 4 0x46495845
PROCSTAT

Dump process statistics oradebug procstat

SQL> oradebug setmypid

SQL> oradebug procstat
SQL> select pid,name from v$process p, v$bgprocess b where b.paddr = p.addr;

SQL> 

PID NAME
-- -----
2   PMON
3   CLMN
4   PSP0
5   VKTM
6   GEN0
7   MMAN
8   OFSD
9   GEN1
10  SCMN
11  DIAG
12  SCMN

PID NAME
-- -----
13  DBRM
14  VKRM
15  SVCB
16  PMAN
17  DIA0
18  DBW0
19  LGWR
20  CKPT
21  SMON
22  SMCO
23  RECO

PID NAME
-- -----
24  W001
25  LREG
27  PXMN
28  FENC
29  MMON
30  MMNL
32  TMON
33  W003
34  TT00
35  ARC0
36  TT01

PID NAME
-- -----
37  ARC1
38  ARC2
39  ARC3
40  TT02
41  AQPC
42  W005
43  QM02
44  W007
45  W000
46  Q003
47  CJQ0

PID NAME
-- -----
49  Q002
54  W004
55  W006

47 rows selected.

SQL>
RESUME

Resume execution oradebug resume

SQL> oradebug resume
SESSION_EVENT

Set trace event in session

oradebug session_event <text>

SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1
SETINST

Set instance list

oradebug setinst <instance# .. | all>

SQL> oradebug setinst "1"
SETMYPID

Sets the oradebug PID to the current process

oradebug setmypid

SQL> oradebug setmypid
SETORAPID

Set PID of Oracle process to debug

oradebug setorapid <orapid> ['force']

 

SETORAPNAME

Set Oracle process name to debug

oradebug setorapname <orapname>

 

 

SETOSPID

Set OS pid of process to debug. The operating system process ID is the PID on Unix systems and the thread number for Windows systems

oradebug setospid <ospid>

Do not use as it often fails. Use setorapid instead.
SETTRACEFILEID

Set tracefile identifier oradebug settracefileid <identifier name>

SQL> oradebug settracefileid odebug
SETVAR

Modify a fixed PGA/SGA/UGA variable

oradebug setvar <pga|sga|uga> <name> <value>

SQL> oradebug setvar SGA kcfdfk 200
SGATOFILE

Dump SGA to file oradebug sgatofile <SGA dump dir>

SQL> oradebug ffbegin

SQL> oradebug sgatofile "c:\temp"

SQL> oradebug ffresumeinst
SHORT_STACK

Get abridged OS stack

oradebug short_stack

SQL> oradebug short_stack
SHOW

Show watchpoints

oradebug show <local|global|target> watchpoint <id>

SQL> oradebug show global watchpoints
SKDSTTPCS

Helps translate PCs to names

oradebug skdsttpcs <ifname> <ofname>
TBD
SUSPEND

Suspends the current process

oradebug suspend

SQL> oradebug suspend
TRACEFILE_NAME

Get trace file name

Will not return a value on Windows systems

oradebug tracefile_name

SQL> oradebug tracefile_name
UNLIMIT

Unlimit the size of the trace file

oradebug unlimit

SQL> oradebug unlimit
WAKEUP

Wake up Oracle process

oradebug wakeup <orapid>

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Oracle Automatic Diagnostic Repository

ADRCI (AUTOMATIC DIAGNOSTIC REPOSITORY COMMAND INTERPRETER) :-

ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables you to:

  1. View diagnostic data within the Automatic Diagnostic Repository (ADR).
  2. View Health Monitor reports.
  3. Package incident and problem information into a zip file for transmission to Oracle Support.
  • The ADR is a file-based repository for database diagnostic data such as traces, dumps, the alert log, health monitor reports, and more.
  • It has a unified directory structure across multiple instances and multiple products.
  • Beginning with Release 11g, the database, Automatic Storage Management (ASM), and other Oracle products or components store all diagnostic data in the ADR.
  • Each instance of each product stores diagnostic data underneath its own ADR home directory.
  • For example,In an Oracle Real Application Clusters environment with shared storage and ASM, each database instance and each ASM instance has a home directory within the ADR. The ADR’s unified directory structure enables customers and Oracle Support to correlate and analyze diagnostic data across multiple instances and multiple products.
  • Most of the diagnostic logs stored in the admin directory in Oracle 10g and earlier has now been moved to the Automatic Diagnostic Repository,or ADR directory, in Oracle 11g.  The ADR directory is created in $ORACLE_BASE/diag and contains a subdirectory for each different Oracle installation type on the system and may also contain some empty directories for products which are not installed.  Within the database, the ADR location is defined by the diagnostic_dest initialization parameter, and the background_dump_dest, user_dump_dest and similar parameters have been deprecated.

Definitions :

  • Automatic Diagnostic Repository (ADR)
  • Problem
  • Incident
  • Problem Key
  • Incident Package
  • Finalizing
  • ADR Home
  • ADR Base
  • Homepath

ADR directory structure :

Subdirectories Under ADR Base :

ADR repository location using V$DIAG_INFO view :

Diagnostic_dest location :-

SQL> show parameter diagnostic_dest;

NAME            TYPE    VALUE
--------------- ------- ------------------
diagnostic_dest string  /oradb/app/oracle

ADRCI command interpreter :

Viewing the alert log:-

One is present in the conventional text format, per OFA in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/trace This location is determined by the new initialization parameter DIAGNOSTIC_DEST, while BACKGROUND_DUMP_DEST is deprecated in 11g.

The other one is in XML format placed in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/alert.

[oracle@orcl:~ orcldemo] adrci

ADRCI: Release 12.2.0.1.0 - Production on Wed Oct 24 21:15:43 2018

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


ADR base = "/oradb/app/oracle"
adrci> show homes;
ADR Homes:
diag/clients/user_oracle/host_680780456_107
diag/rdbms/orcldemo/orcldemo
diag/rdbms/orcl/orcl
diag/tnslsnr/orcl/listener


adrci> set home diag/rdbms/orcldemo/orcldemo


adrci> show alert -tail 20
2018-10-24 03:36:06.135000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 05:33:24.412000 +05:30
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 12:41:59.003000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 15:31:50.376000 +05:30
Resize operation completed for file# 3, old size 1085440K, new size 1095680K
2018-10-24 16:59:56.481000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 17:59:55.271000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
adrci> show alert -p "message_text like '%incident%'"

ADR Home = /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo:
****************************************************************
Output the results to file: /tmp/alert_47719_1400_orcldemo_3.ado
Eg:-
  show alert
  show alert -p "message_text like '%incident%'"
  show alert -tail 20

Viewing Trace files :

To directly viewed the trace file using the SHOW_TRACE command.

adrci> show trace /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
Output the results to file: /tmp/utsout_47719_14007_5.ado
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
----------------------------------------------------------
LEVEL PAYLOAD 
----- ------------------------------------------------------------------------------------------------------------------------------------------------
Trace file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /oradb/app/oracle/product/12.2.0.1/db_1
System name: Linux
Node name: orcl.localdomain.com
Release: 2.6.32-696.el6.x86_64
Version: #1 SMP Tue Mar 21 19:29:05 UTC 2017
Machine: x86_64
Instance name: orcldemo
Redo thread mounted by this instance: 0 <none>
Oracle process number: 5
Unix process pid: 4748, image: oracle@orcl.localdomain.com (VKTM)


*** 2018-10-15T23:33:55.572017+05:30
*** SESSION ID:(5.6196) 2018-10-15T23:33:55.572050+05:30
*** CLIENT ID:() 2018-10-15T23:33:55.572056+05:30
*** SERVICE NAME:() 2018-10-15T23:33:55.572061+05:30
*** MODULE NAME:() 2018-10-15T23:33:55.572068+05:30
*** ACTION NAME:() 2018-10-15T23:33:55.572073+05:30
*** CLIENT DRIVER:() 2018-10-15T23:33:55.572077+05:30

kstmmainvktm: succeeded in setting elevated priority
highres_disabled
VKTM running at (100ms) precision
kstmrmtickcntkeeper: param _dbrm_quantum will not be effective
[Start] HighResTick = 743299503
kstmrmtickcnt = 0, ksudbrmseccnt[0] = 1539626635
kstmchkdrift (kstmrmtickcntkeeper:highres): Time jumped forward by (1796157)usec at (15368631714) whereas (1000000) is allowed

Purging tracefiles :

Database will creates lots of tracefiles that need to be purged from time to time. In fact, this is done automatically, but you may want to change the default purge policy:

The ordinary tracefiles will stay for 30 days (720 hours), while files like incident files stay one year (8760 hours) by default. We can change that policy,

Purging tracefiles manually :

adrci> purge -age 1440   (for older than 1 days)

Problem :

  • A problem is a critical error in the database. Critical errors include internal errors such as ORA-00600 and other severe errors such as ORA-07445 (operating system exception) or ORA-04031 (out of memory in the shared pool).
  • Problems are tracked in the ADR. Each problem has a problem key and a unique problem ID.

To view the problem occurred in database from ADRCI command line,type SHOW PROBLEM

 

INCIDENT :

  • An incident is a single occurrence of a problem. When a problem occurs multiple times, an incident is created for each occurrence. Incidents are tracked in the ADR. Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database makes an entry in the alert log, sends an incident alert to Oracle Enterprise Manager, gathers diagnostic data about the incident in the form of dump files (incident dumps), tags the incident dumps with the incident ID, and stores the incident dumps in an ADR subdirectory created for that incident.
  • Diagnosis and resolution of a critical error usually starts with an incident alert. You can obtain a list of all incidents in the ADR with an ADRCI command. Each incident is mapped to a single problem only.
  • Incidents are flood-controlled so that a single problem does not generate too many incidents and incident dumps.

 

adrci> show incident -mode detail -p "incident_id=12209";

ADR Home = /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo:
**********************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 12209
STATUS ready
CREATE_TIME 2018-09-19 05:07:14.674000 +05:30
PROBLEM_ID 1
CLOSE_TIME <NULL>
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 600
ERROR_ARG1 ktcrcm: caller passed invalid xcb
ERROR_ARG2 4
ERROR_ARG3 0x06CF235C0
ERROR_ARG4 0x000000000
ERROR_ARG5 1
ERROR_ARG6 4232
ERROR_ARG7 <NULL>
ERROR_ARG8 <NULL>
ERROR_ARG9 <NULL>
ERROR_ARG10 <NULL>
ERROR_ARG11 <NULL>
ERROR_ARG12 <NULL>
SIGNALLING_COMPONENT <NULL>
SIGNALLING_SUBCOMPONENT <NULL>
SUSPECT_COMPONENT <NULL>
SUSPECT_SUBCOMPONENT <NULL>
ECID <NULL>
IMPACTS 0
CON_UID 0
PROBLEM_KEY ORA 600 [ktcrcm: caller passed invalid xcb]
FIRST_INCIDENT 12209
FIRSTINC_TIME 2018-09-19 05:07:14.674000 +05:30
LAST_INCIDENT 14407
LASTINC_TIME 2018-09-19 05:56:05.333000 +05:30
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME Service
KEY_VALUE orcldemo.localdomain.com
KEY_NAME Module
KEY_VALUE oracle@test.localdomain.com (TNS V1-V3)
KEY_NAME ProcId
KEY_VALUE 26.141
KEY_NAME Client ProcId
KEY_VALUE oracle@orcl.localdomain.com (TNS V1-V3).13694_139900975931168
KEY_NAME PQ
KEY_VALUE (0, 1537313832)
KEY_NAME SID
KEY_VALUE 65.20850
OWNER_ID 1
INCIDENT_FILE /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_ora_13694.trc
OWNER_ID 1
INCIDENT_FILE /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i 12209.trc
1 row fetched

Show incident tracefile

adrci> show tracefile -t /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
DIA-48415: Syntax error found in string [show tracefile -t /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc] at column [116]

adrci> show trace /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
Output the results to file: /tmp/utsout_49795_13979_4.ado
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
----------------------------------------------------------
LEVEL PAYLOAD 
----- ------------------------------------------------------------------------------------------------------------------------------------------------
Dump file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /oradb/app/oracle/product/12.2.0.1/db_1
System name: Linux
Node name: orcl.localdomain.com
Release: 2.6.32-696.el6.x86_64
Version: #1 SMP Tue Mar 21 19:29:05 UTC 2017
Machine: x86_64
Instance name: orcldemo
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 13694, image: oracle@orcl.localdomain.com


*** 2018-09-19T05:07:14.933565+05:30
*** SESSION ID:(65.20850) 2018-09-19T05:07:14.933582+05:30
*** CLIENT ID:() 2018-09-19T05:07:14.933587+05:30
*** SERVICE NAME:(orcldemo.localdomain.com) 2018-09-19T05:07:14.933593+05:30
*** MODULE NAME:(oracle@test.localdomain.com (TNS V1-V3)) 2018-09-19T05:07:14.933599+05:30
*** ACTION NAME:() 2018-09-19T05:07:14.933604+05:30
*** CLIENT DRIVER:() 2018-09-19T05:07:14.933608+05:30

[TOC00000]
Jump to table of contents
Dump continued from file: /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_ora_13694.trc
[TOC00001]
1> ***** Error Stack *****
ORA-00600: internal error code, arguments: [ktcrcm: caller passed invalid xcb], [4], [0x06CF235C0], [0x000000000], [1], [4232], [], [], [], [],
[], []

Incident Package :

  • An incident package (package) is a collection of data about incidents for one or more problems. Before sending incident data to Oracle Support it must be collected into a package using the Incident Packaging Service (IPS). After a package is created, you can add external files to the package, remove selected files from the package, or scrub (edit) selected files in the package to remove sensitive data.
  • A package is a logical construct only, until you create a physical file from the package contents. That is, an incident package starts out as a collection of metadata in the Automatic Diagnostic Repository (ADR). As you add and remove package contents, only the metadata is modified. When you are ready to upload the data to Oracle Support, you create a physical package using ADRCI, which saves the data into a zip file.

Creation of Packages & ZIP files to send to Oracle Support :

Get Problem ID from incident output.

adrci> show incident -mode detail -p "incident_id=12209";

ADR Home = /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 12209
STATUS ready
CREATE_TIME 2018-09-19 05:07:14.674000 +05:30
PROBLEM_ID 1

Gather all the required information with a method called “Incident Packaging Service” (IPS).

adrci> ips create package problem 1 correlate all
Created package 1 based on problem id 1, correlation level all

Create a ZIP file using logical package

adrci> ips generate package 1 in "/home/oracle"
Generated package 1 in file /home/oracle/ORA600ktc_20181024223141_COM_1.zip, mode complete

The package is now zipped and ready to upload to Oracle Support.

[oracle@orcl:~ orcldemo] ls -lrt ORA600*

-rw-r--r-- 1 oracle oinstall 5452175 Oct 24 22:33 /home/oracle/ORA600ktc_20181024223141_COM_1.zip

 

Examples :-

  • At the ADRCI prompt, enter the following command:

IPS CREATE PACKAGE INCIDENT incident_number

For example, the following command creates a package based on incident 3:

IPS CREATE PACKAGE INCIDENT 3

ADRCI generates output similar to the following:

Created package 10 based on incident id 3, correlation level typical The package number assigned to this logical package is 10.

The following are variations on the IPS CREATE PACKAGE command:

  IPS CREATE PACKAGE

This creates an empty package. You must use the IPS ADD INCIDENT or IPS ADD FILE commands to add diagnostic data to the package before generating it. 

  IPS CREATE PACKAGE PROBLEM problem_ID

This creates a package and includes diagnostic information for incidents that reference the specified problem ID. 

  IPS CREATE PACKAGE SECONDS sec

This creates a package and includes diagnostic information for all incidents that occurred from sec seconds ago until now. sec must be an integer. 

  IPS CREATE PACKAGE TIME ‘start_time’ TO ‘end_time’

  This creates a package and includes diagnostic information for all incidents that occurred within the specified time range. start_time and end_time must be in the format ‘YYYY-MM-DD HH24:MI:SS.FF TZR’. This is a valid format string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.

Finalizing :

  • Before ADRCI can generate a physical package from a logical package, the package must be finalized. This means that other components are called to add any correlated diagnostic data files to the incidents already in this package.
  • Finalizing also adds recent trace files, alert log entries, Health Monitor reports, SQL test cases, and configuration information. This step is run automatically when a physical package is generated, and can also be run manually using the ADRCI utility. After manually finalizing a package, you can review the files that were added and then remove or edit any that contain sensitive information.

ADR Home :

  • An ADR home is the root directory for all diagnostic data—traces, dumps, alert log, and so on—for a particular instance of a particular Oracle product or component.
  • For example, in a Real Application Clusters environment with ASM, each database instance and each ASM instance has an ADR home. All ADR homes share the same hierarchical directory structure. Some of the standard subdirectories in each ADR home include alert (for the alert log), trace (for trace files), and incident (for incident information). All ADR homes are located within the ADR base directory.
  • Some ADRCI commands can work with multiple ADR homes simultaneously. The current ADRCI homepath determines the ADR homes that are searched for diagnostic data when an ADRCI command is issued.

ADR Base :

  • To permit correlation of diagnostic data across multiple ADR homes, ADR homes are grouped together under the same root directory called the ADR base.
  • For example, in an Oracle Real Application Clusters (RAC) environment, the ADR base could be on a shared disk, and the ADR home for each Oracle RAC instance could be located under this ADR base.
  • The location of the ADR base for a database instance is set by the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted or is null, the database sets it to a default value.
  • When multiple database instances share an Oracle home, whether they are multiple single instances or the instances of an Oracle Real Application Clusters database, and when one or more of these instances set ADR base in different locations, the last instance to start up determines the default ADR base for ADRCI.

Homepath :

  • All ADRCI commands operate on diagnostic data in the “current” ADR homes. More than one ADR home can be current at any one time. Some ADRCI commands (such as SHOW INCIDENT) search for and display diagnostic data from all current ADR homes, while other commands require that only one ADR home be current, and display an error message if more than one is current.
  • The ADRCI homepath determines the ADR homes that are current. It does so by pointing to a directory within the ADR base hierarchy. If it points to a single ADR home directory, that ADR home is the only current ADR home. If the homepath points to a directory that is above the ADR home directory level in the hierarchy, all ADR homes that are below the directory that is pointed to become current.
  • The homepath is null by default when ADRCI starts. This means that all ADR homes under ADR base are current.
  • The SHOW HOME and SHOW HOMEPATH commands display a list of the ADR homes that are current, and the SET HOMEPATH command sets the homepath.
Catch Me On:- Hariprasath Rajaram

LinkedIn:      https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:      https://www.facebook.com/HariPrasathdba
Facebook Group:https://www.facebook.com/groups/894402327369506/
Facebook Page: https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba

 

Analyze Oracle Server Diagnostic Information Using OSWatcher Tool

OSWatcher:

Oracle OSWatcher Black Box (OSWbb) collects and archives operating system and network metrics that you can use to diagnose performance issues.

OSWbb operates as a set of background processes on the server and gathers data on a regular basis, invoking such Unix utilities as vmstat, netstat, iostat, and top.

OSWatcher includes a File Manager process that will run once per hour to clean up any data files older than the retention period.

The collection interval and retention period can be changed with the first two parameters, respectively, to the shell script that starts OSWatcher.

OSWbb is particularly useful for Oracle RAC (Real Application Clusters) and Oracle Grid Infrastructure configurations.

The RAC-DDT (Diagnostic Data Tool) script file includes OSWbb, but does not install it by default.

Installing OSWbb

To install OSWbb:

[root@ram db_1]# tar -xvf oswbb812.tar 
oswbb/
oswbb/sarsub.sh
oswbb/psmemsub.sh
oswbb/stopOSWbb.sh
oswbb/analysis/
oswbb/docs/
oswbb/docs/OSWatcher/
oswbb/docs/OSWatcher/oswbb_README.txt
oswbb/docs/OSWatcher/RAC_Traceroute_Automation_Readme.txt
oswbb/docs/OSWatcher/OSWatcherUserGuide.html
oswbb/docs/The_Analyzer/
oswbb/docs/The_Analyzer/AnalyzerUserGuide.html
oswbb/docs/The_Analyzer/AnalyzerWalkThrough.pdf
oswbb/docs/The_Analyzer/oswbba_README.txt
oswbb/docs/OSWatcherOverview090517.pdf
oswbb/Exampleprivate.net
oswbb/OSWatcher.sh~
oswbb/tmp/
oswbb/oswbba.jar
oswbb/oswib.sh
oswbb/ltop.sh
oswbb/oswnet.sh
oswbb/call_uptime.sh
oswbb/OSWatcher.sh
oswbb/vmsub.sh
oswbb/src/
oswbb/src/js/
oswbb/src/js/bootstrap.js
oswbb/src/js/modernizr.js
oswbb/src/js/jquery-2.1.4.min.js
oswbb/src/js/THIRDPARTYLICENSE.txt
oswbb/src/js/jquery.lightbox.js
oswbb/src/js/tabs.js
oswbb/src/js/scripts.js
oswbb/src/js/templatemo_custom.js
oswbb/src/Thumbs.db
oswbb/src/fonts/
oswbb/src/fonts/FontAwesome.otf
oswbb/src/fonts/fontawesome-webfont.woff
oswbb/src/fonts/fontawesome-webfont.woff2
oswbb/src/fonts/fontawesome-webfont.eot
oswbb/src/fonts/fontawesome-webfont.ttf
oswbb/src/fonts/fontawesome-webfont.svg
oswbb/src/META-INF/
oswbb/src/META-INF/application-client.xml
oswbb/src/images/
oswbb/src/images/Thumbnail_Placeholder.png
oswbb/src/images/next.png
oswbb/src/images/bx_loader.gif
oswbb/src/images/close.png
oswbb/src/images/loading.gif
oswbb/src/images/previous.png
oswbb/src/css/
oswbb/src/css/templatemo_misc.css
oswbb/src/css/bootstrap.min.css
oswbb/src/css/style.css
oswbb/src/css/font-awesome.min.css
oswbb/src/css/templatemo_style.css
oswbb/src/css/animate.css
oswbb/src/oswbba_input.txt
oswbb/topaix.sh
oswbb/iosub.sh
oswbb/oswsub.sh
oswbb/nfssub.sh
oswbb/xtop.sh
oswbb/genprvnet.sh
oswbb/oswrds.sh
oswbb/call_sar.sh
oswbb/call_du.sh
oswbb/ifconfigsub.sh
oswbb/mpsub.sh
oswbb/Example_extras.txt
oswbb/locks/
oswbb/OSWatcherFM.sh
oswbb/tar_up_partial_archive.sh
oswbb/data/
oswbb/tar_up_full_archive.sh
oswbb/gif/
oswbb/startOSWbb.sh
[root@ram db_1]#
  •  If the ksh package is not already installed on your system, use yum to install it.
[root@ram db_1]# yum install ksh
Loaded plugins: refresh-packagekit, security
Setting up Install Process
Trying other mirror.
Package ksh-20120801-37.el6_9.x86_64 already installed and latest version
Nothing to do

[root@ram db_1]#[root@bash oswbb]# ls -lrth
total 544K
-rwxr-xr-x. 1 2052 8500  524 Oct  7  2013 oswsub.sh
-rwxr-xr-x. 1 2052 8500  825 Oct  7  2013 oswrds.sh
-rwxr-xr-x. 1 2052 8500  414 Oct  7  2013 oswib.sh
-rwxr-xr-x. 1 2052 8500  740 Oct  7  2013 nfssub.sh
-rwxr-xr-x. 1 2052 8500  542 Oct  7  2013 mpsub.sh
-rwxr-xr-x. 1 2052 8500  743 Oct  7  2013 iosub.sh
-rwxr-xr-x. 1 2052 8500 1.9K Oct  7  2013 Exampleprivate.net
-rwxr-xr-x. 1 2052 8500   68 Oct  7  2013 call_sar.sh
-rwxr-xr-x. 1 2052 8500   71 Jan  7  2014 call_uptime.sh
-rwxr-xr-x. 1 2052 8500  435 Jan  8  2014 oswnet.sh
-rwxr-xr-x. 1 2052 8500 1.5K Jan  8  2014 ltop.sh
-rwxr-xr-x. 1 2052 8500   67 Jan 15  2014 call_du.sh
-rwxr-xr-x. 1 2052 8500  772 May  8  2014 ifconfigsub.sh
-rwxr-xr-x. 1 2052 8500  545 Feb 23  2015 vmsub.sh
-rwxr-xr-x. 1 2052 8500 2.6K Feb 26  2015 startOSWbb.sh
-rwxr-xr-x. 1 2052 8500 1.5K Feb 26  2015 xtop.sh
-rwxr-xr-x. 1 2052 8500 5.3K Feb  7  2017 tar_up_partial_archive.sh
-rwxr-xr-x. 1 2052 8500  834 Feb  7  2017 tar_up_full_archive.sh
-rwxr-xr-x. 1 2052 8500  665 Feb  7  2017 Example_extras.txt
-rwxr-xr-x. 1 2052 8500  527 Feb  8  2017 topaix.sh
-rwxr-xr-x. 1 2052 8500 3.9K Apr 18  2017 genprvnet.sh
drwx------. 2 2052 8500 4.0K May  3  2017 data
-rwxr-xr-x. 1 2052 8500  557 Jul 12  2017 sarsub.sh
-rwxr-xr-x. 1 2052 8500 6.7K Jul 12  2017 OSWatcherFM.sh
-rwxr-xr-x. 1 2052 8500  751 Aug 15  2017 stopOSWbb.sh
-rw-r--r--. 1 2052 8500  47K Aug 15  2017 OSWatcher.sh~
-rwxr-xr-x. 1 2052 8500 1.5K Aug 25  2017 psmemsub.sh
drwx------. 4 2052 8500 4.0K Sep  5  2017 docs
drwx------. 2 2052 8500 4.0K Sep  6  2017 gif
drwx------. 7 2052 8500 4.0K Sep  6  2017 src
-rwxr-xr-x. 1 2052 8500  47K Dec 13  2017 OSWatcher.sh
-rw-r--r--. 1 2052 8500 310K Dec 13  2017 oswbba.jar
drwxr-xr-x. 2 2052 8500 4.0K Dec 13  2017 tmp
drwxr-xr-x. 2 2052 8500 4.0K Dec 13  2017 locks
drwxr-xr-x. 2 2052 8500 4.0K Dec 13  2017 analysis

To start OSWbb, run the startOSWbb.sh script from the oswbb directory.

[root@bash oswbb]# ./startOSWbb.sh 
[root@bash oswbb]# 
Info...You did not enter a value for snapshotInterval.
Info...Using default value = 30
Info...You did not enter a value for archiveInterval.
Info...Using default value = 48
Setting the archive log directory to/u01/app/irfan/product/12.2.0/dbhome_1/oswbb/archive

Testing for discovery of OS Utilities...
VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
IFCONFIG found on your system.
NETSTAT found on your system.
TOP found on your system.
TRACEROUTE found on your system.

Discovery of CPU CORE COUNT
CPU CORE COUNT will be used by oswbba to automatically look for cpu problems

CPU CORE COUNT = 2
VCPUS/THREADS = 2

Discovery completed.

Starting OSWatcher v8.1.2  on Sat Oct 27 01:41:22 IST 2018
With SnapshotInterval = 30
With ArchiveInterval = 48

OSWatcher - Written by Carl Davis, Center of Expertise,
Oracle Corporation
For questions on install/usage please go to MOS (Note:301137.1)
If you need further assistance or have comments or enhancement
requests you can email me Carl.Davis@Oracle.com

Data is stored in directory: /u01/app/irfan/product/12.2.0/dbhome_1/oswbb/archive

Starting Data Collection...

oswbb heartbeat:Sat Oct 27 01:41:27 IST 2018
oswbb heartbeat:Sat Oct 27 01:41:57 IST 2018
oswbb heartbeat:Sat Oct 27 01:42:27 IST 2018
oswbb heartbeat:Sat Oct 27 01:42:57 IST 2018
^C
[root@bash oswbb]# ./stopOSWbb.sh 
[root@bash oswbb]#

To stop OSWbb permanently, run the stopOSWbb.sh script from the oswbb directory.

To View The Collected Data

Collected data will be stored under archive directory, it is created when OSWbb is started for the first time. OSWbb stores data in hourly archive files named system_name_utility_name_timestamp.dat, and each entry in a file is preceded by the characters *** and a timestamp.

To check any data, just navigate to corresponding directory and view the corresponding file. For testing purpose, we printed vmstat file output.

OSWbb collects data in the following directories under the oswbb/archive directory:

Directory Description
oswiostat Contains output from the iostat utility.
oswmeminfo Contains a listing of the contents of /proc/meminfo.
oswmpstat Contains output from the mpstat utility.
oswnetstat Contains output from the netstat utility.
oswprvtnet If you have enable private network tracing for RAC, contains information about the status of the private networks.
oswps Contains output from the ps utility.
oswslabinfo Contains a listing of the contents of /proc/slabinfo.
oswtop Contains output from the top utility.
oswvmstat Contains output from the vmstat utility.

 

 

[root@bash tfa]# ls -lrth
total 266M
-rwxrwxr-x. 1 irfan irfan 266M Oct 27 00:43 TFA-LINUX_v18.3.3.zip
[root@bash tfa]# unzip TFA-LINUX_v18.3.3.zip 
Archive: TFA-LINUX_v18.3.3.zip
inflating: README.txt 
inflating: installTFA-LINUX 
[root@bash tfa]# ls -lrth
total 532M
-rwxr-xr-x. 1 root root 267M Oct 5 23:39 installTFA-LINUX
-rw-r--r--. 1 root root 1.5K Oct 5 23:41 README.txt
-rwxrwxr-x. 1 irfan irfan 266M Oct 27 00:43 TFA-LINUX_v18.3.3.zip
[root@bash tfa]# ./installTFA-LINUX 
TFA Installation Log will be written to File : /tmp/tfa_install_6496_2018_10_27-00_49_26.log

Starting TFA installation

TFA Version: 183300 Build Date: 201810050542

Enter a location for installing TFA (/tfa will be appended if not supplied) [/u01/app/irfan/product/12.2.0/dbhome_1/tfa]:
/u01/app/irfan/product/12.2.0/dbhome_1/tfa/

Running Auto Setup for TFA as user root...

Would you like to do a [L]ocal only or [C]lusterwide installation ? [L|l|C|c] [C] : L
Installing TFA now...

Discovering Nodes and Oracle resources

Starting Discovery...


No Grid Infrastructure Discovered on this system . . . . .

bash
Searching for running databases...
1. IRFAN


Searching out ORACLE_HOME for selected databases...


Getting Oracle Inventory...

ORACLE INVENTORY: /u01/app/oraInventory


Discovery Complete...

TFA Will be Installed on bash...

TFA will scan the following Directories
++++++++++++++++++++++++++++++++++++++++++++

.-----------------------------------------------------------------.
| bash |
+------------------------------------------------------+----------+
| Trace Directory | Resource |
+------------------------------------------------------+----------+
| /u01/app/irfan/cfgtoollogs | CFGTOOLS |
| /u01/app/irfan/diag/rdbms/clone/CLONE/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/clone/CLONE/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/clone/clone/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/clone/clone/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/crcc_pitr_irfan/Crcc/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/crcc_pitr_irfan/Crcc/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/iobh_pitr_irfan/ioBh/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/iobh_pitr_irfan/ioBh/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/irfan/IRFAN/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/irfan/IRFAN/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/ivee_pitr_irfan/ivEE/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/ivee_pitr_irfan/ivEE/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/macaw/IRFAN/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/macaw/IRFAN/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/nrsd_pitr_irfan/nrsd/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/nrsd_pitr_irfan/nrsd/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/orcl/orcl/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/orcl/orcl/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/osiz_pitr_irfan/osiz/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/osiz_pitr_irfan/osiz/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/wfha_pitr_irfan/wfhA/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/wfha_pitr_irfan/wfhA/trace | RDBMS |
| /u01/app/irfan/diag/tnslsnr | TNS |
| /u01/app/irfan/product/12.2.0/dbhome_1/cfgtoollogs | CFGTOOLS |
| /u01/app/irfan/product/12.2.0/dbhome_1/install | INSTALL |
| /u01/app/irfan/product/12.2.0/dbhome_1/rdbms/log | RDBMS |
| /u01/app/irfan/product/12.2.0/dbhome_2/cfgtoollogs | CFGTOOLS |
| /u01/app/irfan/product/12.2.0/dbhome_2/install | INSTALL |
| /u01/app/oraInventory/ContentsXML | INSTALL |
| /u01/app/oraInventory/logs | INSTALL |
'------------------------------------------------------+----------'


Installing TFA on bash:
HOST: bash TFA_HOME: /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bash/tfa_home

.-------------------------------------------------------------------------.
| Host | Status of TFA | PID | Port | Version | Build ID |
+------+---------------+------+-------+------------+----------------------+
| bash | RUNNING | 7102 | 55356 | 18.3.3.0.0 | 18330020181005054218 |
'------+---------------+------+-------+------------+----------------------'

Running Inventory in All Nodes...

Enabling Access for Non-root Users on bash...
ERROR: /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bash/tfa_home/internal/cached_kv.out does not exists

Adding default users to TFA Access list...

Summary of TFA Installation:
.--------------------------------------------------------------------------------.
| bash |
+---------------------+----------------------------------------------------------+
| Parameter | Value |
+---------------------+----------------------------------------------------------+
| Install location | /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bash/tfa_home |
| Repository location | /u01/app/irfan/product/12.2.0/dbhome_1/tfa/repository |
| Repository usage | 0 MB out of 4876 MB |
'---------------------+----------------------------------------------------------'

TFA is successfully installed...

Usage : /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bin/tfactl <command> [options]
commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa|syncnodes|setupmos|upload|availability|rest|events|search|changes|isa
For detailed help on each command use:
/u01/app/irfan/product/12.2.0/dbhome_1/tfa/bin/tfactl <command> -help





 

 

 

[irfan@bash bin]$ tfactl print version
TFA Version : 18.3.3.0.0



[irfan@bash bin]$ tfactl toolstatus

.------------------------------------------------------------------.
| TOOLS STATUS - HOST : bash |
+----------------------+--------------+--------------+-------------+
| Tool Type | Tool | Version | Status |
+----------------------+--------------+--------------+-------------+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+----------------------+--------------+--------------+-------------+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.1.2 | RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+----------------------+--------------+--------------+-------------+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
'----------------------+--------------+--------------+-------------'

Note :-
DEPLOYED : Installed and Available - To be configured or run interactively.
NOT RUNNING : Configured and Available - Currently turned off interactively.
RUNNING : Configured and Available.

 

Invoking OSWBB

[irfan@bash bin]$ tfactl oswbb

Starting OSW Analyzer V8.1.2
OSWatcher Analyzer Written by Oracle Center of Expertise
Copyright (c) 2017 by Oracle Corporation

Parsing Data. Please Wait...

Scanning file headers for version and platform info...

Parsing file bash.localhost.com_iostat_18.10.27.0000.dat ...
Parsing file bash.localhost.com_iostat_18.10.27.0100.dat ...
This directory already exists. Rewriting...

Parsing file bash.localhost.com_vmstat_18.10.27.0000.dat ...
Parsing file bash.localhost.com_vmstat_18.10.27.0100.dat ...


Parsing file bash.localhost.com_netstat_18.10.27.0000.dat ...
Parsing file bash.localhost.com_netstat_18.10.27.0100.dat ...

Parsing file bash.localhost.com_top_18.10.27.0000.dat ...
Parsing file bash.localhost.com_top_18.10.27.0100.dat ...

Parsing file bash.localhost.com_ps_18.10.27.0000.dat ...
Parsing file bash.localhost.com_ps_18.10.27.0100.dat ...


Parsing Completed.


Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter GC to Generate All CPU Gif Files
Enter GM to Generate All Memory Gif Files
Enter GD to Generate All Disk Gif Files
Enter GN to Generate All Network Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter Z to Zoom Graph Time Scale (Does not change analysis dataset)
Enter B to Returns to Baseline Graph Time Scale (Does not change analysis dataset)
Enter R to Remove Currently Displayed Graphs

Enter X to Export Parsed Data to Flat File
Enter S to Analyze Subset of Data(Changes analysis dataset including graph time scale)
Enter A to Analyze Data
Enter D to Generate DashBoard

Enter Q to Quit Program

Please Select an Option:D
Enter a unique analysis/dashBoard directory name or enter <CR> to accept default name:<CR>

A new analysis file analysis/<CR>/analysis.txt has been created.

Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Run_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Run_Adjusted_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Block_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_HB.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_PS_Processes.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Idle.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_System.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_User.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Wa.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Interrupts.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Context_Switches.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Swap.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Free.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Page_In_Rate.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Page_Out_Rate.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Wa.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Block_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_ST.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_AW.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_PB.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_RPS.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_WPS.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_TPS.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_requests_sent_out.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_total_packets_received.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_bad_header_checksum.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_dropped.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_created.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_received.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_dropped_after.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_warn1.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_warn2.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_datagrams_in.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_datagrams_out.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_dropped.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_broadcast_dropped.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_socket_overflows.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_bad_header_checksums.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_in_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_out_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_retrans_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_conn_resets_received_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_resets_sent.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_failed_conn_attempts.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_retran_error_rate.jpg

Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter GC to Generate All CPU Gif Files
Enter GM to Generate All Memory Gif Files
Enter GD to Generate All Disk Gif Files
Enter GN to Generate All Network Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter Z to Zoom Graph Time Scale (Does not change analysis dataset)
Enter B to Returns to Baseline Graph Time Scale (Does not change analysis dataset)
Enter R to Remove Currently Displayed Graphs

Enter X to Export Parsed Data to Flat File
Enter S to Analyze Subset of Data(Changes analysis dataset including graph time scale)
Enter A to Analyze Data
Enter D to Generate DashBoard

Enter Q to Quit Program

Please Select an Option:

Creating the dashboard

If you choose option “D” oswbb will go ahead and create a dashboard.

the result is stored in

$ORACLE_BASE/tfa/repository/suptools/$(host)/oswbb/oracle/oswbb/analysis

[root@bash oswbb]# ls -lrth
total 560K
-rwxr-xr-x. 1 irfan irfan 47K Oct 27 00:52 OSWatcher.sh
-rwxr-xr-x. 1 irfan irfan 545 Oct 27 00:52 vmsub.sh
drwxr-xr-x. 7 irfan irfan 4.0K Oct 27 00:52 src
-rwxr-xr-x. 1 irfan irfan 740 Oct 27 00:52 nfssub.sh
-rwxr-xr-x. 1 irfan irfan 1.5K Oct 27 00:52 ltop.sh
-rwxr-xr-x. 1 irfan irfan 71 Oct 27 00:52 call_uptime.sh
-rwxr-xr-x. 1 irfan irfan 1.5K Oct 27 00:52 xtop.sh
-rwxr-xr-x. 1 irfan irfan 529 Oct 27 00:52 topother.sh
-rwxr-xr-x. 1 irfan irfan 2.6K Oct 27 00:52 startOSWbb.sh
-rwxr-xr-x. 1 irfan irfan 557 Oct 27 00:52 sarsub.sh
-rwxr-xr-x. 1 irfan irfan 524 Oct 27 00:52 oswsub.sh
-rwxr-xr-x. 1 irfan irfan 825 Oct 27 00:52 oswrds.sh
-rwxr-xr-x. 1 irfan irfan 435 Oct 27 00:52 oswnet.sh
-rwxr-xr-x. 1 irfan irfan 414 Oct 27 00:52 oswib.sh
-rwxr-xr-x. 1 irfan irfan 20K Oct 27 00:52 oswbb.pm
-rwxr-xr-x. 1 irfan irfan 542 Oct 27 00:52 mpsub.sh
-rwxr-xr-x. 1 irfan irfan 772 Oct 27 00:52 ifconfigsub.sh
-rwxr-xr-x. 1 irfan irfan 3.9K Oct 27 00:52 genprvnet.sh
-rwxr-xr-x. 1 irfan irfan 1.9K Oct 27 00:52 Exampleprivate.net
-rwxr-xr-x. 1 irfan irfan 665 Oct 27 00:52 Example_extras.txt
-rwxr-xr-x. 1 irfan irfan 67 Oct 27 00:52 call_du.sh
-rwxr-xr-x. 1 irfan irfan 5.3K Oct 27 00:52 tar_up_partial_archive.sh
-rwxr-xr-x. 1 irfan irfan 751 Oct 27 00:52 stopOSWbb.sh
-rwxr-xr-x. 1 irfan irfan 1.5K Oct 27 00:52 psmemsub.sh
-rwxr-xr-x. 1 irfan irfan 310K Oct 27 00:52 oswbba.jar
-rwxr-xr-x. 1 irfan irfan 47K Oct 27 00:52 OSWatcher.sh~
-rwxr-xr-x. 1 irfan irfan 743 Oct 27 00:52 iosub.sh
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 00:52 data
-rwxr-xr-x. 1 irfan irfan 834 Oct 27 00:52 tar_up_full_archive.sh
-rwxr-xr-x. 1 irfan irfan 6.7K Oct 27 00:52 OSWatcherFM.sh
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 00:52 gif
-rwxr-xr-x. 1 irfan irfan 68 Oct 27 00:52 call_sar.sh
drwxr-xr-x. 3 irfan irfan 4.0K Oct 27 01:53 analysis
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 02:02 tmp
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 02:02 locks
[root@bash oswbb]# pwd
/u01/app/irfan/product/12.2.0/dbhome_1/tfa/repository/suptools/bash/oswbb/irfan/oswbb
[root@bash oswbb]# cd analysis/
[root@bash analysis]# ls -lrth
total 4.0K
drwxrwxr-x. 3 irfan irfan 4.0K Oct 27 01:53 <CR>

[root@bash analysis]# cd \<CR\>

[root@bash <CR>]# ls -lrth
total 316K
-rw-rw-r--. 1 irfan irfan 312K Oct 27 01:53 analysis.txt
drwxrwxr-x. 7 irfan irfan 4.0K Oct 27 01:53 dashboard
[root@bash <CR>]# cd dashboard/
[root@bash dashboard]# ls -lrth
total 76K
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 js
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 css
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 images
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 fonts
drwxrwxr-x. 2 irfan irfan 4.0K Oct 27 01:53 generated_files
-rw-rw-r--. 1 irfan irfan 54K Oct 27 01:53 index.html


[root@bash dashboard]# cp index.html /home/irfan/Desktop/

The HTML output

 

 

 

 

 

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FBGroup:https://www.facebook.com/groups/894402327369506/ FBPage: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

 

SQL Health Check (SQLHC) Tips And Tricks

SQL Health Check (SQLHC)

Description 

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise.

The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

SQL Health Check (SQLHC)

Overview: SQLHC checks elements that may affect the performance of the SQL being analyzed, such as Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters. SQLHC is a subset of SQLT, which we will discuss next.  This script is a quick way to diagnose problems without having SQLT installed.

Documentation and Install:SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

Proactive: Use this script to examine SQL that is not running up to standards while testing an upgrade.

Reactive: Use this script to get recommendations on problem SQL after an upgrade.

Caveats: SQLHC does not require any special licensing, it is free and it is designed to run on all systems. If the Diagnostics and Tuning packs are installed, that data will be used by the script. If you are not licensed for Diagnostics and Tuning, answer “NO” to that question in the script to avoid licensing problems.

Parameters Required:
  1. Login to the database server and set the environment used by the Database Instance.
  2.  Download the “sqlhc.zip”  archive file and extract the contents to a suitable directory/folder.
  3. Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the “sqlhc.sql” script. It will request to enter two parameters:
    1. Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
      If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)
    2. A valid SQL_ID for the SQL to be analyzed.

 

Health-checks are performed over:
  • CBO Statistics for schema objects accessed by the one SQL statement being analyzed
  • CBO Parameters
  • CBO System Statistics
  • CBO Data Dictionary Statistics
  • CBO Fixed-objects Statistics
[oracle@ram oracle]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 24 22:06:19 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> select name,open_mode from v$database;

NAME        OPEN_MODE
--------- --------------------
TEST        READ WRITE

SQL> create user d1 identified by d1 default tablespace tbsnew;

User created.

SQL> grant dba to d1;

Grant succeeded.

SQL> conn d1/d1
Connected.

SQL> create table t1 (id number,name varchar2(100),salary number);

Table created.


SQL> begin
for i in 1..100000 loop
insert into t1 values(i,'testing',20000);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> create table t2 (id number,name varchar2(100),salary number);

Table created.

SQL> begin
for i in 1..1000 loop
insert into emp values(i,'testing',2000);
commit;
end loop;
end;
/


SQL> begin
for i in 1..1000 loop
insert into t2 values(i,'testing',2000);
commit;
end loop;
end;
/ 

PL/SQL procedure successfully completed.

SQL>

I am doing Few updations in t1 table

SQL>update t1 set name='vicky' where id between 1 and 1000;
2000 rows updated.

SQL>update t1 set name='venkat' where id between 1001 and 2000;
2000 rows updated.

SQL>update t1 set name='gayathri' where id between 2001 and 3000;
2000 rows updated.

SQL>update t1 set name='haja' where id between 3001 and 4000;
2000 rows updated.

SQL>update t1 set name='srinath' where id between 4001 and 5000;
2000 rows updated.

SQL>update t1 set name='senthil' where id between 5001 and 6000;
2000 rows updated.

SQL>update t1 set name='ram' where id between 6001 and 7000;
2000 rows updated.

SQL>update t1 set name='karthi' where id between 7001 and 8000;
2000 rows updated.

SQL>update t1 set name='venkat' where id between 10001 and 20000;
20000 rows updated.

SQL>update t1 set name='gayathri' where id between 20001 and 30000;
20000 rows updated.

SQL>update t1 set name='haja' where id between 30001 and 40000;
19999 rows updated.

SQL>update t1 set name='srinath' where id between 40001 and 50000;
10000 rows updated.

SQL>update t1 set name='senthil' where id between 50001 and 60000;
10000 rows updated.

SQL>update t1 set name='ram' where id between 60001 and 70000;
10000 rows updated.

SQL>update t1 set name='karthi' where id between 70001 and 80000;
10000 rows updated.

SQL>delete from t1 where id between 1 and 1000;
2000 rows deleted.

SQL>delete from t1 where id between 1001 and 2000;
2000 rows deleted.

SQL>delete from t1 where id between 2001 and 3000;
2000 rows deleted.

SQL>delete from t1 where id between 3001 and 4000;
2000 rows deleted.

SQL>delete from t1 where id between 4001 and 5000;
2000 rows deleted.

SQL>delete from t1 where id between 5001 and 6000;
2000 rows deleted.

SQL>delete from t1 where id between 6001 and 7000;
2000 rows deleted.

SQL>delete from t1 where id between 7001 and 8000;
2000 rows deleted.

SQL>delete from t1 where id between 10001 and 20000;
20000 rows deleted.

SQL>delete from t1 where id between 40001 and 50000;
10000 rows deleted.

 

For particular SQL Id : b62q7nc33gzwx  we need going to check health

 

 

I attached the output files here.

SQL HEALTH CHECK:

Full output screen you can see here

SQL DIAGNOSTICS:

Full output screen you can see here

SQL EXECUTION PLAN:

Full output screen you can see here

SQL DETAIL:

sqlhc_20181024_232227_b62q7nc33gzwx_4_sql_detail

Log Files:

sqlhc_20181024_232227_b62q7nc33gzwx_8_sqldx sqlhc_20181024_232227_b62q7nc33gzwx_9_log

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Health Checks For The Oracle Stack Using ORAchk

ORAchk:

ORAchk replaces the RACCheck utility. ORAchk extends health check coverage to the entire Oracle software stack, and identifies and addresses top issues reported by Oracle users.

ORAchk proactively scans for known problems with Oracle products and deployments, including the following:

  • Standalone Oracle Database
  • Oracle Grid Infrastructure
  • Oracle Real Application Clusters
  • Maximum Availability Architecture (MAA) Validation
  • Upgrade Readiness Validations
  • Oracle Golden Gate

Oracle is continuing to expand checks, based on customer requests.

Installation :

[root@ram orachk]# cp -r /home/oracle/Desktop/orachk.zip /u01/app/oracle/product/12.2.0.1/db_1/suptools/orachk
[root@ram orachk]# ls -lrt
total 418548
-rw-r--r--. 1 oracle oinstall 3879 Jun 10 2016 generate_guests_list.sh
-rw-r--r--. 1 oracle oinstall 11487 Jun 10 2016 cgrep
-rwxr-x---. 1 oracle oinstall 182 Jun 10 2016 UserGuide.txt
-rwxr-x---. 1 oracle oinstall 2201 Jun 10 2016 readme.txt
-rw-r--r--. 1 oracle oinstall 2888 Aug 10 2016 user_defined_checks.xsd
-rwxr-x---. 1 oracle oinstall 5906493 Aug 10 2016 rules.dat
-rwxr-x---. 1 oracle oinstall 2856776 Aug 10 2016 orachk
-rwxr-x---. 1 oracle oinstall 42423762 Aug 10 2016 collections.dat
-rw-r--r--. 1 oracle oinstall 2170483 Aug 10 2016 ORAchk_Health_Check_Catalog.html
-rw-r--r--. 1 oracle oinstall 4801825 Aug 10 2016 CollectionManager_App.sql
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 templates
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 exadiscover
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 doc
-rw-r--r--. 1 oracle oinstall 32159 Oct 22 20:22 orachk_debug_20181022_202100.log
-rw-r--r--. 1 oracle oinstall 11540087 Oct 22 21:14 orachk_debug_20181022_203840.log
-rwxr--r--. 1 root root 358799048 Oct 23 02:22 orachk.zip
[root@ram orachk]# unzip orachk.zip 
Archive: orachk.zip
replace collections.dat? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: collections.dat 
replace rules.dat? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
inflating: rules.dat 
replace .cgrep/versions.dat? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: .cgrep/versions.dat 
inflating: .cgrep/profiles.dat 
inflating: .cgrep/profiles/EF6C016813C51366E04313C0E50AE11F.prf 
inflating: .cgrep/profiles/DA94919CD0DE0913E04312C0E50A7996.prf 
inflating: .cgrep/profiles/D49C0FBF8FBF4B1AE0431EC0E50A0F24.prf 
inflating: .cgrep/profiles/06889D8BB65E575CE05313C0E50ADFD3.prf 
inflating: .cgrep/profiles/D8367AD6754763FEE04312C0E50A6FCB.prf 
inflating: .cgrep/profiles/DF65D6117CB41054E04312C0E50A69D1.prf 
inflating: .cgrep/profiles/579C1EAF1380DEC3E053D298EB0AB847.prf 
inflating: .cgrep/profiles/06702DE980726771E05313C0E50ACF83.prf 
inflating: .cgrep/profiles/42AEB5616C5A7648E0530C98EB0A4B18.prf 
inflating: .cgrep/profiles/68A6BCC041C53A8FE053D398EB0AA546.prf 
inflating: .cgrep/profiles/1B0907A7BA8EA932E0530C98EB0A0947.prf 
inflating: .cgrep/profiles/20E3DAB976AD7377E0530A98EB0A9BCA.prf 
inflating: .cgrep/profiles/398C70DD35E55DE9E0530E98EB0A880F.prf 
inflating: .cgrep/profiles/E1BF012E8F210839E04313C0E50A7B68.prf 
inflating: .cgrep/profiles/D462A6F7E9C340FDE0431EC0E50ABE12.prf 
inflating: .cgrep/profiles/3E121C16D3714EE0E0530E98EB0A6C66.prf 
extracting: .cgrep/profiles/638ED6170D1C053CE053D498EB0A65F6.prf 
inflating: .cgrep/profiles/EA5EE324E7E05128E04313C0E50A4B2A.prf 
inflating: .cgrep/profiles/3194E615F4BBEDD0E0530A98EB0A046E.prf 
inflating: .cgrep/profiles/069273EAA9873FD1E05312C0E50A8953.prf 
inflating: .cgrep/profiles/1C6E4AC8EF3674D0E0530D98EB0ACEC1.prf 
inflating: .cgrep/profiles/E8DF76E07DD82E0DE04313C0E50AA55D.prf 
inflating: .cgrep/profiles/206B850D83B1CE54E0530C98EB0A5C89.prf 
inflating: .cgrep/profiles/53C6697D0F4E825DE0530D98EB0A38AF.prf 
inflating: .cgrep/profiles/D49AD88F8EE75CD8E0431EC0E50A0BC3.prf 
inflating: .cgrep/profiles/F32F44CE0BCD662FE04312C0E50AB058.prf 
inflating: .cgrep/profiles/E2E972DDE1E14493E04312C0E50A1AB1.prf 
inflating: .cgrep/profiles/12B66730A5161437E05312C0E50AABAB.prf 
inflating: .cgrep/profiles/D49B218473787400E0431EC0E50A0BB9.prf 
inflating: .cgrep/profiles/0A82EA8BF9646097E05313C0E50A26D6.prf 
inflating: .cgrep/profiles/1B0907A7BA8DA932E0530C98EB0A0947.prf 
inflating: .cgrep/profiles/F6AFECA37F177C3FE04313C0E50A56BF.prf 
inflating: .cgrep/profiles/F9ED0179CCD8256BE04312C0E50A5399.prf 
inflating: .cgrep/profiles/45E86D52410AC60AE0530E98EB0AA8EB.prf 
inflating: .cgrep/profiles/4F59DD9703B8547CE0530C98EB0A9927.prf 
inflating: .cgrep/profiles/177BBFEE0215240AE0530E98EB0AEBF7.prf 
inflating: .cgrep/profiles/09DC8AC7C7974BDDE05313C0E50A2339.prf 
inflating: .cgrep/profiles/4329C86BB3C356BFE0530B98EB0A4B22.prf 
extracting: .cgrep/profiles/21A3C08B67727E6AE0530E98EB0AE59C.prf 
inflating: .cgrep/profiles/165CCF84D4FE0342E0530A98EB0AAE6E.prf 
inflating: .cgrep/profiles/D49BDC2EC9E624AEE0431EC0E50A3E12.prf 
inflating: .cgrep/profiles/D49C0AB26A6D45A8E0431EC0E50ADE06.prf 
inflating: .cgrep/profiles/70B251DD093D2F72E053D498EB0AD124.prf 
inflating: .cgrep/profiles/D49C4F9F48735396E0431EC0E50A9A0B.prf 
inflating: .cgrep/profiles/DFE9C207A8F2428CE04313C0E50A6B0A.prf 
inflating: .cgrep/profiles/271BD73C756AE5EDE0530B98EB0A6A13.prf 
inflating: .cgrep/profiles/DF65D0F7FB6F1014E04312C0E50A7808.prf 
inflating: .cgrep/profiles/1B0907A7BA8CA932E0530C98EB0A0947.prf 
inflating: .cgrep/profiles/178E758EB8CA06D8E0530D98EB0A7AC9.prf 
inflating: .cgrep/profiles/270F37922A89B520E0530B98EB0ADDE9.prf 
inflating: orachk 
inflating: orachk.bat 
inflating: orachk.pyc 
inflating: CollectionManager_App.sql 
inflating: Apex5_CollectionManager_App.sql 
inflating: sample_user_defined_checks.xml 
inflating: user_defined_checks.xsd 
inflating: .cgrep/acchk.jar 
inflating: .cgrep/utlu112i.sql 
inflating: .cgrep/upgrade.oracle.jar 
inflating: .cgrep/registry_validation.sql 
inflating: .cgrep/acgrep 
inflating: .cgrep/diff_collections.pl 
inflating: .cgrep/lcgreps10 
inflating: .cgrep/raw_data_browser.pl 
inflating: .cgrep/checkvg.sh 
inflating: .cgrep/Recursive.pm 
inflating: .cgrep/checkDiagCollections.sh 
inflating: .cgrep/scgrep 
inflating: .cgrep/cluster_check_os_collect 
inflating: .cgrep/filechecker.sh 
inflating: .cgrep/parse_user_defined_checks.pl 
inflating: .cgrep/idmhc_get_check_status.pl 
extracting: .cgrep/dbsat.zip 
inflating: .cgrep/scnhealthcheck.sql 
inflating: .cgrep/reset_crshome.pl 
inflating: .cgrep/host_specific_collections.pl 
inflating: .cgrep/lcgrep4 
inflating: .cgrep/checkFlashCache.sh 
inflating: .cgrep/checkLocalDisks.sh 
inflating: .cgrep/zonecores.sh 
inflating: .cgrep/isc_summary.pl 
inflating: .cgrep/lcgreps11 
inflating: .cgrep/lcgrep5 
inflating: .cgrep/checkHiddenParams.sh 
inflating: .cgrep/validatePassword.sh 
inflating: .cgrep/preupgrade122.jar 
inflating: .cgrep/ocm_switch.py 
inflating: .cgrep/checkDiskFGMapping.sh 
inflating: .cgrep/parse_index.pl 
inflating: .cgrep/append_merge_collections.pl 
inflating: .cgrep/psqlplus 
inflating: .cgrep/detect_custom_rpms.sh 
inflating: .cgrep/ggdiscovery.sh 
inflating: .cgrep/EM_NLSID_ID.dat 
inflating: .cgrep/preupgrade19.jar 
inflating: .cgrep/preupgrd.sql 
inflating: .cgrep/get_zfs_checks.pl 
inflating: .cgrep/discover_java_home.sh 
inflating: .cgrep/pxhcdr.sql 
inflating: .cgrep/preupgrade18.jar 
inflating: .cgrep/discoverdbasm.pl 
inflating: .cgrep/check_sysctl.awk 
inflating: .cgrep/mineocr.pm 
inflating: .cgrep/lcgreps9 
inflating: .cgrep/zlcgrep6 
inflating: .cgrep/utlusts.sql 
inflating: .cgrep/readreg.pl 
inflating: .cgrep/rac_main.pl 
inflating: .cgrep/lcgrep3 
inflating: .cgrep/check_reblance_free_space.sql 
inflating: .cgrep/ogghc_11203.sql 
inflating: .cgrep/lcgrep6 
inflating: .cgrep/cgrepwin61 
inflating: .cgrep/ofm_client.sh 
inflating: .cgrep/auto_upgrade.pl 
inflating: .cgrep/create_small_file.pl 
inflating: .cgrep/vmpscan.sh 
inflating: .cgrep/check_dom0_ocfs2.sh 
inflating: .cgrep/checkDiskScheduler.sh 
inflating: .cgrep/rac_lib.pm 
inflating: .cgrep/asrexacheck 
inflating: .cgrep/ra_check_version.pl 
inflating: .cgrep/scgrepx86 
inflating: .cgrep/load_checks_attributes.pl 
inflating: .cgrep/oracle-upstarttmpl.conf 
inflating: .cgrep/rack_comparison.py 
inflating: .cgrep/hiacgrep 
inflating: .cgrep/utluppkg.sql 
inflating: .cgrep/top_consumers.pl 
inflating: .cgrep/rac_file_checker.pl 
inflating: .cgrep/merge_collections.pl 
inflating: .cgrep/show_file_in_html.pl 
inflating: .cgrep/wincgrep.exe 
inflating: .cgrep/lcgrep6s 
inflating: .cgrep/profile_collections.pl 
inflating: .cgrep/create_version.pl 
inflating: .cgrep/zfschecks/SoftringWorkflow 
inflating: .cgrep/zfschecks/Datasets 
inflating: .cgrep/zfschecks/ZSPrivateNetworkInterface 
inflating: .cgrep/zfschecks/BlockSize 
inflating: .cgrep/zfschecks/MirrorProfile 
inflating: .cgrep/zfschecks/ZSPool 
inflating: .cgrep/zfschecks/SnapshotVisibility 
inflating: .cgrep/zfschecks/ZSIPMP 
inflating: .cgrep/zfschecks/NFSDomain 
inflating: .cgrep/zfschecks/Cluster 
inflating: .cgrep/zfschecks/DNSConfiguration 
inflating: .cgrep/zfschecks/ComputeZSServices 
inflating: .cgrep/zfschecks/IlomHealth 
inflating: .cgrep/zfschecks/Maintenance 
inflating: .cgrep/zfschecks/ZSDIMM 
inflating: .cgrep/zfschecks/StorageMemSize 
inflating: .cgrep/zfschecks/ComputeAnalyticsRetentionPolicy 
inflating: .cgrep/zfschecks/ExtShareDedup 
inflating: .cgrep/zfschecks/ExtShadows 
inflating: .cgrep/zfschecks/DiskTimeoutWarning 
inflating: .cgrep/zfschecks/ZSPowerSupply 
inflating: .cgrep/zfschecks/Shadows 
inflating: .cgrep/zfschecks/ExtMirrorProfile 
inflating: .cgrep/zfschecks/HeadStatus 
inflating: .cgrep/zfschecks/ZSServices 
inflating: .cgrep/zfschecks/ExtShareQuota 
inflating: .cgrep/zfschecks/ComputeZSZillas 
inflating: .cgrep/zfschecks/Lock 
inflating: .cgrep/zfschecks/ZSFan 
inflating: .cgrep/zfschecks/NISService 
inflating: .cgrep/zfschecks/ComputeDatasets 
inflating: .cgrep/zfschecks/ZSNTP 
inflating: .cgrep/zfschecks/ZSVersion 
inflating: .cgrep/zfschecks/ZSZillas 
inflating: .cgrep/zfschecks/ZSRouting 
inflating: .cgrep/zfschecks/NFSDelegation 
inflating: .cgrep/zfschecks/Backend 
inflating: .cgrep/zfschecks/ZSILOM 
inflating: .cgrep/zfschecks/L2ARCHeader 
inflating: .cgrep/zfschecks/NFS4LockObjectLeak 
inflating: .cgrep/zfschecks/CommonCode 
inflating: .cgrep/zfschecks/AnalyticsRetentionPolicy 
inflating: .cgrep/zfschecks/ZSSlot 
inflating: .cgrep/zfschecks/ZSCPU 
inflating: .cgrep/zfschecks/ExtBlockSize 
inflating: .cgrep/zfschecks/ShareDedup 
inflating: .cgrep/zfschecks/ShareQuota 
inflating: .cgrep/run_individual_checks.pl 
inflating: .cgrep/diff_checks.pl 
inflating: .cgrep/hugeSGAchecks.sh 
inflating: .cgrep/ogghc_12101.sql 
inflating: .cgrep/ogghc_11204.sql 
inflating: .cgrep/combine_collections.pl 
inflating: .cgrep/OVMMCheckChannels.py 
inflating: .cgrep/cluster_check_os_check 
inflating: .cgrep/cgrepwin63 
creating: lib/
inflating: lib/debugger.pyc 
inflating: lib/__init__.pyc 
inflating: lib/fileattr.pyc 
inflating: lib/utils.pyc 
inflating: lib/mail.pyc 
inflating: lib/command.pyc 
inflating: lib/messages.pyc 
inflating: lib/em_xml_template.pyc 
inflating: lib/execute_checks.pyc 
inflating: lib/wallet.pm 
inflating: lib/logger.pyc 
inflating: lib/constant.pyc 
inflating: lib/help.pyc 
inflating: lib/CM_UpgradeScript.sql 
inflating: lib/autoscheduler.tmpl 
inflating: lib/data_collections.pyc 
inflating: lib/discover_env.pyc 
inflating: lib/cleanup.pyc 
inflating: lib/init.tmpl 
inflating: lib/patch_recommendation.pyc 
inflating: lib/mkstore.pyc 
inflating: lib/autostart 
inflating: lib/CM_Wrapper.sql 
inflating: lib/oracle-upstarttmpl.conf 
inflating: lib/generate_report.pyc 
inflating: lib/winservice.pyc 
inflating: lib/cm_lib.pl 
creating: lib/wallet_jars/
inflating: lib/wallet_jars/ojpse.jar 
inflating: lib/wallet_jars/osdt_core.jar 
inflating: lib/wallet_jars/osdt_cert.jar 
extracting: lib/wallet_jars/cwallet.sso 
inflating: lib/wallet_jars/oraclepki.jar 
inflating: lib/wallet_jars/ojmisc.jar 
inflating: lib/dbconnection.pyc 
inflating: lib/connection.pyc 
inflating: lib/security.pyc 
inflating: lib/watchdog.pyc 
creating: build/
extracting: build/Python3_zlinux.zip 
extracting: build/Python3_aix.zip 
extracting: build/Python3_solaris_sparc.zip 
extracting: build/Python3_solaris.zip 
extracting: build/Python3_linux.zip 
inflating: exadiscover/README 
inflating: exadiscover/list_master_node.sql 
inflating: exadiscover/list_all_ips.sql 
inflating: exadiscover/list_all_ips_vars.sql 
inflating: exadiscover/list_assets.sql 
inflating: exadiscover/CHANGELOG 
inflating: exadiscover/exadiscover.py 
inflating: exadiscover/list_ovmm.sql 
inflating: exadiscover/exadiscover.sh 
inflating: templates/exachk_exalogic.conf.tmpl_quarter 
inflating: templates/exachk_exalogic.conf.tmpl_half 
inflating: templates/exachk_exalogic.conf.tmpl_full 
inflating: templates/exachk_exalogic.conf.tmpl_eighth 
creating: bash/
inflating: bash/psqlplus 
inflating: bash/discoverdbasm.pl 
inflating: bash/orachk 
creating: web/
inflating: web/orachk.jar 
inflating: web/setup_ords.sh 
inflating: web/ords.war 
inflating: .cgrep/orachk.pyc 
extracting: build/.DIAGKIT.zip 
inflating: ORAchk_Health_Check_Catalog.html 
inflating: UserGuide.txt 
inflating: readme.txt 
inflating: doc/ORAchk_and_EXAchk_User_Guide.pdf 
[root@ram orachk]#
[oracle@ram ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/suptools/
[oracle@ram suptools]$ ls -lrt
total 12
drwxr-xr-x. 3 oracle oinstall 4096 Jun 19 10:33 tfa
drwxr-xr-x. 6 oracle oinstall 4096 Jun 19 10:35 orachk
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:39 oratop
[oracle@ram suptools]$ cd orachk/
[oracle@ram orachk]$ ls -lrt
total 56848
-rw-r--r--. 1 oracle oinstall 3879 Jun 10 2016 generate_guests_list.sh
-rw-r--r--. 1 oracle oinstall 11487 Jun 10 2016 cgrep
-rwxr-x---. 1 oracle oinstall 182 Jun 10 2016 UserGuide.txt
-rwxr-x---. 1 oracle oinstall 2201 Jun 10 2016 readme.txt
-rw-r--r--. 1 oracle oinstall 2888 Aug 10 2016 user_defined_checks.xsd
-rwxr-x---. 1 oracle oinstall 5906493 Aug 10 2016 rules.dat
-rwxr-x---. 1 oracle oinstall 2856776 Aug 10 2016 orachk
-rwxr-x---. 1 oracle oinstall 42423762 Aug 10 2016 collections.dat
-rw-r--r--. 1 oracle oinstall 2170483 Aug 10 2016 ORAchk_Health_Check_Catalog.html
-rw-r--r--. 1 oracle oinstall 4801825 Aug 10 2016 CollectionManager_App.sql
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 templates
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 exadiscover
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 doc
[oracle@ram orachk]$ cat readme.txt 
For full documentation on ORAchk including details on what's new in 12.1.0.2.7 see the ORAchk_and_EXAchk_User_Guide.pdf in the doc directory or the ORAchk and EXAchk User's Guide at http://docs.oracle.com/cd/E75572_01/index.html

Issues fixed in ORAchk 12.1.0.2.7
---------------------------------
23041776 lnx64-12.2-orachk: failed to use psqlplus
23193819 request orachk to check udev best practice on network configuration hotplug="n"
23147689 orachk 12.1.0.2.6 resource discovery fails for 10.2 databases
23144636 support anonymous pl/sql block in sql checks
23140046 check if compatible.asm and compatible.dbms should be advanced
23074512 problems parsing the values for autorun_schedule
23074163 daemon does not send diff report as email attachment
23071612 orachk to check that service_names parameter is not set in the spfile
23041743 lnx64-12.2-orachk:output not under oracle_base when run orachk not in its home
23014620 can not run orachk from stage directory due to permission issues
23012747 ac: application continuity - filter acchk output
23007768 orachk execution appends discovery name on path
22861038 orachk db discovery fails on linux 7
22746951 password visible in -setupload and -getupload
22721891 testemail does not validate address
22642844 orachk to warn if large amount of hugepage is free
22642302 provide centralized wallet for orachk cm uploads
21947833 orachk 12.1.0.2.4 - database prompt mismatch on standalone server
21773769 lnx64-12.2-oracheck: implement the file checker component for oracheck module
19161674 lnx64-12.1-orachk:orachk hit syntax error
18956483 lnx64-121-cmt: oakcli orachk hit warning sys.audses$ sequence cache size < 10000
18944821 solsp-12.1-utl-orachk:orachk give a wrong information about stack status
18944432 solsp-12.1-utl-orachk:tabular information isn't well aligned in orachk output
18789186 solsp-12.1-utl-orachk: remove/update log ownership check for orachk
18748527 solsp-12.1-utl-orachk:no need to ask non-gi user to set rat_output for -h option

Issues fixed in previous ORAchk releases
----------------------------------------
For a full list of ORAchk version history see ORAchk_EXAchk_Feature_Fix_History.pdf in the doc directory.

To check the version:

using the -v option.

[oracle@ram orachk]$ ./orachk -v

ORACHK VERSION: 12.1.0.2.7_20160526

To capture debug output:

  1. Reproduce the problem with fewest runs before enabling debug.

    Debug captures a lot and the resulting zip file can be large so try to narrow down the amount of run necessary to reproduce the problem.

    Use command-line options to limit the scope of checks.

  2. Enable debug.
    If you are running the tool in on-demand mode, then use the -debug option:
[oracle@ram orachk]$ ./orachk –debug

Output : click here

Permission Problems:

You must have sufficient directory permissions to run Oracle ORAchk

  1. Verify that the permissions on the tools scripts orachk is set to                        755 (-rwrr-xr-x).
    If the permissions are not set, then set the permissions as follows:

Automatic start from TFA install

Oracle Trace File Analyzer root installations on Linux or Solaris on          non-engineered systems will automatically setup and run the ORAchk daemon.

The daemon will be restarted at 1am every morning, in order to discover any environment changes. A full local ORAchk run will be performed at 2am every morning and a partial run of the most impactful checks will be run every 2 hours via the oratier1 profile.

Any collections older than 2 weeks will automatically be purged.

If ORAchk is installed in stand-alone mode Auto start can also be used for quick setup by running orachk -autostart, again only when run as root on Linux or Solaris on a non-engineered system.

Once auto start is enabled the daemon settings can be changed as per normal and auto start can be removed any time by using orachk -autostop or tfactl run orachk -autostop

Pre check:

[root@ram orachk]# ./orachk -u -o pre
This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...

Running orachk
----------------------------------------------------------
PATH : /u01/app/oracle/product/12.2.0.1/db_1/suptools/orachk
VERSION : 12.1.0.2.7_20160526 
COLLECTIONS DATA LOCATION : /u01/app/oracle/orachk 
----------------------------------------------------------


This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...
Enter upgrade target version (valid versions are 11.2.0.3.0, 11.2.0.4.0, 12.1.0.1.0 and 12.1.0.2.0):-

Invalid upgrade target version format or upgrade version not yet supported.

Enter upgrade target version (valid versions are 11.2.0.3.0, 11.2.0.4.0, 12.1.0.1.0 and 12.1.0.2.0) 2 tries left:- 12.1.0.2.0
This computer is for [S]ingle instance database or part of a [C]luster to run RAC database [S|C] [C]:S

RDBMS binaries found at /u01/app/oracle/product/12.2.0.1/db_1/ and ORACLE_HOME not set. Do you want to set ORACLE_HOME to /u01/app/oracle/product/12.2.0.1/db_1/?[y/n][y]y

Checking for prompts for root user on all nodes...


Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . 
-------------------------------------------------------------------------------------------------------
Oracle Stack Status 
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
ram No No Yes No No No 
-------------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------------------------------------
Installed components summary 
---------------------------------------------------------------------------------------------------------------------------------
GI_HOME ORACLE_HOME Database Names 
---------------------------------------------------------------------------------------------------------------------------------

Checking for prompts for oracle user on all nodes...

Source vesrsion (122010) is higher than Target version (121020).

orachk is exiting...

To avoid problems while running the tool from terminal sessions on a network attached workstation or laptop, consider running the tool using VNC.

If there is a network interruption, then the tool continues to process to completion.

If the tool fails to run, then re-run the tool. The tool does not resume from the point of failure.

Running Health Checks On-Demand:

To start on-demand health check runs, log in to the system as an appropriate user, and then run an appropriate tool.

Specify the options to direct the type of run that you want.

[root@ram orachk]# ./orachk 
This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...

Running orachk
----------------------------------------------------------
PATH : /u01/app/oracle/product/12.2.0.1/db_1/suptools/orachk
VERSION : 12.1.0.2.7_20160526 
COLLECTIONS DATA LOCATION : /u01/app/oracle/orachk 
----------------------------------------------------------


This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...

Checking for prompts for oracle user on all nodes...


List of running databases
1. clone
2. test
3. All of above
4. None of above


Searching out ORACLE_HOME for selected databases.

. . . .


Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . . . . 
Checking for prompts for root user on all nodes...

. . . . . . . . . . . . 
-------------------------------------------------------------------------------------------------------
Oracle Stack Status 
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
ram No No Yes No No Yes clone test 
-------------------------------------------------------------------------------------------------------


Copying plug-ins

. . . . . . . . .


*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***


Collections and audit checks log file is 
/u01/app/oracle/orachk/orachk_ram_test_102318_001148/log/orachk.log

Checking for prompts in /root/.bash_profile on ram for root user...


. . 
=============================================================
Node name - ram 
=============================================================
. . . . .

Collecting - Database Parameters for test database
Collecting - Database Undocumented Parameters for test database
Collecting - RDBMS Feature Usage for test database
Collecting - CPU Information
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - OS Packages
Collecting - Operating system release information and kernel version
Collecting - Patches for RDBMS Home 
Collecting - Table of file system defaults
Collecting - number of semaphore operations per semop system call
Collecting - Disk Information 
Collecting - Linux Operating system health check using vmpscan.sh 
Collecting - Root user limits 
Collecting - Verify no database server kernel out of memory errors

Data collections completed. Checking best practices on ram.
--------------------------------------------------------------------


FAIL => Bash is vulnerable to code injection (CVE-2014-6271)
WARNING => Linux Swap Configuration does NOT meet Recommendation
WARNING => physical memory is not sufficient
INFO => Important Storage Minimum Requirements for Grid & Database Homes
INFO => Most recent ADR incidents for /u01/app/oracle/product/12.2.0.1/db_1
INFO => Oracle GoldenGate failure prevention best practices
INFO => user_dump_dest has trace files older than 30 days for test
FAIL => Operating system hugepages count does not satisfy total SGA requirements
WARNING => OSWatcher is not running as is recommended.
WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for test
INFO => Operational Best Practices
INFO => Database Consolidation Best Practices
INFO => Computer failure prevention best practices
INFO => Data corruption prevention best practices
INFO => Logical corruption prevention best practices
INFO => Database/Cluster/Site failure prevention best practices
INFO => Client failover operational best practices
WARNING => Duplicate objects were found in the SYS and SYSTEM schemas for test
WARNING => Oracle clusterware is not being used
WARNING => RAC Application Cluster is not being used for database high availability on test instance
WARNING => DISK_ASYNCH_IO is NOT set to recommended value for test
FAIL => Flashback on PRIMARY is not configured for test
INFO => Database failure prevention best practices
WARNING => fast_start_mttr_target has NOT been changed from default on test instance
FAIL => Primary database is NOT protected with Data Guard (standby database) for real-time data protection and availability for test
FAIL => Active Data Guard is not configured for test
INFO => Oracle recovery manager(rman) best practices
INFO => Consider increasing the COREDUMPSIZE size
WARNING => Consider investigating changes to the schema objects such as DDLs or new object creation for test
WARNING => Consider investigating the frequency of SGA resize operations and take corrective action for test


Best Practice checking completed.Checking recommended patches on ram.
---------------------------------------------------------------------------------


Collecting patch inventory on ORACLE_HOME /u01/app/oracle/product/12.2.0.1/db_1 
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
0 Recommended RDBMS patches for 122010 from /u01/app/oracle/product/12.2.0.1/db_1 on ram
---------------------------------------------------------------------------------
Patch# RDBMS ASM type Patch-Description 
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
RDBMS homes patches summary report
---------------------------------------------------------------------------------
Total patches Applied on RDBMS Applied on ASM ORACLE_HOME 
---------------------------------------------------------------------------------
0 0 0 /u01/app/oracle/product/12.2.0.1/db_1
---------------------------------------------------------------------------------

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



Detailed report (html) - /u01/app/oracle/orachk/orachk_ram_test_102318_001148/orachk_ram_test_102318_001148.html
UPLOAD(if required) - /u01/app/oracle/orachk/orachk_ram_test_102318_001148.zip


[root@ram orachk]# 


Database Server Details

After i run the ORAchk utility i got few output from my server which i added below.

For detailed report click here

Top Consumers :

ORAchk Assessment Report:

For detailed report  click here

Post Upgrade:

After upgrading, run the post-upgrade checks:

[root@ram orachk]# ./orachk -u -o post

Running On-Demand With or Without the Daemon

When running on-demand, if the daemon is running, then the daemon answers all prompts where possible including the passwords.

To run health checks on-demand if the daemon is running, then use:

[root@ram orachk]# ./orachk 

To avoid connecting to the daemon process, meaning the tool to interactively prompt you as required, use the -nodaemon option:

[root@ram orachk]#  ./orachk -nodaemon

Daemon mode is supported only on the Linux and Solaris operating systems.

Sending Results by Email

Optionally email the HTML report to one or more recipients using the            -sendemail option.

[oracle@ram orachk]$ ./orachk -sendemail "NOTIFICATION_EMAIL=support@gmail.com"
Few Commands:
  1. To prevent prompting for which database to run against and check all databases, use the –dball option.
    [oracle@ram orachk]$ ./orachk -dball
  2. To prevent prompting and skip all database checks, use the –dbnone option.
    [oracle@ram orachk]$ ./orachk –dbnone
  3. To run checks against a subset of databases, use the –dbnames database_name option.

    You can check multiple database instances by listing them in a comma-delimited list.

    [oracle@ram orachk]$ ./orachk –dbnames db1,db2,db3

    By default, Oracle ORAchk run checks on all database nodes in the cluster.

  4. To run checks against a subset of PDBs, use the -pdbnames pdb_name option.

    You can check multiple PDBs by listing them in a comma-delimited list.

    [oracle@ram orachk]$ ./orachk –pdbnames pdb1,pdb2,pdb3

    By default, Oracle ORAchk run checks on all PDBs in the cluster.

  5. To run checks against a subset of cluster nodes, use the                                          -clusternodes node option.

    You can check multiple cluster nodes by listing them in a comma-delimited list.

    [oracle@ram orachk]$ ./orachk –clusternodes node1,node2,node3
  6. To run checks against the local node, use the -localonly option.
    [oracle@ram orachk]$ ./orachk -localonly
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FBGroup:https://www.facebook.com/groups/894402327369506/ FBPage: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

Linux iostat Command Usage For Oracle DBA

iostat  linux command:-

It displays information about CPU usage, and I/O statistics for every partition and network filesystems(NFS).It is default size is in KB.

[oracle@orcl:~ ] iostat
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
         1.09  0.00  0.54    1.75    0.00   96.62

Device: tps  Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda     7.66 61.77      155.95     11792568 29771464

Options :

-c Display the CPU utilization report.
-d Display the device utilization report.
-h Make the NFS report displayed by option -n easier to read by a human.
-k Display statistics in kilobytes per second instead of blocks per second. Data displayed are valid only with kernels 2.4 and later.
-m Display statistics in megabytes per second instead of blocks or kilobytes per second. Data displayed are valid only with kernels 2.4 and later.
-N Display the registered device mapper names for any device mapper devices. Useful for viewing LVM2 statistics.
-n Display the network filesystem (NFS) report. This option works only with kernel 2.6.17 and later.
-p [ { device [,...] | ALL } ]
The -p option displays statistics for block devices and all their partitions that are used by the system. If a device name is entered on the command line, then statistics for it and all its partitions are displayed. Last, the ALL keyword indicates that statistics have to be displayed for all the block devices and partitions defined by the system, including those that have never been used. Note that this option works only with post 2.5 kernels.
-t Print the time for each report displayed. The timestamp format may depend on the value of the S_TIME_FORMAT environment variable (see below).
-V Print version number then exit.
-x Display extended statistics. This option works with post 2.5 kernels since it needs /proc/diskstats file or a mounted sysfs to get the statistics. This option may also work with older kernels (e.g. 2.4) only if extended statistics are available in /proc/partitions (the kernel needs to be patched for that).
-z Tell iostat to omit output for any devices for which there was no activity during the sample period.

Examples :

  1. iostat output  in MB
[oracle@orcl:~ ] iostat -m
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.09 0.00 0.54 1.75 0.00 96.62

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 7.64 0.03 0.08 5760 14629

2. iostat with -c arguments displays only CPU statistics

[oracle@orcl:~ ] iostat -c
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.09 0.00 0.54 1.75 0.00 96.62

3. iostat with -c arguments displays only CPU statistics for every 2 seconds.

[oracle@orcl:~ ] iostat -c 2
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.08 0.00 0.54 1.75 0.00 96.63

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.51 0.00 0.00 99.49

4. iostat with -d arguments displays only disks I/O statistics of all partitions

[oracle@orcl:~ ] iostat -d
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

Device: tps  Blk_read/s Blk_wrtn/s Blk_read  Blk_wrtn
sda     7.66 61.71      155.87     11793016  29788536
dm-0    3.26 8.32       23.06      446295370 1237237064

5. It displays the I/O statistics for specific device sda

[oracle@orcl:~ ] iostat -p sda
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.08 0.00 0.54 1.75 0.00 96.62

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 7.65 61.62 155.75 11793576 29809400
sda1 0.00 0.14 0.00 26011 64
sda2 0.92 15.43 14.98 2952435 2866192
sda3 0.01 0.37 1.73 71657 331632
sda4 0.00 0.00 0.00 13 0
sda5 6.72 45.68 139.04 8742243 26611512

6. iostat version

[oracle@orcl:~ ] iostat -V
sysstat version 9.0.4
(C) Sebastien Godard (sysstat <at> orange.fr)

7. It displays a usage of statement

[oracle@orcl:~ ] iostat -?
Usage: iostat [ options ] [ <interval> [ <count> ] ]
Options are:
[ -c ] [ -d ] [ -N ] [ -n ] [ -h ] [ -k | -m ] [ -t ] [ -V ] [ -x ] [ -y ] [ -z ]
[ -j { ID | LABEL | PATH | UUID | ... } [ <device> [...] | ALL ] ]
[ <device> [...] | ALL ] [ -p [ <device> [,...] | ALL ] ]

 

The iostat command generates three types of reports, the CPU Utilization report, the Device Utilization report and the Network Filesystem report.

CPU utilization in iostat output :

avg-cpu: %user %nice %system %iowait %steal %idle
         27.71 0.00  1.16    2.82    0.00   68.31

Columns in iostat CPU utlization output :

%user
Show the percentage of CPU utilization that occurred while executing at the user level (application).

%nice
Show the percentage of CPU utilization that occurred while executing at the user level with nice priority.

%system
Show the percentage of CPU utilization that occurred while executing at the system level (kernel).

%iowait
Show the percentage of time that the CPU or CPUs were idle during which the system had an outstanding disk I/O request.

%steal
Show the percentage of time spent in involuntary wait by the virtual CPU or CPUs while the hypervisor was servicing another virtual processor.

%idle
Show the percentage of time that the CPU or CPUs were idle and the system did not have an outstanding disk I/O request.

Device Utilization in iostat output :

Device: tps  Blk_read/s   Blk_wrtn/s Blk_read Blk_wrtn
sda     7.59 60.05        154.04     11807456 30287688

To get more detailed statistics, we can use ‘-x’ option along with iostat command

[oracle@fhpasadbdr01 ~]$ iostat -x
Linux 2.6.32-279.5.2.el6.x86_64 (fhpasadbdr01.pasa.pas.local) 10/22/2018 _x86_64_ (16 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
27.71 0.00 1.16 2.82 0.00 68.31

Device: rrqm/s wrqm/s r/s  w/s  rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda     12.04  11.67  5.59 2.24 146.36 111.29 32.93    0.02     2.68 1.99  1.56
dm-0    0.00   0.00   0.37 2.88 8.32   23.06  9.64     0.04     12.51 1.49  0.49

Columns in iostat device utlization output :

The standard iostat device display shows the following statistics:

           KB/t    kilobytes per transfer
           tps     transfers per second
           MB/s    megabytes per second

           The standard iostat device display, with the -I flag specified,
           shows the following statistics:

           KB/t    kilobytes per transfer
           xfrs    total number of transfers
           MB      total number of megabytes transferred

           The old-style iostat display (using -o) shows the following statistics:

           sps     sectors transferred per second
           tps     transfers per second
           msps    average milliseconds per transaction

           The old-style iostat display, with the -I flag specified, shows the following statistics:

           blk     total blocks/sectors transferred
           xfr     total transfers
           msps    average milliseconds per transaction

Network file utilization in iostat output :

We can use nfsiostat command to generate the NFS i/o statistics reports. nfsiostat command is the part of the package ‘nfs-utils’. Let’s assume we have mounted two NFS shares on our server, so to generate the statistics report for NFS share run the below command,

[root@prod ~]$ nfsiostat

orcl.localdomain.com:/ifs/backup mounted on /backup:

op/s rpc bklog
9.50 0.00
read: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
2.492 261.008 104.749 0 (0.0%) 12.042 12.185
write: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
0.011 0.132 11.821 0 (0.0%) 4.042 8.698

orcl.localdomain.com:/ifs/orashare1 mounted on /orashare1:

op/s rpc bklog
3.16 0.00
read: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
0.333 3.366 10.105 0 (0.0%) 4.081 4.264
write: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
0.001 0.445 351.851 0 (0.0%) 23.907 9531.542

Click the link for more information about  iostat

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba