win系统删除oracle数据文件恢复

Posted on 2023 年 07 月 07 日 by 惜分飞

有客户联系我们,说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层面无法恢复)

通过工具检测恢复出来的数据文件,损坏的几个block是文件头部不涉及业务数据,运气不错

另外一个数据文件,从os层面无法恢复,对于这种情况,只能基于底层的block层面进行恢复(恢复没有覆盖的block)

参考类似恢复案例:
win文件系统损坏oracle恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
分享运气超级好的一次drop tablespace 数据恢复
恢复出来的两个数据文件,结合该编辑的其他数据文件通过dul工具恢复其中数据,最大程度抢救客户数据,减少损失.

ORA-01122 ORA-01200故障处理

Posted on 2023 年 07 月 04 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01122 ORA-01200故障处理

由于某种原因客户的数据库启动报ORA-01122 ORA-01200错误

让客户把system01.dbf文件发给我进行分析,发现system01.dbf文件大于32G(在8k的blocksize库中,默认情况system01.dbf文件不会超过32G),这个明显异常

检测坏块情况发现4096000之后的block全部为全0块

通过bbed分析文件头记录文件大小

通过bbed修改合适的值,并且把文件截取到适当大小,提供system文件给客户,直接启动库成功,实现数据库完美恢复

通过设置文件头大小和截断合适大小实现本次数据库恢复,以前有过类似恢复:
bbed处理ORA-01200故障

asm磁盘加入vg恢复

Posted on 2023 年 06 月 25 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：asm磁盘加入vg恢复

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

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

对于这种情况,通过对文件头进行修复,结合工具直接拷贝出来数据文件（个别文件元数据损坏通过基于block的方式恢复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故障处理

Posted on 2023 年 05 月 24 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

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

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

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

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

由于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成功

ORA-600 16703故障,客户找人恢复数据库,数据库被进一步恶意破坏—ORA-00704 ORA-00922

Posted on 2023 年 05 月 17 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 16703故障,客户找人恢复数据库,数据库被进一步恶意破坏—ORA-00704 ORA-00922

有朋友找到我,数据库报ORA-600 16703错误,这个本来是一个比较常见的破坏故障(警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703数据库启动报错如下:

修复tab$启动库报ORA-00704 ORA-00922错误

SQL> alter database Open;
alter database Open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00922: missing or invalid option
进程 ID: 1340
会话 ID: 191 序列号: 3

ORA-00704 ORA-00922是比较少见的错误,第一感觉bootstrap$损坏了,对数据库启动过程进行跟踪

PARSING IN CURSOR #11700472 len=600 dep=1 uid=0 oct=1 lid=0 tim=338738406773 hv=4034608779 
ad='7ffdef83f360' sqlid='asgjp8bs7qgnb'
CREATE TABLE UNDO$("US#" 
END OF STMT
PARSE #11700472:c=0,e=361,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=338738406773
EXEC #11700472:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=338738406917
CLOSE #11700472:c=0,e=3,dep=1,type=0,tim=338738406997
=====================
PARSE ERROR #635423520:len=841 dep=1 uid=0 oct=1 lid=0 tim=338738407066 err=922
CREATE TABLE TS$<"TS#" NU ...
ORA-00704: 引导程序进程失败
ORA-00922: 选项缺失或无效
ORA-00704: 引导程序进程失败
ORA-00922: 选项缺失或无效

*** 2023-05-17 19:27:51.813
USER (ospid: 1340): terminating the instance due to error 704

*** 2023-05-17 19:27:54.050
EXEC #11710688:c=0,e=2481834,p=16,cr=62,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=338740646732
ERROR #11710688:err=1092 tim=338740646777

进一步分析bootstrap$表记录

通过上述分析,可以确认原库的CREATE TABLE TS$(“TS#”被人修改为CREATE TABLE TS$<“TS#”,通过观察客户机器以及和客户确认,客户找的技术人员上传了bbed工具,并进行了一些操作.基于上述信息,大概率是被人通过bbed工具把TS$(修改为了TS$<,从而使得数据库修复tab$之后也无法正常启动.