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 Area  313860096 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 ID133.1) 2014-06-17 01:42:17.812
*** CLIENT ID) 2014-06-17 01:42:17.812
*** SERVICE NAMESYS$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'


请大家帮忙看看，谢谢！

xifenfei

TS#	FILE#	TABLESPACE_NAME	STATUS	ERROR	FORMAT	REC	FUZ	CREATE_TIME	SCN	RESETLOGS SCN
0	1	SYSTEM	ONLINE		10	NO	YES	2014-01-27  03:23:01	2968741	1
1	2	SYSAUX	ONLINE		10	NO	YES	2014-01-27  03:23:35	2968741	1
2	3	UNDOTBS1	OFFLINE		10	NO	NO	2014-01-27  03:23:45	2947956	1
4	4	USERS	OFFLINE		10	YES	YES	2014-01-27  03:24:17	2969413	1
5	5	TBS	ONLINE		10	NO	YES	2014-02-12  19:20:19	2968741	1
6	6	RMAN_TBS	ONLINE		10	NO	YES	2014-03-04  18:33:32	2968741	1
7	8	TEST	ONLINE		10	NO	YES	2014-03-08  21:58:42	2968741	1
8	7	TT1	ONLINE		10	NO	YES	2014-03-05  21:03:47	2968741	1

这里很明显的看到你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里面记录