开发转兼职DBA五从救火到防火——参数、内存、监控、备份前面两篇都是数据库起不来了硬恢复。这篇讲怎么从出事再救转向提前预防——参数配置、内存结构、性能监控、备份策略。文章目录开发转兼职DBA五从救火到防火——参数、内存、监控、备份转变一、Oracle的内存结构SGA——所有进程共享的内存PGA——每个进程独占的内存哪些参数控制这些内存常见的内存问题二、关键参数配置游标相关的参数进程和会话数undo相关日志相关三、性能监控等待事件——判断瓶颈在哪AWR报告——全面体检找到慢SQL锁阻塞四、备份策略RMAN备份建议的备份策略验证备份能恢复expdp/impdp——逻辑备份五、日常运维清单从救火到防火转变两次数据库起不来的事故之后我意识到一个事实每次都是出了事才学每次都是在生产环境上赌命。开发者面对数据库的态度通常有三个阶段能用就行——SQL写对了就行不管性能出事再救——查询慢了看执行计划数据库挂了硬恢复提前预防——配好参数、建好监控、做好备份这篇讲的是从阶段二到阶段三的转变。一、Oracle的内存结构调参数之前得先知道数据库的内存是怎么组织的。Oracle的内存分两大块SGASystem Global Area和PGAProgram Global Area。SGA——所有进程共享的内存SGA ├── Database Buffer Cache数据缓冲区 │ └── 存数据块的副本减少磁盘读取 ├── Redo Log Buffer日志缓冲区 │ └── 存redo记录提交时刷到redo log文件 ├── Shared Pool共享池 │ ├── Library Cache库缓存——存SQL的解析结果和执行计划 │ └── Data Dictionary Cache数据字典缓存——存表结构、索引信息 ├── Large Pool大池可选 │ └── RMAN备份、并行查询等大块操作 └── Java PoolJava池可选 └── JVM相关PGA——每个进程独占的内存PGA ├── SQL工作区排序、哈希连接用的内存 ├── 会话信息 └── 游标状态哪些参数控制这些内存Oracle 10g以后可以用一个参数自动管理大部分内存ALTERSYSTEMSETmemory_target4G SCOPESPFILE;Oracle自动在SGA和PGA之间分配。简单但不够精细。手动管理模式——更可控-- SGA大小ALTERSYSTEMSETsga_target3G SCOPESPFILE;-- PGA大小ALTERSYSTEMSETpga_aggregate_target1G SCOPESPFILE;再细一点-- 共享池SQL解析、执行计划缓存ALTERSYSTEMSETshared_pool_size512M SCOPESPFILE;-- 数据缓冲区数据块缓存ALTERSYSTEMSETdb_cache_size1G SCOPESPFILE;-- 日志缓冲区ALTERSYSTEMSETlog_buffer16M SCOPESPFILE;常见的内存问题1. 共享池太小症状SQL执行慢但不是查询本身慢——是每次都要重新解析SQL。SELECTsql_text,executions,parse_callsFROMv$sqlareaWHEREparse_callsexecutions;如果parse_calls接近executions说明SQL几乎每次都在重新解析。可能的原因共享池太小缓存的执行计划被挤掉了SQL没有用绑定变量每次都是硬解析2. 数据缓冲区太小症状磁盘读取频繁。SELECTname,valueFROMv$sysstatWHEREnameIN(db block gets from cache,consistent gets from cache,physical reads);计算命中率命中率 1 - (physical reads / (db block gets from cache consistent gets from cache))命中率低于90%考虑增大数据缓冲区。3. 排序溢出到磁盘症状排序操作慢。SELECTname,valueFROMv$sysstatWHEREnameIN(sorts (memory),sorts (disk));如果sorts (disk)不为0说明排序在内存里放不下溢出到临时表空间了。增大PGA或pga_aggregate_target。二、关键参数配置除了内存参数还有几个影响数据库行为的参数。游标相关的参数-- 每个会话能打开的游标数ALTERSYSTEMSETopen_cursors300SCOPESPFILE;-- 会话缓存游标的数量软解析用ALTERSYSTEMSETsession_cached_cursors100SCOPESPFILE;游标数太小会报ORA-01000: maximum open cursors exceeded。但不要设太大——每个游标都占共享池内存。进程和会话数-- 最大进程数ALTERSYSTEMSETprocesses500SCOPESPFILE;-- 最大会话数通常比processes大10%~20%ALTERSYSTEMSETsessions555SCOPESPFILE;政务系统并发用户多、连接池大默认值150经常不够。undo相关-- undo保留时间秒ALTERSYSTEMSETundo_retention900SCOPESPFILE;undo_retention900意味着Oracle尝试保留undo数据至少15分钟。用于闪回查询Flashback Query和一致性读。设置太短长查询可能遇到ORA-01555: snapshot too old。日志相关-- 日志切换的间隔秒-- 如果日志切换太频繁几分钟一次考虑增大日志文件大小ALTERSYSTEMSETarchive_lag_target1800SCOPESPFILE;日志文件大小在建库时设定不能动态改。一般建议日志切换间隔15~30分钟。三、性能监控等待事件——判断瓶颈在哪SELECTevent,total_waits,time_waitedFROMv$system_eventWHEREwait_class!IdleORDERBYtime_waitedDESC;常见的等待事件等待事件含义可能的原因db file sequential read单块读等待索引访问大量数据可能索引选择不当db file scattered read多块读等待全表扫描log file sync日志同步等待提交太频繁buffer busy waits缓冲区忙等待热块竞争enq: TX - row lock contention行锁等待事务冲突latch: shared pool共享池锁存器竞争硬解析太多db file sequential read多→ 检查索引是否合理是不是走了不该走的索引。db file scattered read多→ 检查是否有不该全表扫描的查询。log file sync多→ 检查是否有频繁提交的循环逻辑考虑批量提交。enq: TX - row lock contention多→ 检查是否有多个事务同时改同一行。AWR报告——全面体检Oracle的AWRAutomatic Workload Repository每隔一段时间自动采集数据库状态快照。生成报告-- 查看快照列表SELECTsnap_id,begin_interval_time,end_interval_timeFROMdba_hist_snapshotORDERBYsnap_idDESC;-- 生成报告在SQL*Plus中执行?/rdbms/admin/awrrpt.sqlAWR报告很长重点看几个部分Top 10 Foreground Events——数据库时间花在哪了SQL ordered by Elapsed Time——最慢的SQLSQL ordered by Gets——消耗最多逻辑读的SQLSegment by Physical Reads——读取最多的表/索引Tablespace I/O——表空间的I/O情况找到慢SQLSELECTsql_id,sql_text,elapsed_time/1000000ASelapsed_sec,executions,elapsed_time/executions/1000000ASavg_secFROMv$sqlWHEREexecutions0ORDERBYelapsed_timeDESCFETCHFIRST20ROWSONLY;找到慢SQL后拿sql_id查执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id_here));锁阻塞用户说操作卡住了先查锁SELECTsid,serial#, blocking_session, wait_class, event, seconds_in_waitFROMv$sessionWHEREblocking_sessionISNOTNULL;blocking_session字段告诉你谁在阻塞谁。找到阻塞源头SELECTsid,serial#, sql_textFROMv$sessionsJOINv$sqlqONs.sql_idq.sql_idWHEREs.sidblocking_session;必要时杀掉阻塞的会话ALTERSYSTEMKILLSESSIONsid,serial#IMMEDIATE;四、备份策略前面两次事故都是因为没有备份才那么狼狈。恢复成功是运气不是能力。RMAN备份Oracle的标准备份工具是RMANRecovery Manager。全库备份rman target / RMANBACKUP DATABASE PLUS ARCHIVELOG;增量备份只备份变化的数据块RMANBACKUP INCREMENTAL LEVEL0DATABASE;-- 基础备份 RMANBACKUP INCREMENTAL LEVEL1DATABASE;-- 增量备份建议的备份策略每天凌晨增量备份LEVEL 1 每周日凌晨全量备份LEVEL 0 每小时归档日志备份保留策略RMANCONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF7DAYS;保留7天的恢复窗口。验证备份能恢复备份不做恢复测试等于没备份。RMANRESTORE DATABASE VALIDATE;RMANRESTORE ARCHIVELOG ALL VALIDATE;VALIDATE只验证备份文件是否完整可读不做实际恢复。定期在测试环境做真实的恢复演练——恢复到指定时间点RMANRUN{SET UNTIL TIMETO_DATE(2024-01-15 14:00:00, YYYY-MM-DD HH24:MI:SS);RESTORE DATABASE;RECOVER DATABASE;ALTER DATABASE OPEN RESETLOGS;}expdp/impdp——逻辑备份除了RMAN的物理备份还有逻辑备份导出数据expdp scott/tigerDIRECTORYdp_dirDUMPFILEkc22_%U.dmpTABLESkc22PARALLEL4逻辑备份不能做媒体恢复磁盘坏了不能用它恢复但适合迁移数据只恢复个别表跨版本导出五、日常运维清单把以上内容整合成日常要做的事频率做什么怎么做每天检查告警日志adrci或直接读alert_SID.log每天检查表空间使用率SELECT * FROM dba_tablespace_usage_metrics;每天检查备份是否成功SELECT * FROM v$rman_status;每周查看AWR报告?/rdbms/admin/awrrpt.sql每周查看慢SQLv$sql按elapsed_time排序每月收集统计信息DBMS_STATS.GATHER_DATABASE_STATS每月检查无效对象SELECT * FROM dba_objects WHERE statusINVALID;每季度恢复演练测试环境做RMAN恢复从救火到防火前面四篇前两篇是性能问题执行计划、索引后两篇是可靠性问题redo损坏、undo损坏。都是出了事才处理。这篇讲的——参数配置、内存结构、性能监控、备份策略——是让事尽量不出。但现实中防火做得再好也要准备救火的工具。因为硬盘会坏内存会出错人会写错SQL电力会断下一篇讲这些原理在不同数据库里的对应——从Oracle到MySQL到PostgreSQL底层逻辑是不是同一套。标签#DBA #Oracle #参数配置 #内存结构 #SGA #PGA #性能监控 #AWR #RMAN #备份策略
