标签归档:ORA-01200

记录一次ORA-01200完美恢复

客户虚拟化平台断电,导致oracle其数据库启动ORA-01200错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oradata/orcl/system01.dbf'
ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks

对应的alert日志如下

Thu Jan 11 11:36:48 2024
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1685778896
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Thu Jan 11 11:36:52 2024
ALTER DATABASE OPEN
Read of datafile '/oradata/orcl/system01.dbf' (fno 1) header failed with ORA-01200
Rereading datafile 1 header failed with ORA-01200
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10847.trc:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oradata/orcl/system01.dbf'
ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks
ORA-1122 signalled during: ALTER DATABASE OPEN...
Thu Jan 11 11:36:53 2024
Checker run found 1 new persistent data failures
Thu Jan 11 11:41:55 2024
alter database open
Read of datafile '/oradata/orcl/system01.dbf' (fno 1) header failed with ORA-01200
Rereading datafile 1 header failed with ORA-01200
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12550.trc:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oradata/orcl/system01.dbf'
ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks
ORA-1122 signalled during: alter database open...

报错比较明显system01.dbf文件本来大小应该为1131521个block,但是实际上只有1122561个block,因此无法正常启动,通过修改数据文件欺骗数据库
20240112123849


然后对异常的system文件进行处理,把人工构造的部分除掉

SQL> alter database datafile 1 resize 8770M;

Database altered.

rman检测system文件正常
20240112124307


数据库恢复完成,数据完美恢复(0丢失,不用逻辑迁移),该库可以继续使用,以前有过类似case:
bbed处理ORA-01200故障
ORA-01122 ORA-01200故障处理
ORA-1200/ORA-1207数据库恢复

发表在 Oracle备份恢复 | 标签为 | 评论关闭

ORA-1200/ORA-1207数据库恢复

由于系统性能问题或者底层io问题,数据库alert日志报一下控制文件损坏错误然后crash掉

Mon Nov 13 08:06:44 2023
Thread 1 advanced to log sequence 12100 (LGWR switch)
  Current log# 1 seq# 12100 mem# 0: /u01/oracle/oradata/xifenfei/redo01.log
Mon Nov 13 09:23:59 2023
********************* ATTENTION: ******************** 
 The controlfile header block returned by the OS
 has a sequence number that is too old. 
 The controlfile might be corrupted.
 PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE 
 without following the steps below.
 RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE 
 TO THE DATABASE, if the controlfile is truly corrupted.
 In order to re-start the instance safely, 
 please do the following:
 (1) Save all copies of the controlfile for later 
     analysis and contact your OS vendor and Oracle support.
 (2) Mount the instance and issue: 
     ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
 (3) Unmount the instance. 
 (4) Use the script in the trace file to
     RE-CREATE THE CONTROLFILE and open the database. 
*****************************************************
USER (ospid: 17064): terminating the instance
Mon Nov 13 09:24:00 2023
System state dump requested by (instance=1, osid=17064), summary=[abnormal instance termination].

重启数据库报ORA-01122 ORA-01110 ORA-01207错误

Mon Nov 13 10:11:21 2023
ALTER DATABASE OPEN
Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_25824.trc:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/oracle/oradata/xifenfei/system01.dbf'
ORA-01207: file is more recent than control file - old control file
ORA-1122 signalled during: ALTER DATABASE OPEN...

处理好上述错误之后遭遇ORA-01122 ORA-01110 ORA-01200,类似文章:
bbed处理ORA-01200故障
ORA-01122 ORA-01200故障处理

Mon Nov 13 10:51:48 2023
alter database open
Read of datafile '/u01/oracle/oradata/xifenfei/sysaux01.dbf' (fno 2) header failed with ORA-01200
Rereading datafile 2 header failed with ORA-01200
Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_24148.trc:
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/u01/oracle/oradata/xifenfei/sysaux01.dbf'
ORA-01200: actual file size of 2860800 is smaller than correct size of 2867200 blocks
ORA-1122 signalled during: alter database open...

解决上述错误之后,尝试open库报ORA-00314 ORA-00312之类错误

Mon Nov 13 18:00:43 2023
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 61894 KB redo, 589 data blocks need recovery
Started redo application at
 Thread 1: logseq 12100, block 112760
Recovery of Online Redo Log: Thread 1 Group 1 Seq 12100 Reading mem 0
  Mem# 0: /u01/oracle/oradata/xifenfei/redo01.log
Completed redo application of 1.20MB
Mon Nov 13 18:00:44 2023
Hex dump of (file 2, block 39078) in trace file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p011_27469.trc
Reading datafile '/u01/oracle/oradata/xifenfei/sysaux01.dbf' for corruption at rdba: 0x008098a6 (file 2, block 39078)
Reread (file 2, block 39078) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 39078 OF FILE 2
Mon Nov 13 18:00:44 2023
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC][PC:0x95FB838, kdxlin()+4946][flags: 0x0, count: 1]
Mon Nov 13 18:00:44 2023
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC][PC:0x95FB4DE, kdxlin()+4088][flags: 0x0, count: 1]
Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p011_27469.trc:
ORA-00314: log 2 of thread 1, expected sequence# 12093 doesn't match 12085
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/xifenfei/redo02.log'
Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p011_27469.trc:
ORA-00314: log 3 of thread 1, expected sequence# 12096 doesn't match 12080
ORA-00312: online log 3 thread 1: '/u01/oracle/oradata/xifenfei/redo03.log'
ORA-00314: log 2 of thread 1, expected sequence# 12093 doesn't match 12085
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/xifenfei/redo02.log'

后面继续处理遇到类似这样错误

ALTER DATABASE RECOVER    CANCEL  
Errors in file /u01/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pr00_31110.trc:
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: '/u01/oracle/oradata/xifenfei/system01.dbf'
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Mon Nov 13 19:06:28 2023
alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...

最后确认其他数据文件均可recover 成功,只有file 2 无法正常recover

SQL> recover datafile 1;
Media recovery complete.
SQL> recover datafile 2;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [2], [950840], [9339448],
[], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 950840, file
offset is 3494313984 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '/u01/oracle/oradata/xifenfei/sysaux01.dbf'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
SQL> recover datafile 3;
Media recovery complete.
SQL> recover datafile 4;
Media recovery complete.
SQL> recover datafile 5;
Media recovery complete.
SQL> recover datafile 6;
Media recovery complete.
SQL> recover datafile 7;
Media recovery complete.

SQL> recover  datafile 2 allow 1 corruption;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [2], [2410240], [10798848],
[], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 2410240, file
offset is 2564816896 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '/u01/oracle/oradata/xifenfei/sysaux01.dbf'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

通过bbed修改文件头,直接open数据库成功,并协助客户顺利导出数据
参考类似文章:
使用bbed修复损坏datafile header
使用bbed让rac中的sysaux数据文件online

发表在 Oracle备份恢复 | 标签为 , | 评论关闭

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

客户那边硬件故障(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成功,实现了最大限度抢救数据.

发表在 Oracle备份恢复 | 标签为 , , , | 评论关闭