Oracle UNDO tablespace reconstruction and restoration

Data files in the UNDO tablespace are directly deleted from the OS, and the database has not been shutduwn, and UNDO needs to be restored.

1. The database is running normally;

2. rm-fundotbs02.dbf: the database runs without knowing it until there is a log error alarm.

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. An error is returned when you create a 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. At this time, pay attention to keep the site, do not close the database at will, and query where UNDO segments are in use.
SQL> select * from v $ rollname;
USN NAME
----------------------------------------
0 SYSTEM
11 _ SYSSMU11_2087506584 $
12 _ syssmu12_19030000335 $
13 _ SYSSMU13_3816552520 $
14 _ SYSSMU14_2728152801 $
15_syssmu15_2082928664 $
16 _ SYSSMU16_1938921541 $
17 _ SYSSMU17_3062294638 $
 
8 rows selected.
If the database has been forcibly disabled, You can query it as follows.
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_19030000335 $ 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

1 2 Next Page