标签云
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,659)
- DB2 (22)
- MySQL (72)
- Oracle (1,522)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (158)
- 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备份恢复 (554)
- 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)
-
最近发表
- 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问题解决
- redo写丢失导致ORA-600 kcrf_resilver_log_1故障
- 硬件故障导致ORA-01242 ORA-01122等错误
- 200T 数据库非归档无备份恢复
- 利用flashback快速恢复failover 的备库
- [comingback2022@cock.li].eking和[tsai.shen@mailfence.com].faust扩展名勒索病毒数据库可以完美恢复
_allow_resetlogs_corruption 的搜索结果
_allow_resetlogs_corruption和adjust_scn解决ORA-01190
一、模拟offline文件然后resetlogs操作
1.设置datafile 5数据文件offline 2.rman备份数据库 3.关闭原数据库,删除数据文件/当前日志和部分归档日志 4.执行不完全恢复,resetlogs打开数据库(如下面操作) [oracle@xifenfei ora11g]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover database until cancel; ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf ORA-00280: change 868870 for thread 1 is in sequence #29 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; Database altered. SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file; FILE# ONLINE_STATUS TO_CHAR(CHANGE#,'999999999 ---------- -------------- -------------------------- 5 OFFLINE 868810 SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01190: control file or data file 5 is from before the last RESETLOGS ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf' SQL> select file#,to_char(checkpoint_change#,'999999999999'), 2 to_char(last_change#,'999999999999') from v$datafile; FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999 ---------- -------------------------- -------------------------- 1 868874 2 868874 3 868874 4 868874 5 868810 868874 --可以看到offline的数据文件,没有因为resetlogs操作而改变 --CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息 SQL> select file#,to_char(checkpoint_change#,'999999999999'), 2 to_char(RESETLOGS_CHANGE#,'999999999999') 3 from v$datafile_header; FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#, ---------- -------------------------- -------------------------- 1 868874 868871 2 868874 868871 3 868874 868871 4 868874 868871 5 868810 787897
二、隐含参数设置
SQL> create pfile='/tmp/pfile' from spfile; File created. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 在pfile中增加 _allow_resetlogs_corruption=true _allow_error_simulation=TRUE(10g及其以上版本需要)
三、打开数据库,online离线文件
SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 368263168 bytes Fixed Size 1345016 bytes Variable Size 293603848 bytes Database Buffers 67108864 bytes Redo Buffers 6205440 bytes Database mounted. --在mount状态下执行 SQL> alter session set events '10015 trace name adjust_scn level 2'; Session altered. --[一定要]在mount状态下执行online操作 SQL> alter database datafile 5 online; Database altered. SQL> recover database until cancel; ORA-00279: change 868810 generated at 03/13/2012 22:19:37 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_27_777766629.dbf ORA-00280: change 868810 for thread 1 is in sequence #27 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01190: control file or data file 1 is from before the last RESETLOGS ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; Database altered. SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file; no rows selected
姊妹篇:bbed解决ORA-01190
使用_allow_resetlogs_corruption打开无归档日志rman备份库
rman还原恢复操作
--还原数据库 RMAN> restore database; --恢复数据库 RMAN> recover database; Starting recover at 2012-03-08 21:20:45 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=65 device type=DISK starting media recovery RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/08/2012 21:20:47 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of archived log for thread 1 with sequence 2936 and starting SCN of 25991695 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 2935 and starting SCN of 25991652 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 2934 and starting SCN of 25991649 found to restore …………………… RMAN-06025: no backup of archived log for thread 1 with sequence 2902 and starting SCN of 25991156 found to restore 这里报日志缺少,实际上是备份的数据库文件后,没有备份归档日志,归档日志全部丢失
进行不完全恢复
SQL> recover database until cancel; ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf ORA-00280: change 25991194 for thread 1 is in sequence #2902 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: '/opt/oracle/oradata/chf/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: '/opt/oracle/oradata/chf/system01.dbf'
查看相关SCN
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile; FILE# TO_CHAR(CHECK ---------- ------------- 1 25992214 2 25992214 3 25992214 4 25992214 5 25992214 6 25992214 7 25992214 8 25992214 9 25992214 10 25992214 11 25992214 FILE# TO_CHAR(CHECK ---------- ------------- 13 25992214 14 25992214 13 rows selected. SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file; FILE# ONLINE_ TO_CHAR(CHANG ---------- ------- ------------- 1 ONLINE 25991194 2 ONLINE 25991194 3 ONLINE 25991194 4 ONLINE 25991194 5 ONLINE 25991194 6 ONLINE 25991194 7 ONLINE 25991194 8 ONLINE 25991194 9 ONLINE 25991194 10 ONLINE 25991194 11 ONLINE 25991194 FILE# ONLINE_ TO_CHAR(CHANG ---------- ------- ------------- 13 ONLINE 25991194 14 ONLINE 25991194 13 rows selected. SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header; FILE# TO_CHAR(CHECK ---------- ------------- 1 25991194 2 25991194 3 25991194 4 25991194 5 25991194 6 25991194 7 25991194 8 25991194 9 25991194 10 25991194 11 25991194 FILE# TO_CHAR(CHECK ---------- ------------- 13 25991194 14 25991194 13 rows selected. --发现数据文件scn和控制文件不一致,重建控制文件,然后查询相关scn SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile; FILE# TO_CHAR(CHECK ---------- ------------- 1 25991194 2 25991194 3 25991194 4 25991194 5 25991194 6 25991194 7 25991194 8 25991194 9 25991194 10 25991194 11 25991194 FILE# TO_CHAR(CHECK ---------- ------------- 13 25991194 14 25991194 13 rows selected. SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file; FILE# ONLINE_ TO_CHAR(CHANG ---------- ------- ------------- 1 ONLINE 25991194 2 ONLINE 25991194 3 ONLINE 25991194 4 ONLINE 25991194 5 ONLINE 25991194 6 ONLINE 25991194 7 ONLINE 25991194 8 ONLINE 25991194 9 ONLINE 25991194 10 ONLINE 25991194 11 ONLINE 25991194 FILE# ONLINE_ TO_CHAR(CHANG ---------- ------- ------------- 13 ONLINE 25991194 14 ONLINE 25991194 13 rows selected. SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header; FILE# TO_CHAR(CHECK ---------- ------------- 1 25991194 2 25991194 3 25991194 4 25991194 5 25991194 6 25991194 7 25991194 8 25991194 9 25991194 10 25991194 11 25991194 FILE# TO_CHAR(CHECK ---------- ------------- 13 25991194 14 25991194 13 rows selected. --此时所有scn均一致
尝试打开数据库
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: '/opt/oracle/oradata/chf/system01.dbf' SQL> recover database using backup controlfile until cancel; ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf ORA-00280: change 25991194 for thread 1 is in sequence #2902 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: '/opt/oracle/oradata/chf/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: '/opt/oracle/oradata/chf/system01.dbf'
使用隐含参数打开数据库
SQL> create pfile='/tmp/pfile' from spfile; File created. -------/tmp/pfile中加上---------- _allow_resetlogs_corruption= TRUE --------------------------------- SQL> startup mount pfile='/tmp/pfile' force ORACLE instance started. Total System Global Area 622149632 bytes Fixed Size 2230912 bytes Variable Size 419431808 bytes Database Buffers 192937984 bytes Redo Buffers 7548928 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE
总结
这次的试验没有多少实际意义,但是可以说明几个问题:
1.所有的数据文件的scn都一致,甚至和控制文件的也一致,数据库不一定可以open成功
(怀疑是数据文件中的scn大于data header scn)
2.对于这样的问题,如果使用bbed修改所有数据文件header的scn不知道是否可以解决
3.如果rman只备份了数据文件而没有任何一个归档日志,数据库通过隐含参数还是可以open,抢救数据
Oracle 19C 报ORA-704 ORA-01555故障处理
异常断电导致数据库无法启动,尝试对数据文件进行recover操作,报ORA-00283 ORA-00742 ORA-00312错误,由于redo写丢失无法正常应用
D:\check_db>sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 7月 30 07:49:19 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. 连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> recover datafile 1; ORA-00283: 恢复会话因错误而取消 ORA-00742: 日志读取在线程 1 序列 9274 块 18057 中检测到写入丢失情况 ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'
屏蔽数据一致性,尝试强制打开库,报ORA-00604,ORA-00704,ORA-01555错误
SQL> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced 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 9 with name "_SYSSMU9_4165470211$" too small 进程 ID: 4036 会话 ID: 2277 序列号: 40707
alert日志对应错误
2023-07-30T06:54:43.457383+08:00 .... (PID:5836): Clearing online redo logfile 1 complete .... (PID:5836): Clearing online redo logfile 2 complete .... (PID:5836): Clearing online redo logfile 3 complete Resetting resetlogs activation ID 3572089731 (0xd4e9c383) Online log D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG: Thread 1 Group 1 was previously cleared Online log D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG: Thread 1 Group 2 was previously cleared Online log D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO03.LOG: Thread 1 Group 3 was previously cleared 2023-07-30T06:54:43.863676+08:00 Setting recovery target incarnation to 2 2023-07-30T06:54:44.816771+08:00 Ping without log force is disabled: instance mounted in exclusive mode. Endian type of dictionary set to little 2023-07-30T06:54:44.957395+08:00 Assigning activation ID 3664275149 (0xda6866cd) 2023-07-30T06:54:44.957395+08:00 TT00 (PID:4640): Gap Manager starting 2023-07-30T06:54:45.004305+08:00 Redo log for group 1, sequence 1 is not located on DAX storage 2023-07-30T06:54:46.176153+08:00 Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG Successful open of redo thread 1 2023-07-30T06:54:46.191771+08:00 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set stopping change tracking 2023-07-30T06:54:46.223036+08:00 TT03 (PID:1816): Sleep 5 seconds and then try to clear SRLs in 2 time(s) 2023-07-30T06:54:46.332398+08:00 ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000000017b852a7 ): 2023-07-30T06:54:46.332398+08:00 select ctime, mtime, stime from obj$ where obj# = :1 2023-07-30T06:54:46.332398+08:00 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc: ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 2023-07-30T06:54:46.332398+08:00 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 2023-07-30T06:54:46.348028+08:00 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 Error 704 happened during db open, shutting down database Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc (incident=474502): ORA-00603: ORACLE 服务器会话因致命错误而终止 ORA-01092: ORACLE 实例终止。强制断开连接 ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_474502\xff_ora_5836_i474502.trc 2023-07-30T06:54:47.785549+08:00 opiodr aborting process unknown ospid (5836) as a result of ORA-603 2023-07-30T06:54:47.816792+08:00 ORA-603 : opitsk aborting process License high water mark = 6 USER (ospid: (prelim)): terminating the instance due to ORA error
这类错误比较常见,参考以前类似恢复:
在数据库open过程中常遇到ORA-01555汇总
数据库open过程遭遇ORA-1555对应sql语句补充
Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障
使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
对于本次故障,通过Oracle Recovery Tools工具快速处理
open数据库成功
SQL> alter database open; 数据库已更改。 SQL> SQL> SQL> select status,count(1) from v$datafile group by status; STATUS COUNT(1) -------------- ---------- SYSTEM 1 ONLINE 61