設定10231事件並結合匯入匯出工具恢復壞塊
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產生壞塊：
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 貼上上述需要的內容到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檔案進行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
--轉換到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 回滾誤操作：如果操作中發生誤操作，可以使用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 改寫資料塊(模擬壞塊)
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 檢查資料塊損壞
使用verify命令，可以發現剛才修改的file 8 block 144已經被標記為損壞。
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工具驗證是否有壞塊
[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. 資料塊損壞，需要恢復。
3.1 在這種情況下，如果有備份，需要從備份中恢復；如果沒有備份，那麼壞塊部分的資料庫就要丟失了。檢查損壞的物件：
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 設定event10231事件
如果損失的是資料，可以設定內部事件，使得全表掃描跳過那些損壞的block
SQL> alter system set events='10231 trace name context forever,level 10';
3.3 將該使用者下的資料匯出，刪除後，再重建該表，
匯出資料： 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>
--執行匯入：
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 驗證，不存在壞塊：
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>
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產生壞塊：
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 貼上上述需要的內容到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檔案進行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
--轉換到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 回滾誤操作：如果操作中發生誤操作，可以使用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 改寫資料塊(模擬壞塊)
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 檢查資料塊損壞
使用verify命令，可以發現剛才修改的file 8 block 144已經被標記為損壞。
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工具驗證是否有壞塊
[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. 資料塊損壞，需要恢復。
3.1 在這種情況下，如果有備份，需要從備份中恢復；如果沒有備份，那麼壞塊部分的資料庫就要丟失了。檢查損壞的物件：
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 設定event10231事件
如果損失的是資料，可以設定內部事件，使得全表掃描跳過那些損壞的block
SQL> alter system set events='10231 trace name context forever,level 10';
3.3 將該使用者下的資料匯出，刪除後，再重建該表，
匯出資料： 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>
--執行匯入：
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 驗證，不存在壞塊：
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>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2150746/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mongodb的備份恢復與匯出匯入MongoDB
- Oracle中匯出修復資料塊損壞Oracle
- 設定 EVENT 10231 跳過壞塊
- Docker容器中的備份、恢復、遷移、匯入、匯出Docker
- Oracle備份與恢復系列 五 邏輯匯入匯出Oracle
- 【匯入匯出】Oracle 常用匯入匯出工具集錦Oracle
- 使用exp/imp匯出匯入資料（邏輯備份恢復）
- 【匯出匯入】匯出匯入 大物件物件
- sqoop定時增量匯入匯出OOP
- java 匯入匯出Excel工具類ExcelUtilJavaExcel
- exp/imp匯出匯入工具的使用
- Excel/CSV 匯入匯出庫，支援大檔案，樣式設定，單元格合併Excel
- Oracle exp/imp匯出匯入工具的使用Oracle
- 【匯入匯出】sqlldr 匯入案例SQL
- 【匯出匯入】% 在匯入匯出中的應用。
- PostgreSQL邏輯備份恢復--pg_dump匯出及psql匯入案例SQL
- Oracle 資料庫備份與恢復總結－exp/imp (匯出與匯入裝庫與卸庫)Oracle資料庫
- 用GridView匯入匯出Excel,並列印GridViewViewExcel
- Activity 流程模型匯入匯出-activity流程模型匯入匯出模型
- js匯入匯出總結與實踐JS
- oracle 匯出/匯入小結 以防忘記Oracle
- Mysql匯入&匯出MySql
- Mysql匯入匯出MySql
- doris匯入匯出
- esayExcel匯入匯出Excel
- laravel結合maatwebsite/excel包，匯出excel，將部分內容設定不可LaravelWebExcel
- mongodb使用自帶命令工具匯出匯入資料MongoDB
- 【EXP/IMP】使用EXP /IMP工具“模糊”匯出和匯入
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- MySQL資料匯入匯出方法與工具介紹MySql
- vue excel匯入匯出VueExcel
- navlicat 匯入匯出SQLSQL
- mysql 命令匯入匯出MySql
- BCP匯入匯出MsSqlSQL
- mysql匯入匯出慢MySql
- mysql 匯入、匯出命令MySql
- Oracle增量匯入匯出Oracle
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫