oracle中12C sysaux 异常恢复―ORA-01190错误恢复

作者：袖梨 2022-06-29

有朋友请求支援，他们数据库由于file 3 大量坏块，然后直接使用rman 备份还原了file 3，但是在recover过程中发现归档丢失，而且整个库在丢失归档的scn之后，还做过resetlogs操作，导致现在整个库无法正常启动，报ORA-01190错误，希望帮忙把file 3 给online起来，整个库正常open【当然在丢失sysaux的情况下，数据库可以open起来，但是这种情况下，迁移数据比较麻烦】
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area 3.1868E+10 bytes
Fixed Size                  3601144 bytes
Variable Size            2.8655E+10 bytes
Database Buffers         3154116608 bytes
Redo Buffers               54804480 bytes
数据库装载完毕。
ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 3: 'E:APPORAADMORADATAORCLSYSAUX01.DBF'
Oracle Database Recovery Check Result结果显示[脚本]
wrong+resetlogs
尝试不完全恢复并使用隐含参数打开库
Fri Oct 02 19:10:12 2015
ALTER DATABASE RECOVER database until cancel
Fri Oct 02 19:10:12 2015
Media Recovery Start
Started logmerger process
Fri Oct 02 19:10:12 2015
Media Recovery failed with error 16433
Fri Oct 02 19:10:14 2015
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER database until cancel ...
Fri Oct 02 19:10:37 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_5176.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:10:37 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_5176.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
ALTER DATABASE RECOVER database until cancel
Fri Oct 02 19:11:18 2015
Media Recovery Start
Started logmerger process
Fri Oct 02 19:11:18 2015
Media Recovery failed with error 16433
Fri Oct 02 19:11:19 2015
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER database until cancel ...
alter database open resetlogs
ORA-1139 signalled during: alter database open resetlogs...
alter database open
Fri Oct 02 19:11:49 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_ora_4252.trc:
ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 3: 'E:APPORAADMORADATAORCLSYSAUX01.DBF'
ORA-1190 signalled during: alter database open...
Fri Oct 02 19:15:38 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_5292.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:15:38 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_5292.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:20:39 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_2276.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:20:39 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_2276.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:25:40 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_4804.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:25:40 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_4804.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:30:41 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_876.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:30:41 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_m000_876.trc:
ORA-16433: The database or pluggable database must be opened in read/write mode.
Fri Oct 02 19:32:40 2015
Shutting down instance (abort)
数据库遭遇ORA-16433,此类方法无法打开数据库，根据经验值出现此类问题，可能需要重建控制文件，但是由于其中file 3的resetlogs scn不正确，无法包含该文件重建控制文件
Fri Oct 02 20:10:55 2015
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Fri Oct 02 20:10:55 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_ora_5004.trc:
ORA-01189: ????????????? RESETLOGS
ORA-01110: ???? 3: 'E:APPORAADMORADATAORCLSYSAUX01.DBF'
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 2921
LOGFILE
GROUP 3 'E:APPORAADMORADATAORCLREDO03.LOG' size 50M,
GROUP 2 'E:APPORAADMORADATAORCLREDO02.LOG' size 50M,
GROUP 1 'E:APPORAADMORADATAORCLREDO01.LOG' size 50M
DATAFILE
'E:APPORAADMORADATAORCLSYSTEM01.DBF',
'E:APPORAADMORADATAORCLPDBSEEDSYSTEM01.DBF',
'E:APPORAADMORADATAORCLSYSAUX01.DBF',
'E:APPORAADMORADATAORCLPDBSEEDSYSAUX01.DBF',
'E:APPORAADMORADATAORCLUNDOTBS01.DBF',
'E:APPORAADMORADATAORCLUSERS01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSYSTEM01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSYSAUX01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSAMPLE_SCHEMA_USERS01.DBF',
'E:APPORAADMORADATAORCLPDBORCLEXAMPLE01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE.DBF',
'E:APPORAADMORADATAORCLNMSA_BACKUP01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE1.DBF',
'E:APPORAADMORADATAORCLV3XSPACE01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE02.DBF',
'E:APPORAADMORADATAORCLV3XSPACE03.DBF',
'E:APPORAADMORADATAORCLV3XSPACE04.DBF'
CHARACTER SET AL32UTF8
...
除掉file 3 继续重建控制文件
Fri Oct 02 20:33:11 2015
Successful mount of redo thread 1, with mount id 1419796614
Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 2921
LOGFILE
GROUP 3 'E:APPORAADMORADATAORCLREDO03.LOG' size 50M,
GROUP 2 'E:APPORAADMORADATAORCLREDO02.LOG' size 50M,
GROUP 1 'E:APPORAADMORADATAORCLREDO01.LOG' size 50M
DATAFILE
'E:APPORAADMORADATAORCLSYSTEM01.DBF',
'E:APPORAADMORADATAORCLPDBSEEDSYSTEM01.DBF',
--'E:APPORAADMORADATAORCLSYSAUX01.DBF',
'E:APPORAADMORADATAORCLPDBSEEDSYSAUX01.DBF',
'E:APPORAADMORADATAORCLUNDOTBS01.DBF',
'E:APPORAADMORADATAORCLUSERS01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSYSTEM01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSYSAUX01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSAMPLE_SCHEMA_USERS01.DBF',
'E:APPORAADMORADATAORCLPDBORCLEXAMPLE01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE.DBF',
'E:APPORAADMORADATAORCLNMSA_BACKUP01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE1.DBF',
'E:APPORAADMORADATAORCLV3XSPACE01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE02.DBF',
'E:APPORAADMORADATAORCLV3XSPACE03.DBF',
'E:APPORAADMORADATAORCLV3XSPACE04.DBF'
CHARACTER SET AL32UTF8
继续恢复数据库
ALTER DATABASE OPEN
Fri Oct 02 20:34:57 2015
…………
Archived Log entry 3 added for thread 1 sequence 8 ID 0x54a083a3 dest 1:
Fri Oct 02 20:35:16 2015
Tablespace 'SYSAUX' #1 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #3 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00003' in the controlfile.
Corrected file 15 plugged in read-only status in control file
Corrected file 16 plugged in read-only status in control file
Corrected file 17 plugged in read-only status in control file
Corrected file 18 plugged in read-only status in control file
Corrected file 19 plugged in read-only status in control file
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Fri Oct 02 20:35:19 2015
SMON: enabling tx recovery
Fri Oct 02 20:35:19 2015
*********************************************************************
WARNING: The following temporary tablespaces in container(CDB$ROOT)
         contain no files.
Starting background process SMCO
Fri Oct 02 20:35:19 2015
SMCO started with pid=45, OS id=1500
         This condition can occur when a backup controlfile has
         been restored. It may be necessary to add files to these
         tablespaces. That can be done using the SQL statement:

         ALTER TABLESPACE ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
Fri Oct 02 20:35:21 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:APPORAADMPRODUCT12.1.0DBHOME_1DATABASEMISSING00003'
Fri Oct 02 20:35:21 2015
Errors in file E:APPORAADMdiagrdbmsorcloaorcltraceoaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:APPORAADMPRODUCT12.1.0DBHOME_1DATABASEMISSING00003'
Error 376 happened during db open, shutting down database
USER (ospid: 2220): terminating the instance due to error 376
Fri Oct 02 20:35:26 2015
Instance terminated by USER, pid = 2220
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (2220) as a result of ORA-1092
此时由于file 3 未包含在控制文件中，但是存在数据字典中，因此在数据库open的时候出现了默认文件名MISSING0003，尝试重命名改文件指定为存在的file 3,并且尝试恢复
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 3.1868E+10 bytes
Fixed Size                  3601144 bytes
Variable Size            2.8655E+10 bytes
Database Buffers         3154116608 bytes
Redo Buffers               54804480 bytes
数据库装载完毕。
SQL> alter database datafile 3 offline;

数据库已更改。

SQL> alter database rename file 'C:APPORAADMPRODUCT12.1.0DBHOME_1DATABASE
MISSING00003' to 'E:APPORAADMORADATAORCLSYSAUX01.DBF';

数据库已更改。

SQL> recover database until cancel;
ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:APPORAADMFAST_RECOVERY_AREAORCLARCHIVELOG2015_10_02O1_MF_1_9_%U_.ARC
ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中

指定日志: {=suggested | filename | AUTO | CANCEL}
E:APPORAADMORADATAORCLREDO01.LOG
ORA-00310: archived log contains sequence 7; sequence 9 required
ORA-00334: archived log: 'E:APPORAADMORADATAORCLREDO01.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:APPORAADMORADATAORCLSYSTEM01.DBF'

SQL> recover database until cancel;
ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:APPORAADMFAST_RECOVERY_AREAORCLARCHIVELOG2015_10_02O1_MF_1_9_%U_.ARC
ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中

指定日志: {=suggested | filename | AUTO | CANCEL}
E:APPORAADMORADATAORCLREDO03.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 3 online;

数据库已更改。

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01122: 数据库文件 3 验证失败
ORA-01110: 数据文件 3: 'E:APPORAADMORADATAORCLSYSAUX01.DBF'
ORA-01202: 此文件的原型错误 - 创建时间错误
这里比较明显ORA-01202，由于创建控制文件之时没有file 3信息，因此导致控制文件中关于file 3的信息和该文件头的创建时间不一致（此处之时显示了时间不一致，如果通过bbed修改时间，后续可能还有很多东西不一致，因此通过bbed 一个个修改一个个尝试，理论可行，但实际可操作性不好）,因此尝试直接使用bbed修改file 3文件头（由于是win环境，操作稍微麻烦点），把resetlogs信息修改和其他的一样
BBED> m /x 3c6b2b35
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 112 to 143           Dba:0x00c00002
------------------------------------------------------------------------
3c6b2b35 386b2200 00000000 00000000 00000000 00000000 00004000 bb460000

<32 bytes per line>

BBED> set offset 116
        OFFSET          116

BBED> m /x 3137ca24
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 116 to 147           Dba:0x00c00002
------------------------------------------------------------------------
3137ca24 00000000 00000000 00000000 00000000 00004000 bb460000 7dc12b35

<32 bytes per line>

BBED> m /x b9f8
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 484 to 515           Dba:0x00c00002
------------------------------------------------------------------------
b9f8a424 00000000 e65e2435 01000000 d3410000 b89b0000 10000900 02000000

<32 bytes per line>

BBED> set offset +2
        OFFSET          486

BBED> m /x cc24
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 486 to 517           Dba:0x00c00002
------------------------------------------------------------------------
cc240000 0000e65e 24350100 0000d341 0000b89b 00001000 09000200 00000000

<32 bytes per line>

BBED> m /x 87df offset 492
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 492 to 523           Dba:0x00c00002
------------------------------------------------------------------------
87df2435 01000000 d3410000 b89b0000 10000900 02000000 00000000 00000000

<32 bytes per line>

BBED>
BBED> m /x 2b35 offset +2
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 494 to 525           Dba:0x00c00002
------------------------------------------------------------------------
2b350100 0000d341 0000b89b 00001000 09000200 00000000 00000000 00000000

<32 bytes per line>

BBED> d offset 140
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 140 to 171           Dba:0x00c00002
------------------------------------------------------------------------
bb460000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> m /x 4248
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 140 to 171           Dba:0x00c00002
------------------------------------------------------------------------
42480000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> d offset 148
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 148 to 179           Dba:0x00c00002
------------------------------------------------------------------------
ba460000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> m /x 4148
File: SYSAUX01.dbf (3)
Block: 2                Offsets: 148 to 179           Dba:0x00c00002
------------------------------------------------------------------------
41480000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply
Check value for File 3, Block 2:
current = 0xd0c8, required = 0xd0c8

BBED> verify
DBVERIFY - Verification starting
FILE = SYSAUX01.dbf
BLOCK = 1

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found; product=RDBMS; facility=BBED
修改完file 3的文件头之后，再次重建控制文件,此次包含file 3
Fri Oct 02 21:19:58 2015
Successful mount of redo thread 1, with mount id 1419797885
Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS FORCE LOGGING ARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 2921
LOGFILE
GROUP 3 'E:APPORAADMORADATAORCLREDO03.LOG' size 50M,
GROUP 2 'E:APPORAADMORADATAORCLREDO02.LOG' size 50M,
GROUP 1 'E:APPORAADMORADATAORCLREDO01.LOG' size 50M
DATAFILE
'E:APPORAADMORADATAORCLSYSTEM01.DBF',
'E:APPORAADMORADATAORCLPDBSEEDSYSTEM01.DBF',
'E:APPORAADMORADATAORCLSYSAUX01.DBF',
'E:APPORAADMORADATAORCLPDBSEEDSYSAUX01.DBF',
'E:APPORAADMORADATAORCLUNDOTBS01.DBF',
'E:APPORAADMORADATAORCLUSERS01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSYSTEM01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSYSAUX01.DBF',
'E:APPORAADMORADATAORCLPDBORCLSAMPLE_SCHEMA_USERS01.DBF',
'E:APPORAADMORADATAORCLPDBORCLEXAMPLE01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE.DBF',
'E:APPORAADMORADATAORCLNMSA_BACKUP01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE1.DBF',
'E:APPORAADMORADATAORCLV3XSPACE01.DBF',
'E:APPORAADMORADATAORCLV3XSPACE02.DBF',
'E:APPORAADMORADATAORCLV3XSPACE03.DBF',
'E:APPORAADMORADATAORCLV3XSPACE04.DBF'
CHARACTER SET AL32UTF8
继续恢复数据库,数据库正常open，而且file 3 已经正常online，数据库可以直接导出来，至此恢复大体完成