Oracle ora-01555解决方法

发现大量ORA-01555

发现大量ORA-01555

ORA-01555 caused by SQL statement below (SQL ID: 2cqskkn0c41kq, Query Duration=3906 sec, SCN: 0x0000.ad2056a9):

ORA-01555 caused by SQL statement below (SQL ID: 2dmk2qf1dxkwt, Query Duration=4332 sec, SCN: 0x0000.acf400a7):

ORA-01555 caused by SQL statement below (SQL ID: 2dmk2qf1dxkwt, Query Duration=5098 sec, SCN: 0x0000.ae793e79):

一般Alert日志里面发现这种，就是回滚段太小了，一般不用管，如果确实影响到业务，可以杀一些占用资源搞的回滚或查询进程。

1.查看大事务 gv$transaction

SQL> select inst_id,addr,used_urec,used_ublk from gv$transaction;

SQL> select start_date,inst_id,addr,used_urec,used_ublk from gv$transaction;

START_DATE INST_ID ADDR USED_UREC USED_UBLK

----------------- ---------- ---------------- ---------- ----------

20121019 06:08:53 2 07000001FCC35D08 1 1

20121019 07:20:15 2 07000001FB884A78 9 1

20121019 07:20:15 2 07000001FDF215A0 6 1

20121019 07:20:15 2 07000001FDF76818 9 1

20121019 06:42:42 1 07000001FDF06398 10 1

20121019 07:16:53 1 07000001FB8A1660 6 1

20121019 06:40:28 1 07000001FCC6EC38 6 1

20121019 07:20:15 1 07000001F9B42A68 9 1

20121019 05:13:54 1 07000001FDF79338 42976487 693162

2.根据上面gv$transaction中ADDR地址与v$session中的taddr字段匹配查找sid,serial#

SQL> select sql_child_number,

sid,

sql_id,last_call_et,

blocking_session,

blocking_instance,

state,

event,

p1,

p2,

seconds_in_wait

from v$session

where

taddr='07000001FDF79338';

3.根据sid可以查找spid

查询得到该session对应的OS进程号:

SQL> select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);

4.监控undo 使用情况

CLEAR COLUMNS BREAKS COMPUTES

COLUMN tablespace FORMAT a20 HEADING 'Tablespace Name'

COLUMN sizeb FORMAT 999,999,999 HEADING 'Used Size'

COLUMN sizea FORMAT 999,999,999 HEADING 'Tablespace Size'

COLUMN status FORMAT a12 HEADING 'Status'

COLUMN pct FORMAT a8 HEADING 'Used Pct'

CLEAR COMPUTES BREAKS

BREAK ON tablespace on report

COMPUTE sum LABEL "Total: " OF sizeb ON report

select a.tablespace_name tablespace

,b.status status

,b.bytes sizeb

,a.bytes sizea

,round(100*(b.bytes/a.bytes),2)||'%' pct

from

,sum(bytes)/1024/1024 bytes

from dba_data_files

group by tablespace_name) a,

,sum(bytes)/1024/1024 bytes

from dba_undo_extents

group by tablespace_name,status) b

where a.tablespace_name=b.tablespace_name

order by 1,2;

SQL> select tablespace_name,status,sum(bytes)/1024/1024/1024 from dba_undo_extents group by tablespace_name,status order by 1,3;

5.可以考虑使用

kill -9 spid

或alter system kill session '986,16161';的方式

6.检查会话是否已经被杀掉

select a.sid,a.serial#,b.spid,a.status from v$session a,v$process b where a.paddr=b.addr and a.taddr='07000001FDF79338';

7.事务回滚监控

SQL> select * from v$fast_start_transactions;

USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS

---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------

10 38 4314095 RECOVERING 50 906028 67 42 0 0 0 000A00260041D3EF 0000000000000000 32

SQL> select file_id,block_id from dba_rollback_segs where SEGMENT_ID=10;

FILE_ID BLOCK_ID

---------- ----------

2 153