有一个环境是10gR2,一主两备,因为10g的备库还不是active,所以有一些查询的需求的时候,我们还是会打开相应的窗口时间。
开发的同学需要做一个大查询,数据只能全表,而且还有order by,势必会消耗大量的temp空间,这个时候充分利用备库就是好一些,有一个备库平时也没有用过,今天就用这个备库来完成查询需求。
但是过了一会,开发同事说,查询失败了。让我看看什么原因。
开发同学提供的日志为:
2015-11-20 10:48:05,---exception: ---- StatementCallback; uncategorized SQLException for SQL [select c.cn as cn,c.uin as uin from test_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1]; SQL state [99999]; error code [25153]; ORA-25153: Temporary Tablespace is Empty
; nested exception is java.sql.SQLException: ORA-25153: Temporary Tablespace is Empty
看来这个问题还挺不好意思的,原来临时表空间为空了。
SQL> select file_name,bytes from dba_temp_files;
no rows
那么备库中的临时表空间怎么没了呢?
查看历史记录发现是在前几天的一次日志应用后,临时表空间清空了。
Tue Nov 17 17:48:23 CST 2015
Media Recovery Log /U01/app/oracle/admin/acctest/arch/1_21281_782846320.dbf
Recovery deleting tempfile #3:'/U03/app/oracle/oradata/acctest/temp03.dbf'
Recovery deleting tempfile #2:'/U03/app/oracle/oradata/acctest/temp02.dbf'
Recovery deleting tempfile #1:'/U03/app/oracle/oradata/acctest/temp01.dbf'
Recovery dropped temporary tablespace 'TEMP'
Media Recovery Waiting for thread 1 sequence 21282
因为临时表空间对于数据库来说还是一个辅助的部分,主备库可以不同。所以简单的分析之后决定还是手工添加临时数据文件。
这个时候查看临时表空间,发现已经是TEMP2了。
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
---------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
...
TEMP2
9 rows selected.
既然是空的,那就添加一个临时数据文件吧。结果基本功不扎实,错误提示还是有些误导。
SQL> alter tablespace temp2 add datafile /U03/app/oracle/oradata/acctest/temp01.dbf' size 32G;
alter tablespace temp2 add datafile /U03/app/oracle/oradata/acctest/temp01.dbf' size 32G
*
ERROR at line 1:
ORA-16000: database open for read-only access
简单修改,把datafile改为tempfile继续
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G;
alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G
*
ERROR at line 1:
ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'
ORA-27038: created file already exists
Additional information: 1
这个时候提示文件已经存在,好吧,确实是文件存在,那我就reuse吧。
使用resue的方式,结果报出了ORA-27086的错误。
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;
alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse
*
ERROR at line 1:
ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 19076
这个错误还是让人有些摸不着头脑。是本身就有临时数据文件吗?
SQL> select file_name,bytes from dba_temp_files;
no rows
这个时候查看文件系统中文件的情况。发现貌似时间戳确实是更新了,但是这个文件就是不在数据字典里。
$ ll temp*.dbf
total 432600976
-rw-r----- 1 oracle oinstall 32212262912 Nov 20 10:58 temp01.dbf
-rw-r----- 1 oracle oinstall 21109940224 Jan 5 2015 temp02.dbf
-rw-r----- 1 oracle oinstall 21109940224 Jan 5 2015 temp03.dbf
那么这个问题还是很奇怪的,只能联想到是bug了,结果一查还真有这么一个bug,版本都完全符合。
Bug 15944809 - add tempfile at physical standby fails with ORA-1119, ORA-27086 (Doc ID 15944809.8)
这个问题的workaround 有两个,一个就是重启备库
Workaround
A shutdown/startup of the standby databse will clear the DBW0's
stale file lock state, and then the new tempfile can be created.
那我先试试添加一个新的数据文件,先不停库。
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' size 10G;
Tablespace altered.
可以创建了,那就开始resize一下。
SQL> alter database tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' resize 30G;
Database altered.
这个时候,先让开发同学去完成这个查询任务。
然后查询完成之后,收回环境之后,就可以尝试重启了。
重启之后,再次添加临时数据文件,就没有问题了。
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;
Tablespace altered.
SQL> select name,bytes from v$tempfile;
NAME BYTES
-------------------------------------------------- ----------
/U03/app/oracle/oradata/acctest/temp04.dbf 3.2212E+10
/U03/app/oracle/oradata/acctest/temp01.dbf 3.2212E+10
开发的同学需要做一个大查询,数据只能全表,而且还有order by,势必会消耗大量的temp空间,这个时候充分利用备库就是好一些,有一个备库平时也没有用过,今天就用这个备库来完成查询需求。
但是过了一会,开发同事说,查询失败了。让我看看什么原因。
开发同学提供的日志为:
2015-11-20 10:48:05,---exception: ---- StatementCallback; uncategorized SQLException for SQL [select c.cn as cn,c.uin as uin from test_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1]; SQL state [99999]; error code [25153]; ORA-25153: Temporary Tablespace is Empty
; nested exception is java.sql.SQLException: ORA-25153: Temporary Tablespace is Empty
看来这个问题还挺不好意思的,原来临时表空间为空了。
SQL> select file_name,bytes from dba_temp_files;
no rows
那么备库中的临时表空间怎么没了呢?
查看历史记录发现是在前几天的一次日志应用后,临时表空间清空了。
Tue Nov 17 17:48:23 CST 2015
Media Recovery Log /U01/app/oracle/admin/acctest/arch/1_21281_782846320.dbf
Recovery deleting tempfile #3:'/U03/app/oracle/oradata/acctest/temp03.dbf'
Recovery deleting tempfile #2:'/U03/app/oracle/oradata/acctest/temp02.dbf'
Recovery deleting tempfile #1:'/U03/app/oracle/oradata/acctest/temp01.dbf'
Recovery dropped temporary tablespace 'TEMP'
Media Recovery Waiting for thread 1 sequence 21282
因为临时表空间对于数据库来说还是一个辅助的部分,主备库可以不同。所以简单的分析之后决定还是手工添加临时数据文件。
这个时候查看临时表空间,发现已经是TEMP2了。
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
---------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
...
TEMP2
9 rows selected.
既然是空的,那就添加一个临时数据文件吧。结果基本功不扎实,错误提示还是有些误导。
SQL> alter tablespace temp2 add datafile /U03/app/oracle/oradata/acctest/temp01.dbf' size 32G;
alter tablespace temp2 add datafile /U03/app/oracle/oradata/acctest/temp01.dbf' size 32G
*
ERROR at line 1:
ORA-16000: database open for read-only access
简单修改,把datafile改为tempfile继续
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G;
alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G
*
ERROR at line 1:
ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'
ORA-27038: created file already exists
Additional information: 1
这个时候提示文件已经存在,好吧,确实是文件存在,那我就reuse吧。
使用resue的方式,结果报出了ORA-27086的错误。
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;
alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse
*
ERROR at line 1:
ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 19076
这个错误还是让人有些摸不着头脑。是本身就有临时数据文件吗?
SQL> select file_name,bytes from dba_temp_files;
no rows
这个时候查看文件系统中文件的情况。发现貌似时间戳确实是更新了,但是这个文件就是不在数据字典里。
$ ll temp*.dbf
total 432600976
-rw-r----- 1 oracle oinstall 32212262912 Nov 20 10:58 temp01.dbf
-rw-r----- 1 oracle oinstall 21109940224 Jan 5 2015 temp02.dbf
-rw-r----- 1 oracle oinstall 21109940224 Jan 5 2015 temp03.dbf
那么这个问题还是很奇怪的,只能联想到是bug了,结果一查还真有这么一个bug,版本都完全符合。
Bug 15944809 - add tempfile at physical standby fails with ORA-1119, ORA-27086 (Doc ID 15944809.8)
这个问题的workaround 有两个,一个就是重启备库
Workaround
A shutdown/startup of the standby databse will clear the DBW0's
stale file lock state, and then the new tempfile can be created.
那我先试试添加一个新的数据文件,先不停库。
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' size 10G;
Tablespace altered.
可以创建了,那就开始resize一下。
SQL> alter database tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' resize 30G;
Database altered.
这个时候,先让开发同学去完成这个查询任务。
然后查询完成之后,收回环境之后,就可以尝试重启了。
重启之后,再次添加临时数据文件,就没有问题了。
SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;
Tablespace altered.
SQL> select name,bytes from v$tempfile;
NAME BYTES
-------------------------------------------------- ----------
/U03/app/oracle/oradata/acctest/temp04.dbf 3.2212E+10
/U03/app/oracle/oradata/acctest/temp01.dbf 3.2212E+10
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1845934/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-1845934/