Oracle 当前redo 损坏恢复

世事洞明皆学问，人情练达即文章。这篇文章主要讲述Oracle 当前redo 损坏恢复相关的知识，希望能为你提供帮助。
测试一 insert
会话1

/*创建数据*/sqlplus scott/scott create table t6 (id int,name varchar2(100)); beginfor i in 1 .. 50000 loop insert into t6 values(i,AAAAAA); end loop; commit; end; / begin for i in 1 .. 50000 loop insert into t6 values(i,AAAAAA); end loop; end; /SQL> select count(*) from t6; COUNT(*) ---------- 100000

会话2

/*破坏redo文件*/ sqlplus / as sysdba SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 UNUSED 3 UNUSED[oracle@slave prod]$ dd if=/dev/null of=/u01/app/oracle/oradata/prod/redo01.log bs=512 count=10 记录了0+0 的读入记录了0+0 的写出 0字节(0 B)已复制，0.000111934 秒，0.0 kB/秒[oracle@slave prod]$ sqlplus / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 4449 Session ID: 38 Serial number: 5

alert 日志

Tue Feb 22 11:05:27 2022 Thread 1 cannot allocate new log, sequence 2 Private strand flush not complete Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/prod/redo01.log Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_lgwr_4313.trc: ORA-00320: cannot read file header from log 1 of thread 1 ORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/prod/redo01.log ORA-27072: File I/O error Additional information: 4 Additional information: 1 Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_lgwr_4313.trc: ORA-00320: cannot read file header from log 1 of thread 1 ORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/prod/redo01.log ORA-27072: File I/O error Additional information: 4 Additional information: 1

不完全恢复

一. 启动 SQL> startup mount; 日志alert Thread 1 cannot allocate new log, sequence 2 Private strand flush not complete Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/prod/redo01.log Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_lgwr_4313.trc: ORA-00320: cannot read file header from log 1 of thread 1 ORA-00312: online log 1 thread 1: /u01/app/oracle/oradata/prod/redo01.log ORA-27072: File I/O error Additional information: 4二. 不完全恢复 SQL> recover database until cancel; auto三. 隐含参数 *._allow_resetlogs_corruption=TRUE *._allow_error_simulation=TRUE四. 启动 SQL> startup mount pfile =/u01/backup/pfile.ora; SQL> alter database open resetlogs; 五. 查看数据 SQL> select count(*) from Scott.t6; COUNT(*) ---------- 50000

测试二 delete
会话1

/*创建数据*/ SQL> select count(*) from t6; COUNT(*) ---------- 50000SQL> delete from t6 where rownum < 10000; 9999 rows deleted.SQL> select count(*) from t6; COUNT(*) ---------- 40001SQL> commit; Commit complete.

会话2

/*破坏redo文件*/ 一 SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 CURRENT 3 INACTIVE[oracle@slave prod]$ dd if=/dev/null of=/u01/app/oracle/oradata/prod/redo02.log bs=512 count=10 记录了0+0 的读入记录了0+0 的写出 0字节(0 B)已复制，0.000118987 秒，0.0 kB/秒SQL> shutdown immediate ORA-03113: end-of-file on communication channel Process ID: 7852 Session ID: 28 Serial number: 23/*恢复操作*/SQL> startup mount; ORACLE instance started. Total System Global Area 1202556928 bytes Fixed Size2252704 bytes Variable Size771752032 bytes Database Buffers419430400 bytes Redo Buffers9121792 bytes Database mounted.SQL> recover database until cancel; ORA-00279: change 1104809 generated at 02/22/2022 11:45:33 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/PROD/archivelog/2022_02_22/o1_mf_1_5_%u_.arc ORA-00280: change 1104809 for thread 1 is in sequence #5 Specify log: < RET> =suggested | filename | AUTO | CANCEL autoSQL> alter database open resetlogs; ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: /u01/app/oracle/oradata/prod/system01.dbfSQL> shutdown abort; SQL> startup mount pfile=/u01/backup/pfile.ora; ORACLE instance started. Total System Global Area 1202556928 bytes Fixed Size2252704 bytes Variable Size771752032 bytes Database Buffers419430400 bytes Redo Buffers9121792 bytes Database mounted.SQL> alter database open resetlogs ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [1104816], [0], [1105040], [4194432], [], [], [], [], [], [] Process ID: 8037 Session ID: 1 Serial number: 5SQL> alter session set events 10015 trace name adjust_scn level 1 SQL> alter database open;

open resetlogs 报错

SQL> alter database open resetlogs 2; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [1104816], [0], [1105040], [4194432], [], [], [], [], [], [] Process ID: 8037 Session ID: 1 Serial number: 5

alert 日志

日志 alertMon Feb 21 22:32:21 2022 SMON: enabling cache recovery Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_5021.trc(incident=9753): ORA-00600: internal error code, arguments: [2662], [0], [1027755], [0], [1029748], [4194432], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/prod/prod/incident/incdir_9753/prod_ora_5021_i9753.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_5021.trc: ORA-00600: internal error code, arguments: [2662], [0], [1027755], [0], [1029748], [4194432], [], [], [], [], [], [] Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_5021.trc: ORA-00600: internal error code, arguments: [2662], [0], [1027755], [0], [1029748], [4194432], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 5021): terminating the instance due to error 600 Instance terminated by USER, pid = 5021 ORA-1092 signalled during:alter database open resetlogs... opiodr aborting process unknown ospid (5021) as a result of ORA-1092 Mon Feb 21 22:32:23 2022解释： ORA-600 [2662] "Block SCN is ahead of Current SCN"，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数，分别代表不同的含义， ORA-600 [2662] [a] [b] [c] [d] [e] Arg [a] Current SCN WRAP Arg [b] Current SCN BASE Arg [c] dependent SCN WRAP Arg [d] dependent SCN BASE Arg [e] Where present this is the DBA where the dependent SCN came from. 当前的报错是当前的SCN=1027755 小于dependent SCN4194432！

【Oracle 当前redo 损坏恢复】恢复后建议导出导入一次。
参考blog

https://www.xifenfei.com/2011/12/%e5%bc%82%e5%b8%b8%e6%96%ad%e7%94%b5%e5%af%bc%e8%87%b4current-redo%e6%8d%9f%e5%9d%8f%e5%a4%84%e7%90%86.html https://developer.aliyun.com/article/27940 https://blog.csdn.net/zhengwei125/article/details/50432605