*._corrupted_rollback_segments='_SYSSMU1_4115952380$','_SYSSMU2_3882698531$','_SYSSMU3_1780844141$','_SYSSMU4_1137450214$','_SYSSMU5_2972601029$','_SYSSMU6_2318781079$','_SYSSMU7_1865616030$','_SYSSMU8_4279519761$','_SYSSMU9_1551968587$','_SYSSMU10_2324134815$','_SYSSMU11_2069826877$','_SYSSMU12_2242918609$','_SYSSMU13_811223436$','_SYSSMU14_1093125402$','_SYSSMU15_2825991097$','_SYSSMU16_252471872$','_SYSSMU17_3347133763$','_SYSSMU18_1765883319$','_SYSSMU19_1005333767$','_SYSSMU20_3293637928$','_SYSSMU21_3641740596$','_SYSSMU22_3421614834$','_SYSSMU23_138031739$'
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 515903032 bytes
Database Buffers 264241152 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
创建新的UNDO表空间
create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;
删除旧的UNDO表空间
drop tablespace UNDOTBS1 including contents and datafiles;
修改参数文件
*.undo_tablespace='UNDOTBS2'
*.undo_management=AUTO
#*.undo_tablespace='SYSTEM'
#*.undo_management='MANUAL'
#*._corrupted_rollback_segments='_SYSSMU1_4115952380$','_SYSSMU2_3882698531$','_SYSSMU3_1780844141$','_SYSSMU4_1137450214$','_SYSSMU5_2972601029$','_SYSSMU6_2318781079$','_SYSSMU7_1865616030$','_SYSSMU8_4279519761$','_SYSSMU9_1551968587$','_SYSSMU10_2324134815$','_SYSSMU11_2069826877$','_SYSSMU12_2242918609$','_SYSSMU13_811223436$','_SYSSMU14_1093125402$','_SYSSMU15_2825991097$','_SYSSMU16_252471872$','_SYSSMU17_3347133763$','_SYSSMU18_1765883319$','_SYSSMU19_1005333767$','_SYSSMU20_3293637928$','_SYSSMU21_3641740596$','_SYSSMU22_3421614834$','_SYSSMU23_138031739$'
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 515903032 bytes
Database Buffers 264241152 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
BBED修改数据块是比较危险的操作,如果某个修改操作有误,可以通过revert或undo命令回退BBED的修改操作;
例如:BBED回退3,280块上所有修改
BBED> revert dba 3,280
All changes made to this block will be rolled back. Proceed? (Y/N) y
Reverted file '/u02/app/oracle/oradata/orcl11/undotbs01.dbf', block 280
BBED> sum apply;
Check value for File 3, Block 280:
current = 0x3f90, required = 0x3f90
UNDO非段头(文件头)块损坏测试与修复
undo非段头(文件头)损坏,数据库可以正常启动,在没有备份的情况下,可以通过alert报错信息,找到并删除受损的回滚段
SQL> insert into t values(1); -----插入一条数据,不提交
SQL> select usn,status,xacts from v$rollstat;
USN STATUS XACTS
---------- --------------- ----------
0 ONLINE 0
8 ONLINE 0
9 ONLINE 1 ----9号回滚段存在活动事物
10 ONLINE 0
11 ONLINE 0
12 ONLINE 0
24 ONLINE 0
25 ONLINE 0
26 ONLINE 0
27 ONLINE 0
28 ONLINE 0
11 rows selected.
---查看回滚段头块位置
SQL> SET LINE 100
SQL> col segment_name for a30
SQL> select segment_name,header_file,header_block from dba_segments where segment_name like '_SYSSMU%' order by 3;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
_SYSSMU8_4161384913$ 3 8
_SYSSMU9_1458183674$ 3 24
_SYSSMU10_2644453179$ 3 40
_SYSSMU11_4737420$ 3 56
_SYSSMU12_392022772$ 3 72
_SYSSMU24_4044825012$ 3 88
_SYSSMU25_2098992521$ 3 104
_SYSSMU26_2158116475$ 3 120
_SYSSMU27_4048022843$ 3 136
_SYSSMU28_1413754230$ 3 152
10 rows selected.
通过BBED工具,手动破坏9号回滚段非头块;
[oracle11@primary ~]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sat Aug 13 22:35:38 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> copy dba 1,1 to dba 3,25
BBED> sum apply;
Check value for File 3, Block 25:
current = 0xae9a, required = 0xae9a
BBED> verify
DBVERIFY - Verification starting
FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf
BLOCK = 25
Block 25 is corrupt
Corrupt block relative dba: 0x00400019 (file 3, block 25)
Bad header found during verification
Data in bad block:
type: 11 format: 2 rdba: 0x00400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000b01
check value in block header: 0xae9a
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 : 0
Message 531 not found; product=RDBMS; facility=BBED
[oracle11@primary orcl11]$ dbv file=undotbs01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Aug 17 11:39:35 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u02/app/oracle/oradata/orcl11/undotbs01.dbf
Page 25 is marked corrupt
Corrupt block relative dba: 0x00c00019 (file 3, block 25)
Bad header found during dbv:
Data in bad block:
type: 11 format: 2 rdba: 0x00400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000b01
check value in block header: 0xae9a
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 208
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 88
Total Pages Processed (Seg) : 10
Total Pages Failing (Seg) : 0
Total Pages Empty : 119
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1570655 (0.1570655)
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 515903032 bytes
Database Buffers 264241152 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened.
数据库可以正常启动,后台alert日志也没有报错,通过dbv或者bbed工具检查出坏块后,可以手动删除坏块对应的undo段:
(1):select * from dba_extents where file_id=xx and xxx between block_id and block_id+blocks-1;
(2):DROP ROLLBACK SEGMENT rollback_segment;
或者直接新建UNDO表空间:
(1):创建新的UNDO表空间
create undo tablespace UNDOTBS2 datafile '/u02/app/oracle/oradata/orcl11/undotbs02.dbf' size 1M autoextend on;
(2):删除旧的UNDO表空间
drop tablespace UNDOTBS1 including contents and datafiles;
UNDO文件头块损坏测试与修复
UNDO文件头损坏,无法正常open数据库;
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 515903032 bytes
Database Buffers 264241152 bytes
Redo Buffers 2596864 bytes
Database mounted.
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/u02/app/oracle/oradata/orcl11/undotbs01.dbf'
ORA-01210: data file header is media corrupt
在没有备份的情况下,需要通过BBED工具进行修复损坏的文件头;
修复的方式是通过复制其他数据文件头,并手动修改文件头中相关信息;
1、修改数据的DBA,rdba_kcbh
2、修改文件的大小,kccfhfsz
3、修改文件号,kccfhfno
4、修改文件创建时SCN,kcvfhcrs
5、修改文件创建时间,kcvfhcrt
6、修改表空间号,kcvfhtsn
7、修改相对文件号,kcvfhrfn
8、修改表空间的名称, kcvfhtnm
9、修改表空间的长度,kcvfhtln
10、修改检查点的SCN,kcvfhckp
11、修改检查点的时间,kcvcptim
12、修改检查点的计数器,kcvfhcpc
13、修改检查点的控制文件备份的计数器, kcvfhccc
14、如果你修改是1号文件的1号块,他的root rdba的地针是指向了bootstrap$