Oratop is a text based user interface tool for monitoring basic database operations in real time. In this article we will explain how to use oratop tool .

There are two ways we can install oratop.

MTEHOD 1:( using TFACTL):

Now oracle is providing oratop, tfactl, oswatcher, orachk etc as part of Automatic health framework(AHF). So if AHF is installed , means oratop is already available.

STEPS FOR – > HOW TO INSTALL AHF :

Check whether the status using below tfactl command:

root@dbhost-1:/export/home/scsun# tfactl toolstatus

.------------------------------------------------------------------.
|                   TOOLS STATUS - HOST : dbhost-1                  |
+----------------------+--------------+--------------+-------------+
| Tool Type            | Tool         | Version      | Status      |
+----------------------+--------------+--------------+-------------+
| Development Tools    | orachk       |   20.2.0.0.0 | DEPLOYED    |
|                      | oratop       |       14.1.2 | DEPLOYED    |. - >>> ORATOP IS AVAIABLE.
+----------------------+--------------+--------------+-------------+
| Support Tools Bundle | darda        | 2.10.0.R6036 | DEPLOYED    |
|                      | oswbb        |        8.3.2 | RUNNING     |
|                      | prw          | 12.1.13.11.4 | NOT RUNNING |
+----------------------+--------------+--------------+-------------+
| TFA Utilities        | alertsummary |   20.2.0.0.0 | DEPLOYED    |
|                      | calog        |   20.2.0.0.0 | DEPLOYED    |
|                      | dbcheck      |   18.3.0.0.0 | DEPLOYED    |
|                      | dbglevel     |   20.2.0.0.0 | DEPLOYED    |
|                      | grep         |   20.2.0.0.0 | DEPLOYED    |
|                      | history      |   20.2.0.0.0 | DEPLOYED    |
|                      | ls           |   20.2.0.0.0 | DEPLOYED    |
|                      | managelogs   |   20.2.0.0.0 | DEPLOYED    |
|                      | menu         |   20.2.0.0.0 | DEPLOYED    |
|                      | param        |   20.2.0.0.0 | DEPLOYED    |
|                      | ps           |   20.2.0.0.0 | DEPLOYED    |
|                      | pstack       |   20.2.0.0.0 | DEPLOYED    |
|                      | summary      |   20.2.0.0.0 | DEPLOYED    |
|                      | tail         |   20.2.0.0.0 | DEPLOYED    |
|                      | triage       |   20.2.0.0.0 | DEPLOYED    |
|                      | vi           |   20.2.0.0.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.

We can observe oratop is already available.

USAGE:

tfactl oratop -help

 

oracle$ tfactl oratop -help
Usage : /opt/oracle.ahf/tfa/bin/tfactl.pl [run] oratop -database
Options:
-database Database name to run oratop
: default will be / as sysdba. Specify a different user using
{username[/password][@connect_identifier] | / }
[AS {SYSDBA|SYSOPER}]
connect_identifier: host[:port]/[service_name]
:
-d : real-time (RT) wait events, section 3 (default is Cumulative)
-k : FILE#:BLOCK#, section 4 lt is (EVENT/LATCH)
-m : MODULE/ACTION, section 4 (default is USERNAME/PROGRAM)
-s : SQL mode, section 4 (default is process mode)
-c : database service mode (default is connect string)
-f : detailed format, 132 columns (default: standard, 80 columns)
-b : batch mode (default is text-based user interface)
-n : maximum number of iterations (requires number)
-i : interval delay, requires value in seconds (default: 5s)

e.g:
/opt/oracle.ahf/tfa/bin/tfactl.pl oratop -database testdb1
/opt/oracle.ahf/tfa/bin/tfactl.pl oratop -database testdb1 -bn1

/opt/oracle.ahf/tfa/bin/tfactl.pl run oratop -database testdb1
/opt/oracle.ahf/tfa/bin/tfactl.pl run oratop -database testdb1 -bn1


 o:~$ tfactl oratop -database PROD1 -dsf

oratop: Release 14.1.2 Production on Sat Aug 22 12:32:26 2020
Copyright (c) 2011, Oracle.  All rights reserved.

Connecting ...
Oracle 12c - Primary PROD1 12:32:28 up:  66d,   2 ins,   32 sn,   3 us, 204G mt,    0% fra,   0 er,                         0% db
ID %CPU LOAD %DCU   AAS  ASC  ASI  ASW  ASP  AST  UST MBPS IOPS IORL LOGR PHYR PHYW  %FR   PGA TEMP UTPS UCPS SSRT DCTR DWTR  %DBT
 2    0    0    0     0    0    0    0    0    0   11    0    4  44u   26    0    0   25  4.0G 4.0M    0   10 766u   98    1  97.9
 1    0    0    0     0    0    0    0    0    0   21    0    4  21u    3    0    0   25   90M 4.0M    0    5 576u   64   35   2.1

EVENT (RT)                                                        TOTAL WAITS   TIME(s)  AVG_MS  PCT                    WAIT_CLASS
DB CPU                                                                         4.55E+07          100
ges generic event                                                   4.546E+09  4.55E+07     0.1  100                         Other




ID  USERNAME  MODULE   ACTION  SQL_ID          SQL_TEXT               X  ELAP  CPUT  IOWT  WAIT  EXEC  ROWS  BUFG  DISK  BH%  LOAD

METHOD 2: (Using standalone oratop)

If AHF setup is not available in the server, And you want just a standalone oratop utlity, then follow below steps:

1.Download the oratop utility:

Use the metalink Doc ID 1500864.1 for download:

2. Copy and provide proper permission:

-- Rename to downloaded file to proper name

mv oratop* oratop

--- provide execute permission:

chmod 755 oratop

3. Now run oratop command:

-- Set env

export ORACLE_HOME=/oracle/app/oracle/product/dbhome_1
export ORACLE_SID=PROD1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

oracle$ ./oratop -help

oratop: Release 14.1.2
Usage:
oratop [ [Options] [Logon] ]

Logon:
{username[/password][@connect_identifier] | / }
[AS {SYSDBA|SYSOPER}]

connect_identifier:
o TNS alias resolved in the tnsnames.ora, or,
o Easy Connect: host[:port]/[service_name]
Options:
-d : real-time (RT) wait events, section 3 (default is Cumulative)
-k : FILE#:BLOCK#, section 4 lt is (EVENT/LATCH)
-m : MODULE/ACTION, section 4 (default is USERNAME/PROGRAM)
-s : SQL mode, section 4 (default is process mode)
-c : database service mode (default is connect string)
-f : detailed format, 132 columns (default: standard, 80 columns)
-b : batch mode (default is text-based user interface)
-n : maximum number of iterations (requires number)
-i : interval delay, requires value in seconds (default: 5s)
-v : oratop program version number
-h : this help


--- Run oratop now:


 ./oratop / as sysdba

oratop: Release 14.1.2 Production on Sat Aug 22 12:07:02 2020
Copyright (c) 2011, Oracle.  All rights reserved.

Connecting ...
Oracle 12c - D2R 12:06:59 up: 274d,   1 ins,   40 sn,   2 us,  16G mt,  0.2% db
ID %CPU LOAD %DCU   AAS  ASC  ASI  ASW  AST IOPS %FR   PGA UTPS UCPS SSRT  %DBT
 1    1    4    0   0.4    0    0    0    0   35  17  813M    1  240 501u   100

EVENT (C)                        TOT WAITS   TIME(s)  AVG_MS  PCT    WAIT_CLASS
DB CPU                                       7283862           69
log file parallel write          1.582E+09   2077567     1.3   20    System I/O
SQL*Net break/reset to client    3.098E+09    526294     0.2    5   Application
db file parallel write           1.057E+08    310476     2.9    3    System I/O
log file sync                    2.641E+08    290967     1.1    3        Commit

ID   SID    SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LAT  W/T
 1   979   28260 SYS orat D 4.3M gncgu1f2aq8my SEL    0 ACT CPU cpu runque   3u                                                                                                  
 


-- with full column output:


./oratop -f

oratop: Release 14.1.2 Production on Sat Aug 22 12:23:56 2020
Copyright (c) 2011, Oracle.  All rights reserved.

Connecting ...


oratop: Release 14.1.2 Production on Sat Aug 22 12:23:56 2020

Copyright (c) 2011, Oracle.  All rights reserved.

Enter username: / as sysdba

Oracle 12c - Primary PROD1 12:26:55 up: 5.8d,   2 ins, 3.6k sn,  10 us, 524G mt,    0% fra,   7 er,                       0.3% db
ID %CPU LOAD %DCU   AAS  ASC  ASI  ASW  ASP  AST  UST MBPS IOPS IORL LOGR PHYR PHYW  %FR   PGA TEMP UTPS UCPS SSRT DCTR DWTR  %DBT
 2    0    6    0   6.0    5    4    0    0    5 3.3k    4  503  16u 649k  732    2   33  862M 3.9G    2  207  58m   68   31  93.5
 1    0    2    0   0.4    0    0    0    0    0  308    0   39   5u 1.3k    0    0   41  2.7G  87M    0   31   4m   93    6   6.5

EVENT (C)                                                         TOTAL WAITS   TIME(s)  AVG_MS  PCT                    WAIT_CLASS
gc buffer busy acquire                                              9.247E+09   6895716     0.8   41                       Cluster
DB CPU                                                                          4167972           25
read by other session                                               4.638E+09   3261348     0.7   19                      User I/O
latch: cache buffers chains                                         8.731E+09   1509927     0.2    9                   Concurrency
db file sequential read                                             1.530E+09    905780     0.6    5                      User I/O

ID   SID    SPID USERNAME  PROGRAM    SRV  SERVICE  PGA  SQLID/BLOCKER OPN  E/T  STA  STE  WAIT_CLASS  EVENT/*LATCH            W/T
 2 13169   14079 SADMIN    siebmtshm  DED  PROD1  1.0G  3nwd46r124xc2 SEL 3.0d  ACT  CPU  Cluster     cpu runqueue           658u
 2  1706   41071 SADMIN    siebmtshm  DED  PROD1  395M  3nwd46r124xc2 SEL 3.0d  ACT  CPU  User I/O    cpu runqueue           517u
 2 15424   52740 SADMIN    siebmtshm  DED  PROD1  1.0G  3nwd46r124xc2 SEL 3.0d  ACT  CPU  Concurrenc  cpu runqueue            28u
 2 22572   27719 SADMIN    siebmtshm  DED  PROD1  395M  3nwd46r124xc2 SEL 3.0d  ACT  WAI  Cluster     gc buffer busy acquire    0
 2  7429  109975 SADMIN    siebmtshm  DED  PROD1  395M  3nwd46r124xc2 SEL 2.9d  ACT  WAI  Cluster     gc buffer busy acquire    0

— Few other oratop commands:

./oratop -f

./oratop -dsf

./oratop -sbn4

SEE ALSO: