UNDO 表空间错误，无法OPEN DATABASE
应用环境： Solaris 10（X86） + Oracle 10g
|
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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
|
错误现象：
数据库在启动时，出现以下错误信息：
Errors
in
file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:39:36 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c
for
failed op 8
Doing block recovery
for
file 2 block 2116 -------------文件2 错误
No block recovery was needed
Tue Dec 13 17:40:05 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:22 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
以下内容选自告警日志：
Starting ORACLE instance (normal)
......
processes = 150
__shared_pool_size = 75497472
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 167772160
control_files = /export/home/oracle/orcl/oradata/cuug/control01.ctl, /export/home/oracle/orcl/oradata/cuug/control02.ctl, /export/home/oracle/orcl/oradata/cuug/control03.ctl
db_block_size = 8192
__db_cache_size = 79691776
compatible = 10.2.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = /export/home/oracle/orcl/flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=cuugXDB)
job_queue_processes = 10
background_dump_dest = /export/home/oracle/orcl/admin/cuug/bdump
user_dump_dest = /export/home/oracle/orcl/admin/cuug/udump
core_dump_dest = /export/home/oracle/orcl/admin/cuug/cdump
audit_file_dest = /export/home/oracle/orcl/admin/cuug/adump
db_name = cuug
open_cursors = 300
pga_aggregate_target = 16777216
......
Completed: ALTER DATABASE MOUNT
Tue Dec 13 17:38:47 2011
ALTER DATABASE RECOVER datafile 2
Tue Dec 13 17:38:48 2011
Media Recovery Start
SMON: enabling cache recovery
ARC2 started
with
pid=18, OS id=1982
Tue Dec 13 17:39:11 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
fTue Dec 13 17:39:22 2011
Successfully onlined Undo Tablespace 1.
Tue Dec 13 17:39:22 2011
SMON: enabling tx recovery
Tue Dec 13 17:39:23 2011
Database Characterset is ZHS16GBK
Tue Dec 13 17:39:32 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:39:36 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c
for
failed op 8
Doing block recovery
for
file 2 block 2116 -------------文件2 错误
No block recovery was needed
Tue Dec 13 17:40:05 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:22 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/udump/cuug_ora_1959.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:22 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c
for
failed op 8
Doing block recovery
for
file 2 block 2116
No block recovery was needed
Tue Dec 13 17:40:53 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/bdump/cuug_smon_1945.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:40:56 2011
DEBUG: Replaying xcb 0x588c40e4, pmd 0x58a54c7c
for
failed op 8
Tue Dec 13 17:40:56 2011
Non-fatal internal error happenned
while
SMON was doing shrinking of rollback segments.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Dec 13 17:40:56 2011
Doing block recovery
for
file 2 block 2116
No block recovery was needed
Tue Dec 13 17:41:32 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/bdump/cuug_pmon_1933.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:41:37 2011
Errors
in
file /export/home/oracle/orcl/admin/cuug/bdump/cuug_pmon_1933.trc:
ORA-00600: internal error code, arguments: [4194], [38], [40], [], [], [], [], []
Tue Dec 13 17:41:37 2011
PMON: terminating instance due to error 472
Instance terminated by PMON, pid = 1933
--------Oracle 内部错误，无法正常open database
启动数据库到mount 状态：
----------查看数据文件，发现file 2 是undo datafile
SQL> select checkpoint_change
# ,file# from v$datafile;
CHECKPOINT_CHANGE
# FILE#
------------------ ----------
785377 1
785377 2
785377 3
785377 4
785377 5
SQL> select checkpoint_change
# ,file# from v$datafile_header;
CHECKPOINT_CHANGE
# FILE#
------------------ ----------
785377 1
785377 2
785377 3
785377 4
785377 5
SQL> col name
for
a50
SQL> select file
# ,name from v$datafile;
FILE
# NAME
---------- --------------------------------------------------
1 /export/home/oracle/orcl/oradata/cuug/system01.dbf
2 /export/home/oracle/orcl/oradata/cuug/undotbs01.dbf
3 /export/home/oracle/orcl/oradata/cuug/sysaux01.dbf
4 /export/home/oracle/orcl/oradata/cuug/users01.dbf
5 /export/home/oracle/orcl/oradata/cuug/example01.dbf
SQL> show parameter undo
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS1
---------设置undo management 为手工方式（manual）
SQL> alter system set undo_management = manual scope=spfile;
System altered.
SQL> alter system set undo_tablespace=
''
scope=spfile;
System altered.
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1279120 bytes
Variable Size 83889008 bytes
Database Buffers 79691776 bytes
Redo Buffers 2912256 bytes
Database mounted.
----------正常打开库
SQL>alter database open;
Database altered.
-----------添加新的undo 表空间
SQL> create undo tablespace rtbs
2 datafile
'/export/home/oracle/orcl/oradata/cuug/rtbs01.dbf'
size 100m;
Tablespace created.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
4 /export/home/oracle/orcl/oradata/cuug/users01.dbf USERS
3 /export/home/oracle/orcl/oradata/cuug/sysaux01.dbf SYSAUX
2 /export/home/oracle/orcl/oradata/cuug/undotbs01.dbf UNDOTBS1
1 /export/home/oracle/orcl/oradata/cuug/system01.dbf SYSTEM
5 /export/home/oracle/orcl/oradata/cuug/example01.dbf EXAMPLE
6 /export/home/oracle/orcl/oradata/cuug/rtbs01.dbf RTBS
6 rows selected.
SQL> alter system set undo_management =auto scope=spfile;
System altered.
SQL> alter system set undo_tablespace = rtbs scope=spfile;
System altered.
----------重新启动数据库，正常后，删除旧的undo tablespace
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
----------重新启动数据库，数据库恢复正常
SQL> startup force
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1279120 bytes
Variable Size 83889008 bytes
Database Buffers 79691776 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
SQL> col file_name
for
a50
SQL> select file_id,file_name ,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
4 /export/home/oracle/orcl/oradata/cuug/users01.dbf USERS
3 /export/home/oracle/orcl/oradata/cuug/sysaux01.dbf SYSAUX
1 /export/home/oracle/orcl/oradata/cuug/system01.dbf SYSTEM
5 /export/home/oracle/orcl/oradata/cuug/example01.dbf EXAMPLE
6 /export/home/oracle/orcl/oradata/cuug/rtbs01.dbf RTBS
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/741124，如需转载请自行联系原作者
