标签归档：ORA-01190

Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障

发表于 2021 年 1 月 6 日由惜分飞

今天有一个客户数据库恢复请求,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本分析发现resetlog信息异常

导致数据库恢复报ORA-01190 ORA-01110错

alter database open
Errors in file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4404.trc:
ORA-01190: 控制文件或数据文件 1 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 1: 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1190 signalled during: alter database open...

通过Oracle Recovery Tools工具进行修复resetlog 信息

再次尝试open数据库报ORA-1248错

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01248: ?? 44 ????????????
ORA-01110: ???? 44: 'E:\ORADATA\ORCL\XIFENFEI.DBF'

Wed Jan 06 14:44:44 2021
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
ORA-1248 signalled during: alter database open resetlogs...

再次通过Oracle Recovery Tools进行修复SCN,数据库open成功

T:\xff>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 6 14:47:36 2021

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup mount 
ORACLE 例程已经启动。

Total System Global Area 6.9214E+10 bytes
Fixed Size                  2182712 bytes
Variable Size            3.5165E+10 bytes
Database Buffers         3.3823E+10 bytes
Redo Buffers              224296960 bytes
数据库装载完毕。
SQL>
SQL>
SQL> alter database open;

数据库已更改。

软件下载:OraRecovery下载
使用说明:使用说明

发表在小工具 | 标签为 ORA-01190, ORA-01248, Oracle Recovery Tools | 评论关闭

_ALLOW_RESETLOGS_CORRUPTION

发表于 2016 年 7 月 31 日由惜分飞

我相信_ALLOW_RESETLOGS_CORRUPTION 这个参数一定很多人都熟悉,是redo异常恢复的杀手锏之一,以下文章是来自官方的解释

DB_Parameter _ALLOW_RESETLOGS_CORRUPTION 
========================================
 
This documentation has been prepared avoiding the mention of the complex 
structures from the code and to simply give an insight to the 'damage it could 
cause'.  The usage of this parameter leads to an in-consistent Database with no 
other alternative but to rebuild the complete Database.  This parameter could 
be used when we realize that there are no stardard options available and are 
convinced that the customer understands the implications of using the Oracle's 
secret parameter.  The factors to be considered are ;-- 
 
1. Customer does not have a good backup. 
2. A lot of time and money has been invested after the last good backup and     
   there is no possibility for reproduction of the lost data. 
3. The customer has to be ready to export the full database and import it     
   back after creating a new one. 
4. There is no 100% guarantee that by using this parameter the database would 
   come up. 
5. Oracle does not support the database after using this parameter for        
   recovery.    
6. ALL OPTIONS including the ones mentioned in the action part of the error   
   message have been tried. 
 
 

 
By setting _ALLOW_RESETLOGS_CORRUPTION=TRUE, certain consistency checks are 
SKIPPED during database open stage.  This basically means it does not check 
the datafile headers as to what the status was before the shutdown and how it 
was shutdown.  The following cases mention few of the checks that were skipped. 
 
Case-I 
------ 
Verification that the datafile present has not been restored from a BACKUP 
taken before the database was opened successfully by using RESETLOGS.   

ORA-01190: control file or data file %s is from before the last RESETLOGS
    Cause: Attempting to use a data file when the log reset information in  
           the file does not match the control file.  Either the data file or  
           the control file is a backup that was made before the most recent 
           ALTER DATABASE OPEN RESETLOGS. 
   Action: Restore file from a more recent backup. 

 
Case-II 
------- 
Verification that the status bit of the datafile is not in a FUZZY state. 
The datafile could be in this state due to the database going down when the  
 - Datafile was on-line and open 
 - Datafile was not closed cleanly (maybe due to OS). 

ORA-01194: file %s needs more recovery to be consistent 
    Cause: An incomplete recover session was started, but an insufficient 
           number of logs were applied to make the file consistent.  The  
           reported file was not closed cleanly when it was last opened by 
           the database.  It must be recovered to a time when it was not  
           being updated.  The most likely cause of this error is forgetting 
           to restore the file from a backup before doing incomplete  
           recovery. 
   Action: Either apply more logs until the file is consistent or restore the 
           file from an older backup and repeat recovery. 
 

Case-III 
-------- 
Verification that the COMPLETE recover strategies have been applied for 
recovering the datafile and not any of the INCOMPLETE recovery options.  
Basically because the complete recovery is one in which we even apply the 
ON-LINE redo log files and open the DB without reseting the logs. 

ORA-01113: file '%s' needs media recovery starting at log sequence # %s 
    Cause: An attempt was made to open a database file that is in need of  
           media recovery. 
   Action: First apply media recovery to the file. 

 
Case-IV 
------- 
Verification that the datafile has been recovered through an END BACKUP if the 
control file indicates that it was in backup mode. 
This is useful when the DB has crashed while in hot backup mode and we lost 
all log files in DB version's less than V7.2. 

ORA-01195: on-line backup of file %s needs more recovery to be consistent" 
    Cause: An incomplete recovery session was started, but an insufficient  
           number of logs were applied to make the file consistent.  The 
           reported file is an on-line backup which must be recovered to the 
           time the backup ended. 
   Action: Either apply more logs until the file is consistent or resotre 
          the database files from an older backup and repeat recovery. 
 
In version 7.2, we could simply issue the ALTER DATABASE DATAFILE xxxx END 
BACKUP statement and proceed with the recovery.  But again to issue this 
statement, we need to have the ON-LINE redo logs or else we still are forced to
use this parameter. 
 

Case-V 
------ 
Verification that the data file status is not still in (0x10) MEDIA recovery 
FUZZY. 
When recovery is started, a flag is set in the datafile header status flag to 
indicate that the file is presently in media recovery.  This is reset when 
recovery is completed and at times when it has not been reset we are forced to 
use this paramter. 

ORA-01196: file %s is inconsistent due to a failed media recovery session 
    Cause: The file was being recovered but the recovery did not terminate 
           normally.  This left the file in an inconsistent state.  No more  
           recovery was successfully completed on this file. 
   Action: Either apply more logs until the file is consistent or restore the 
           backup again and repeat recovery. 
 
 
Case-VI 
------- 
Verification that the datafile has been restored form a proper backup to 
correspond with the log files.  This situation could happen when we have 
decided that the data file is invalid since its SCN is ahead of the last 
applied logs SCN but it has not failed on one of the ABOVE CHECKS. 

ORA-01152: file '%s' was not restored from a sufficientluy old backup" 
    Cause: A manual recovery session was started, but an insufficient number 
           of logs were applied to make the database consistent.  This file is 
           still in the future of the last log applied.  Note that this  
           mistake can not always be caught. 
   Action: Either apply more logs until the database is consistent or 
           restore the database file from an older backup and repeat  
           recovery.

使用_ALLOW_RESETLOGS_CORRUPTION 参数需谨慎,因为该参数可能导致数据库逻辑不一致,甚至可能把本来很简单的一个恢复弄的非常复杂甚至不可恢复的后果,建议在oracle support支持下使用.另外使用该参数resetlogs库之后,强烈建议通过逻辑方式重建库

发表在 Oracle备份恢复 | 标签为 ORA-01113, ORA-01152, ORA-01190, ORA-01194, ORA-01195, ORA-01196, _ALLOW_RESETLOGS_CORRUPTION | 评论关闭

有朋友请求支援，他们数据库由于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:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_4252.trc:
ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前
ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
ORA-1190 signalled during: alter database open...
Fri Oct 02 19:15:38 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_5004.trc:
ORA-01189: ????????????? RESETLOGS
ORA-01110: ???? 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.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:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
 GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG'  size 50M,
 GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG'  size 50M
 DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.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:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
 GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG'  size 50M,
 GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG'  size 50M
 DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
--'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.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 <tablespace_name> 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:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003'
Fri Oct 02 20:35:21 2015
Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc:
ORA-00376: 此时无法读取文件 3
ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003'
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:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\
MISSING00003' to 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF';

数据库已更改。

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


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG
ORA-00310: archived log contains sequence 7; sequence 9 required
ORA-00334: archived log: 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.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:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF'


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


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG
已应用的日志。
完成介质恢复。
SQL> alter database datafile 3 online;

数据库已更改。

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01122: 数据库文件 3 验证失败
ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.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:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M,
 GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG'  size 50M,
 GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG'  size 50M
 DATAFILE
'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF',
'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF'
 CHARACTER SET AL32UTF8

继续恢复数据库,数据库正常open，而且file 3 已经正常online，数据库可以直接导出来，至此恢复大体完成

发表在非常规恢复 | 标签为 bbed, ORA-01110, ORA-01122, ORA-01190, ORA-01202, sysaux恢复 | 评论关闭

17813235971

QQ 咨询

标签归档：ORA-01190

Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障

_ALLOW_RESETLOGS_CORRUPTION

12C sysaux 异常恢复—ORA-01190错误恢复

文章分类

最近发表

标签归档：ORA-01190

Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障

_ALLOW_RESETLOGS_CORRUPTION

12C sysaux 异常恢复—ORA-01190错误恢复

标签云

文章分类

最近发表