环境同上一篇
模拟处于active状态的redo log损坏
sesion 1
SYS@ orcl >/ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 19 52428800 512 2 NO CURRENT 9777054 06-JUN-19 2.8147E+14 2 1 17 52428800 512 1 YES ACTIVE 9776201 06-JUN-19 9776627 06-JUN-19 3 1 18 52428800 512 1 YES ACTIVE 9776627 06-JUN-19 9777054 06-JUN-19
session 2
[oracle@DSI ~]$ sqlplus test/test SQL> create table t7 (id int,name varchar2(100)); Table created. SQL> begin for i in 1 .. 50000000 loop insert into t7 values(i,'AAAAAA'); end loop; commit; end; /
session 1
SYS@ orcl >shutdown abort; ORACLE instance shut down.
session 2 报错
/ 2 3 4 5 6 7 8 begin * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6269 Session ID: 141 Serial number: 527
SQL> conn /as sysdba Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2257352 bytes Variable Size 478154296 bytes Database Buffers 297795584 bytes Redo Buffers 6791168 bytes Database mounted. SQL> set linesize 1000 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 19 52428800 512 2 YES ACTIVE 9777054 06-JUN-19 9777480 06-JUN-19 3 1 18 52428800 512 1 YES ACTIVE 9776627 06-JUN-19 9777054 06-JUN-19 2 1 20 52428800 512 1 NO CURRENT 9777480 06-JUN-19 2.8147E+14 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------------------------------- --- 2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO 3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO 1 ONLINE /u01/app/oracle/oradata/orcl/redo11.log
损坏active的3号文件
[oracle@DSI ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo03.log bs=512 count=20 0+0 records in 0+0 records out 0 bytes (0 B) copied, 0.000121828 s, 0.0 kB/s
打开报错
SQL> alter database open; alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log' ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device Additional information: 1 SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery SQL> recover database until cancel; ORA-00279: change 9776627 generated at 06/06/2019 09:25:59 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_18_ghjtxbjq_.arc ORA-00280: change 9776627 for thread 1 is in sequence #18 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 9777054 generated at 06/06/2019 09:26:02 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_19_ghjtxfjt_.arc ORA-00280: change 9777054 for thread 1 is in sequence #19 ORA-00278: log file '/u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_18_ghjtxbjq_.arc' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 9777480 generated at 06/06/2019 09:26:05 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_20_%u_.arc ORA-00280: change 9777480 for thread 1 is in sequence #20 ORA-00278: log file '/u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_19_ghjtxfjt_.arc' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_20_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 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/app/oracle/oradata/orcl/system01.dbf' 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/app/oracle/oradata/orcl/system01.dbf' [oracle@DSI ~]$ tail -n 100 /tmp/pfile.ora
修改隐含参数
*._allow_resetlogs_corruption=true
*._allow_error_simulation=true
[oracle@DSI ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 6 09:32:52 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/tmp/pfile.ora' mount; ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2257352 bytes Variable Size 478154296 bytes Database Buffers 297795584 bytes Redo Buffers 6791168 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 1 52428800 512 2 YES ACTIVE 9777481 06-JUN-19 9778409 06-JUN-19 2 1 2 52428800 512 1 YES ACTIVE 9778409 06-JUN-19 9778925 06-JUN-19 3 1 3 52428800 512 1 NO CURRENT 9778925 06-JUN-19 2.8147E+14 SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> select * from test.t7; no rows selected
查看错误日志
[oracle@DSI ~]$ tail -f -n 100 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
##处于active/current redo log损坏，进行强制启动打开的话，比较小的库最好是进行导出导出，防止出现其他错误，比较大的库（上百t）迁移的时间就比较长
如果有报错，有些可能修复，有些不能修复的致命错误比如ora-600等，还是导出导入修复比较安全