oracle断电后重建undo,Oracle UNDO表空间重建与恢复

UNDO表空间的数据文件从OS直接删除了，数据库还没shutduwn，UNDO需要恢复。

1，数据库正常运行状态中；

2，rm - fundotbs02.dbf， 数据库在不知情的情况下接着运行，直到有日志错误告警。

Fri Apr 19 13:27:03 2013

[3942] ORA-01554 reason: failed to get txn-enqueue TX-0x000d0000-0x00000000

Errors in file /u01/app/Oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_smon_3942.trc:

ORA-01595: error freeing extent (17) of rollback segment (13))

ORA-01554: transaction concurrency limit reached reason:failed to get TX-enqueue params:851968, 0

Fri Apr 19 13:27:51 2013

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_j000_10253.trc:

ORA-12012: error on auto execute of job 43

ORA-01116: error in opening database file 7

ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_j000_10253.trc:

ORA-12012: error on auto execute of job 43

ORA-01116: error in opening database file 7

ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

3，此时创建spfile也会报错。

SQL> create pfile from spfile;

create pfile from spfile

*

ERROR at line 1:

ORA-01116: error in opening database file 7

ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

4，此时，注意保留现场，不要随意关闭数据库，查询有哪里UNDO的段正在使用。

SQL> select * from v$rollname;

USN NAME

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

0 SYSTEM

11 _SYSSMU11_2087506584$

12 _SYSSMU12_1903386335$

13 _SYSSMU13_3816552520$

14 _SYSSMU14_2728152801$

15 _SYSSMU15_2082928664$

16 _SYSSMU16_1938921541$

17 _SYSSMU17_3062294638$

8 rows selected.

如果数据库已经强行关闭了，可以查询如下。

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME                  STATUS          TABLESPACE_NAME

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

11 _SYSSMU11_2087506584$ NEEDS RECOVERY  UNDOTBS2

12 _SYSSMU12_1903386335$ NEEDS RECOVERY  UNDOTBS2

13 _SYSSMU13_3816552520$ NEEDS RECOVERY  UNDOTBS2

14 _SYSSMU14_2728152801$ NEEDS RECOVERY  UNDOTBS2

15 _SYSSMU15_2082928664$ NEEDS RECOVERY  UNDOTBS2

16 _SYSSMU16_1938921541$ NEEDS RECOVERY  UNDOTBS2

        17 _SYSSMU17_3062294638$ NEEDS RECOVERY  UNDOTBS2