标签云
asm 恢复 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 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)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:十月 2015
ORA-65101 container database set up incorrectly
老朋友找我帮忙,说他们从笔记本中拷贝数据库到客户服务器上,无法启动,让我帮忙看下
启动报ORA-00211 ORA-00202错误
C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on 星期二 10月 13 21:35:45 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. 连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions SQL> select open_MODE FROM V$DATABASE; select open_MODE FROM V$DATABASE * 第 1 行出现错误: ORA-01507: ?????? SQL> alter database mount; alter database mount * 第 1 行出现错误: ORA-00211: control file does not match previous control files ORA-00202: control file: ''D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL12C\CONTROL02.CTL''
控制文件版本不匹配
这个错误很明显数据库中多个控制文件版本不一致,使用其中一个试试看
SQL> show parameter control; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ control_file_record_keep_time integer 7 control_files string D:\APP\ADMINISTRATOR\ORADATA\O RCL12C\CONTROL01.CTL, D:\APP\A DMINISTRATOR\FAST_RECOVERY_ARE A\ORCL12C\CONTROL02.CTL control_management_pack_access string DIAGNOSTIC+TUNING SQL> alter system set control_files='d:\app\administrator\oradata\orcl12c\contro l01.ctl' scope=spfile; 系统已更改。 SQL> shutdown abort ORACLE 例程已经关闭。 SQL> startup ORACLE 例程已经启动。 Total System Global Area 1703624704 bytes Fixed Size 2403448 bytes Variable Size 1090519944 bytes Database Buffers 603979776 bytes Redo Buffers 6721536 bytes ORA-65101:container database set up incorrectly
数据库此处报ORA-65101错误,官方解释
解释很清楚,使用一个no cdb的控制文件启动一个cdb的库,因此出现ORA-65101错误。官方解决方案:重建控制文件,但是本库通过分析是数据文件和控制文件一起拷贝过来,应该不是改问题
Error code: ORA-65101 Description: container database set up incorrectly Cause: An attempt was made to use a non container database control file to startup a container database (CDB). Action: Create a new control file for the CDB.
检查enable_pluggable_database参数
果然该参数设置为cdb模式,和控制文件的no cdb不匹配,因此出现该问题
SQL> show parameter enable_pluggable_database NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ enable_pluggable_database boolean TRUE SQL> SQL> alter system set enable_pluggable_database=false scope=spfile; 系统已更改。 SQL> shutdown abort ORACLE 例程已经关闭。 SQL> startup ORACLE 例程已经启动。 Total System Global Area 1703624704 bytes Fixed Size 2403448 bytes Variable Size 1090519944 bytes Database Buffers 603979776 bytes Redo Buffers 6721536 bytes 数据库装载完毕。 数据库已经打开。
ORA-15042: ASM disk “N” is missing from group number “M” 故障恢复
接到一个朋友恢复请求,19个lun的asm 磁盘组,由于其中一个lun有问题,他们进行了增加一个新lun,删除老lun的方法操作,但是操作一半hang住了(因为坏的lun是底层损坏,无法完成rebalance),然后存储工程师继续修复异常lun,非常幸运异常lun修复好了,但是高兴过了头,直接从存储上删除了新加入的lun(已经rebalance一部分数据进去了),这个时候asm dg彻底趴下了,不能mount成功,请求恢复支持。由于某种原因,无法从lun层面恢复,只能让我们提供数据库层面恢复
Mon Sep 21 19:52:35 2015 SQL> alter diskgroup dg_XFF add disk '/dev/rhdisk116' size 716800M drop disk dg_XFF_0012 NOTE: Assigning number (1,20) to disk (/dev/rhdisk116) NOTE: requesting all-instance membership refresh for group=1 NOTE: initializing header on grp 1 disk DG_XFF_0020 NOTE: requesting all-instance disk validation for group=1 Mon Sep 21 19:52:44 2015 NOTE: skipping rediscovery for group 1/0xb94738f1 (DG_XFF) on local instance. NOTE: requesting all-instance disk validation for group=1 NOTE: skipping rediscovery for group 1/0xb94738f1 (DG_XFF) on local instance. NOTE: initiating PST update: grp = 1 Mon Sep 21 19:52:44 2015 GMON updating group 1 at 25 for pid 27, osid 12124486 NOTE: PST update grp = 1 completed successfully NOTE: membership refresh pending for group 1/0xb94738f1 (DG_XFF) GMON querying group 1 at 26 for pid 18, osid 10092734 NOTE: cache opening disk 20 of grp 1: DG_XFF_0020 path:/dev/rhdisk116 GMON querying group 1 at 27 for pid 18, osid 10092734 SUCCESS: refreshed membership for 1/0xb94738f1 (DG_XFF) Mon Sep 21 19:52:47 2015 SUCCESS: alter diskgroup dg_XFF add disk '/dev/rhdisk116' size 716800M drop disk dg_XFF_0012 NOTE: starting rebalance of group 1/0xb94738f1 (DG_XFF) at power 1 Starting background process ARB0 Mon Sep 21 19:52:47 2015 ARB0 started with pid=28, OS id=10944804 NOTE: assigning ARB0 to group 1/0xb94738f1 (DG_XFF) with 1 parallel I/O NOTE: Attempting voting file refresh on diskgroup DG_XFF Mon Sep 21 20:35:06 2015
SQL> ALTER DISKGROUP DG_XFF MOUNT /* asm agent *//* {1:51107:7083} */ NOTE: cache registered group DG_XFF number=1 incarn=0xdd6f975a NOTE: cache began mount (first) of group DG_XFF number=1 incarn=0xdd6f975a NOTE: Assigning number (1,0) to disk (/dev/rhdisk10) NOTE: Assigning number (1,1) to disk (/dev/rhdisk11) NOTE: Assigning number (1,2) to disk (/dev/rhdisk16) NOTE: Assigning number (1,3) to disk (/dev/rhdisk17) NOTE: Assigning number (1,4) to disk (/dev/rhdisk22) NOTE: Assigning number (1,5) to disk (/dev/rhdisk23) NOTE: Assigning number (1,6) to disk (/dev/rhdisk28) NOTE: Assigning number (1,7) to disk (/dev/rhdisk29) NOTE: Assigning number (1,8) to disk (/dev/rhdisk33) NOTE: Assigning number (1,9) to disk (/dev/rhdisk34) NOTE: Assigning number (1,10) to disk (/dev/rhdisk4) NOTE: Assigning number (1,11) to disk (/dev/rhdisk40) NOTE: Assigning number (1,12) to disk (/dev/rhdisk41) NOTE: Assigning number (1,13) to disk (/dev/rhdisk45) NOTE: Assigning number (1,14) to disk (/dev/rhdisk46) NOTE: Assigning number (1,15) to disk (/dev/rhdisk5) NOTE: Assigning number (1,16) to disk (/dev/rhdisk52) NOTE: Assigning number (1,17) to disk (/dev/rhdisk53) NOTE: Assigning number (1,18) to disk (/dev/rhdisk57) NOTE: Assigning number (1,19) to disk (/dev/rhdisk58) Wed Sep 30 11:08:07 2015 NOTE: start heartbeating (grp 1) GMON querying group 1 at 33 for pid 35, osid 4194488 NOTE: Assigning number (1,20) to disk () GMON querying group 1 at 34 for pid 35, osid 4194488 NOTE: cache dismounting (clean) group 1/0xDD6F975A (DG_XFF) NOTE: dbwr not being msg'd to dismount NOTE: lgwr not being msg'd to dismount NOTE: cache dismounted group 1/0xDD6F975A (DG_XFF) NOTE: cache ending mount (fail) of group DG_XFF number=1 incarn=0xdd6f975a NOTE: cache deleting context for group DG_XFF 1/0xdd6f975a GMON dismounting group 1 at 35 for pid 35, osid 4194488 NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment ERROR: diskgroup DG_XFF was not mounted ORA-15032: not all alterations performed ORA-15040: diskgroup is incomplete ORA-15042: ASM disk "20" is missing from group number "1" ERROR: ALTER DISKGROUP DG_XFF MOUNT /* asm agent *//* {1:51107:7083} */
这里比较明显,由于存储工程师直接删除了lun,这里导致磁盘组DG_XFF丢失asm disk 20,使得磁盘组无法直接mount,由于该磁盘组已经进行了较长时间的rebalance,丢失的盘中已经有大量数据(包括元数据),因此就算修改pst让磁盘组mount起来(不一定成功),也会丢失大量数据,也不一定可以直接拿出来里面的数据,如果只是加入盘,但是由于某种原因没有做rebalance,那我们直接可以通过修改pst,使得磁盘组mount起来。因此对于这样的情况,我们能够做的,只能从底层扫描磁盘,生成数据文件(因为有部分文件的元数据在丢失lun之上,如果直接使用现存元数据信息,直接拷贝,或者unload数据都会丢失大量数据),然后再进一步unload数据,完成恢复。需要恢复磁盘信息
grp# dsk# bsize ausize disksize diskname groupname path ---- ---- ----- ------ -------- --------------- --------------- ------------- 1 0 4096 4096K 179200 DG_XFF_0000 DG_XFF /dev/rhdisk10 1 1 4096 4096K 179200 DG_XFF_0001 DG_XFF /dev/rhdisk11 1 2 4096 4096K 179200 DG_XFF_0002 DG_XFF /dev/rhdisk16 1 3 4096 4096K 179200 DG_XFF_0003 DG_XFF /dev/rhdisk17 1 4 4096 4096K 179200 DG_XFF_0004 DG_XFF /dev/rhdisk22 1 5 4096 4096K 179200 DG_XFF_0005 DG_XFF /dev/rhdisk23 1 6 4096 4096K 179200 DG_XFF_0006 DG_XFF /dev/rhdisk28 1 7 4096 4096K 179200 DG_XFF_0007 DG_XFF /dev/rhdisk29 1 8 4096 4096K 179200 DG_XFF_0008 DG_XFF /dev/rhdisk33 1 9 4096 4096K 179200 DG_XFF_0009 DG_XFF /dev/rhdisk34 1 10 4096 4096K 179200 DG_XFF_0010 DG_XFF /dev/rhdisk4 1 11 4096 4096K 179200 DG_XFF_0011 DG_XFF /dev/rhdisk40 1 12 4096 4096K 179200 DG_XFF_0012 DG_XFF /dev/rhdisk41 1 13 4096 4096K 179200 DG_XFF_0013 DG_XFF /dev/rhdisk45 1 14 4096 4096K 179200 DG_XFF_0014 DG_XFF /dev/rhdisk46 1 15 4096 4096K 179200 DG_XFF_0015 DG_XFF /dev/rhdisk5 1 16 4096 4096K 179200 DG_XFF_0016 DG_XFF /dev/rhdisk52 1 17 4096 4096K 179200 DG_XFF_0017 DG_XFF /dev/rhdisk53 1 18 4096 4096K 179200 DG_XFF_0018 DG_XFF /dev/rhdisk57 1 19 4096 4096K 179200 DG_XFF_0019 DG_XFF /dev/rhdisk58
这次运气比较好,丢失的磁盘组只是一个业务磁盘组,而且里面只有19个表空间,10个分区表,因此在数据字典完成的情况下,恢复10个分区表(一共6443个分区)的数据,整体恢复效果如下:
从整体数据量看恢复比例为:6003.26953/6027.26935*100%=99.6018127%,对于丢失了一个已经rebalance的大部分的lun,依旧能够恢复如此的数据,整体看非常理想.
如果您遇到此类情况,无法解决请联系我们,提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
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,数据库可以直接导出来,至此恢复大体完成