Oracle 恢复坏块方法（ORA-01578)—RMAN恢复

左手的ㄟ右手 2022-05-31 23:45 299阅读 0赞

之前我们测试过利用bbed跳过坏块进行恢复，但是这种方法却会丢失部分数据，那么有什么办法可以确保数据不会被丢失呢？

如果在坏块之前，有rman备份，可以使用rman的备份来进行恢复，确保数据不会被丢失。

1.创建测试表

  1. SQL> conn test/test
  2. Connected.
  3. SQL> create table test as select * from all_objects;
  5. Table created.
  7. SQL> select count(*) from test;
  9.   COUNT(*)
  10. ----------
  11.      80440

2.对数据库进行rman备份

  1. cebpm:/data/backup/cebpm/fullback@cebpm>rman target /
  3. Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 15 09:24:50 2018
  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
  7. connected to target database: CEBPM (DBID=3677012495)
  9. RMAN> backup database format '/data/backup/cebpm/fullback/cebpm';
  11. Starting backup at 2018/01/15 09:25:17
  12. using target database control file instead of recovery catalog
  13. allocated channel: ORA_DISK_1
  14. channel ORA_DISK_1: SID=34 device type=DISK
  15. channel ORA_DISK_1: starting full datafile backup set
  16. channel ORA_DISK_1: specifying datafile(s) in backup set
  17. input datafile file number=00001 name=/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf
  18. input datafile file number=00002 name=/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf
  19. input datafile file number=00005 name=/data/CEBPM/datafile/test01.dbf
  20. input datafile file number=00003 name=/data/CEBPM/datafile/undotbs01.dbf
  21. input datafile file number=00004 name=/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf
  22. channel ORA_DISK_1: starting piece 1 at 2018/01/15 09:25:21
  23. channel ORA_DISK_1: finished piece 1 at 2018/01/15 09:27:07
  24. piece handle=/data/backup/cebpm/fullback/cebpm tag=TAG20180115T092520 comment=NONE
  25. channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
  26. Finished backup at 2018/01/15 09:27:07
  28. Starting Control File and SPFILE Autobackup at 2018/01/15 09:27:09
  29. piece handle=/data/backup/cebpm/ctlbackup/control_c-3677012495-20180115-01 comment=NONE
  30. Finished Control File and SPFILE Autobackup at 2018/01/15 09:27:12

3.利用bbed破坏数据

具体可参考:

http://blog.csdn.net/shiyu1157758655/article/details/79043618

4.验证坏块

  1. cebpm:/home/oracle@cebpm>dbv file=/data/CEBPM/datafile/test01.dbf 
  3. DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jan 15 09:33:09 2018
  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
  7. DBVERIFY - Verification starting : FILE = /data/CEBPM/datafile/test01.dbf
  8. Page 131 is marked corrupt
  9. Corrupt block relative dba: 0x01400083 (file 5, block 131)
  10. Bad check value found during dbv: 
  11. Data in bad block:
  12.  type: 6 format: 2 rdba: 0x01400083
  13.  last change scn: 0x0000.0012dad3 seq: 0x2 flg: 0x04
  14.  spare1: 0x0 spare2: 0x0 spare3: 0x0
  15.  consistency value in tail: 0xdad30602
  16.  check value in block header: 0x4fe7
  17.  computed block checksum: 0x33c4
  21. DBVERIFY - Verification complete
  23. Total Pages Examined         : 16384
  24. Total Pages Processed (Data) : 1149
  25. Total Pages Failing   (Data) : 0
  26. Total Pages Processed (Index): 0
  27. Total Pages Failing   (Index): 0
  28. Total Pages Processed (Other): 155
  29. Total Pages Processed (Seg)  : 0
  30. Total Pages Failing   (Seg)  : 0
  31. Total Pages Empty            : 15079
  32. Total Pages Marked Corrupt   : 1
  33. Total Pages Influx           : 0
  34. Total Pages Encrypted        : 0
  35. Highest block SCN            : 1235718 (0.1235718)
  37. SQL> select count(*) from test;
  38. select count(*) from test
  39.                      *
  40. ERROR at line 1:
  41. ORA-01578: ORACLE data block corrupted (file # 5, block # 131)
  42. ORA-01110: data file 5: '/data/CEBPM/datafile/test01.dbf'

5.使用rman进行恢复

  1. cebpm:/home/oracle@cebpm>rman target /
  3. Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 15 09:34:48 2018
  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
  7. connected to target database: CEBPM (DBID=3677012495)
  9. RMAN> blockrecover datafile 5 block 131;
  11. Starting recover at 2018/01/15 09:37:00
  12. using target database control file instead of recovery catalog
  13. allocated channel: ORA_DISK_1
  14. channel ORA_DISK_1: SID=37 device type=DISK
  15. searching flashback logs for block images until SCN 1235817
  16. finished flashback log search, restored 1 blocks
  18. starting media recovery
  19. media recovery complete, elapsed time: 00:00:03
  21. Finished recover at 2018/01/15 09:37:10

6.检测坏块是否被修复

  1. cebpm:/home/oracle@cebpm>dbv file=/data/CEBPM/datafile/test01.dbf 
  3. DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jan 15 09:38:42 2018
  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
  7. DBVERIFY - Verification starting : FILE = /data/CEBPM/datafile/test01.dbf
  10. DBVERIFY - Verification complete
  12. Total Pages Examined         : 16384
  13. Total Pages Processed (Data) : 1150
  14. Total Pages Failing   (Data) : 0
  15. Total Pages Processed (Index): 0
  16. Total Pages Failing   (Index): 0
  17. Total Pages Processed (Other): 155
  18. Total Pages Processed (Seg)  : 0
  19. Total Pages Failing   (Seg)  : 0
  20. Total Pages Empty            : 15079
  21. Total Pages Marked Corrupt   : 0
  22. Total Pages Influx           : 0
  23. Total Pages Encrypted        : 0
  24. Highest block SCN            : 1235718 (0.1235718)
  26. RMAN> backup check logical validate datafile 5;
  28. Starting backup at 2018/01/15 09:38:24
  29. using channel ORA_DISK_1
  30. channel ORA_DISK_1: starting full datafile backup set
  31. channel ORA_DISK_1: specifying datafile(s) in backup set
  32. input datafile file number=00005 name=/data/CEBPM/datafile/test01.dbf
  33. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  34. List of Datafiles
  35. =================
  36. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  37. ---- ------ -------------- ------------ --------------- ----------
  38. 5    OK     0              15079        16384           1235718   
  39.   File Name: /data/CEBPM/datafile/test01.dbf
  40.   Block Type Blocks Failing Blocks Processed
  41.   ---------- -------------- ----------------
  42.   Data       0              1150            
  43.   Index      0              0               
  44.   Other      0              155             
  46. Finished backup at 2018/01/15 09:38:26

7.验证数据是否正确

  1. SQL> conn test/test
  2. Connected.
  3. SQL> select count(*)  from test;
  5.   COUNT(*)
  6. ----------
  7.      80440

由上可知数据已经完全恢复，不存在丢失数据的情况。

发表评论

表情：
评论列表 （有 0 条评论，299人围观）

还没有评论，来说两句吧...

相关阅读