Part1查询控制文件信息
查询控制文件路径和大小
select v.*,round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) from v$controlfile v;
STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS ROUND(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2)
------- --------------------------------------------------- ------------------------ ----------- -------------- -------------------------------------------
+DATA/dev1/controlfile/current.256.1136586383 NO 16384 1466 22.91
登录asmcmd查看控制文件大小
以防万一复制出来操作
ASMCMD> cp Current.256.1136586383 /tmp
copying +DATA/DEV1/CONTROLFILE/Current.256.1136586383 -> /tmp/Current.256.1136586383
查看文件大小
du -sh /tmp/Current.256.1136586383
23M /tmp/Current.256.1136586383
#######################################################################################
或者直接在ASMCMD里操作
ASMCMD> ls -s
Block_Size Blocks Bytes Space Name
16384 1467 24035328 25165824 Current.256.1136586383
Bytes是控制文件大小 22.92M
查看控制文件内部构造（前五个）
SELECT
type AS "区域类型",
records_total AS "预分配记录数",
record_size AS "单条记录大小(字节)",
-- 核心计算：区域总大小 = 记录数 × 每条大小
ROUND((records_total * record_size) / 1024 /1024, 2) AS "总大小(MB)"
FROM v$controlfile_record_section
ORDER BY (records_total * record_size) DESC;
区域类型 预分配记录数 单条记录大小(字节) 总大小(MB)
---------------------------- ----------------- -------------------------- ------------
ARCHIVED LOG 4480 584 2.5
FILENAME 4674 524 2.34
PROXY COPY 1004 928 .89
DATAFILE COPY 1000 736 .7
BACKUP PIECE 1000 736 .7
数据库内部构造中英文对比
ARCHIVED LOG 归档日志
FILENAME 文件名
PROXY COPY 代理副本
DATAFILE COPY 数据文件副本
BACKUP PIECE 备份片
DATABASE BLOCK CORRUPTION 数据库块损坏
FOREIGN ARCHIVED LOG 外部归档日志
DATAFILE 数据文件
RESTORE POINT 恢复点
GUARANTEED RESTORE POINT 保证恢复点
CKPT PROGRESS 检查点进度
BACKUP DATAFILE 备份数据文件
OFFLINE RANGE 脱机范围
FLASHBACK LOG 闪回日志
DELETED OBJECT 已删除对象
TABLESPACE 表空间
TEMPORARY FILENAME 临时文件名
RMAN CONFIGURATION RMAN 配置
BACKUP SET 备份集
COPY CORRUPTION 副本损坏
BACKUP CORRUPTION 备份损坏
BACKUP REDOLOG 备份重做日志
DATAFILE HISTORY 数据文件历史
REMOVABLE RECOVERY FILES 可移动恢复文件
INSTANCE SPACE RESERVATION 实例空间预留
RMAN STATUS RMAN 状态
DATABASE INCARNATION 数据库版本
LOG HISTORY 日志历史
BACKUP SPFILE 备份 SPFILE
REDO LOG 重做日志
STANDBY DATABASE MATRIX 备用数据库矩阵
REDO THREAD 重做线程
ACM OPERATION ACM 操作
MTTR 平均故障恢复时间
THREAD INSTANCE NAME MAPPING 线程实例名称映射
DATABASE 数据库
RECOVERY DESTINATION 恢复目标
我百度了一下网上的信息，大多数都是因为闪回和归档太大导致的控制文件大小剧增
查询现存的数据库归档，deleted表示归档是否删除，name一定要指定对，因为可能会有ADG，所以只需要看本地实际存在的就行。
查询现存的数据库归档，deleted表示归档是否删除，name一定要指定对，因为可能会有ADG，所以只需要看本地实际存在的就行。
SELECT
COUNT(1) AS "现存归档条数",
ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024,2) AS "总大小(MB)",
MIN(FIRST_TIME) AS "最早归档时间",
MAX(COMPLETION_TIME) AS "最新归档时间"
FROM v$archived_log
WHERE
name IS NOT NULL -- 确保是有效归档
AND deleted = 'NO' -- 未被删除
AND standby_dest = 'NO' -- 排除备用库归档;
现存归档条数 总大小(MB) 最早归档时间 最新归档时间
---------- -------------- ------------ ------------
2330 361791.38 05-MAY-25 17-AUG-25
Part2尝试缩减控制文件
2.1、清理归档
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-1/24';
SELECT
COUNT(1) AS "现存归档条数",
ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024,2) AS "总大小(MB)",
MIN(FIRST_TIME) AS "最早归档时间",
MAX(COMPLETION_TIME) AS "最新归档时间"
FROM v$archived_log
WHERE
name IS NOT NULL -- 确保是有效归档
AND deleted = 'NO' -- 未被删除
AND standby_dest = 'NO' -- 排除备用库归档;
空值代表清理完毕
刷新检查点
网上说这两个方法都行，我就都执行了
alter system checkpoint;
ALTER SYSTEM ARCHIVE LOG CURRENT;
查询控制文件大小没变化
select v.*,round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) from v$controlfile v;
2.2、MOS推荐操作
按照mos清理控制文件归档信息
su - oracle
sqlplus / as sysdba
execute sys.dbms_backup_restore.resetCfileSection( 11);
su - oracle
rman target /
catalog start with '+ARCH';
输入yes确定，注册成功
刷新检查点
网上说这两个方法都行，我就都执行了
alter system checkpoint;
ALTER SYSTEM ARCHIVE LOG CURRENT;
查询控制文件大小
select v.*,round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) from v$controlfile v;
控制文件大小还是没变化
2.3、重建控制文件（危险）
备份控制文件
SQL> alter database backup controlfile to trace as '/tmp/ora.trace';
查询控制文件路径
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/ojndev1/controlfile/current.256.1209483215
删除控制文件
su - grid
asmcmd
cd +DATA/ojndev1/controlfile/
rm -f current.256.1209483215
重启数据库
su - grid
##其中dev6指的是数据库实例
srvctl start database -d dev6
SQL> startup
ORACLE instance started.
Total System Global Area 4977278976 bytes
Fixed Size 2261768 bytes
Variable Size 1006636280 bytes
Database Buffers 3959422976 bytes
Redo Buffers 8957952 bytes
ORA-00205: error in identifying control file, check alert log for more info
启动数据库报错了
后台alert报警日志有更具体的信息
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/dev1/controlfile/current.256.1136586383'
ORA-17503: ksfdopn:2 Failed to open file +DATA/dev1/controlfile/current.256.1136586383
ORA-15012: ASM file '+DATA/dev1/controlfile/current.256.1136586383' does not exist
如果是集群，需要先关闭集群参数
alter system set cluster_database=FALSE scope=spfile sid='*';
shutdown immediate
startup nomount;
##集群需要关闭数据库参数，否则会报错 重建过程出错： ORA-01503: CREATE CONTROLFILE failed ORA-12720: operation requires database is in EXCLUSIVE mode
打开控制文件备份，摘取所需信息
cat /tmp/ora.trace
注意两点，一个是 -- 都是注释，可以去掉，第二个是控制文件里的创建信息都有重复的，选取一份就行
##启动到nomount状态
STARTUP NOMOUNT
##创建控制文件里的基础信息
CREATE CONTROLFILE REUSE DATABASE "DEV1" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/DEV1/onlinelog/group_1.257.1136586383' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/DEV1/onlinelog/group_2.258.1136586387' SIZE 50M BLOCKSIZE 512,
GROUP 3 '+DATA/DEV1/onlinelog/group_3.265.1136588301' SIZE 50M BLOCKSIZE 512,
GROUP 4 '+DATA/DEV1/onlinelog/group_4.266.1136588305' SIZE 50M BLOCKSIZE 512,
GROUP 5 '+REDO1/DEV1/onlinelog/group_5.256.1137165825' SIZE 512M BLOCKSIZE 512,
GROUP 6 '+REDO1/DEV1/onlinelog/group_6.257.1137165845' SIZE 512M BLOCKSIZE 512,
GROUP 7 '+REDO1/DEV1/onlinelog/group_7.258.1137165869' SIZE 512M BLOCKSIZE 512,
GROUP 8 '+REDO1/DEV1/onlinelog/group_8.259.1137165895' SIZE 512M BLOCKSIZE 512,
GROUP 9 '+REDO1/DEV1/onlinelog/group_9.260.1137165919' SIZE 512M BLOCKSIZE 512,
GROUP 10 '+REDO1/DEV1/onlinelog/group_10.261.1137165945' SIZE 512M BLOCKSIZE 512,
GROUP 11 '+REDO1/DEV1/onlinelog/group_11.262.1137165967' SIZE 512M BLOCKSIZE 512,
GROUP 12 '+REDO1/DEV1/onlinelog/group_12.263.1137165991' SIZE 512M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/DEV1/datafile/system.259.1136586391',
'+DATA/DEV1/datafile/sysaux.260.1136586395',
'+DATA/DEV1/datafile/undotbs1.261.1136586401',
'+DATA/DEV1/datafile/undotbs2.263.1136586411',
'+DATA/DEV1/datafile/users.264.1136586413',
'+DATA/DEV1/datafile/data216.279.1143817619',
'+DATA/DEV1/datafile/data118.269.1146082531',
'+DATA/DEV1/datafile/data201.270.1140513811',
'+DATA/DEV1/datafile/data202.271.1140514565',
'+DATA/DEV1/datafile/data203.272.1140515325',
'+DATA/DEV1/datafile/data204.273.1140516139',
'+DATA/DEV1/datafile/data205.274.1140517355',
'+DATA/DEV1/datafile/data206.275.1140518125',
'+DATA/DEV1/datafile/data207.276.1140518967',
'+DATA/DEV1/datafile/data117.268.1146082829',
'+DATA/DEV1/datafile/data208.284.1140686939',
'+DATA/DEV1/datafile/data209.285.1140687429',
'+DATA/DEV1/datafile/data209.286.1140688513',
'+DATA/DEV1/datafile/data210.287.1140688719',
'+DATA/DEV1/datafile/data211.288.1140690141',
'+DATA/DEV1/datafile/data211.289.1140690991',
'+DATA/DEV1/datafile/data212.290.1140692383',
'+DATA/DEV1/datafile/data212.291.1140693221',
'+DATA/DEV1/datafile/data213.292.1140693931',
'+DATA/DEV1/datafile/data213.293.1140695049',
'+DATA/DEV1/datafile/data214.294.1140695737',
'+DATA/DEV1/datafile/data214.295.1140696435',
'+DATA/DEV1/datafile/data.296.1140697115',
'+DATA/DEV1/datafile/ram.297.1140697417',
'+DATA/DEV1/datafile/data209.298.1140698281',
'+DATA/DEV1/datafile/data215.299.1140777177',
'+DATA/DEV1/datafile/le.300.1140814863',
'+DATA/DEV1/datafile/ta.301.1141232877',
'+DATA/DEV1/datafile/data117.302.1146082981',
'+DATA/DEV1/datafile/data119.303.1146083149',
'+DATA/DEV1/datafile/data119.304.1146083319',
'+DATA/DEV1/datafile/data101.305.1146172563',
'+DATA/DEV1/datafile/data101.306.1146172873',
'+DATA/DEV1/datafile/data112.307.1146216447',
'+DATA/DEV1/datafile/data114.308.1146216763',
'+DATA/DEV1/datafile/data121.309.1146217091',
'+DATA/DEV1/datafile/data121.310.1146217501',
'+DATA/DEV1/datafile/data120.311.1146218151',
'+DATA/DEV1/datafile/data120.312.1146218465',
'+DATA/DEV1/datafile/data.313.1146396267',
'+DATA/DEV1/datafile/bis.314.1146398503',
'+DATA/DEV1/datafile/data101.315.1157060861',
'+DATA/DEV1/datafile/data102.316.1157061107',
'+DATA/DEV1/datafile/data103.317.1157061489',
'+DATA/DEV1/datafile/data121.318.1157064139',
'+DATA/DEV1/datafile/data217.319.1164661325',
'+DATA/DEV1/datafile/data1.320.1166440571',
'+DATA/DEV1/datafile/data218.321.1180952741',
'+DATA/DEV1/datafile/data218.322.1180953329',
'+DATA/DEV1/datafile/data219.323.1180954095',
'+DATA/DEV1/datafile/data220.324.1180994957',
'+DATA/DEV1/datafile/data209.325.1181209855',
'+DATA/DEV1/datafile/ggtbs.326.1190308823'
CHARACTER SET ZHS16GBK
;
##查看是否需要恢复库（我这里不需要）
RECOVER DATABASE
##强制数据库立即归档当前所有尚未归档的在线重做日志文件 (Online Redo Log Files)。
ALTER SYSTEM ARCHIVE LOG ALL;
##启动数据库
ALTER DATABASE OPEN;
但是报错没有临时表空间
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/dev1/tempfile/temp.262.1136586401' SIZE 32767M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE CWTEMP027 ADD TEMPFILE '+DATA/dev1/tempfile/temp027.277.1140538611' SIZE 5120M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE CWTEMP206 ADD TEMPFILE '+DATA/dev1/tempfile/temp206.278.1140538613' SIZE 5120M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE CWTEMP202 ADD TEMPFILE '+DATA/dev1/tempfile/temp202.280.1140538615' SIZE 5120M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE CWTEMP205 ADD TEMPFILE '+DATA/dev1/tempfile/temp205.281.1140538617' SIZE 5120M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE CWTEMP203 ADD TEMPFILE '+DATA/dev1/tempfile/temp203.282.1140538619' SIZE 5120M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE CWTEMP201 ADD TEMPFILE '+DATA/dev1/tempfile/temp201.283.1140538671' SIZE 5120M REUSE AUTOEXTEND OFF;
检查控制文件信息发现已经自动更新了
SQL> show parameter control_files
NAME TYPE VALUE
--------------- ----------- ------------------------------
control_files string +DATA/ojndev1/controlfile/current.256.1209483215
重启数据库
##修改回集群参数
su - oracle
sqlplus / as sysdba
alter system set cluster_database=True scope=spfile sid='*';
##重启数据库集群
su - grid
srvctl start database -d dev6
查看控制文件构造和大小
SELECT
type AS "区域类型",
records_total AS "预分配记录数",
record_size AS "单条记录大小(字节)",
-- 核心计算：区域总大小 = 记录数 × 每条大小
ROUND((records_total * record_size) / 1024 /1024, 2) AS "总大小(MB)"
FROM v$controlfile_record_section
ORDER BY (records_total * record_size) DESC;
区域类型 预分配记录数 单条记录大小(字节) 总大小(MB)
---------------------------- ----------------- -------------------------- ------------
FILENAME 4674 524 2.34
PROXY COPY 1004 928 .89
DATAFILE COPY 1000 736 .7
BACKUP PIECE 1000 736 .7
DATABASE BLOCK CORRUPTION 8384 80 .64
FOREIGN ARCHIVED LOG 1002 604 .58
DATAFILE 1024 520 .51
RESTORE POINT 2083 212 .42
GUARANTEED RESTORE POINT 2048 212 .41
CKPT PROGRESS 35 8180 .27
OFFLINE RANGE 1063 200 .2
BACKUP DATAFILE 1063 200 .2
ARCHIVED LOG 308 584 .17
数据库层面检查总大小
select v.*,round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) from v$controlfile v;
STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS ROUND(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2)
------- --------------------------------------------------- ------------------------ ----------- -------------- -------------------------------------------
+DATA/ojndev1/controlfile/current.256.1136586383 NO 16384 1466 17.94
服务器层面查看
ASMCMD> ls -s Current.256.1209483215
Block_Size Blocks Bytes Space Name
16384 1149 18825216 25165824 Current.256.1209483215
可以确定控制文件的确缩小了
从22.92M重建以后缩小到17.94M
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
