标签云
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,666)
- DB2 (22)
- MySQL (73)
- Oracle (1,528)
- 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备份恢复 (559)
- 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)
-
最近发表
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过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故障处理
标签归档:ORA-1194
重建控制文件丢失数据文件导致悲剧
在Oracle职业生涯中,恢复过生产环境数据库也有几百个.对于Oracle恢复我还是相当的自信,今天因为自己的一时过于自信,对于环境错了错误的判断,简单问题复杂化,差点变成悲剧
开发出来了Oracle Recovery Tools恢复MISSING00000文件故障工具,能够一键解决类似问题,实现快速恢复
数据库最初故障
Thu Sep 25 09:27:26 2014 MMON started with pid=15, OS id=1968 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... starting up 1 shared server(s) ... ORACLE_BASE from environment = F:\oracle Thu Sep 25 09:27:26 2014 ALTER DATABASE MOUNT Thu Sep 25 09:27:26 2014 MMNL started with pid=16, OS id=5976 Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_4624.trc: ORA-00202: ????: ''F:\ORACLE\ORADATA\ORCL\CONTROL01.CTL ORA-27070: ????/???? OSD-04006: ReadFile() 失败, 无法读取文件 O/S-Error: (OS 23) 数据错误(循环冗余检查)。 Thu Sep 25 09:28:31 2014 ORA-204 signalled during: ALTER DATABASE MOUNT...
因为硬件或者系统层面问题,导致控制文件无法正常访问
重建控制文件
Fri Sep 26 12:28:44 2014 Successful mount of redo thread 1, with mount id 1387065723 Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS ARCHIVELOG MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 2 MAXLOGHISTORY 226 LOGFILE GROUP 1 'F:\oracle\oradata\orcl\REDO01.LOG' SIZE 50M, --redo log ???? GROUP 2 'F:\oracle\oradata\orcl\REDO02.LOG' SIZE 50M, --redo log ???? GROUP 3 'F:\oracle\oradata\orcl\REDO03.LOG' SIZE 50M --redo log ???? -- STANDBY LOGFILE DATAFILE 'F:\oracle\oradata\orcl\SYSAUX01.DBF', --sysaux??????? 'F:\oracle\oradata\orcl\SYSTEM01.DBF', 'F:\oracle\oradata\orcl\USERS01.DBF', --user???????? 'F:\oracle\oradata\orcl\UNDOTBS01.DBF' --undo??????? CHARACTER SET ZHS16GBK Fri Sep 26 12:29:55 2014 alter database open resetlogs ORA-1194 signalled during: alter database open resetlogs...
埋下了雷,创建控制文件中未全部列举出来所有数据文件
进行不完全恢复,尝试resetlogs库发现redo异常
Fri Sep 26 14:13:24 2014 ALTER DATABASE MOUNT Fri Sep 26 14:13:24 2014 MMNL started with pid=16, OS id=9024 Successful mount of redo thread 1, with mount id 1387037444 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Fri Sep 26 14:14:08 2014 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. Fri Sep 26 14:15:16 2014 Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc: ORA-00333: 重做日志读取块 2049 计数 6143 出错 ORA-00312: 联机日志 1 线程 1: 'F:\ORACLE\ORADATA\ORCL\REDO01.LOG' ORA-27070: 异步读取/写入失败 OSD-04016: 异步 I/O 请求排队时出错。 O/S-Error: (OS 23) 数据错误(循环冗余检查)。 Fri Sep 26 14:16:24 2014 Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc: ORA-00333: 重做日志读取块 1 计数 8191 出错 ORA-00312: 联机日志 1 线程 1: 'F:\ORACLE\ORADATA\ORCL\REDO01.LOG' ORA-27070: 异步读取/写入失败 OSD-04006: ReadFile() 失败, 无法读取文件 O/S-Error: (OS 23) 数据错误(循环冗余检查)。 Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc: ORA-00333: 重做日志读取块 1 计数 8191 出错 ARCH: All Archive destinations made inactive due to error 333
使用隐含参数尝试拉库,报ORA-600[2662]
Fri Sep 26 14:16:45 2014 SMON: enabling cache recovery Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc (incident=57761): ORA-00600: 内部错误代码, 参数: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], [] Incident details in: f:\oracle\diag\rdbms\orcl\orcl\incident\incdir_57761\orcl_ora_3720_i57761.trc Fri Sep 26 14:16:45 2014 ARC3 started with pid=23, OS id=9692 ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00600: 内部错误代码, 参数: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], [] Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00600: 内部错误代码, 参数: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], [] Error 704 happened during db open, shutting down database USER (ospid: 3720): terminating the instance due to error 704 Instance terminated by USER, pid = 3720 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (3720) as a result of ORA-1092
数据库在未使用所有数据文件的情况下,进行了resetlogs操作,悲剧的本质已经注定,我的失误是没有评估好现状,还继续在错误的道路上越走越远.
我开始接手该库现况
Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Fri Sep 26 14:18:55 2014 alter database open Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_8968.trc: ORA-01113: 文件 1 需要介质恢复 ORA-01110: 数据文件 1: 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open... Fri Sep 26 14:19:31 2014 alter database open Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_8968.trc: ORA-01113: 文件 1 需要介质恢复 ORA-01110: 数据文件 1: 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open ... Fri Sep 26 14:22:26 2014 ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Fri Sep 26 14:22:26 2014 Media Recovery failed with error 16433 Recovery Slave PR00 previously exited with exception 283 ORA-283 signalled during: ALTER DATABASE RECOVER database ... Fri Sep 26 14:24:25 2014 ALTER DATABASE RECOVER datafile 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' Media Recovery Start Media Recovery failed with error 16433 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' ... Fri Sep 26 14:28:47 2014 alter database open read write Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_8968.trc: ORA-01113: 文件 1 需要介质恢复 ORA-01110: 数据文件 1: 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open read write... Fri Sep 26 14:31:48 2014 ALTER DATABASE RECOVER datafile 'F:\oracle\oradata\orcl\SYSTEM01.DBF' Media Recovery Start Media Recovery failed with error 16433 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 'F:\oracle\oradata\orcl\SYSTEM01.DBF' ...
提示ORA-01110: 数据文件 1需要恢复,尝试recover操作
尝试recover操作
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover database ; ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode. SQL> alter database backup controlfile to trace as 'd:\ctl.txt'; alter database backup controlfile to trace as 'd:\ctl.txt' * 第 1 行出现错误: ORA-16433: 必须以读/写模式打开数据库。 SQL> recover database using backup controlfile; ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode.
重建控制文件
SQL> shutdown immediate; ORA-01109: 数据库未打开 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> STARTUP NOMOUNT ORACLE 例程已经启动。 Total System Global Area 970895360 bytes Fixed Size 1375452 bytes Variable Size 603980580 bytes Database Buffers 360710144 bytes Redo Buffers 4829184 bytes SQL> CREATE CONTROLFILE REUSE DATABASE orcl NORESETLOGS FORCE LOGGING ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 2921 7 LOGFILE 8 GROUP 1 'F:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 50M, 9 GROUP 2 'F:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 50M, 10 GROUP 3 'F:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 50M 11 DATAFILE 12 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF', 13 'F:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF', 14 'F:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF', 15 'F:\ORACLE\ORADATA\ORCL\USERS01.DBF' 16 CHARACTER SET ZHS16GBK 17 ; 控制文件已创建。
这一步严重发错,在恢复前未认真看alert日志,太依赖v$datafile查询出来结果,导致重建控制文件丢失数据文件,埋下大雷。根据前面alert日志报错ORA-600 2662,决定一并处理该问题,然后进行恢复
SQL> shutdown immediate; ORA-01109: ?????? 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup pfile='d:\pfile.txt' mount; ORACLE 例程已经启动。 Total System Global Area 970895360 bytes Fixed Size 1375452 bytes Variable Size 603980580 bytes Database Buffers 360710144 bytes Redo Buffers 4829184 bytes 数据库装载完毕。 SQL> recover database; 完成介质恢复。 SQL> alter database open; alter database open * 第 1 行出现错误: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [4194], [], [
数据库报ORA-600 4194,直接修改undo_management=manual,然后尝试启动数据库
SQL> conn / as sysdba 已连接到空闲例程。 SQL> startup pfile='d:\pfile.txt' ORACLE 例程已经启动。 Total System Global Area 970895360 bytes Fixed Size 1375452 bytes Variable Size 603980580 bytes Database Buffers 360710144 bytes Redo Buffers 4829184 bytes 数据库装载完毕。 数据库已经打开。 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF F:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF F:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF F:\ORACLE\ORADATA\ORCL\USERS01.DBF F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005 F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00006 已选择6行。 SQL> alter database rename file 'F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005' 2 to 'F:\oracle\oradata\SOURCE_DATA1.DBF'; 数据库已更改。 SQL> alter database rename file 'F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00006' 2 to 'F:\oracle\oradata\SOURCE_idx1.DBF'; 数据库已更改。 SQL> shutdown immediate; 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup mount pfile='d:\pfile.txt' ORACLE 例程已经启动。 Total System Global Area 970895360 bytes Fixed Size 1375452 bytes Variable Size 603980580 bytes Database Buffers 360710144 bytes Redo Buffers 4829184 bytes 数据库装载完毕。 SQL> alter datafile 5 online; alter datafile 5 online * 第 1 行出现错误: ORA-00940: 无效的 ALTER 命令 SQL> alter database datafile 5 online; 数据库已更改。 SQL> alter database datafile 6 online; 数据库已更改。 SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 5 belongs to an orphan incarnation ORA-01110: data file 5: 'F:\ORACLE\ORADATA\SOURCE_DATA1.DBF' SQL> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效 SQL> alter database datafile 6 offline; 数据库已更改。 SQL> alter database datafile 5 offline; 数据库已更改。 SQL> recover database until cancel; 完成介质恢复。 SQL> alter database datafile 6 online; 数据库已更改。 SQL> alter database datafile 5 online; 数据库已更改。 SQL> alter database open resetlogs; 数据库已更改。
还好结合一些隐含参数侥幸恢复成功,差点到了要使用bbed的程度,如果遇到极端情况无法处理可以参考:Oracle Recovery Tools恢复MISSING00000文件故障
这次的恢复告诉我:Oracle数据库恢复千万比大意,需要认真分析alert日志和咨询客户做了那些操作,不然可能导致万劫不复之禁地