Extracting Datafile Blocks From ASM

最新推荐文章于 2021-12-24 18:20:19 发布

ZhongWeicheng

最新推荐文章于 2021-12-24 18:20:19 发布

阅读量182

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/staricqxyz/article/details/18356445

Oracle 同时被 3 个专栏收录

165 篇文章

订阅专栏

Oracle ASM

22 篇文章

订阅专栏

Oracle Troubleshooting

4 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中修复损坏数据块的方法。通过使用RMAN工具进行坏块的定位与制造，再利用DD工具从文件系统层面修复损坏的数据块，并最终通过RMAN将修复后的数据文件恢复到数据库中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查看数据存放的file#和block#

[oracle@db11g ~]$ sqlplus zwc

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 16 14:02:27 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select count(*) from tab01;

  COUNT(*)
----------
     86956

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from tab01 where rownum<=500;

     FILE#     BLOCK#
---------- ----------
         6        131
         6        135
         6        134
         6        133
         6        132
         6        137
         6        136

7 rows selected.

使用RMAN制造坏块

[oracle@db11g ~]$ rman 

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 16 14:04:13 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target

connected to target database: ZWC (DBID=592899731)

RMAN> list backup;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> blockrecover datafile 6 block 137 clear;

Starting recover at 16-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
Finished recover at 16-JAN-14

RMAN>

SQL> SELECT /*+ FIRST_ROWS */ * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TAB01) A WHERE ROWNUM <= 500 ) WHERE RN >= 480;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 137)
ORA-01110: data file 6: '+DATADG/zwc/datafile/zwc.263.837006911'



no rows selected

[oracle@db11g ~]$ dbv file="+DATADG/zwc/datafile/zwc.263.837006911" userid=system/oracle

DBVERIFY: Release 11.2.0.4.0 - Production on Thu Jan 16 14:06:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DATADG/zwc/datafile/zwc.263.837006911
Page 137 is marked corrupt
Corrupt block relative dba: 0x01800089 (file 6, block 137)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x01800089
 last change scn: 0x0000.001030c6 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x30c60602
 check value in block header: 0xc058
 computed block checksum: 0x70d2



DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 1241
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 182
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 167
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 11209
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

将file#6数据文件离线，确保没有块变化

SQL> alter database datafile 6 offline;

Database altered.

使用RMAN将file#6从ASM中提取到文件系统

RMAN> run {
2> set maxcorrupt for datafile 6 to 1;
3> backup as copy datafile 6 format '/tmp/df_%f';
4> }

executing command: SET MAX CORRUPT

Starting backup at 16-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATADG/zwc/datafile/zwc.263.837006911
output file name=/tmp/df_6 tag=TAG20140116T141706 RECID=2 STAMP=837008230
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 16-JAN-14

使用dd提取、检查和修复坏块

[oracle@db11g tmp]$ dd if=df_6 of=df_6.dd count=1 skip=137 bs=8192
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.5577e-05 seconds, 230 MB/s
[oracle@db11g tmp]$ 
[oracle@db11g tmp]$ dd if=df_6.dd of=df_6 bs=8192 seek=137 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 2.736e-05 seconds, 299 MB/s
[oracle@db11g tmp]$

使用RMAN将修复好的file#6恢复到ASM中

RMAN> list  copy;

specification does not match any control file copy in the repository
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
2       6    A 16-JAN-14       1062097    16-JAN-14      
        Name: /tmp/df_6
        Tag: TAG20140116T141706

List of Archived Log Copies for database with db_unique_name ZWC
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
1       1    5       A 15-JAN-14
        Name: +FRADG/zwc/archivelog/2014_01_15/thread_1_seq_5.260.836937233

2       1    6       A 15-JAN-14
        Name: +FRADG/zwc/archivelog/2014_01_15/thread_1_seq_6.261.836949657

3       1    7       A 15-JAN-14
        Name: +FRADG/zwc/archivelog/2014_01_16/thread_1_seq_7.262.837000313


RMAN> run{
2> restore datafile 6 from tag 'TAG20140116T141706';
3> recover datafile 6;
4> sql 'alter database datafile 6 online';
5> }

Starting restore at 16-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK

channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=2 STAMP=837008230 file name=/tmp/df_6
destination for restore of datafile 00006: +DATADG/zwc/datafile/zwc.263.837006911
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=+DATADG/zwc/datafile/zwc.263.837006911 RECID=0 STAMP=0
Finished restore at 16-JAN-14

Starting recover at 16-JAN-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 16-JAN-14

sql statement: alter database datafile 6 online

RMAN>

验证