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:
[email protected]:/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:
- Useful Srvctl Commands
- Useful CRSCTL Commands
- Useful Gather Statistics Commands In Oracle
- Useful ADRCI Commands In Oracle
- Useful DGMGRL Commands In Oracle Dataguard
- Useful database monitoring queries
- Useful Flashback Related Commands
- Useful scheduler job Related Commands
- Useful Oracle Auditing Related Commands
- Useful RMAN Commands
- Useful TFACTL Commands
oratoptfactl DB TOOLS