一个数据文件恢复的问题

[wdslhr]

我有个数据文件undotbs01.dbf 在表空间UNDOTBS1下，现在undotbs01.dbf 该文件为脱机状态，我想让他重新联机，也就是恢复它。我试图用归档日志恢复它，但没有成功，报如下错误：ORA-01178: file 2 created before last CREATE CONTROLFILE, cannot recreate。

我现在又没有其他备份文件，这种情况，我该如何恢复呢，或怎么处理呢？

[liuyi8903]

现在数据库是什么情况?

create undo tablespace ....

[wangzk0206]

fis_smon_3051.trc:
这些内容贴出来看看

[wdslhr]

借道问下:

我在linux 下已经建了一个数据库并已用一个oracle_sid = test1
现在我想再在上面建一个oracle_sid = test2,根据某些途径的方法是在 .bash_profile中添加
export ORACLE_SID=test2
export ORACLE_HOME=/u01/app/oracle/product/9.2.0.4
export PATH=$PATH:$ORACLE_HOME/bin:/tmp/OPatch:/sbin:$ORACLE_HOME/Apache/Apache/bin

添加这三条容易,可是我原来的.bash_profile已经有
export ORACLE_SID=test1
export ORACLE_HOME=/u01/app/oracle/product/9.2.0.4
export PATH=$PATH:$ORACLE_HOME/bin:/tmp/OPatch:/sbin:$ORACLE_HOME/Apache/Apache/bin

再加上去岂不变成了

export ORACLE_SID=test1
export ORACLE_HOME=/u01/app/oracle/product/9.2.0.4
export PATH=$PATH:$ORACLE_HOME/bin:/tmp/OPatch:/sbin:$ORACLE_HOME/Apache/Apache/bin
export ORACLE_SID=test2
export ORACLE_HOME=/u01/app/oracle/product/9.2.0.4
export PATH=$PATH:$ORACLE_HOME/bin:/tmp/OPatch:/sbin:$ORACLE_HOME/Apache/Apache/bin

这样不会oracle_sid=test2覆盖oracle_sid=test1吗?到时启动时如何选择呢,如何启动两个呢?

[wangzk0206]

重新创建一个UNDO表空间吧

然后修改下数据库参数UNDO_TABLESPACE就可以了

没有必要恢复它

[guo0399]

同意2楼所说

[liuyi8903]

如果没有活动事务,可以直接创建新的好了.

所以我问他现在数据库是什么情况.

如果有活动事务存在损坏了的话,那是比较麻烦的.

[wdslhr]

我也想过重建军表空间,但是这如下操作:
create undo tablespace UNDOTBS03 datafile '/u01/app/oracle/oradata/fis/undotbs03.dbf' size 100M;
也建不了,出现错误:ERROR at line 1:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

[wdslhr]

to :liuyi8903

如果有活动事务损坏又如何处理呢?

[wdslhr]

在alert日志中发现如下错误(从24号oracle出现问题开始):
SMON: mark undo segment 10 as needs recovery
Mon Dec 24 12:48:26 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_3051.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'
SMON: about to recover undo segment 1

Mon Dec 24 15:14:45 2007
ORA-1652: unable to extend temp segment by 8 in tablespace UNDOTBS1
Mon Dec 24 15:14:45 2007
Errors in file /u01/app/oracle/admin/fis/udump/fis_ora_4965.trc:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
Mon Dec 24 15:14:46 2007

Mon Dec 24 15:14:46 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_4680.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'
Mon Dec 24 15:14:46 2007
Errors in file /u01/app/oracle/admin/fis/udump/fis_ora_4965.trc:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
Mon Dec 24 15:14:46 2007
Errors in file /u01/app/oracle/admin/fis/udump/fis_ora_4965.trc:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
SMON: about to recover undo segment 1

on Dec 24 16:04:09 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_5245.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'

Mon Dec 24 17:05:47 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_5245.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'


Mon Dec 24 17:25:03 2007
Errors in file /u01/app/oracle/admin/fis/udump/fis_ora_3136.trc:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

Tue Dec 25 09:20:10 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_2980.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'


Tue Dec 25 11:24:17 2007
Errors in file /u01/app/oracle/admin/fis/udump/fis_ora_4071.trc:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
Tue Dec 25 11:24:17 2007
Errors in file /u01/app/oracle/admin/fis/udump/fis_ora_4071.trc:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

Tue Dec 25 13:57:35 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_2980.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'

Tue Dec 25 19:16:01 2007
/* OracleOEM */ ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/fis/undotbs01.dbf' ONLINE
Tue Dec 25 19:16:01 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_dbw0_3069.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'

Tue Dec 25 19:27:07 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_3075.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'

Wed Dec 26 09:57:53 2007
Errors in file /u01/app/oracle/admin/fis/bdump/fis_smon_2969.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/fis/undotbs01.dbf'

[wangzk0206]

数据库版本是什么？
你24号作了什么 是数据库升过级吗？

[wdslhr]

没有升过级,24 号一来，数据库未能启动，我手工启动，发现报错,我就把 undotbs01.dbf'　offline了

[bloodstain]

写一个ORACLE_SID=test1 就可以了
先默认启动test1
要启动test2的话
set ORACLE_SID=test2
sqlplus "/as sysdba"
startup