小工具 – Database SOS

Patch SCN工具for Linux

前几年开发了图形化的win平台的patch scn工具:一键修改Oracle SCN工具升级(patch scn)

最近基于linux平台开发了命令行方式的Patch scn工具,大概使用如下:
1. 数据库必须处于mount状态才能修改scn

[oracle@iZbp11c0qyuuo1gr7j98upZ tmp]$ ./Patch_SCN   328 247884300
ERROR:Oracle Database must be in Mount state in order to modify SCN

2. Patch_SCN参数提示

[oracle@iZbp11c0qyuuo1gr7j98upZ tmp]$ ./Patch_SCN   
Use Parameters: PID  SCN(10)  [ADDR(16,don't 0x)]

3.修改SCN 具体操作

1)启动数据库到mount状态
SQL> startup mount;
ORACLE instance started.

Total System Global Area  551165952 bytes
Fixed Size                  2255112 bytes
Variable Size             369100536 bytes
Database Buffers          171966464 bytes
Redo Buffers                7843840 bytes
Database mounted.

2)查询数据库当前scn
SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
           2478843

3)新会话中修改数据库scn
[oracle@iZbp11c0qyuuo1gr7j98upZ tmp]$ ./Patch_SCN   328 247884300
Please press ENTER to continue...
Modify the Oracle SCN value to:EC66A0C:247884300

4)启动数据库并查询scn
SQL> ALTER DATABASE OPEN;

Database altered.

SQL>  select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
         247884301 （比修改scn稍微大由于数据库已经启动会自动增加scn值）

这个小工具直接通过内存地址修改scn,绕过Oracle在一些版本中的oradebug的限制:oradebug poke ORA-32521/ORA-32519故障解决,软件还处于测试阶段,后续稳定后将对外提供软件形式服务.

Patch SCN一键解决ORA-600 2662故障

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

标题：Patch SCN一键解决ORA-600 2662故障

客户强制重启库之后,数据库启动报ORA-600 2037,ORA-745 kcbs_reset_pool/kcbzre1等错误

Wed Aug 09 13:25:38 2023
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1672229586
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 84074 KB redo, 37037 data blocks need recovery
Wed Aug 09 13:25:49 2023
Started redo application at
 Thread 1: logseq 998542, block 61480
Recovery of Online Redo Log: Thread 1 Group 1 Seq 998542 Reading mem 0
  Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 998543 Reading mem 0
  Mem# 0: E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Wed Aug 09 13:25:49 2023
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1350B61, kcbs_reset_pool()+3135]
Wed Aug 09 13:25:49 2023
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p011_2508.trc  (incident=1203440):
ORA-00600: internal error code, arguments: [2037], [1981399269], [3236233216], [37], [193], [11], [2842064409], [100728832], [], [], [], 
Wed Aug 09 13:25:49 2023
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p002_2472.trc  (incident=1203368):
ORA-00600: internal error code, arguments: [2037], [1882432643], [2760048640], [77], [31], [11], [2915397683], [100731648], [], [], [], 
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
dbgexProcessError()+193<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+1726<-dbkePostKGE_kgsf()+75<-kgeade()+560<-kgerev()+125
<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1869<-sss_xcpt_EvalFilter()+174<-.1.4_5+59<-00007FF85F46C92F<-00007FF85F47D82D
<-00007FF85F42916B<-00007FF85F47C9EE<-kcbs_reset_pool()+3135<-kcbs_resize_pool()+105<-kcbw_deferred_complete()+87
<-kmgs_deferred_initialize()+674<-ksbabs()+1086<-ksbrdp()+1286<-opirip()+853<-opidrv()+909<-sou2o()+98
<-opimai_real()+299<-opimai()+191<-BackgroundThreadStart()+693<-00007FF85DBA16AD<-00007FF85F454629
Wed Aug 09 13:25:49 2023
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_mman_1192.trc  (incident=1203256):
ORA-07445: exception encountered:core dump [kcbs_reset_pool()+3135] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1350B61] [UNABLE_TO_READ]
Incident details in: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1203256\orcl_mman_1192_i1203256.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0xECE0BF, kcbzre1()+2811]
Wed Aug 09 13:25:49 2023
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p004_2480.trc  (incident=1203384):
ORA-00600: internal error code, arguments: [2037], [12693108], [2926837760], [72], [217], [11], [2837053633], [33622528], [], [], [], []
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0xECE0BF, kcbzre1()+2811]
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p007_2492.trc  (incident=1203409):
ORA-07445: exception encountered:core dump [kcbzre1()+2811] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0xECE0BF] [UNABLE_TO_READ]
ORA-00600: internal error code, arguments: [2037], [12693108], [2926837760], [72], [217], [11], [2837053633], [33622528], [], [], [], []
Incident details in: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1203385\orcl_p004_2480_i1203385.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0xECE0BF, kcbzre1()+2811]
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0xECE0BF, kcbzre1()+2811]
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_p001_2468.trc  (incident=1203361):
ORA-07445: exception encountered: core dump [kcbzre1()+2811] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0xECE0BF] [UNABLE_TO_READ] 
ORA-00600: internal error code, arguments: [2037], [130229293], [606928896], [227], [99], [11], [4149544899], [100740356], [], [], [],

屏蔽数据库一致性,强制拉库报ORA-600 2662错误

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2076.trc  (incident=1315352):
ORA-00600: 内部错误代码, 参数: [2662], [11], [1088067894], [11], [1088145382], [12583040], [], [], [], [], [], []
Incident details in: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1315352\orcl_ora_2076_i1315352.trc
Trace dumping is performing id=[cdmp_20230809170852]
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2076.trc:
ORA-00600: 内部错误代码, 参数: [2662], [11], [1088067894], [11], [1088145382], [12583040], [], [], [], [], [], []
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2076.trc:
ORA-00600: 内部错误代码, 参数: [2662], [11], [1088067894], [11], [1088145382], [12583040], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 2076): terminating the instance due to error 600
Instance terminated by USER, pid = 2076
ORA-1092 signalled during: ALTER DATABASE OPEN...

这种ORA-600 2662的错误比较常见,通过Patch SCN工具一键式解决,参考以前恢复案例：
Patch SCN工具快速解决ORA-600 2662问题

实现数据库open成功，并顺利导出数据

Oracle Recovery Tools恢复csc higher than block scn

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

标题：Oracle Recovery Tools恢复csc higher than block scn

有客户强制关闭数据库,结果有数据块报坏块,dbv检查为:csc higher than block scn问题

该问题主要是由于scn异常导致通过Oracle Recovery工具进行修复

dbv再次验证数据块ok,Oracle Recovery完美代替bbed解决该问题

通过OraRecovery工具快速解决csc higher than block scn故障
软件下载:OraRecovery下载
使用说明:使用说明

Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题）

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

标题：Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题）

由于异常断电,数据库启动报错ORA-01113和ORA-01110，ORA-00322和ORA-00312以及ORA-00314和ORA-00312错误

Mon Apr 17 09:35:04 2023
ALTER DATABASE OPEN
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_10192.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: ALTER DATABASE OPEN...
Mon Apr 17 09:35:05 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00322: 日志 6 (用于线程 1) 不是最新副本
ORA-00312: 联机日志 6 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00314: 日志 7 (用于线程 1) 要求的 sequence# 15257 与 15248 不匹配
ORA-00312: 联机日志 7 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO07.LOG'

客户那边技术尝试强制拉库之后,报ORA-00704、ORA-01555等错误,数据库启动失败

Mon Apr 17 10:10:49 2023
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 3159276957
Resetting resetlogs activation ID 1558785994 (0x5ce92fca)
Mon Apr 17 10:11:11 2023
Setting recovery target incarnation to 3
Mon Apr 17 10:11:11 2023
Assigning activation ID 1662170916 (0x6312b724)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Mon Apr 17 10:11:11 2023
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Apr 17 10:11:12 2023
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.bc4ebda4):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Error 704 happened during db open, shutting down database
USER (ospid: 9324): terminating the instance due to error 704
Instance terminated by USER, pid = 9324
ORA-1092 signalled during: alter database open resetlogs...

然后尝试一系列操作,比如重建ctl,再次拉库等等,均未绕过该错误.对于ORA-704 ORA-01555错误,使用开发的小工具Oracle Recovery Tools可以一键式解决,参考处理过的类似文章:Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障,不过最近发布的版本中增加了直接修改内存SCN功能,可以更加快速的解决给问题

修复之后,数据库open成功

SQL> alter database open;

数据库已更改。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1.7170E+10 bytes
Fixed Size                  2262088 bytes
Variable Size            1.3153E+10 bytes
Database Buffers         3959422976 bytes
Redo Buffers               54947840 bytes
数据库装载完毕。
SQL> select name from v$datafile where name like '%MISSING%';

NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013

已选择2行。

很不幸技术在尝试重建ctl的恢复的过程中遗漏了两个数据库文件,通过Oracle Recovery Tools再次进行恢复

在sqlplus中恢复这两个文件成功

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012' 
   2 to 'D:\oradata\XIFENFEI_04.DBF';

数据库已更改。

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013' 
   2 to 'D:\oradata\XIFENFEI_5.DBF';

数据库已更改。


SQL> alter database datafile 12,13 online;

数据库已更改。

SQL>
SQL>
SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-01113: 文件 12 需要介质恢复
ORA-01110: 数据文件 12: 'D:\ORADATA\XIFENFEI_04.DBF'


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

数据库已更改。

至此数据库基本上恢复完成,剩余工作就是逻辑导出导入.
软件下载:OraRecovery工具
使用说明:Oracle_Recovery_Tools说明

Oracle Recovery Tools更新—202304

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

标题：Oracle Recovery Tools更新—202304

最近对Oracle Recovery Tools工具进行了升级,增加了在某些恢复情况下对于单个文件的CheckPoint Time的显示,便于判断文件的有效性(便于历史文件区分),该软件是一个恢复小工具,主要定位:
1. 对于某些恢复场景的快速判断,便于问题定位和后续恢复
2. 实现图形化快速修复损坏的oracle数据块和构造块
3. 实现图形化快速修改文件头信息,代替bbed解决文件头的各种scn等问题
4. 快速修改内存中记录,在某些强制拉库的过程中提供便利,特别是在win平台后续版本中无法通过oradebug等方式修改的情况下使用

把对Oracle内存修改功能集成到该软件中

增加了一些可能使用到的小功能

Oracle Recovery Tools快速恢复重建ctl遗漏数据文件故障

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

标题：Oracle Recovery Tools快速恢复重建ctl遗漏数据文件故障

数据库被强制打开,由于重建ctl把部分文件没有列入其中导致数据库在resetlogs打开之后部分文件异常

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_HRP' #21 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #19 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00019' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #25 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00025' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #26 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00026' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #27 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00027' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #66 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00066' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #67 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00067' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #68 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00068' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #69 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00069' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #70 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00070' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #91 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00091' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #92 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00092' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #93 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00093' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #94 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00094' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #95 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00095' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #96 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00096' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #97 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00097' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #98 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00098' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #99 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00099' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #100 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00100' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #102 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00102' in the controlfile.
This file can no longer be recovered so it must be dropped.

通过查询恢复之后的v$datafile发现

SQL> select name from v$datafile where status='RECOVER';

NAME
--------------------------------------------------------------------------------

C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00019
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00025
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00026
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00027
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00066
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00067
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00068
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00069
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00070
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00091
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00092
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00093
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00094
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00095
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00096
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00097
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00098
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00099
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00100
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00102

20 rows selected.

对于这种情况,使用OraRecovery工具,可以快速修复

然后批量重命名数据文件,recover datafile，online datafile一气呵成

Sun Oct 23 23:27:36 2022
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00019' to 'E:\oradata\datafile\xifenfei.287.948643517'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00019' to 'E:\oradata\datafile\xifenfei.287.948643517'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00025' to 'E:\oradata\datafile\xifenfei.293.968102781'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00025' to 'E:\oradata\datafile\xifenfei.293.968102781'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00026' to 'E:\oradata\datafile\xifenfei.294.968102903'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00026' to 'E:\oradata\datafile\xifenfei.294.968102903'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00027' to 'E:\oradata\datafile\xifenfei.295.968103023'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00027' to 'E:\oradata\datafile\xifenfei.295.968103023'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00066' to 'E:\oradata\datafile\xifenfei.346.1050578857'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00066' to 'E:\oradata\datafile\xifenfei.346.1050578857'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00067' to 'E:\oradata\datafile\xifenfei.347.1050578871'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00067' to 'E:\oradata\datafile\xifenfei.347.1050578871'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00068' to 'E:\oradata\datafile\xifenfei.348.1050578873'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00068' to 'E:\oradata\datafile\xifenfei.348.1050578873'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00069' to 'E:\oradata\datafile\xifenfei.349.1050578875'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00069' to 'E:\oradata\datafile\xifenfei.349.1050578875'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00070' to 'E:\oradata\datafile\xifenfei.350.1050578877'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00070' to 'E:\oradata\datafile\xifenfei.350.1050578877'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00091' to 'E:\oradata\datafile\xifenfei.371.1081159403'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00091' to 'E:\oradata\datafile\xifenfei.371.1081159403'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00092' to 'E:\oradata\datafile\xifenfei.372.1081159409'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00092' to 'E:\oradata\datafile\xifenfei.372.1081159409'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00093' to 'E:\oradata\datafile\xifenfei.373.1081159425'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00093' to 'E:\oradata\datafile\xifenfei.373.1081159425'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00094' to 'E:\oradata\datafile\xifenfei.374.1081159427'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00094' to 'E:\oradata\datafile\xifenfei.374.1081159427'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00095' to 'E:\oradata\datafile\xifenfei.375.1088945947'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00095' to 'E:\oradata\datafile\xifenfei.375.1088945947'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00096' to 'E:\oradata\datafile\xifenfei.376.1088945949'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00096' to 'E:\oradata\datafile\xifenfei.376.1088945949'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00097' to 'E:\oradata\datafile\xifenfei.377.1088945953'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00097' to 'E:\oradata\datafile\xifenfei.377.1088945953'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00098' to 'E:\oradata\datafile\xifenfei.378.1088945955'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00098' to 'E:\oradata\datafile\xifenfei.378.1088945955'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00099' to 'E:\oradata\datafile\xifenfei.379.1088945957'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00099' to 'E:\oradata\datafile\xifenfei.379.1088945957'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00100' to 'E:\oradata\datafile\xifenfei.380.1100595805'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00100' to 'E:\oradata\datafile\xifenfei.380.1100595805'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00102' to 'E:\oradata\datafile\xifenfei.382.1100595821'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00102' to 'E:\oradata\datafile\xifenfei.382.1100595821'
Sun Oct 23 23:30:54 2022
ALTER DATABASE RECOVER  datafile 19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102  
Media Recovery Start
Serial Media Recovery started
Completed: ALTER DATABASE RECOVER  datafile 19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102  
Sun Oct 23 23:31:30 2022
alter database datafile  19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102 online
Completed: alter database datafile  19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102 online

通过OraRecovery工具快速实现MISSING数据文件恢复
软件下载:OraRecovery下载
使用说明:使用说明

一键修改Oracle SCN工具升级(patch scn)

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

标题：一键修改Oracle SCN工具升级(patch scn)

前段时间开发了基于内存修改的patch scn小程序（修改oracle scn小工具(patch scn)），用来修改oracle数据库scn,实现快速调整oracle scn的功能（特别是在一些恢复的时候很有用）,最近对其功能进一步完善，扩展了通过修改oracle controlfile实现调整scn功能,简单测试如下
数据库当前scn情况

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 10月 6 21:50:32 2022

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> set num 16
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
      141743308712

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

通过工具一键修改oracle scn

启动库查看修改之后scn值

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 6413680640 bytes
Fixed Size                  2293216 bytes
Variable Size            1275068960 bytes
Database Buffers         5117050880 bytes
Redo Buffers               19267584 bytes
数据库装载完毕。
SQL> set num 16
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
      141743339676

SQL> alter database open;

数据库已更改。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
      151743339602

数据库启动过程中scn会被改变，在mount的时候未发生改变
Patch_SCN下载:Patch_SCN下载
Patch_SCN使用说明:Patch_SCN使用说明
OraRecovery下载:OraRecovery下载
OraRecovery使用说明:OraRecovery使用说明

Oracle Recovery Tools快速解决sysaux文件不能online问题

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

标题：Oracle Recovery Tools快速解决sysaux文件不能online问题

又一客户sysaux表空间对应的数据文件离线(file 6 为测试表空间数据可以不要)

Tue Jul 26 11:33:41 2022
alter database datafile 2 offline drop
Completed: alter database datafile 2 offline drop
Tue Jul 26 11:35:26 2022
alter database datafile 6 offline drop
Completed: alter database datafile 6 offline drop
Tue Jul 26 11:36:04 2022
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 19 processes
Started redo scan
Completed redo scan
 read 14595 KB redo, 954 data blocks need recovery
Started redo application at
 Thread 1: logseq 52560, block 31365
Recovery of Online Redo Log: Thread 1 Group 3 Seq 52560 Reading mem 0
  Mem# 0: D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG
Completed redo application of 6.50MB
Completed crash recovery at
 Thread 1: logseq 52560, block 60555, scn 4397986801
 954 data blocks read, 954 data blocks written, 14595 redo k-bytes read
Tue Jul 26 11:36:11 2022
Thread 1 advanced to log sequence 52561 (thread open)
Thread 1 opened at log sequence 52561
  Current log# 1 seq# 52561 mem# 0: D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jul 26 11:36:11 2022
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
Tue Jul 26 11:36:14 2022
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file d:\XXXX\datas\diag\rdbms\XXXX\XXXX\trace\XXXX_ora_8476.trc  (incident=275156):
ORA-25319: 队列表重新分区已中止
Incident details in: d:\XXXX\datas\diag\rdbms\XXXX\XXXX\incident\incdir_275156\XXXX_ora_8476_i275156.trc
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Tue Jul 26 11:36:23 2022
QMNC started with pid=50, OS id=11200 
Tue Jul 26 11:36:23 2022
Trace dumping is performing id=[cdmp_20220726113623]
 XDB UNINITIALIZED: XDB$SCHEMA not accessible 
Tue Jul 26 11:36:27 2022
Completed: ALTER DATABASE OPEN

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 OFFLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 OFFLINE

7月份offline datafile 2,然后open数据库一直运行至今,数据库一直无法进行备份,需要我们进行解决

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            D:\APP\DATAS\product\11.2.0.4\dbhome_1\RDBMS
最早的联机日志序列     55557
当前日志序列           55559

SQL> recover datafile 2;
ORA-00279: 更改 4397905894 (在 07/25/2022 18:26:58 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\XXXX\DATAS\FLASH_RECOVERY_AREA\XXXX\ARCHIVELOG\2022_08_29\O1_MF_1_52560_%U_.ARC
ORA-00280: 更改 4397905894 (用于线程 1) 在序列 #52560 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

数据库为非归档,常规方法无法直接online datafile 2,对于这样的情况,使用Oracle Recovery Tools工具,进行快速修改文件头信息

查询文件头信息

SQL> set pages 1000
SQL> set linesize 150
SQL> select ts#,file#,TABLESPACE_NAME,status,
  2  to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,
  3  to_char(checkpoint_change#,'9999999999999999') "SCN",
  4  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
  5  from v$datafile_header;

       TS#      FILE# TABLESPACE_NAME                STATUS  CREATE_TIME         SCN               RESETLOGS SCN     FUZ

---------- ---------- ------------------------------ ------- ------------------- ----------------- ----------------- ---

         0          1 SYSTEM                         ONLINE  2010-03-30 10:07:48        4599488977            947455 NO
         1          2 SYSAUX                         ONLINE  2010-03-30 10:07:52        4599488977            947455 YES
         2          3 UNDOTBS1                       ONLINE  2010-03-30 11:07:21        4599488977            947455 NO
         4          4 USERS                          ONLINE  2010-03-30 10:08:04        4599488977            947455 NO
         6          5 XXXX                           ONLINE  2020-05-29 09:45:48        4599488977            947455 NO

并且尝试online datafile 2

SQL> recover datafile 2;
ORA-00283: 恢复会话因错误而取消
ORA-01122: 数据库文件 2 验证失败
ORA-01110: 数据文件 2: 'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF'
ORA-01207: 文件比控制文件更新 - 旧的控制文件

由于ctl中的关于datafile2 的信息没有更新，因此数据文件的信息比ctl中的新,无法正常recover,需要重建ctl

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 1.3195E+10 bytes
Fixed Size                  2188168 bytes
Variable Size            1.0301E+10 bytes
Database Buffers         2885681152 bytes
Redo Buffers                5738496 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XXXX" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2336
  7  LOGFILE
  8    GROUP 1 'D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\XXXX\DATAS\ORADATA\XXXX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\XXXX\DATAS\ORADATA\XXXX\SYSTEM01.DBF',
 13    'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF',
 14    'D:\XXXX\DATAS\ORADATA\XXXX\UNDOTBS01.DBF',
 15    'D:\XXXX\DATAS\ORADATA\XXXX\USERS01.DBF',
 16    'D:\XXXX\DATAS\ZYSPACE\XXXX.DBF',
 17    'E:\XXXX\DATAS\BACKUP\XXXXX.DBF'
 18  CHARACTER SET ZHS16GBK
 19  ;
CREATE CONTROLFILE REUSE DATABASE "XXXX" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01229: ???? 2 ??????
ORA-01110: ???? 2: 'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF'

由于redo中信息也不对,重建需要使用resetlogs方式进行

SQL> CREATE CONTROLFILE REUSE DATABASE "XXXX" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2336
  7  LOGFILE
  8    GROUP 1 'D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\XXXX\DATAS\ORADATA\XXXX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\XXXX\DATAS\ORADATA\XXXX\SYSTEM01.DBF',
 13    'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF',
 14    'D:\XXXX\DATAS\ORADATA\XXXX\UNDOTBS01.DBF',
 15    'D:\XXXX\DATAS\ORADATA\XXXX\USERS01.DBF',
 16    'D:\XXXX\DATAS\ZYSPACE\XXXX.DBF',
 17    'E:\XXXX\DATAS\BACKUP\XXXXX.DBF'
 18  CHARACTER SET ZHS16GBK
 19  ;

控制文件已创建。

后续处理

SQL> alter database datafile 6 offline drop;

数据库已更改。

SQL> recover database using backup controlfile;
ORA-00279: ?? 4599488977 (? 08/29/2022 20:59:25 ??) ???? 1 ????
ORA-00289: ??: D:\XXXX\DATAS\FLASH_RECOVERY_AREA\XXXX\ARCHIVELOG\2022_08_29\O1_MF_1_55279_%U_.ARC
ORA-00280: ?? 4599488977 (???? 1) ??? #55279 ?


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG
已应用的日志。
完成介质恢复。
SQL> alter database open resetlogs;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\XXXX\DATAS\ORADATA\XXXX\TEMP01.DBF' REUSE;

表空间已更改。

数据导出成功

Oracle Recovery Tools修复空闲坏块

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

标题：Oracle Recovery Tools修复空闲坏块

我们经常遇到由于某种原因,表上面有坏块,通过event或者dbms包跳过坏块,然后重建该表之后,但是dbv和rman检查坏块依旧存在(而且导致常规情况下rman无法正常备份),最近在Oracle Recovery Tools工具中增加的这种异常数据块修复功能,通过试验重现类似故障:
创建表并进行破坏

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期一 8月 8 14:00:34 2022

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> create tablespace ts_test datafile 'e:/oradata/test/ts_test.dbf' size 128M autoextend on;

表空间已创建。

SQL> create table t_xifenfei tablespace ts_test
  2  as
  3  select * from dba_objects;

表已创建。

SQL> alter system checkpoint;

系统已更改。

SQL> set pages 100
SQL> select file_id,block_id,blocks from dba_extents where segment_name='T_XIFENFEI';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         5        128          8
         5        136          8
         5        144          8
         5        152          8
         5        160          8
         5        168          8
         5        176          8
         5        184          8
         5        192          8
         5        200          8
         5        208          8
         5        216          8
         5        224          8
         5        232          8
         5        240          8
         5        248          8
         5        256        128
         5        384        128
         5        512        128
         5        640        128
         5        768        128
         5        896        128
         5       1024        128
         5       1152        128
         5       1280        128

已选择25行。


SQL> SELECT COUNT(1) FROM T_XIFENFEI;

  COUNT(1)
----------
     86048

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。


C:\Windows\system32>dd of=e:/oradata/test/ts_test.dbf  if=/dev/zero bs=1k seek=1419 conv=notrunc count=1
rawwrite dd for windows version 0.6beta3.
Written by John Newbigin <jn@it.swin.edu.au>
This program is covered by terms of the GPL Version 2.

notrunc
1+0 records in
1+0 records out


SQL> startup
ORACLE 例程已经启动。

Total System Global Area 3206836224 bytes
Fixed Size                  2285512 bytes
Variable Size             754974776 bytes
Database Buffers         2432696320 bytes
Redo Buffers               16879616 bytes
数据库装载完毕。
数据库已经打开。
SQL> select count(1) from t_xifenfei;
select count(1) from t_xifenfei
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 177)
ORA-01110: 数据文件 5: 'E:\ORADATA\TEST\TS_TEST.DBF'

跳过坏块重建该表

SQL> alter session set events '10231 trace name context forever, level 10';

会话已更改。

SQL> create table t_xifenfei_bak tablespace ts_test
  2  as select * from t_xifenfei;

表已创建。

SQL> select count(1) from t_xifenfei_bak;

  COUNT(1)
----------
     85968

SQL> drop table t_xifenfei purge;

表已删除。

SQL> rename t_xifenfei_bak to t_xifenfei;

表已重命名。

SQL> select count(1) from t_xifenfei;

  COUNT(1)
----------
     85968

检查坏块情况
通过rman和dbv检查,均表明file 5 block 177为坏块

C:\Users\XFF>dbv file=E:\ORADATA\TEST\TS_TEST.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期一 8月 8 17:25:57 2022

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

DBVERIFY - 开始验证: FILE = E:\ORADATA\TEST\TS_TEST.DBF
页 177 标记为损坏
Corrupt block relative dba: 0x014000b1 (file 5, block 177)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x014000b1
 last change scn: 0x0000.000ebc27 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xbc270602
 check value in block header: 0x5b2a
 computed block checksum: 0xbb32



DBVERIFY - 验证完成

检查的页总数: 16384
处理的页总数 (数据): 2456
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 155
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 13772
标记为损坏的总页数: 1
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 967616 (0.967616)

RMAN> backup validate check logical datafile 5;

启动 backup 于 08-8月 -22
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=118 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00005 名称=E:\ORADATA\TEST\TS_TEST.DBF
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:01
数据文件列表
=================
文件状态 标记为损坏 空块 已检查的块 高 SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              13744        16384           967621
  文件名: E:\ORADATA\TEST\TS_TEST.DBF
  块类型 失败的块 已处理的块
  ---------- -------------- ----------------
  数据       1              2457
  索引      0              0
  其他      0              183

验证找到一个或多个损坏的块
有关详细信息, 请参阅跟踪文件 C:\APP\XFF\diag\rdbms\test\test\trace\test_ora_22284.trc
完成 backup 于 08-8月 -22

SQL> select * from v$database_block_corruption ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         5        177          1                  0 CHECKSUM

查询坏块所属对象
没有查询到该坏块所属对象,证明该block为游离块[不属于任何数据对象,是空闲块,但是损坏]

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
输入 file_id 的值:  5
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 5
输入 block_id 的值:  177
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 177 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

未选定行

通过Oracle Recovery Tools工具进行修复

再次检查坏块
通过工具修复之后,dbv和rman检查均正常

C:\Users\XFF>rman target /

恢复管理器: Release 11.2.0.4.0 - Production on 星期一 8月 8 17:59:26 2022

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

已连接到目标数据库: TEST (DBID=2410248200)

RMAN> backup validate check logical datafile 5;

启动 backup 于 08-8月 -22
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=54 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00005 名称=E:\ORADATA\TEST\TS_TEST.DBF
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:01
数据文件列表
=================
文件状态 标记为损坏 空块 已检查的块 高 SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              13745        16384           967621
  文件名: E:\ORADATA\TEST\TS_TEST.DBF
  块类型 失败的块 已处理的块
  ---------- -------------- ----------------
  数据       0              2456
  索引      0              0
  其他      0              183

完成 backup 于 08-8月 -22


C:\Users\XFF>dbv file=E:\ORADATA\TEST\TS_TEST.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期一 8月 8 17:56:45 2022

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

DBVERIFY - 开始验证: FILE = E:\ORADATA\TEST\TS_TEST.DBF


DBVERIFY - 验证完成

检查的页总数: 16384
处理的页总数 (数据): 2456
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 183
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 13745
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 967621 (0.967621)


SQL> select * from v$database_block_corruption ;

未选定行

Oracle Recovery Tools 解决ORA-600 3020故障

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

标题：Oracle Recovery Tools 解决ORA-600 3020故障

尝试recover datafile,部分文件报ORA-600 3020，其他文件recover成功

ALTER DATABASE RECOVER  datafile 1  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24972 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_72232.trc  (incident=749532):
ORA-00600: 内部错误代码, 参数: [3020], [1], [272255], [4466559], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 272255, file offset is 2230312960 bytes)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 383
Media Recovery failed with error 600
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 1  ...
Tue Aug 02 10:28:24 2022
Trace dumping is performing id=[cdmp_20220802102824]
Tue Aug 02 10:28:31 2022
ALTER DATABASE RECOVER  datafile 2  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24972 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_72232.trc  (incident=749533):
ORA-00600: 内部错误代码, 参数: [3020], [2], [92323], [8480931], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 92323, file offset is 756310016 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 12330
Media Recovery failed with error 600
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 2  ...

利用Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查文件头相关信息，发现recover 失败的两个文件异常

通过Oracle Recovery Tools工具进行修复

数据库recover 成功，并顺利open

Tue Aug 02 10:56:13 2022
ALTER DATABASE RECOVER  datafile 1  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24972 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Completed: ALTER DATABASE RECOVER  datafile 1  
ALTER DATABASE RECOVER  datafile 2  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24972 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Completed: ALTER DATABASE RECOVER  datafile 2  
Tue Aug 02 10:56:34 2022
alter database open 
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 8504 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 24972, block 2, scn 177712270
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24972 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 24972, block 17011, scn 177734679
 0 data blocks read, 0 data blocks written, 8504 redo k-bytes read
Tue Aug 02 10:56:35 2022
Thread 1 advanced to log sequence 24973 (thread open)
Thread 1 opened at log sequence 24973
  Current log# 1 seq# 24973 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 02 10:56:35 2022
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
SMON: enabling tx recovery
*********************************************************************
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
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

增加tempfile,导出数据该库恢复完成

分类目录归档：小工具