标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,682)
- DB2 (22)
- MySQL (73)
- Oracle (1,544)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (53)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (565)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 断电引起的ORA-08102: 未找到索引关键字, 对象号 39故障处理
- ORA-00227: corrupt block detected in control file
- 手工删除19c rac
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
标签归档:ORA-01110
system01.dbf文件被offline,导致数据库报ORA-01245 ORA-01110故障恢复
对于这样的情况,可以使用自研的Oracle Recovery Tools工具快速修改scn,然后online数据文件即可
有朋友找到我,说数据库做recover报ORA-01245和ORA-01110错误,无法继续恢复,请求支持
SQL> recover database using backup controlfile until cancel; ………… 第 1 行出现错误: ORA-01245: RESETLOGS 完成时脱机文件 1 将丢失 ORA-01110: 数据文件 1: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\SYSTEM01.DBF'
通过Oracle Database Recovery Check检查数据库情况,发现datafile 1处于offline状态
Wed Aug 26 23:11:00 2015 alter database datafile 1 offline drop Completed: alter database datafile 1 offline drop
从这里基本上可以知道为什么出现ORA-01245错误了,由于system表空间中文件被offline导致.
redo信息
Mon Aug 24 22:38:35 2015 alter database clear unarchived logfile group 2 Clearing online log 2 of thread 1 sequence number 5705 Completed: alter database clear unarchived logfile group 2 Wed Aug 26 23:13:23 2015 alter database clear logfile group 3 Clearing online log 3 of thread 1 sequence number 5706 Completed: alter database clear logfile group 3
除当前redo之外,其他redo被clear
尝试恢复
SQL> alter database datafile 1 online; 数据库已更改。 SQL> recover database; ORA-00283: 恢复会话因错误而取消 ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成 SQL> recover database using backup controlfile; ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570 5_%U_.ARC ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG ORA-00310: 归档日志包含序列 5706; 要求序列 5705 ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG' SQL> recover database using backup controlfile; ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570 5_%U_.ARC ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG ORA-00339: 归档日志未包含任何重做 ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG' SQL> recover database using backup controlfile; ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570 5_%U_.ARC ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG ORA-00310: 归档日志包含序列 5707; 要求序列 5705 ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG'
数据库做恢复需要seq 5705的redo,但是redo已经被clear,导致现在数据库常规手段无法恢复,只用使用隐含参数屏蔽数据库前滚(一致性检查)
再次尝试打开数据库
ORACLE 例程已经启动。 Total System Global Area 778387456 bytes Fixed Size 1374808 bytes Variable Size 486540712 bytes Database Buffers 285212672 bytes Redo Buffers 5259264 bytes 数据库装载完毕。 SQL> recover database using backup controlfile; ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570 5_%U_.ARC ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} cancel 介质恢复已取消。 SQL> alter database open resetlogs; 数据库已更改。
在数据库恢复中,请不要对system表空间数据文件进行offline操作,如果对此类文件进行offline操作,讲在数据库恢复过程中出现ORA-01245和ORA-01110错误,而且文件还会出现SYSOFF状态
aix平台 ORA-01115 ORA-01110 ORA-27067 故障恢复
接到朋友恢复请求,aix 5.3,Oracle 10.2.0.1平台,数据库启动报ORA-01115 ORA-01110 ORA-27067错误,数据库无法正常打开,通过分析,是由于10201在aix上面的bug导致,通过技巧规避,完美解决给问题,数据0丢失
数据库报错alert日志
Mon Aug 10 13:25:22 2015 ALTER DATABASE MOUNT Mon Aug 10 13:25:29 2015 Setting recovery target incarnation to 1 Mon Aug 10 13:25:29 2015 Successful mount of redo thread 1, with mount id 432339141 Mon Aug 10 13:25:29 2015 Database mounted in Exclusive Mode Completed: ALTER DATABASE MOUNT Mon Aug 10 13:25:36 2015 alter database open Mon Aug 10 13:25:36 2015 Beginning crash recovery of 1 threads parallel recovery started with 15 processes Mon Aug 10 13:25:37 2015 Started redo scan Mon Aug 10 13:25:52 2015 Completed redo scan 7889582 redo blocks read, 75305 data blocks need recovery Mon Aug 10 13:25:53 2015 Errors in file /dc/admin/datacent/bdump/datacent_p002_144124.trc: ORA-01115: IO error reading block from file 2 (block # 40704) ORA-01110: data file 2: '/dc/oradata/datacent/undotbs01.dbf' ORA-27067: size of I/O buffer is invalid Additional information: 2 Additional information: 1572864 Mon Aug 10 13:25:53 2015 Aborting crash recovery due to slave death, attempting serial crash recovery Mon Aug 10 13:25:53 2015 Beginning crash recovery of 1 threads Mon Aug 10 13:25:53 2015 Started redo scan Mon Aug 10 13:26:09 2015 Completed redo scan 7889582 redo blocks read, 75305 data blocks need recovery Mon Aug 10 13:26:12 2015 Aborting crash recovery due to error 1115 Mon Aug 10 13:26:12 2015 Errors in file /dc/admin/datacent/udump/datacent_ora_123384.trc: ORA-01115: IO error reading block from file 2 (block # 39077) ORA-01110: data file 2: '/dc/oradata/datacent/undotbs01.dbf' ORA-27067: size of I/O buffer is invalid Additional information: 2 Additional information: 1310720 ORA-1115 signalled during: alter database open...
这里报的前面两个错误ORA-01115 ORA-01110我们都非常熟悉,类似数据库启动遇到坏块或者io错误之时可能就会报如此错误。但是ORA-27067确实不多见,从mos上看,很多是由于rman备份之时的bug可能导致该错误。
dbv检测undo坏块文件
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Aug 10 23:18:15 2015 Copyright (c) 1982, 2003, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /dc/oradata/datacent/undotbs01.dbf DBVERIFY - Verification complete Total Pages Examined : 329600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 327504 Total Pages Processed (Seg) : 17 Total Pages Failing (Seg) : 0 Total Pages Empty : 2096 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 1887888 (0.1887888)
这里可以看到,undo文件本身并没有逻辑和物理的坏块,证明因为数据库异常的原因,可能是由于ORA-27067: size of I/O buffer is invalid导致。根据官方文档ORA-01115 ORA-27067 DURING PARALLEL INSTANCE RECOVERY AFTER INSTANCE CRASH中的解释,我们基本上可以确定很可能是由于10.2.0.1在aix平台的jfs2系统中,由于大量事务操作,突然abort掉数据库(也可能断电),从而数据库在启动的时候进行实例恢复,而由于内部的bug,导致实例恢复无法成功。通过我们处理后的,数据库完美启动,数据0丢失
数据库启动日志
Mon Aug 10 16:34:14 2015 alter database open Mon Aug 10 16:34:14 2015 Beginning crash recovery of 1 threads parallel recovery started with 15 processes Mon Aug 10 16:34:14 2015 Started redo scan Mon Aug 10 16:34:27 2015 Completed redo scan 7889582 redo blocks read, 0 data blocks need recovery Mon Aug 10 16:34:27 2015 Started redo application at Thread 1: logseq 664704, block 1286922 Mon Aug 10 16:34:27 2015 Recovery of Online Redo Log: Thread 1 Group 4 Seq 664704 Reading mem 0 Mem# 0 errs 0: /dev/rredo04 Mon Aug 10 16:34:32 2015 Recovery of Online Redo Log: Thread 1 Group 5 Seq 664705 Reading mem 0 Mem# 0 errs 0: /dev/rredo05 Mon Aug 10 16:34:38 2015 Recovery of Online Redo Log: Thread 1 Group 6 Seq 664706 Reading mem 0 Mem# 0 errs 0: /dev/rredo06 Mon Aug 10 16:34:40 2015 Completed redo application Mon Aug 10 16:34:40 2015 Completed crash recovery at Thread 1: logseq 664706, block 1017805, scn 8554793334 0 data blocks read, 0 data blocks written, 7889582 redo blocks read Mon Aug 10 16:34:40 2015 Thread 1 advanced to log sequence 664707 Thread 1 opened at log sequence 664707 Current log# 1 seq# 664707 mem# 0: /dev/rredo01 Successful open of redo thread 1 Mon Aug 10 16:34:40 2015 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Aug 10 16:34:40 2015 SMON: enabling cache recovery Mon Aug 10 16:34:40 2015 Successfully onlined Undo Tablespace 1. Mon Aug 10 16:34:40 2015 SMON: enabling tx recovery Mon Aug 10 16:34:41 2015 Database Characterset is ZHS32GB18030 replication_dependency_tracking turned off (no async multimaster replication found) WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected. Mon Aug 10 16:34:41 2015 SMON: Parallel transaction recovery tried Mon Aug 10 16:34:42 2015 db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Mon Aug 10 16:34:42 2015 Completed: alter database open
分享一次ORA-01113 ORA-01110故障处理过程
近期编写了小工具,对于此类问题,可以实现一键恢复,参考:一键恢复ORA-01113 ORA-01110—Oracle Recovery Tools
数据库报ORA-01113 ORA-01110无法正常open
SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
alert日志
Wed Jul 16 17:17:56 2014 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 1380870980 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Wed Jul 16 17:19:01 2014 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc (incident=367575): ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\metadata\HM_FINDING.ams], [1245], [], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_367575\orcl_m000_3540_i367575.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc (incident=367576): ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [1245], [0], [80], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_367576\orcl_m000_3540_i367576.trc Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_3540.trc: ORA-51106: check failed to complete due to an error. See error below ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [1245], [0], [80], [], [], [], [], [], [], [], [] ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
这里出现ORA-00700[dbgrmblcp_corrupt_page]与ORA-00600[dbgrmblgp_get_page_1]是Bug 10321285 : ORA-600 [DBGRMBLCP_CORRUPT_PAGE]
尝试recover database 遭遇ORA-00283 ORA-16433
SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode.
alert日志信息
Wed Jul 16 17:36:33 2014 ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Wed Jul 16 17:36:33 2014 Media Recovery failed with error 16433 Slave exiting with ORA-283 exception Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_pr00_3868.trc: ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode. Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database ... Wed Jul 16 17:36:45 2014 alter database open Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3236.trc: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open...
重建控制文件后恢复,遭遇ORA-600 2662错误
SQL> shutdown abort ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 10255212544 bytes Fixed Size 2264088 bytes Variable Size 4529849320 bytes Database Buffers 5704253440 bytes Redo Buffers 18845696 bytes SQL> @d:\ctl.sql Control file created. SQL> recover database using backup controlfile; ORA-00279: change 13953689551797 generated at 07/16/2014 17:53:41 needed for thread 1 ORA-00289: suggestion : D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_07_16\O1_MF_1_1_%U_ .ARC ORA-00280: change 13953689551797 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} d:\app\administrator\oradata\orcl\redo01.log Log applied. Media recovery complete. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [2662], [3248], [3635774399], [3249], [3580470066], [4194545], [], [], [], [], [], [] Process ID: 3932 Session ID: 200 Serial number: 1
因为该数据库是11.2.0.3未打上scn patch,直接推进scn(可以使用event,隐含参数,oradebug,bbed等),数据库就正常open