Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

SUMMARY, my favorite Trace File Analyzer (TFA) command …

Posted by FatDBA on October 16, 2022

Hi Guys,

Trace File Analyzer (TFA) is one of the popular Oracle provided toolkit that is very useful when you are dealing with any ORA error and want to pack incident files or when you want to monitor your database using oratop (now part of tfa), or want to run orachk (part of tfa) for a health check of your entire Oracle software stack for issue detection. It also comes with support tools like OSWatcher, process watcher and darda which we all are very much familiar. Today’s post is about one of my favorite TFA utilities option/flag ‘Summary’ which is not that commonly used, but is one of the cool things that we you can do with the TFA.

For this post I have used TFA version 21.2.0.0.0, I suggest to use latest version as it comes with bug fixes and lot of new features.

Feature : ‘summary
This flag/function of TFA provides you a detailed (high level) real time status summary of your full DB system. It collects stats of your CRS, ASM, ACFS, Database, Patching Info, Network, Listeners and Operating System. Once you execute ‘summary’ mode within TFA command line (TFACTL), it will take you to tfactl_summary mode or SUMMARY Command-Line Interface (CLI) from where you can select from multiple options to view component wise statistics. The best part is you can even save the report in the form of HTML page (using option q) which is more fancy and easy to read version, otherwise text based is the default version.

Alright lets get started, I am going to call the ‘summary’ option and will see what is there for us.

tfactl>
tfactl>
tfactl> summary

  Executing Summary in Parallel on Following Nodes:
    Node : nodeabc
    Node : nodexyz

LOGFILE LOCATION : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221015052849/log/summary_command_20221015052849_nodeabc_32378.log

  Component Specific Summary collection :
    - Collecting CRS details ... Done.
    - Collecting ASM details ... Done.
    - 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.

  Remote Summary Data Collection : In-Progress - Please wait ...
  - Data Collection From Node - nodexyz .. Done.

  Prepare Clusterwide Summary Overview ... Done
      cluster_status_summary

  COMPONENT   STATUS    DETAILS
+-----------+---------+---------------------------------------------------------------------------------------------------+
  CRS         PROBLEM   .-----------------------------------------------.
                        | CRS_SERVER_STATUS   : ONLINE                  |
                        | CRS_STATE           : ONLINE                  |
                        | CRS_INTEGRITY_CHECK : FAIL                    |
                        | CRS_RESOURCE_STATUS : OFFLINE Resources Found |
                        '-----------------------------------------------'
  ASM         PROBLEM   .-------------------------------------------------------.
                        | ASM_DISK_SIZE_STATUS : WARNING - Available Size < 20% |
                        | ASM_BLOCK_STATUS     : PASS                           |
                        | ASM_CHAIN_STATUS     : PASS                           |
                        | ASM_INCIDENTS        : PASS                           |
                        | ASM_PROBLEMS         : PASS                           |
                        '-------------------------------------------------------'
  ACFS        OFFLINE   .-----------------------.
                        | ACFS_STATUS : OFFLINE |
                        '-----------------------'
  DATABASE    PROBLEM   .-----------------------------------------------------------------------------------------------.
                        | ORACLE_HOME_DETAILS                                                        | ORACLE_HOME_NAME |
                        +----------------------------------------------------------------------------+------------------+
                        | .------------------------------------------------------------------------. | OraDB19000_home1 |
                        | | PROBLEMS | INCIDENTS | DB_BLOCKS | DATABASE_NAME | STATUS  | DB_CHAINS | |                  |
                        | +----------+-----------+-----------+---------------+---------+-----------+ |                  |
                        | | PROBLEM  | PROBLEM   | PASS      | FATDBA1       | PROBLEM | PROBLEM   | |                  |
                        | '----------+-----------+-----------+---------------+---------+-----------' |                  |
                        '----------------------------------------------------------------------------+------------------'
  PATCH       OK        .----------------------------------------------.
                        | CRS_PATCH_CONSISTENCY_ACROSS_NODES      : OK |
                        | DATABASE_PATCH_CONSISTENCY_ACROSS_NODES : OK |
                        '----------------------------------------------'
  LISTENER    OK        .-----------------------.
                        | LISTNER_STATUS   : OK |
                        '-----------------------'
  NETWORK     OK        .---------------------------.
                        | CLUSTER_NETWORK_STATUS :  |
                        '---------------------------'
  OS          OK        .-----------------------.
                        | MEM_USAGE_STATUS : OK |
                        '-----------------------'
  TFA         OK        .----------------------.
                        | TFA_STATUS : RUNNING |
                        '----------------------'
  SUMMARY     OK        .------------------------------------.
                        | SUMMARY_EXECUTION_TIME : 0H:1M:41S |
                        '------------------------------------'
+-----------+---------+---------------------------------------------------------------------------------------------------+


        ### Entering in to SUMMARY Command-Line Interface ###

tfactl_summary>list

  Components : Select Component - select [component_number|component_name]
        1 => overview
        2 => crs_overview
        3 => asm_overview
        4 => acfs_overview
        5 => database_overview
        6 => patch_overview
        7 => listener_overview
        8 => network_overview
        9 => os_overview
        10 => tfa_overview
        11 => summary_overview

tfactl_summary>
tfactl_summary>
tfactl_summary>

Now when all statistics summaries are collected, this will automatically take us to the SUMMARY Command-Line Interface (tfactl_summary>) where if we use ‘list’ option will bring the entire chart of options to choose from (see above all 11 different areas). Next I am going to select option 3 to get ASM_OVERVIEW from tfactl_summary CLI which when selected will further take you to another linked CLI that is tfactl_summary_asmoverview> which will further gives you details of your entire cluster, like in my case I have node machines as a part of this cluster.

So basically the flow will be something like this
tfactl —> Use summary command and enter into tfactl_summary —> Choose option from the list using ‘list’ command and enter into option related CLI (i.e. tfactl_summary_asmoverview>) and choose option or node to view stats.

Please take a look below for better understanding.

tfactl_summary>3

  ASM_INSTANCE   HOSTNAME   ASM_DISK_SIZE_STATUS             ASM_BLOCK_STATUS   ASM_CHAIN_STATUS   ASM_INCIDENTS   ASM_PROBLEMS
+--------------+----------+--------------------------------+------------------+------------------+---------------+--------------+
  +ASM1          nodeabc    WARNING - Available Size < 20%   PASS               PASS               PASS            PASS
+--------------+----------+--------------------------------+------------------+------------------+---------------+--------------+

tfactl_summary_asmoverview>list

  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => asm_clusterwide_status
        2 => asm_nodeabc
        3 => asm_nodexyz

tfactl_summary_asmoverview>2


     =====> asm_problems

  ADR_EVENTS
+---------------------------------------------------------------------------------------+
  .-----------------------------------------------------------------------------------.
  | STATUS_TYPE          | DETAILS                                                    |
  +----------------------+------------------------------------------------------------+
  | ADR_HOME             | /u01/app/grid/diag/clients/user_oracle/host_2241630980_110 |
  | ORACLE_HOME          | /u01/app/19.0.0.0/grid                                     |
  | PROBLEM_STATUS_CHECK | host_2241630980_110:PASS                                   |
  '----------------------+------------------------------------------------------------'
..
....
......

     =====> asm_instancefiles

  file_number   compound_index   bytes         block_size   name               modification_date   creation_date   striped   incarnation   group_number
+-------------+----------------+-------------+------------+------------------+-------------------+---------------+---------+-------------+--------------+
          253         16777469          1536          512   ASMPARAMETERFILE   31-AUG-20           31-AUG-20       COARSE     1049911287              1
          255         16777471     165974016         4096   OCRFILE            18-APR-21           31-AUG-20       COARSE     1049911291              1
          256         16777472         21504          512   PASSWORD           31-AUG-20           31-AUG-20       COARSE     1049911289              1
          257         16777473         11264          512   PASSWORD           31-AUG-20           31-AUG-20       COARSE     1049911469              1
          258         16777474      20971520        32768   ASMVDRL            18-APR-21           31-AUG-20       CUSTOM     1049912853              1
          259         16777475    5368709120      1048576   ASMVOL             18-APR-21           31-AUG-20       CUSTOM     1049912853              1
          260         16777476    5368717312         8192   DATAFILE           14-OCT-22           31-AUG-20       COARSE     1049923157          
...
....
     =====> asm_status_summary

  STATUS_TYPE                    STATUS
+------------------------------+-----------------------------------------------------------------+
  SYSTEM_DATE                    Sat Oct 15 05:29:06 UTC 2022
  ASM_HOME                       /u01/app/19.0.0.0/grid
  ASM_VERSION
  ASM_INSTANCE                   +ASM1
  ASM_DIAGNOSTICS_TRACE_FOLDER   /u01/app/grid/diag/asm/+asm/+ASM1/trace
  ASM_CHAIN_STATUS               PASS
  ASM_BLOCK_STATUS               PASS
  ASM_DISK_SIZE_STATUS           WARNING - Available Size < 20%
  ASM_DISK_GROUP_SUMMARY         .------------------------.
                                 | name | disk_size_alert |
                                 +------+-----------------+
                                 | DATA | Red : 14%       |
                                 | RECO | Red : 8%        |
                                 '------+-----------------'
  ADR_EVENTS                     .-------------------------------------------------------------.
                                 | HOSTNAME | INCIDENT_STATUS | INSTANCE_NAME | PROBLEM_STATUS |
                                 +----------+-----------------+---------------+----------------+
                                 | nodeabc  | PASS            | ASM1          | PASS           |
                                 '----------+-----------------+---------------+----------------'
+------------------------------+-----------------------------------------------------------------+

    =====> asm_diskgroup_details

  allocation_unit_size   name   disk_size_alert   state     usable_file_mb   type   total_mb   group_number
+----------------------+------+-----------------+---------+----------------+------+----------+--------------+
               4194304   DATA   Red : 14%         MOUNTED          5141366   HIGH   35163648              1
               4194304   RECO   Red : 8%          MOUNTED           731597   HIGH    8788992              2
+----------------------+------+-----------------+---------+----------------+------+----------+--------------+


  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => asm_clusterwide_status
        2 => asm_nodeabc
        3 => asm_nodexyz








--
--
--
--
--
--
--
-- Next I am going to select option 9 to view OS_OVERVIEW
--
--
tfactl_summary>9

  IDLE_TIME   #CORES   HOSTNAME   SWAP_USED   MEM_USED   TASKS   LOAD   #PROCESSORS
+-----------+--------+----------+-----------+----------+-------+------+-------------+
       94.4       12   nodeabc     0.00 %      59.94 %    1971   2.44            48
+-----------+--------+----------+-----------+----------+-------+------+-------------+

tfactl_summary_osoverview>list

  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => os_clusterwide_status
        2 => os_nodeabc
        3 => os_nodexyz

tfactl_summary_osoverview>2


     =====> os_disk_details

  PATH
+------------------------------------+
  /dev/asm/commonstore-332
  /dev/mapper/IO1_P1S00_2758906044
  /dev/mapper/IO1_P1S01_2758880300
  /dev/mapper/IO1_P1S02_2758924712
  /dev/mapper/IO1_P1S03_2758924872
  /dev/mapper/IO1_P1S04_2758924616
  /dev/mapper/IO1_P1S05_2758906308
  /dev/mapper/VolGroupSys-LogVolOpt
  /dev/mapper/VolGroupSys-LogVolRoot
  /dev/mapper/VolGroupSys-LogVolSwap
  /dev/mapper/VolGroupSys-LogVolU01
  /dev/md126
  /dev/sda
  /dev/sdb
  /dev/sdc
  /dev/sdd
  /dev/sde
  /dev/sdf
  /dev/sdg
  /dev/sdh
  /dev/sdi
  /dev/sdj
  /dev/sdk
  /dev/sdl
  /dev/sdm
  /dev/sdn
+------------------------------------+



     =====> os_status_summary

  STATUS TYPE     DETAILS
+---------------+--------------------------------------------------------------------------+
  OS and SYSTEM   .----------------------------------------------------------------------.
                  | #CORES | CACHE    | KERNEL_RELEASE                     | #PROCESSORS |
                  +--------+----------+------------------------------------+-------------+
                  |     12 | 22528 KB | 4.14.35-1902.303.4.1.el7uek.x86_64 |          48 |
                  '--------+----------+------------------------------------+-------------'
  SLEEPING TASK   .----------------------------------.
                  | PID | TASK              | USER   |
                  +-----+-------------------+--------+
                  | 531 | wait_woken        | oracle |
                  | 301 | KsWaitEvent_OSD   | root   |
                  | 267 | worker_thread     | root   |
                  | 217 | do_semtimedop     | oracle |
                  | 192 | smpboot_thread_fn | root   |
                  '-----+-------------------+--------'
  CPU DETAILS     .-------------------------------------------------.
                  | IDLE_TIME | SWAP_USED | LOAD | TASKS | MEM_USED |
                  +-----------+-----------+------+-------+----------+
                  |      94.4 |  0.00 %   | 2.44 |  1971 |  59.94 % |
                  '-----------+-----------+------+-------+----------'
+---------------+--------------------------------------------------------------------------+



     =====> os_disk_location

  ID   BLOCKS       BOOT   START   SYSTEM   END         PATH
+----+------------+------+-------+--------+-----------+-----------+
  ee   468845567+   NO         1   GPT      937691135   /dev/sda1
  ee   468845567+   NO         1   GPT      937691135   /dev/sdb1
+----+------------+------+-------+--------+-----------+-----------+



     =====> os_sleeping_tasks

  PID   TASK                    USER
+-----+-----------------------+--------+
  531   wait_woken              oracle
  301   KsWaitEvent_OSD         root
  267   worker_thread           root
  217   do_semtimedop           oracle
  192   smpboot_thread_fn       root
  112   rescuer_thread          root
   64   kthread_parkme          root
   38   do_semtimedop           grid
   22   pipe_wait               grid
   20   poll_schedule_timeout   root
+-----+-----------------------+--------+



     =====> system_configuration

|                                               System configuration                                              |
+-----------+-------------+----------+------------------------------------------+-------+----------+--------------+
  Frequency   #Processors   Hostname   Model_name                                 Cores   Cache      Vendor
+-----------+-------------+----------+------------------------------------------+-------+----------+--------------+
   3100.005            48   nodeabc    Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz      12   22528 KB   GenuineIntel
+-----------+-------------+----------+------------------------------------------+-------+----------+--------------+



     =====> os_cpu_details

  TYPE           DETAILS
+--------------+-----------------------------------------------------------------------------+
  AVERAGE_LOAD   .------------------------------------------------.
                 | LAST_5_MINUTE | LAST_1_MINUTE | LAST_15_MINUTE |
                 +---------------+---------------+----------------+
                 |          2.95 |          3.55 |           2.44 |
                 '---------------+---------------+----------------'
  TASKS          .------------------------------------------------.
                 | Total | Stopped | Running | Sleeping | Zombies |
                 +-------+---------+---------+----------+---------+
                 |  1971 |      17 |       1 |     1508 |       0 |
                 '-------+---------+---------+----------+---------'
  MEMORY         .-------------------------------------------------------------------------.
                 | Total        | Used         | Percent_Used | Buffers      | Free        |
                 +--------------+--------------+--------------+--------------+-------------+
                 | 39466067+ KB | 23657516+ KB |  59.94 %     | 12640815+ KB | 31677368 KB |
                 '--------------+--------------+--------------+--------------+-------------'
  SWAP           .-------------------------------------------------------------------.
                 | Total       |  | Used | Cached       | Percent_Used | Free        |
                 +-------------+--+------+--------------+--------------+-------------+
                 | 25165820 KB |  | 0 KB | 14715107+ KB |  0.00 %      | 25165820 KB |
                 '-------------+--+------+--------------+--------------+-------------'
  CPU            .----------------------------.
                 | VALUE | TYPE               |
                 +-------+--------------------+
                 |   1.9 | USER_CPU_TIME      |
                 |   1.7 | SYSTEM_CPU_TIME    |
                 |   0.0 | USER_NICE_CPU_TIME |
                 |  94.4 | IDLE_CPU_TIME      |
                 |   2.0 | IO_WAIT_CPU_TIME   |
                 |   0.0 | HARDWARE_IRQ       |
                 |   0.0 | SOFTWARE_IRQ       |
                 |   0.0 | STEAL_TIME         |
                 '-------+--------------------'
+--------------+-----------------------------------------------------------------------------+


  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => os_clusterwide_status
        2 => os_nodeabc
        3 => os_nodexyz

tfactl_summary_osoverview>

You can even save your output into an HTML file which is great for a quick view and very easy to present and transfer too. The option to generate report into HTML format is by calling “summary -overview -html” command within TFACTL and use option ‘q’ to save reports from tfactl_summary> CLI.
Let’s see how to do that.

[root@nodeabc ~]#
[root@nodeabc ~]# tfactl
WARNING - AHF Software is older than 180 days. Please consider upgrading AHF to the latest version using ahfctl upgrade.
tfactl> summary -overview -html

  Executing Summary in Parallel on Following Nodes:
    Node : nodeabc
    Node : nodexyz

LOGFILE LOCATION : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221012121321/log/summary_command_20221012121321_nodeabc_82905.log

  Component Specific Summary collection :
    - Collecting CRS details ... Done.
    - Collecting ASM details ... Done.
    - 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.

  Remote Summary Data Collection : In-Progress - Please wait ...
  - Data Collection From Node - nodexyz .. Done.

  Prepare Clusterwide Summary Overview ... Done
      cluster_status_summary

  COMPONENT   STATUS    DETAILS
+-----------+---------+---------------------------------------------------------------------------------------------------+
  CRS         PROBLEM   .-----------------------------------------------.
                        | CRS_SERVER_STATUS   : ONLINE                  |
                        | CRS_STATE           : ONLINE                  |
                        | CRS_INTEGRITY_CHECK : FAIL                    |
                        | CRS_RESOURCE_STATUS : OFFLINE Resources Found |
                        '-----------------------------------------------'
  ASM         PROBLEM   .-------------------------------------------------------.
                        | ASM_DISK_SIZE_STATUS : WARNING - Available Size < 20% |
                        | ASM_BLOCK_STATUS     : PASS                           |
                        | ASM_CHAIN_STATUS     : PASS                           |
                        | ASM_INCIDENTS        : PASS                           |
                        | ASM_PROBLEMS         : PASS                           |
                        '-------------------------------------------------------'
  ACFS        OFFLINE   .-----------------------.
                        | ACFS_STATUS : OFFLINE |
                        '-----------------------'
  DATABASE    PROBLEM   .-----------------------------------------------------------------------------------------------.
                        | ORACLE_HOME_DETAILS                                                        | ORACLE_HOME_NAME |
                        +----------------------------------------------------------------------------+------------------+
                        | .------------------------------------------------------------------------. | OraDB19000_home1 |
                        | | PROBLEMS | INCIDENTS | DB_BLOCKS | DATABASE_NAME | STATUS  | DB_CHAINS | |                  |
                        | +----------+-----------+-----------+---------------+---------+-----------+ |                  |
                        | | PROBLEM  | PROBLEM   | PASS      | FATDBA1       | PROBLEM | PROBLEM   | |                  |
                        | '----------+-----------+-----------+---------------+---------+-----------' |                  |
                        '----------------------------------------------------------------------------+------------------'
  PATCH       OK        .----------------------------------------------.
                        | CRS_PATCH_CONSISTENCY_ACROSS_NODES      : OK |
                        | DATABASE_PATCH_CONSISTENCY_ACROSS_NODES : OK |
                        '----------------------------------------------'
  LISTENER    OK        .-----------------------.
                        | LISTNER_STATUS   : OK |
                        '-----------------------'
  NETWORK     OK        .---------------------------.
                        | CLUSTER_NETWORK_STATUS :  |
                        '---------------------------'
  OS          OK        .-----------------------.
                        | MEM_USAGE_STATUS : OK |
                        '-----------------------'
  TFA         OK        .----------------------.
                        | TFA_STATUS : RUNNING |
                        '----------------------'
  SUMMARY     OK        .------------------------------------.
                        | SUMMARY_EXECUTION_TIME : 0H:1M:44S |
                        '------------------------------------'
+-----------+---------+---------------------------------------------------------------------------------------------------+


        ### Entering in to SUMMARY Command-Line Interface ###

tfactl_summary>list

  Components : Select Component - select [component_number|component_name]
        1 => overview
        2 => crs_overview
        3 => asm_overview
        4 => acfs_overview
        5 => database_overview
        6 => patch_overview
        7 => listener_overview
        8 => network_overview
        9 => os_overview
        10 => tfa_overview
        11 => summary_overview

tfactl_summary>q

        ### Exited From SUMMARY Command-Line Interface ###

--------------------------------------------------------------------
REPOSITORY  : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221012121321/nodeabc
HTML REPORT : <REPOSITORY>/report/Consolidated_Summary_Report_20221012121321.html
--------------------------------------------------------------------

tfactl> 

This is how the HTML report looks like, the summary TFA report.

Hope It Helped!
Prashant Dixit

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: