create tablespace test datafile /u01/app/oracle/oradata/DBdb/test01.dbf size 30m; Tablespace created. SQL> create table bb..."/>
溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

設(shè)置10231事件并結(jié)合導(dǎo)入導(dǎo)出工具恢復(fù)壞塊

發(fā)布時(shí)間：2020-08-07 10:12:29 來(lái)源：ITPUB博客 閱讀：161 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫(kù)
1.模擬表、及表空間
SQL> create tablespace test datafile '/u01/app/oracle/oradata/DBdb/test01.dbf' size 30m;

Tablespace created.

SQL> create table bbed tablespace test as select * from dba_tables;

Table created.

SQL> select file_id,tablespace_name,file_name,status from dba_data_files where tablespace_name='TEST';

   FILE_ID TABLESPACE_NAME                FILE_NAME                                                    STATUS
---------- ------------------------------ ------------------------------------------------------------ ---------
         8 TEST                           /u01/app/oracle/oradata/DBdb/test01.dbf                      AVAILABLE
         
SQL> select count(*) from bbed;

  COUNT(*)
----------
      2877

SQL> col segment for a10
SQL> select segment_name,file_id,block_id from dba_extents where segment_name='BBED';

SEGMENT_NAME       FILE_ID   BLOCK_ID
--------------- ---------- ----------
BBED                     8        128
BBED                     8        136
BBED                     8        144
BBED                     8        152
BBED                     8        160
BBED                     8        168
BBED                     8        176
BBED                     8        184
BBED                     8        192
BBED                     8        200
BBED                     8        208
BBED                     8        216
BBED                     8        224
BBED                     8        232

14 rows selected.

SQL>

2.使用bbed產(chǎn)生壞塊：
2.1 生成filelist文件：
SQL> select file#||' '||name||' '||bytes from v$datafile ;

FILE#||''||NAME||''||BYTES
-----------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/DBdb/system01.dbf 2936012800
2 /u01/app/oracle/oradata/DBdb/sysaux01.dbf 723517440
3 /u01/app/oracle/oradata/DBdb/undotbs01.dbf 2710568960
4 /u01/app/oracle/oradata/DBdb/users01.dbf 3207331840
5 /u01/app/oracle/oradata/DBdb/example01.dbf 355205120
6 /u01/app/oracle/oradata/DBdb/tbs.dbf 12582912
7 /u01/app/oracle/oradata/DBdb/tbs_tmp.dbf 12582912
8 /u01/app/oracle/oradata/DBdb/test01.dbf 31457280

8 rows selected.

2.2 粘貼上述需要的內(nèi)容到filelist記錄文件，如下    
[oracle@wang ~]$ cat filelist.txt
6 /u01/app/oracle/oradata/DBdb/tbs.dbf 12582912
7 /u01/app/oracle/oradata/DBdb/tbs_tmp.dbf 12582912
8 /u01/app/oracle/oradata/DBdb/test01.dbf 31457280

2.3 編輯bbed的parfile文件，如下：
[oracle@wang ~]$ cat bbed.par
blocksize=8192
listfile=/home/oracle/filelist.txt
mode=edit
[oracle@wang ~]$
[oracle@wang ~]$  

2.4 使用parfile文件進(jìn)行bbed界面：
[oracle@wang lib]$ bbed parfile=/home/oracle/bbed.par
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Feb 1 12:58:16 2018

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> show
        FILE#           6
        BLOCK#          1
        OFFSET          0
        DBA             0x01800001 (25165825 6,1)
        FILENAME        /u01/app/oracle/oradata/DBdb/tbs.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

--轉(zhuǎn)換到file 8：

BBED> set file 8
        FILE#           8

BBED> show  
        FILE#           8
        BLOCK#          1
        OFFSET          0
        DBA             0x02000001 (33554433 8,1)
        FILENAME        /u01/app/oracle/oradata/DBdb/test01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED>

2.5 回滾誤操作：如果操作中發(fā)生誤操作，可以使用revert命令回滾
BBED> help modify
MODIFY[/x|d|u|o|c] numeric/character string
      [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]

BBED> modify /x 0x02000001
 File: /u01/app/oracle/oradata/DBdb/test01.dbf (8)
 Block: 1                Offsets:    0 to  511           Dba:0x02000001
------------------------------------------------------------------------
 02000001 01000002 00000000 00000104 bc1d0000 00000000 0004200b 440badc3
 44424442 00000000 142e0000 000f0000 00200000 08000300 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 5a683a00 00000000 1e77a239 c7912c38 06200e00 00000000 00000000
 00000000 00000000 00000400 02000000 00000000 01000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 09000000 04005445 53540000 00000000 00000000
 00000000 00000000 00000000 00000000 08000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 7ac92131 01000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 5b683a00 00000000 1f77a239 01000000 50030000 91150000 10000000

 <32 bytes per line>

BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/u01/app/oracle/oradata/DBdb/test01.dbf', block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y


2.6 改寫數(shù)據(jù)塊(模擬壞塊)
BBED> modify 1000 file 8 block 144
 File: /u01/app/oracle/oradata/DBdb/test01.dbf (8)
 Block: 144              Offsets:    0 to  511           Dba:0x02000090
------------------------------------------------------------------------
 03e80000 90000002 87693a00 00000204 16cc0000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 04000000 ffffffff 00000000 01000000 10000000
 02000100 00000000 00000000 00000000 00000000 10000000 00000000 00000000
 00000000 01001c00 2e350000 02000000 81000002 01000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 2c5f0100 08200e00 00000000 90000002 08000000 00000000 98000002 08000000
 08000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 11111111 11111111 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

2.6 檢查數(shù)據(jù)塊損壞
使用verify命令，可以發(fā)現(xiàn)剛才修改的file 8 block 144已經(jīng)被標(biāo)記為損壞。
BBED> help verify
VERIFY [ DBA | FILE | FILENAME | BLOCK ]

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/DBdb/test01.dbf
BLOCK = 144

Block 144 is corrupt
Corrupt block relative dba: 0x02000090 (file 0, block 144)
Bad header found during verification
Data in bad block:
 type: 3 format: 0 rdba: 0x02000090
 last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x69872002
 check value in block header: 0xcc16
 computed block checksum: 0x4a23


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED>

2.7 使用dbv工具驗(yàn)證是否有壞塊
[oracle@wang lib]$ dbv file=/u01/app/oracle/oradata/DBdb/test01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Thu Feb 1 13:09:03 2018

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DBdb/test01.dbf
Page 144 is marked corrupt
Corrupt block relative dba: 0x02000090 (file 8, block 144)
Bad header found during dbv:
Data in bad block:
 type: 3 format: 0 rdba: 0x02000090
 last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x69872002
 check value in block header: 0xcc16
 computed block checksum: 0x4a23



DBVERIFY - Verification complete

Total Pages Examined         : 3840
Total Pages Processed (Data) : 100
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 135
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3604
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3828105 (0.3828105)
[oracle@wang lib]$

3. 數(shù)據(jù)塊損壞，需要恢復(fù)。
3.1 在這種情況下，如果有備份，需要從備份中恢復(fù)；如果沒(méi)有備份，那么壞塊部分的數(shù)據(jù)庫(kù)就要丟失了。檢查損壞的對(duì)象：
SELECT owner, segment_name, segment_type, relative_fno, tablespace_name
  FROM dba_extents
 WHERE file_id = &AFN
   and &BL between block_id AND block_id + blocks - 1;

SQL> SELECT owner, segment_name, segment_type, relative_fno, tablespace_name
  2    FROM dba_extents
  3   WHERE file_id = &AFN
  4     and &BL between block_id AND block_id + blocks - 1;
Enter value for afn: 8
old   3:  WHERE file_id = &AFN
new   3:  WHERE file_id = 8
Enter value for bl: 144
old   4:    and &BL between block_id AND block_id + blocks - 1
new   4:    and 144 between block_id AND block_id + blocks - 1

OWNER                          SEGMENT_NAME    SEGMENT_TYPE       RELATIVE_FNO TABLESPACE_NAME
------------------------------ --------------- ------------------ ------------ ------------------------------
SYS                            BBED            TABLE                         8 TEST


3.2 設(shè)置event10231事件
如果損失的是數(shù)據(jù)，可以設(shè)置內(nèi)部事件，使得全表掃描跳過(guò)那些損壞的block

SQL> alter system set events='10231 trace name context forever,level 10';

3.3 將該用戶下的數(shù)據(jù)導(dǎo)出，刪除后，再重建該表，
導(dǎo)出數(shù)據(jù)： exp system/oracle file=bbed.dmp log=bbed.log tables=sys.bbed
[oracle@wang ~]$ exp system/oracle file=bbed.dmp log=bbed.log tables=sys.bbed

Export: Release 11.2.0.4.0 - Production on Thu Feb 1 13:38:13 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                           BBED       2877 rows exported
Export terminated successfully without warnings.
[oracle@wang ~]$

--刪除表
SQL> drop table bbed purge;

Table dropped.

SQL>


--執(zhí)行導(dǎo)入：
imp \'\/ as sysdba\' file=bbed.dmp log=bbedimp.log TABLES=bbed

[oracle@wang ~]$ imp \'\/ as sysdba\' file=bbed.dmp log=bbedimp.log TABLES=bbed

Import: Release 11.2.0.4.0 - Production on Thu Feb 1 13:49:48 2018

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


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

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYS
. importing SYS's objects into SYS
. . importing table                         "BBED"       2877 rows imported
Import terminated successfully without warnings.
[oracle@wang ~]$

--查詢：
SQL> select count(*) from bbed;

  COUNT(*)
----------
      2877

3.4 取消10231事件：
SQL> alter system set events='10231 trace name context off';

System altered.

3.5 驗(yàn)證，不存在壞塊：
RMAN> validate datafile 8;

Starting validate at 01-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00008 name=/u01/app/oracle/oradata/DBdb/test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              3601         3840            3830998   
  File Name: /u01/app/oracle/oradata/DBdb/test01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              103             
  Index      0              0               
  Other      0              136             

Finished validate at 01-FEB-18

RMAN>

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手