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故障

asm磁盘加入vg恢复

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

标题:asm磁盘加入vg恢复

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

又一个客户把asm disk做成pv,加到vg中,并且对lv进行了扩展(ext4的文件系统)
asm-disk-pv


这个客户做了上述操作之后,没有对lv进行写入其他数据,所以破坏较少(主要的破坏就是ext4的每个一段就会置空一部分block预留给文件系统写入元数据使用),通过winhex查看被破坏磁盘发现lvm信息
lvm

对于这种情况,通过对文件头进行修复,结合工具直接拷贝出来数据文件(个别文件元数据损坏通过基于block的方式恢复dbf)
asm-dbf

然后直接恢复dbf中数据文件(对于异常的主要是segment header被置空的tab使用dul单独扫描处理),实现客户数据的最大限度恢复
以前类似文章:
asm disk被加入vg恢复
asm disk被分区,格式化为ext4恢复
pvcreate asm disk导致asm磁盘组异常恢复
再一起asm disk被格式化成ext3文件系统故障恢复
一次完美的asm disk被格式化ntfs恢复
oracle asm disk格式化恢复—格式化为ext4文件系统

ORA-16038 ORA-00354故障处理

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

标题:ORA-16038 ORA-00354故障处理

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

遇到一个案例,数据库open报ORA-16038,ORA-00354等错误
ORA-16038-ORA-00354


查询该redo状态(使用Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本收集),确认为inactive
20230524213907

由于inactive 状态的redo损坏,无法被arch进程归档导致数据库无法正常open,尝试强制clear联机日志
ORA-00393-ORA-00312

由于25号文件属于offline状态,导致联机日志无法正常被clear,报ORA-00393 ORA-00312等错误.通过试验重现该问题.

SQL> alter database datafile 5 offline;

Database altered.

--使用一些技巧让数据库无法归档

SQL> select group#,status,archived from v$log;

    GROUP# STATUS           ARC
---------- ---------------- ---
         1 INACTIVE         NO
         2 ACTIVE           NO
         3 CURRENT          NO

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             234882088 bytes
Database Buffers          385875968 bytes
Redo Buffers                3313664 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00393: log 1 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf'

SQL> !oerr ora 393
00393, 00000, "log %s of thread %s is needed for recovery of offline datafiles"
// *Cause:  Log cannot be cleared because the redo in it is needed to recover
//          offline datafiles. It has not been archived so there is no
//          other copy available. If the log is cleared the tablespaces
//          containing the files will have to be dropped.
// *Action: Archive the log then repeat the clear command. If archiving is not
//          possible, and dropping the tablespaces is acceptible, then add the
//          clause UNRECOVERABLE DATAFILE at the end of the clear command.


SQL>  alter database clear unarchived logfile group 1 unrecoverable datafile;

Database altered.

SQL> select group#,status,archived from v$log;

    GROUP# STATUS           ARC
---------- ---------------- ---
         1 UNUSED           YES
         3 CURRENT          NO
         2 ACTIVE           NO

客户的问题也是通过unrecoverable datafile 方式强制clear联机日志成功,数据库open成功