ORA-01190 – Database SOS

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

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

今天有一个客户数据库恢复请求,通过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;

数据库已更改。

_ALLOW_RESETLOGS_CORRUPTION

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：_ALLOW_RESETLOGS_CORRUPTION

我相信_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库之后,强烈建议通过逻辑方式重建库

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

联系：手机/微信(+86 17813235971) QQ(107644445)

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

有朋友请求支援，他们数据库由于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，数据库可以直接导出来，至此恢复大体完成

_allow_resetlogs_corruption和adjust_scn解决ORA-01190

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：_allow_resetlogs_corruption和adjust_scn解决ORA-01190

一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_STATUS  TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
         5 OFFLINE               868810
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(last_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
         1        868874
         2        868874
         3        868874
         4        868874
         5        868810                     868874
--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        868874                     868871
         2        868874                     868871
         3        868874                     868871
         4        868874                     868871
         5        868810                     787897

二、隐含参数设置

SQL> create pfile='/tmp/pfile' from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
在pfile中增加
_allow_resetlogs_corruption=true
_allow_error_simulation=TRUE(10g及其以上版本需要)

三、打开数据库,online离线文件

SQL> startup pfile='/tmp/pfile' mount;
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             293603848 bytes
Database Buffers           67108864 bytes
Redo Buffers                6205440 bytes
Database mounted.
--在mount状态下执行
SQL> alter session set events '10015 trace name adjust_scn level 2';
Session altered.
--[一定要]在mount状态下执行online操作
SQL> alter database datafile 5 online;
Database altered.
SQL> recover database until cancel;
ORA-00279: change 868810 generated at 03/13/2012 22:19:37 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_27_777766629.dbf
ORA-00280: change 868810 for thread 1 is in sequence #27
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
no rows selected

姊妹篇:bbed解决ORA-01190

bbed解决ORA-01190

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：bbed解决ORA-01190

当我们使用resetlogs方式打开数据库后，发现有数据文件处于offline状态，这个时候很可能就是悲剧降临的时候，很有可能这个文件文件在resetlogs之前就处于offline状态，然后你resetlogs之后，这个文件使用常规方法很难再online，会出现ORA-01190或者ORA-01189之类的错误。
一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_STATUS  TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
         5 OFFLINE               868810
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(last_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
         1        868874
         2        868874
         3        868874
         4        868874
         5        868810                     868874
--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        868874                     868871
         2        868874                     868871
         3        868874                     868871
         4        868874                     868871
         5        868810                     787897
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

二、bbed修改相关项

下面两项与resetlogs相关
kcvfhrlc表示 reset logs count
kcvfhrls表示 resetlogs scn
下面四项与数据库文件scn相关
kscnbas (at offset 140) – SCN of last change to the datafile.
kcvcptim (at offset 148) - Time of the last change to the datafile.
kcvfhcpc (at offset 176) – Checkpoint count.
kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
BBED> set filename '/u01/oracle/oradata/ora11g/system01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/system01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
   ub4 kscnbas                              @116      0x000d4207
   ub2 kscnwrp                              @120      0x0000
BBED>  p kcvcpscn
struct kcvcpscn, 8 bytes                 @484
   ub4 kscnbas                           @484      0x000d4495
   ub2 kscnwrp                           @488      0x0000
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000077
BBED> set filename '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/xifenfei01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5bc6e5
BBED> set mode edit
        MODE            Edit
BBED> m /x 37ed5e2e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
   ub4 kscnbas                              @116      0x000c05b9
   ub2 kscnwrp                              @120      0x0000
BBED> m /x 07420d00
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
   ub4 kscnbas                              @116      0x000d4207
   ub2 kscnwrp                              @120      0x0000
BBED>  p kcvcpscn
struct kcvcpscn, 8 bytes                 @484
   ub4 kscnbas                           @484      0x000d41ca
   ub2 kscnwrp                           @488      0x0000
BBED> set offset 484
        OFFSET          484
BBED> m /x 95440d00
BBED-00209: invalid number (95440d00)
BBED> m /x 9544
BBED> set offset +2
        OFFSET          486
BBED> m /x 0d00
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000003
BBED> m /x 78000000
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000002
BBED> m /x 77000000
BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000077
BBED> sum
Check value for File 0, Block 1:
current = 0xe079, required = 0x5940
BBED> sum apply
Check value for File 0, Block 1:
current = 0x5940, required = 0x5940

三、数据文件online

[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:48:48 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             301992456 bytes
Database Buffers           58720256 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
  2  to_char(RESETLOGS_CHANGE#,'999999999999')
  3  from v$datafile_header;
     FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
         1        869528                     868871
         2        869528                     868871
         3        869528                     868871
         4        869528                     868871
         5        869525                     868871
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.

姊妹篇:_allow_resetlogs_corruption和adjust_scn解决ORA-01190

标签归档：ORA-01190