Redo logfile os block header损坏怎么办
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
前几天某客户遇到这个问题；文件系统损坏导致Current redo log异常；最终恢复过程比较简单，这里不再累述。主要是想简单测试一下，对于redo log os header block的损坏如何进行简单处理。如下是简单测试过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> select * from v$Log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ---------- 1 1 21 209715200 512 1 NO INACTIVE 13356440 12-MAY-20 13356445 12-MAY-20 0 2 1 22 209715200 512 1 NO CURRENT 13356445 12-MAY-20 9.2954E+18 0 3 1 19 209715200 512 1 NO INACTIVE 13356426 12-MAY-20 13356435 12-MAY-20 0 4 1 20 104857600 512 1 NO INACTIVE 13356435 12-MAY-20 13356440 12-MAY-20 0 SQL> select member from v$Logfile; MEMBER -------------------------------------------------- /opt/oracle/oradata/ENMOTECH/redo03.log /opt/oracle/oradata/ENMOTECH/redo02.log /opt/oracle/oradata/ENMOTECH/redo01.log /opt/oracle/oradata/ENMOTECH/redo04.log SQL> shutdown abort; ORACLE instance shut down. SQL> host [oracle@mysqldb1 ~]$ cp /opt/oracle/oradata/ENMOTECH/redo02.log /opt/oracle/oradata/ENMOTECH/redo02.log.bak |
这里我们分别dump一下redo log的os block：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[oracle@mysqldb1 ~]$ dd if=/opt/oracle/oradata/ENMOTECH/redo02.log bs=512 count=1 | od -x 1+0 records in 1+0 records out 512 bytes (512 B) copied, 4.6621e-05 s, 11.0 MB/s 0000000 2200 0000 0000 ffc0 0000 0000 0000 0400 0000020 9dc3 0000 0200 0000 4000 0006 7c7d 7a7b 0000040 0003 0000 0000 0000 0000 0000 0000 0000 0000060 0000 0000 0000 0000 0000 0000 0000 0000 * 0001000 [oracle@mysqldb1 ~]$ dd if=/opt/oracle/oradata/ENMOTECH/redo01.log bs=512 count=1 | od -x 1+0 records in 1+0 records out 512 bytes (512 B) copied, 6.8299e-05 s, 7.5 MB/s 0000000 2200 0000 0000 ffc0 0000 0000 0000 0400 0000020 9dc3 0000 0200 0000 4000 0006 7c7d 7a7b 0000040 0003 0000 0000 0000 0000 0000 0000 0000 0000060 0000 0000 0000 0000 0000 0000 0000 0000 * 0001000 |
由于这2个redo log文件大小完全一致，因此dump内容完全一致；这里我们针对上述内容进行简单解释：
22 ： 表示file type；即logfile;如果为a2则表示是datafile
200: 转换为10进制为512，表示block size
4000 0006: 表示logfile size大小，单位是block；转换为10进制后卫4096000
7c7d 7b7a:表示mgiac number
0003: 表示file number
下面尝试破坏redo log os block：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[oracle@mysqldb1 ~]$ dd if=/opt/oracle/oradata/ENMOTECH/redo02.log of=/tmp/dd_redo_corrupt bs=512 count=1 1+0 records in 1+0 records out 512 bytes (512 B) copied, 0.000263995 s, 1.9 MB/s [oracle@mysqldb1 ~]$ vi /tmp/dd_redo_corrupt [oracle@mysqldb1 ~]$ ls -ltr /tmp/dd_redo_corrupt -rw-r--r-- 1 oracle oinstall 512 May 12 15:55 /tmp/dd_redo_corrupt [oracle@mysqldb1 ~]$ 这里vi随便编辑输入一些内容，注意保证该文件大小必须为512 byte. [oracle@mysqldb1 ~]$ dd if=/tmp/dd_redo_corrupt of=/opt/oracle/oradata/ENMOTECH/redo02.log bs=512 count=1 conv=notrunc 1+0 records in 1+0 records out 512 bytes (512 B) copied, 0.000312707 s, 1.6 MB/s |
我们启动数据库看看情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> startup ORACLE instance started. Total System Global Area 1157626160 bytes Fixed Size 9566512 bytes Variable Size 671088640 bytes Database Buffers 469762048 bytes Redo Buffers 7208960 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 85646 Session ID: 1 Serial number: 23142 2020-05-12T16:03:16.597704+08:00 Errors in file /opt/oracle/diag/rdbms/enmotech/enmotech/trace/enmotech_lgwr_85776.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ENMOTECH/redo02.log' ORA-27048: skgfifi: file header information is invalid Additional information: 2 2020-05-12T16:03:16.597845+08:00 Errors in file /opt/oracle/diag/rdbms/enmotech/enmotech/trace/enmotech_lgwr_85776.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ENMOTECH/redo02.log' ORA-27048: skgfifi: file header information is invalid Additional information: 2 2020-05-12T16:03:16.601276+08:00 Errors in file /opt/oracle/diag/rdbms/enmotech/enmotech/trace/enmotech_ora_85852.trc: ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ENMOTECH/redo02.log' 2020-05-12T16:03:16.690686+08:00 System state dump requested by (instance=1, osid=85852), summary=[abnormal instance termination]. System State dumped to trace file /opt/oracle/diag/rdbms/enmotech/enmotech/trace/enmotech_diag_85753.trc USER (ospid: 85852): terminating the instance due to ORA error 313 2020-05-12T16:03:16.793750+08:00 |
如我们所想；报错完全一样。。。Oracle无法识别到这个redo logfile.
那么既然每个redo log文件的os block几乎都类似，能不能直接copy覆盖呢？答案是：当然可以
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
+++尝试还原 首先将一个完好的redo log header 备份出来(注意确保redo 大小必须一致)： [oracle@mysqldb1 ~]$ dd if=/opt/oracle/oradata/ENMOTECH/redo03.log of=/tmp/dd_good bs=512 count=1 1+0 records in 1+0 records out 512 bytes (512 B) copied, 0.000187029 s, 2.7 MB/s 然后通过dd 进行还原: [oracle@mysqldb1 ~]$ dd if=/tmp/dd_good of=/opt/oracle/oradata/ENMOTECH/redo02.log bs=512 count=1 conv=notrunc 1+0 records in 1+0 records out 512 bytes (512 B) copied, 0.000256535 s, 2.0 MB/s [oracle@mysqldb1 ~]$ +++启动数据库并进行验证 [oracle@mysqldb1 ~]$ sqlplus "/as sysdba" SQL*Plus: Release 20.0.0.0.0 - Production on Tue May 12 16:06:03 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1157626160 bytes Fixed Size 9566512 bytes Variable Size 671088640 bytes Database Buffers 469762048 bytes Redo Buffers 7208960 bytes Database mounted. Database opened. SQL> 2020-05-12T16:06:19.563598+08:00 ALTER DATABASE OPEN Ping without log force is disabled: instance mounted in exclusive mode. 2020-05-12T16:06:19.580961+08:00 Crash Recovery excluding pdb 2 which was cleanly closed. Endian type of dictionary set to little 2020-05-12T16:06:19.608797+08:00 Thread 1 advanced to log sequence 23 (thread open) Redo log for group 3, sequence 23 is not located on DAX storage Thread 1 opened at log sequence 23 Current log# 3 seq# 23 mem# 0: /opt/oracle/oradata/ENMOTECH/redo03.log Successful open of redo thread 1 2020-05-12T16:06:19.625657+08:00 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set stopping change tracking 2020-05-12T16:06:19.730666+08:00 TT00 (PID:86011): Gap Manager starting 2020-05-12T16:06:20.076465+08:00 Undo initialization recovery: Parallel FPTR failed: start:290504951 end:290504965 diff:14 ms (0.0 seconds) Undo initialization recovery: err:0 start: 290504944 end: 290505032 diff: 88 ms (0.1 seconds) [86009] Successfully onlined Undo Tablespace 2. Undo initialization online undo segments: err:0 start: 290505037 end: 290505271 diff: 234 ms (0.2 seconds) Undo initialization finished serial:0 start:290504944 end:290505281 diff:337 ms (0.3 seconds) Database Characterset is AL32UTF8 2020-05-12T16:06:20.611451+08:00 No Resource Manager plan active 2020-05-12T16:06:21.606709+08:00 joxcsys_required_dirobj_exists: directory object exists with required path /opt/soft/javavm/admin/, pid 86009 cid 1 2020-05-12T16:06:21.690387+08:00 Starting background process RCBG 2020-05-12T16:06:21.765583+08:00 RCBG started with pid=45, OS id=86019 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process AQPC 2020-05-12T16:06:22.174256+08:00 AQPC started with pid=46, OS id=86021 2020-05-12T16:06:23.474703+08:00 PDB$SEED(2):Autotune of undo retention is turned on. 2020-05-12T16:06:23.505054+08:00 PDB$SEED(2):Endian type of dictionary set to little PDB$SEED(2):Undo initialization finished serial:0 start:290508680 end:290508680 diff:0 ms (0.0 seconds) PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8 2020-05-12T16:06:24.881991+08:00 PDB$SEED(2):Opening pdb with no Resource Manager plan active 2020-05-12T16:06:25.222055+08:00 : QPI: opatch file present, opatch : QPI: qopiprep.bat file present 2020-05-12T16:06:26.079591+08:00 KILLDB(3):Autotune of undo retention is turned on. 2020-05-12T16:06:26.113478+08:00 KILLDB(3):Endian type of dictionary set to little KILLDB(3):Undo initialization recovery: Parallel FPTR failed: start:290511271 end:290511285 diff:14 ms (0.0 seconds) KILLDB(3):Undo initialization recovery: err:0 start: 290511270 end: 290511340 diff: 70 ms (0.1 seconds) KILLDB(3):[86009] Successfully onlined Undo Tablespace 2. KILLDB(3):Undo initialization online undo segments: err:0 start: 290511340 end: 290511764 diff: 424 ms (0.4 seconds) KILLDB(3):Undo initialization finished serial:0 start:290511270 end:290511786 diff:516 ms (0.5 seconds) KILLDB(3):Database Characterset for KILLDB is AL32UTF8 2020-05-12T16:06:28.836526+08:00 KILLDB(3):Opening pdb with no Resource Manager plan active KILLDB(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/soft/javavm/admin/, pid 86009 cid 3 Pluggable database KILLDB opened read write 2020-05-12T16:06:29.905303+08:00 Starting background process CJQ0 2020-05-12T16:06:29.979162+08:00 CJQ0 started with pid=52, OS id=86206 Completed: ALTER DATABASE OPEN |
那么如果我数据库中的redo log大小不一致怎么办呢？ 因为os block中有记录文件大小；大小不一致也没有关系；我们dd后编辑一下即可。
Leave a Reply
You must be logged in to post a comment.