适用范围

Oracle Database - Enterprise Edition - Version 11.2.0.4.0 and later

问题概述

### 1. 控制文件异常损坏，数据库异常宕机
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/db11/db11/control01.ctl'
ORA-27041: unable to open file

### 2. 使用bacukp controlfile恢复
RMAN> restore controlfile from '/home/oracle/backup/ctl,bkp';

Starting restore at 16-JUN-23
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/db11/db11/control01.ctl
output file name=/oradata/db11/db11/control02.ctl
Finished restore at 16-JUN-23

RMAN> 

### 3. 数据库启动报错
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL>

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/db11/db11/system01.dbf'


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL>

问题原因

### 1. 重建控制文件前v$log信息
SQL>

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         19   52428800        512          1 YES INACTIVE               1111348 2023-06-16 15:01:00      1111356 2023-06-16 15:01:02
         3          1         18   52428800        512          1 YES INACTIVE               1111344 2023-06-16 15:00:57      1111348 2023-06-16 15:01:00
         2          1         20   52428800        512          1 NO  CURRENT                1111356 2023-06-16 15:01:02   2.8147E+14

SQL>
### 2. 重建控制文件后v$log信息
SQL>

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         25   52428800        512          1 NO  INACTIVE               1111442 2023-06-16 15:02:50      1111446 2023-06-16 15:02:51
         3          1         27   52428800        512          1 NO  CURRENT                1111474 2023-06-16 15:03:56   2.8147E+14
         2          1         26   52428800        512          1 NO  INACTIVE               1111446 2023-06-16 15:02:51      1111474 2023-06-16 15:03:56

SQL>

### 3. 小结
Oracle会使用v$log的信息进行数据库恢复，v$log中的信息是从控制文件中获取的，使用old controlfile中v$log的信息不准确，造成数据库恢复失败。

解决方案

建议通过重建控制文件，更新控制文件中v$log信息
SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt';

Database altered.

SQL>
SQL> startup nomount force
SQL> @cr_ctl.sql
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> 
SQL> @add_tmpfile.sql

参考文档

Open Database failed - DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194
ORA-1547 ORA-1110 (Doc ID 1528788.1)
OERR: ORA-283 "recovery session canceled due to errors" Reference Note (Doc ID 18352.1)