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

联系：手机/微信(+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成功,实现了最大限度抢救数据.

Oracle Recovery Tools快速恢复ORA-19909

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

标题：Oracle Recovery Tools快速恢复ORA-19909

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

数据库服务器异常断电，数据库启动报ORA-01113 ORA-01110错误,无法正常open

Sun Jan 01 17:02:55 2023
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1652739647
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_4396.trc:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE11G\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open...

offline datafile 4，并open数据库

Sun Jan 01 20:36:22 2023
alter database datafile 4 offline drop
Completed: alter database datafile 4 offline drop

Sun Jan 01 20:37:40 2023
ALTER DATABASE OPEN
Thread 1 opened at log sequence 13068
  Current log# 3 seq# 13068 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
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
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
Completed: ALTER DATABASE OPEN

尝试recover datafile 4和online datafile 4失败

Sun Jan 01 22:33:19 2023
ALTER DATABASE RECOVER  datafile 4  
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 4 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 4  ...
Sun Jan 01 22:34:02 2023
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL 
ALTER DATABASE RECOVER  datafile 4  
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 4 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 4  ...
Sun Jan 01 22:34:15 2023
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
Errors with log E:\ORACLE11G\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2023_01_01\O1_MF_1_13067_%U_.ARC
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL 
Sun Jan 01 22:36:34 2023
alter database datafile 4 online
ORA-1113 signalled during: alter database datafile 4 online

在datafile 4 offline的情况下，resetlogs库

Sun Jan 01 23:50:01 2023
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 56 slaves
Sun Jan 01 23:50:02 2023
Warning: Datafile 4 (E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF) 
    is offline during full database recovery and will not be recovered
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER  database until cancel  
Sun Jan 01 23:50:15 2023
alter database open
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...
Sun Jan 01 23:50:34 2023
alter database open RESETLOGS
RESETLOGS after complete recovery through change 158902238
Resetting resetlogs activation ID 1504008459 (0x59a5590b)
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG'
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG'
Sun Jan 01 23:50:36 2023
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG'
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_ora_2480.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO03.LOG'
Sun Jan 01 23:50:38 2023
Setting recovery target incarnation to 3
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_m000_8340.trc:
ORA-00314: log 2 of thread 1, expected sequence# 13070 doesn't match 0
ORA-00312: online log 2 thread 1: 'E:\ORACLE11G\ORADATA\ORCL\REDO02.LOG'
Sun Jan 01 23:50:39 2023
Assigning activation ID 1652808490 (0x6283db2a)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: E:\ORACLE11G\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Jan 01 23:50:39 2023
SMON: enabling cache recovery
Checker run found 5 new persistent data failures
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
File #4 is offline, but is part of an online tablespace.
data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF'
Dictionary check complete
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
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Jan 01 23:50:47 2023
Completed: alter database open RESETLOGS

后续尝试恢复datafile 4报ORA-19909

Mon Jan 02 00:02:10 2023
alter database datafile 4 online
Completed: alter database datafile 4 online
Mon Jan 02 00:03:31 2023
ALTER DATABASE RECOVER  database using backup controlfile  
Media Recovery Start
 started logmerger process
Mon Jan 02 00:03:31 2023
Datafile 4 is on orphaned branch
          File status = 4
        Abs fuzzy SCN = 0
 Hot backup fuzzy SCN = 0
Media Recovery failed with error 19909
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_8868.trc:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 4 belongs to an orphan incarnation
ORA-01110: data file 4: 'E:\ORACLE11G\ORADATA\ORCL\USERS01.DBF'
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ...

通过Oracle Database Recovery Check检查发现,确实datafile 4的状态为:WRONG RESETLOGS
wrong-resetlogs

对于此类情况,参考：Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障快速解决
20230102161304
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Mon Jan 02 16:14:15 2023
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_10712.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
Mon Jan 02 16:14:29 2023
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Mon Jan 02 16:14:29 2023
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file e:\oracle11g\diag\rdbms\orcl\orcl\trace\orcl_pr00_20032.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
alter database open
Mon Jan 02 16:14:37 2023
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: H:\BAIDUNETDISK\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jan 02 16:14:37 2023
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         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 ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jan 02 16:14:37 2023
QMNC started with pid=22, OS id=14152 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

后续增加tempfile,导出数据完成本次恢复