分类目录归档:软件开发

Oracle Recovery Tools实战批量坏块修复

有客户数据库无法正常启动ORA-600 6711错误

SQL> startup mount pfile='d:/pfile.txt'
ORACLE instance started.

Total System Global Area 4294964032 bytes
Fixed Size                  9036608 bytes
Variable Size             889192448 bytes
Database Buffers         3388997632 bytes
Redo Buffers                7737344 bytes
Database mounted.
SQL> alter database open ;
alter database open 
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898],
[0], [], [], [], [], [], [], []
Process ID: 22708
Session ID: 978 Serial number: 56675

alert日志报错

2022-06-26T12:34:41.855326+08:00
alter database open
2022-06-26T12:34:41.984974+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
Endian type of dictionary set to little
Undo initialization finished serial:0 start:313418906 end:313418906 diff:0 ms (0.0 seconds)
Database Characterset is ZHS16GBK
No Resource Manager plan active
2022-06-26T12:34:43.302315+08:00
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc  (incident=38629):
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\orcl\ora19c\incident\incdir_38629\ora19c_ora_22708_i38629.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-06-26T12:34:44.232115+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2022-06-26T12:34:44.315431+08:00
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc:
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
2022-06-26T12:34:44.315431+08:00
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc:
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Errors in file C:\APP\XFF\diag\rdbms\orcl\ora19c\trace\ora19c_ora_22708.trc  (incident=38630):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [6711], [4313028], [1], [4309898], [0], [], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\orcl\ora19c\incident\incdir_38630\ora19c_ora_22708_i38630.trc
2022-06-26T12:34:45.266678+08:00
opiodr aborting process unknown ospid (22708) as a result of ORA-603
2022-06-26T12:34:45.274688+08:00
ORA-603 : opitsk aborting process
License high water mark = 1
USER (ospid: (prelim)): terminating the instance due to ORA error 

通过分析trace文件进行分析,确认是由于histgrm$表异常导致,通过一些特殊处理,绕过该表相关sql,open数据库,并且尝试导出数据

SQL> startup mount pfile='d:/pfile.txt';
ORACLE instance started.

Total System Global Area 4294964032 bytes
Fixed Size                  9036608 bytes
Variable Size             889192448 bytes
Database Buffers         3388997632 bytes
Redo Buffers                7737344 bytes
Database mounted.
SQL>
SQL>
SQL> alter database open;

Database altered.

使用expdp导出数据报ORA-01578错
expdp-ora-1578


通过分析是由于system有坏块导致,dbv检查文件
dbv-huikuai

通过Oracle Recovery Tools工具批量坏块修复功能修复
20220626123245
20220626123343

通过工具修复大量主要坏块被修复,还有一些内部逻辑错误(后续工具继续完善),再次尝试逻辑导出数据,无任何报错,数据比较完美恢复
20220626160209

发表在 小工具, 非常规恢复 | 标签为 , , , , | 留下评论

修改oracle scn小工具(patch scn)

在一些情况下(特别是一些数据库非常规恢复场景中),需要修改oracle scn绕过一些错误,让数据库open成功,在以前的版本中我们可以通过event,隐含参数,oradebug等方法进行修改,在一些较新的版本中这些方法都被oracle屏蔽,无法实现oracle scn进行调整,针对这种情况,开发了一个Patch_SCN小程序,实现对oracle数据库的scn进行调整

SQL> select dbms_flashback.get_system_change_number a from dual;

               A
----------------
    107367806959

通过工具查询scn信息,由于oracle的scn是动态的,因此和get_system_change_number 查询值有细微出入
20220614130358


修改scn
20220614130543

通过查询确认scn修改成功
20220614130553

该功能的通用版已经包含到oracle racovery tools工具中(注册版可用)
oracle-recovery-tools

Patch_SCN下载:Patch_SCN下载
Patch_SCN使用说明:Patch_SCN使用说明
OraRecovery下载:OraRecovery下载
OraRecovery使用说明:OraRecovery使用说明

发表在 Oracle, 小工具 | 标签为 , , , | 留下评论

Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障

由于虚拟化环境使用了精简模式(预分配),后面出现分布式存储空间不足,导致虚拟化环境中的数据库服务器异常,通过一系列操作恢复好系统,发现数据库无法open,请求我们给予解决
通过我们的Oracle Database Recovery Check脚本分析,分析文件的checkpoint scn 有部分3月2日,还有一些是2月28日,是严重不一致,而且对应的归档也丢失
20210314194011


基于这样的情况,试试看强制打开库

C:\Users\XIFENFEI>sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 3月 11 23:51:39 2021

Copyright (c) 1982, 2013, Oracle.  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

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 1603411968 bytes
Fixed Size                  2281656 bytes
Variable Size             469765960 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7290880 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 57834775 (在 02/28/2021 22:37:35 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\XIFENFEI\PRODUCT\11.2.0.4\DBHOME_1\RDBMS\ARC0000003072_1043082043.0001
ORA-00280: 更改 57834775 (用于线程 1) 在序列 #3072 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'D:\BAIDUNETDISKDOWNLOAD\DATA\PROD\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 10 with name
"_SYSSMU10_1197734989$" too small
进程 ID: 7928
会话 ID: 96 序列号: 3

在数据库open的过程中,报ORA-01555错误,这类问题比较明显以前写过类似文章:
在数据库open过程中常遇到ORA-01555汇总
数据库open过程遭遇ORA-1555对应sql语句补充
使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
这次尝试使用自己开发的小程序:Oracle Recovery Tools进行恢复
20210311235641


然后直接尝试打开数据库成功

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: ?? 1 ??????
ORA-01110: ???? 1: 'D:\BAIDUNETDISKDOWNLOAD\DATA\XFF\SYSTEM01.DBF'


SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

这次证明,对于数据库open过程汇总报ORA-00704 ORA-01555故障,可以通过Oracle Recovery Tools工具一键式open库。
后续安排数据导出,对于个别导出报错的表利用dul进行处理,完成本次恢复任务
软件下载:OraRecovery下载
使用说明:使用说明

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