由于盘柜掉电，导致多个数据文件offline, 包括undo文件的offline.
直接重启操作系统后，数据库能正常打开,但是部分表查询是出错．
下面是生产库的alert log.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thu Aug 06 06:09:26 2009
Errors in file d:\oracle\admin\DB\bdump\DB_ckpt_1480.trc:
ORA-01171: datafile 134 going offline due to error advancing checkpoint
ORA-01110: data file 134: 'F:\DB\UNDO1.DBF'
ORA-01115: IO error reading block from file 134 (block # 1)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1117) The request could not be performed because of an I/O device
error.
.
.
.
Thu Aug 06 08:21:59 2009
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Thu Aug 06 08:21:59 2009
Errors in file d:\oracle\admin\db\bdump\db_smon_1388.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 134 cannot be read at this time
ORA-01110: data file 134: 'E:\db\UNDO1.DBF'
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
模拟undo文件offline，重启后对数据库进行恢复
1)包含有回滚信息的undo tablespace的数据文件offline.
+在session 1 插入两行数据，但不提交．
+Session 1:
SQL> conn lgx/lgx
Connected.
SQL> insert into lgx values(3);
1 row created.
SQL> insert into lgx values(3);
+Session 2:
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ------------------------- ------------------------------
4 K:\PC\USERS01.DBF USERS
3 K:\PC\SYSAUX01.DBF SYSAUX
1 K:\PC\SYSTEM01.DBF SYSTEM
5 K:\PC\MTS01.DBF MS
11 K:\PC\TOOLS.DBF TOOLS
18 L:\PC\SYSAUX02.DBF SYSAUX
19 L:\PC\SYSTEM02.DBF SYSTEM
20 L:\PC\TOOLS1.DBF TOOLS
21 L:\PC\USERS02.DBF USERS
34 M:\PC\SYSAUX03.DBF SYSAUX
35 M:\PC\SYSTEM03.DBF SYSTEM
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ------------------------- ------------------------------
36 M:\PC\TOOLS2.DBF TOOLS
37 M:\PC\USERS03.DBF USERS
40 M:\PC\UNDOTBS2_1.DBF UNDOTBS2
2 K:\PC\UNDOTBS2_2.DBF UNDOTBS2
43 M:\PC\MTS_DEMO_1.DBF DEMO
16 rows selected.
SQL> alter database datafile 4 offline;
Database altered.
SQL> alter database datafile 40 offline;
alter database datafile 40 offline
*
ERROR at line 1:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS2 ONLINE
_SYSSMU2$ UNDOTBS2 ONLINE
_SYSSMU3$ UNDOTBS2 ONLINE
_SYSSMU4$ UNDOTBS2 ONLINE
_SYSSMU5$ UNDOTBS2 ONLINE
_SYSSMU6$ UNDOTBS2 ONLINE
_SYSSMU7$ UNDOTBS2 ONLINE
_SYSSMU8$ UNDOTBS2 ONLINE
_SYSSMU9$ UNDOTBS2 ONLINE
_SYSSMU10$ UNDOTBS2 ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS2 OFFLINE
_SYSSMU12$ UNDOTBS2 OFFLINE
_SYSSMU13$ UNDOTBS2 OFFLINE
_SYSSMU267$ UNDOTBS2 OFFLINE
_SYSSMU268$ UNDOTBS2 OFFLINE
_SYSSMU269$ UNDOTBS2 OFFLINE
_SYSSMU270$ UNDOTBS2 OFFLINE
_SYSSMU271$ UNDOTBS2 OFFLINE
_SYSSMU272$ UNDOTBS2 OFFLINE
_SYSSMU273$ UNDOTBS2 OFFLINE
_SYSSMU274$ UNDOTBS2 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU275$ UNDOTBS2 OFFLINE
_SYSSMU276$ UNDOTBS2 OFFLINE
_SYSSMU277$ UNDOTBS2 OFFLINE
_SYSSMU278$ UNDOTBS2 OFFLINE
_SYSSMU279$ UNDOTBS2 OFFLINE
_SYSSMU280$ UNDOTBS2 OFFLINE
_SYSSMU281$ UNDOTBS2 OFFLINE
_SYSSMU282$ UNDOTBS2 OFFLINE
_SYSSMU283$ UNDOTBS2 OFFLINE
_SYSSMU284$ UNDOTBS2 OFFLINE
32 rows selected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5368709120 bytes
Fixed Size 2062520 bytes
Variable Size 939525960 bytes
Database Buffers 4412407808 bytes
Redo Buffers 14712832 bytes
Database mounted.
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU2$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU3$ UNDOTBS2 ONLINE
_SYSSMU4$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU5$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU6$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU7$ UNDOTBS2 ONLINE
_SYSSMU8$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU9$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU10$ UNDOTBS2 NEEDS RECOVERY
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS2 OFFLINE
_SYSSMU12$ UNDOTBS2 OFFLINE
_SYSSMU13$ UNDOTBS2 OFFLINE
_SYSSMU267$ UNDOTBS2 OFFLINE
_SYSSMU268$ UNDOTBS2 OFFLINE
_SYSSMU269$ UNDOTBS2 OFFLINE
_SYSSMU270$ UNDOTBS2 OFFLINE
_SYSSMU271$ UNDOTBS2 OFFLINE
_SYSSMU272$ UNDOTBS2 OFFLINE
_SYSSMU273$ UNDOTBS2 OFFLINE
_SYSSMU274$ UNDOTBS2 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU275$ UNDOTBS2 OFFLINE
_SYSSMU276$ UNDOTBS2 OFFLINE
_SYSSMU277$ UNDOTBS2 OFFLINE
_SYSSMU278$ UNDOTBS2 OFFLINE
_SYSSMU279$ UNDOTBS2 OFFLINE
_SYSSMU280$ UNDOTBS2 OFFLINE
_SYSSMU281$ UNDOTBS2 OFFLINE
_SYSSMU282$ UNDOTBS2 OFFLINE
_SYSSMU283$ UNDOTBS2 OFFLINE
_SYSSMU284$ UNDOTBS2 OFFLINE
32 rows selected.
SQL> select * from lgx.lgx;
select * from lgx.lgx
*
ERROR at line 1:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5368709120 bytes
Fixed Size 2062520 bytes
Variable Size 939525960 bytes
Database Buffers 4412407808 bytes
Redo Buffers 14712832 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> select file# from v$datafile where status not in ('ONLINE','SYSTEM');
FILE#
----------
4
40
SQL> alter database datafile 4 online;
Database altered.
SQL> alter database datafile 40 online;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'K:\PC\USERS01.DBF'
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 40 needs media recovery
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
SQL> recover datafile 40;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS2 ONLINE
_SYSSMU2$ UNDOTBS2 ONLINE
_SYSSMU3$ UNDOTBS2 ONLINE
_SYSSMU4$ UNDOTBS2 ONLINE
_SYSSMU5$ UNDOTBS2 ONLINE
_SYSSMU6$ UNDOTBS2 ONLINE
_SYSSMU7$ UNDOTBS2 ONLINE
_SYSSMU8$ UNDOTBS2 ONLINE
_SYSSMU9$ UNDOTBS2 ONLINE
_SYSSMU10$ UNDOTBS2 ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS2 OFFLINE
_SYSSMU12$ UNDOTBS2 OFFLINE
_SYSSMU13$ UNDOTBS2 OFFLINE
_SYSSMU267$ UNDOTBS2 OFFLINE
_SYSSMU268$ UNDOTBS2 OFFLINE
_SYSSMU269$ UNDOTBS2 OFFLINE
_SYSSMU270$ UNDOTBS2 OFFLINE
_SYSSMU271$ UNDOTBS2 OFFLINE
_SYSSMU272$ UNDOTBS2 OFFLINE
_SYSSMU273$ UNDOTBS2 OFFLINE
_SYSSMU274$ UNDOTBS2 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU275$ UNDOTBS2 OFFLINE
_SYSSMU276$ UNDOTBS2 OFFLINE
_SYSSMU277$ UNDOTBS2 OFFLINE
_SYSSMU278$ UNDOTBS2 OFFLINE
_SYSSMU279$ UNDOTBS2 OFFLINE
_SYSSMU280$ UNDOTBS2 OFFLINE
_SYSSMU281$ UNDOTBS2 OFFLINE
_SYSSMU282$ UNDOTBS2 OFFLINE
_SYSSMU283$ UNDOTBS2 OFFLINE
_SYSSMU284$ UNDOTBS2 OFFLINE
32 rows selected.
SQL> select * from lgx.lgx;
no rows selected
SQL> spool off;
+alert log出现的错误信息
Thu Aug 06 15:47:59 2009
alter database datafile 40 offline
ORA-376 signalled during: alter database datafile 40 offline...
Thu Aug 06 15:48:04 2009
Errors in file c:\oracle\product\10.2.0\admin\pc\bdump\pc_j000_5472.trc:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
Thu Aug 06 15:48:06 2009
Errors in file c:\oracle\product\10.2.0\admin\pc\bdump\pc_j000_5472.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
Thu Aug 06 15:48:09 2009
Errors in file c:\oracle\product\10.2.0\admin\pc\bdump\pc_pmon_584.trc:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
Thu Aug 06 15:48:09 2009
Errors in file c:\oracle\product\10.2.0\admin\pc\bdump\pc_pmon_584.trc:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: 'M:\PC\UNDOTBS2_1.DBF'
.
.
.
