一、 SYSTEM和SYSAUX版本演进与功能增强
1.1 Oracle 8i/9i：系统表空间的初期架构
- 单一系统表空间：在Oracle 8i和9i版本中，数据库仅有一个核心系统表空间——SYSTEM。这个表空间承担了所有系统管理功能，包括：
- 数据字典存储
- 系统回滚段管理
- 高级复制元数据
- 其他系统对象
这种集中式架构导致SYSTEM表空间面临空间压力和碎片问题，特别是当使用高级复制功能时，复制队列表可能增长到数十GB，严重影响系统性能。
- 管理挑战：此时期常见的管理问题包括：
- 空间异常增长：高级复制的队列表（如DEF$_LOB）无限制扩展
- 性能下降：频繁的字典操作导致行级锁争用
- 碎片化严重：反复创建和删除对象导致空间碎片这些问题促使Oracle重新设计系统表空间架构。
表：Oracle各版本系统表空间架构演进
|
版本 |
架构特点 |
关键改进 |
遗留问题 |
|
8i/9i |
单一SYSTEM表空间 |
- |
空间压力、碎片问题 |
|
10g |
SYSTEM+SYSAUX双表空间 |
引入SYSAUX分担负载 |
组件迁移不彻底 |
|
11g |
增强SYSAUX管理 |
AWR自动清理、统计信息管理 |
SM/OPTSTAT空间回收 |
|
12c+ |
云环境优化 |
多租户支持、热迁移 |
CDB/PDB特殊管理 |
3.2 Oracle 10g：SYSAUX表空间的引入
- 架构革新：Oracle 10g是系统表空间架构的里程碑版本，引入了SYSAUX辅助表空间。这一设计将非核心组件从SYSTEM表空间中迁移出来，包括：
- Enterprise Manager（OEM）存储库
- LogMiner工作区
- Oracle Streams元数据
- Oracle Spatial组件
- Statspack（性能诊断工具）存储库
- 组件化迁移机制：Oracle 10g提供标准化的迁移接口，通过DBMS包实现组件表空间的灵活迁移。例如，迁移LogMiner组件的命令为：
EXEC SYS.DBMS_LOGMNR_D.SET_TABLESPACE('USERS');
迁移后，通过V$SYSAUX_OCCUPANTS视图可验证空间释放：
SELECT occupant_name, space_usage_kbytes
FROM v$sysaux_occupants
WHERE occupant_name='LOGMNR';
--
OCCUPANT_NAME SPACE_USAGE_KBYTES
________________ _____________________
LOGMNR 11712
- 管理优势：SYSAUX表空间的引入带来显著优势：
- 降低SYSTEM表空间负载：减少约40%-60%的空间使用
- 避免空间碎片：组件独立存储减少碎片
- 增强可用性：SYSAUX可脱机维护而不影响核心功能但10g版本仍存在组件空间回收不彻底的问题（如AWR数据无限增长）。
1.3 Oracle 11g：管理功能增强
- AWR自动管理：Oracle 11g增强了AWR空间自动管理能力，通过MMON后台进程自动清理过期快照。DBA可配置保留策略：
-- 设置AWR保留时间为7天
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 10080);
--默认保留期从10g的7天延长至8天，适应更大规模性能数据分析
- 统计信息历史管理：11g强化了优化器统计信息历史（SM/OPTSTAT）管理：
- 自动保留历史统计信息（默认31天）
- 提供DBMS_STATS包管理历史数据：
-- 清理30天前的统计信息
EXEC DBMS_STATS.PURGE_STATS(SYSDATE-30);
--PL/SQL procedure successfully completed.
这一机制提高了执行计划稳定性，但需注意历史数据可能占用大量SYSAUX空间。
- 审计功能改进：11g默认开启数据库审计，审计数据存储在SYSTEM表空间的AUD$表中。为避免空间问题：
-- 关闭审计功能
ALTER SYSTEM SET audit_trail=none SCOPE=spfile;
--System altered.
--或迁移审计表：
ALTER TABLE sys.aud$ MOVE TABLESPACE users;
--Table SYS.AUD$ altered.
--这些改进提高了系统表空间的可管理性。
1.4 Oracle 12c~19c~23ai 及更高版本：云环境优化
- 多租户架构支持：Oracle 12开始 引入多租户架构，系统表空间管理发生重大变化：
- 容器数据库（CDB）：拥有独立的SYSTEM/SYSAUX表空间，存储公共元数据
- 可插拔数据库（PDB）：每个PDB拥有私有的SYSTEM/SYSAUX表空间这种架构使单个CDB可托管数百个PDB，显著提高资源利用率。
- 热迁移与在线重组：12c增强表空间在线维护能力：
- 热迁移组件：在不中断服务的情况下移动组件表空间
- 在线表重组：对SYSAUX中的大表（如WRH$_ACTIVE_SESSION_HISTORY）进行分区级MOVE操作：
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION partition_P1;
这些功能提高了系统表空间在云环境中的可维护性。
- 空间效率优化：更高版本持续优化系统表空间的空间效率：
- 列式存储：AWR数据采用混合列压缩（HCC）
- 智能默认值：自动优化初始化参数减少空间开销
- 内存外部化：部分统计信息存储在内存而非磁盘这些优化使系统表空间在同等功能下减少约20%的空间占用。
二、管理脚本与运维实践
2.1 表空间监测与诊断脚本
- 空间使用分析：使用以下脚本获取表空间使用概况：
SELECT df.tablespace_name "表空间",
ROUND(df.bytes/1048576) "大小(MB)",
ROUND((df.bytes - fs.bytes)/1048576) "已用(MB)",
ROUND(fs.bytes/1048576) "空闲(MB)",
ROUND((df.bytes - fs.bytes)/df.bytes * 100, 2) "使用率(%)"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name IN ('SYSTEM','SYSAUX');
--
表空间 大小(MB) 已用(MB) 空闲(MB) 使用率(%)
_________ _________ _________ _________ _________
SYSAUX 1730 1617 113 93.45
SYSTEM 1110 1110 0 99.99
- SYSAUX组件分析：识别SYSAUX空间占用最多的组件：
--此脚本输出包括迁移方法（move_procedure），为空间优化提供直接路径。
SELECT occupant_name, occupant_desc,
ROUND(space_usage_kbytes/1024) "空间(MB)",
move_procedure
FROM v$sysaux_occupants
ORDER BY space_usage_kbytes DESC;
--
OCCUPANT_NAME OCCUPANT_DESC 空间(MB) MOVE_PROCEDURE
___________________________ _______________________________________________________ _________ ___________________________________________
SM/AWR Server Manageability - Automatic Workload Repository 809
SM/OPTSTAT Server Manageability - Optimizer Statistics History 146
SDO Oracle Spatial 128 MDSYS.MOVE_SDO
…………
- 对象级空间分析：针对特定表空间进行对象级分析：
--此脚本帮助定位空间消耗大的具体对象。
SELECT segment_name, segment_type,
ROUND(SUM(bytes)/1024/1024, 2) "大小(MB)"
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
GROUP BY segment_name, segment_type
HAVING SUM(bytes) > 10485760 -- >10MB
ORDER BY SUM(bytes) DESC;
--SEGMENT_NAME SEGMENT_TYPE 大小(MB)
_________________________________ __________________ _________
SYS_LOB0000014176C00038$$ LOB PARTITION 70.25
WRH$_SYSSTAT_PK INDEX PARTITION 70.06
WRH$_SYSSTAT TABLE PARTITION 49.06
WRH$_LATCH TABLE PARTITION 48.06
WRH$_LATCH_PK INDEX PARTITION 45.06
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 44
SYS_LOB0000063524C00006$$ LOBSEGMENT 40.25
WRI$_OPTSTAT_HISTGRM_HISTORY TABLE PARTITION 37.63
SYS_LOB0000063424C00006$$ LOBSEGMENT 37.31
WRH$_SYSMETRIC_HISTORY_INDEX INDEX PARTITION 37.06
SYS_LOB0000022597C00030$$ LOB PARTITION 31.5
WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION 30.06
WRH$_SYSMETRIC_HISTORY TABLE PARTITION 30.06
WRH$_SQL_PLAN TABLE PARTITION 25.06
SYS_LOB0000062553C00004$$ LOBSEGMENT 24.25
WRH$_PARAMETER_PK INDEX PARTITION 23.06
I_WRI$_OPTSTAT_H_ST INDEX 22
WRH$_EVENT_HISTOGRAM TABLE PARTITION 21.5
WRH$_CON_SYSSTAT_PK INDEX PARTITION 19.94
SYS_LOB0000014152C00004$$ LOB PARTITION 16.25
WRH$_PARAMETER TABLE PARTITION 15.63
WRH$_PROCESS_WAITTIME_PK INDEX PARTITION 12.88
AUD$UNIFIED TABLE PARTITION 12.69
WRH$_SQLSTAT TABLE PARTITION 12.5
WRH$_CON_SYSSTAT TABLE PARTITION 12.19
WRH$_SQL_PLAN_PK INDEX PARTITION 11.44
WRH$_CON_SYSMETRIC_SUMMARY TABLE PARTITION 11.13
2.2 空间回收与优化脚本
- AWR快照管理：控制AWR空间占用的关键脚本：
-- 调整AWR设置 -- 保留7天（7 * 24 * 60分钟）
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 60, -- 60分钟采集一次
retention => 10080);
-- 删除历史快照，123456789，dbid需要提前获取
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id => 1000,
high_snap_id => 2000,
dbid => 123456789);
END;
/
- 统计信息历史清理：管理优化器统计信息历史：
-- 设置保留策略（默认31天）
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(15);
-- 清理历史统计
BEGIN
FOR i IN REVERSE 15..31 LOOP
DBMS_STATS.PURGE_STATS(SYSDATE - i);
END LOOP;
END;
/
--对WRI$_OPTSTAT*系列表执行定期清理可有效释放空间。
- 分区维护操作：对SYSAUX中的大表进行分区级维护：
-- 移动分区表并更新索引
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY
MOVE PARTITION SYS_P1 TABLESPACE sysaux
UPDATE GLOBAL INDEXES;
ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK
REBUILD PARTITION SYS_P1;
--此操作需在维护窗口进行，避免性能影响。
- USERS表空间
- 用户隔离机制：创建用户时指定默认表空间，避免使用SYSTEM：
CREATE USER TEST_USER IDENTIFIED BY Oracle_4U
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
- 空间管理优化：本地管理表空间（LMT）使用位图跟踪区分配，减少数据字典竞争。
2.3 最佳运维实践
- 空间监控体系：建立三层监控体系防范空间风险：
- 实时预警：设置表空间使用率>80%的告警阈值
- 每日巡检：检查AWR/统计信息保留策略
- 月度分析：使用DBMS_SPACE_ADVISOR分析碎片情况
组件迁移策略：对非核心组件实施分层存储：
-- 迁移LogMiner到普通表空间
EXEC SYS.DBMS_LOGMNR_D.SET_TABLESPACE('USER_DATA');
-- 迁移审计表
ALTER TABLE sys.aud$ MOVE TABLESPACE audit_data;
--迁移后需验证组件功能完整性。
- 初始化参数优化：关键参数配置建议：
-- 审计设置
ALTER SYSTEM SET audit_trail=db SCOPE=spfile;
-- 修改参数文件（spfile）中的值
ALTER SYSTEM SET recyclebin=off SCOPE=spfile;
-- 重启数据库使生效
SHUTDOWN IMMEDIATE;
STARTUP;
-- 检查recyclebin状态
SHOW PARAMETER recyclebin;
--
NAME TYPE VALUE
---------- ------ -----
recyclebin string off
-- AWR设置
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 30, retention => 4320);
- 备份恢复策略：系统表空间需特殊备份策略：
- RMAN增量备份：每日增量+每周全备
- 控制文件多路复用：至少3份拷贝
- 快速恢复区：确保足够空间存放备份，恢复演练应每季度执行，验证恢复流程有效性
扫一扫
2545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
