databfile online 和datafile offline
前几天碰到这样一件事情：一个生产库上DATA表空间，其中有20几个文件，但是有一个文件是处于offline drop状态，这个文件在物理上早就被删除了。
将这个表空间offline temporary下来后，就再也无法online起来了，从而业务也无法运行。这个表空间有60个G左右，重新导入数据的话，时间太长了。。。
环境：
oracle 8.1.6.3 32bit
os:aix 4.3.3
数据库大小为170G左右，非归档。
当是为了将这个表空间online，我们试了多种方法，将该数据文件创建回去，offline drop,重建控制文件等，均不成功。最后不得不采用bbed和dul修改该文件的scn号，使的在online时，oracle不恢复该数据文件。这种方法最后是成功了。
事后我想到另外一个思路就是手工修改数据字典，于是就开始了表空间online和offline的研究。。。
通过10046事件跟踪，可以得知oracle在offline过程中做了以下事情：
1.首先oracle会在system表空间中产生一个defered回滚段。
2.将该回滚段的信息插入到seg$基表中。其type为3即临时段。
3.更新ts$基表，将表空间变为offline,且写入defered回滚段的信息（即ts$表中的undofile#和undoblock#字段)。
4.将defered回滚段的类型由type变为save undo类型。
从
trace文件中，只能得出上面几件事情，但是从后面的研究可以得知，在这些过程，oracle还有一个内部触发器，修改了x$ktfbhc基表，将
ktfbhccval字段改为1，这意味着该数据文件是不可用的。这个字段在alter database datafile online和alter
tablespace offline时会被修改。
接着对online做跟踪，可以发现oracle做了以下的事情：
1.更新ts$基表的scnwrp和scnbas字段
2.更新ts$基表的online$字段为1，scnwrp和scnbas字段为0。online$字段为1，表示为online，为2表示offline,为3表示invalid。
3.更新seg$基表，将对应的defered回滚段更新为临时段，即type#改为3
4.更新ts$基表，将undofile#和undoblock#字段更新为0
5.drop defered回滚段
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
根据 这些步骤，我手工做了一个测试：
首先创建一个test表空间：
create tablespace test datafile 'e:1.dbf' size 10M;
alter tablespace test add datafile 'e:2.dbf' size 10M;
然后将e:2.dbf offline drop掉：
alter database datafile 'e:2.dbf' offline drop;
在test表空间中创建一个表test:
create table test tablespace test as select *from dba_tables;
然后做几次切换，将所有的日志都冲掉，以防recover datafile成功。
alter system switch logfile;
然后将表空间test offline temporary:
alter tablespace test offline temporary;
接着试着将test表空间online：
alter tablespace test online;
此时会报出数据文件'e:2.dbf'需要recover，因为没有相应的日志，recover datafile显然不成功。
然后将数据文件'e:1.dbf' online起来：
alter database datafile 'e:1.dbf' online;
然
后手工修改数据字典ts$和seg$。再重起数据库，发现test表空间是online状态,数据文件e:1.dbf也是online状态，但是从
v$datafile的enabled字段中可以看出该数据文件是disabled的，正常应该为read
write的。从dba_data_files视图中可以发现e:1.dbf和e:2.dbf的字节数均为0.该表空间虽然是online的，但是
select * from test;就会报出错误，说数据文件e:1.dbf此时不可读取。
由于v$datafile和
dbA_data_files主要是从x$ktfbhc中获取信息的。查询x$ktfbhc表，发现e:1.dbf和e:2.dbf文件的
ktfbhccval字段为1。也就是说在表空间offline下来后，即使alter database datafile
online也不会修改此字段。如果是数据文件offline下来的话，将数据文件online起来是会修改的。
研究进行到此，停顿了一段时间，一直把焦点放在如何修改x$ktfbhc表上面。但是这张基表，oracle不让修改。
后来我打开logmnr，通过logmnr发现oracle在执行完上面那些步骤后还执行了一下"alter tablespace online的语句。这条语句应该触发了一个内部触发器修改x$ktfbhc表。
我试着再扫执行一遍alter tablespace online。结果依然一样。
我再试着执行了一下alter tablespace offline。结果当然是报错，然后执行alter tablespace online.当然这条语句也是报错。
但是令我惊奇的是，此时数据文件的状态变为read write了。
这时test表空间中的表格也能访问了。
至此修改数据字典将表空间online成功了！
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Apr 12 10:58:27 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> create tablespace hc datafile 'e:1.dbf' size 10M;
Tablespace created.
SQL> alter tablespace hc add datafile 'e:2.dbf'size 10M;
Tablespace altered.
SQL> alter database datafile 'e:2.dbf' offline drop;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> create table test (a number) tablespace hc;
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter tablespace hc offline temporary;
Tablespace altered.
SQL> alter tablespace hc online;
alter tablespace hc online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: 'E:2.DBF'
SQL> recover datafile 7
ORA-00279: change 2154323474 generated at 04/12/2005 10:58:59 needed for threa
1
ORA-00289: suggestion : D:ORACLE9IARCHIVEPRIMARYARC00306.001
ORA-00280: change 2154323474 for thread 1 is in sequence #306
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log 'D:ORACLE9IARCHIVEPRIMARYARC00306.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-00308: cannot open archived log 'D:ORACLE9IARCHIVEPRIMARYARC00306.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
SQL> select *from test;
select *from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'E:1.DBF'
SQL> select ts#,undofile#,undoblock# from ts$;
TS# UNDOFILE# UNDOBLOCK#
---------- ---------- ----------
0 0 0
1 0 0
2 0 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 1 23913
10 0 0
TS# UNDOFILE# UNDOBLOCK#
---------- ---------- ----------
11 0 0
12 1 23865
13 rows selected.
SQL> select ts#,name,undofile#,undoblock# from ts$;
TS# NAME UNDOFILE# UNDOBLOCK#
---------- ------------------------------ ---------- ----------
0 SYSTEM 0 0
1 UNDOTBS1 0 0
2 TEMP 0 0
3 EXAMPLE 0 0
4 TOOLS 0 0
5 USERS 0 0
6 TEST 0 0
7 TEST_TBS 0 0
8 TEST2 0 0
9 TEST3 1 23913
10 AAA 0 0
TS# NAME UNDOFILE# UNDOBLOCK#
---------- ------------------------------ ---------- ----------
11 LOGZGH 0 0
12 HC 1 23865
13 rows selected.
SQL> update ts$ set scnwrp=65536,scnbas=3445667786 where ts#=12;
1 row updated.
SQL> commit;
Commit complete.
SQL> update ts$ set online$=1,scnbas=0,scnwrp=0 where ts#=12;
1 row updated.
SQL> commit;
Commit complete.
SQL> update seg$ set type#=3 where file#=1 and block#=23865;
1 row updated.
SQL> commit;
Commit complete.
SQL> update ts$ set undofile#=0,undoblock#=0 where ts#=12;
1 row updated.
SQL> delete from seg$ where file#=1 and block#=23865;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select *from test;
select *from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'E:1.DBF'
SQL> alter tablespace hc online;
alter tablespace hc online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: 'E:2.DBF'
SQL> select *from test;
select *from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'E:1.DBF'
SQL> alter tablespace hc offline;
alter tablespace hc offline
*
ERROR at line 1:
ORA-01191: file 6 is already offline - cannot do a normal offline
ORA-01110: data file 6: 'E:1.DBF'
SQL> alter tablespace hc online;
alter tablespace hc online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: 'E:2.DBF'
SQL> select *from test;
select *from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'E:1.DBF'
SQL> alter database datafile 6 online;
Database altered.
SQL> select *from test;
A
----------
1
*************************************
为了验证一下做了如下实验：
因为以前我的测试库中就有一个test的表空间
SQL> alter tablespace test add datafile 'd:test2.dbf' size 10m；
SQL>create table test(a number) tablespace test;
SQL> insert into test values(4);
1 row created.
SQL> insert into test values(5);
1 row created.
SQL> insert into test values(6);
1 row created.
SQL> insert into test values(10);
1 row created.
SQL> insert into test values(11);
1 row created.
SQL> insert into test values(12);
1 row created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter tablespace test offline temporary;
SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: 'D:TEST2.DBF'
SQL> recover datafile 8;
ORA-00279: change 4265421 generated at 03/23/2006 11:23:32 needed for thread 1
ORA-00289: suggestion : D:ORACLEORADATAIDDBSVRARCHIVEIDDBSVRT001S01631.AR
ORA-00280: change 4265421 for thread 1 is in sequence #1631
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'D:ORACLEORADATAIDDBSVRARCHIVEIDDBSVRT001S01631.ARC'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
SQL> select ts#,undofile#,undoblock# from ts$;
TS# UNDOFILE# UNDOBLOCK#
---------- ---------- ----------
0 0 0
1 0 0
2 0 0
3 0 0
4 0 0
5 0 0
6 1 12523
7 rows selected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:ORACLEORADATAIDDBSVRTEST.DBF'
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:ORACLEORADATAIDDBSVRTEST.DBF'
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:ORACLEORADATAIDDBSVRTEST.DBF'
SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: 'D:TEST2.DBF'
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:ORACLEORADATAIDDBSVRTEST.DBF'
SQL> update ts$ set scnwrp=65536,scnbas=3445667786 where ts#=6;
1 row updated.
SQL> commit;
Commit complete.
SQL> update ts$ set online$=1,scnbas=0,scnwrp=0 where ts#=6;
1 row updated.
SQL> commit;
Commit complete.
SQL> select type#,file# from seg$ where block#=12523;
TYPE# FILE#
---------- ----------
2 1
SQL> select type#,file# from seg$ where block#=1252;
no rows selected
SQL> select type#,file# from seg$ where block#=12522;
no rows selected
SQL> select type#,file# from seg$ where block#=125242;
no rows selected
SQL> select type#,file# from seg$ where block#=12523;
TYPE# FILE#
---------- ----------
2 1
SQL> update seg$ set type#=3 where file#=1 and block#=12523;
1 row updated.
SQL> commit;
Commit complete.
SQL> update ts$ set undofile#=0,undoblock#=0 where ts#=6;
1 row updated.
SQL> delete from seg$ where file#=1 and block#=12523;
1 row deleted.
SQL> commit;
Commit complete.
SQL> /
Commit complete.
SQL>
SQL> alter tablespace test offline;
alter tablespace test offline
*
ERROR at line 1:
ORA-01191: file 7 is already offline - cannot do a normal offline
ORA-01110: data file 7: 'D:ORACLEORADATAIDDBSVRTEST.DBF'
SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: 'D:TEST2.DBF'
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:ORACLEORADATAIDDBSVRTEST.DBF'
SQL> alter database datafile 7 online;
Database altered.
SQL> select * from test;
A
----------
1
2
2
3
4
5
6
10
11
12
10 rows selected.
SQL>
posted on 2012-05-30 10:19 restService 阅读(564) 评论(0) 编辑 收藏 举报
