标签云
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,664)
- DB2 (22)
- MySQL (72)
- Oracle (1,527)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (558)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (77)
- 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)
-
最近发表
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
标签归档:ORA-01122
12C sysaux 异常恢复—ORA-01190错误恢复
有朋友请求支援,他们数据库由于file 3 大量坏块,然后直接使用rman 备份还原了file 3,但是在recover过程中发现归档丢失,而且整个库在丢失归档的scn之后,还做过resetlogs操作,导致现在整个库无法正常启动,报ORA-01190错误,希望帮忙把file 3 给online起来,整个库正常open【当然在丢失sysaux的情况下,数据库可以open起来,但是这种情况下,迁移数据比较麻烦】
SQL> startup; ORACLE 例程已经启动。 Total System Global Area 3.1868E+10 bytes Fixed Size 3601144 bytes Variable Size 2.8655E+10 bytes Database Buffers 3154116608 bytes Redo Buffers 54804480 bytes 数据库装载完毕。 ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前 ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'
Oracle Database Recovery Check Result结果显示[脚本]
尝试不完全恢复并使用隐含参数打开库
Fri Oct 02 19:10:12 2015 ALTER DATABASE RECOVER database until cancel Fri Oct 02 19:10:12 2015 Media Recovery Start Started logmerger process Fri Oct 02 19:10:12 2015 Media Recovery failed with error 16433 Fri Oct 02 19:10:14 2015 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database until cancel ... Fri Oct 02 19:10:37 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5176.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:10:37 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5176.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. ALTER DATABASE RECOVER database until cancel Fri Oct 02 19:11:18 2015 Media Recovery Start Started logmerger process Fri Oct 02 19:11:18 2015 Media Recovery failed with error 16433 Fri Oct 02 19:11:19 2015 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database until cancel ... alter database open resetlogs ORA-1139 signalled during: alter database open resetlogs... alter database open Fri Oct 02 19:11:49 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_4252.trc: ORA-01190: 控制文件或数据文件 3 来自最后一个 RESETLOGS 之前 ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF' ORA-1190 signalled during: alter database open... Fri Oct 02 19:15:38 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5292.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:15:38 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_5292.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:20:39 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_2276.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:20:39 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_2276.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:25:40 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_4804.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:25:40 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_4804.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:30:41 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_876.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:30:41 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_m000_876.trc: ORA-16433: The database or pluggable database must be opened in read/write mode. Fri Oct 02 19:32:40 2015 Shutting down instance (abort)
数据库遭遇ORA-16433,此类方法无法打开数据库,根据经验值出现此类问题,可能需要重建控制文件,但是由于其中file 3的resetlogs scn不正确,无法包含该文件重建控制文件
Fri Oct 02 20:10:55 2015 WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command Default Temporary Tablespace will be necessary for a locally managed database in future release Fri Oct 02 20:10:55 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_5004.trc: ORA-01189: ????????????? RESETLOGS ORA-01110: ???? 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF' ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2921 LOGFILE GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M, GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG' size 50M, GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG' size 50M DATAFILE 'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF' CHARACTER SET AL32UTF8 ...
除掉file 3 继续重建控制文件
Fri Oct 02 20:33:11 2015 Successful mount of redo thread 1, with mount id 1419796614 Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2921 LOGFILE GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M, GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG' size 50M, GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG' size 50M DATAFILE 'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF', --'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF' CHARACTER SET AL32UTF8
继续恢复数据库
ALTER DATABASE OPEN Fri Oct 02 20:34:57 2015 ………… Archived Log entry 3 added for thread 1 sequence 8 ID 0x54a083a3 dest 1: Fri Oct 02 20:35:16 2015 Tablespace 'SYSAUX' #1 found in data dictionary, but not in the controlfile. Adding to controlfile. Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. File #3 found in data dictionary but not in controlfile. Creating OFFLINE file 'MISSING00003' in the controlfile. Corrected file 15 plugged in read-only status in control file Corrected file 16 plugged in read-only status in control file Corrected file 17 plugged in read-only status in control file Corrected file 18 plugged in read-only status in control file Corrected file 19 plugged in read-only status in control file Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed Fri Oct 02 20:35:19 2015 SMON: enabling tx recovery Fri Oct 02 20:35:19 2015 ********************************************************************* WARNING: The following temporary tablespaces in container(CDB$ROOT) contain no files. Starting background process SMCO Fri Oct 02 20:35:19 2015 SMCO started with pid=45, OS id=1500 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 ********************************************************************* Database Characterset is AL32UTF8 No Resource Manager plan active Fri Oct 02 20:35:21 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc: ORA-00376: 此时无法读取文件 3 ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件 ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003' Fri Oct 02 20:35:21 2015 Errors in file E:\APP\ORAADM\diag\rdbms\orcl\oaorcl\trace\oaorcl_ora_2220.trc: ORA-00376: 此时无法读取文件 3 ORA-01111: 数据文件 3 名称未知 - 请重命名以更正文件 ORA-01110: 数据文件 3: 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\MISSING00003' Error 376 happened during db open, shutting down database USER (ospid: 2220): terminating the instance due to error 376 Fri Oct 02 20:35:26 2015 Instance terminated by USER, pid = 2220 ORA-1092 signalled during: ALTER DATABASE OPEN... opiodr aborting process unknown ospid (2220) as a result of ORA-1092
此时由于file 3 未包含在控制文件中,但是存在数据字典中,因此在数据库open的时候出现了默认文件名MISSING0003,尝试重命名改文件指定为存在的file 3,并且尝试恢复
SQL> startup mount; ORACLE 例程已经启动。 Total System Global Area 3.1868E+10 bytes Fixed Size 3601144 bytes Variable Size 2.8655E+10 bytes Database Buffers 3154116608 bytes Redo Buffers 54804480 bytes 数据库装载完毕。 SQL> alter database datafile 3 offline; 数据库已更改。 SQL> alter database rename file 'C:\APP\ORAADM\PRODUCT\12.1.0\DBHOME_1\DATABASE\ MISSING00003' to 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF'; 数据库已更改。 SQL> recover database until cancel; ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\ORAADM\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_10_02\O1_MF_1_9_%U_.ARC ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG ORA-00310: archived log contains sequence 7; sequence 9 required ORA-00334: archived log: 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG' ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF' SQL> recover database until cancel; ORA-00279: 更改 617412726 (在 10/02/2015 20:35:06 生成) 对于线程 1 是必需的 ORA-00289: 建议: E:\APP\ORAADM\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_10_02\O1_MF_1_9_%U_.ARC ORA-00280: 更改 617412726 (用于线程 1) 在序列 #9 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG 已应用的日志。 完成介质恢复。 SQL> alter database datafile 3 online; 数据库已更改。 SQL> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-01122: 数据库文件 3 验证失败 ORA-01110: 数据文件 3: 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF' ORA-01202: 此文件的原型错误 - 创建时间错误
这里比较明显ORA-01202,由于创建控制文件之时没有file 3信息,因此导致控制文件中关于file 3的信息和该文件头的创建时间不一致(此处之时显示了时间不一致,如果通过bbed修改时间,后续可能还有很多东西不一致,因此通过bbed 一个个修改一个个尝试,理论可行,但实际可操作性不好),因此尝试直接使用bbed修改file 3文件头(由于是win环境,操作稍微麻烦点),把resetlogs信息修改和其他的一样
BBED> m /x 3c6b2b35 File: SYSAUX01.dbf (3) Block: 2 Offsets: 112 to 143 Dba:0x00c00002 ------------------------------------------------------------------------ 3c6b2b35 386b2200 00000000 00000000 00000000 00000000 00004000 bb460000 <32 bytes per line> BBED> set offset 116 OFFSET 116 BBED> m /x 3137ca24 File: SYSAUX01.dbf (3) Block: 2 Offsets: 116 to 147 Dba:0x00c00002 ------------------------------------------------------------------------ 3137ca24 00000000 00000000 00000000 00000000 00004000 bb460000 7dc12b35 <32 bytes per line> BBED> m /x b9f8 File: SYSAUX01.dbf (3) Block: 2 Offsets: 484 to 515 Dba:0x00c00002 ------------------------------------------------------------------------ b9f8a424 00000000 e65e2435 01000000 d3410000 b89b0000 10000900 02000000 <32 bytes per line> BBED> set offset +2 OFFSET 486 BBED> m /x cc24 File: SYSAUX01.dbf (3) Block: 2 Offsets: 486 to 517 Dba:0x00c00002 ------------------------------------------------------------------------ cc240000 0000e65e 24350100 0000d341 0000b89b 00001000 09000200 00000000 <32 bytes per line> BBED> m /x 87df offset 492 File: SYSAUX01.dbf (3) Block: 2 Offsets: 492 to 523 Dba:0x00c00002 ------------------------------------------------------------------------ 87df2435 01000000 d3410000 b89b0000 10000900 02000000 00000000 00000000 <32 bytes per line> BBED> BBED> m /x 2b35 offset +2 File: SYSAUX01.dbf (3) Block: 2 Offsets: 494 to 525 Dba:0x00c00002 ------------------------------------------------------------------------ 2b350100 0000d341 0000b89b 00001000 09000200 00000000 00000000 00000000 <32 bytes per line> BBED> d offset 140 File: SYSAUX01.dbf (3) Block: 2 Offsets: 140 to 171 Dba:0x00c00002 ------------------------------------------------------------------------ bb460000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> m /x 4248 File: SYSAUX01.dbf (3) Block: 2 Offsets: 140 to 171 Dba:0x00c00002 ------------------------------------------------------------------------ 42480000 7dc12b35 ba460000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> d offset 148 File: SYSAUX01.dbf (3) Block: 2 Offsets: 148 to 179 Dba:0x00c00002 ------------------------------------------------------------------------ ba460000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> m /x 4148 File: SYSAUX01.dbf (3) Block: 2 Offsets: 148 to 179 Dba:0x00c00002 ------------------------------------------------------------------------ 41480000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 3, Block 2: current = 0xd0c8, required = 0xd0c8 BBED> verify DBVERIFY - Verification starting FILE = SYSAUX01.dbf BLOCK = 1 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
修改完file 3的文件头之后,再次重建控制文件,此次包含file 3
Fri Oct 02 21:19:58 2015 Successful mount of redo thread 1, with mount id 1419797885 Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2921 LOGFILE GROUP 3 'E:\APP\ORAADM\ORADATA\ORCL\REDO03.LOG' size 50M, GROUP 2 'E:\APP\ORAADM\ORADATA\ORCL\REDO02.LOG' size 50M, GROUP 1 'E:\APP\ORAADM\ORADATA\ORCL\REDO01.LOG' size 50M DATAFILE 'E:\APP\ORAADM\ORADATA\ORCL\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\UNDOTBS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\USERS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSTEM01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SYSAUX01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\NMSA_BACKUP01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE1.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE01.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE02.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE03.DBF', 'E:\APP\ORAADM\ORADATA\ORCL\V3XSPACE04.DBF' CHARACTER SET AL32UTF8
继续恢复数据库,数据库正常open,而且file 3 已经正常online,数据库可以直接导出来,至此恢复大体完成
ORA-01122 ORA-01210 故障恢复
有朋友数据文件头出现错误ORA-01122和ORA-01210等错误,数据库无法正常open。
因为平台是win,他们找我咨询win bbed,因为回老家电脑没有带,无法提供win的bbed.我通过dd部分文件头,然后在linux平台分析发现是该文件的文件头block大量坏块
bbed分析坏块情况
BBED> show all FILE# 0 BLOCK# 1 OFFSET 0 DBA 0x00000000 (0 0,1) FILENAME /tmp/30.dbf BIFILE bifile.bbd LISTFILE BLOCKSIZE 8192 MODE Browse EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL No BBED> set count 64 COUNT 64 BBED> map File: /tmp/30.dbf (0) Block: 1 Dba:0x00000000 ------------------------------------------------------------ BBED-00400: invalid blocktype (27) BBED> d File: /tmp/30.dbf (0) Block: 1 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 03004400 bffd8a1d 0000000c acba0000 008f4500 00003455 fc020000 02040000 00000000 00008001 04000000 00000000 00000000 949400b4 94514005 <32 bytes per line> BBED> set block +1 BLOCK# 2 BBED> map File: /tmp/30.dbf (0) Block: 2 Dba:0x00000000 ------------------------------------------------------------ BBED-00400: invalid blocktype (27) BBED> d File: /tmp/30.dbf (0) Block: 2 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 04004400 bffd8a1d 0000000c a6e00000 008f4500 00003455 fc020000 0204e81f 00000000 0000241e 05000000 00000000 00000000 11fc297f b426fe2b <32 bytes per line> BBED> set block +1 BLOCK# 3 BBED> d File: /tmp/30.dbf (0) Block: 3 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 05004400 bffd8a1d 0000000c 780a0000 008f4500 00003455 fc020000 0204e81f 00000000 0000c001 06000000 00000000 00000000 2969a0d2 d30168a2 <32 bytes per line> BBED> set block +1 BLOCK# 4 BBED> d File: /tmp/30.dbf (0) Block: 4 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 06004400 bffd8a1d 0000000c 6c5a0000 008f4500 00003455 fc020000 0204e81f 00000000 0000f81d 07000000 00000000 00000000 7b51d409 6dc7ca4d <32 bytes per line> BBED> set block +1 BLOCK# 5 BBED> d File: /tmp/30.dbf (0) Block: 5 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 07004400 bffd8a1d 0000000c c5600000 008f4500 00003455 fc020000 02040000 00000000 0000c001 08000000 00000000 00000000 14514005 25145200 <32 bytes per line> BBED> set block +1 BLOCK# 6 BBED> d File: /tmp/30.dbf (0) Block: 6 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 08004400 bffd8a1d 0000000c 60480000 008f4500 00003455 fc020000 0204e81f 00000000 0000c301 09000000 00000000 00000000 c2a1606a 7615130a <32 bytes per line> BBED> set block +1 BLOCK# 7 BBED> d File: /tmp/30.dbf (0) Block: 7 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 09004400 bffd8a1d 0000000c e3430000 008f4500 00003455 fc020000 0204e81f 00000000 00000002 0a000000 00000000 00000000 00a28a28 00a28a28 <32 bytes per line> BBED> set block +1 BLOCK# 8 BBED> d File: /tmp/30.dbf (0) Block: 8 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0a004400 07fe8a1d 0000000c fc000000 008f4500 00003455 fc020000 0205e81f 00000000 0000f41d 00000000 00000000 00000000 ffd8ffe0 00104a46 <32 bytes per line> BBED> set block +1 BLOCK# 9 BBED> d File: /tmp/30.dbf (0) Block: 9 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0b004400 07fe8a1d 0000000c 48da0000 008f4500 00003455 fc020000 0205e81f 00000000 0000c601 01000000 00000000 00000000 b47d69d3 7fa96a6f <32 bytes per line> BBED> set block +1 BLOCK# 10 BBED> d File: /tmp/30.dbf (0) Block: 10 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0c004400 07fe8a1d 0000000c be0f0000 008f4500 00003455 fc020000 0205e81f 00000000 0000181d 02000000 00000000 00000000 9de3e868 4782d83a <32 bytes per line> BBED> set block +1 BLOCK# 11 BBED> d File: /tmp/30.dbf (0) Block: 11 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0d004400 07fe8a1d 0000000c 9cd00000 008f4500 00003455 fc020000 0205e81f 00000000 0000241e 03000000 00000000 00000000 dead1259 5919e385 <32 bytes per line> BBED> set block +1 BLOCK# 12 BBED> d File: /tmp/30.dbf (0) Block: 12 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0e004400 07fe8a1d 0000000c df450000 008f4500 00003455 fc020000 0205e81f 00000000 00004001 04000000 00000000 00000000 31d9a292 9698828a <32 bytes per line> BBED> set block +1 BLOCK# 13 BBED> d File: /tmp/30.dbf (0) Block: 13 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0f004400 07fe8a1d 0000000c 18790000 008f4500 00003455 fc020000 02050000 00000000 00000002 05000000 00000000 00000000 b93f8235 5ea063b7 <32 bytes per line>
拿block 1的rdba(04004400–倒序存储)分析[win文件拷贝到linux后使用bbed查看相差1 block]可以的出来block信息为file=1, block=262148,明显错误.
通过dul分析文件头损坏情况
Data UnLoader: 10.2.0.6.9 - Internal Only - on Tue Sep 29 22:15:22 2015 with 64-bit io functions Copyright (c) 1994 2015 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: Recreating file "dul.log" Reading SCANNEDLOBPAGE.dat 1204 entries loaded and sorted 1204 entries Reading SEG.dat 0 entries loaded Reading EXT.dat 44 entries loaded and sorted 44 entries Reading COMPATSEG.dat 0 entries loaded DUL: Warning: Wrong DBA 0X00440004 (file=1, block=262148) (Ignored) DUL: Error: While processing file# 30 block# 1 DUL: Warning: Found mismatch while checking file E:\TEMP\shebao\30.dbf DUL: Warning: DUL osd_parameter or control.dul configuration error DUL: Warning: Given file number(30) in control file does not match file# in dba(1) DUL: Warning: Wrong DBA 0X00440004 (file=1, block=262148) (Ignored) DUL: Error: While processing file# 30 block# 1 DUL>
通过bbed和dul证明文件头大量损坏,而且尚未有任何该文件的物理备份,因此恢复起来难道较大。
分析Oracle Database Recovery Check Result
通过对Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)的分析结果,我们意外的发现,人品不错,发现异常的文件创建时间为2015-09-26 19:39:33,进一步和客户沟通,这个文件存储为图片,少量丢失可以允许,优先恢复业务
有了这个结论,那处理起来就easy了,直接offline异常文件,然后分析丢失的表
从而确定时lob字典的少量extent数据分配到了file 30上
为了避免查询对应lob之时出现错误,通过update 对应lob为空规避该问题
create table corrupt_lobs (corrupt_rowid rowid,table_name varchar2(100)); declare n number; begin for cursor_lob in (select rowid r, xff_lob from xff.t_xifenfei) loop begin n:=dbms_lob.instr(cursor_lob.xff_lob,hextoraw('889911')); exception when others then insert into corrupt_lobs values (cursor_lob.r,'xff.t_xifenfei'); commit; end; end loop; end; / update xff.t_xifenfei set xff_lob = empty_blob() where rowid in (select corrupted_rowid from corrupt_lobs);
本次恢复是由于运气好,遇到异常文件刚好是最近加入,而且都是图片,客户允许少量丢失,如果是不允许丢失的数据文件,可能需要通过找历史的该文件的备份(Oracle 12C的第一次异常恢复—文件头坏块),在某些情况下,如果也没有此类备份,只能通过bbed重构block 1(如果有其他异常块一次处理,如果太多无法处理,最少也需要重构block 1),然后尝试open数据库或者使用dul之类工具处理(因为文件头损坏,工具可能不能识别文件无法恢复)