ORA-01122 ORA-01208 故障处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-01122 ORA-01208 故障处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库突然故障ORA-01122 ORA-01208,导致实例crash

Tue Jul 11 09:06:43 2023
Thread 1 cannot allocate new log, sequence 254989
Private strand flush not complete
  Current log# 3 seq# 254988 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\xff\REDO03.LOG
Thread 1 advanced to log sequence 254989 (LGWR switch)
  Current log# 1 seq# 254989 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\xff\REDO01.LOG
Tue Jul 11 09:09:46 2023
Read of datafile 'E:\APP\ADMINISTRATOR\ORADATA\xff\SYSTEM01.DBF' (fno 1) header failed with ORA-01208
Rereading datafile 1 header found valid data
Repaired corruption in datafile 1 header
Read of datafile 'E:\APP\ADMINISTRATOR\ORADATA\xff\SYSAUX01.DBF' (fno 2) header failed with ORA-01208
Rereading datafile 2 header found valid data
Repaired corruption in datafile 2 header
Read of datafile 'E:\APP\ADMINISTRATOR\ORADATA\xff\UNDOTBS01.DBF' (fno 3) header failed with ORA-01208
Rereading datafile 3 header failed with ORA-01208
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ckpt_5820.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: 'E:\APP\ADMINISTRATOR\ORADATA\xff\UNDOTBS01.DBF'
ORA-01208: data file is an old version - not accessing current version
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ckpt_5820.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: 'E:\APP\ADMINISTRATOR\ORADATA\xff\UNDOTBS01.DBF'
ORA-01208: data file is an old version - not accessing current version
CKPT (ospid: 5820): terminating the instance due to error 1242
…………
Tue Jul 11 09:10:10 2023
Instance terminated by CKPT, pid = 5820
Tue Jul 11 09:18:32 2023

重启实例无法open

Tue Jul 11 09:18:41 2023
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1485684209
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_406776.trc:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: 'E:\APP\ADMINISTRATOR\ORADATA\xff\UNDOTBS01.DBF'
ORA-1113 signalled during: alter database open...

通过Oracle Database Recovery Check工具分析
20230715200500


确认数据库恢复需要sequence为254986的日志,但是数据库为非归档模式,redo已经被覆盖,因此常规方法无法正常open库,通过Oracle Recovery Tools工具快速修改文件头实现数据库文件头一致,open数据库成功
20230417230141

硬件故障恢复出文件之后数据库故障处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:硬件故障恢复出文件之后数据库故障处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

客户那边硬件故障(raid损坏磁盘超过了极限,导致raid offline),通过硬件恢复出来数据文件,然后尝试自行恢复,我接手的时候大量数据文件resetlogs scn异常.
wrong_resetlogs


重建控制文件报错

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_5949.trc:
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 153: '/home/oracle/oracledata/orcl/sysaux02.dbf'
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG

通过修改文件头然后重建控制文件,可以通过bbed,或者我的小工具Oracle Recovery Tools
bbed解决ORA-01190
Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障
重建control遗漏数据文件,reseltogs报ORA-1555错误处理
然后继续重建ctl发现以下错误

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_34075.trc:
ORA-01200: actual file size of 2015415 is smaller than correct size of 2944000 blocks
ORA-01110: data file 178: '/home/oracle/oracledata/orcl/xifenfei20_10.dbf'
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

通过对比发现是由于客户上传恢复文件异常导致
20230713002257


重新上传文件,然后修改文件头,该问题解决,重建ctl成功,提个醒:对于这种硬件恢复之后文件上次到服务器上进行恢复的,一定要确认上传文件和原文件一致,不然做无用功或者恢复效果差很多
尝试open数据库报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [5], [1653389530], [5],
[1653496702], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [5], [1653389529], [5],
[1653496702], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [5], [1653389527], [5],
[1653496702], [12583040], [], [], [], [], [], []
Process ID: 4710
Session ID: 1847 Serial number: 3

这个错误比较简单,一般是scn问题,有过大量的处理经验案例:
使用bbed解决ORA-00600[2662]
硬件故障导致ORA-600 2662错误处理
Patch SCN工具快速解决ORA-600 2662问题
解决好该问题之后,数据库open成功,实现了最大限度抢救数据.

dul支持arm版本Oracle数据库恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:dul支持arm版本Oracle数据库恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

确认数据库名字,DBID,运行在ARM平台

SQL> select name,dbid,PLATFORM_ID,PLATFORM_NAME from v$database;

NAME            DBID PLATFORM_ID
--------- ---------- -----------
PLATFORM_NAME
--------------------------------------------------------------------------------
ARMDB     1195886419          23
Linux OS (AARCH64)
dul恢复ARM平台数据库
[oracle@xifenfei dul]$ ./dul

Data UnLoader: 12.2.0.2.5 - Internal Only - on Sun Jul  9 22:05:51 2023
with 64-bit io functions and the decompression option

Copyright (c) 1994 2023 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: ulimit process stack size is only 33554432
Found db_id = 1195886419
Found db_name = ARMDB
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   23092 rows unloaded
. unloading table                      TAB$    1794 rows unloaded
. unloading table                      COL$  118438 rows unloaded
. unloading table                     USER$      85 rows unloaded
Reading USER.dat 85 entries loaded
Reading OBJ.dat 23092 entries loaded and sorted 23092 entries
Reading TAB.dat 1794 entries loaded
Reading COL.dat 118438 entries loaded and sorted 118438 entries
Reading BOOTSTRAP.dat 60 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
 TABPART$: segobjno 822, file 1 block 5496
 INDPART$: segobjno 827, file 1 block 5536
 TABCOMPART$: segobjno 844, file 1 block 5672
 INDCOMPART$: segobjno 849, file 1 block 5712
 TABSUBPART$: segobjno 834, file 1 block 5592
 INDSUBPART$: segobjno 839, file 1 block 5632
 IND$: segobjno 2, tabno 3, file 1  block 144
 ICOL$: segobjno 2, tabno 4, file 1  block 144
 LOB$: segobjno 2, tabno 6, file 1  block 144
 COLTYPE$: segobjno 2, tabno 7, file 1  block 144
 TYPE$: segobjno 748, tabno 1, file 1  block 4960
 COLLECTION$: segobjno 748, tabno 2, file 1  block 4960
 ATTRIBUTE$: segobjno 748, tabno 3, file 1  block 4960
 LOBFRAG$: segobjno 855, file 1 block 5768
 LOBCOMPPART$: segobjno 858, file 1 block 5792
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 127, file 1 block 1320
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   23092 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    1794 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  118438 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      85 rows unloaded
. unloading table                  TABPART$     320 rows unloaded
. unloading table                  INDPART$     186 rows unloaded
. unloading table               TABCOMPART$       1 row  unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$      32 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    2273 rows unloaded
. unloading table                     ICOL$    4155 rows unloaded
. unloading table                      LOB$     566 rows unloaded
. unloading table                  COLTYPE$    2794 rows unloaded
. unloading table                     TYPE$    4381 rows unloaded
. unloading table               COLLECTION$     983 rows unloaded
. unloading table                ATTRIBUTE$   11584 rows unloaded
. unloading table                  LOBFRAG$       8 rows unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      11 rows unloaded
. unloading table                       TS$       5 rows unloaded
. unloading table                    PROPS$      39 rows unloaded
Reading USER.dat 85 entries loaded
Reading OBJ.dat 23092 entries loaded and sorted 23092 entries
Reading TAB.dat 1794 entries loaded
Reading COL.dat 118438 entries loaded and sorted 118438 entries
Reading TABPART.dat 320 entries loaded and sorted 320 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 186 entries loaded and sorted 186 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2273 entries loaded
Reading LOB.dat 566 entries loaded
Reading ICOL.dat 4155 entries loaded
Reading COLTYPE.dat 2794 entries loaded
Reading TYPE.dat
DUL: Notice: Increased the size of DC_TYPES from 4096 to 32768 entries
 4381 entries loaded
Reading ATTRIBUTE.dat 11584 entries loaded
Reading COLLECTION.dat 983 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 8 entries loaded and sorted 8 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 11 entries loaded
Reading TS.dat 5 entries loaded
Reading PROPS.dat 39 entries loaded
Database character set is AL32UTF8
Database national character set is AL16UTF16
DUL> unload table sys.obj$;
. unloading table                      OBJ$   23092 rows unloaded
DUL> 

通过上述测试,证明dul支持arm版本Oracle数据库恢复

win系统删除oracle数据文件恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:win系统删除oracle数据文件恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户联系我们,说win平台下的数据库,在由于空间紧张,在关闭数据库的情况下删除的两个数据文件,导致数据库无法正常访问很多业务表,需要对其进行恢复,查看alert日志发现大概操作,删除文件之后,启动数据库失败

Completed: alter database mount exclusive
alter database open
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_4060.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_4060.trc:
ORA-01157: cannot identify/lock data file 38 - see DBWR trace file
ORA-01110: data file 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Tue Jun 27 14:50:28 2023
Checker run found 2 new persistent data failures

人工创建被删除文件,启动库报ORA-27047,OSD-04006等错误

Tue Jun 27 16:45:10 2023
ALTER DATABASE OPEN
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_5456.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 38) 已到文件结尾。

offline相关数据文件,启动库成功,但是job开始报错

Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_5456.trc:
ORA-01157: cannot identify/lock data file 38 - see DBWR trace file
ORA-01110: data file 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_648.trc:
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Tue Jun 27 16:48:43 2023
alter database datafile 'D:\DATASPACE\XXXXX.DBF' offline drop
Completed: alter database datafile 'D:\DATASPACE\XXXXX.DBF' offline drop
Tue Jun 27 16:49:08 2023
alter database open
Tue Jun 27 16:49:08 2023
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_5456.trc:
ORA-01157: cannot identify/lock data file 38 - see DBWR trace file
ORA-01110: data file 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3976.trc:
ORA-01157: 无法标识/锁定数据文件 38 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 38: 'D:\DATASPACE\XXXXX24.DBF'
ORA-1157 signalled during: alter database open...
Tue Jun 27 16:49:08 2023
Checker run found 1 new persistent data failures
Tue Jun 27 16:49:28 2023
alter database datafile 'D:\DATASPACE\XXXXX24.DBF' offline drop
Completed: alter database datafile 'D:\DATASPACE\XXXXX24.DBF' offline drop
alter database open
Tue Jun 27 16:49:37 2023
Thread 1 opened at log sequence 145929
  Current log# 3 seq# 145929 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jun 27 16:49:37 2023
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
Tue Jun 27 16:49:39 2023
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Jun 27 16:49:40 2023
QMNC started with pid=21, OS id=6096 
Completed: alter database open
Tue Jun 27 16:49:43 2023
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Jun 27 16:49:44 2023
Starting background process CJQ0
Tue Jun 27 16:49:44 2023
CJQ0 started with pid=142, OS id=6036 
Tue Jun 27 16:49:48 2023
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j007_5184.trc:
ORA-12012: 自动执行作业 64 出错
ORA-00376: 此时无法读取文件 6
ORA-01110: 数据文件 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-06512: 在 "XIFENFEI.XXXXXXXX", line 2897
ORA-06512: 在 line 1
Tue Jun 27 16:51:52 2023
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_2548.trc:
ORA-12012: 自动执行作业 64 出错
ORA-00376: 此时无法读取文件 6
ORA-01110: 数据文件 6: 'D:\DATASPACE\XXXXX.DBF'
ORA-06512: 在 "XIFENFEI.XXXXXXXX", line 2897
ORA-06512: 在 line 1
Tue Jun 27 16:54:44 2023
Starting background process SMCO
Tue Jun 27 16:54:44 2023
SMCO started with pid=42, OS id=908 
Tue Jun 27 16:55:52 2023

接手现场之后,关闭数据库,使用操作系统层面反删除工具进行扫描恢复,发现其中一个文件(另外一个文件os层面无法恢复)
20230707132040


通过工具检测恢复出来的数据文件,损坏的几个block是文件头部不涉及业务数据,运气不错
20230707135054

另外一个数据文件,从os层面无法恢复,对于这种情况,只能基于底层的block层面进行恢复(恢复没有覆盖的block)
20230707150912
参考类似恢复案例:
win文件系统损坏oracle恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
分享运气超级好的一次drop tablespace 数据恢复
恢复出来的两个数据文件,结合该编辑的其他数据文件通过dul工具恢复其中数据,最大程度抢救客户数据,减少损失.

ORA-01122 ORA-01200故障处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-01122 ORA-01200故障处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

由于某种原因客户的数据库启动报ORA-01122 ORA-01200错误
ORA-01200


让客户把system01.dbf文件发给我进行分析,发现system01.dbf文件大于32G(在8k的blocksize库中,默认情况system01.dbf文件不会超过32G),这个明显异常
system01.dbf

检测坏块情况发现4096000之后的block全部为全0块
20230704165111

通过bbed分析文件头记录文件大小
20230704165343

通过bbed修改合适的值,并且把文件截取到适当大小,提供system文件给客户,直接启动库成功,实现数据库完美恢复
20230704165533

通过设置文件头大小和截断合适大小实现本次数据库恢复,以前有过类似恢复:
bbed处理ORA-01200故障