ORA-1652 ORA-01114 ORA-27072

最新推荐文章于 2021-04-05 17:08:54 发布

congrang4031

最新推荐文章于 2021-04-05 17:08:54 发布

阅读量401

点赞数

文章标签：数据库

今天接到同事电话，让我帮忙处理一个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;

/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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10771/viewspace-1022602/