ITPub博客

首页 > 数据库 > Oracle > ORA-1652 ORA-01114 ORA-27072

ORA-1652 ORA-01114 ORA-27072

原创 Oracle 作者:西门吹牛 时间:2009-05-30 17:30:18 0 删除 编辑

今天接到同事电话,让我帮忙处理一个TEMP表空间的问题,开始的信息很简单,我也觉得这是一个简单的CASE,后来才发现原来还有点意思。

警告日志内容如下

hsscena:orahsp 10> pwd
/oracle/HSP/saptrace/background
-rw------- 1 orahsp dba 379602 May 29 15:14 alert_HSP.log

Fri May 29 08:32:30 2009
Completed checkpoint up to RBA [0x1643b.2.10], SCN: 10671889620
Fri May 29 08:43:52 2009
Incremental checkpoint up to RBA [0x1643b.5d84.0], current log tail at RBA [0x1643b.678a.0]
Fri May 29 08:49:40 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 08:50:38 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 08:54:05 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 08:54:53 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 08:55:05 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 08:56:19 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 08:56:30 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 08:59:00 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 09:00:15 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 09:13:53 2009
Incremental checkpoint up to RBA [0x1643b.b50f.0], current log tail at RBA [0x1643b.c140.0]
Fri May 29 09:25:04 2009
Fri May 29 09:25:04 2009
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
ORA-1652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Fri May 29 09:25:38 2009

原来就是会滚段不够了,扩展不就可以了吗

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
PSAPUNDO ONLINE
SYSAUX ONLINE
PSAPTEMP ONLINE
PSAPSR3 ONLINE
PSAPSR3700 ONLINE
PSAPSR3USR ONLINE
PSAPSR3DB ONLINE
HP_DBSPI ONLINE
TS_PAYWARE ONLINE

10 rows selected.

SQL> select file_name,tablespace_name,status from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/oracle/HSP/sapdata1/temp_1/temp.data1
PSAPTEMP AVAILABLE


SQL> select bytes/1024/1024/1024 from dba_temp_files;

BYTES/1024/1024/1024
--------------------
1.953125

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS
---------- ---------------- --------------- ---------- ---------- -------
ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
1 7007 22-AUG-06 3 1 ONLINE
READ WRITE 2097152000 256000 1394606080 8192
/oracle/HSP/sapdata1/temp_1/temp.data1


SQL>

bdf 查看文件系统空间剩余大小

/dev/datavg/lvol12 56524800 56524800 0 100% /oracle/HSP/sapdata1
/dev/datavg/lvol13 52428800 52151031 260413 100% /oracle/HSP/sapdata2
/dev/datavg/lvol14 52428800 52253431 164412 100% /oracle/HSP/sapdata3
/dev/datavg/lvol15 55296000 54814138 451749 99% /oracle/HSP/sapdata4
/dev/sapvg/lvol4 15360000 3223019 11378446 22% /archivelog
/dev/datavg/lvol16 63373312 61472098 1782393 97% /oracle/HSP/sapdata5
/dev/data2vg/lvol1 122880000 122607131 255847 100% /oracle/HSP/sapdata6
/dev/data2vg/lvol2 116146176 78659620 35143713 69% /oracle/HSP/sapdata7

通过sapdba 增加的文件,主要语法如下:
alter tablespace PSAPTEMP add tempfile '/oracle/HSP/sapdata7/temp_2/temp.data2' size 2000M autoextend off;

但是用户还是反映有问题,说SAP系统报告下面两个错误信息

ORA-1114
ORA-27072

可是我在警告日志没有发现有这两个错误,奇怪


select tablespace_name,current_users,total_blocks,used_blocks,free_blocks
from v$sort_segment;

select username,session_addr,sqladdr,sqlhash from v$sort_usage;

我查询发现没有用户使用临时表空间,真是奇怪,没有人用,还报警

我看到前面文件系统空间列表的时候,我知道了,因为
/dev/datavg/lvol12 56524800 56524800 0 100% /oracle/HSP/sapdata1
TEMP 所在的第一个文件系统满了,所以报告IO错误

ALTER DATABASE TEMPFILE '/oracle/HSP/sapdata1/temp_1/temp.data1' DROP INCLUDING DATAFILES;

SQL> select file_name,tablespace_name,status from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/oracle/HSP/sapdata7/temp_2/temp.data2
PSAPTEMP AVAILABLE


/oracle/HSP/sapdata1 2009.05.24 56524800 56361139 153434 100% 56029898 0
2009.05.25 56524800 56361139 153434 100% 0 153434
2009.05.26 56524800 56361139 153434 100% 0 153434
2009.05.27 56524800 56361139 153434 100% 0 153434
2009.05.28 56524800 56524800 0 100% 163661 0
2009.05.29 56524800 56524800 0 100% 0 0

检查哪天满的
发现是 05.28日记录的时候就满了
程序在凌晨1点运行,就是说,05.27的01点到05.28日01点之间有变更,占用了153M空闲空间
控制文件是可以增长,但是控制文件目前一共才22M,不可能涨这么快

检查数据库告警日志

Wed May 27 14:21:56 2009
Errors in file /oracle/HSP/saptrace/usertrace/hsp_ora_17131.trc:
ORA-01114: IO error writing block to file 255 (block # 208404)
ORA-27072: File I/O error
HPUX-ia64 Error: 28: No space left on device
Additional information: 4
Additional information: 208404
Additional information: -1
ORA-01114: IO error writing block to file 255 (block # 208404)
ORA-27072: File I/O error
HPUX-ia64 Error: 28: No space left on device
Additional information: 4
Additional information: 208404
Additional information: -1
ORA-01114: IO error writing block to file 255 (block # 208404)
ORA-27072: File I/O error
HPUX-ia64 Error: 28: No space left on device
Additional information: 4
Additional information: 208404
Additional information: -1
ORA-01114: IO error writing block to file 255 (block # 208342)
ORA-27072: File I/O error
HPUX-ia64 Error: 11: Resource temporarily unavailable
Additional information: 4
Additional information: 208342
Additional information: 234496


Beginning log switch checkpoint up to RBA [0x16396.2.10], SCN: 10655625477
Thread 1 advanced to log sequence 91030
Current log# 6 seq# 91030 mem# 0: /oracle/HSP/origlogB/log_g6_m1.dbf
Current log# 6 seq# 91030 mem# 1: /oracle/HSP/mirrlogB/log_g6_m2.dbf
Wed May 27 14:58:19 2009
WARNING: event:10629 is set. pid:5997 online index build starting final merge.
pid:5997 completed merge of index.
Wed May 27 14:58:33 2009
pid:5997 completed merge of index.
Wed May 27 14:58:33 2009
WARNING: event:10629 is set. pid:7429 online index build starting final merge.
pid:7429 completed merge of index.
Wed May 27 15:03:20 2009
Completed checkpoint up to RBA [0x16396.2.10], SCN: 10655625477
Wed May 27 15:09:23 2009

这个系统不是我管理,目前就只能查到这里了,我们数据库的数据文件和临时文件的自动扩展属性都是OFF,不会是我们的文件涨的,等6.1上班问问同事在27日有什么操作影响了吧。

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10771/viewspace-1022602/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-06-13

  • 博文量
    18
  • 访问量
    123038