测试现象:
SQL> exec dbms_tts.transport_set_check('USERS',TRUE);
BEGIN dbms_tts.transport_set_check('USERS',TRUE); END;
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
检查原因:
看了一下DBA_TABLESPACES,嗯...TEMP的status还online....不过是假象。
SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
INDX ONLINE
ODM ONLINE
TOOLS ONLINE
SQL> select tablespace_name, file_name from dba_temp_files;
no rows selected
dba_temp_files里可是空空如也~~
解决问题:
我们会遇到两种情况,一种是OS level的physical datafile不见了,一种是logical tempfile不见了,我不知道我之前曾做了 啥,不过似乎遇到的是第二种情况,验证一下。
$ ls -al
total 11642092
drwxr-xr-x 5 oracle dba 1024 Sep 2 16:43 .
drwxrwxrw- 8 oracle dba 512 Sep 24 17:55 ..
-rw-r----- 1 oracle dba 1925120 Oct 5 13:56 control01.ctl
-rw-r----- 1 oracle dba 1925120 Oct 5 13:56 control02.ctl
-rw-r----- 1 oracle dba 1925120 Oct 5 13:56 control03.ctl
-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 cwmlite01.dbf
-rw-r--r-- 1 oracle dba 415486976 Aug 5 18:01 dba.dmp
-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 drsys01.dbf
-rw-r----- 1 oracle dba 144842752 Oct 5 13:17 example01.dbf
-rw-r----- 1 oracle dba 69476352 Oct 5 13:17 indx01.dbf
-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 odm01.dbf
-rw-r----- 1 oracle dba 52445184 Oct 5 13:17 oem_repository.dbf
-rw-r----- 1 oracle dba 20979712 Mar 3 2004 ram_01.dbf
drwxrjkr-x 2 oracle dba 512 Dec 17 2003 redo01
drwxr-xr-x 2 oracle dba 512 Dec 17 2003 redo02
drwxr-xr-x 2 oracle dba 512 Dec 17 2003 redo03
-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 rman_tbs01.dbf
-rw-r--r-- 1 oracle dba 664 Aug 5 18:03 sqlnet.log
-rw-r----- 1 oracle dba 429924352 Oct 5 13:49 system01.dbf
-rw-r----- 1 oracle dba 99622912 Aug 16 14:16 temp01.dbf
-rw-r----- 1 oracle dba 38019072 Oct 5 13:17 tools01.dbf
-rw-r----- 1 oracle dba 351281152 Oct 5 13:56 undotbs01.dbf
-rw-r----- 1 oracle dba 1048584192 Oct 5 13:17 users01.dbf
-rw-r----- 1 oracle dba 1048584192 Oct 5 13:17 users02.dbf
-rw-r----- 1 oracle dba 47194112 Oct 5 13:17 xdb01.dbf
-rw-r----- 1 oracle dba 5251072 Oct 5 13:17 xyz.dbf
这temp01.dbf datafile最后access时间怎么还停留在8月勒...可见我两个月没上来数据库玩了,都没发现到。不管了,先救回来再说。
SQL> alter tablespace temp add tempfile '/oradata/elife02/temp01.dbf'
Tablespace altered.
SQL> select FILE_NAME,TABLESPACE_NAME,STATUS from dba_temp_files;
FILE_NAME TABLESPACE_NAME STATUS
----------------------------------- ------------------------------ ---------
/oradata/elife02/temp01.dbf TEMP AVAILABLE
模拟现象:
现在来恶搞,先模拟第一种drop physical datafile
#rm temp01.dbf
SQL> select file_name , TABLESPACE_NAME, STATUS from dba_temp_files;
FILE_NAME
------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/oradata/elife02/temp01.dbf
TEMP AVAILABLE
此时temp file还是保留 AVAILABLE
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/oradata/elife02/temp01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
不过只要有process在进行sorting,就会出现错误了,那此时数据库仍可正常开关机吗?
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 337086968 bytes
Fixed Size 731640 bytes
Variable Size 318767104 bytes
Database Buffers 16777216 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> select file_name , TABLESPACE_NAME, STATUS from dba_temp_files;
select file_name , TABLESPACE_NAME, STATUS from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/oradata/elife02/temp01.dbf'
显然是可以,不过在开机时候,DBWR会把找不到temp file的信息写入trace file内。
由于我只是砍掉physical file,对于Oracle来说他仍然认为这个tempfile需要存在,故原本我想 依照文件所说,先drop掉仍然存在的metadata,只是当我下这指令时,系统似乎会hang住不动。
SQL> alter database tempfile '/oradata/elife02/temp01.dbf' drop;
....................
我只好先把他offline,再试试看可不可以drop。
SQL> alter database tempfile '/oradata/elife02/temp01.dbf' offline;
Database altered.
SQL> alter database tempfile '/oradata/elife02/temp01.dbf' drop;
^Calter database tempfile '/oradata/elife02/temp01.dbf' drop
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
还是不能drop,好吧,重新启动试试 看。
SQL> startup force
ORACLE instance started.
Total System Global Area 337086968 bytes
Fixed Size 731640 bytes
Variable Size 318767104 bytes
Database Buffers 16777216 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> alter database tempfile '/oradata/elife02/temp01.dbf' drop;
Database altered.
可以了,我不知道为什么,但后来我不死心又一次砍掉tempfile再立刻drop掉却又是可行的~~
$ rm temp02.dbf
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0 .5.0 - Production on 星期二 10月 5 14:42:46 2004
Connected to:
Oracle9i Enterprise Edition Release 9.2.0 .5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0 .5.0 - Production
SQL> alter database tempfile '/oradata/elife02/temp02.dbf' drop;
Database altered. (又可以了)
SQL> alter tablespace temp add tempfile '/oradata/elife02/temp01.dbf' size 5m ;
Tablespace altered.![](http://img.zemanta.com/pixy.gif?x-id=0f874b41-7dae-8644-9eb2-7c59ec9b70f0)