undo表空间误删除,数据库无法打开,itpub搜遍,未解决

ALLSTARS_ORACLE

数据库版本 10.2.0.3.0
System name:        Linux
Node name:        node1
Release:        2.4.21-37.EL
Version:        #1 Wed Sep 7 13:35:21 EDT 2005
Machine:        i686
Instance name: oracle

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  364904448 bytes
Fixed Size                  1261852 bytes
Variable Size             180358884 bytes
Database Buffers          180355072 bytes
Redo Buffers                2928640 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'

############
alert_oracle.ora 报错

Errors in file /u01/app/oracle/admin/oracle/bdump/oracle_dbw0_1774.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Aug 24 09:59:04 2009
ORA-1157 signalled during: alter database open...
############

SQL> select status from v$instance;

STATUS
------------
MOUNTED

1、创建参数文件

SQL> create pfile='/home/oracle/test.ora' from spfile;

File created.

2、修改参数

vi /home/oracle/test.ora

*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'

3、以pfile启动数据库

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/home/oracle/test.ora'
ORACLE instance started.

Total System Global Area  364904448 bytes
Fixed Size                  1261852 bytes
Variable Size             184553188 bytes
Database Buffers          176160768 bytes
Redo Buffers                2928640 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      SYSTEM
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

4、将undo01文件脱机

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

Database altered.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

############
alert_oracle.ora

alter database open
Mon Aug 24 10:46:37 2009
Beginning crash recovery of 1 threads
Mon Aug 24 10:46:37 2009
Started redo scan
Mon Aug 24 10:46:38 2009
Completed redo scan
11596 redo blocks read, 803 data blocks need recovery
Mon Aug 24 10:46:38 2009
Started redo application at
Thread 1: logseq 98, block 289
Mon Aug 24 10:46:38 2009
Recovery of Online Redo Log: Thread 1 Group 4 Seq 98 Reading mem 0
  Mem# 0: /u02/oradata/oracle/redo04_1.log
Mon Aug 24 10:46:38 2009
Completed redo application
Mon Aug 24 10:46:38 2009
Completed crash recovery at
Thread 1: logseq 98, block 11885, scn 1730816
803 data blocks read, 803 data blocks written, 11596 redo blocks read
Mon Aug 24 10:46:38 2009
Thread 1 advanced to log sequence 99
Thread 1 opened at log sequence 99
  Current log# 5 seq# 99 mem# 0: /u02/oradata/oracle/redo05_1.log
Successful open of redo thread 1
Mon Aug 24 10:46:38 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Aug 24 10:46:38 2009
SMON: enabling cache recovery
Mon Aug 24 10:46:38 2009
Errors in file /u01/app/oracle/admin/oracle/udump/oracle_ora_2001.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
Mon Aug 24 10:46:38 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 2001
ORA-1092 signalled during: alter database open...
############

查看 数据文件状态

select file#||' '||name||' '||status from v$datafile;

FILE#||''||NAME||''||STATUS
--------------------------------------------------------------------------------
1 /u02/oradata/oracle/system01.dbf SYSTEM
2 /u02/oradata/oracle/undotbs01.dbf RECOVER
3 /u02/oradata/oracle/sysaux01.dbf ONLINE
4 /u02/oradata/oracle/users01.dbf ONLINE
5 /u02/oradata/oracle/wap.dbf ONLINE
8 /u02/oradata/oracle/test.dbf ONLINE

6 rows selected.

发现数据文件处于 RECOVER 状态

recover 数据文件，发现还是报错

SQL> recover datafile '/u02/oradata/oracle/undotbs01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'

再次尝试打开数据库，仍然报错。

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


======================

实在找不到解决的办法了,请各位帮助,谢谢！
我想只要能打开，exp/imp下就好，可是就是open不了，唉。。

ALLSTARS_ORACLE

用了的
SQL> startup pfile='/home/oracle/test.ora' mount;
ORACLE instance started.

Total System Global Area  364904448 bytes
Fixed Size                  1261852 bytes
Variable Size             150998756 bytes
Database Buffers          209715200 bytes
Redo Buffers                2928640 bytes
Database mounted.
SQL> show parameter resetlog

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     TRUE
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced



*._CORRUPTED_ROLLBACK_SEGMENTS=  这个不知道 _SYSSMU $ 这个的序号

*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'

这两个加了的

我现在的test.ora文件内容：

*.audit_file_dest='/u01/app/oracle/admin/oracle/adump'
*.background_dump_dest='/u01/app/oracle/admin/oracle/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u02/oradata/oracle/control01.ctl','/u02/oradata/oracle/control02.ctl','/home/oracle/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/oracle/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='oracle'
*.db_recovery_file_dest=''
*.db_recovery_file_dest_size=1
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracleXDB)'
*.job_queue_processes=10
*.local_listener=''
*.open_cursors=300
*.pga_aggregate_target=120586240
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=364904448
*.sga_target=361758720
*.shared_pool_size=138412032
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
*.user_dump_dest='/u01/app/oracle/admin/oracle/udump'
_allow_resetlogs_corruption=true


oracle_ora_2001.trc

内容如下：

/u01/app/oracle/admin/oracle/udump/oracle_ora_2001.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2/db_1
System name:        Linux
Node name:        node1
Release:        2.4.21-37.EL
Version:        #1 Wed Sep 7 13:35:21 EDT 2005
Machine:        i686
Instance name: oracle
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 2001, image: oracle@node1 (TNS V1-V3)

*** 2009-08-24 10:46:37.938
*** SERVICE NAME) 2009-08-24 10:46:37.937
*** SESSION ID324.3) 2009-08-24 10:46:37.937
Thread 1 checkpoint: logseq 98, block 2, scn 1710411
  cache-low rba: logseq 98, block 289
    on-disk rba: logseq 98, block 11885, scn 1710816
  start recovery at logseq 98, block 289, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 5798Kb in 0.22s => 25.74 Mb/sec
Total physical reads: 8192Kb
Longest record: 12Kb, moves: 0/4460 (0%)
Change moves: 7/81 (8%), moved: 0Mb
Longest LWN: 943Kb, moves: 1/37 (2%), moved: 0Mb
Last redo scn: 0x0000.001a1adf (1710815)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 803/802 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 3731/4607 = 0.8
----------------------------------------------
*** 2009-08-24 10:46:38.164
KCRA: start recovery claims for 803 data blocks
*** 2009-08-24 10:46:38.334
KCRA: blocks processed = 803/803, claimed = 803, eliminated = 0
*** 2009-08-24 10:46:38.335
Recovery of Online Redo Log: Thread 1 Group 4 Seq 98 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 803/802 = 1.0
Max compares per lookup = 2
Avg compares per lookup = 4520/4533 = 1.0
----------------------------------------------
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'


正在整理数据，几个dml操作都没提交。

突然断电，重启后，磁盘阵列起不来了，因为空间不够，undo表空间的数据文件放在磁阵上的，结果就出现这种情况了。

其实不算是误删，只是和误删除的结果一样，为了看的方便，所以就写误删了。

ALLSTARS_ORACLE

   这个磁阵拿去修了，估计要1-2个星期的，比较老的磁阵了。我们的数据库现在要用的，不可能等这么久的啊。

而且，修理的人说了，数据就不要抱太大期望了。。。

strings system01.dbf | grep _SYSSMU，找到了 _SYSSMU1$ -- _SYSSMU10$

修改了init文件，加上了
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/home/oracle/test.ora' mount;
ORACLE instance started.

Total System Global Area  364904448 bytes
Fixed Size                  1261852 bytes
Variable Size             150998756 bytes
Database Buffers          209715200 bytes
Redo Buffers                2928640 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


还是老样子， alert 文件报错

alter database open
Tue Aug 25 09:44:48 2009
Beginning crash recovery of 1 threads
Tue Aug 25 09:44:48 2009
Started redo scan
Tue Aug 25 09:44:50 2009
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Tue Aug 25 09:44:50 2009
Started redo application at
Thread 1: logseq 118, block 2, scn 2110896
Tue Aug 25 09:44:50 2009
Recovery of Online Redo Log: Thread 1 Group 4 Seq 118 Reading mem 0
  Mem# 0: /u02/oradata/oracle/redo04_1.log
Tue Aug 25 09:44:50 2009
Completed redo application
Tue Aug 25 09:44:50 2009
Completed crash recovery at
Thread 1: logseq 118, block 3, scn 2130898
0 data blocks read, 0 data blocks written, 1 redo blocks read
Tue Aug 25 09:44:50 2009
Thread 1 advanced to log sequence 119
Thread 1 opened at log sequence 119
  Current log# 5 seq# 119 mem# 0: /u02/oradata/oracle/redo05_1.log
Successful open of redo thread 1
Tue Aug 25 09:44:51 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 25 09:44:51 2009
SMON: enabling cache recovery
Tue Aug 25 09:44:51 2009
Errors in file /u01/app/oracle/admin/oracle/udump/oracle_ora_4564.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u02/oradata/oracle/undotbs01.dbf'
Tue Aug 25 09:44:51 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 4564
ORA-1092 signalled during: alter database open...




删除*.undo_tablespace='SYSTEM' ，再次重启，还是不行啊~报错同上