一、背景
公司一套测试环境，突然开发说数据库登录不进去了，看了下进程不存在，尝试启动一下，提示控制文件损坏，查看日志检查存储空间满了，清理了一些空间，重新启动还是异常提示控制文件损坏，查看服务器启动时间是最近，应该是存储空间满加上服务起重启导致控制文件损坏了，正常空间满一般不会造成此问题
启动报错
RMAN> startup mount;
database is already started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/02/2025 10:33:27
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/u01/app/oracle/oradata/xxx/control01.ctl'
二、修复
1、先备份所有控制文件，避免造成二次损坏
我们整个操作种可能会多次修改控制文件，所有操作钱先把控制文件备份，避免二次损坏
cp /u01/app/oracle/oradata/xxx/control01.ctl /u01/app/oracle/oradata/xxx/control01.ctl_bak
cp /u01/app/oracle/fast_recovery_area/xxx/control02.ctl /u01/app/oracle/fast_recovery_area/xxx/control02.ctl_bak
2、尝试使用冗余控制文件恢复
报control01.ctl控制文件损坏，先尝试用control02.ctl尝试恢复，通过spfile创建pfile，然后只保留control02.ctl，然后用pfile启动
#创建pfile
SQL> create pfile='/home/oracle/1.ora' from spfile ;
File created.#修改pfile控制文件只使用control02.ctl
*.control_files='/u01/app/oracle/oradata/xxx/control01.ctl','/u01/app/oracle/fast_recovery_area/xxx/control02.ctl'#Restore Controlfile
修改为
*.control_files='/u01/app/oracle/fast_recovery_area/xxx/control02.ctl'#Restore Controlfile
#使用pfile启动
SQL> startup nomount pfile='/home/oracle/1.ora';
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 4693340160 bytes
Fixed Size 2236208 bytes
Variable Size 2868904144 bytes
Database Buffers 1811939328 bytes
Redo Buffers 10260480 bytes#切换数据库为mount尝试失败
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file:
'/u01/app/oracle/fast_recovery_area/xxx/control02.ctl'
3、使用rman备份控制文件恢复
检查rman备份去年有控制文件备份，然后检查归档日志是连续的从备份后到当前没有删除过，如果满足我们可以使用rman备份恢复控制文件
1）检查备份
检查下rman最新备份的控制文件，及最新备份的控制文件后是否所有归档日志都是否存在
dzsj-ctks-db01:/home/oracle$rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 14 10:46:20 2025
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: xxx(DBID=86756943)
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 9.64M DISK 00:00:02 2024-10-22 12:06:54
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20241022T120652
Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/xxx_xxx_set=1_piece=1_date=20241022_0138792s_1_1.ctl
Control File Included: Ckp SCN: 15632368570225 Ckp time: 2024-10-22 12:06:52
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 80.00K DISK 00:00:00 2024-10-22 12:07:15
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20241022T120715
Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/xxx_xxx_set=3_piece=1_date=20241022_0338793j_1_1.sp.ora
SPFILE Included: Modification time: 2024-10-22 12:01:23
SPFILE db_unique_name:xxx
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5 Full 9.67M DISK 00:00:00 2024-10-22 12:18:54
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20241022T121854
Piece Name: /u01/app/oracle/fast_recovery_area/xxx/autobackup/2024_10_22/o1_mf_s_1183033134_mkg9xgw5_.bkp
SPFILE Included: Modification time: 2024-10-22 12:01:23
SPFILE db_unique_name: xxx
Control File Included: Ckp SCN: 15632368571282 Ckp time: 2024-10-22 12:18:54
2）恢复控制文件
先通过rman恢复控制文件
RMAN> restore controlfile from autobackup;
Starting restore at 2025-04-02 10:42:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=767 device type=DISK
recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: CSSTAX
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/CSSTAX/autobackup/2024_10_22/o1_mf_s_1183033432_mkgb6rs2_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/CSSTAX/autobackup/2024_10_22/o1_mf_s_1183033432_mkgb6rs2_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/csstax/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/csstax/control02.ctl
Finished restore at 2025-04-02 10:42:24
3) recover数据库
通过归档介质恢复数据库，等恢复完成后，尝试启动数据库
RMAN> recover database;
Starting recover at 2025-04-02 10:43:05
Starting implicit crosscheck backup at 2025-04-02 10:43:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=767 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 2025-04-02 10:43:07
Starting implicit crosscheck copy at 2025-04-02 10:43:07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2025-04-02 10:43:07
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/xxx/archivelog/2024_08_13/o1_mf_1_634_mcp2jfjy_.arc
。。。。。
File Name: /u01/app/oracle/fast_recovery_area/xxx/archivelog/2023_12_25/o1_mf_1_581_lrm2r0x5_.arc
File Name: /u01/app/oracle/fast_recovery_area/xxx/autobackup/2024_04_03/o1_mf_s_1165322333_m0sqbxdb_.bkp
File Name: /u01/app/oracle/fast_recovery_area/xxx/autobackup/2024_10_22/o1_mf_s_1183033432_mkgb6rs2_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 651 is already on disk as file /u01/app/oracle/fast_recovery_area/xxx/archivelog/2024_10_27/o1_mf_1_651_mkt5jff5_.arc
archived log for thread 1 with sequence 652 is already on disk as file /u01/app/oracle/fast_recovery_area/xxx/archivelog/2024_11_02/o1_mf_1_652_mlb05vpc_.arc
。。。。。
archived log file name=/u01/app/oracle/fast_recovery_area/xxx/archivelog/2025_03_01/o1_mf_1_672_mw4dj0yb_.arc thread=1 sequence=672
archived log file name=/u01/app/oracle/oradata/xxx/redo03.log thread=1 sequence=673
archived log file name=/u01/app/oracle/oradata/xxx/redo04.log thread=1 sequence=674
media recovery complete, elapsed time: 00:02:59
Finished recover at 2025-04-02 10:46:09
4）启动数据库
恢复完后我们尝试启动数据库，无法直接启动需要resetlogs或者 NORESETLOGS来启动数据库
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/02/2025 10:46:32
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
#提示需要resetlogs或者 NORESETLOGS ，无法直接启动，我们使用resetlogs
RMAN> alter database open resetlogs;
database opened
5）收尾
重新关闭重新启动一下
SQL> hutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 4693340160 bytes
Fixed Size 2236208 bytes
Variable Size 2868904144 bytes
Database Buffers 1811939328 bytes
Redo Buffers 10260480 bytes
Database mounted.
Database opened.
SQL>
三、命令总结
#从spfile创建pfile文件
create pfile='/home/oracle/1.ora' from spfile ;
#使用pfile启动数据库
startup nomount pfile='/home/oracle/1.ora';
#修改数据库为mount
alter database mount;
#恢复控制文件
restore controlfile from autobackup;
#介质恢复
recover database;
#使用resetlog打开数据库
alter database open resetlogs;
备份是数据库的第二生命，谨记！！！
