Category Archives: 非常规恢复

ORA-39773: parse of metadata stream failed故障处理

Posted on by

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

标题：ORA-39773: parse of metadata stream failed故障处理

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

客户expdp导出数据,在写入生成SYS_EXPORT_SCHEMA表所在的users表空间不足,导致expdp报部分异常

Export: Release 11.2.0.4.0 - Production on Thu Feb 27 15:12:36 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "XFF"."SYS_EXPORT_SCHEMA_95":XFF/**** directory=DUMP dumpfile=20250227.dmp logfile=20250227.log schemas=XFF 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table XFF.SYS_EXPORT_SCHEMA_95 by 128 in tablespace USERS
Total estimation using BLOCKS method: 481.9 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
. . exported "XFF"."PUB_WORKINGTASKLOG"              88.09 GB  112681 rows
. . exported "XFF"."SM_BUSILOG_DEFAULT"              51.94 GB 3149092 rows
. . exported "XFF"."FFW_DISTRIBUTESUBTASK"           46.47 GB  552214 rows
. . exported "XFF"."GL_DETAIL"                       11.32 GB 16214805 rows
…………
. . exported "XFF"."ZDP_10000000RR8NKX"                  0 KB       0 rows
. . exported "XFF"."ZDP_10000000RTB1GI"                  0 KB       0 rows
. . exported "XFF"."ZDP_10000000RTB1GK"                  0 KB       0 rows
Master table "XFF"."SYS_EXPORT_SCHEMA_95" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_SCHEMA_95 is:
  /rman_bak/dump/20200227.dmp
Job "XFF"."SYS_EXPORT_SCHEMA_95" completed with 10 error(s) at Thu Feb 27 19:32:22 2025 elapsed 0 04:16:28

尝试把该dmp导入数据库,报ORA-31694/ORA-02354/ORA-39773错误

Import: Release 11.2.0.4.0 - Production on 星期日 3月 16 02:38:14 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31694: 加载/卸载主表 "XFF"."SYS_IMPORT_FULL_01" 失败
ORA-02354: 导出/导入数据时出错
ORA-39773: parse of metadata stream failed

客户需要恢复其中的GL_DETAIL表数据,通过dul实现expdp dump文件转换sqlldr格式方法进行恢复,实现数据完美恢复
QQ20250322-211130

对于dmp(exp/expdp)文件,我们可以实现比较完美的恢复,最后限度抢救数据.如果你有oracle expdp/exp dmp被加密或者破坏,无法正常导入数据库,可以联系我们对其进行恢复处理:提供(ORACLE数据库恢复技术支持)：
Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com

aix磁盘损坏oracle数据库恢复

Posted on by

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

标题：aix磁盘损坏oracle数据库恢复

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

客户aix环境硬盘异常导致系统无法启动,初步判断是数据文件存放在本地磁盘的空间中(本地两个盘都异常,系统无法启动),通过硬件恢复厂商镜像出来,但是通过aix文件系统直接挂载提示需要fsck,但是做fsck之后,提示大量文件丢失(最关键的数据文件和备份文件都被自动删除)
dmp-remove
fsck-remove

基于这种情况,采用镜像主机挂载的方式肯定不行,考虑直接采用软件直接解析,能够看到软件,可惜由于大量的文件系统元数据损坏,解析出来的数据文件和dmp也不可用(大量损坏和空块)
QQ20250307-122939
基于上述情况,只能采用碎片级别恢复出来数据文件
QQ20250307-123123
然后使用dul工具把数据恢复到表中,实现最大限度抢救客户数据
QQ20250307-123653
对于数据库级别恢复,这个是理论上的终极恢复方法

ORA-01092 ORA-00604 ORA-01558故障处理

Posted on by

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

标题：ORA-01092 ORA-00604 ORA-01558故障处理

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

客户一个11.2.0.1的库,在重启之前报ORA-00604和ORA-01558: out of transaction ID’s in rollback segment SYSTEM错误

Sat Mar 16 05:51:44 2024
Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_smon_47709.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTEM
Sat Mar 16 05:51:55 2024
Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_smon_47709.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTEM

数据库关闭之后无法open,报ORA-01092 ORA-00604 ORA-01558错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTEM
Process ID: 44308
Session ID: 769 Serial number: 3

对应的alert日志信息

Fri Aug 30 20:05:40 2024
alter database open
Beginning crash recovery of 1 threads
Fri Aug 30 20:06:08 2024
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 7583120, block 130906, scn 832944617073
Recovery of Online Redo Log: Thread 1 Group 6 Seq 7583120 Reading mem 0
  Mem# 0: /OracleData/db_mc/xff/redo06.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 7583120, block 130906, scn 832944637074
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Fri Aug 30 20:06:09 2024
Thread 1 advanced to log sequence 7583121 (thread open)
Thread 1 opened at log sequence 7583121
  Current log# 4 seq# 7583121 mem# 0: /OracleData/db_mc/xff/redo04.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Aug 30 20:06:09 2024
SMON: enabling cache recovery
Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_ora_6066.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTEM
Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_ora_6066.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01558: out of transaction ID's in rollback segment SYSTEM
Error 604 happened during db open, shutting down database
USER (ospid: 6066): terminating the instance due to error 604
Instance terminated by USER, pid = 6066
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (6066) as a result of ORA-1092

通过对异常block进行dump

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0xfffffffe  0x0059  0x00c1.ee6ab58f  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x01    9    0x00  0xfffffffe  0x0008  0x00c1.ee6ab5a7  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x02    9    0x00  0xfffffffe  0x005b  0x00c1.ee6ab59f  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x03    9    0x00  0xfffffffe  0x0007  0x00c1.ee6ab5b5  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x04    9    0x00  0xfffffffe  0x005f  0x00c1.ee6ab593  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x05    9    0x00  0xfffffffe  0x000a  0x00c1.ee6ab5ad  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x06    9    0x00  0xfffffffe  0x0019  0x00c1.ee6ab5dd  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x07    9    0x00  0xfffffffe  0x0013  0x00c1.ee6ab5b7  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x08    9    0x00  0xfffffffe  0x0035  0x00c1.ee6ab5a9  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x09    9    0x00  0xfffffffe  0x0002  0x00c1.ee6ab59d  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x0a    9    0x00  0xfffffffe  0x003c  0x00c1.ee6ab5af  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x0b    9    0x00  0xfffffffe  0x0011  0x00c1.ee6ab5d7  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x0c    9    0x00  0xfffffffe  0x0026  0x00c1.ee6ab605  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x0d    9    0x00  0xfffffffe  0x000f  0x00c1.ee6ab5c5  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x0e    9    0x00  0xfffffffe  0x0014  0x00c1.ee6ab5d3  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x0f    9    0x00  0xfffffffe  0x0012  0x00c1.ee6ab5c7  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x10    9    0x00  0xfffffffe  0x0029  0x00c1.ee6ab5c1  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x11    9    0x00  0xfffffffe  0x0018  0x00c1.ee6ab5d9  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x12    9    0x00  0xfffffffe  0x001e  0x00c1.ee6ab5c9  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x13    9    0x00  0xfffffffe  0x001b  0x00c1.ee6ab5b9  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x14    9    0x00  0xfffffffe  0x000b  0x00c1.ee6ab5d5  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x15    9    0x00  0xfffffffe  0x0003  0x00c1.ee6ab5b3  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x16    9    0x00  0xfffffffe  0x0023  0x00c1.ee6ab5f1  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x17    9    0x00  0xfffffffe  0x0021  0x00c1.ee6ab5bd  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x18    9    0x00  0xfffffffe  0x0006  0x00c1.ee6ab5db  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x19    9    0x00  0xfffffffe  0x0049  0x00c1.ee6ab5df  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x1a    9    0x00  0xfffffffe  0x0043  0x00c1.ee6ab617  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x1b    9    0x00  0xfffffffe  0x0017  0x00c1.ee6ab5bb  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x1c    9    0x00  0xfffffffe  0x002e  0x00c1.ee6ab5e9  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x1d    9    0x00  0xfffffffe  0x001c  0x00c1.ee6ab5e7  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x1e    9    0x00  0xfffffffe  0x002a  0x00c1.ee6ab5cb  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x1f    9    0x00  0xffffff28  0x003e  0x00c1.ee6ab62d  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x20    9    0x00  0xfffffffe  0x0022  0x00c1.ee6ab5cf  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x21    9    0x00  0xfffffffe  0x0010  0x00c1.ee6ab5bf  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x22    9    0x00  0xfffffffe  0x000e  0x00c1.ee6ab5d1  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x23    9    0x00  0xfffffffe  0x0025  0x00c1.ee6ab5f3  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x24    9    0x00  0xfffffffe  0x0032  0x00c1.ee6ab5f7  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x25    9    0x00  0xfffffffd  0x0024  0x00c1.ee6ab5f5  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x26    9    0x00  0xfffffffe  0x003d  0x00c1.ee6ab607  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x27    9    0x00  0xfffffffe  0x0041  0x00c1.ee6ab63b  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x28    9    0x00  0xfffffffe  0x003f  0x00c1.ee6ab613  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x29    9    0x00  0xfffffffb  0x000d  0x00c1.ee6ab5c3  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x2a    9    0x00  0xfffffffe  0x0020  0x00c1.ee6ab5cd  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x2b    9    0x00  0xfffffffe  0x0037  0x00c1.ee6ab60d  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x2c    9    0x00  0xfffffffe  0x002d  0x00c1.ee6ab5ed  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x2d    9    0x00  0xfffffffe  0x0016  0x00c1.ee6ab5ef  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x2e    9    0x00  0xfffffffe  0x002c  0x00c1.ee6ab5eb  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x2f    9    0x00  0xfffffffe  0x0034  0x00c1.ee6ab5ff  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x30    9    0x00  0xfffffffe  0x001f  0x00c1.ee6ab62b  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x31    9    0x00  0xfffffffe  0x000c  0x00c1.ee6ab603  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x32    9    0x00  0xfffffffe  0x003b  0x00c1.ee6ab5f9  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x33    9    0x00  0xfffffffa  0x0038  0x00c1.ee6ab61b  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x34    9    0x00  0xfffffffe  0x0031  0x00c1.ee6ab601  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x35    9    0x00  0xfffffffd  0x0005  0x00c1.ee6ab5ab  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x36    9    0x00  0xfffffffe  0x0028  0x00c1.ee6ab611  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x37    9    0x00  0xfffffffe  0x0036  0x00c1.ee6ab60f  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x38    9    0x00  0xfffffffe  0x0048  0x00c1.ee6ab61d  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x39    9    0x00  0xfffffffe  0x0052  0x00c1.ee6ab64d  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x3a    9    0x00  0xfffffffe  0x002f  0x00c1.ee6ab5fd  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x3b    9    0x00  0xfffffffe  0x003a  0x00c1.ee6ab5fb  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x3c    9    0x00  0xfffffffd  0x0015  0x00c1.ee6ab5b1  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x3d    9    0x00  0xfffffffe  0x0061  0x00c1.ee6ab609  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x3e    9    0x00  0xfffffffe  0x004d  0x00c1.ee6ab62f  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x3f    9    0x00  0xfffffffe  0x001a  0x00c1.ee6ab615  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x40    9    0x00  0xfffffffd  0x001d  0x00c1.ee6ab5e5  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x41    9    0x00  0xfffffffe  0x0058  0x00c1.ee6ab63d  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x42    9    0x00  0xfffffffe  0x0027  0x00c1.ee6ab639  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x43    9    0x00  0xfffffffe  0x0033  0x00c1.ee6ab619  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x44    9    0x00  0xfffffffe  0x0050  0x00c1.ee6ab635  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x45    9    0x00  0xfffffffe  0x0044  0x00c1.ee6ab633  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x46    9    0x00  0xfffffffe  0x0057  0x00c1.ee6ab625  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x47    9    0x00  0xfffffffe  0x0030  0x00c1.ee6ab629  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x48    9    0x00  0xfffffffe  0x004c  0x00c1.ee6ab61f  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x49    9    0x00  0xfffffffd  0x004e  0x00c1.ee6ab5e1  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x4a    9    0x00  0xfffffffe  0x0054  0x00c1.ee6ab641  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x4b    9    0x00  0xfffffffe  0x0046  0x00c1.ee6ab623  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x4c    9    0x00  0xfffffffe  0x004b  0x00c1.ee6ab621  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x4d    9    0x00  0xfffffffe  0x0045  0x00c1.ee6ab631  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x4e    9    0x00  0xfffffffd  0x0040  0x00c1.ee6ab5e3  0x03000437  0x0000.000.00000000  0x00000001   0x00000000
   0x4f    9    0x00  0xfffffffd  0x005a  0x00c1.ee6ab599  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x50    9    0x00  0xfffffffe  0x0042  0x00c1.ee6ab637  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x51    9    0x00  0xfffffffe  0x0053  0x00c1.ee6ab645  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x52    9    0x00  0xfffffffe  0x0060  0x00c1.ee6ab64f  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x53    9    0x00  0xfffffffe  0x0056  0x00c1.ee6ab647  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x54    9    0x00  0xfffffffe  0x0051  0x00c1.ee6ab643  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x55    9    0x00  0xfffffffe  0x0039  0x00c1.ee6ab64b  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x56    9    0x00  0xfffffffe  0x0055  0x00c1.ee6ab649  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x57    9    0x00  0xfffffffe  0x0047  0x00c1.ee6ab627  0x03000438  0x0000.000.00000000  0x00000001   0x00000000
   0x58    9    0x00  0xfffffffe  0x004a  0x00c1.ee6ab63f  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x59    9    0x00  0xfffffffd  0x0004  0x00c1.ee6ab591  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x5a    9    0x00  0xfffffffd  0x0009  0x00c1.ee6ab59b  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x5b    9    0x00  0xfffffffd  0x005e  0x00c1.ee6ab5a1  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x5c    9    0x00  0xfffffffd  0x0001  0x00c1.ee6ab5a5  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x5d    9    0x00  0xfffffffd  0x004f  0x00c1.ee6ab597  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x5e    9    0x00  0xfffffffd  0x005c  0x00c1.ee6ab5a3  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x5f    9    0x00  0xfffffffd  0x005d  0x00c1.ee6ab595  0x03000436  0x0000.000.00000000  0x00000001   0x00000000
   0x60    9    0x00  0xfffffffe  0xffff  0x00c1.ee6ab651  0x03000439  0x0000.000.00000000  0x00000001   0x00000000
   0x61    9    0x00  0xfffffdb4  0x002b  0x00c1.ee6ab60b  0x03000438  0x0000.000.00000000  0x00000001   0x00000000

确实是Wrap#达到了极限值,通过bbed也可以进一步确认

BBED> p ktuxe[1]
struct ktuxe[1], 40 bytes                   @4292
   ub4 ktuxexid                             @4292     0xfffffffe
   ub4 ktuxebrb                             @4296     0x03000436
   struct ktuxescn, 8 bytes                 @4300
      ub4 kscnbas                           @4300     0xee6ab5a7
      ub2 kscnwrp                           @4304     0x00c1
   sb4 ktuxesta                             @4308     524297 (KTU_SCO, KTU_DEAD)
   ub1 ktuxecfl                             @4309     0x00
   sb2 ktuxeuel                             @4310     8

BBED> p ktuxe[2]
struct ktuxe[2], 40 bytes                   @4332
   ub4 ktuxexid                             @4332     0xfffffffe
   ub4 ktuxebrb                             @4336     0x03000436
   struct ktuxescn, 8 bytes                 @4340
      ub4 kscnbas                           @4340     0xee6ab59f
      ub2 kscnwrp                           @4344     0x00c1
   sb4 ktuxesta                             @4348     5963785 (KTU_SCO, KTU_DEAD)
   ub1 ktuxecfl                             @4349     0x00
   sb2 ktuxeuel                             @4350     91

处理方法,通过bbed对异常的block进行编辑,修改Wrap#的值,重新dumpblock进行确认

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0015  0x0003  0x0000.11e1a313  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x01    9    0x00  0x0015  0x0007  0x0000.11e1a317  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x02    9    0x00  0x0015  0x000c  0x0000.11e32087  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x03    9    0x00  0x0015  0x005a  0x0000.11e1a314  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x04    9    0x00  0x0015  0x0060  0x0000.11e3206e  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x05    9    0x00  0x0015  0x005d  0x0000.11e1a311  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x06    9    0x00  0x0015  0x0015  0x0000.11e3208b  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x07    9    0x00  0x0015  0x0004  0x0000.11e1a318  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x08    9    0x00  0x0015  0x0014  0x0000.11e32084  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x09    9    0x00  0x0015  0x0001  0x0000.11e1a316  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x0a    9    0x00  0x0015  0x0013  0x0000.11e3207d  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x0b    9    0x00  0x0015  0x0011  0x0000.11e3207f  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x0c    9    0x00  0x0015  0x0059  0x0000.11e32089  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x0d    9    0x00  0x0015  0x000a  0x0000.11e3207c  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x0e    9    0x00  0x0015  0x0017  0x0000.11e32082  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x0f    9    0x00  0x0015  0x002a  0x0000.120964dc  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x10    9    0x00  0x0015  0x001a  0x0000.11e3208d  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x11    9    0x00  0x0015  0x005f  0x0000.11e32080  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x12    9    0x00  0x0015  0x0028  0x0000.120964d9  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x13    9    0x00  0x0015  0x000b  0x0000.11e3207e  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x14    9    0x00  0x0015  0x0018  0x0000.11e32085  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x15    9    0x00  0x0015  0x0010  0x0000.11e3208c  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x16    9    0x00  0x0015  0x0032  0x0000.120964e2  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x17    9    0x00  0x0015  0x0008  0x0000.11e32083  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x18    9    0x00  0x0015  0x0002  0x0000.11e32086  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x19    9    0x00  0x0015  0x0020  0x0000.120964d3  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x1a    9    0x00  0x0015  0x0025  0x0000.120964c5  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x1b    9    0x00  0x0015  0x0021  0x0000.120964d6  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x1c    9    0x00  0x0015  0x001b  0x0000.120964d5  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x1d    9    0x00  0x0015  0x0019  0x0000.120964d2  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x1e    9    0x00  0x0015  0x0030  0x0000.1210ab57  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x1f    9    0x00  0x0015  0x000f  0x0000.120964db  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x20    9    0x00  0x0015  0x001c  0x0000.120964d4  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x21    9    0x00  0x0015  0x0012  0x0000.120964d8  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x22    9    0x00  0x0015  0x0029  0x0000.120964de  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x23    9    0x00  0x0015  0x0031  0x0000.120964e4  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x24    9    0x00  0x0015  0x001d  0x0000.120964d1  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x25    9    0x00  0x0014  0x0024  0x0000.120964cf  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x26    9    0x00  0x0015  0x003b  0x0000.1210ab5b  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x27    9    0x00  0x0014  0x0058  0x0000.0ec9fb55  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x28    9    0x00  0x0015  0x001f  0x0000.120964da  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x29    9    0x00  0x0015  0x002b  0x0000.120964df  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x2a    9    0x00  0x0015  0x0022  0x0000.120964dd  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x2b    9    0x00  0x0015  0x002d  0x0000.120964e0  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x2c    9    0x00  0x0015  0x003d  0x0000.1210ab60  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x2d    9    0x00  0x0015  0x0016  0x0000.120964e1  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x2e    9    0x00  0x0014  0x003c  0x0000.0ec9fb51  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x2f    9    0x00  0x0015  0x0045  0x0000.1210ab65  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x30    9    0x00  0x0015  0x0034  0x0000.1210ab58  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x31    9    0x00  0x0015  0x0036  0x0000.1210ab4b  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x32    9    0x00  0x0015  0x0023  0x0000.120964e3  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x33    9    0x00  0x0015  0x0041  0x0000.1210ab67  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x34    9    0x00  0x0015  0x003a  0x0000.1210ab59  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x35    9    0x00  0x0015  0x002c  0x0000.1210ab5e  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x36    9    0x00  0x0015  0x001e  0x0000.1210ab55  0x00400225  0x0000.000.00000000  0x00000001   0x00000000
   0x37    9    0x00  0x0014  0x0050  0x0000.0ec9fb53  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x38    9    0x00  0x0015  0x003f  0x0000.1210ab62  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x39    9    0x00  0x0015  0x0035  0x0000.1210ab5d  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x3a    9    0x00  0x0015  0x0026  0x0000.1210ab5a  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x3b    9    0x00  0x0015  0x0039  0x0000.1210ab5c  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x3c    9    0x00  0x0014  0x0037  0x0000.0ec9fb52  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x3d    9    0x00  0x0015  0x0038  0x0000.1210ab61  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x3e    9    0x00  0x0014  0x0040  0x0000.0ec9fb49  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x3f    9    0x00  0x0015  0x0044  0x0000.1210ab63  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x40    9    0x00  0x0014  0x004b  0x0000.0ec9fb4a  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x41    9    0x00  0x0015  0x0043  0x0000.1210ab68  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x42    9    0x00  0x0015  0xffff  0x0000.1210ab6b  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x43    9    0x00  0x0015  0x0042  0x0000.1210ab69  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x44    9    0x00  0x0015  0x002f  0x0000.1210ab64  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x45    9    0x00  0x0015  0x0033  0x0000.1210ab66  0x00400226  0x0000.000.00000000  0x00000001   0x00000000
   0x46    9    0x00  0x0014  0x004e  0x0000.0ec9fb4e  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x47    9    0x00  0x0014  0x004f  0x0000.0ec9fb4c  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x48    9    0x00  0x0014  0x0054  0x0000.11e1a306  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x49    9    0x00  0x0014  0x0055  0x0000.0ec9fd5b  0x00400223  0x0000.000.00000000  0x00000002   0x00000000
   0x4a    9    0x00  0x0014  0x003e  0x0000.0ec9fb48  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x4b    9    0x00  0x0014  0x0047  0x0000.0ec9fb4b  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x4c    9    0x00  0x0014  0x000d  0x0000.11e3207b  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x4d    9    0x00  0x0014  0x0061  0x0000.11e1a30f  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x4e    9    0x00  0x0014  0x002e  0x0000.0ec9fb4f  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x4f    9    0x00  0x0014  0x0046  0x0000.0ec9fb4d  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x50    9    0x00  0x0014  0x0027  0x0000.0ec9fb54  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x51    9    0x00  0x0014  0x0053  0x0000.11e1a30a  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x52    9    0x00  0x0014  0x005b  0x0000.11e1a30c  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x53    9    0x00  0x0014  0x0052  0x0000.11e1a30b  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x54    9    0x00  0x0014  0x0057  0x0000.11e1a307  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x55    9    0x00  0x0014  0x0048  0x0000.11e1a304  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x56    9    0x00  0x0014  0x0051  0x0000.11e1a309  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x57    9    0x00  0x0014  0x0056  0x0000.11e1a308  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x58    9    0x00  0x0014  0x0049  0x0000.0ec9fb56  0x00400222  0x0000.000.00000000  0x00000001   0x00000000
   0x59    9    0x00  0x0014  0x0006  0x0000.11e3208a  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x5a    9    0x00  0x0014  0x0009  0x0000.11e1a315  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x5b    9    0x00  0x0014  0x005e  0x0000.11e1a30d  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x5c    9    0x00  0x0014  0x004c  0x0000.11e3207a  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x5d    9    0x00  0x0014  0x0000  0x0000.11e1a312  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x5e    9    0x00  0x0014  0x004d  0x0000.11e1a30e  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x5f    9    0x00  0x0014  0x000e  0x0000.11e32081  0x00400224  0x0000.000.00000000  0x00000001   0x00000000
   0x60    9    0x00  0x0014  0x005c  0x0000.11e32078  0x00400223  0x0000.000.00000000  0x00000001   0x00000000
   0x61    9    0x00  0x0014  0x0005  0x0000.11e1a310  0x00400223  0x0000.000.00000000  0x00000001   0x00000000

直接启动数据库成功

[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 21 13:45:23 2024

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size		    2215064 bytes
Variable Size		 2483028840 bytes
Database Buffers	 6039797760 bytes
Redo Buffers		   26533888 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;       

Database altered.

由于客户遇到故障之后,第一时间保护了现场,没有进行二次破坏,使用bbed进行修改block,实现完美恢复.
以前有过一次在11.2.0.4的环境中恢复的同样案例:ORA-01558: out of transaction ID’s in rollback segment SYSTEM
通过查询mos,确认类似Bug 19700135 – ORA-600 [4187] when the undo segment wrap# is close to the max value of 0xffffffff (Doc ID 19700135.8)
bug-19700135

Description
ORA-600 [4187] can occur for undo segments where wrap# is close to the max value of 0xffffffff (KSQNMAXVAL).
This normally affects databases with high transaction rate that have existed for a relatively long time.
 
To identify undo segments causing the above error and others that may potentially cause it 
in the future, run the next query:
 
 select b.segment_name, b.tablespace_name 
         ,a.ktuxeusn "Undo Segment Number"
         ,a.ktuxeslt "Slot"
         ,a.ktuxesqn "Wrap#"
   from  x$ktuxe a, dba_rollback_segs b
   where a.ktuxesqn > -429496730 and a.ktuxesqn < 0
       and a.ktuxeusn = b.segment_id;
 
Then drop the undo segments or the undo tablespace from the output above.
 
With this fix in place an error ORA-1558 is eventually produced for the affected undo segment
which still requires dropping the undo segment:
  ORA-1558 "out of transaction ID's in rollback segment %s"
   Cause: All the available transaction id's have been used
   Action: Shutdown the instance and restart using other rollback segment(s),
                then drop the rollback segment that has no more transaction id's.
 
Workaround
 Once the ORA-600 [4187] is produced, drop the affected undo segment or undo tablespace.

由于本次故障是在system的rollback回滚段上,无法删除该回滚段.所以处理起来比较棘手.

rm -rf误删Oracle数据库恢复

Posted on by

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

标题：rm -rf误删Oracle数据库恢复

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

有客户把虚拟化环境中装有oracle数据库的linux操作系统,由于操作失误在/下面执行了rm -rf *，导致所有文件被删除,系统无法启动.客户希望要求恢复出其中的Oracle数据库.由于是虚拟化环境,然后客户直接从虚拟化平台下载下来磁盘文件,通过工具加载和分析确认是一个xfs的文件系统
20240516190746

使用工具进一步扫描分析,找到部分数据文件
20240516190505
这里可以获取到两个信息:
1. 尝试恢复oracle的control01.ctl文件,然后通过该文件尝试分析其他数据文件位置,运气不错该文件恢复出来是好的,直接加载到新库查询v$datafile,分析出来所有数据文件信息
2. 这里有一个非常不幸的信息,oracle最核心的system01.dbf文件大小明显异常,进一步分析该文件信息,结论是该文件无法通过反删除方式进行恢复
20240515223607
先把可以os层面可以恢复的数据恢复出来,并且检查坏块情况
20240516191836
对于异常的system文件,有两个处理方法:
1. 通过阅览被删除的文件，发现客户有5月14日1点左右的rman备份,通过恢复软件中完整度提示,大概率应该没有什么问题,但是分析发现部分归档日志损坏无法完整恢复
2. 通过对磁盘做碎片,恢复出来该数据文件,参考以往文章:
dbca删除库和rm删库恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
通过这个方法运气不错,恢复出来该库的system01.dbf文件非常完整0丢失

[oracle@localhost oradata]$ dbv file=system01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Thu May 15 23:26:57 2024

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

DBVERIFY - Verification starting : FILE = /u01/oradata/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 199680
Total Pages Processed (Data) : 113988
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 26869
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 40253
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18570
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658228557 (0.658228557)

完成上述恢复工作之后,目前确认只有sysaux01.dbf有8026个block损坏,但是该表空间不涉及业务数据,尝试在新的系统中直接修改路径并open库

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


SQL> alter database flashback off;

Database altered.

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

Database altered.

运气不错,数据库直接open成功,现在处理sysaux01.dbf中的损坏文件:
1. 确认该文件具体坏块开始位置:
20240516193650

2. 由于坏块在文件中比较靠后,分析实际存储数据最后的位置

SQL> select max(block_id+blocks) from dba_extents where file_id=3;

MAX(BLOCK_ID+BLOCKS)
--------------------
             3493120

最后存储数据的位置小于坏块的位置,证明坏块部分是没有存储数据的,直接resize掉坏块部分

SQL> alter database datafile '/u01/oradata/sysaux01.dbf' resize 27290m;

Database altered.

然后dbv该数据文件,确认没有任何问题

[oracle@localhost trace]$ dbv file=/u01/oradata/sysaux01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Wed May 15 22:43:00 2024

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

DBVERIFY - Verification starting : FILE = /u01/oradata/sysaux01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 3493120
Total Pages Processed (Data) : 1516833
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1868832
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 56577
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 32107
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18771
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 658223915 (0.658223915)

使用rman检测全库,也确定没有任何问题

[oracle@localhost trace]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 15 22:43:58 2024
Version 19.15.0.0.0

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

connected to target database: XIFENFEI (DBID=2912535091)

RMAN> 

RMAN> 

RMAN> backup validate check logical database skip inaccessible;

Starting backup at 15-MAY-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=278 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
………………
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
32   OK     0              6273         6400            370625094 
  File Name: /u01/oradata/xff_com.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              127             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
33   OK     0              163752       832000          627920639 
  File Name: /u01/oradata/XFF_DATA_202312231.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              374296          
  Index      0              285002          
  Other      0              8950            

Finished backup at 15-MAY-24

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 15 22:47:44 2024
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> select * from v$database_block_corruption ;

no rows selected

SQL>

至此对于这次rm -rf /*的故障实现了Oracle数据库完美恢复,数据0丢失.

asm disk被加入到另外一个磁盘组故障恢复

Posted on by

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

标题：asm disk被加入到另外一个磁盘组故障恢复

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

有朋友在aix环境对其中一个rac的asm磁盘组进行扩容
add_disk

之后另外一套rac的磁盘组直接dismount

Wed Aug 23 12:44:02 2023
NOTE: SMON starting instance recovery for group DATA domain 2 (mounted)
NOTE: F1X0 found on disk 0 au 2 fcn 0.128808679
NOTE: SMON skipping disk 7 - no header
NOTE: cache initiating offline of disk 7 group DATA
NOTE: process _smon_+asm1 (1770932) initiating offline of disk 7.3422955792 (DATA_0007) with mask 0x7e in group 2
NOTE: initiating PST update: grp = 2, dsk = 7/0xcc062910, mask = 0x6a, op = clear
Wed Aug 23 12:44:02 2023
GMON updating disk modes for group 2 at 7 for pid 17, osid 1770932
ERROR: Disk 7 cannot be offlined, since diskgroup has external redundancy.
ERROR: too many offline disks in PST (grp 2)
Wed Aug 23 12:44:02 2023
NOTE: cache dismounting (not clean) group 2/0x7FE6D808 (DATA) 
WARNING: Offline for disk DATA_0007 in mode 0x7f failed.
Wed Aug 23 12:44:02 2023
NOTE: halting all I/Os to diskgroup 2 (DATA)
ERROR: No disks with F1X0 found on disk group DATA
NOTE: aborting instance recovery of domain 2 due to diskgroup dismount
NOTE: SMON skipping lock domain (2) validation because diskgroup being dismounted
Abort recovery for domain 2
Wed Aug 23 12:44:02 2023
ERROR: ORA-15130 in COD recovery for diskgroup 2/0x7fe6d808 (DATA)
ERROR: ORA-15130 thrown in RBAL for group number 2
Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_rbal_2360526.trc:
ORA-15130: diskgroup "DATA" is being dismounted
[

再次尝试mount该磁盘组,报ORA-15042和ORA-15038错误

SQL> alter diskgroup data mount 
NOTE: cache registered group DATA number=2 incarn=0x79e6d861
NOTE: cache began mount (first) of group DATA number=2 incarn=0x79e6d861
NOTE: Assigning number (2,0) to disk (/dev/rhdisk31)
NOTE: Assigning number (2,3) to disk (/dev/rhdisk33)
NOTE: Assigning number (2,4) to disk (/dev/rhdisk34)
NOTE: Assigning number (2,5) to disk (/dev/rhdisk35)
NOTE: Assigning number (2,6) to disk (/dev/rhdisk36)
NOTE: Assigning number (2,9) to disk (/dev/rhdisk39)
NOTE: Assigning number (2,1) to disk (/dev/rhdisk8)
NOTE: Assigning number (2,2) to disk (/dev/rhdisk9)
Wed Aug 23 12:58:46 2023
NOTE: GMON heartbeating for grp 2
GMON querying group 2 at 11 for pid 27, osid 3736034
NOTE: Assigning number (2,7) to disk ()
NOTE: Assigning number (2,8) to disk ()
GMON querying group 2 at 12 for pid 27, osid 3736034
NOTE: cache dismounting (clean) group 2/0x79E6D861 (DATA) 
NOTE: messaging CKPT to quiesce pins Unix process pid: 3736034, image: oracle@hbbz01 (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 2/0x79E6D861 (DATA) 
NOTE: cache ending mount (fail) of group DATA number=2 incarn=0x79e6d861
NOTE: cache deleting context for group DATA 2/0x79e6d861
GMON dismounting group 2 at 13 for pid 27, osid 3736034
NOTE: Disk DATA_0000 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0001 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0002 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0003 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0004 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0005 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0006 in mode 0x7f marked for de-assignment
NOTE: Disk  in mode 0x7f marked for de-assignment
NOTE: Disk  in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0009 in mode 0x7f marked for de-assignment
ERROR: diskgroup DATA was not mounted
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "8" is missing from group number "2" 
ORA-15042: ASM disk "7" is missing from group number "2" 
ORA-15038: disk '/dev/rhdisk37' mismatch on 'Time Stamp' with target disk group [2129689239] [2062898314]
ERROR: alter diskgroup data mount

怀疑把报错这个磁盘组的rhdisk37加入到另外一套rac的asm中了(也就是说两套asm使用了同一块磁盘)，aix操作系统层面分析确认

---对asm扩容的机器上
# lscfg -vpl hdisk15
  hdisk15          U78C5.001.DQD076A-P2-C4-T1-W200C00A098BC9A83-L0  MPIO NetApp FCP Default PCM Disk

        Manufacturer................NETAPP  
        Machine Type and Model......LUN C-Mode      
        ROS Level and ID............9000
        Serial Number...............80DYz]L/OpCA
        Device Specific.(Z0)........FAS8020         


  PLATFORM SPECIFIC

  Name:  disk
    Node:  disk
    Device Type:  block

---磁盘组dismount的机器上
# lscfg -vpl hdisk37      
  hdisk37          U5802.001.9K87776-P1-C1-T1-W200500A098BC9A83-L0  MPIO NetApp FCP Default PCM Disk

        Manufacturer................NETAPP  
        Machine Type and Model......LUN C-Mode      
        ROS Level and ID............9000
        Serial Number...............80DYz]L/OpCA
        Device Specific.(Z0)........FAS8020         


  PLATFORM SPECIFIC

  Name:  disk
    Node:  disk
    Device Type:  block

通过lscfg 命令确认两套rac使用了同一块盘导致一个磁盘组异常，在新加的机器上查询确认新盘被破坏情况(新加入的磁盘由于reblance操作,已经被写入了380G左右数据[也就意味着这个磁盘在老磁盘组中最少会丢失380G数据]
20230905140603

对于这种情况，dismount磁盘组是外部冗余不可能直接mount起来,只能通过以前处理的类似方法:
asm disk header 彻底损坏恢复
asm磁盘加入vg恢复
asm磁盘dd破坏恢复
asm disk 磁盘部分被清空恢复
再一例asm disk被误加入vg并且扩容lv恢复
fdisk分区导致asm disk破坏数据库恢复
再一起asm disk被格式化成ext3文件系统故障恢复
oracle asm disk格式化恢复—格式化为ext4文件系统
oracle asm disk格式化恢复—格式化为ntfs文件系统
ORA-15063: ASM discovered an insufficient number of disks for diskgroup 恢复
通过底层处理恢复出来没有覆盖的数据块中数据
20230827200941
再使用dul恢复出来其中数据,完成这次故障的核心数据恢复