凌晨两点半手机震了。某业务系统夜间批处理跑了一半卡住开发急得跳脚领导盯着大屏。登录服务器一看会话数600多processes参数才设了800监听日志已经刷了几万行临时表空间撑到99%。这种场景我经历过不止一次——大多数时候问题不是突然冒出来的而是我们没在巡检里提前发现趋势。Oracle数据库优化及巡检运维常用这两个词看着朴素实际上涵盖了两个完全不同的能力维度巡检是防守优化是进攻。这篇文章把我这些年做Oracle运维的巡检清单、优化思路、踩坑实录一次性整理出来给刚接手Oracle的运维新人、想建巡检体系的DBA、写自动化巡检脚本的工程师做个参考内容偏实战所有SQL都是我实际用过的。1. 数据库巡检到底在巡什么——先搞清楚目标再动手1.1 巡检的三个维度实例、对象、业务很多新手巡检就是跑一个AWR报告或者随便搜几个脚本往上一贴看到没有红色告警就完事。这样其实等于没巡。我习惯把巡检拆成三个维度实例层、对象层、业务层。实例层管的是数据库进程、内存、告警日志、归档状态这些“地基”类的东西。比如数据库是不是OPEN状态、监听还活着吗、有没有ORA-600或者ORA-01555这种需要立刻处理的内部错误。对象层管的是表空间使用率、段空间碎片、索引状态、数据文件是否自动扩展这些“房子内部结构”的东西。业务层管的则是当前会话在干什么、有没有慢SQL、等待事件集中在哪、连接数是不是逼近上限。这三层是递进关系实例层出问题往往立刻崩对象层出问题一般先恶化再报错业务层出问题通常直接表现为“系统慢”或者“登录不上”。巡检要有节奏感。日常巡检看实例层和关键业务指标我一般每天定时跑一次周巡检多查表空间增长趋势、归档日志产生速度、异常等待事件月巡检才做重活比如统计信息健康度检查、段碎片分析、无效对象清理。这个节奏不是拍脑袋定的而是基于故障爆发的概率——大多数事故都不是当天突然发生的而是经过几天甚至几周的积累才暴露。如果你能提前一周看到表空间使用率每天都在涨2%就不会等到业务报错才去加数据文件。1.2 一套能直接用起来的巡检SQL清单巡检脚本不在于多在于精准。这里列几个我每台Oracle机器上都会放的脚本覆盖了实例、表空间、等待事件、归档四个高频故障点。实例状态是最基本的一条SQL搞定SELECT instance_name, status, database_status, host_name, version, startup_time FROM v$instance;注意看startup_time。我遇到过一台机器运行了300多天没重启结果某个内存结构泄漏导致性能骤降重启之后立刻恢复正常。这个字段帮你掌握实例的“年龄”太老的实例要留心。告警日志的检查本质上是个Linux操作但很重要cd $ORACLE_BASE/diag/rdbms/*/trace tail -100 alert_*.log grep -E ORA-00600|ORA-01555|ORA-07445|ORA-00060 alert_*.logORA-00600是Oracle内部错误ORA-01555是快照太旧ORA-00060是死锁。出现任何一个都要详细排查不能拖。很多运维只看alert日志有没有ORA-错误其实更应该关注的是错误出现前的那些“预兆”比如连续出现“checkpoint not complete”警告说明日志切换太快归档跟不上这往往是空间问题的前奏。表空间使用率这条SQL我用了很多年是把DBA_DATA_FILES和DBA_FREE_SPACE拼起来算的SELECT b.tablespace_name, b.total_mb, b.total_mb - NVL(a.free_mb, 0) AS used_mb, ROUND((b.total_mb - NVL(a.free_mb, 0)) / b.total_mb * 100, 1) AS used_pct FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name() b.tablespace_name ORDER BY used_pct DESC;注意我用了外连接因为有的表空间刚创建还没有任何free_space记录普通内连接会把它漏掉。used_pct超过85%就要开始规划扩容了不要等到95%才处理因为很多业务的临时操作、排序操作会瞬间吃满空间。如果数据文件设置了自动扩展也要确认maxsize我曾经见过数据文件已经扩到32GB上限但业务还在继续写结果报错。会话和等待事件是判断“数据库到底卡在哪”的关键入口SELECT inst_id, event, COUNT(*) AS cnt FROM gv$session WHERE wait_class ! Idle GROUP BY inst_id, event ORDER BY cnt DESC;大量会话集中在buffer busy waits说明存在热点块竞争集中在log file sync说明提交太频繁或者磁盘IO慢集中在enq: TX - row lock contention说明有人在锁行。看到这些等待事件比看CPU利用率更直接。CPU高不代表有问题但如果CPU高且等待事件集中在某个具体事件上那就有了明确的排查方向。1.3 巡检要看“趋势”不是看“现在”很多人巡检就爱看“当前状态”当前表空间还有多少、当前会话几个、当前告警有没有。这种瞬时快照有个致命问题——抓不到正在恶化的指标。举个例子你今天看表空间使用率80%觉得没事。但如果你有历史记录能看到它从周一的60%涨到现在的80%每天涨5个点那下周这个表空间就会满。有没有告警不是巡检的终点指标变化的斜率才是真正需要关注的东西。我习惯每次巡检把关键指标输出成一个带时间戳的文本文件每周汇总一次。哪怕不做图表统计只看数字和上周的对比也能发现苗头。比如归档日志从每天10GB涨到每天30GB可能就是业务量上涨也可能是某个批处理任务出现了循环执行。这种问题不会立刻宕机但会在一周后把归档目录塞满。提示巡检报告里一定要给自己留一句“结论性描述”比如“SYSTEM表空间使用率78%较上周上升15%预计10天后达到90%建议周末扩容”。一个只会贴SQL输出的巡检报告没有任何决策价值。2. SQL性能优化三板斧——执行计划、统计信息、改写2.1 执行计划一切优化的起点优化SQL不先看执行计划等于没看病先抓药。我见过太多人一上来就说“这个SQL慢加个索引吧”——结果一查执行计划发现走的索引其实已经有了问题是CBO压根没用它。拿到执行计划最快的办法是在SQL*Plus里SET AUTOTRACE TRACEONLY EXPLAIN; -- 执行你的SQL SET AUTOTRACE OFF;或者在SQL Developer里选中SQL按F10。我更喜欢这种方式EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_date DATE 2024-01-01; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);读执行计划不要去看那些奇奇怪怪的百分比重点看三件事。第一是操作类型TABLE ACCESS FULL意味着全表扫描对于大表通常是性能杀手INDEX RANGE SCAN是正常的索引范围扫描一般问题不大。第二是Cost和RowsCBO估算的返回行数如果和实际值差几个数量级说明统计信息有问题。第三是谓词信息看看WHERE条件里有没有对列做了函数处理或者隐式类型转换。一个经典案例某订单查询表里有500万行加了索引之后还是慢。看执行计划发现虽然走了索引但是Optimizer选择了INDEX FULL SCAN SORT ORDER BY因为ORDER BY字段的索引顺序跟WHERE条件里的索引不是同一个。最后把复合索引改成(where_clause_col, order_by_col)直接从6秒降到0.3秒。这就是读执行计划的意义——它让你知道Oracle到底是怎么干活的而不是你以为它是怎么干活的。2.2 统计信息过期是隐形杀手CBOCost-Based Optimizer就像手机里的导航软件。导航依赖地图数据地图数据过期了路线规划就会出问题CBO依赖统计信息统计信息过期了执行计划就会选错。这个类比我经常跟开发讲他们一下就懂了。怎么判断统计信息是否过期查一下表的统计信息收集时间SELECT table_name, last_analyzed, num_rows FROM dba_tables WHERE owner APP AND table_name ORDERS;如果一个1000万行的表last_analyzed还是半年前而num_rows显示的只有100万那CBO天天在拿过期的数据做决策。处理方式很直接EXEC DBMS_STATS.GATHER_TABLE_STATS(APP, ORDERS, CASCADE TRUE);注意CASCADETRUE会连同表的索引、列的直方图一起收集比只收集表级统计信息全面得多。对于分区表建议加GRANULARITYAUTO对于超大表如果不想阻塞业务可以用ONLINE或加ESTIMATE_PERCENT10采样。我经历过一次最典型的场景开发反馈“某条SQL上周还跑0.5秒这周突然要3秒”。查了下统计信息收集时间发现收集统计信息的JOB把旧统计信息刷新了新增的直方图让CBO选择了另一个索引。这种问题不是Bug是统计信息更新后执行计划变更导致的。解决方法是SQL Plan Management锁定计划或者调整直方图收集策略。2.3 改写SQL的四个经典场景场景一分页慢。Oracle经典分页写法是三层嵌套SELECT * FROM ( SELECT a.*, ROWNUM rn FROM ( SELECT * FROM orders ORDER BY create_time DESC ) a WHERE ROWNUM 200000 ) WHERE rn 199990;这个写法在查询到第100页的时候Oracle会把前20万行全部排序然后丢掉19.99万行慢是必然的。优化的核心思路是减少排序数据量。如果order by字段有索引让子查询直接走索引扫描如果没有索引可以考虑延迟关联先分页取出主键再关联回原表取出其他列。12c以上还可以用OFFSET FETCH语法但底层逻辑一样地基不打牢语法再新也没用。场景二trunc函数导致索引失效。这个坑非常经典我几乎每隔一阵子就会遇到一次WHERE TRUNC(create_time) TRUNC(SYSDATE) - 1这条SQL的含义是查昨天所有的记录。问题是你在create_time列上用了TRUNC函数CBO无法使用create_time上的普通索引只能全表扫描。正确写法是范围条件WHERE create_time TRUNC(SYSDATE) - 1 AND create_time TRUNC(SYSDATE)范围条件让Oracle在索引上直接定位性能天差地别。我有一次就是这么帮开发改了一条SQL把一个千万级表的全表扫描改成索引范围扫描查询从8秒变成了80毫秒。场景三判断字符串包含。开发常写WHERE remark LIKE %退款%前置通配符的LIKE索引根本用不上除非用全文索引大表上这个写法真的很伤。如果只是判断“包含”换成INSTR写法更高效WHERE INSTR(remark, 退款) 0INSTR的效果在功能上和LIKE %xxx%一致但底层逻辑不同。LIKE前置通配符要逐个字符匹配INSTR是定位函数在某些版本和场景下效率更高。注意如果字段本身就没建索引这两种写法性能差异不大真正的优化坑在于很多开发不知道LIKE前置通配符会让索引失效这个前提。场景四存储过程逐行提交。见过太多存储过程里写这种逻辑FOR r IN (SELECT ... FROM big_table) LOOP INSERT INTO target_table VALUES (...); COMMIT; -- 每次循环都提交 END LOOP;逐行提交会导致三个后果redo log暴涨undo空间无法回收锁的持有时间变长。最直接的改法是把COMMIT移出循环改成每处理1000行或者处理完再提交。我曾经把一个逐行提交的批处理从40分钟优化到5分钟就是单纯把COMMIT挪出来加了个计数器每1000行做一次批次提交。这种优化不需要任何高深技巧收益却非常实在。3. 内存、存储、空间——巡检优化里的“硬骨头”3.1 SGA和PGA不是越大越好很多运维觉得数据库性能不行就加内存把memory_target从8G调到16G结果问题依旧。内存池不是越大越好关键要看命中率和等待事件。查看Buffer Cache命中率的经典SQLSELECT name, value FROM v$sysstat WHERE name IN (physical reads, db block gets, consistent gets);命中率逻辑读-物理读/逻辑读。95%以上算正常如果低于90%说明Buffer Cache偏小或者SQL扫描量太大。注意即使命中率到了99%如果free buffer waits很高说明DBWn进程写磁盘的速度跟不上这个时候加内存也解决不了反而要检查IO子系统。PGA的主要用途是排序、哈希连接和位图操作。查看PGA使用情况SELECT name, value FROM v$pgastat WHERE name IN (aggregate PGA target parameter, total PGA inuse, total PGA allocated);如果total PGA inuse经常超过aggregate PGA target说明PGA不够会触发临时表空间的磁盘排序。Oracle推荐OLTP系统的PGA大约占内存的20%OLAP系统可以到50%。但这些都是参考值一定要结合等待事件看。我记得有一次给客户做优化PGA命中率只有60%一查发现有个报表SQL每次做2GB的排序操作临时表空间都看着往上涨。把workarea_size_policy调成AUTO、加大pga_aggregate_target再改造SQL减少排序这才是完整解法。自动内存管理AMM虽然省事但我个人在日常运维中还是建议设置下限值。比如ALTER SYSTEM SET sga_max_size8G SCOPESPFILE; ALTER SYSTEM SET sga_target6G SCOPEBOTH; ALTER SYSTEM SET pga_aggregate_target2G SCOPEBOTH;设定sga_max_size的目的是防止Oracle在负载变化时把内存撑得太大导致操作系统内存不足尤其对于内存相对紧张的服务器这一步能有效避免数据库和OS抢内存。3.2 表空间高水位线和碎片问题巡检中经常看到一种奇怪的现象表空间明明还有几百MB空闲空间但某张表的INSERT越来越慢。这很可能就是高水位线HWM问题。简单理解高水位线就像一个刻度线标记了段里曾经使用过的最大位置。DELETE删除数据后空间虽然释放了但高水位线不会自动下降Oracle全表扫描时依然会扫描到高水位线的位置所以表很大、数据很少的时候全表扫描还是慢。解决高水位线问题的两种方式-- 方式一收缩 ALTER TABLE app.orders ENABLE ROW MOVEMENT; ALTER TABLE app.orders SHRINK SPACE CASCADE; -- 方式二重建段需要重建索引 ALTER TABLE app.orders MOVE; ALTER INDEX app.idx_orders_01 REBUILD;SHRINK SPACE可以在线执行对业务影响小但需要开启行移动。MOVE操作会锁表适合在停机窗口执行。还要注意MOVE之后表上的索引会失效必须重建。这两个操作做完表扫描的数据量会大幅下降。我处理过一张200GB的表DELETE了一多半数据后还有180GB因为高水位线上去了下不来SHRINK之后降到80GB查询速度快了一倍多。行迁移和行链接也是巡检对象。一张表频繁更新某些行的长度超过数据块可用空间后会把一部分数据挪到别的块产生“行迁移”。查询时要多读一个块IO翻倍。查看方法ANALYZE TABLE app.orders LIST CHAINED ROWS; SELECT * FROM chained_rows WHERE table_name ORDERS;如果chained_rows太多说明PCTFREE参数设置不合理。PCTFREE是数据块里预留的更新空间比例对于经常更新且更新后长度增大的表建议调到15甚至20减少行迁移。3.3 归档日志和临时表空间两个易爆点归档日志目录被塞满导致数据库挂起是生产环境最常发生的事故之一。日志切换正常归档进程跟不上就会报“ARCH hung”或者“ORA-00257: archiver error”。巡检命令很简单SELECT name, round(space_limit/1024/1024/1024,2) AS limit_gb, round(space_used/1024/1024/1024,2) AS used_gb, round(space_used/space_limit*100,1) AS used_pct FROM v$recovery_file_dest;如果使用率超过90%赶紧清理旧归档。清理前一定要确认对应时间的备份存在我一般先查SELECT * FROM v$backup_archivelog_details;另外很多人不知道临时表空间也会成为瓶颈。排序、哈希连接都会用临时表空间临时表空间满了SQL会报ORA-01652。巡检临时表空间大小和当前使用SELECT tablespace_name, current_size, max_size, free_space FROM dba_temp_free_space;如果Temp总是不够除了加tempfile还要从SQL层面看是不是有大量排序操作。找到排序大户SELECT a.sql_id, a.SQL_TEXT, b.blocks FROM v$sql a, v$sort_usage b WHERE a.sql_id b.sql_id;优化排序SQL通常比无脑加Temp空间更治本。比如去掉多余的DISTINCT、把UNION改成UNION ALL、让ORDER BY走索引都能有效减少排序量。4. 连接与监听问题排查实录4.1 sqlplus登录缓慢的典型原因sqlplus登录慢这个问题热词里都单独列了。我遇到过的案例不少于十次最常见的原因有三个DNS反解、监听日志过大、认证方式配置不当。DNS反解是最容易踩的坑。当客户端连接到Oracle服务器时监听器会尝试反向解析客户端IP对应的主机名如果DNS配置有问题或者网络环境里没有DNS这个解析过程会超时导致登录卡顿几十秒。排查方法很简单查看$ORACLE_HOME/network/admin/sqlnet.ora如果没有设置加上这几项NAMES.DIRECTORY_PATH (TNSNAMES) SQLNET.INBOUND_CONNECT_TIMEOUT 5为了禁用监听器的DNS反解可以在listener.ora的监听配置里加上USE_DEDICATED_SERVERON不过更彻底的办法是在sqlnet.ora中显式设置SQLNET.INBOUND_CONNECT_TIMEOUT3监听器在3秒内接受不了连接就直接拒绝避免长时间卡在解析环节。监听日志过大也会拖慢登录。listener.log默认位置在$ORACLE_BASE/diag/tnslsnr/ /listener/trace/listener.log。日志文件长到几百MB甚至1GB监听进程每次写日志到文件末尾都要做大量IO整个监听性能都会受影响。建议在巡检脚本里加一条判断监听日志大小的命令超过100MB就归档到历史目录然后清空当前文件。清理时注意不要直接rm掉listener.log因为监听进程还握着文件句柄应该用重命名的方式mv listener.log listener_$(date %Y%m%d).log4.2 监听服务无法启动监听器启动失败的直接报错一般是“TNS-12541: TNS:no listener”或者“TNS-12560: TNS:protocol adapter error”。定位的时候按下面几步走。先确认端口被占用netstat -lntp | grep 1521如果1521端口已经被其他进程占用了监听自然起不来。我遇到过一台测试服务器上装了多个Oracle一个实例的监听占了1521另一个实例配也是1521结果冲突。解决方法是把其中一个实例改到1522端口或者在listener.ora里指定多个监听端口。再看hosts文件。Oracle监听启动时要解析主机名如果/etc/hosts里把主机名解析到127.0.0.1而实际网卡IP是别的地址监听可能只会监听在127.0.0.1上外部客户端永远连不上。检查一下cat /etc/hosts cat $ORACLE_HOME/network/admin/listener.oralistener.ora里如果写了HOST某个具体主机名这个主机名必须能解析到正确的IP。我做运维这些年因为hosts文件写错导致监听起不来的问题至少遇到过五回。这个文件常常被操作系统运维改动改完就忘了数据库还依赖它。还有一类常见问题是环境变量。Oracle用户的环境变量里ORACLE_HOME和ORACLE_SID没有正确设置或者lsnrctl命令使用的是其他ORACLE_HOME下的监听程序。登录Oracle用户后先确认echo $ORACLE_HOME echo $ORACLE_SID which lsnrctl4.3 会话数打满与进程泄漏数据库连不上先看是不是processes参数到了上限。Oracle默认processes是150生产环境一般会调到1000甚至更多但如果连接池配置没跟上或者应用有连接泄漏会话数很容易被打满。查看当前连接进程数SELECT COUNT(*) FROM v$process; SELECT COUNT(*) FROM v$session; SHOW PARAMETER processes; SHOW PARAMETER sessions;如果v$session的数量接近sessions参数上限立即找出占用会话的应用和机器SELECT machine, program, COUNT(*) FROM v$session GROUP BY machine, program ORDER BY COUNT(*) DESC;通常会发现某个应用服务器的连接数异常高比如本该只保持50个连接的程序实际建立了500个。这是应用层连接池配置或者连接泄漏的问题在数据库层能做的临时处理是kill掉那些空闲会话ALTER SYSTEM KILL SESSION sid,serial# IMMEDIATE;需要注意KILL SESSION只是让Oracle标记会话终止。如果会话卡在某种等待上不能立即释放可能需要从操作系统层kill进程SELECT spid FROM v$process WHERE addr (SELECT paddr FROM v$session WHERE sid xxx); kill -9 spid根本的解决方案是让开发检查连接池的最大连接数设置确保“应用连接池上限 数据库processes参数”并且给连接设置合理的空闲超时时间。我处理过最夸张的一个案例应用连接池配置上限500数据库processes设了800但应用侧因为一个线程安全问题每次请求都新建连接又不释放3小时就把800个进程全占满了最后加了监控脚本超过阈值自动告警才消停。5. 巡检自动化与基线检查落地5.1 用Shell加SQL*Plus写一个巡检脚本手工巡检最大的问题是“今天心情好就跑心情不好就不跑”。自动化巡检脚本可以让你每天定时执行输出结果还能留档。我常用的巡检脚本骨架长这样#!/bin/bash export ORACLE_HOME/u01/app/oracle/product/19.0.0/dbhome_1 export ORACLE_SIDORCL export PATH$ORACLE_HOME/bin:$PATH DATE$(date %Y%m%d_%H%M) OUTFILE/tmp/check_$DATE.txt sqlplus -S / as sysdba EOF set pagesize 0 set feedback off set heading off set linesize 200 spool $OUTFILE SELECT Instance Status FROM dual; SELECT instance_name || | || status || | || startup_time FROM v\$instance; SELECT Tablespace Usage FROM dual; SELECT tablespace_name || | || ROUND(used_pct, 1) FROM ( SELECT b.tablespace_name, ROUND((b.total_mb - NVL(a.free_mb, 0)) / b.total_mb * 100, 1) AS used_pct FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name() b.tablespace_name ) WHERE used_pct 85; SELECT Sessions FROM dual; SELECT COUNT(*) || sessions in total FROM v\$session; SELECT Alert Log Last Errors FROM dual; spool off EOF注意在Shell脚本里Oracle的$要写成$否则会被Shell当成变量解析。spool生成的文件可以直接用cron定时跑0 8 * * * /u01/scripts/db_check.sh /var/log/db_check.log 21跑完之后可以把生成的文件通过邮件发出来或者用Python脚本转成HTML格式这样每天早上打开邮件就能看到所有数据库的健康状态。5.2 等保基线检查里的Oracle关键参数等级保护测评等保里对数据库有一堆检查项Oracle作为商用数据库是重点对象。从技术角度我建议运维至少把以下参数列入自检基线SELECT name, value FROM v$parameter WHERE name IN ( audit_trail, -- 审计开关等保要求开启 password_life_time, -- 密码有效期 failed_login_attempts, -- 连续失败登录次数限制 remote_login_passwordfile, -- 远程密码文件登录限制 resource_limit, -- 资源限制开关 sec_max_failed_login_attempts -- 安全增强型失败登录限制 );等保要求审计必须开启audit_trail设置为DB或OS级别然后启用统一审计ALTER SYSTEM SET audit_trailDB SCOPESPFILE; AUDIT SELECT, INSERT, UPDATE, DELETE ON app.orders BY ACCESS;密码策略方面password_life_time建议设90天failed_login_attempts设5次。12c以上如果设置了SEC_MAX_FAILED_LOGIN_ATTEMPTS还会在连续失败后自动锁定账号。这些参数不要等到测评前再去改平时运维就应该保持这个水位等保测评不过是检查你平时有没有做对。5.3 用Python和Ansible批量跑巡检Oracle官方对Python的支持这些年越来越好oracledb模块已经支持Thin模式不需要安装Oracle客户端就能连库。一个简单的连接代码import oracledb conn oracledb.connect(usersystem, passwordyour_password, dsn192.168.1.10:1521/ORCL) cur conn.cursor() cur.execute(SELECT instance_name, status FROM v$instance) for row in cur.fetchall(): print(row) conn.close()Thin模式对运维来说最大的价值就是不用在巡检机器上装Oracle客户端一台普通CentOS机器装个python和oracledb就能跑巡检脚本。配合Pandas可以做表格输出配合Excel或者HTML模板可以生成漂亮的巡检报告。大批服务器的话Ansible是个好帮手。写一个Playbook批量在目标机器上执行巡检脚本并收集输出- name: Oracle DB health check hosts: oracle_servers gather_facts: no tasks: - name: Run check script shell: /u01/scripts/db_check.sh register: result - name: Fetch result file fetch: src: /tmp/check_{{ ansible_date_time.date }}.txt dest: ./reports/{{ inventory_hostname }}_check.txt flat: yes这种做法特别适合管理几十套数据库的场景。人工一台台登录去跑巡检既费时间又容易漏Ansible批量执行之后生成的report目录就是完整的巡检留档。6. 常见问题速查表——运维排障实战参考把这些年踩过的坑和常见的生产故障整理成一张速查表遇到问题先对照症状再按列出的命令排查效率会高很多。症状可能原因排查命令处理方式登录非常慢卡10秒以上DNS反解故障查看sqlnet.ora、listener.ora配置SQLNET.INBOUND_CONNECT_TIMEOUT检查hosts监听无法启动端口被占用/hosts错误netstat -lntp; cat /etc/hosts改端口或修hosts确认ORACLE_HOME环境变量报ORA-00257归档器错误归档目录空间满查v$recovery_file_dest清理过期归档增大FLASH_RECOVERY_AREA应用报ORA-01652临时表空间不足排序量过大/tempfile太小查dba_temp_free_space、v$sort_usage加tempfile并从SQL层减少排序SQL突然变慢统计信息变化/执行计划改变查last_analyzed、执行计划锁定SQL计划或重新收集统计信息数据库连接数打满应用连接泄漏/参数太小查v$session、processes参数调大processes并推动应用修复连接池ORA-01555快照太旧undo太小或查询时间太长查undo使用率、v$undostat增大undo表空间优化长查询数据删除后表空间不下降高水位线太高查dba_segmentsSHRINK SPACE或者MOVE并重建索引会话杀不掉等待事件导致会话无法终止查v$session、v$process从OS层kill spid导入数据缓慢约束和索引未禁用查看导入日志导入前禁用约束和索引导入后重新enable这张表不是万能的但它覆盖了生产环境80%的常见故障。我每次培训新运维都会把这表打印出来贴在工位上遇到问题先对照一下能省很多乱试的时间。我个人在实际操作中的体会是做Oracle运维最忌讳“一慢就重启”。很多问题重启之后确实能暂时解决但根本原因还埋在里面过几周又会以更严重的姿态冒出来。巡检和优化的本质是把那些“本来早该发现”的问题提前找出来在它们还只是隐患的时候就处理掉。有时候巡检脚本跑出来的结果全是正常的那也是一种成功——说明这套系统还在健康状态你的工作是让它继续健康下去。真遇到疑难杂症记得先把手头的时间戳、日志片段、执行计划截图保存下来这些资料是你后续跟研发、跟Oracle官方支持沟通时最有力的证据。最后一个建议你手里积累的巡检脚本和优化案例一定要整理成自己的知识库哪怕只是几个带注释的SQL文本半年之后回头看价值远超你现在的想象。 Oracle数据库巡检与优化实战：从巡检清单到SQL优化
凌晨两点半手机震了。某业务系统夜间批处理跑了一半卡住开发急得跳脚领导盯着大屏。登录服务器一看会话数600多processes参数才设了800监听日志已经刷了几万行临时表空间撑到99%。这种场景我经历过不止一次——大多数时候问题不是突然冒出来的而是我们没在巡检里提前发现趋势。Oracle数据库优化及巡检运维常用这两个词看着朴素实际上涵盖了两个完全不同的能力维度巡检是防守优化是进攻。这篇文章把我这些年做Oracle运维的巡检清单、优化思路、踩坑实录一次性整理出来给刚接手Oracle的运维新人、想建巡检体系的DBA、写自动化巡检脚本的工程师做个参考内容偏实战所有SQL都是我实际用过的。1. 数据库巡检到底在巡什么——先搞清楚目标再动手1.1 巡检的三个维度实例、对象、业务很多新手巡检就是跑一个AWR报告或者随便搜几个脚本往上一贴看到没有红色告警就完事。这样其实等于没巡。我习惯把巡检拆成三个维度实例层、对象层、业务层。实例层管的是数据库进程、内存、告警日志、归档状态这些“地基”类的东西。比如数据库是不是OPEN状态、监听还活着吗、有没有ORA-600或者ORA-01555这种需要立刻处理的内部错误。对象层管的是表空间使用率、段空间碎片、索引状态、数据文件是否自动扩展这些“房子内部结构”的东西。业务层管的则是当前会话在干什么、有没有慢SQL、等待事件集中在哪、连接数是不是逼近上限。这三层是递进关系实例层出问题往往立刻崩对象层出问题一般先恶化再报错业务层出问题通常直接表现为“系统慢”或者“登录不上”。巡检要有节奏感。日常巡检看实例层和关键业务指标我一般每天定时跑一次周巡检多查表空间增长趋势、归档日志产生速度、异常等待事件月巡检才做重活比如统计信息健康度检查、段碎片分析、无效对象清理。这个节奏不是拍脑袋定的而是基于故障爆发的概率——大多数事故都不是当天突然发生的而是经过几天甚至几周的积累才暴露。如果你能提前一周看到表空间使用率每天都在涨2%就不会等到业务报错才去加数据文件。1.2 一套能直接用起来的巡检SQL清单巡检脚本不在于多在于精准。这里列几个我每台Oracle机器上都会放的脚本覆盖了实例、表空间、等待事件、归档四个高频故障点。实例状态是最基本的一条SQL搞定SELECT instance_name, status, database_status, host_name, version, startup_time FROM v$instance;注意看startup_time。我遇到过一台机器运行了300多天没重启结果某个内存结构泄漏导致性能骤降重启之后立刻恢复正常。这个字段帮你掌握实例的“年龄”太老的实例要留心。告警日志的检查本质上是个Linux操作但很重要cd $ORACLE_BASE/diag/rdbms/*/trace tail -100 alert_*.log grep -E ORA-00600|ORA-01555|ORA-07445|ORA-00060 alert_*.logORA-00600是Oracle内部错误ORA-01555是快照太旧ORA-00060是死锁。出现任何一个都要详细排查不能拖。很多运维只看alert日志有没有ORA-错误其实更应该关注的是错误出现前的那些“预兆”比如连续出现“checkpoint not complete”警告说明日志切换太快归档跟不上这往往是空间问题的前奏。表空间使用率这条SQL我用了很多年是把DBA_DATA_FILES和DBA_FREE_SPACE拼起来算的SELECT b.tablespace_name, b.total_mb, b.total_mb - NVL(a.free_mb, 0) AS used_mb, ROUND((b.total_mb - NVL(a.free_mb, 0)) / b.total_mb * 100, 1) AS used_pct FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name() b.tablespace_name ORDER BY used_pct DESC;注意我用了外连接因为有的表空间刚创建还没有任何free_space记录普通内连接会把它漏掉。used_pct超过85%就要开始规划扩容了不要等到95%才处理因为很多业务的临时操作、排序操作会瞬间吃满空间。如果数据文件设置了自动扩展也要确认maxsize我曾经见过数据文件已经扩到32GB上限但业务还在继续写结果报错。会话和等待事件是判断“数据库到底卡在哪”的关键入口SELECT inst_id, event, COUNT(*) AS cnt FROM gv$session WHERE wait_class ! Idle GROUP BY inst_id, event ORDER BY cnt DESC;大量会话集中在buffer busy waits说明存在热点块竞争集中在log file sync说明提交太频繁或者磁盘IO慢集中在enq: TX - row lock contention说明有人在锁行。看到这些等待事件比看CPU利用率更直接。CPU高不代表有问题但如果CPU高且等待事件集中在某个具体事件上那就有了明确的排查方向。1.3 巡检要看“趋势”不是看“现在”很多人巡检就爱看“当前状态”当前表空间还有多少、当前会话几个、当前告警有没有。这种瞬时快照有个致命问题——抓不到正在恶化的指标。举个例子你今天看表空间使用率80%觉得没事。但如果你有历史记录能看到它从周一的60%涨到现在的80%每天涨5个点那下周这个表空间就会满。有没有告警不是巡检的终点指标变化的斜率才是真正需要关注的东西。我习惯每次巡检把关键指标输出成一个带时间戳的文本文件每周汇总一次。哪怕不做图表统计只看数字和上周的对比也能发现苗头。比如归档日志从每天10GB涨到每天30GB可能就是业务量上涨也可能是某个批处理任务出现了循环执行。这种问题不会立刻宕机但会在一周后把归档目录塞满。提示巡检报告里一定要给自己留一句“结论性描述”比如“SYSTEM表空间使用率78%较上周上升15%预计10天后达到90%建议周末扩容”。一个只会贴SQL输出的巡检报告没有任何决策价值。2. SQL性能优化三板斧——执行计划、统计信息、改写2.1 执行计划一切优化的起点优化SQL不先看执行计划等于没看病先抓药。我见过太多人一上来就说“这个SQL慢加个索引吧”——结果一查执行计划发现走的索引其实已经有了问题是CBO压根没用它。拿到执行计划最快的办法是在SQL*Plus里SET AUTOTRACE TRACEONLY EXPLAIN; -- 执行你的SQL SET AUTOTRACE OFF;或者在SQL Developer里选中SQL按F10。我更喜欢这种方式EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_date DATE 2024-01-01; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);读执行计划不要去看那些奇奇怪怪的百分比重点看三件事。第一是操作类型TABLE ACCESS FULL意味着全表扫描对于大表通常是性能杀手INDEX RANGE SCAN是正常的索引范围扫描一般问题不大。第二是Cost和RowsCBO估算的返回行数如果和实际值差几个数量级说明统计信息有问题。第三是谓词信息看看WHERE条件里有没有对列做了函数处理或者隐式类型转换。一个经典案例某订单查询表里有500万行加了索引之后还是慢。看执行计划发现虽然走了索引但是Optimizer选择了INDEX FULL SCAN SORT ORDER BY因为ORDER BY字段的索引顺序跟WHERE条件里的索引不是同一个。最后把复合索引改成(where_clause_col, order_by_col)直接从6秒降到0.3秒。这就是读执行计划的意义——它让你知道Oracle到底是怎么干活的而不是你以为它是怎么干活的。2.2 统计信息过期是隐形杀手CBOCost-Based Optimizer就像手机里的导航软件。导航依赖地图数据地图数据过期了路线规划就会出问题CBO依赖统计信息统计信息过期了执行计划就会选错。这个类比我经常跟开发讲他们一下就懂了。怎么判断统计信息是否过期查一下表的统计信息收集时间SELECT table_name, last_analyzed, num_rows FROM dba_tables WHERE owner APP AND table_name ORDERS;如果一个1000万行的表last_analyzed还是半年前而num_rows显示的只有100万那CBO天天在拿过期的数据做决策。处理方式很直接EXEC DBMS_STATS.GATHER_TABLE_STATS(APP, ORDERS, CASCADE TRUE);注意CASCADETRUE会连同表的索引、列的直方图一起收集比只收集表级统计信息全面得多。对于分区表建议加GRANULARITYAUTO对于超大表如果不想阻塞业务可以用ONLINE或加ESTIMATE_PERCENT10采样。我经历过一次最典型的场景开发反馈“某条SQL上周还跑0.5秒这周突然要3秒”。查了下统计信息收集时间发现收集统计信息的JOB把旧统计信息刷新了新增的直方图让CBO选择了另一个索引。这种问题不是Bug是统计信息更新后执行计划变更导致的。解决方法是SQL Plan Management锁定计划或者调整直方图收集策略。2.3 改写SQL的四个经典场景场景一分页慢。Oracle经典分页写法是三层嵌套SELECT * FROM ( SELECT a.*, ROWNUM rn FROM ( SELECT * FROM orders ORDER BY create_time DESC ) a WHERE ROWNUM 200000 ) WHERE rn 199990;这个写法在查询到第100页的时候Oracle会把前20万行全部排序然后丢掉19.99万行慢是必然的。优化的核心思路是减少排序数据量。如果order by字段有索引让子查询直接走索引扫描如果没有索引可以考虑延迟关联先分页取出主键再关联回原表取出其他列。12c以上还可以用OFFSET FETCH语法但底层逻辑一样地基不打牢语法再新也没用。场景二trunc函数导致索引失效。这个坑非常经典我几乎每隔一阵子就会遇到一次WHERE TRUNC(create_time) TRUNC(SYSDATE) - 1这条SQL的含义是查昨天所有的记录。问题是你在create_time列上用了TRUNC函数CBO无法使用create_time上的普通索引只能全表扫描。正确写法是范围条件WHERE create_time TRUNC(SYSDATE) - 1 AND create_time TRUNC(SYSDATE)范围条件让Oracle在索引上直接定位性能天差地别。我有一次就是这么帮开发改了一条SQL把一个千万级表的全表扫描改成索引范围扫描查询从8秒变成了80毫秒。场景三判断字符串包含。开发常写WHERE remark LIKE %退款%前置通配符的LIKE索引根本用不上除非用全文索引大表上这个写法真的很伤。如果只是判断“包含”换成INSTR写法更高效WHERE INSTR(remark, 退款) 0INSTR的效果在功能上和LIKE %xxx%一致但底层逻辑不同。LIKE前置通配符要逐个字符匹配INSTR是定位函数在某些版本和场景下效率更高。注意如果字段本身就没建索引这两种写法性能差异不大真正的优化坑在于很多开发不知道LIKE前置通配符会让索引失效这个前提。场景四存储过程逐行提交。见过太多存储过程里写这种逻辑FOR r IN (SELECT ... FROM big_table) LOOP INSERT INTO target_table VALUES (...); COMMIT; -- 每次循环都提交 END LOOP;逐行提交会导致三个后果redo log暴涨undo空间无法回收锁的持有时间变长。最直接的改法是把COMMIT移出循环改成每处理1000行或者处理完再提交。我曾经把一个逐行提交的批处理从40分钟优化到5分钟就是单纯把COMMIT挪出来加了个计数器每1000行做一次批次提交。这种优化不需要任何高深技巧收益却非常实在。3. 内存、存储、空间——巡检优化里的“硬骨头”3.1 SGA和PGA不是越大越好很多运维觉得数据库性能不行就加内存把memory_target从8G调到16G结果问题依旧。内存池不是越大越好关键要看命中率和等待事件。查看Buffer Cache命中率的经典SQLSELECT name, value FROM v$sysstat WHERE name IN (physical reads, db block gets, consistent gets);命中率逻辑读-物理读/逻辑读。95%以上算正常如果低于90%说明Buffer Cache偏小或者SQL扫描量太大。注意即使命中率到了99%如果free buffer waits很高说明DBWn进程写磁盘的速度跟不上这个时候加内存也解决不了反而要检查IO子系统。PGA的主要用途是排序、哈希连接和位图操作。查看PGA使用情况SELECT name, value FROM v$pgastat WHERE name IN (aggregate PGA target parameter, total PGA inuse, total PGA allocated);如果total PGA inuse经常超过aggregate PGA target说明PGA不够会触发临时表空间的磁盘排序。Oracle推荐OLTP系统的PGA大约占内存的20%OLAP系统可以到50%。但这些都是参考值一定要结合等待事件看。我记得有一次给客户做优化PGA命中率只有60%一查发现有个报表SQL每次做2GB的排序操作临时表空间都看着往上涨。把workarea_size_policy调成AUTO、加大pga_aggregate_target再改造SQL减少排序这才是完整解法。自动内存管理AMM虽然省事但我个人在日常运维中还是建议设置下限值。比如ALTER SYSTEM SET sga_max_size8G SCOPESPFILE; ALTER SYSTEM SET sga_target6G SCOPEBOTH; ALTER SYSTEM SET pga_aggregate_target2G SCOPEBOTH;设定sga_max_size的目的是防止Oracle在负载变化时把内存撑得太大导致操作系统内存不足尤其对于内存相对紧张的服务器这一步能有效避免数据库和OS抢内存。3.2 表空间高水位线和碎片问题巡检中经常看到一种奇怪的现象表空间明明还有几百MB空闲空间但某张表的INSERT越来越慢。这很可能就是高水位线HWM问题。简单理解高水位线就像一个刻度线标记了段里曾经使用过的最大位置。DELETE删除数据后空间虽然释放了但高水位线不会自动下降Oracle全表扫描时依然会扫描到高水位线的位置所以表很大、数据很少的时候全表扫描还是慢。解决高水位线问题的两种方式-- 方式一收缩 ALTER TABLE app.orders ENABLE ROW MOVEMENT; ALTER TABLE app.orders SHRINK SPACE CASCADE; -- 方式二重建段需要重建索引 ALTER TABLE app.orders MOVE; ALTER INDEX app.idx_orders_01 REBUILD;SHRINK SPACE可以在线执行对业务影响小但需要开启行移动。MOVE操作会锁表适合在停机窗口执行。还要注意MOVE之后表上的索引会失效必须重建。这两个操作做完表扫描的数据量会大幅下降。我处理过一张200GB的表DELETE了一多半数据后还有180GB因为高水位线上去了下不来SHRINK之后降到80GB查询速度快了一倍多。行迁移和行链接也是巡检对象。一张表频繁更新某些行的长度超过数据块可用空间后会把一部分数据挪到别的块产生“行迁移”。查询时要多读一个块IO翻倍。查看方法ANALYZE TABLE app.orders LIST CHAINED ROWS; SELECT * FROM chained_rows WHERE table_name ORDERS;如果chained_rows太多说明PCTFREE参数设置不合理。PCTFREE是数据块里预留的更新空间比例对于经常更新且更新后长度增大的表建议调到15甚至20减少行迁移。3.3 归档日志和临时表空间两个易爆点归档日志目录被塞满导致数据库挂起是生产环境最常发生的事故之一。日志切换正常归档进程跟不上就会报“ARCH hung”或者“ORA-00257: archiver error”。巡检命令很简单SELECT name, round(space_limit/1024/1024/1024,2) AS limit_gb, round(space_used/1024/1024/1024,2) AS used_gb, round(space_used/space_limit*100,1) AS used_pct FROM v$recovery_file_dest;如果使用率超过90%赶紧清理旧归档。清理前一定要确认对应时间的备份存在我一般先查SELECT * FROM v$backup_archivelog_details;另外很多人不知道临时表空间也会成为瓶颈。排序、哈希连接都会用临时表空间临时表空间满了SQL会报ORA-01652。巡检临时表空间大小和当前使用SELECT tablespace_name, current_size, max_size, free_space FROM dba_temp_free_space;如果Temp总是不够除了加tempfile还要从SQL层面看是不是有大量排序操作。找到排序大户SELECT a.sql_id, a.SQL_TEXT, b.blocks FROM v$sql a, v$sort_usage b WHERE a.sql_id b.sql_id;优化排序SQL通常比无脑加Temp空间更治本。比如去掉多余的DISTINCT、把UNION改成UNION ALL、让ORDER BY走索引都能有效减少排序量。4. 连接与监听问题排查实录4.1 sqlplus登录缓慢的典型原因sqlplus登录慢这个问题热词里都单独列了。我遇到过的案例不少于十次最常见的原因有三个DNS反解、监听日志过大、认证方式配置不当。DNS反解是最容易踩的坑。当客户端连接到Oracle服务器时监听器会尝试反向解析客户端IP对应的主机名如果DNS配置有问题或者网络环境里没有DNS这个解析过程会超时导致登录卡顿几十秒。排查方法很简单查看$ORACLE_HOME/network/admin/sqlnet.ora如果没有设置加上这几项NAMES.DIRECTORY_PATH (TNSNAMES) SQLNET.INBOUND_CONNECT_TIMEOUT 5为了禁用监听器的DNS反解可以在listener.ora的监听配置里加上USE_DEDICATED_SERVERON不过更彻底的办法是在sqlnet.ora中显式设置SQLNET.INBOUND_CONNECT_TIMEOUT3监听器在3秒内接受不了连接就直接拒绝避免长时间卡在解析环节。监听日志过大也会拖慢登录。listener.log默认位置在$ORACLE_BASE/diag/tnslsnr/ /listener/trace/listener.log。日志文件长到几百MB甚至1GB监听进程每次写日志到文件末尾都要做大量IO整个监听性能都会受影响。建议在巡检脚本里加一条判断监听日志大小的命令超过100MB就归档到历史目录然后清空当前文件。清理时注意不要直接rm掉listener.log因为监听进程还握着文件句柄应该用重命名的方式mv listener.log listener_$(date %Y%m%d).log4.2 监听服务无法启动监听器启动失败的直接报错一般是“TNS-12541: TNS:no listener”或者“TNS-12560: TNS:protocol adapter error”。定位的时候按下面几步走。先确认端口被占用netstat -lntp | grep 1521如果1521端口已经被其他进程占用了监听自然起不来。我遇到过一台测试服务器上装了多个Oracle一个实例的监听占了1521另一个实例配也是1521结果冲突。解决方法是把其中一个实例改到1522端口或者在listener.ora里指定多个监听端口。再看hosts文件。Oracle监听启动时要解析主机名如果/etc/hosts里把主机名解析到127.0.0.1而实际网卡IP是别的地址监听可能只会监听在127.0.0.1上外部客户端永远连不上。检查一下cat /etc/hosts cat $ORACLE_HOME/network/admin/listener.oralistener.ora里如果写了HOST某个具体主机名这个主机名必须能解析到正确的IP。我做运维这些年因为hosts文件写错导致监听起不来的问题至少遇到过五回。这个文件常常被操作系统运维改动改完就忘了数据库还依赖它。还有一类常见问题是环境变量。Oracle用户的环境变量里ORACLE_HOME和ORACLE_SID没有正确设置或者lsnrctl命令使用的是其他ORACLE_HOME下的监听程序。登录Oracle用户后先确认echo $ORACLE_HOME echo $ORACLE_SID which lsnrctl4.3 会话数打满与进程泄漏数据库连不上先看是不是processes参数到了上限。Oracle默认processes是150生产环境一般会调到1000甚至更多但如果连接池配置没跟上或者应用有连接泄漏会话数很容易被打满。查看当前连接进程数SELECT COUNT(*) FROM v$process; SELECT COUNT(*) FROM v$session; SHOW PARAMETER processes; SHOW PARAMETER sessions;如果v$session的数量接近sessions参数上限立即找出占用会话的应用和机器SELECT machine, program, COUNT(*) FROM v$session GROUP BY machine, program ORDER BY COUNT(*) DESC;通常会发现某个应用服务器的连接数异常高比如本该只保持50个连接的程序实际建立了500个。这是应用层连接池配置或者连接泄漏的问题在数据库层能做的临时处理是kill掉那些空闲会话ALTER SYSTEM KILL SESSION sid,serial# IMMEDIATE;需要注意KILL SESSION只是让Oracle标记会话终止。如果会话卡在某种等待上不能立即释放可能需要从操作系统层kill进程SELECT spid FROM v$process WHERE addr (SELECT paddr FROM v$session WHERE sid xxx); kill -9 spid根本的解决方案是让开发检查连接池的最大连接数设置确保“应用连接池上限 数据库processes参数”并且给连接设置合理的空闲超时时间。我处理过最夸张的一个案例应用连接池配置上限500数据库processes设了800但应用侧因为一个线程安全问题每次请求都新建连接又不释放3小时就把800个进程全占满了最后加了监控脚本超过阈值自动告警才消停。5. 巡检自动化与基线检查落地5.1 用Shell加SQL*Plus写一个巡检脚本手工巡检最大的问题是“今天心情好就跑心情不好就不跑”。自动化巡检脚本可以让你每天定时执行输出结果还能留档。我常用的巡检脚本骨架长这样#!/bin/bash export ORACLE_HOME/u01/app/oracle/product/19.0.0/dbhome_1 export ORACLE_SIDORCL export PATH$ORACLE_HOME/bin:$PATH DATE$(date %Y%m%d_%H%M) OUTFILE/tmp/check_$DATE.txt sqlplus -S / as sysdba EOF set pagesize 0 set feedback off set heading off set linesize 200 spool $OUTFILE SELECT Instance Status FROM dual; SELECT instance_name || | || status || | || startup_time FROM v\$instance; SELECT Tablespace Usage FROM dual; SELECT tablespace_name || | || ROUND(used_pct, 1) FROM ( SELECT b.tablespace_name, ROUND((b.total_mb - NVL(a.free_mb, 0)) / b.total_mb * 100, 1) AS used_pct FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name() b.tablespace_name ) WHERE used_pct 85; SELECT Sessions FROM dual; SELECT COUNT(*) || sessions in total FROM v\$session; SELECT Alert Log Last Errors FROM dual; spool off EOF注意在Shell脚本里Oracle的$要写成$否则会被Shell当成变量解析。spool生成的文件可以直接用cron定时跑0 8 * * * /u01/scripts/db_check.sh /var/log/db_check.log 21跑完之后可以把生成的文件通过邮件发出来或者用Python脚本转成HTML格式这样每天早上打开邮件就能看到所有数据库的健康状态。5.2 等保基线检查里的Oracle关键参数等级保护测评等保里对数据库有一堆检查项Oracle作为商用数据库是重点对象。从技术角度我建议运维至少把以下参数列入自检基线SELECT name, value FROM v$parameter WHERE name IN ( audit_trail, -- 审计开关等保要求开启 password_life_time, -- 密码有效期 failed_login_attempts, -- 连续失败登录次数限制 remote_login_passwordfile, -- 远程密码文件登录限制 resource_limit, -- 资源限制开关 sec_max_failed_login_attempts -- 安全增强型失败登录限制 );等保要求审计必须开启audit_trail设置为DB或OS级别然后启用统一审计ALTER SYSTEM SET audit_trailDB SCOPESPFILE; AUDIT SELECT, INSERT, UPDATE, DELETE ON app.orders BY ACCESS;密码策略方面password_life_time建议设90天failed_login_attempts设5次。12c以上如果设置了SEC_MAX_FAILED_LOGIN_ATTEMPTS还会在连续失败后自动锁定账号。这些参数不要等到测评前再去改平时运维就应该保持这个水位等保测评不过是检查你平时有没有做对。5.3 用Python和Ansible批量跑巡检Oracle官方对Python的支持这些年越来越好oracledb模块已经支持Thin模式不需要安装Oracle客户端就能连库。一个简单的连接代码import oracledb conn oracledb.connect(usersystem, passwordyour_password, dsn192.168.1.10:1521/ORCL) cur conn.cursor() cur.execute(SELECT instance_name, status FROM v$instance) for row in cur.fetchall(): print(row) conn.close()Thin模式对运维来说最大的价值就是不用在巡检机器上装Oracle客户端一台普通CentOS机器装个python和oracledb就能跑巡检脚本。配合Pandas可以做表格输出配合Excel或者HTML模板可以生成漂亮的巡检报告。大批服务器的话Ansible是个好帮手。写一个Playbook批量在目标机器上执行巡检脚本并收集输出- name: Oracle DB health check hosts: oracle_servers gather_facts: no tasks: - name: Run check script shell: /u01/scripts/db_check.sh register: result - name: Fetch result file fetch: src: /tmp/check_{{ ansible_date_time.date }}.txt dest: ./reports/{{ inventory_hostname }}_check.txt flat: yes这种做法特别适合管理几十套数据库的场景。人工一台台登录去跑巡检既费时间又容易漏Ansible批量执行之后生成的report目录就是完整的巡检留档。6. 常见问题速查表——运维排障实战参考把这些年踩过的坑和常见的生产故障整理成一张速查表遇到问题先对照症状再按列出的命令排查效率会高很多。症状可能原因排查命令处理方式登录非常慢卡10秒以上DNS反解故障查看sqlnet.ora、listener.ora配置SQLNET.INBOUND_CONNECT_TIMEOUT检查hosts监听无法启动端口被占用/hosts错误netstat -lntp; cat /etc/hosts改端口或修hosts确认ORACLE_HOME环境变量报ORA-00257归档器错误归档目录空间满查v$recovery_file_dest清理过期归档增大FLASH_RECOVERY_AREA应用报ORA-01652临时表空间不足排序量过大/tempfile太小查dba_temp_free_space、v$sort_usage加tempfile并从SQL层减少排序SQL突然变慢统计信息变化/执行计划改变查last_analyzed、执行计划锁定SQL计划或重新收集统计信息数据库连接数打满应用连接泄漏/参数太小查v$session、processes参数调大processes并推动应用修复连接池ORA-01555快照太旧undo太小或查询时间太长查undo使用率、v$undostat增大undo表空间优化长查询数据删除后表空间不下降高水位线太高查dba_segmentsSHRINK SPACE或者MOVE并重建索引会话杀不掉等待事件导致会话无法终止查v$session、v$process从OS层kill spid导入数据缓慢约束和索引未禁用查看导入日志导入前禁用约束和索引导入后重新enable这张表不是万能的但它覆盖了生产环境80%的常见故障。我每次培训新运维都会把这表打印出来贴在工位上遇到问题先对照一下能省很多乱试的时间。我个人在实际操作中的体会是做Oracle运维最忌讳“一慢就重启”。很多问题重启之后确实能暂时解决但根本原因还埋在里面过几周又会以更严重的姿态冒出来。巡检和优化的本质是把那些“本来早该发现”的问题提前找出来在它们还只是隐患的时候就处理掉。有时候巡检脚本跑出来的结果全是正常的那也是一种成功——说明这套系统还在健康状态你的工作是让它继续健康下去。真遇到疑难杂症记得先把手头的时间戳、日志片段、执行计划截图保存下来这些资料是你后续跟研发、跟Oracle官方支持沟通时最有力的证据。最后一个建议你手里积累的巡检脚本和优化案例一定要整理成自己的知识库哪怕只是几个带注释的SQL文本半年之后回头看价值远超你现在的想象。