系统环境：
操作系统：RedHat EL55
Oracle： Oracle 11gR2
Oracle 9i后，采用了undo tablespace管理undo数据，实现undo的自动管理，本案例演示了undo表空间被破坏后如何恢复；如果有备份，通过备份恢复非常容易，但在没有备份的情况下，就需要采用非常规手段来恢复了，呵呵。
1、案例应用环境
undo表空间undo segments：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
14
:
34
:
44
SYS@ prod>select segment_name,segment_type,tablespace_name
from
dba_segments
14
:
35
:
03
2
where
tablespace_name=
'UNDOTBS1'
;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ ------------------------------
_SYSSMU10_3550978943$ TYPE2 UNDO UNDOTBS1
_SYSSMU9_1424341975$ TYPE2 UNDO UNDOTBS1
_SYSSMU8_2012382730$ TYPE2 UNDO UNDOTBS1
_SYSSMU7_3286610060$ TYPE2 UNDO UNDOTBS1
_SYSSMU6_2443381498$ TYPE2 UNDO UNDOTBS1
_SYSSMU5_1527469038$ TYPE2 UNDO UNDOTBS1
_SYSSMU4_1152005954$ TYPE2 UNDO UNDOTBS1
_SYSSMU3_2097677531$ TYPE2 UNDO UNDOTBS1
_SYSSMU2_2232571081$ TYPE2 UNDO UNDOTBS1
_SYSSMU1_3780397527$ TYPE2 UNDO UNDOTBS1
10
rows selected.
Elapsed:
00
:
00
:
00.19
|
模拟应用环境：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
14
:
43
:
16
SYS@ prod>select count(*)
from
scott.emp1;
COUNT(*)
----------
1
Elapsed:
00
:
00
:
00.06
14
:
43
:
54
SYS@ prod>
insert
into
scott.emp1 select *
from
scott.emp;
14
rows created.
Elapsed:
00
:
00
:
00.08
14
:
44
:
04
SYS@ prod>commit;
Commit complete.
Elapsed:
00
:
00
:
00.03
14
:
44
:
06
SYS@ prod>select count(*)
from
scott.emp1;
COUNT(*)
----------
15
Elapsed:
00
:
00
:
00.01
14
:
44
:
20
SYS@ prod>
insert
into
scott.emp1 select *
from
scott.emp
where
rownum <
4
;
3
rows created.
Elapsed:
00
:
00
:
00.03
14
:
44
:
40
SYS@ prod>select count(*)
from
scott.emp1;
COUNT(*)
----------
18
Elapsed:
00
:
00
:
00.01
关库前，事务未提交！
|
开启新的会话，模拟断电，将数据库非正常关闭：
[oracle@rh6 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 14:46:11 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
14:46:12 SYS@ prod>shutdown abort;
ORACLE instance shut down.
Undo tablesapce datafile被破坏：
[oracle@rh6 dbs]$ rm /u01/app/oracle/oradata/prod/undotbs01.dbf
2、Open database时数据库报错：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
[oracle@rh6 prod]$ sqlplus
'/as sysdba'
SQL*Plus: Release
11.2.
0.1.
0
Production
on
Thu Jun
26
14
:
47
:
26
2014
Copyright (c)
1982
,
2009
, Oracle. All rights reserved.
Connected to an idle instance.
14
:
47
:
26
SYS@ prod>startup
ORACLE instance started.
Total System Global Area
835104768
bytes
Fixed Size
2217952
bytes
Variable Size
775948320
bytes
Database Buffers
54525952
bytes
Redo Buffers
2412544
bytes
Database mounted.
ORA
-01157
: cannot identify/lock data file
3
- see DBWR trace file
ORA
-01110
: data file
3
:
'/u01/app/oracle/oradata/prod/undotbs01.dbf'
告警日志：
ALTER DATABASE OPEN
Errors
in
file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_dbw0_7481.trc:
ORA
-01157
: cannot identify/lock data file
3
- see DBWR trace file
ORA
-01110
: data file
3
:
'/u01/app/oracle/oradata/prod/undotbs01.dbf'
ORA
-27037
: unable to obtain file status
Linux-x86_64 Error:
2
: No such file
or
directory
Additional information:
3
Errors
in
file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_7503.trc:
ORA
-01157
: cannot identify/lock data file
3
- see DBWR trace file
ORA
-01110
: data file
3
:
'/u01/app/oracle/oradata/prod/undotbs01.dbf'
ORA
-1157
signalled during: ALTER DATABASE OPEN...
14
:
47
:
37
SYS@ prod>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer
900
undo_tablespace string UNDOTBS1
14
:
48
:
18
SYS@ prod>
|
3、在没有备份的情况下进行修复：
创建pfile：
14:48:18 SYS@ prod>create pfile from spfile;
File created.
Elapsed: 00:00:00.09
查看undo tablespace使用的undo segments：
[oracle@rh6 dbs]$ cd /u01/app/oracle/oradata/prod/
[oracle@rh6 prod]$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
and substr(drs.segment_name,1,7) != '_SYSSMU'
D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU10_3550978943
_SYSSMU10_3904554333
_SYSSMU11_286947212
_SYSSMU12_3068564564
_SYSSMU13_2761193625
_SYSSMU1_3780397527
_SYSSMU14_2421411996
_SYSSMU15_1683924174
_SYSSMU16_2313212396
_SYSSMU17_2041439332
_SYSSMU1_783380902
_SYSSMU18_2800789714
_SYSSMU19_53723967
_SYSSMU20_3850939844
_SYSSMU2_2232571081
_SYSSMU2_3138176977
_SYSSMU3_1645411166
_SYSSMU3_2097677531
_SYSSMU4_1152005954
_SYSSMU4_870421980
_SYSSMU5_1527469038
_SYSSMU5_2525172762
_SYSSMU6_2443381498
_SYSSMU6_3753507049
_SYSSMU7_1260614213
_SYSSMU7_3286610060
_SYSSMU8_2012382730
_SYSSMU8_2806087761
_SYSSMU9_1424341975
_SYSSMU9_973944058
注意：通过system01.dbf查出了，正在使用的undo segment，以上按使用时间做了排序，注意只选择那些排在最前面的（相同回滚段）；默认每个undo tablespace 会应用10个undo segments。
编辑pfile，添加Oracle隐含参数，跳过undo segments的检测：
[oracle@rh6 dbs]$ cat initprod.ora
*.undo_tablespace='SYSTEM'
*.undo_management='MANUAL'
*._corrupted_rollback_segments=(_SYSSMU10_3550978943$,_SYSSMU9_1424341975$,_SYSSMU8_2012382730$,_SYSSMU7_3286610060$,_SYSSMU6_2443381498$,_SYSSMU5_1527469038$,_SYSSMU4_1152005954$,_SYSSMU3_2097677531$,_SYSSMU2_2232571081$,_SYSSMU1_3780397527$)
*._allow_resetlogs_corruption=true
通过pfile启动instance：
14:56:33 SYS@ prod>startup force pfile=$ORACLE_HOME/dbs/initprod.ora
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 775948320 bytes
Database Buffers 54525952 bytes
Redo Buffers 2412544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/prod/undotbs01.dbf'
14:56:52 SYS@ prod>alter database datafile 3 offline drop;
Database altered.
14:57:11 SYS@ prod>alter database open;
Database altered.
Elapsed: 00:00:05.25
14:57:23 SYS@ prod>
告警日志：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
alter database open
Beginning crash recovery of
1
threads
Started redo scan
Completed redo scan
read
157
KB redo,
43
data blocks need recovery
Started redo application
at
Thread
1
: logseq
3
, block
451
Recovery of Online Redo Log: Thread
1
Group
3
Seq
3
Reading mem
0
Mem#
0
: /dsk1/oradata/prod/redo03a.log
Mem#
1
: /dsk2/oradata/prod/redo03b.log
Completed redo application of
0.
04MB
Completed crash recovery
at
Thread
1
: logseq
3
, block
766
, scn
1878512
43
data blocks read,
43
data blocks written,
157
redo k-bytes read
Thu Jun
26
14
:
57
:
19
2014
LGWR: STARTING ARCH PROCESSES
Thu Jun
26
14
:
57
:
19
2014
ARC0 started
with
pid=
20
, OS id=
7638
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread
1
advanced to log sequence
4
(thread open)
Thu Jun
26
14
:
57
:
20
2014
ARC1 started
with
pid=
21
, OS id=
7641
Thread
1
opened
at
log sequence
4
Current log#
1
seq#
4
mem#
0
: /dsk1/oradata/prod/redo01a.log
Current log#
1
seq#
4
mem#
1
: /dsk2/oradata/prod/redo01b.log
Successful open of redo thread
1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is
not
set
Thu Jun
26
14
:
57
:
20
2014
SMON: enabling cache recovery
Thu Jun
26
14
:
57
:
21
2014
ARC2 started
with
pid=
22
, OS id=
7643
ARC1: Archival started
ARC2: Archival started
Thu Jun
26
14
:
57
:
21
2014
ARC2: Becoming the
'no FAL'
ARCH
ARC2: Becoming the
'no SRL'
ARCH
ARC1: Becoming the heartbeat ARCH
ARC3 started
with
pid=
23
, OS id=
7645
Archived Log entry
31
added
for
thread
1
sequence
3
ID
0xfaf1e60
dest
2
:
Thu Jun
26
14
:
57
:
21
2014
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)
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Starting background process QMNC
Thu Jun
26
14
:
57
:
23
2014
QMNC started
with
pid=
24
, OS id=
7647
Completed: alter database open
Thu Jun
26
14
:
57
:
26
2014
Starting background process CJQ0
Thu Jun
26
14
:
57
:
26
2014
CJQ0 started
with
pid=
27
, OS id=
7661
|
4、创建新的undo tablespace
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
14
:
59
:
27
SYS@ prod>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer
900
undo_tablespace string SYSTEM
14
:
59
:
34
SYS@ prod>create undo tablespace undotbs2
14
:
59
:
53
2
datafile
'/u01/app/oracle/oradata/prod/undotbs2.dbf'
size 100m
15
:
00
:
09
3
autoextend
on
;
15
:
01
:
28
SYS@ prod>create spfile
from
pfile;
File created.
Elapsed:
00
:
00
:
00.12
15
:
02
:
23
SYS@ prod>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
15
:
02
:
39
SYS@ prod>startup
ORACLE instance started.
Total System Global Area
835104768
bytes
Fixed Size
2217952
bytes
Variable Size
775948320
bytes
Database Buffers
54525952
bytes
Redo Buffers
2412544
bytes
Database mounted.
Database opened.
15
:
03
:
36
SYS@ prod>alter system set undo_management=auto scope=spfile;
System altered.
Elapsed:
00
:
00
:
00.09
15
:
03
:
49
SYS@ prod>alter system set undo_tablespace=undotbs2 scope=spfile;
System altered.
Elapsed:
00
:
00
:
00.04
15
:
04
:
09
SYS@ prod>startup force;
ORACLE instance started.
Total System Global Area
835104768
bytes
Fixed Size
2217952
bytes
Variable Size
775948320
bytes
Database Buffers
54525952
bytes
Redo Buffers
2412544
bytes
Database mounted.
Database opened.
15
:
04
:
28
SYS@ prod>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer
900
undo_tablespace string UNDOTBS2
15
:
04
:
36
SYS@ prod>select usn,name
from
v$rollname;
USN NAME
---------- ------------------------------
0
SYSTEM
21
_SYSSMU21_2312338076$
22
_SYSSMU22_3375463809$
23
_SYSSMU23_4084707454$
24
_SYSSMU24_386518199$
25
_SYSSMU25_2810228709$
26
_SYSSMU26_2968904537$
27
_SYSSMU27_3269963619$
28
_SYSSMU28_707429450$
29
_SYSSMU29_2754652023$
30
_SYSSMU30_1737877121$
11
rows selected.
Elapsed:
00
:
00
:
00.05
15
:
04
:
44
SYS@ prod>create pfile
from
spfile;
File created.
将隐含参数从pfile删除：
[oracle@rh6 dbs]$ cat initprod.ora
prod.__db_cache_size=
16777216
prod.__oracle_base=
'/u01/app/oracle'
#ORACLE_BASE set
from
environment
#*._allow_resetlogs_corruption=
true
#*._corrupted_rollback_segments=
'_SYSSMU10_3550978943$'
,
'_SYSSMU9_1424341975$'
,
'_SYSSMU8_2012382730$'
,
'_SYSSMU7_3286610060$'
,
'_SYSSMU6_2443381498$'
,
'_SYSSMU5_1527469038$'
,
'_SYSSMU4_1152005954$'
,
'_SYSSMU3_2097677531$'
,
'_SYSSMU2_2232571081$'
,
'_SYSSMU1_3780397527$'
*.audit_file_dest=
'/u01/app/oracle/admin/prod/adump'
*.audit_trail=
'db'
*.compatible=
'11.2.0.0.0'
*.control_files=
'/u01/app/oracle/oradata/prod/control01.ctl'
,
'/dsk1/oradata/prod/control02.ctl'
#Restore Controlfile
*.db_16k_cache_size=
25165824
*.db_block_size=
8192
*.db_cache_size=
16777216
*.db_domain=
''
*.db_keep_cache_size=
0
*.db_name=
'prod'
*.db_recycle_cache_size=
12582912
*.diagnostic_dest=
'/u01/app/oracle'
*.dispatchers=
'(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.log_archive_dest_1=
'location=/dsk4/arch_prod'
*.log_archive_dest_2=
'location=/dsk4/arch1'
*.log_archive_dest_state_1=
'DEFER'
*.log_archive_format=
'arch_%t_%s_%r.log'
*.memory_target=
0
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=
300
*.processes=
150
*.remote_login_passwordfile=
'EXCLUSIVE'
*.sga_max_size=
838860800
*.shared_pool_reserved_size=
12582912
*.shared_pool_size=
200886080
*.undo_management=
'AUTO'
*.undo_tablespace=
'UNDOTBS2'
重新创建spfile：
15
:
04
:
44
SYS@ prod>create spfile
from
pfile;
File created.
|
@至此，undo tablespace恢复完毕！
