Oracle輕量級實時監控工具-oratop

Oracle 輕量級實時監控工具 -oratop

 

適用於oracle單機、oracle RAC、oracle ADG

支援的資料庫版本：

11gR2 (11.2.0.3, 11.2.0.4)

12cR1 (12.1.0.1, 12.1.0.2, 12.2.0.1)

18c、19c、20c ...

 

本文包括兩部分內容

一：翻譯官方文件 oratop User Guide

二：安裝和測試使用oratop

 

說明： oratop User Guide 文件以及oratop( 11gR2和12cR1 )安裝包來自

oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

12cR1 以後的版本預設在： $Oracle_HOME/suptools/oratop 目錄下

 

一：翻譯官方 oratop User Guide 文件

oratop

A Database Monitoring Tool  

Jan 2019

 

使用Oracle呼叫介面（OCI）API的基於文字的使用者介面實用程式，其外觀類似於Unix“top”實用程式 。它收集的資料來源於資料庫內部檢視。該工具即適用於Oracle單機環境，也適用於RAC叢集環境。它為使用者提供了近實時監視資料庫的能力，並不會取代Unix“top”、EMGC、EM database Express或伺服器可管理性工具（如ASH、AWR、ADDM等），這些工具提供了更深入和不同維度的資料。

A text-based user interface utility using Oracle Call Interface (OCI) API that   resembles Unix “top” utility in appearance. Its data gathering is solely from the database using internal views. The utility can be run against Real Application Cluster (RAC) or non-RAC databases. It provides a user the ability to monitor the database in near real time and it is not intended to replace Unix “top”, EMGC, EM  Database Express, or server manageability tools like ASH, AWR, ADDM, etc., that provides in-depth and granular details. 

Abstract   摘要

or a top可用於監視任何平臺上的Oracle資料庫，但是可執行檔案必須在支援的Unix平臺上執行，並具有與Oracle相容的客戶端。要監視其他平臺（如Windows）上的資料庫，只需在Unix客戶機上的tnsnames.ora中定義一個別名，然後像使用sqlplus一樣連線到遠端資料庫。

oratop can be used to monitor Oracle databases on any platform but the   executable must run on supported Unix platforms with an Oracle compatible client. To monitor databases on other platforms like Windows, simply define an alias in tnsnames.ora on a Unix client and connect to the remote database as you would with sqlplus.

Oracle支援的最低版本是11gR2。MOS Note（Doc ID 1500864.1）中提供了與11gR2和12cR1 Oracle版本相容的獨立oratop 下載 。對於以後的Oracle版本，它位於資料夾$Oracle_HOME/suptools/oratop下。此外，它還與Oracle跟蹤檔案分析器（TFA）捆綁在一起。

Minimum supported Oracle release is 11gR2. A standalone oratop compatible with 11gR2 and 12cR1 Oracle releases is available in MOS Note (Doc ID 1500864.1). For later Oracle releases it is available under the folder   $ORACLE_HOME/suptools/oratop. Also, i t is bundled   with Oracle Trace File Analyzer (TFA).

 

Release 15.0.0   版本15.0.0

除了一些bug修復之外， 還有一些 例項部分section2 中的 次要的列名、位置、替換和新新增的更改 。

廢棄的特性：多租戶的可插入資料庫（pdb容器級）監視和基於RAC服務的監視。原因是該工具可用的伺服器統計資訊有限。

安全性:不再允許在命令列或重定向(FIFO管道)上使用 明文 密碼登入，只允許在提示符處( ***加密輸入密碼 )。

In addition to some bug fixes, some minor columns name, placement, replacement and new addition changes mostly in the instance section, section2.

Obsoleted Features : Multitenant’s pluggable database (pdb container level) monitoring and RAC service-based monitoring. Reason is the limited server stats available to the tool.

Security : Login with visible password on the command line or redirection (FIFO pipe) is no longer permissible, its allowed only at the prompt (hidden).

 

INTRODUCTION   介紹

Oratop工具 允許有特權的使用者監視Oracle資料庫活動。它幾乎實時地動態執行，併為正在執行的資料庫提供一個活動視窗。它是單例項和RAC感知的實用程式。(參見下面圖1所示的示例快照)

The Oracle program named oratop allows privileged users to monitor oracle database activities. It runs dynamically in near real time and provides a live window to a running database. It is Single instance and RAC aware utility. (see sample snapshot shown in Figure 1 below)

 

Key motivations include ( 主要監控 ) :

監控當前的資料庫活動，

Monitoring current database activities,

監控資料庫效能，

Database performance,

識別爭用和瓶頸。

Identifying contentions and bottleneck

 

Features Highlights ( 特色亮點 )  

程式和SQL 監視

Process & SQL Monitoring

實時等待事件監控

Real time wait events

支援ADG監控

Active Data Guard support

支援 多租戶資料庫（CDB ）（僅種子級）

Multitenant Database (CDB) support (seed level only)

 

VISUAL LAYOUT (視覺化介面)

The “oratop” displays relevant database activity information presented in four sections.

oratop在下面四個部分顯示了資料庫活動資訊。

1：全域性資料庫資訊

Section 1 DATABASE : Global database information

2：資料庫例項資訊

Section 2 INSTANCE : Database instance Activity

3：類似AWR中的前五等待事件

Section 3 EVENT : AWR like “Top 5 Timed Events“

4：程式或SQL資訊

Section 4 PROCESS | SQL : Processes or SQL mode information 

Oracle 客戶端環境變數

Oracle Client Home Environment

Shell環境變數設定：

Shell environment settings:

$ export ORACLE_HOME=<path>

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/suptools/oratop:$PATH

 

幫助和引數選項

Help and Input arguments options

各種選項和選擇的具體細節可以在命令列中使用help(或' -h ')或在執行時互動式地(按鍵盤鍵' h ')獲得。

Specific detail of the various options and selection can be obtained using help (or ‘ -h ’) at the command line or interactively at runtime (keyboard key press ‘ h ’).

一．命令列模式

I. COMMAND LINE MODE

通過“-h”選項可以獲得命令列概要，如圖2所示。

Command line synopsis can be obtained by invoking the utility with “-h” option as shown in Figure 2. 

Logon 登入

連線方法在很大程度上類似於sqlplus，但是，命令列不允許使用密碼，而是提示使用者輸入密碼。有關工具的使用，請參見圖2。

Connection method is largely similar to sqlplus, however, passwords are not allowed

at the command line, rather the user will be prompted for it. See Figure 2 for tool

usage.

Examples , 例如

$ oratop

使用者將被提示輸入密碼

User will be prompted to enter credentials.

連線本地資料庫

Bequeath 1  

export ORACLE_SID=<sid>

oratop / as sysdba

連線遠端資料庫

Remote Connection 2

TNS

匯出TNS_ADMIN並使用tnsnames中定義的TNS別名進行連線。或將TWO_TASK設定為tns別名，並在沒有別名的情況下進行連線。

export TNS_ADMIN and connect with a TNS alias name as defined in tnsnames.ora,

or set TWO_TASK to the tns alias and connect without alias name

EZConnect

使用者將被提示輸入密碼

user will be prompted to enter password

oratop system@rmtdbhost:1530/db1.domain.com

 

Batch Mode 批處理模式

批處理模式適用於帶有相關輸入選項的命令列。下面的圖3顯示了在sql模式下批量執行2個週期的輸出(第4部分)。

Batch mode is applicable at the command line with the relevant input options. See Figure 3 below shows output from batch run of 2 cycles in sql mode (section 4).

請注意。在RAC 環境中，雖然第 2 部分 ( 例項部分 ) 僅限於基於文字的使用者介面中的前 5 個例項，但是所有可用的例項都將以批處理模式列出。

Note. in RAC environment, while section 2 (instance section) is restricted to the top 5 instances in the text-based user interface, all of the available instances will be listed in the batch mode.

 

I. INTERACTIVE MODE 交換模式

在啟動實用程式後，按下鍵盤鍵“h”，可以看到執行時互動和詳細選項(參見下面的圖4)

Runtime interactive and detail options can be seen after starting the utility followed by pressing the keyboard key ‘h’, (See Figure 4 below). 

Detailed format (long) 詳細的格式(長)

圖5 . 長格式( 按 f 鍵 ) 顯示擴充套件和附加資訊

Figure 5. Long format (key press ‘f’) shows expanded and additional information

 

Miscellaneous 雜項

%DCP

列“%DCP”(在第2節中)表示資料庫例項佔用主機cpu百分比。

Column “ %DCP ” (in section 2) is the database instance cpu usage as %CPU of the host. 

值已紅色顯示

Values appears in RED color

用紅色標記和突出顯示的值僅僅是一個警告或強調重要性

Values flagged and highlighted in red color  are merely a warning or to emphasize

importance

Exiting 3   退出

退出程式時，使用者可按下列任意鍵:“q”或“ Q ”，或按Esc鍵或Ctrl+c(中止)

在所有情況下，都要執行適當的OCI會話清理和登出

To quit the program, user may press any of the following keyboard keys: "q" or "Q", or Esc key or Ctrl+c (to abort)

In all cases, proper OCI session cleanup and logout is performed.

Unprivileged user   特權使用者

一個典型錯誤，非特權使用者連線oratop會報錯 “ORA-00942: table or view does not exist”   ，系統管理員可以對普通使用者授權 GRANT SELECT ANY DICTIONARY TO <username>; 退出實用程式時，在某些情況下(極端情況)，程式可能會將終端(xterm) SHELL環境置於不希望的設定中。要將終端恢復到原來的設定，例如在Linux上，使用者可以發出“reset”命令。

A typical error encountered by a non-privileged user upon connection to the database using the tool is “ORA-00942: table or view does not exist”  

To allow the non-privileged user to use oratop, the system administrator with a DBA role may issue the following grant: “ GRANT SELECT ANY DICTIONARY TO <username>;”

3 Upon exiting the utility and in some situation (corner cases) the program may leave the terminal (xterm) SHELL environment in an undesirable setting. To restore the   terminal to its original settings is platform specific, e.g. on Linux, the user may issue the command “ reset ”.

TECHNICAL DETAILS   技術細節

“oratop”程式是用C程式編寫的，使用的是Oracle程式介面(OCI)。它不會對資料庫伺服器造成壓力，並且在伺服器上留下很小的痕跡，即程式使用的sql。它使用Unix“termio”、“ioctl”庫和VT100轉義字元來實現游標控制、字型顏色、程式退出控制和終端(xterm)大小調整功能。

The “oratop” program is written in C program using Oracle program interface (OCI). It is not intrusive to a database server, and it leaves small footprints on the server, namely the sqls used by the program. It employs Unix “termio, “ioctl” libraries, and VT100 escape characters to achieve cursor control, font colors, program exit   control and terminal (xterm) resizing capability.

在客戶端，or a top程式是一個非常 輕量級 的程式，它使用 很少 的CPU和記憶體。對於伺服器程式，它的資源也很少，但是依賴於伺服器。

On the client side, the oratop program is a very light process that uses minimal CPU and Memory. For the server process, its resources are minimal too but server   dependent.

 

Limitations   限制

程式不能移植到Windows/NT 平臺 ;它只在Unix平臺上執行。

不過，可以通過從Unix上相容的Oracle客戶機遠端連線來監視在Windows上執行的資料庫。該程式與正在使用的oracle版本的oracle客戶端相容。

更高版本的Oracle release home中的oratop也可以用於監視較低版本的資料庫。 資料庫需要配置如下引數： statistics_level=TYPICAL

The program is not portable to Windows/NT; it runs on Unix platforms only.

Nevertheless, one can monitor a database running on Windows by connecting remotely from a compatible Oracle client on Unix.

The program is compatible with oracle client of the oracle release in use.

oratop from higher Oracle release home can be used also to monitor databases with lower version.

Requires server to have been started with the following parameter

statistics_level=TYPICAL 4

 

Caveats   警告

程式在執行時可能會出現異常，大多數異常都是意料之中的，因為事件可能發生在程式執行/ 獲取操作的過程中。

The program may exhibit anomalies at run time, most of which are expected since an event may occur while the program is in the middle of executing/ fetching operations.

Blank Screen   白屏

在 負載特別高的伺服器 上 ， 下列操作可 能使 終端機在短時間內 白屏：

On a busy server, the following operations may leave the terminal blank for a short period:

程式初始化

A program initialization

終端大小調整(縮小/擴充套件終端)

A terminal resizing (shrink/expand the terminal)

一個例項加入或離開叢集

An instance joining/leaving the cluster

退出互動式按鍵選單

Quitting interactive keys menus

退出程式時

Upon exiting the program

 

對鍵盤按鍵反應遲緩

Sluggish response to keyboard key press

oratop SQL效能可能會受到超載系統的影響。在繁忙的伺服器上按下鍵盤鍵與程式進行互動可能會出現響應緩慢的情況，或者是由於遠端執行時的網路延遲造成的。按鍵響應時間也可能取決於間隔/執行狀態。

oratop SQL performance may be impacted on an over loaded systems.

Pressing a keyboard key to interact with the program may appear to have slow response on a   busy server or due to network latency if run remotely. Key press response time may also depends on the interval/execution state.

異常終止與任何SQL會話一樣，程式可能由於伺服器錯誤而失敗，並且將顯示特定的錯誤。

Abnormal termination   Like any SQL session, the program may fail due to a server error, and the particular error will be displayed.

 

CONCLUSION   結論

oratop實用程式為dba使用者提供了實時監控執行資料庫狀態和活動方式。它聚合了一些有用的診斷資訊，比如資料庫的整體效能，從最優到由於瓶頸而可能出現的效能下降，低效的sql，潛在的阻塞，記憶體洩漏等等。此外，它還可以用於調優資料庫、應用程式和使用者併發。Statistics_level不能設定為“BASIC”，因為它將禁用所需的timed_statistics。

The oratop utility provides dba user a quick overview of a running database status and activity. It aggregates useful diagnostics such as overall database performance from optimal to possible degradation due bottleneck, badly performing SQLs,   potential blocker(s), memory leak, etc. Furthermore, it can be useful in tuning some aspects of the database, application and user concurrency.

4 Statistics_level cannot  be set to “BASIC” since it will disable the required timed_statistics

 

二：安裝和測試使用oratop

作業系統資訊

[root@cjcos ~]# cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.5 (Maipo)

[root@cjcos ~]# uname -a

Linux cjcos 4.1.12-112.16.4.el7uek.x86_64 #2 SMP Mon Mar 12 23:57:12 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux

資料庫資訊

SQL> select banner_full from v$version;

BANNER_FULL

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME     OPEN MODE  RESTRICTED

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

 2 PDB$SEED     READ ONLY  NO

 3 CJCPDB     READ WRITE NO

自帶oratop工具

[oracle@cjcos ~]$ cd $ORACLE_HOME/suptools/oratop

[oracle@cjcos oratop]$ pwd

/u01/app/oracle/product/19.0.0/dbhome_1/suptools/oratop

[oracle@cjcos oratop]$ ll -rth

total 128K

-rwxr-x--x 1 oracle oinstall 127K Jan 25 17:10 oratop

預設沒有設定oratop命令的環境變數

[oracle@cjcos ~]$ oratop

bash: oratop: command not found...

Oratop工具 11gR2和12cR1 版本在下面文章裡下載：

oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

12cR1 以後版本，預設在  $ORACLE_HOME/suptools/oratop 下。

設定環境變數

[oracle@cjcos ~]$ vim .bash_profile

......

export ORACLE_SID=cjcdb

#export PATH=$ORACLE_HOME/bin:$PATH

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/suptools/oratop:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

[oracle@cjcos ~]$ source .bash_profile

檢視幫助資訊

登入

[oracle@cjcos ~]$ oratop

 

oratop: Release 15.0.0 Production on Thu Feb 20 04:41:26 2020

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

 

Enter username: / as sysdba

 

Connecting ..

Processing ...

按f顯示長格式

模擬鎖阻塞 會話資訊

[oracle@cjcos ~]$ oratop / as sysdba

SQL> select sql_text from v$sql where sql_id='9j7zacgg2rt9q';

SQL_TEXT

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

update t2 set object_id=10000 where object_id=1000

SQL> select sql_text from v$sql where sql_id='49znxwtck1hr8';

SQL_TEXT

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

update t2 set object_id=10000000 where object_id=1000

SQL資訊

[oracle@cjcos ~]$ oratop / as sysdba -s

關閉資料庫

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2676385/，如需轉載，請註明出處，否則將追究法律責任。