小工具 – Database SOS

数据库异常断电,然后启动异常,我接手该库,尝试recover恢复

SQL> recover database;
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
63710)
ORA-10564: tablespace SYSAUX
ORA-01110: ???????? 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 179030
ORA-00600: ????????????, ????: [6101], [0], [700], [32], [], [], [], [], [],
[], [], []

SQL> recover datafile 1;
完成介质恢复。
SQL> recover datafile 3;
完成介质恢复。
SQL> recover datafile 4;
完成介质恢复。
SQL> recover datafile 5;
完成介质恢复。
SQL> recover datafile 6;
完成介质恢复。
SQL> recover datafile 2;
ORA-00283: ??????????
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
63744)
ORA-10564: tablespace SYSAUX
ORA-01110: ???? 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 67482
ORA-00600: ??????, ??: [kdxlin:psno out of range], [], [], [], [], [], [], [],
[], [], [], []

alert日志信息

Mon Apr 14 12:31:55 2025
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 20 slaves
Mon Apr 14 12:31:56 2025
Recovery of Online Redo Log: Thread 1 Group 2 Seq 6845 Reading mem 0
  Mem# 0: H:\TEMP\GDLISNET\REDO02.LOG
Mon Apr 14 12:31:56 2025
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr0e_9840.trc  (incident=1489):
ORA-00600: 内部错误代码, 参数: [6101], [0], [700], [32], [], [], [], [], [], [], [], []
Incident details in: c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\incident\incdir_1489\gdlisnet_pr0e_9840_i1489.trc
Slave exiting with ORA-10562 exception
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr0e_9840.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 63710)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 179030
ORA-00600: 内部错误代码, 参数: [6101], [0], [700], [32], [], [], [], [], [], [], [], []
Mon Apr 14 12:31:57 2025
Checker run found 6 new persistent data failures
Recovery Slave PR0E previously exited with exception 10562
Media Recovery failed with error 448
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr00_14232.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00448: 后台进程正常结束
Slave exiting with ORA-283 exception
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_pr00_14232.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00448: 后台进程正常结束
ORA-10562 signalled during: ALTER DATABASE RECOVER  database  ...

ALTER DATABASE RECOVER  datafile 2  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 6845 Reading mem 0
  Mem# 0: H:\TEMP\GDLISNET\REDO02.LOG
Errors in file c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\trace\gdlisnet_ora_22400.trc  (incident=1369):
ORA-00600: ??????, ??: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\xff\diag\rdbms\gdlisnet\gdlisnet\incident\incdir_1369\gdlisnet_ora_22400_i1369.trc
Media Recovery failed with error 10562
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 2  ...

发现2号数据文件异常,报ORA-600 6101和ORA-600 kdxlin:psno out of range错误,出现该错误的原因主要是由于数据文件的bock和redo中信息不匹配导致,对于这种情况,我这边选择使用Oracle Recovery Tools工具进行快速恢复

再次recover并且直接打开库

SQL> set numw 16
SQL> col CHECKPOINT_TIME for a40
SQL> set lines 150
SQL> set pages 1000
SQL> SELECT status,
  2  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
  3  count(*) ROW_NUM
  4  FROM v$datafile_header
  5  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
  6  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
ONLINE  2025-03-28 22:00:09                      NO           118575875                6

SQL>
SQL>
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 2 需要介质恢复
ORA-01110: 数据文件 2: 'H:\TEMP\GDLISNET\SYSAUX01.DBF'


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

数据库已更改。

SQL>

逻辑方法导出该库,完成该库的恢复工作

Patch SCN工具for Linux

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

标题：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 ;

未选定行

分类目录归档：小工具