带你深入了解回滚表空间丢失的解决方法
来源：岁月联盟
时间：2008-01-28
问题：Oracle数据库的undotbs01.dbf文件损坏？
解决方法如下：
首先，我们需要模拟这个错误，方法是将undotbs01.dbf移到其它目录下，然后再来进行修正，过程如下：
SQL> startupORACLE instance started.Total System Global Area 135338868 bytesFixed Size 453492 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 2 - see DBWR trace fileORA-01110: data file 2: ''D:/ORACLE/ORADATA/ORCL/UNDOTBS01.DBF''SQL> alter system set undo_management=''MANUAL'' scope=spfile;System altered.SQL> alter database datafile ''d:/oracle/oradata/orcl/undotbs01.dbf'' offline drop;Database altered.SQL> alter database open;Database altered.SQL> |
现在，此数据库已经能正常启动，但是还是没有达到我们的要求，因为在Oracle9i以后的版本建议用undo表空间来代替回滚段，现在我们需要将其设置为undo表空间。
SQL>create undo tablespace undotbs2 datafile ''d:/oracle/oradata/orcl/undotbs02.dbf'' size 100M;Tablespace created.SQL> select * from v$tablespace; TS# NAME INC---------- ------------------------------ --- 3 CWMLITE YES 4 DRSYS YES 5 EXAMPLE YES 6 INDX YES 7 ODM YES 0 SYSTEM YES 8 TOOLS YES 1 UNDOTBS1 YES 9 USERS YES 10 XDB YES 2 TEMP YES TS# NAME INC---------- ------------------------------ --- 11 UNDOTBS2 YES12 rows selected.SQL> alter system set undo_management=''AUTO'' scope=spfile;System altered.SQL> alter system set undo_tablespace=''UNDOTBS2'' scope=spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 135338868 bytesFixed Size 453492 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> show parameter undoNAME TYPE VALUE------------------------------------ ----------- -----undo_management string AUTOundo_retention integer 10800undo_suppress_errors boolean FALSEundo_tablespace string UNDOTBS2SQL> |
此时，数据库成功启动。
