标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 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)
- 操作系统 (103)
- 数据库 (1,767)
- DB2 (22)
- MySQL (77)
- Oracle (1,608)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (166)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (69)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (590)
- Oracle安装升级 (97)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (86)
- PostgreSQL (30)
- pdu工具 (6)
- PostgreSQL恢复 (9)
- SQL Server (32)
- SQL Server恢复 (13)
- TimesTen (7)
- 达梦数据库 (3)
- 达梦恢复 (1)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (39)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (22)
-
最近发表
- ORA-00756 ORA-10567故障数据0丢失恢复
- 数据库文件变成32k故障恢复
- tcp连接过多导致监听TNS-12532 TNS-12560 TNS-00502错误
- 文件系统格式化MySQL数据库恢复
- .sstop勒索加密数据库恢复
- 解决一次硬件恢复之后数据文件0kb的故障恢复case
- Error in invoking target ‘libasmclntsh19.ohso libasmperl19.ohso client_sharedlib’问题处理
- ORA-01171: datafile N going offline due to error advancing checkpoint
- linux环境oracle数据库被文件系统勒索加密为.babyk扩展名溯源
- ORA-600 ksvworkmsgalloc: bad reaper
- ORA-600 krccfl_chunk故障处理
- Oracle Recovery Tools恢复案例总结—202505
- ORA-600 kddummy_blkchk 数据库循环重启
- 记录一次asm disk加入到vg通过恢复直接open库的案例
- CHECKDB 发现了 N 个分配错误和 M 个一致性错误
- 达梦数据库dm.ctl文件异常恢复
- Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障
- 可能是 tempdb 空间用尽或某个系统表不一致故障处理
- 11.2.0.4库中遇到ORA-600 kcratr_nab_less_than_odr报错
- [MY-013183] [InnoDB] Assertion failure故障处理
_allow_resetlogs_corruption 的搜索结果
不当使用_allow_resetlogs_corruption参数引起ORA-600 2662错误
有一个数据库由于机房掉电,导致数据库无法启动,由于不当恢复导致open过程报ORA-600 2662等错误客户无法自行解决,让我们协助.幸运的是客户对现场做了备份,我接手恢复之后,让客户还原现场,然后进行恢复,比较顺利的直接open数据库,实现数据0丢失原库直接可用,避免了一次因为不当操作而引起少量数据丢失的风险,和业务的快速恢复(避免的因为强制拉库引起的不一致性问题而要做数据库逻辑迁移).通过日志回顾第一次现场恢复经历
最初故障数据库mount报错
SQL> startup mount pfile='d:/pfile.txt' ORACLE 例程已经启动。 Total System Global Area 1071333376 bytes Fixed Size 1375792 bytes Variable Size 754975184 bytes Database Buffers 310378496 bytes Redo Buffers 4603904 bytes ORA-03113: 通信通道的文件结尾 进程 ID: 964 会话 ID: 1145 序列号: 1
无法mount,大部分情况是由于控制文件损坏,然后客户选择了重建controlfile
Thu Feb 20 10:20:45 2025 Successful mount of redo thread 1, with mount id 1721384698 Completed: CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 254 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 'D:\app\Administrator\oradata\orcl\redo01.log' size 50M, GROUP 2 'D:\app\Administrator\oradata\orcl\redo02.log' size 50M, GROUP 3 'D:\app\Administrator\oradata\orcl\redo03.log' size 50M DATAFILE 'D:\app\Administrator\oradata\orcl\XFF.DBF', 'D:\app\Administrator\oradata\orcl\XIFENFEI.DBF', 'D:\app\Administrator\oradata\orcl\SYSAUX01.DBF', 'D:\app\Administrator\oradata\orcl\SYSTEM01.DBF', 'D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF', 'D:\app\Administrator\oradata\orcl\USERS01.DBF' CHARACTER SET ZHS16GBK
重建ctl使用了resetlogs模式,然后下一步客户进行恢复使用命令为:ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
Thu Feb 20 10:22:05 2025 ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE Media Recovery Start started logmerger process Thu Feb 20 10:22:05 2025 WARNING! Recovering data file 1 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 3 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 4 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 5 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 6 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command. Parallel Media Recovery started with 4 slaves ORA-279 signalled during: ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_02_20\O1_MF_1_9087_%U_.ARC Errors with log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_02_20\O1_MF_1_9087_%U_.ARC Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_pr00_3044.trc: ORA-00308: cannot open archived log 'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_02_20\O1_MF_1_9087_%U_.ARC' ORA-27041: unable to open file OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_02_20\O1_MF_1_9087_%U_.ARC Errors with log D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_02_20\O1_MF_1_9087_%U_.ARC Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_pr00_3044.trc: ORA-00308: cannot open archived log 'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_02_20\O1_MF_1_9087_%U_.ARC' ORA-27041: unable to open file OSD-04002: 无法打开文件 O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CANCEL Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_pr00_3044.trc: 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: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' ORA-10879 signalled during: ALTER DATABASE RECOVER CANCEL ...
直接提示需要找归档日志seq为9087的,但是由于该库为非归档模式,客户直接输入了auto,无法找到对应的日志.然后尝试直接resetlogs打开库
Thu Feb 20 10:22:58 2025 ALTER DATABASE OPEN RESETLOGS ORA-1194 signalled during: ALTER DATABASE OPEN RESETLOGS... ALTER DATABASE OPEN Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4836.trc: ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ?? ORA-1589 signalled during: ALTER DATABASE OPEN... ALTER DATABASE OPEN RESETLOGS ORA-1194 signalled during: ALTER DATABASE OPEN RESETLOGS...
由于数据文件不一致(前面recover没有成功),导致直接reseltogs方式打开库失败,然后设置一些参数
Thu Feb 20 10:27:24 2025 ALTER SYSTEM SET _allow_error_simulation=TRUE SCOPE=SPFILE; ALTER SYSTEM SET _allow_terminal_recovery_corruption=TRUE SCOPE=SPFILE; Thu Feb 20 10:27:38 2025 ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE SCOPE=SPFILE;
重启库之后强制拉库
Completed: alter database mount Thu Feb 20 10:29:08 2025 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. Thu Feb 20 10:29:19 2025 Archived Log entry 1 added for thread 1 sequence 9088 ID 0x5d904d0a dest 1: Archived Log entry 2 added for thread 1 sequence 9086 ID 0x5d904d0a dest 1: Thu Feb 20 10:29:31 2025 Archived Log entry 3 added for thread 1 sequence 9087 ID 0x5d904d0a dest 1: RESETLOGS after incomplete recovery UNTIL CHANGE 223770120 Thu Feb 20 10:29:38 2025 Setting recovery target incarnation to 2 Thu Feb 20 10:29:39 2025 Assigning activation ID 1721340650 (0x669992ea) LGWR: STARTING ARCH PROCESSES Thu Feb 20 10:29:39 2025 ARC0 started with pid=20, OS id=2924 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Thu Feb 20 10:29:40 2025 ARC1 started with pid=21, OS id=1832 Thu Feb 20 10:29:40 2025 ARC2 started with pid=22, OS id=3668 ARC1: Archival started Thu Feb 20 10:29:40 2025 ARC3 started with pid=23, OS id=5104 ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH 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 Thu Feb 20 10:29:41 2025 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Thu Feb 20 10:29:41 2025 SMON: enabling cache recovery ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1652.trc (incident=1006385): ORA-00600: ??????, ??: [2662], [0], [223770128], [0], [223811777], [12583040], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1006385\orcl_ora_1652_i1006385.trc Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1652.trc: ORA-00600: ??????, ??: [2662], [0], [223770128], [0], [223811777], [12583040], [], [], [], [], [], [] Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1652.trc: ORA-00600: ??????, ??: [2662], [0], [223770128], [0], [223811777], [12583040], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 1652): terminating the instance due to error 600 Instance terminated by USER, pid = 1652
数据库报ORA-600 2662错误,导致强制拉库失败.
后面客户进行了一系列折腾,导致出现其他错误,比如:
ORA-01595/ORA-600 4194
Fri Feb 21 04:52:19 2025 Trace dumping is performing id=[cdmp_20250221045219] Doing block recovery for file 3 block 472 Resuming block recovery (PMON) for file 3 block 472 Block recovery from logseq 2, block 51 to scn 223837038 Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG Block recovery stopped at EOT rba 2.54.16 Block recovery completed at rba 2.54.16, scn 0.223837035 Doing block recovery for file 3 block 144 Resuming block recovery (PMON) for file 3 block 144 Block recovery from logseq 2, block 51 to scn 223837033 Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG Block recovery completed at rba 2.52.16, scn 0.223837034 Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_2124.trc: ORA-01595: error freeing extent (3) of rollback segment (2)) ORA-00600: internal error code, arguments: [4194], [], [
ORA-600 2256/ORA-600 4194
Fri Feb 21 05:02:43 2025 SMON: enabling cache recovery Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2184.trc (incident=1134285): ORA-00600: 内部错误代码, 参数: [2256], [0], [1073741824], [0], [1073761870], [], [], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1134285\orcl_ora_2184_i1134285.trc 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 ********************************************************************* WARNING: The following temporary tablespaces contain no files. 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 ZHS16GBK No Resource Manager plan active Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smon_648.trc (incident=1134237): ORA-00600: internal error code, arguments: [4194], [], [ Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1134237\orcl_smon_648_i1134237.trc Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2184.trc (incident=1134286): ORA-00600: 内部错误代码, 参数: [4194], [0], [ Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1134286\orcl_ora_2184_i1134286.trc
ORA-600 3712
Fri Feb 21 05:24:51 2025 Assigning activation ID 1721440698 (0x669b19ba) Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_lgwr_3620.trc (incident=1150206): ORA-00600: internal error code, arguments: [3712], [1], [1], [3584], [3], [3584], [1], [], [], [], [], [] Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_1150206\orcl_lgwr_3620_i1150206.trc Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_lgwr_3620.trc: ORA-00600: internal error code, arguments: [3712], [1], [1], [3584], [3], [3584], [1], [], [], [], [], [] LGWR (ospid: 3620): terminating the instance due to error 470
基于客户本身库不大,而且在操作之前备份了现场,然后客户还原故障现场备份,我开始接手恢复
重建控制文件
Fri Feb 21 21:57:19 2025 Successful mount of redo thread 1, with mount id 1721495486 Completed: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 5000 MAXINSTANCES 8 MAXLOGHISTORY 2920 LOGFILE group 1 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG' size 50M, group 3 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG' size 50M, group 2 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG' size 50M DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF', 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF', 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF', 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF', 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\XIFENFEI.DBF', 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\XFF.DBF' CHARACTER SET ZHS16GBK
这里重建控制文件使用的是noresetlogs模式,然后尝试recover database
ALTER DATABASE RECOVER database Media Recovery Start started logmerger process Parallel Media Recovery started with 4 slaves Fri Feb 21 21:57:25 2025 Recovery of Online Redo Log: Thread 1 Group 3 Seq 9087 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG Recovery of Online Redo Log: Thread 1 Group 1 Seq 9088 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG Fri Feb 21 21:57:30 2025 Completed: ALTER DATABASE RECOVER database
数据库直接recover成功,然后尝试正常open库
Fri Feb 21 21:58:17 2025 alter database open Beginning crash recovery of 1 threads parallel recovery started with 3 processes Started redo scan Completed redo scan read 12019 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 9088, block 2, scn 223805802 Recovery of Online Redo Log: Thread 1 Group 1 Seq 9088 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 9088, block 24040, scn 223836931 0 data blocks read, 0 data blocks written, 12019 redo k-bytes read Fri Feb 21 21:58:17 2025 Thread 1 advanced to log sequence 9089 (thread open) Thread 1 opened at log sequence 9089 Current log# 2 seq# 9089 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri Feb 21 21:58:17 2025 SMON: enabling cache recovery Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery ********************************************************************* WARNING: The following temporary tablespaces contain no files. 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 ZHS16GBK No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Fri Feb 21 21:58:20 2025 QMNC started with pid=22, OS id=524 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open
数据库完美打开,然后增加tempfile,检查/导出数据均没有任何问题,业务可以直接使用本库,不用逻辑迁移
其实这是一个比较小的故障,由于断电导致控制文件损坏,然后客户重建控制文件使用resetlogs方式,然后recover没有正确指定redo,来完成数据库实例恢复,直接使用_allow_resetlogs_corruption参数强制拉库,然后出现了ORA-600 2662/ORA-600 4194/ORA-600 2256等大家熟悉的错误.这个库不大,也做了故障现场保护,如果是一个10T以上大库,发生这样的故障,后果还是比较麻烦的(库可以打开,但是redo中的数据肯定丢失导致数据库不一致,后续可能有很多不一致性问题,可能涉及逻辑迁移),增加企业业务不可用时间成本和业务数据丢失风险,再次提醒对于Oracle隐含参数,还是需要慎重,做好专业的评估再使用.
_ALLOW_RESETLOGS_CORRUPTION
我相信_ALLOW_RESETLOGS_CORRUPTION 这个参数一定很多人都熟悉,是redo异常恢复的杀手锏之一,以下文章是来自官方的解释
DB_Parameter _ALLOW_RESETLOGS_CORRUPTION ======================================== This documentation has been prepared avoiding the mention of the complex structures from the code and to simply give an insight to the 'damage it could cause'. The usage of this parameter leads to an in-consistent Database with no other alternative but to rebuild the complete Database. This parameter could be used when we realize that there are no stardard options available and are convinced that the customer understands the implications of using the Oracle's secret parameter. The factors to be considered are ;-- 1. Customer does not have a good backup. 2. A lot of time and money has been invested after the last good backup and there is no possibility for reproduction of the lost data. 3. The customer has to be ready to export the full database and import it back after creating a new one. 4. There is no 100% guarantee that by using this parameter the database would come up. 5. Oracle does not support the database after using this parameter for recovery. 6. ALL OPTIONS including the ones mentioned in the action part of the error message have been tried. By setting _ALLOW_RESETLOGS_CORRUPTION=TRUE, certain consistency checks are SKIPPED during database open stage. This basically means it does not check the datafile headers as to what the status was before the shutdown and how it was shutdown. The following cases mention few of the checks that were skipped. Case-I ------ Verification that the datafile present has not been restored from a BACKUP taken before the database was opened successfully by using RESETLOGS. ORA-01190: control file or data file %s is from before the last RESETLOGS Cause: Attempting to use a data file when the log reset information in the file does not match the control file. Either the data file or the control file is a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS. Action: Restore file from a more recent backup. Case-II ------- Verification that the status bit of the datafile is not in a FUZZY state. The datafile could be in this state due to the database going down when the - Datafile was on-line and open - Datafile was not closed cleanly (maybe due to OS). ORA-01194: file %s needs more recovery to be consistent Cause: An incomplete recover session was started, but an insufficient number of logs were applied to make the file consistent. The reported file was not closed cleanly when it was last opened by the database. It must be recovered to a time when it was not being updated. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery. Action: Either apply more logs until the file is consistent or restore the file from an older backup and repeat recovery. Case-III -------- Verification that the COMPLETE recover strategies have been applied for recovering the datafile and not any of the INCOMPLETE recovery options. Basically because the complete recovery is one in which we even apply the ON-LINE redo log files and open the DB without reseting the logs. ORA-01113: file '%s' needs media recovery starting at log sequence # %s Cause: An attempt was made to open a database file that is in need of media recovery. Action: First apply media recovery to the file. Case-IV ------- Verification that the datafile has been recovered through an END BACKUP if the control file indicates that it was in backup mode. This is useful when the DB has crashed while in hot backup mode and we lost all log files in DB version's less than V7.2. ORA-01195: on-line backup of file %s needs more recovery to be consistent" Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the file consistent. The reported file is an on-line backup which must be recovered to the time the backup ended. Action: Either apply more logs until the file is consistent or resotre the database files from an older backup and repeat recovery. In version 7.2, we could simply issue the ALTER DATABASE DATAFILE xxxx END BACKUP statement and proceed with the recovery. But again to issue this statement, we need to have the ON-LINE redo logs or else we still are forced to use this parameter. Case-V ------ Verification that the data file status is not still in (0x10) MEDIA recovery FUZZY. When recovery is started, a flag is set in the datafile header status flag to indicate that the file is presently in media recovery. This is reset when recovery is completed and at times when it has not been reset we are forced to use this paramter. ORA-01196: file %s is inconsistent due to a failed media recovery session Cause: The file was being recovered but the recovery did not terminate normally. This left the file in an inconsistent state. No more recovery was successfully completed on this file. Action: Either apply more logs until the file is consistent or restore the backup again and repeat recovery. Case-VI ------- Verification that the datafile has been restored form a proper backup to correspond with the log files. This situation could happen when we have decided that the data file is invalid since its SCN is ahead of the last applied logs SCN but it has not failed on one of the ABOVE CHECKS. ORA-01152: file '%s' was not restored from a sufficientluy old backup" Cause: A manual recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. Note that this mistake can not always be caught. Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery.
使用_ALLOW_RESETLOGS_CORRUPTION 参数需谨慎,因为该参数可能导致数据库逻辑不一致,甚至可能把本来很简单的一个恢复弄的非常复杂甚至不可恢复的后果,建议在oracle support支持下使用.另外使用该参数resetlogs库之后,强烈建议通过逻辑方式重建库
发表在 Oracle备份恢复
标签为 ORA-01113, ORA-01152, ORA-01190, ORA-01194, ORA-01195, ORA-01196, _ALLOW_RESETLOGS_CORRUPTION
评论关闭
使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
以前写过一篇乱用_allow_resetlogs_corruption参数导致悲剧的文章,昨天晚上又遇到一个朋友不谨慎使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
环境描述
系统环境:solaris
数据库版本:10.2.0.5.7
数据存储方式:ASM
数据量:15T以上
补充事宜:数据库SCN距离headroom只有54天
报ORA-00020错误,实例crash
数据库因为超过了系统的进程数,出现dbwn进程写数据文件异常
Sun Aug 25 16:00:41 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_dbw0_7490.trc: ORA-01148: 无法刷新数据文件 22 的文件大小 ORA-01110: 数据文件 22: '+DATA/orcl/datafile/index_jh.dbf' ORA-00020: 超出最大进程数 () Sun Aug 25 16:00:41 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_dbw0_7490.trc: ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式 ORA-01110: 数据文件 22: '+DATA/orcl/datafile/index_jh.dbf' Sun Aug 25 16:00:41 CST 2013 DBW0: terminating instance due to error 1242 Termination issued to instance processes. Waiting for the processes to exit Sun Aug 25 16:00:51 CST 2013 Instance termination failed to kill one or more processes Instance terminated by DBW0, pid = 7490
ORA-00600[kcbtema_10]
实例恢复出现ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], []
Sun Aug 25 19:19:23 CST 2013 ALTER DATABASE OPEN Sun Aug 25 19:19:38 CST 2013 Beginning crash recovery of 1 threads parallel recovery started with 16 processes Sun Aug 25 19:19:40 CST 2013 Started redo scan Sun Aug 25 19:20:07 CST 2013 Completed redo scan 12016413 redo blocks read, 93405 data blocks need recovery Sun Aug 25 19:20:19 CST 2013 Started redo application at Thread 1: logseq 53681, block 1091966 Sun Aug 25 19:20:19 CST 2013 Recovery of Online Redo Log: Thread 1 Group 1 Seq 53681 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/redo_1_1.log Mem# 1: +DATA/orcl/onlinelog/redo_1_2.log Sun Aug 25 19:20:21 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_p011_16944.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] Sun Aug 25 19:20:23 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_p011_16944.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] Sun Aug 25 19:20:23 CST 2013 Aborting crash recovery due to slave death, attempting serial crash recovery Sun Aug 25 19:20:23 CST 2013 Beginning crash recovery of 1 threads Sun Aug 25 19:20:23 CST 2013 Started redo scan Sun Aug 25 19:20:47 CST 2013 Completed redo scan 12016413 redo blocks read, 93405 data blocks need recovery Sun Aug 25 19:20:54 CST 2013 Started redo application at Thread 1: logseq 53681, block 1091966 Sun Aug 25 19:20:54 CST 2013 Recovery of Online Redo Log: Thread 1 Group 1 Seq 53681 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/redo_1_1.log Mem# 1: +DATA/orcl/onlinelog/redo_1_2.log Sun Aug 25 19:20:54 CST 2013 Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_16751.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] Sun Aug 25 19:20:56 CST 2013 Aborting crash recovery due to error 600 Sun Aug 25 19:20:56 CST 2013 Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_16751.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] ORA-600 signalled during: ALTER DATABASE OPEN...
使用隐含参数
ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE SCOPE=SPFILE;
报ORA-00704/ORA-01555
因为在前面的恢复中进行了不完全恢复,因此这里加入隐含参数,然后尝试resetlogs,然后报如下错误
Sun Aug 25 20:11:54 CST 2013 alter database open resetlogs Sun Aug 25 20:12:10 CST 2013 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 13429649847189 Resetting resetlogs activation ID 1312390734 (0x4e397e4e) Sun Aug 25 20:16:25 CST 2013 Setting recovery target incarnation to 2 Sun Aug 25 20:16:42 CST 2013 ************************************************************ Warning: The SCN headroom for this database is only 54 days! ************************************************************ Sun Aug 25 20:16:43 CST 2013 Assigning activation ID 1352200163 (0x5098efe3) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: +DATA/orcl/onlinelog/redo_1_1.log Current log# 1 seq# 1 mem# 1: +DATA/orcl/onlinelog/redo_1_2.log Successful open of redo thread 1 Sun Aug 25 20:16:43 CST 2013 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun Aug 25 20:16:52 CST 2013 SMON: enabling cache recovery Sun Aug 25 20:16:52 CST 2013 ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0c36.d582339b): Sun Aug 25 20:16:52 CST 2013 select ctime, mtime, stime from obj$ where obj# = :1 Sun Aug 25 20:16:52 CST 2013 Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_2859.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 143 (名称为 "_SYSSMU143$") 过小 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Termination issued to instance processes. Waiting for the processes to exit Sun Aug 25 20:17:02 CST 2013 Instance termination failed to kill one or more processes Instance terminated by USER, pid = 2859 ORA-1092 signalled during: alter database open resetlogs...
数据库当前SCN
SQL > select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 13429649947222 SQL > select distinct CHECKPOINT_CHANGE# from v$datafile_header; CHECKPOINT_CHANGE# ------------------ 13429649947222
解决方法
因为该数据库版本为10.2.0.5.7,已经包含了scn patch,因此不能使用event或者隐含参数来修改scn,而且该库容量15T以上(asm),因此也无法使用bbed修改数据文件头,最后决定使用ordebug来解决该问题
使用oradebug DUMPvar SGA kcsgscn_
使用oradebug poke
sqlplus / as sysdba startup mount oradebug setmypid oradebug DUMPvar SGA kcsgscn_ oradebug poke recover database; alter database open;
事后总结
查询MOS,发现ORA-00600[kcbtema_10] Raised During Recovery Operations (Doc ID 472282.1)
--故障原因 The cause of this problem has been identified and verified in unpublished Bug 5184359 ORA-600 [KCBTEMA_10]. Due to this bug, during recovery, the class designation of a data block has changed. --处理方法 SQL>startup mount SQL>recover database; SQL>alter database open;
因为MOS上给的解决思路在该数据库中已经无法尝试,不能确定该方法一定可行,但是对于本次的恢复过程中,没有任何直接recover database操作(只有一次不完全恢复)确实让人有无限的遗憾和可惜。对于本次应该先查询MOS,尝试该种方法,慎重使用_allow_resetlogs_corruption参数