原 Oracle数据库巡检脚本英文版来啦
👉 本文共约429个字，系统预计阅读时间或需2分钟。
前言
麦老师的Oracle数据库巡检脚本使用说明请参考：https://dbhealthcheck.dbaup.com/script_docs/Oracle_dbhealthcheck.html
在使用时，需要配置export NLS_LANG=AMERICAN_AMERICA.AL32UTF8，但有的朋友仍然可能会出现乱码，
故写了一版纯英文版本供大家使用。
使用示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | [oracle@lhrora11204 ~]$ sqlplus / as sysdba @DB_Oracle_HC_lhr_v7.0.0_11g_en.sql SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 5 19:24:03 2024 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Note1: check Session Information INST_ID DBID NAME DATABASE_ROLE CREATED LOG_MODE OPEN_MODE VERSION SESSIONID ---------- ---------- --------- -------------------- -------------------- ------------- -------------------- ---------- -------------------- 1 2007947551 LHR11G PRIMARY 2020-07-24 08:10:07 ARCHIVELOG READ WRITE 11.2.0.4.0 187,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 any check 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 to check 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 to set environment variables and SSH software: Linux: LANG=en_US.UTF-8 Run export NLS_LANG="AMERICAN_AMERICA.AL32UTF8" Set the character set of 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 to create files in this directory. ② The final check 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 GRANT SELECT ANY DICTIONARY TO XXX GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO XXX GRANT EXECUTE ON DBMS_SYSTEM TO XXX GRANT SELECT ON MGMT$ALERT_CURRENT TO XXX B. The user must have query permissions on x$bh, otherwise, hot blocks cannot be queried. The script is as follows: CREATE OR REPLACE VIEW BH AS SELECT * FROM SYS.X$BH CREATE OR 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 SELECT COUNT(1) FROM DBA_FREE_SPACE; to check. If it runs slowly, it is recommended to first use the SYS user to 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 are not 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 time to execute.... End of 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 Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real 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 |
结果
其它不截图了，全部内容，大家可以打开 https://dbhealthcheck.dbaup.com/DBhealthcheck_Results_Example/oracle/Oracle_11g_health_check_report_en.html 查看。
