SRE空间
Oracle不能启动，报错：
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7601.trc (incident=53018):
ORA-01578: ORACLE data block corrupted (file # 6, block # 208)
ORA-01110: data file 6: ‘/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf’
Incident details in: /oracle/diag/rdbms/orcl/orcl/incident/incdir_53018/orcl_ora_7601_i53018.trc
Incremental checkpoint up to RBA [0x3f.3.0], current log tail at RBA [0x3f.3.0]
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7601.trc:
ORA-01578: ORACLE data block corrupted (file # 6, block # 208)
ORA-01110: data file 6: ‘/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf’
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7601.trc:
ORA-01578: ORACLE data block corrupted (file # 6, block # 208)
ORA-01110: data file 6: ‘/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf’
Error 1578 happened during db open, shutting down database
USER (ospid: 7601): terminating the instance due to error 1578
Sat Jun 21 15:49:21 2014
Sweep [inc][53018]: completed
Instance terminated by USER, pid = 7601
ORA-1092 signalled during: ALTER DATABASE OPEN…
opiodr aborting process unknown ospid (7601) as a result of ORA-1092
Sat Jun 21 15:49:21 2014
ORA-1092 : opitsk aborting process
原因为UNDO回滚段的一个BLOCK损坏，此时需要将损坏的UNDO段找出来，并通过设置Oracle的隐含参数 _corrupted_rollback_segments启动数据库。
关键的一部是找到UNDO段的段名，在11G中国回滚段命名不再按照10G中的_SYSSMUN$(N=1,2,3,4…)方式命名，在11G中命名方式为：
_SYSSMUN_T$（N=1,2,3,4…,T=一个按创建时间转换的时间戳整数），所以在10G中简单的通过设置
_corrupted_rollback_segments=’_SYSSMU1$’,’_SYSSMU2$’,…’_SYSSMUN$’这种枚举方法就可以启动数据库，但在11G中不行。
所以首先需要找到回滚段名。
怎样找到回滚段名呢？
在另一台相同版本的数据库中执行对bootstrap$的查询，会发现UNDO$的创建SQL：
SQL> select * from bootstrap$ where sql_text like ‘%UNDO%’;
CREATE TABLE UNDO$(“US#” NUMBER NOT NULL,”NAME” VARCHAR2(30) NOT NULL,”USER#” NUMBER NOT NULL,”FILE#” NUMBER NOT NULL,”BLOCK#” NUMBER NOT NULL,”SCNBAS” NUMBER,”SCNWRP” NUMBER,”XACTSQN” NUMBER,”UNDOSQN” NUMBER,”INST#” NUMBER,”STATUS$” NUMBER NOT NULL,”TS#” NUMBER,”UGRP#” NUMBER,”KEEP” NUMBER,”OPTIMAL” NUMBER,”FLAGS” NUMBER,”SPARE1″ NUMBER,”SPARE2″ NUMBER,”SPARE3″ NUMBER,”SPARE4″ VARCHAR2(1000),”SPARE5″ VARCHAR2(1000),”SPARE6″ DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 15 EXTENTS (FILE 1 BLOCK 224))
发现UNDO$的段头位于DBA 1，224。
我们可以用BBED查看，
BBED>
BBED>
BBED> set dba 1,224
DBA 0x004000e0 (4194528 1,224)
BBED> map
File: /oracle/oradata/ORCL/datafile/o1_mf_system_9rpzgh24_.dbf (1)
Block: 224 Dba:0x004000e0
————————————————————
Unlimited Data Segment Header
struct kcbh, 20 bytes @0
struct ktech, 72 bytes @20
struct ktemh, 16 bytes @92
struct ktetb[1], 8 bytes @108
struct ktshc, 8 bytes @4148
struct ktsfs_seg[1], 20 bytes @4156
struct ktsfs_txn[16], 320 bytes @4176
ub4 tailchk @8188
可以看到dba 1,224是一个段头，我们可以向下一个block,
BBED> set block +1
BLOCK# 225
BBED> x /*rnc
就可以打印出所有的UNDO段名：
rowdata[6624] @7967
————-
flag@7967: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7968: 0x00
cols@7969: 17
col 0[2] @7970: 3
col 1[20] @7973: _SYSSMU3_1645411166$
col 2[2] @7994: ..
col 3[2] @7997: ..
col 4[3] @8000: ..=
col 5[1] @8004: .
col 6[1] @8006: .
col 7[1] @8008: .
col 8[1] @8010: .
col 9[1] @8012: .
col 10[2] @8014: ..
col 11[2] @8017: ..
col 12[0] @8020: *NULL*
col 13[0] @8021: *NULL*
col 14[0] @8022: *NULL*
col 15[0] @8023: *NULL*
col 16[2] @8024: ..
rowdata[6684] @8027
————-
flag@8027: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8028: 0x00
cols@8029: 17
col 0[2] @8030: 2
col 1[20] @8033: _SYSSMU2_3138176977$
col 2[2] @8054: ..
col 3[2] @8057: ..
col 4[3] @8060: ..-
col 5[1] @8064: .
col 6[1] @8066: .
col 7[1] @8068: .
col 8[1] @8070: .
col 9[1] @8072: .
col 10[2] @8074: ..
col 11[2] @8077: ..
col 12[0] @8080: *NULL*
col 13[0] @8081: *NULL*
col 14[0] @8082: *NULL*
col 15[0] @8083: *NULL*
col 16[2] @8084: ..
rowdata[6744] @8087
————-
flag@8087: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8088: 0x00
cols@8089: 17
col 0[2] @8090: 1
col 1[19] @8093: _SYSSMU1_783380902$
col 2[2] @8113: ..
col 3[2] @8116: ..
col 4[3] @8119: …
col 5[1] @8123: .
col 6[1] @8125: .
col 7[1] @8127: .
col 8[1] @8129: .
col 9[1] @8131: .
col 10[2] @8133: ..
col 11[2] @8136: ..
col 12[0] @8139: *NULL*
col 13[0] @8140: *NULL*
col 14[0] @8141: *NULL*
col 15[0] @8142: *NULL*
col 16[2] @8143: ..
rowdata[6803] @8146
————-
flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8147: 0x00
cols@8148: 17
col 0[1] @8149: 0
col 1[6] @8151: SYSTEM
col 2[1] @8158: .
col 3[2] @8160: ..
col 4[3] @8163: …
col 5[1] @8167: .
col 6[1] @8169: .
col 7[1] @8171: .
col 8[1] @8173: .
col 9[1] @8175: .
col 10[2] @8177: ..
col 11[1] @8180: .
col 12[0] @8182: *NULL*
col 13[0] @8183: *NULL*
col 14[0] @8184: *NULL*
col 15[0] @8185: *NULL*
col 16[1] @8186: .
tailchk @8188
——-
BBED-00210: no row at this offset
设置_corrupted_rollback_segments=’_SYSSMU2_3138176977$’,’_SYSSMU2_3138176977$’,再重启DB。
alter system set “_corrupted_rollback_segments”=’_SYSSMU1_3780397527$’,’_SYSSMU2_3138176977$’,’_SYSSMU3_1645411166$’,’_SYSSMU4_1152005954$’,’_SYSSMU4_870421980$’,’_SYSSMU5_1527469038$’,’_SYSSMU6_2443381498$’,’_SYSSMU6_3753507049$’,’_SYSSMU7_3286610060$’,’_SYSSMU7_1260614213$’,’_SYSSMU8_2012382730$’,’_SYSSMU9_1424341975$’,’_SYSSMU9_973944058$’,’_SYSSMU10_3550978943$’,’_SYSSMU10_3904554333$’,’_SYSSMU11_1189042706$’,’_SYSSMU11_286947212$’,’_SYSSMU12_3068564564$’,’_SYSSMU12_483429471$’,’_SYSSMU13_2761193625$’,’_SYSSMU13_1803514519$’,’_SYSSMU1_783380902$’,’_SYSSMU2_2232571081$’,’_SYSSMU3_2097677531$’,’_SYSSMU5_2525172762$’,’_SYSSMU8_2806087761$’,’_SYSSMU14_3428641269$’,’_SYSSMU14_2421411996$’,’_SYSSMU15_1660868316$’,’_SYSSMU15_1683924174$’,’_SYSSMU16_3285257599$’,’_SYSSMU16_2313212396$’,’_SYSSMU17_2041439332$’,’_SYSSMU17_1843067439$’,’_SYSSMU18_4058605626$’,’_SYSSMU18_2800789714$’,’_SYSSMU20_2408133183$’,’_SYSSMU20_3850939844$’,’_SYSSMU19_660708022$’,’_SYSSMU19_53723967$’ scope=spfile;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL>
SQL>
DB可以起来了，再创建一个新的UNDO表空间替换原来的UNDO表空间。
SQL> create undo tablespace undotbs02;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL> alter system set undo_tablespace=undotbs02 scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL>
S
SQL>
SQL> select segment_name from dba_segments where header_file=6 and header_block=208;
SEGMENT_NAME
——————————————————————————–
_SYSSMU16_3285257599$
SQL>
SQL> show parameter roll
NAME TYPE VALUE
———————————— ———– ——————————
_corrupted_rollback_segments string _SYSSMU1_3780397527$, _SYSSMU2
_3138176977$, _SYSSMU3_1645411
166$, _SYSSMU4_1152005954$, _S
YSSMU4_870421980$, _SYSSMU5_15
27469038$, _SYSSMU6_2443381498
$, _SYSSMU6_3753507049$, _SYSS
MU7_3286610060$, _SYSSMU7_1260
614213$, _SYSSMU8_2012382730$,
_SYSSMU9_1424341975$, _SYSSMU
9_973944058$, _SYSSMU10_355097
8943$, _SYSSMU10_3904554333$,
NAME TYPE VALUE
———————————— ———– ——————————
_SYSSMU11_1189042706$, _SYSSMU
11_286947212$, _SYSSMU12_30685
64564$, _SYSSMU12_483429471$,
_SYSSMU13_2761193625$, _SYSSMU
13_1803514519$, _SYSSMU1_78338
0902$, _SYSSMU2_2232571081$, _
SYSSMU3_2097677531$, _SYSSMU5_
2525172762$, _SYSSMU8_28060877
61$, _SYSSMU14_3428641269$, _S
YSSMU14_2421411996$, _SYSSMU15
_1660868316$, _SYSSMU15_168392
NAME TYPE VALUE
———————————— ———– ——————————
4174$, _SYSSMU16_3285257599$,
_SYSSMU16_2313212396$, _SYSSMU
17_2041439332$, _SYSSMU17_1843
067439$, _SYSSMU18_4058605626$
, _SYSSMU18_2800789714$, _SYSS
MU20_2408133183$, _SYSSMU20_38
50939844$, _SYSSMU19_660708022
$, _SYSSMU19_53723967$
_offline_rollback_segments string _SYSSMU1$, _SYSSMU2$, _SYSSMU3
$, _SYSSMU4$, _SYSSMU5$, _SYSS
MU6$, _SYSSMU7$, _SYSSMU8$, _S
NAME TYPE VALUE
———————————— ———– ——————————
YSSMU9$, _SYSSMU11$
fast_start_parallel_rollback string LOW
rollback_segments string SYSTEM
transactions_per_rollback_segment integer 5
SQL> alter system set “_offline_rollback_segments”=” scope=spfile;
System altered.
将_corrupted_rollback_segments设置为损坏的UNDO段，也可启动DB
SQL> alter system set “_corrupted_rollback_segments”=’_SYSSMU16_3285257599$’ scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL>
7 rows selected.
SQL> alter system set undo_tablespace=UNDOTBS2;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
将损坏的UNDO段从_corrupted_rollback_segments中拿掉，DB启动不了。
SQL> alter system set “_corrupted_rollback_segments”=” scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01578: ORACLE data block corrupted (file # 6, block # 208)
ORA-01110: data file 6:
‘/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf’
Process ID: 8994
Session ID: 1 Serial number: 5
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
SQL> alter system set “_corrupted_rollback_segments”=’_SYSSMU16_3285257599$’ scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
删除回滚段，将损坏的UNDO段从_corrupted_rollback_segments拿掉，此时可以启动DB。
SQL> drop rollback segment “_SYSSMU16_3285257599$”;
Rollback segment dropped.
SQL> alter system set “_corrupted_rollback_segments”=” scope=spfile;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select segment_name from dba_segments where header_file=6 and header_block=208;
SEGMENT_NAME
——————————————————————————–
6.208
SQL> purge recyclebin;
Recyclebin purged.
SQL> select segment_name from dba_segments where header_file=6 and header_block=208;
SEGMENT_NAME
——————————————————————————–
6.208
SQL> desc undo$
Name Null? Type
—————————————– ——– —————————-
US# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
USER# NOT NULL NUMBER
FILE# NOT NULL NUMBER
BLOCK# NOT NULL NUMBER
SCNBAS NUMBER
SCNWRP NUMBER
XACTSQN NUMBER
UNDOSQN NUMBER
INST# NUMBER
STATUS$ NOT NULL NUMBER
TS# NUMBER
UGRP# NUMBER
KEEP NUMBER
OPTIMAL NUMBER
FLAGS NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE
SQL> set lines 2000
SQL> select * from undo$ where NAME=’6.208′;
no rows selected
SQL>
SQL> select * from undo$ where name like ‘_SYSSMU16_3285257599$’;
US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# UGRP# KEEP OPTIMAL FLAGS SPARE1 SPARE2 SPARE3 SPARE4
———- —————————— ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———-
SPARE5 SPARE6
——————————————————————————————————————————————————————————————————————————————–
16 _SYSSMU16_3285257599$ 1 6 208 1317215 0 4294967295 2 0 1 5 2
SQL> delete undo$ where name like ‘_SYSSMU16_3285257599$’;
1 row deleted.
SQL> commit;
Commit complete.
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> show parameter roll
NAME TYPE VALUE
———————————— ———– ——————————
_corrupted_rollback_segments string
_offline_rollback_segments string
fast_start_parallel_rollback string LOW
rollback_segments string SYSTEM
transactions_per_rollback_segment integer 5
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select segment_name from dba_segments where header_file=6 and header_block=208;
SEGMENT_NAME
———————————————————————————
6.208
SQL> delete seg$ where file#=6 and block#=208;
1 row deleted.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL>
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1378582471
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sat Jun 21 16:41:47 2014
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 71, block 93, scn 1843378
Recovery of Online Redo Log: Thread 1 Group 2 Seq 71 Reading mem 0
Mem# 0: /oracle/oradata/ORCL/onlinelog/o1_mf_2_9rpzmh7z_.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 71, block 93, scn 1863379
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thread 1 advanced to log sequence 72 (thread open)
Thread 1 opened at log sequence 72
Current log# 3 seq# 72 mem# 0: /oracle/oradata/ORCL/onlinelog/o1_mf_3_9rpzmkg6_.log
Successful open of redo thread 1
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 5.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Hex dump of (file 6, block 208) in trace file /oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_9940.trc
Corrupt block relative dba: 0x018000d0 (file 6, block 208)
Bad header found during buffer read
Data in bad block:
type: 38 format: 2 rdba: 0x00c000d0
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000026ff
check value in block header: 0x6029
computed block checksum: 0x0
Reading datafile ‘/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf’ for corruption at rdba: 0x018000d0 (file 6, block 208)
Reread (file 6, block 208) found same corrupt data
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_9940.trc (incident=66180):
ORA-01578: ORACLE data block corrupted (file # 6, block # 208)
ORA-01110: data file 6: ‘/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf’
Incident details in: /oracle/diag/rdbms/orcl/orcl/incident/incdir_66180/orcl_smon_9940_i66180.trc
Incremental checkpoint up to RBA [0x48.3.0], current log tail at RBA [0x48.21.0]
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Trace dumping is performing id=[cdmp_20140621164150]
Starting background process QMNC
Sat Jun 21 16:41:50 2014
QMNC started with pid=21, OS id=10036
Sat Jun 21 16:41:50 2014
Sweep [inc][66180]: completed
Hex dump of (file 6, block 208) in trace file /oracle/diag/rdbms/orcl/orcl/incident/incdir_66180/orcl_m000_10032_i66180_a.trc
Corrupt block relative dba: 0x018000d0 (file 6, block 208)
Bad header found during validation
Data in bad block:
type: 38 format: 2 rdba: 0x00c000d0
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000026ff
check value in block header: 0x6029
computed block checksum: 0x0
Reread of blocknum=208, file=/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf. found same corrupt data
Reread of blocknum=208, file=/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf. found same corrupt data
Reread of blocknum=208, file=/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf. found same corrupt data
Reread of blocknum=208, file=/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf. found same corrupt data
Reread of blocknum=208, file=/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_9s7tftbj_.dbf. found same corrupt data
Completed: ALTER DATABASE OPEN
Starting background process CJQ0
Sat Jun 21 16:41:52 2014
CJQ0 started with pid=22, OS id=10048
还是报错######
SQL> select segment_name from dba_segments where header_file=6 and header_block=208;
SEGMENT_NAME
——————————————————————————–
6.208
SQL> select * from seg$ where file#=6 and block#=208;
FILE# BLOCK# TYPE# TS# BLOCKS EXTENTS INIEXTS
———- ———- ———- ———- ———- ———- ———-
MINEXTS MAXEXTS EXTSIZE EXTPCT USER# LISTS GROUPS
———- ———- ———- ———- ———- ———- ———-
BITMAPRANGES CACHEHINT SCANHINT HWMINCR SPARE1 SPARE2
———— ———- ———- ———- ———- ———-
6 208 3 5 8 1 16
2 32765 8 0 0 0 0
0 0 0 16 4227075
SQL> select segment_name from dba_segments where header_file=6 and header_block=208;
SEGMENT_NAME
——————————————————————————–
6.208
SQL> select * from seg$ where file#=6 and block#=208;
FILE# BLOCK# TYPE# TS# BLOCKS EXTENTS INIEXTS
———- ———- ———- ———- ———- ———- ———-
MINEXTS MAXEXTS EXTSIZE EXTPCT USER# LISTS GROUPS
———- ———- ———- ———- ———- ———- ———-
BITMAPRANGES CACHEHINT SCANHINT HWMINCR SPARE1 SPARE2
———— ———- ———- ———- ———- ———-
6 208 3 5 8 1 16
2 32765 8 0 0 0 0
0 0 0 16 4227075
还是能查到，很奇怪，原来没有提交。
SQL> delete seg$ where file#=6 and block#=208;
1 row deleted.
SQL> commit;
Commit complete.
SQL> startup force
SQL> select * from undo$ where name like ‘_SYSSMU16_3285257599$’;
no rows selected
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 549455864 bytes
Database Buffers 285212672 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL>
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1378631580
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sat Jun 21 16:45:20 2014
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 72, block 102, scn 1863556
Recovery of Online Redo Log: Thread 1 Group 3 Seq 72 Reading mem 0
Mem# 0: /oracle/oradata/ORCL/onlinelog/o1_mf_3_9rpzmkg6_.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 72, block 102, scn 1883557
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thread 1 advanced to log sequence 73 (thread open)
Thread 1 opened at log sequence 73
Current log# 1 seq# 73 mem# 0: /oracle/oradata/ORCL/onlinelog/o1_mf_1_9rpzmfgz_.log
Successful open of redo thread 1
SMON: enabling cache recovery
Incremental checkpoint up to RBA [0x49.3.0], current log tail at RBA [0x49.11.0]
Successfully onlined Undo Tablespace 5.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Jun 21 16:45:22 2014
QMNC started with pid=20, OS id=10262
Starting background process CJQ0
Sat Jun 21 16:45:24 2014
CJQ0 started with pid=22, OS id=10274
Completed: ALTER DATABASE OPEN
此时日志正常了：
SQL> select segment_name from dba_segments where header_file=6 and header_block=208;
no rows selected
SQL> select * from seg$ where file#=6 and block#=208;
no rows selected
SQL>
附dba_segments基表：
select owner, segment_name, partition_name, segment_type, tablespace_name,
header_file, header_block,
decode(bitand(segment_flags, 131072), 131072, blocks,
(decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, blocks), blocks)))*blocksize,
decode(bitand(segment_flags, 131072), 131072, blocks,
(decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, blocks), blocks))),
decode(bitand(segment_flags, 131072), 131072, extents,
(decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_extents(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, extents) , extents))),
initial_extent, next_extent, min_extents, max_extents, pct_increase,
freelists, freelist_groups, relative_fno,
decode(buffer_pool_id, 0, ‘DEFAULT’, 1, ‘KEEP’, 2, ‘RECYCLE’, NULL)
from sys_dba_segs
select NVL(u.name, ‘SYS’), o.name, o.subname,
so.object_type, s.type#,
ts.ts#, ts.name, ts.blocksize,
f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), o.dataobj#
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s,
sys.file$ f
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user# (+)
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
union all
select NVL(u.name, ‘SYS’), un.name, NULL,
decode(s.type#, 1, ‘ROLLBACK’, 10, ‘TYPE2 UNDO’), s.type#,
ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts,
s.maxexts, s.extpct,
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), un.us#
from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
where s.file# = un.file#
and s.block# = un.block#
and s.ts# = un.ts#
and s.ts# = ts.ts#
and s.user# = u.user# (+)
and s.type# in (1, 10)
and un.status$ != 1
and un.ts# = f.ts#
and un.file# = f.relfile#
union all
select NVL(u.name, ‘SYS’), to_char(f.file#) || ‘.’ || to_char(s.block#), NULL,
decode(s.type#, 2, ‘DEFERRED ROLLBACK’, 3, ‘TEMPORARY’,
4, ‘CACHE’, 9, ‘SPACE HEADER’, ‘UNDEFINED’), s.type#,
ts.ts#, ts.name, ts.blocksize,
f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), s.hwmincr
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
and s.user# = u.user# (+)
and s.type# not in (1, 5, 6, 8, 10)
and s.ts# = f.ts#
and s.file# = f.relfile#
未经允许不得转载：SRE空间 » 记一次UNDO块损坏：ORA-01578
评论前必须登录！
注册