ORA-00604 ORA-00376 ORA-01110 ORA-06512错误
之前用RMAN copy了users表空间的数据文件，然后offline drop 数据文件。然后利用copy来恢复users表空间
RMAN>restore tablespace users;--没问题
RMAN>recover tablespace users;--没问题
RMAN>sql 'alter tablespace users online';
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
ORA-06512: at line 999
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
SQL>alter tablespace users online;
也报同样的错误。
SQL> startup
ORACLE instance started.
Total System Global Area313860096 bytes
Fixed Size 1336232 bytes
Variable Size 205524056 bytes
Database Buffers 100663296 bytes
Redo Buffers 6336512 bytes
Database mounted.
Database opened.
SQL> set linesize 1000
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS OFFLINE
TBS ONLINE
RMAN_TBS ONLINE
TEST ONLINE
TT1 ONLINE
9 rows selected.
SQL> !oerr ora 25153
25153, 00000, "Temporary Tablespace is Empty"
// *Cause: An attempt was made to use space in a temporary tablespace with
// no files.
// *Action: Add files to the tablespace using ADD TEMPFILE command.
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
ORA-06512: at line 999
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
SQL> select * from v$recover_file;
select * from v$recover_file
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
SQL> alter tablespace temp add tempfile '+data_dg/hjj/tempfile/TEMP.263.837919427' size 100M reuse;
alter tablespace temp add tempfile '+data_dg/hjj/tempfile/TEMP.263.837919427' size 100M reuse
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
ORA-06512: at line 999
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
SQL> create table t(id int) tablespace tbs;
create table t(id int) tablespace tbs
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
ORA-06512: at line 999
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
用10046跟踪了一下
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
ORA-06512: at line 999
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/s01/app/oracle/product/11.2.0/dbhome_1/dbs/users01.dbf'
SQL> alter session set events '10046 trace name context off';
Session altered.
10046跟踪日志信息
*** 2014-06-17 01:42:17.812
*** SESSION ID:(133.1) 2014-06-17 01:42:17.812
*** CLIENT ID:() 2014-06-17 01:42:17.812
*** SERVICE NAME:(SYS$BACKGROUND) 2014-06-17 01:42:17.812
*** MODULE NAME:() 2014-06-17 01:42:17.812
*** ACTION NAME:() 2014-06-17 01:42:17.812
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 3: '+DATA_DG/hjj/datafile/undotbs1.262.837919417'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 3: '+DATA_DG/hjj/datafile/undotbs1.262.837919417'
*** 2014-06-17 01:42:23.132
****KET ABA Slave Failed, return code 25153
*** 2014-06-17 01:44:50.507
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 3: '+DATA_DG/hjj/datafile/undotbs1.262.837919417'
*** 2014-06-17 01:47:50.779
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 3: '+DATA_DG/hjj/datafile/undotbs1.262.837919417'
*** 2014-06-17 01:50:50.906
DDE: Problem Key 'ORA 1110' was flood controlled (0x5) (no incident)
ORA-01110: data file 3: '+DATA_DG/hjj/datafile/undotbs1.262.837919417'
*** 2014-06-17 01:51:50.943
DDE: Problem Key 'ORA 1110' was flood controlled (0x5) (no incident)
ORA-01110: data file 3: '+DATA_DG/hjj/datafile/undotbs1.262.837919417'
请大家帮忙看看，谢谢！
TS#FILE#TABLESPACE_NAMESTATUSERRORFORMATRECFUZCREATE_TIMESCNRESETLOGS SCN
01SYSTEMONLINE 10NOYES2014-01-2703:23:0129687411
12SYSAUXONLINE 10NOYES2014-01-2703:23:3529687411
23UNDOTBS1OFFLINE 10NONO2014-01-2703:23:4529479561
44USERSOFFLINE 10YESYES2014-01-2703:24:1729694131
55TBSONLINE 10NOYES2014-02-1219:20:1929687411
66RMAN_TBSONLINE 10NOYES2014-03-0418:33:3229687411
78TESTONLINE 10NOYES2014-03-0821:58:4229687411
87TT1ONLINE 10NOYES2014-03-0521:03:4729687411
这里很明显的看到你online datafile 4 不成功，是因为你的undo 对应的数据文件是offline的，而且从报错上看，因为有undo回滚段可能还有事务存在也就是说，数据库在datafile 4 online的时候需要使用undo，但是现在undo表空间无法正常访问，从而出现该问题
解决方案：0. 尝试undotbs1 online 试试看1. 尝试设置undo_management=manual，然后启动数据库，删除现在undo表空间，然后创建新undo表空间2. 直接使用屏蔽undo隐含参数，然后删除相关undo回滚段参考：dba_rollback_segs里面记录
页:
[1]