误删除了UNDO对应的文件，数据库起不来了怎么办

EnochPersist

谢谢

nyh

这个办法行不通

nyh

我现在根本就open不了，所以也没法创新的undo表空间

kew888

先OPEN了再说吧

在INIT.ORA中加入_ALLOW_RESETLOGS_CORRUPTION=TRUE

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
SQL> Cancel
SQL>ALTER DATABASE OPEN RESETLOGS;

opsc

原帖由 nyh 于 2009-10-22 22:18 发表
我现在根本就open不了，所以也没法创新的undo表空间

I tried, you can:

comment auto undo related parameter
add:
undo_management = manual
rollback_segments = (SYSTEM)  -- just like to create a new db, you can use this temporarily
Then:

offline your undo file
open you database         -> you can do this now
recreate your undo tablespace

aiirii

SQL> startup
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2087376 bytes
Variable Size             587204144 bytes
Database Buffers         2617245696 bytes
Redo Buffers               14688256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oracle/oradata/undotbs01.dbf'


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/system01.dbf
/oracle/oradata/undotbs01.dbf
...
/oracle/oradata/system02.dbf
/oracle/oradata/sysaux02.dbf




SQL> alter database datafile '/oracle/oradata/undotbs01.dbf' offline drop;

SQL> alter database open;

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/oracle/oradata/undotbs02.dbf' SIZE 110M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

SQL> alter system set undo_tablespace=UNDOTBS2;

SQL> drop tablespace UNDOTBS1 including contents AND DATAFILES;

viadeazhu

你看看11楼的例子不是行得通么。。。

听海★蓝心梦

原帖由 aiirii 于 2009-10-23 09:22 发表
SQL> startup
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2087376 bytes
Variable Size             587204144 bytes
Database Buffers         2617245696 bytes
Redo Buffers               14688256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oracle/oradata/undotbs01.dbf'


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/system01.dbf
/oracle/oradata/undotbs01.dbf
...
/oracle/oradata/system02.dbf
/oracle/oradata/sysaux02.dbf




SQL> alter database datafile '/oracle/oradata/undotbs01.dbf' offline drop;

SQL> alter database open;

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/oracle/oradata/undotbs02.dbf' SIZE 110M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

SQL> alter system set undo_tablespace=UNDOTBS2;

SQL> drop tablespace UNDOTBS1 including contents AND DATAFILES;

这个是正解，我前两天刚遇到过一次同样的错误，也是这样解决的。

idoi

不过我在最后一步删除undotbs1的时候报错了。

sys@baal> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU1$', 终止删除表空间

wupenglove2005

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;


SEGMENT_NAME STATUS TABLESPACE_NAME

------------------------------ ---------------- ---------------

SYSTEM ONLINE SYSTEM

_SYSSMU2$ ONLINE UNDOTBS1

_SYSSMU3$ ONLINE UNDOTBS1

_SYSSMU4$ ONLINE UNDOTBS1

_SYSSMU5$ ONLINE UNDOTBS1

_SYSSMU6$ ONLINE UNDOTBS1

_SYSSMU7$ ONLINE UNDOTBS1

_SYSSMU8$ ONLINE UNDOTBS1

_SYSSMU9$ ONLINE UNDOTBS1

_SYSSMU10$ ONLINE UNDOTBS1

_SYSSMU21$ ONLINE UNDOTBS1

用上面的语句查出UNDO中的段。

然后在pfile 中加入
undo_management = manual
rollback_segments = (SYSTEM,_SYSSMU2$ ,...........)

最后
SQL> alter database datafile '/oracle/oradata/undotbs01.dbf' offline drop;

SQL> alter database open;

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/oracle/oradata/undotbs02.dbf' SIZE 110M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

SQL> alter system set undo_tablespace=UNDOTBS2;

SQL> drop tablespace UNDOTBS1 including contents AND DATAFILES;