oracle坏块，一招教你手把手修复oracle坏块(二)！！！

接着上文，今天我们来模拟一下当碰到UNDO坏块时，可以怎样进行处理。

 

首先还是先来破坏一下undo数据文件~

不知道怎么使用bbed的小伙伴可以先看一下我的上一个博客，里面有bbed的下载方式和使用方法，这里不过多进行阐述

https://blog.csdn.net/weixin_45112130/article/details/113881463

[oracle@mongodb lib]$ ./bbed parfile=par.txt password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Tue Feb 23 11:13:47 2021

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

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

BBED> set dba 3,1
        DBA             0x00c00001 (12582913 3,1)

BBED> map
 File: /oracle/app/oradata/xtts11g/undotbs01.dbf (3)
 Block: 1                                     Dba:0x00c00001
------------------------------------------------------------
 Data File Header

 struct kcvfh, 860 bytes                    @0       

 ub4 tailchk                                @8188    


BBED> m /x ff 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oracle/app/oradata/xtts11g/undotbs01.dbf (3)
 Block: 1                Offsets: 8188 to 8191           Dba:0x00c00001
------------------------------------------------------------------------
 ff0b0000 

 <32 bytes per line>

BBED> sum apply
Check value for File 3, Block 1:
current = 0x6292, required = 0x6292
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/app/oradata/xtts11g/undotbs01.dbf
BLOCK = 1

Block 1 is corrupt
Corrupt block relative dba: 0x00c00001 (file 0, block 1)
Fractured block found during verification
Data in bad block:
 type: 11 format: 2 rdba: 0x00c00001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000bff
 check value in block header: 0x6292
 computed block checksum: 0x0


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           : 2
Message 531 not found;  product=RDBMS; facility=BBED

 触发检查点，看看是不是成功制造了undo坏块

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 42344
Session ID: 18 Serial number: 61

我们看一下alert日志是否有相关的undo报错

Tue Feb 23 11:24:04 2021
Read of datafile '/oracle/app/oradata/xtts11g/undotbs01.dbf' (fno 3) header failed with ORA-01210
Hex dump of (file 3, block 1) in trace file /oracle/app/diag/rdbms/xtts11g/xtts11g/trace/xtts11g_ckpt_36280.trc
Corrupt block relative dba: 0x00c00001 (file 3, block 1)
Fractured block found during datafile header read
Data in bad block:
 type: 11 format: 2 rdba: 0x00c00001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000bff
 check value in block header: 0x6292
 computed block checksum: 0x0
Rereading datafile 3 header failed with ORA-01210
Errors in file /oracle/app/diag/rdbms/xtts11g/xtts11g/trace/xtts11g_ckpt_36280.trc:
ORA-63999: data file suffered media failure
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/oracle/app/oradata/xtts11g/undotbs01.dbf'
ORA-01210: data file header is media corrupt
Errors in file /oracle/app/diag/rdbms/xtts11g/xtts11g/trace/xtts11g_ckpt_36280.trc:
ORA-63999: data file suffered media failure
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/oracle/app/oradata/xtts11g/undotbs01.dbf'
ORA-01210: data file header is media corrupt
CKPT (ospid: 36280): terminating the instance due to error 63999
Tue Feb 23 11:24:05 2021
System state dump requested by (instance=1, osid=36280 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/diag/rdbms/xtts11g/xtts11g/trace/xtts11g_diag_36268_20210223112405.trc
Dumping diagnostic data in directory=[cdmp_20210223112405], requested by (instance=1, osid=36280 (CKPT)), summary=[abnormal instance termination].
Instance terminated by CKPT, pid = 36280

现在进行undo坏块修复

[oracle@mongodb xtts11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 23 11:27:12 2021

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  759943168 bytes
Fixed Size                  2257112 bytes
Variable Size             654315304 bytes
Database Buffers           96468992 bytes
Redo Buffers                6901760 bytes
Database mounted.
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/oracle/app/oradata/xtts11g/undotbs01.dbf'
ORA-01210: data file header is media corrupt


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  759943168 bytes
Fixed Size                  2257112 bytes
Variable Size             654315304 bytes
Database Buffers           96468992 bytes
Redo Buffers                6901760 bytes
Database mounted.
SQL> alter database datafile 3 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> create undo tablespace undotbs2 datafile '/oracle/app/oradata/xtts11g/UNDOTBS2.dbf' size 500M autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_3724004606$' found, terminate
dropping tablespace


SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

STATUS           SEGMENT_NAME
---------------- ------------------------------
NEEDS RECOVERY   _SYSSMU1_3724004606$
NEEDS RECOVERY   _SYSSMU2_2996391332$
NEEDS RECOVERY   _SYSSMU3_1723003836$
NEEDS RECOVERY   _SYSSMU4_1254879796$
NEEDS RECOVERY   _SYSSMU5_898567397$
NEEDS RECOVERY   _SYSSMU6_1263032392$
NEEDS RECOVERY   _SYSSMU7_2070203016$
NEEDS RECOVERY   _SYSSMU8_517538920$
NEEDS RECOVERY   _SYSSMU9_1650507775$
NEEDS RECOVERY   _SYSSMU10_1197734989$

10 rows selected.

SQL> create pfile from spfile;

File created.

SQL> select segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

SEGMENT_NAME
------------------------------
_SYSSMU1_3724004606$
_SYSSMU2_2996391332$
_SYSSMU3_1723003836$
_SYSSMU4_1254879796$
_SYSSMU5_898567397$
_SYSSMU6_1263032392$
_SYSSMU7_2070203016$
_SYSSMU8_517538920$
_SYSSMU9_1650507775$
_SYSSMU10_1197734989$

10 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

这里将数据库打开到open模式使用的是offline数据文件，也可以采取另一种方式也可以打开。 

startup mount

alter system set events '10513 trace name context forever,level 2';


startup force;

编辑pfile，添加隐含参数 

*._allow_resetlogs_corruption=true
#._allow_terminal_recovery_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1_3724004606$ ,_SYSSMU2_2996391332$ ,_SYSSMU3_1723003836$ ,_SYSSMU4_1254879796$ ,_SYSSMU5_898567397$ ,_SYSSMU6_1263032392$ ,_SYSSMU7_2070203016$ ,_SYSSMU8_517538920$ ,_SYSSMU9_1650507775$ ,_SYSSMU10_1197734989$)

SQL> startup pfile='/oracle/app/product/11.2.0/db_1/dbs/initxtts11g.ora';
ORACLE instance started.

Total System Global Area  759943168 bytes
Fixed Size                  2257112 bytes
Variable Size             654315304 bytes
Database Buffers           96468992 bytes
Redo Buffers                6901760 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  759943168 bytes
Fixed Size                  2257112 bytes
Variable Size             654315304 bytes
Database Buffers           96468992 bytes
Redo Buffers                6901760 bytes
Database mounted.
Database opened.

至此，undo坏块修复完成。

下篇文章将介绍oracle业务数据坏块修复方式之无备份恢复。