标签归档：ORA-19909

resetlogs强制拉库失败并使用备份system文件还原数据库故障处理

发表于 2024 年 5 月 27 日 由 惜分飞

接手一个库,在open的过程中遭遇到ORA-600 2662错误

Sun May 26 10:15:54 2024
alter database open RESETLOGS
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 84303583
Clearing online redo logfile 1 /data/OracleData/xff/redo01.log
Clearing online log 1 of thread 1 sequence number 8330
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /data/OracleData/xff/redo02.log
Clearing online log 2 of thread 1 sequence number 8327
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /data/OracleData/xff/redo03.log
Clearing online log 3 of thread 1 sequence number 8329
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 /data/OracleData/xff/redo04.log
Clearing online log 4 of thread 1 sequence number 8328
Clearing online redo logfile 4 complete
Resetting resetlogs activation ID 1431370398 (0x5550fa9e)
Online log /data/OracleData/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /data/OracleData/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /data/OracleData/xff/redo03.log: Thread 1 Group 3 was previously cleared
Online log /data/OracleData/xff/redo04.log: Thread 1 Group 4 was previously cleared
Sun May 26 10:15:59 2024
Setting recovery target incarnation to 3
Sun May 26 10:15:59 2024
Read of datafile '/data/OracleData/xff/temp01.dbf' (fno 201) header failed with ORA-01200
Rereading datafile 201 header failed with ORA-01200
Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_dbw0_1563.trc:
ORA-01186: file 201 failed verification tests
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/data/OracleData/xff/temp01.dbf'
ORA-01200: actual file size of 3711 is smaller than correct size of 3712 blocks
File 201 not verified due to error ORA-01122
Sun May 26 10:15:59 2024
Assigning activation ID 1509069065 (0x59f29109)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /data/OracleData/xff/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun May 26 10:15:59 2024
SMON: enabling cache recovery
Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590.trc  (incident=163897):
ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] 
Incident details in:/data/u01/app/oracle/diag/rdbms/xff/xff/incident/incdir_163897/xff_ora_1590_i163897.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590.trc:
ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] 
Errors in file /data/u01/app/oracle/diag/rdbms/xff/xff/trace/xff_ora_1590.trc:
ORA-00600: internal error code, arguments: [2662], [0], [84303590], [0], [84314659], [12583040] 
Error 600 happened during db open, shutting down database
USER (ospid: 1590): terminating the instance due to error 600

然后客户使用备份的system01.dbf文件替换了被resetlogs之后文件,导致数据库后续操作无法继续

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'

这个问题比较简单,通过bbed或者Oracle Recovery Tools修改文件头相关信息,然后open数据库成功
重建控制文件丢失数据文件导致悲剧
Oracle Recovery Tools快速恢复ORA-19909

SQL> recover datafile 1;
Media recovery complete.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

但是由于system文件有大量坏块导致数据库无法正常登录和导出

[oracle@et-dbserver ~]$ exp "'/ as sysdba'" owner=USERNAME  file=/tmp/2user.dmp log=/tmp/2user.log 

Export: Release 11.2.0.4.0 - Production on Sun May 26 13:00:50 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86500)
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'
Username: / as sysdba

EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86500)
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'
Username:
Password:

EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86500)
ORA-01110: data file 1: '/data/OracleData/xff/system01.dbf'
ORA-01017: invalid username/password; logon denied
EXP-00005: all allowable logon attempts failed
EXP-00000: Export terminated unsuccessfully

通过dbv检查system数据文件

DBVERIFY: Release 11.2.0.4.0 - Production on Sun May 26 12:33:28 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


DBVERIFY - Verification starting : FILE = /data/OracleData/xff/system01.dbf
Page 1044 is influx - most likely media corrupt
Corrupt block relative dba: 0x00400414 (file 1, block 1044)
Fractured block found during dbv: 
Data in bad block:
 type: 0 format: 2 rdba: 0x00400414
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1d7f550b
 check value in block header: 0xa354
 computed block checksum: 0x6830

Page 1103 is marked corrupt
Corrupt block relative dba: 0x0040044f (file 1, block 1103)
Bad header found during dbv: 
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x508f.5f74492e seq: 0x53 flg: 0x0c
 spare1: 0xc spare2: 0xa6 spare3: 0xc757
 consistency value in tail: 0x00000001
 check value in block header: 0x8925
 computed block checksum: 0x5d3b

Page 1143 is marked corrupt
Corrupt block relative dba: 0x00400477 (file 1, block 1143)
Bad header found during dbv: 
Data in bad block:
 type: 0 format: 0 rdba: 0x00000001
 last change scn: 0x65c4.52eb202e seq: 0x28 flg: 0x0e
 spare1: 0xe spare2: 0xe2 spare3: 0xfa46
 consistency value in tail: 0x00000001
 check value in block header: 0x6405
 computed block checksum: 0x28b1

………………

Page 124805 is influx - most likely media corrupt
Corrupt block relative dba: 0x0041e785 (file 1, block 124805)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0041e785
 last change scn: 0x0000.0434fc6c seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1991255b
 check value in block header: 0x6386
 computed block checksum: 0x1384


DBVERIFY - Verification complete

Total Pages Examined         : 130560
Total Pages Processed (Data) : 95634
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 14949
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1774
Total Pages Processed (Seg)  : 1669
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 16251
Total Pages Marked Corrupt   : 283
Total Pages Influx           : 149
Total Pages Encrypted        : 0
Highest block SCN            : 84314727 (0.84314727)

对于这样问题,通过Oracle Recovery Tools实战批量坏块修复,实现数据库可以完美导出数据

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

Oracle Recovery Tools快速恢复ORA-19909

发表于 2023 年 1 月 2 日 由 惜分飞

数据库服务器异常断电，数据库启动报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,导出数据完成本次恢复

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

ORA-600 3600恢复—-resetlogs scn异常

发表于 2022 年 1 月 28 日 由 惜分飞

由于客户误操作,在有文件offline的情况下进行resetlogs操作,导致有文件resetlogs scn不对
20220128000040

尝试offline异常文件,均报ORA-600 3600

---直接offline
Wed Jan 26 11:08:15 2022
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Wed Jan 26 11:08:17 2022
Datafile 8 (ckpscn 731239901) is orphaned on incarnation#=2
Media Recovery failed with error 19909
Slave exiting with ORA-283 exception
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_pr00_133504.trc:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 8 belongs to an orphan incarnation
ORA-01110: data file 8: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\XIFENFEI.DBF'
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
Wed Jan 26 11:08:31 2022
alter database datafile 8 offline
Wed Jan 26 11:08:31 2022
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133948.trc  (incident=134637):
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_134637\orcl_dbw0_133948_i134637.trc
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133948.trc:
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
DBW0 (ospid: 133948): terminating the instance due to error 471
Instance terminated by DBW0, pid = 133948

---offline drop
Wed Jan 26 11:09:20 2022
alter database datafile 8 offline drop
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133932.trc  (incident=135837):
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_135837\orcl_dbw0_133932_i135837.trc
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133932.trc:
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
DBW0 (ospid: 133932): terminating the instance due to error 471
Wed Jan 26 11:09:22 2022
Instance terminated by DBW0, pid = 133932

因为resetlogs scn不对,也无法正常重建控制文件,对于这样的case,可以Oracle Recovery Tools进行修复resetlogs scn,然后直接open库

Wed Jan 26 11:15:12 2022
SMON: enabling cache recovery
Dictionary check beginning
Archived Log entry 3 added for thread 1 sequence 381 ID 0x60b930a1 dest 1:
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process QMNC
Wed Jan 26 11:15:15 2022
QMNC started with pid=25, OS id=131784 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

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

发表在 Oracle | 标签为 , , | 评论关闭