4 Replies Latest reply: Dec 4, 2012 11:36 PM by 977352 RSS

    如何修復 ORA-01578: ORACLE data block corrupted ?

    977352
      [現象]
      Oracle 版本是 11.2.0.3.0
      本來打算做backup server的Oracle備份,但是RMAN報錯如下:
      RMAN-03009: failure of backup command on oem_disk_backup channel at 11/28/2012 18:42:02
      ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/SID/users01.dbf
      然後去查alter log,發現有如下錯誤:
      ORA-01578: ORACLE data block corrupted (file # 4, block # 642)
      ORA-01110: data file 4: '/u01/app/oracle/oradata/SID/users01.dbf'

      用如下sql:
      SELECT segment_name,segment_type,extent_id,block_id, blocks
      from dba_extents t
      where file_id = 4
      AND 642 between block_id and (block_id + blocks - 1) ;
      檢查結果是
      no rows selected

      [起因]
      問了同事,他想做個production server的完全鏡像,他通過RAID 1熱插拔從production server拷貝過來了,他說當時Oracle還在運行中。

      [問題]
      1. 我覺得這種方式的拷貝肯定有問題,但是是如何造成ORA-01578的,希望能從redo log buffer,redo log, data files和control file這些層面有個詳細的瞭解?
      2. 爲什麽上面的sql結果是no rows selected?不是報file # 4, block # 642有錯嗎?
      3. 如何修復ORA-01578? 現在的production server和backup server上都沒有任何備份,想通過RMAN命令修復,估計是不行吧?那怎麼才能修復USERS01.dbf呢?可以重建USERS表空間嗎?
      4. 還有幾個問題待續。。。

      帖子经 user5734681编辑过

      帖子经 user5734681编辑过
        • 1. Re: 如何修復 ORA-01578: ORACLE data block corrupted ?
          LiuMaclean(刘相兵)
          ORA-01578 =》物理坏块        


          考虑用 blockrecover/recover 命令修复相关物理坏块

          应当先 使用 如下帖子中的脚本找出 具体坏块

          http://www.askmaclean.com/archives/rman-validate-check-logical-database-corrupted-block.html


          若没有备份 且坏块出现在数据表上 则Workaround:

          考虑绕过 坏块 利用rowid 重建 所在数据表

          若没有备份 坏块出现在索引上 则考虑重建索引
          • 2. Re: 如何修復 ORA-01578: ORACLE data block corrupted ?
            977352
            我試過修復壞塊
            blockrecover datafile 4 block 642;
            但是沒作用。

            查找壞塊也試過,
            SELECT segment_name,segment_type,extent_id,block_id, blocks
            from dba_extents t
            where file_id = 4
            AND 642 between block_id and (block_id + blocks - 1);
            但是結果是
            no rows selected
            (爲什麽沒有數據呢?)

            以下是我dump的file#4,block#642
            -------------------------------------------------------------------------------------------------------
            Start dump data blocks tsn: 4 file#:4 minblk 642 maxblk 642
            Block dump from cache:
            Dump of buffer cache at level 4 for tsn=4 rdba=16777858
            BH (0xdff6df18) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xdf0a2000
            set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xe5f7a878,0x15c603620] lru: [0xe1fd1d30,0xe1fd1ad0]
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: CR md: NULL tch: 0
            cr: [scn: 0x0.c14285],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c142c3],[sfl: 0x0],[lc: 0x0.0]
            flags:
            BH (0xe5f7a7c8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe51f4000
            set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xe8fd1cf8,0xdff6dfc8] lru: [0xe5f7a650,0xe5f71570]
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: CR md: NULL tch: 0
            cr: [scn: 0x0.c1270b],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c12749],[sfl: 0x0],[lc: 0x0.0]
            flags:
            BH (0xe8fd1c48) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe8b24000
            set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 77,19
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xedf76d18,0xe5f7a878] lru: [0xe8fd1e60,0xe8fd1c00]
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: CR md: NULL tch: 0
            cr: [scn: 0x0.c10b41],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c10b7f],[sfl: 0x0],[lc: 0x0.0]
            flags:
            BH (0xedf76c68) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xed190000
            set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xeef7df18,0xe8fd1cf8] lru: [0xedf7bce0,0xedf76c20]
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: CR md: NULL tch: 0
            cr: [scn: 0x0.c0ef7c],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c0efba],[sfl: 0x0],[lc: 0x0.0]
            flags:
            BH (0xeef7de68) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xee250000
            set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 76,19
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xdef90338,0xedf76d18] lru: [0xeef85c00,0xeef7a9e0]
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: CR md: NULL tch: 0
            cr: [scn: 0x0.c0d3a9],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c0d3e7],[sfl: 0x0],[lc: 0x0.0]
            flags:
            BH (0xdef90288) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xde43c000
            set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xe1fd1bc8,0xeef7df18] lru: [0xdef904a0,0xdef94f70]
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: CR md: NULL tch: 0
            cr: [scn: 0x0.c0b86c],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c0b86c],[sfl: 0x0],[lc: 0x0.0]
            flags:
            BH (0xe1fd1b18) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe1b22000
            set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xe5f72378,0xdef90338] lru: [0xeef99580,0xe5f723b0]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xe5f722c8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe5114000
            set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xd7fd73a8,0xe1fd1bc8] lru: [0xe1fd1c00,0x15cc5a268]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xd7fd72f8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xd7bb6000
            set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 77,19
            dbwrid: 0 obj: 82235 objn: 0 tsn: 4 afn: 4 hint: f
            hash: [0xe9fb2578,0xe5f72378] lru: [0xd7fdce20,0xedfe5450]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xe9fb24c8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe97d4000
            set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xedfe5418,0xd7fd73a8] lru: [0xd7f76890,0xdefde840]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xedfe5368) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xedd30000
            set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 77,19
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xeef99548,0xe9fb2578] lru: [0xd7fd73e0,0x15cc58d68]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xeef99498) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xee532000
            set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xdefde808,0xedfe5418] lru: [0xd7fc29c0,0xe1fd1c00]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xdefde758) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xdec7a000
            set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xe1f855d8,0xeef99548] lru: [0xe9fb25b0,0xe1f85610]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xe1f85528) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe1318000
            set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
            dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
            hash: [0xf2f861b8,0xdefde808] lru: [0xdefde840,0x15cc59468]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            BH (0xf2f86108) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xf232c000
            set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 76,19
            dbwrid: 0 obj: 82235 objn: 0 tsn: 4 afn: 4 hint: f
            hash: [0x15c603620,0xe1f855d8] lru: [0x62fb13e0,0xecfceb50]
            lru-flags: on_auxiliary_list
            ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
            st: FREE md: NULL tch: 0 lfb: 33
            flags:
            Block dump from disk:
            buffer tsn: 4 rdba: 0x01000282 (4/642)
            scn: 0x0000.00a791fc seq: 0x01 flg: 0x04 tail: 0x80852301
            frmt: 0x02 chkval: 0xb664 type: 0x23=PAGETABLE SEGMENT HEADER
            Hex dump of corrupt header 2 = BROKEN
            Dump of memory from 0x00002AC14E7ADA00 to 0x00002AC14E7ADA14
            2AC14E7ADA00 0000A223 01000282 00A791FC 04010000 [#...............]
            2AC14E7ADA10 0000B664 [d...]
            Hex dump of corrupt block
            Dump of memory from 0x00002AC14E7ADA14 to 0x00002AC14E7AF9FC
            2AC14E7ADA10 00000000 00000000 00000000 [............]
            2AC14E7ADA20 00000000 00000001 00000008 00000A9C [................]
            2AC14E7ADA30 00000000 00000008 00000008 01000288 [................]
            2AC14E7ADA40 00000000 00000000 00000000 00000005 [................]
            2AC14E7ADA50 0006000A 00015C7E 00000001 00000000 [....~\..........]
            2AC14E7ADA60 00000008 00000008 01000288 00000000 [................]
            2AC14E7ADA70 00000000 00000000 00000005 01000280 [................]
            2AC14E7ADA80 01000280 00000000 00000000 00000000 [................]
            2AC14E7ADA90 00000000 00000000 00000000 00000000 [................]
            Repeat 3 times
            2AC14E7ADAD0 00000001 00002000 00000000 00001434 [..... ......4...]
            2AC14E7ADAE0 00000000 01000281 00000001 01000280 [................]
            2AC14E7ADAF0 01000281 00000000 00000000 00000000 [................]
            2AC14E7ADB00 00000000 00000000 00000001 00000000 [................]
            2AC14E7ADB10 0001413B 10000000 01000280 00000008 [;A..............]
            2AC14E7ADB20 00000000 00000000 00000000 00000000 [................]
            Repeat 152 times
            2AC14E7AE4B0 01000280 01000283 00000000 00000000 [................]
            2AC14E7AE4C0 00000000 00000000 00000000 00000000 [................]
            Repeat 151 times
            2AC14E7AEE40 00000000 00000000 01000281 00000000 [................]
            2AC14E7AEE50 00000000 00000000 00000000 00000000 [................]
            Repeat 185 times
            2AC14E7AF9F0 00000000 00000000 00000000 [............]
            End dump data blocks tsn: 4 file#: 4 minblk 642 maxblk 642
            -------------------------------------------------------------------------------------------------------

            如何分析這裏面的數據呢?
            • 3. Re: 如何修復 ORA-01578: ORACLE data block corrupted ?
              惜分飞
              有shell脚本可以读出block中的内容,只是这个block真的那么重要,非折腾出来不可吗?
              • 4. Re: 如何修復 ORA-01578: ORACLE data block corrupted ?
                977352
                這個block, 從dba_extents里是查不出來的,爲什麽alert log里說這個block有錯,可又查不出來?
                我看了此block前後的一些blocks,剛好都是production schema的數據表或索引,所以我懷疑這個有錯的block也是某個表或索引的。
                暫且不說此block是否重要,想要知道的是,這個壞塊產生的原因,是raid 1拷貝造成的還是其他原因?從上面那個dump能不能看出來出錯的原因呢。