【重磅更新】Oracle数据库巡检脚本英文版来啦

[oracle@lhrora11204 ~]$ sqlplus /as sysdba @DB_Oracle_HC_lhr_v7.0.0_11g_en.sql

SQL*Plus: Release11.2.0.4.0 Production on Thu Dec519:24:032024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options

Note1: check Session Information

   INST_ID       DBID NAME      DATABASE_ROLE        CREATED              LOG_MODE      OPEN_MODE            VERSION    SESSIONID
---------- ---------- --------- -------------------- -------------------- ------------- -------------------- ---------- --------------------
         12007947551 LHR11G    PRIMARY              2020-07-2408:10:07  ARCHIVELOG    READ WRITE           11.2.0.4.0187,59095,917494

Note2: Database Recycle Bin Status for This check

OWNER           RECYB_SIZE_M  RECYB_CNT
--------------- ------------ ----------
LHR                     5.19          6
SYSMAN                     0          2
SUM                     5.19          8


+------------------------------------------------------------------------------------------------------------+
|                   Oracle Database health Check script                                                      |
|------------------------------------------------------------------------------------------------------------+
|  Copyright (c) 2015-2100 lhr. All rights reserved. (My Blog Address: https://www.dbaup.com)       |
+------------------------------------------------------------------------------------------------------------+
|  About Me: Xiaomaimiao   QQ: 646634621       WeChat Official Account: AiDBA                                                     |
+------------------------------------------------------------------------------------------------------------+

Note: Do not modify anycheck results
Please send the file of DB_healthcheck_by_lhr_dbname_YYYYMMDD.html to lhrbest@qq.com or QQ(646634621)

: Database check Script
Author: Xiaomaimiao [All rights reserved, infringement will be investigated]
QQ: 646634621

Description:
This script is used tocheck various indicators of an Oracle 11g database, including key parameters, major objects, storage space configuration, database performance (AWR, ASH, ADDM), RMAN backup status, and more.

Important Notes:
① A. If there is garbled output on the screen when executing the script, you need toset environment variables and SSH software:
Linux: LANG=en_US.UTF-8
Run export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
Set the character setof the software executing the script to UTF8.

B. If there is garbled text in the final HTML report, open the HTML file with a text editor and modify the third line (charset=UTF-8).

C. It is strongly recommended to run this script on Windows.

D. The HTML report is generated in the current directory, so the Oracle user needs permission tocreate files in this directory.

② The finalcheck report will be generated in the current directory (please ensure the current OS user has write permissions for the directory).
③ The executing user must meet the following two conditions, or it is best to run the script with the SYS user: A. The executing user should be granted the DBA role, and permissions to query any data dictionary, DBMS_SYSTEM, and AWR:

GRANT DBA TO XXX
GRANTSELECTANY DICTIONARY TO XXX
GRANTEXECUTEON DBMS_WORKLOAD_REPOSITORY TO XXX
GRANTEXECUTEON DBMS_SYSTEM TO XXX
GRANTSELECTON MGMT$ALERT_CURRENT TO XXX

B. The user must have query permissions on x$bh, otherwise, hot blocks cannot be queried. The script isas follows:

CREATEOR REPLACE VIEW BH ASSELECT*FROM SYS.X$BH
CREATEOR REPLACE PUBLIC SYNONYM X$BH FOR BH

④ If the script hangs on the DBA_FREE_SPACE view, it might be due to too many objects in the recycle bin. You can manually execute the SQL command SELECTCOUNT(1) FROM DBA_FREE_SPACE; to check. If it runs slowly, it is recommended tofirst use the SYS userto clear the recycle bin (PURGE DBA_RECYCLEBIN;) before running the health check script.
+----------------------------------------------------------------------------+
The check script execution will take a few minutes, depending on the size of the database.
Execution is starting...
+----------------------------------------------------------------------------+

-----Oracle Database  Check STRAT, Starting Collect Data Dictionary Information----
start..... Setting environment variables and configuring HTML header....
start... Database check service overview. .
            Database overview. . .
            TableSpace condition. . .
            ASM Disk Monitoring. . .
            Job info. . .
start... Database check service details. .
            Rman Info. . .
            archive  information . . .
            SGA information . . .
            file IO information . . .
            SQL monitoring. . .
            flashback  archive . . .
            DG. . .
start... Database  security . .
            database  user . . .
            System TableSpace user. . .
start... Database  object . .
             segments  information . . .
                The 10 segments with the largest volume . . . .
                The 10 segments with the most extensions . . . .
                LOB segments . . . .
                Undo  segments . . . .
             partition tables  information . . .
             Index  information . . .
                Foreign keys arenot indexed. . . .
                The large index is never used . . . .
                The number of index columns is greater than three. . . .
                The index height is greater than 3. . . .
                The Statistics information of the index is too old. . . .
             parallel . . .
             Others  object . . .
                Alert  log . . . .
start... Database Performance analysis . .
            AWR. . .
                Hot block . . . .
            Statistics information . . .
            Session . . .
                Number of historical ACTIVE sessions. . . .
            Waiting event . . .
generate The latest AWR report....
generate The latest ASH report....
generate The SQL report that takes the longest timeto execute....
Endof script execution....

The check report is generated in the current directory (Ensure that the current OS user has writable permission on the current directory).: DB_healthcheck_by_lhr_LHR11G_11.2.0.4.0_20241205192403.html
The check script is complete！
Disconnected from Oracle Database 11g Enterprise Edition Release11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options
[oracle@lhrora11204~]$ ll
total 2864
-rw-r--r-- 1 oracle oinstall 2269457 Dec  5 19:24 DB_healthcheck_by_lhr_LHR11G_11.2.0.4.0_20241205192403.html
-rw-r--r-- 1 oracle oinstall  658290 Dec  5 19:22 DB_Oracle_HC_lhr_v7.0.0_11g_en.sql