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查看控制文件大小
以防萬一復(fù)制出來操作
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
查看控制文件內(nèi)部構(gòu)造（前五個）
SELECT
type AS "區(qū)域類型",
records_total AS "預(yù)分配記錄數(shù)",
record_size AS "單條記錄大小(字節(jié))",
-- 核心計算：區(qū)域總大小 = 記錄數(shù) × 每條大小
ROUND((records_total * record_size) / 1024 /1024, 2) AS "總大小(MB)"
FROM v$controlfile_record_section
ORDER BY (records_total * record_size) DESC;
區(qū)域類型 預(yù)分配記錄數(shù) 單條記錄大小(字節(jié)) 總大小(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
數(shù)據(jù)庫內(nèi)部構(gòu)造中英文對比
ARCHIVED LOG 歸檔日志
FILENAME 文件名
PROXY COPY 代理副本
DATAFILE COPY 數(shù)據(jù)文件副本
BACKUP PIECE 備份片
DATABASE BLOCK CORRUPTION 數(shù)據(jù)庫塊損壞
FOREIGN ARCHIVED LOG 外部歸檔日志
DATAFILE 數(shù)據(jù)文件
RESTORE POINT 恢復(fù)點
GUARANTEED RESTORE POINT 保證恢復(fù)點
CKPT PROGRESS 檢查點進(jìn)度
BACKUP DATAFILE 備份數(shù)據(jù)文件
OFFLINE RANGE 脫機(jī)范圍
FLASHBACK LOG 閃回日志
DELETED OBJECT 已刪除對象
TABLESPACE 表空間
TEMPORARY FILENAME 臨時文件名
RMAN CONFIGURATION RMAN 配置
BACKUP SET 備份集
COPY CORRUPTION 副本損壞
BACKUP CORRUPTION 備份損壞
BACKUP REDOLOG 備份重做日志
DATAFILE HISTORY 數(shù)據(jù)文件歷史
REMOVABLE RECOVERY FILES 可移動恢復(fù)文件
INSTANCE SPACE RESERVATION 實例空間預(yù)留
RMAN STATUS RMAN 狀態(tài)
DATABASE INCARNATION 數(shù)據(jù)庫版本
LOG HISTORY 日志歷史
BACKUP SPFILE 備份 SPFILE
REDO LOG 重做日志
STANDBY DATABASE MATRIX 備用數(shù)據(jù)庫矩陣
REDO THREAD 重做線程
ACM OPERATION ACM 操作
MTTR 平均故障恢復(fù)時間
THREAD INSTANCE NAME MAPPING 線程實例名稱映射
DATABASE 數(shù)據(jù)庫
RECOVERY DESTINATION 恢復(fù)目標(biāo)
我百度了一下網(wǎng)上的信息，大多數(shù)都是因為閃回和歸檔太大導(dǎo)致的控制文件大小劇增
查詢現(xiàn)存的數(shù)據(jù)庫歸檔，deleted表示歸檔是否刪除，name一定要指定對，因為可能會有ADG，所以只需要看本地實際存在的就行。
查詢現(xiàn)存的數(shù)據(jù)庫歸檔，deleted表示歸檔是否刪除，name一定要指定對，因為可能會有ADG，所以只需要看本地實際存在的就行。
SELECT
COUNT(1) AS "現(xiàn)存歸檔條數(shù)",
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' -- 排除備用庫歸檔;
現(xiàn)存歸檔條數(shù) 總大小(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 "現(xiàn)存歸檔條數(shù)",
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' -- 排除備用庫歸檔;
空值代表清理完畢
刷新檢查點
網(wǎng)上說這兩個方法都行，我就都執(zhí)行了
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確定，注冊成功
刷新檢查點
網(wǎng)上說這兩個方法都行，我就都執(zhí)行了
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
重啟數(shù)據(jù)庫
su - grid
##其中dev6指的是數(shù)據(jù)庫實例
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
啟動數(shù)據(jù)庫報錯了
后臺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
如果是集群，需要先關(guān)閉集群參數(shù)
alter system set cluster_database=FALSE scope=spfile sid='*';
shutdown immediate
startup nomount;
##集群需要關(guān)閉數(shù)據(jù)庫參數(shù)，否則會報錯 重建過程出錯： ORA-01503: CREATE CONTROLFILE failed ORA-12720: operation requires database is in EXCLUSIVE mode
打開控制文件備份，摘取所需信息
cat /tmp/ora.trace
注意兩點，一個是 -- 都是注釋，可以去掉，第二個是控制文件里的創(chuàng)建信息都有重復(fù)的，選取一份就行
##啟動到nomount狀態(tài)
STARTUP NOMOUNT
##創(chuàng)建控制文件里的基礎(chǔ)信息
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
;
##查看是否需要恢復(fù)庫（我這里不需要）
RECOVER DATABASE
##強(qiáng)制數(shù)據(jù)庫立即歸檔當(dāng)前所有尚未歸檔的在線重做日志文件 (Online Redo Log Files)。
ALTER SYSTEM ARCHIVE LOG ALL;
##啟動數(shù)據(jù)庫
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;
檢查控制文件信息發(fā)現(xiàn)已經(jīng)自動更新了
SQL> show parameter control_files
NAME TYPE VALUE
--------------- ----------- ------------------------------
control_files string +DATA/ojndev1/controlfile/current.256.1209483215
重啟數(shù)據(jù)庫
##修改回集群參數(shù)
su - oracle
sqlplus / as sysdba
alter system set cluster_database=True scope=spfile sid='*';
##重啟數(shù)據(jù)庫集群
su - grid
srvctl start database -d dev6
查看控制文件構(gòu)造和大小
SELECT
type AS "區(qū)域類型",
records_total AS "預(yù)分配記錄數(shù)",
record_size AS "單條記錄大小(字節(jié))",
-- 核心計算：區(qū)域總大小 = 記錄數(shù) × 每條大小
ROUND((records_total * record_size) / 1024 /1024, 2) AS "總大小(MB)"
FROM v$controlfile_record_section
ORDER BY (records_total * record_size) DESC;
區(qū)域類型 預(yù)分配記錄數(shù) 單條記錄大小(字節(jié)) 總大小(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
數(shù)據(jù)庫層面檢查總大小
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
服務(wù)器層面查看
ASMCMD> ls -s Current.256.1209483215
Block_Size Blocks Bytes Space Name
16384 1149 18825216 25165824 Current.256.1209483215
可以確定控制文件的確縮小了
從22.92M重建以后縮小到17.94M
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，墨天輪將立刻刪除相關(guān)內(nèi)容。
