联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
对于oracle的有些故障,如果数据库完全open会报错,导致启动失败,这个时候,我们可以尝试在数据库open到一半的过程中把启动过程挂起,然后登录数据库对字典进行修复,然后就可以正常启动数据库。非win平台可以通过gdb挂起数据库启动过程,因为win系统是进程模式,无法通过gdb挂起来实现,通过自己写程序修改oracle内存来实现这个功能
在Oracle Recovery Tools工具中,点击修改内存—>检索Oracle数据库进程—>选择需要操作的数据库进程—>挂起Oracle进程 就可以实现oracle数据库在open的过程中挂住
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle Recovery Tools恢复案例总结—202505
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
开发出来Oracle Recovery Tools小工具已经一段时间,而且在大量的客户恢复case中使用,大大的提高的恢复效率,特别是win平台需要bbed或者类似工具的时候.现在对该工具在实战中的一些case进行总结:
Oracle Recovery Tools修复空闲坏块
Oracle Recovery Tools实战批量坏块修复
Oracle Recovery Tools快速恢复ORA-19909
Oracle Recovery Tools 解决ORA-600 3020故障
Oracle Recovery Tools恢复csc higher than block scn
Oracle Recovery Tools恢复MISSING00000文件故障
Oracle Recovery Tools快速恢复重建ctl遗漏数据文件故障
一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools
Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障
Oracle Recovery Tools快速解决sysaux文件不能online问题
Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障
ORA-01113 ORA-01110错误不一定都要Oracle Recovery Tools解决
Oracle Recovery Tools解决ORA-00279 ORA-00289 ORA-00280故障
Oracle Recovery Tools修复ORA-600 6101/kdxlin:psno out of range故障
Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障（文件offline）
Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障
Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题）
软件下载:OraRecovery下载
使用说明:使用说明
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle Recovery Tools修复ORA-600 6101/kdxlin:psno out of range故障
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库异常断电,然后启动异常,我接手该库,尝试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>
逻辑方法导出该库,完成该库的恢复工作
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障（文件offline）
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户在win上面迁移数据文件,由于原库非归档,结果导致有两个文件scn不一致,无法打开库,结果他们选择offline文件,然后打开数据库
Wed Dec 04 14:06:04 2024 alter database open Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6056.trc: ORA-01113: 文件 10 需要介质恢复 ORA-01110: 数据文件 10: 'C:\PROGRAM FILES\ORACLE\XFF1.DBF' ORA-1113 signalled during: alter database open... Wed Dec 04 14:08:18 2024 alter database datafile 'c:\program files\oracle\XFF1.dbf' offline drop Completed: alter database datafile 'c:\program files\oracle\XFF1.dbf' offline drop Wed Dec 04 14:08:31 2024 alter database open Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6056.trc: ORA-01113: 文件 26 需要介质恢复 ORA-01110: 数据文件 26: 'C:\PROGRAM FILES\ORACLE\XFF2.DBF' ORA-1113 signalled during: alter database open... Wed Dec 04 14:08:31 2024 Checker run found 1 new persistent data failures Wed Dec 04 14:08:51 2024 alter database datafile 'c:\program files\oracle\XFF2.dbf' offline drop Completed: alter database datafile 'c:\program files\oracle\XFF2.dbf' offline drop alter database open Wed Dec 04 14:08:57 2024 Thread 1 opened at log sequence 136210 Current log# 1 seq# 136210 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 Wed Dec 04 14:08:57 2024 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 AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Wed Dec 04 14:08:59 2024 QMNC started with pid=20, OS id=4264 Completed: alter database open
后面自行尝试recover 数据文件没有成功
Wed Dec 04 14:42:50 2024 ALTER DATABASE RECOVER datafile 26 Media Recovery Start Serial Media Recovery started ORA-279 signalled during: ALTER DATABASE RECOVER datafile 26 ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2024_12_04\O1_MF_1_135983_%U_.ARC Errors with log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2024_12_04\O1_MF_1_135983_%U_.ARC ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL
由于这两个文件处于offline状态导致客户很多操作报ORA-00376 ORA-01110之类错
ORA-00376: file 10 cannot be read at this time ORA-01110: data file 10: 'C:\PROGRAM FILES\ORACLE\XFF1.DBF'
对于这类故障使用Oracle Recovery Tools工具,一键恢复
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
国内xx医院使用了国外医疗行业龙头的pacs系统,由于是一个历史库,存放在分布式存储中,由于存储同时多个节点故障,导致数据库多个文件异常,数据库无法启动,三方维护人员尝试通通过rman归档进行应用日志,结果发现日志有损坏报ORA-00354 ORA-00353,无法记录恢复,希望我们给予支持
Mon Apr 29 13:28:40 2024 Media Recovery failed with error 354 Mon Apr 29 13:28:40 2024 Errors in file F:\XXXXXX_DB\ORACLE\ADMIN\diag\rdbms\xxx\msxxx1\trace\xxx_pr00_4568.trc: ORA-00283: recovery session canceled due to errors ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 487424 change 8737273868 time 04/01/2024 01:38:25 ORA-00334: archived log: 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001' ORA-283 signalled during: alter database recover logfile 'F:\XXXXXX_DB\ORADATA\XXX\LOGARC0000052184_0922116268.0001'...
接手故障之后,通过尝试恢复发现除该错误之外,还有ORA-600 4552之类错误
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle Recovery Tools恢复csc higher than block scn
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户强制关闭数据库,结果有数据块报坏块,dbv检查为:csc higher than block scn问题
联系：手机/微信(+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功能,可以更加快速的解决给问题
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再次进行恢复
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说明
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle Recovery Tools更新—202304
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近对Oracle Recovery Tools工具进行了升级,增加了在某些恢复情况下对于单个文件的CheckPoint Time的显示,便于判断文件的有效性(便于历史文件区分),该软件是一个恢复小工具,主要定位:
1. 对于某些恢复场景的快速判断,便于问题定位和后续恢复
2. 实现图形化快速修复损坏的oracle数据块和构造块
3. 实现图形化快速修改文件头信息,代替bbed解决文件头的各种scn等问题
4. 快速修改内存中记录,在某些强制拉库的过程中提供便利,特别是在win平台后续版本中无法通过oradebug等方式修改的情况下使用
联系：手机/微信(+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工具,可以快速修复
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
联系：手机/微信(+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;
表空间已更改。
数据导出成功
