首页 > 数据库 >Oracle日常运维（一线DBA必备技能）（四）-综合巡检

Oracle日常运维（一线DBA必备技能）（四）-综合巡检

时间：2024-09-26 16:52:18浏览次数：10  
标签：1024 NAME 运维 DBA 数据库 -- SQL Oracle SELECT

  1. List item
    在Oracle数据库日常维护中，定期巡检以确保数据库的健康和性能，本文结合我自身经验，总结了些常用并且能够真实反应DB健康状况的项目，并附上相应的巡检SQL语句和脚本，供大家参考。

综合巡检项目（可配置定期自动化任务）：

  1. 检查DB实例状况，保证数据库正常的运行；
  2. 检查数据库的存储空间，确保有足够的空间用于数据增长；
  3. 检查数据文件、日志文件、控制文件、归档日志的完整性和可用性；
  4. 检查数据库的版本和补丁级别；
  5. 检查是否有过期的备份，并且备份的可恢复性；
  6. 检查是否有长时间运行的SQL操作，如果有，它们是否影响性能；
  7. 检查是否有未使用的对象，如未使用的表和索引，以便清理；
  8. 检查是否有性能瓶颈，如由于缺少索引或统计信息过时导致的；
  9. 检查数据库的性能统计，如等待事件和执行计划，以优化性能；
  10. 定期运行健康检查脚本或使用Oracle提供的诊断工具进行全面检查；
  11. 定期rebuild索引；
  12. 定期分析 tables和indexs；
  13. 定期检查服务器系统空间使用情况；
  14. 定期做数据库的恢复测试；
  15. 定期查看alert_SID.log，及时发现并解决数据库出现的问题；

自动化定期巡检脚本

-- 设置SQL*Plus环境
SET ECHO OFF
SET TERMOUT OFF
SET HEADING ON
SET FEEDBACK OFF
SET PAGESIZE 0
SET TIMING OFF
SET TRIMSPOOL ON
-- 1. 检查 DB 实例状况，保证数据库正常的运行
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
-- 2. 检查数据库的存储空间，确保有足够的空间用于数据增长
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS SIZE_GB, SUM(MAXBYTES)/1024/1024/1024 AS MAX_SIZE_GB
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;
-- 3. 检查数据文件、日志文件、控制文件、归档日志的完整性和可用性
-- 数据文件
SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;
-- 日志文件
SELECT GROUP#, STATUS FROM V$LOG;
-- 控制文件
SELECT STATUS FROM V$CONTROLFILE;
-- 归档日志
SELECT DESTINATION, STATUS FROM V$ARCHIVE_DEST_STATUS;
-- 4. 检查数据库的版本和补丁级别
SELECT BANNER FROM V$VERSION;
-- 5. 检查是否有过期的备份，并且备份的可恢复性
-- 查询最近一次备份时间
SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS;
-- 检查备份是否成功
SELECT STATUS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE END_TIME = (SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS);
-- 6. 检查是否有长时间运行的 SQL 操作，如果有，它们是否影响性能
SELECT SQL_ID, SQL_TEXT, ELAPSED_TIME, CPU_TIME
FROM V$SQL
WHERE ELAPSED_TIME > 60 -- 假设 60 秒以上为长时间运行
ORDER BY ELAPSED_TIME DESC;
-- 7. 检查是否有未使用的对象，如未使用的表和索引，以便清理
-- 未使用的表
SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = 'YOUR_SCHEMA_NAME' -- 替换为你的模式名
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_TAB_COL_STATISTICS);
-- 未使用的索引
SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE OWNER = 'YOUR_SCHEMA_NAME' -- 替换为你的模式名
AND INDEX_NAME NOT IN (SELECT INDEX_NAME FROM DBA_IND_COL_USAGE);
-- 8. 检查是否有性能瓶颈，如由于缺少索引或统计信息过时导致的
-- 缺少索引的查询可能导致的高负载 SQL
SELECT SQL_ID, SQL_TEXT, DISK_READS
FROM V$SQL
WHERE DISK_READS > 1000 AND SQL_TEXT NOT LIKE '%INDEX%' -- 假设磁盘读取大于 1000 且 SQL 中不包含 INDEX 可能缺少索引
ORDER BY DISK_READS DESC;
-- 统计信息过时的表
SELECT OWNER, TABLE_NAME
FROM DBA_TABLES
WHERE LAST_ANALYZED IS NULL OR (SYSDATE - LAST_ANALYZED) > 7; -- 假设一周以上未分析可能统计信息过时
-- 9. 检查数据库的性能统计，如等待事件和执行计划，以优化性能
-- 等待事件
SELECT EVENT, TIME_WAITED, WAITS
FROM V$SYSTEM_EVENT
ORDER BY TIME_WAITED DESC;
-- 执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 10. 定期运行健康检查脚本或使用 Oracle 提供的诊断工具进行全面检查
-- 可以使用 Oracle Enterprise Manager 等工具进行全面检查，这里暂不提供脚本。
-- 11. 定期 rebuild 索引
BEGIN
    FOR ind IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'YOUR_SCHEMA_NAME') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX '||ind.INDEX_NAME||' REBUILD';
    END LOOP;
END;
/
-- 12. 定期分析 tables 和 indexs
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS('YOUR_SCHEMA_NAME'); -- 替换为你的模式名
END;
/
-- 13. 定期检查服务器系统空间使用情况
-- 假设数据库服务器为 Linux，可以使用以下脚本来查询磁盘空间使用情况
SELECT df.FILE_SYSTEM_NAME, df.TOTAL_SIZE_MB, df.USED_SIZE_MB, df.FREE_SIZE_MB
FROM (
    SELECT TRIM(df.name) AS FILE_SYSTEM_NAME,
           df.total_bytes/1024/1024 AS TOTAL_SIZE_MB,
           (df.total_bytes - fs.free_bytes)/1024/1024 AS USED_SIZE_MB,
           fs.free_bytes/1024/1024 AS FREE_SIZE_MB
    FROM v$asm_diskgroup df JOIN v$asm_diskgroup_stat fs ON df.group_number = fs.group_number
    WHERE df.name!= 'FRA'
    UNION ALL
    SELECT TRIM(df.name) AS FILE_SYSTEM_NAME,
           df.total_bytes/1024/1024 AS TOTAL_SIZE_MB,
           (df.total_bytes - fs.free_bytes)/1024/1024 AS USED_SIZE_MB,
           fs.free_bytes/1024/1024 AS FREE_SIZE_MB
    FROM v$datafile df JOIN v$filestat fs ON df.file# = fs.file#
    WHERE df.file#!= 0
    UNION ALL
    SELECT TRIM(df.name) AS FILE_SYSTEM_NAME,
           df.total_bytes/1024/1024 AS TOTAL_SIZE_MB,
           (df.total_bytes - fs.free_bytes)/1024/1024 AS USED_SIZE_MB,
           fs.free_bytes/1024/1024 AS FREE_SIZE_MB
    FROM v$tempfile df JOIN v$tempstat fs ON df.file# = fs.file#
    WHERE df.file#!= 0
) df;
-- 14. 定期做数据库的恢复测试
-- 可以使用 RMAN 进行恢复测试，具体脚本根据实际情况而定。
-- 15. 定期查看 alert_SID.log，及时发现并解决数据库出现的问题
-- 可以使用操作系统命令查看告警日志文件， Linux 上：
SELECT SUBSTR(TEXT, INSTR(TEXT, 'ORA-') - 20) AS ERROR_MESSAGE FROM (SELECT TEXT FROM V$DIAG_ALERT_LOG ORDER BY TIMESTAMP DESC) WHERE ROWNUM <= 10;

Oracle运维管理-每天必做巡检项目：

  1. 确认实例的状态是否正常：检查oracle后台进程,数据库是否是open状态，数据文件是否都处在online状态下；
  2. 检查监听是否正常，告警日志alert.log中是否有报错，如果有错误（ORA-）记录到日报，并解决；
  3. 检查文件系统的使用情况，如果文件系统的剩余空间小于20%，删除一些不用的文件；
  4. 检查表空间的剩余空间情况；
  5. 利用外部命令（操作系统命令）或者工具检查，检查数据库的性能是否有异常，如：top、vmstat、iostat；
  6. 检查数据库当日备份的有效性（检查备份日志）；
  7. 工作协同：协助开发人员、其它运维人员，协调第三方厂家，帮助开发人员优化SQL，解决开发人员遇到的数据库方面的问题；

Oracle运维管理-每周必做巡检项目：

  1. 每周生成awr,addm（statspack），检查load_profile,top 5 time events,SQL order by elapsed time等检查数据库性能是否有问题；
  2. 清理和备份一周所产生的Alert日志、跟踪文件、dump文件，清理和备份过期的数据库性能数据，以保证数据库性能监控的连续性和有效性；
  3. 空间使用分析，监测数据量总体增长情况，按表空间分析使用率增长情况 ；

Oracle运维管理日-每月必做巡检项目：

  1. 每月对表和索引进行analyze，消除行迁移，表空间碎片，对数据库进行一次全面的健康性检查；
  2. 每月的性能检查重点关注：数据库主要性能指标、数据库主要等待事件、最消耗内存资源的SQL语句；
  3. 每月需要对备份的数据进行恢复演练以保证备份的有效性；

Oracle运维日常巡检方法-手动（不能一味地依赖自动巡检脚本，也要定期手动巡检，提升自己）

1、数据库实例状态检查
查询实例状态：
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
检查数据库是否可访问：
SELECT 1 FROM DUAL;
2、资源使用情况监测
CPU 利用率：

SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'CPU used by this session';

内存使用：
SGA 大小：

SELECT SUM(BYTES) / 1024 / 1024 / 1024 AS SGA_SIZE_GB FROM V$SGA;

PGA 大小：

SELECT VALUE / 1024 / 1024 / 1024 AS PGA_SIZE_GB FROM V$PGASTAT WHERE NAME = 'total PGA allocated';

内存命中率：

SELECT (1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))) * 100 AS BUFFER_CACHE_HIT_RATIO FROM V$BUFFER_POOL_STATISTICS;

磁盘空间：
数据文件所在磁盘空间：

SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 AS SIZE_GB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

控制文件所在磁盘空间：

SELECT NAME, BLOCK_SIZE * FILE_SIZE_BLKS / 1024 / 1024 / 1024 AS SIZE_GB FROM V$CONTROLFILE;

重做日志文件所在磁盘空间：

SELECT GROUP#, BYTES / 1024 / 1024 / 1024 AS SIZE_GB FROM V$LOG;

I/O 性能：
平均磁盘读写时间：

SELECT NAME, VALUE / 1000 AS AVG_DISK_READ_TIME_MS, VALUE / 1000 AS AVG_DISK_WRITE_TIME_MS FROM V$SYSMETRIC WHERE METRIC_NAME IN ('Average Synchronous Single-Block Read Latency', 'Average Synchronous Single-Block Write Latency');

3、数据库备份检查
备份策略执行情况：
查询最近一次备份时间：

SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS;

检查备份是否成功：

SELECT STATUS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE END_TIME = (SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS);

备份存储管理：
查询备份存储设备空间使用情况（假设使用磁带库，需根据实际情况调整）：
– 查询磁带库中已使用空间和总空间（示例，实际情况需根据磁带库管理系统查询）

SELECT USED_SPACE_GB, TOTAL_SPACE_GB FROM TAPE_LIBRARY_STATUS;

4、数据库安全检查
用户权限管理：
查询用户权限：

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS;

查询用户密码过期时间：

SELECT USERNAME, PASSWORD_EXPIRY_DATE FROM DBA_USERS;

数据库访问控制：
查询数据库连接方式：

SELECT NETWORK_SERVICE_BANNER FROM V$SESSION_CONNECT_INFO WHERE SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);

查询数据库监听状态：

SELECT STATUS FROM V$LISTENER;

数据库审计：
查询审计设置：

SELECT * FROM DBA_AUDIT_PARAMETERS;

查询审计日志：

SELECT * FROM DBA_AUDIT_TRAIL;

5、数据库性能优化
SQL 语句优化：
查询执行时间较长的 SQL 语句：

SELECT SQL_ID, SQL_TEXT, ELAPSED_TIME, CPU_TIME FROM V$SQL ORDER BY ELAPSED_TIME DESC;

查询全表扫描的 SQL 语句：

SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE DISK_READS > 1000 AND OPERATOR = 'TABLE ACCESS FULL';

数据库参数调整：
查询内存参数：

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME IN ('sga_max_size', 'sga_target', 'pga_aggregate_target');

查询连接池大小：

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'processes';

数据库结构优化：
查询表分区信息：

SELECT TABLE_NAME, PARTITIONING_TYPE FROM DBA_PART_TABLES;

查询索引使用情况：

SELECT INDEX_NAME, TABLE_NAME, NUM_ROWS, LEAF_BLOCKS FROM DBA_INDEXES;

6、数据库日志分析
数据库告警日志：
查看告警日志最后若干行行（根据实际情况调整行数）（可参考我之前的文章：《linux下DB及系统日常巡检及故障排查log查看技巧分享（一）》）：

SELECT SUBSTR(TEXT, INSTR(TEXT, 'ORA-') - 20) AS ERROR_MESSAGE FROM (SELECT TEXT FROM V$DIAG_ALERT_LOG ORDER BY TIMESTAMP DESC) WHERE ROWNUM <= 10;

数据库跟踪日志：
查询跟踪日志文件位置（需根据实际情况调整）：

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest';

本篇完。
码字不易，宝贵经验分享不易，请各位支持原创，转载注明出处，多多关注作者，后续不定期分享DB核心知识和排障案例及经验、性能调优等。*

标签：1024,NAME,运维,DBA,数据库,--,SQL,Oracle,SELECT
From： https://blog.csdn.net/qq_45732829/article/details/142486832

相关文章

  • Linux服务器运维管理面板1Panel快速安装及安全配置
    1Panel是一个现代化、开源的Linux服务器运维管理面板，旨在帮助运维人员简化服务器管理任务。它提供了直观的界面和强大的功能，使用户可以通过图形化操作界面对服务器进行管理，减少了对命令行的依赖。1Panel支持多种操作系统，适用于Linux服务器，提供了如网站管理、数据库管......
  • oracle 并行执行
    oracle并行执行所谓并行执行，是指能够将一个大型串行任务(任何DML，一般的DDL)物理的划分为叫多个小的部分，这些较小的部分可以同时得到处理。何时使用并行执行:1、必须有一个非常大的任务2、必须有充足的资源(CPU,I/O,MEMORY)并行查询并行查询允许将一个SQLSELECT语句划分为......
  • redhat7静默安装oracle19c
    文章目录创建用户组：创建安装目录：安装依赖：环境变量配置：上传安装包，赋权并解压编辑安装响应文件：根据自己的路径修改查看参数修改情况：路径修改为自己的路径运行安装监听创建：无需修改响应文件数据库实例创建：查看修改后的参数：运行创建：创建oracle数据库用户创建用户组：group......
  • Oracle的归档操作
    1、查询数据库归档模式两种方式：查询v$database和命令查看。NOARCHIVELOG和NoArchiveMode都是未开启归档SQL>selectname,log_modefromv$database;NAMELOG_MODE---------------------ORCLNOARCHIVELOGSQL>archiveloglist;Databaselogmode......
  • Oracle 19c OCP 认证考试 082 题库(第26题)- 2024年修正版
    【优技教育】Oracle19cOCP082题库(Q26题)-2024年修正版考试科目：1Z0-082考试题量：90通过分数：60%考试时间：150min本文为(CUUG原创)整理并解析，转发请注明出处，禁止抄袭及未经注明出处的转载。原文地址：http://www.cuug.com/index.php?s=/home/article/detail/id/3412.html第......
  • 【运维监控】Prometheus+grafana+blackbox_exporter+alertmanager监控业务接口异常预
    运维监控系列文章入口：【运维监控】系列文章汇总索引文章目录一、prometheus部署二、grafana部署三、blackbox_exporter部署1、下载2、解压3、启动4、验证四、prometheus集成blackbox_exporter1、添加prometheus监控规则2、添加prometheus的预警规则3、添加prometheus......
  • JavaScript 之父联手近万名开发者集体讨伐 Oracle：给 JavaScript 一条活路吧！
    JavaScript之父联手近万名开发者集体讨伐Oracle：给JavaScript一条活路吧！投递人 itwriter 发布于 2024-09-2401:08 评论(6) 有1528人阅读 原文链接 [收藏] « »近日，据外媒消息，JavaScript杰出人士和至少9000名其他相关方签署了一封联名信，再次要求Oracle......
  • 【YashanDB知识库】yashandb执行包含带oracle dblink表的sql时性能差
    本文内容来自YashanDB官网，具体内容请见https://www.yashandb.com/newsinfo/7396959.html?templateId=1718516问题现象yashandb执行带oracledblink表的sql性能差：同样的语句，同样的数据，oracle通过dblink访问远端oracle执行，耗时不到1秒钟：问题的风险及影响yashandb通过dblink访问oracle......
  • 【YashanDB知识库】如何配置jdbc驱动使getDatabaseProductName()返回Oracle
    本文转自YashanDB官网，具体内容请见https://www.yashandb.com/newsinfo/7352676.html?templateId=1718516问题现象某些三方件，例如工作流引擎activiti，暂未适配yashandb，使用中会出现如下异常：问题的风险及影响影响客户业务无法进行。问题影响的版本所有的yashandbjdbc驱动版本。问题......
  • oracle/dm查询分组中最大的一条数据：ROW_NUMBER()
    你可以使用Oracle数据库中的ROW_NUMBER()窗口函数来实现这个需求。该函数能够在每个分组内对行进行编号，然后我们可以选择每个分组中number最大的那一行。以下是一个示例SQL查询，演示如何根据id分组，并获取number最大的对象：SELECTid,number,nameFROM(SELECT......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99