标签云
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,674)
- DB2 (22)
- MySQL (73)
- Oracle (1,536)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (22)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (562)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- 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)
-
最近发表
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-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默认服务配置优先节点
标签归档:ORA-01194
_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
评论关闭
ORACLE REDO各种异常恢复
redo是oracle数据库比较核心文件,当redo异常之后,数据库无法正常启动,而且有丢失数据的风险,强烈建议条件允许redo多路镜像.redo文件异常的故障可以说是千奇百怪,但是总体上可以分为几类:
数据库归档/非归档模式下inactive redo异常
ORA-00316 ORA-00327
SQL> startup mount ORACLE instance started. Total System Global Area 260046848 bytes Fixed Size 1266896 bytes Variable Size 83888944 bytes Database Buffers 167772160 bytes Redo Buffers 7118848 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-00316: log 2 of thread 1, type in header is not log file ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log' SQL> col member for a40 SQL> set lines 120 SQL> SELECT thread#, 2 a.sequence#, 3 a.group#, 4 TO_CHAR (first_change#, '9999999999999999') "SCN", 5 a.status, 6 MEMBER 7 FROM v$log a, v$logfile b 8 WHERE a.group# = B.GROUP# 9 ORDER BY a.sequence# DESC; THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER ---------- ---------- ---------- ----------------- ---------------- ----------------------------------- 1 15 3 665697 CURRENT /u01/oracle/oradata/XFF/redo03.log 1 14 2 645619 INACTIVE /u01/oracle/oradata/XFF/redo02.log 1 13 1 625540 INACTIVE /u01/oracle/oradata/XFF/redo01.log SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-00327: log 2 of thread 1, physical size less than needed ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log' SQL> alter database drop logfile group 2; Database altered. SQL> alter database open; Database altered. SQL> alter database add logfile group 2 ('/u01/oracle/oradata/XFF/redo02.log') size 50M reuse; Database altered.
正常关闭数据库current redo异常
ORA-00316 ORA-01623
SQL> alter database open; alter database open * ERROR at line 1: ORA-00316: log 1 of thread 1, type in header is not log file ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log' SQL> SELECT thread#, 2 a.sequence#, 3 a.group#, 4 TO_CHAR (first_change#, '9999999999999999') "SCN", 5 a.status, 6 MEMBER 7 FROM v$log a, v$logfile b 8 WHERE a.group# = B.GROUP# 9 ORDER BY a.sequence# DESC; THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER ---------- ---------- ---------- ----------------- ---------------- ---------------------------------- 1 16 1 685918 CURRENT /u01/oracle/oradata/XFF/redo01.log 1 15 3 665697 INACTIVE /u01/oracle/oradata/XFF/redo03.log 1 0 2 0 UNUSED /u01/oracle/oradata/XFF/redo02.log SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; ALTER DATABASE CLEAR LOGFILE GROUP 1 * ERROR at line 1: ORA-00316: log 1 of thread 1, type 0 in header is not log file ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log' SQL> ALTER DATABASE drop logfile group 1; ALTER DATABASE drop logfile group 1 * ERROR at line 1: ORA-01623: log 1 is current log for instance XFF (thread 1) - cannot drop ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log' SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered.
数据库异常关闭current/active redo异常
ORA-00316 ORA-01624 ORA-01194
SQL> alter database open; alter database open * ERROR at line 1: ORA-00316: log 1 of thread 1, type 0 in header is not log file ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log' SQL> SELECT thread#, 2 a.sequence#, 3 a.group#, 4 TO_CHAR (first_change#, '9999999999999999') "SCN", 5 a.status, 6 MEMBER 7 FROM v$log a, v$logfile b 8 WHERE a.group# = B.GROUP# 9 ORDER BY a.sequence# DESC; THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER ---------- ---------- ---------- ----------------- ---------------- ----------------------------------- 1 8 2 686310 CURRENT /u01/oracle/oradata/XFF/redo02.log 1 7 1 686294 ACTIVE /u01/oracle/oradata/XFF/redo01.log 1 6 3 686289 INACTIVE /u01/oracle/oradata/XFF/redo03.log SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; ALTER DATABASE CLEAR LOGFILE GROUP 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance XFF (thread 1) ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log' SQL> ALTER DATABASE drop logfile group 1; ALTER DATABASE drop logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance XFF (thread 1) ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log' SQL> recover database until cancel ORA-00279: change 686294 generated at 04/20/2013 01:37:16 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oracle/product/10.2.0/db_1/dbs/arch1_7_813202529.dbf ORA-00280: change 686294 for thread 1 is in sequence #7 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oracle/oradata/XFF/redo01.log ORA-00308: cannot open archived log '/u01/oracle/oradata/XFF/redo01.log' ORA-27047: unable to read the header block of file Additional information: 2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel 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: '/u01/oracle/oradata/XFF/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oracle/oradata/XFF/system01.dbf' SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 260046848 bytes Fixed Size 1266896 bytes Variable Size 83888944 bytes Database Buffers 167772160 bytes Redo Buffers 7118848 bytes Database mounted. SQL> recover database until cancel ORA-00279: change 686294 generated at 04/20/2013 01:37:16 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oracle/product/10.2.0/db_1/dbs/arch1_7_813202529.dbf ORA-00280: change 686294 for thread 1 is in sequence #7 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel 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: '/u01/oracle/oradata/XFF/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; Database altered.
在这样的情况下,数据库异常关闭,current/active redo异常,通过使用隐含参数可能可以侥幸的恢复数据库,但是也可能导致数据丢失.这里因为是模拟情况,无业务所以在很多较为繁忙的业务系统中,使用隐含参数resetlogs过程中可能还会遇到如下很多常见的错误,进一步增加了恢复难度
current/active redo异常后附带其他错误
ORA-600[2662]
Wed Dec 07 13:02:49 2011 SMON: enabling cache recovery Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc (incident=216664): ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216664\hzyl_ora_3388_i216664.trc Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc: ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], [] Error 600 happened during db open, shutting down database USER (ospid: 3388): terminating the instance due to error 600
ORA-00600[4000]
Thu Feb 28 19:29:10 2013 SMON: enabling cache recovery Thu Feb 28 19:29:11 2013 Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc: ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], [] Thu Feb 28 19:29:13 2013 Incremental checkpoint up to RBA [0x1.3.0], current log tail at RBA [0x1.3.0] Thu Feb 28 19:29:13 2013 Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], []
ORA-00704 ORA-00604 ORA-01555
Fri May 4 21:04:21 2012 select ctime, mtime, stime from obj$ where obj# = :1 Fri May 4 21:04:21 2012 Errors in file /oracle/admin/standdb/udump/perfdb_ora_1286288.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 40 with name "_SYSSMU40$" too small Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 1286288 ORA-1092 signalled during: alter database open resetlogs...
current/active redo异常还可能报如下错误
redo文件损坏报错
Started redo scan Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc (incident=214262): ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11 ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG' Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214262\hzyl_ora_2960_i214262.trc Aborting crash recovery due to error 368 Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc: ORA-00368: 重做日志块中的校验和错误 ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11 ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG' ORA-368 signalled during: ALTER DATABASE OPEN...
redo文件被其他实例占用报错
Wed May 16 17:03:11 2012 Started redo scan Wed May 16 17:03:11 2012 Errors in file /oracle/admin/odsdb/udump/odsdb1_ora_2040024.trc: ORA-00305: log 14 of thread 1 inconsistent; belongs to another database ORA-00312: online log 14 thread 1: '/dev/rods_redo1_2_2' ORA-00305: log 14 of thread 1 inconsistent; belongs to another database ORA-00312: online log 14 thread 1: '/dev/rods_redo1_2_1' ORA-305 signalled during: ALTER DATABASE OPEN...
存储整体异常
Mon Oct 17 09:35:09 2011 Errors in file /oracle/app/admin/orcl/bdump/orcl2_lgwr_348814.trc: ORA-00340: IO error processing online log 4 of thread 2 ORA-00345: redo log write error block 6732 count 2 ORA-00312: online log 4 thread 2: '/dev/rredo21' ORA-27063: number of bytes read/written is incorrect IBM AIX RISC System/6000 Error: 6: No such device or address Additional information: -1 Additional information: 1024 Mon Oct 17 09:35:09 2011 LGWR: terminating instance due to error 340
存储IO异常
Fri Feb 21 08:44:42 2014 Thread 1 advanced to log sequence 591 (LGWR switch) Current log# 1 seq# 591 mem# 0: J:\ORADATA\ORCL\REDO01.LOG Fri Feb 21 15:31:20 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_10312.trc: ORA-00316: log 1 of thread 1, type 286 in header is not log file ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'
使用_disable_logging参数
Sat May 14 23:16:49 2005 Errors in file d:\oracle\admin\rman\bdump\rman_arc0_736.trc: ORA-16038: log 3 sequence# 72 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\RMAN\REDO03.LOG'
如果你在使用这些思路进行恢复遇到突发情况不能自行解决,请联系我们,将为您提供专业数据库技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
姊妹篇
undo异常总结和恢复思路
ORACLE丢失各种文件导致数据库不能OPEN恢复
发表在 Oracle备份恢复
标签为 ORA-00316, ORA-00327, ORA-01194, ORA-01623, ORA-01624, ORA-600 2662, ORA-600 4000, ORACLE恢复, ORACLE数据库恢复, redo异常
一条评论