ora-01555

2^#

发表于 2012-3-30 09:15:05

兄弟们，都是只看不吭声。太简单了？

拿那个时候的ash，先看了1分钟的，里面没有这条sql，又看了5分钟的还是没有这条sql.

引起的原因有可能是：改这个表的数据的sql一直没有提交？？？？

3^#

发表于 2012-3-30 20:49:57

ORA-1555产生的主要原因包括查询过久、undo被覆盖等，也可能由于bug引起。

Action Plan:

1. 上传ALERT.LOG
2.

运行一下SQL，并上传结果：

set linesize 140

ttitle center "Undo Extents Error Conditions (Default - Last 4 Hours)" skip 2

col UNXPSTEALCNT format 999,999,999  heading "# Unexpired|Stolen"
col EXPSTEALCNT format 999,999,999 heading "# Expired|Reused"
col SSOLDERRCNT format 999,999,999 heading "ORA-1555|Error"
col NOSPACEERRCNT format 999,999,999 heading "Out-Of-space|Error"
col MAXQUERYLEN format 999,999,999 heading "Max Query|Length"
col TUNED_UNDORETENTION format 999,999,999  heading "Auto-Ajusted|Undo Retention"
col hours format 999,999 heading "Tuned|(HRs)"

select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
   UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN,
   TUNED_UNDORETENTION, TUNED_UNDORETENTION/60/60 hours
from gv$undostat
where begin_time between (sysdate-10)
                  and sysdate
order by inst_id, begin_time
/

col inst_id format 999 head "Instance #"
col Parameter format a35 wrap
col "Session Value" format a25 wrapped
col "Instance Value" format a25 wrapped

prompt
prompt  ############## PARAMETERS ##############
prompt

select  a.inst_id, a.ksppinm  "Parameter",
         b.ksppstvl "Session Value",
         c.ksppstvl "Instance Value"
   from x$ksppi a, x$ksppcv b, x$ksppsv c
   where a.indx = b.indx and a.indx = c.indx
   and a.inst_id=b.inst_id and b.inst_id=c.inst_id
   and a.ksppinm in ('_undo_autotune', '_smu_debug_mode',
                     '_highthreshold_undoretention',
            'undo_tablespace','undo_retention','undo_management')
order by 2;

4^#

发表于 2012-3-31 10:49:36

alert内容。附件中为sql执行的结果。

Sat Mar 31 02:57:38 2012
Thread 1 advanced to log sequence 107391
Current log# 1 seq# 107391 mem# 0: /oradata/GNOSDB/redo01.log
Sat Mar 31 03:26:49 2012
ORA-01555 caused by SQL statement below (SQL ID: b9ft5m4krysfm, Query Duration=3 sec, SCN: 0x09dd.7ae8e1d9):
Sat Mar 31 03:26:49 2012
ORA-01555 caused by SQL statement below (SQL ID: g7q54917afkpz, Query Duration=3 sec, SCN: 0x09dd.7ae8e1ef):
Sat Mar 31 03:26:49 2012
SELECT SUBSTR("A1"."ALARMTIME",0,7),"A1"."FACTORY","A1"."NETID",COUNT(*) FROM "T_ALARM_DATA_CURRENT" "A1" WHERE "A1"."NETTYPE"='BTS' OR "A1"."NETTYPE"='TRX' GROUP BY SUBSTR("A1"."ALARMTIME",0,7),"A1"."FACTORY","A1"."NETID"
Sat Mar 31 03:26:49 2012
SELECT SUBSTR("A1"."ALARMTIME",0,7),"A1"."FACTORY","A1"."BSC","A1"."ALARMTYPE",COUNT(*) FROM "T_ALARM_DATA_CURRENT" "A1" GROUP BY SUBSTR("A1"."ALARMTIME",0,7),"A1"."FACTORY","A1"."BSC","A1"."ALARMTYPE"
Sat Mar 31 03:26:49 2012
ORA-01555 caused by SQL statement below (SQL ID: 5tc204kdq343z, Query Duration=3 sec, SCN: 0x09dd.7ae8e1af):
Sat Mar 31 03:26:49 2012
SELECT SUBSTR("A1"."ALARMTIME",0,7),"A1"."FACTORY","A1"."ALARMCODE","A1"."ALARMNAME",COUNT(*) FROM "T_ALARM_DATA_CURRENT" "A1" GROUP BY SUBSTR("A1"."ALARMTIME",0,7),"A1"."FACTORY","A1"."ALARMCODE","A1"."ALARMNAME"
Sat Mar 31 04:06:14 2012
Thread 1 advanced to log sequence 107392
Current log# 2 seq# 107392 mem# 0: /oradata/GNOSDB/redo02.log

3Q .liu

t.txt

131.24 KB, 下载次数: 998

5^#

发表于 2012-3-31 21:33:49

ORA-01555 caused by SQL statement below (SQL ID: b9ft5m4krysfm, Query Duration=3 sec, SCN: 0x09dd.7ae8e1d9):

ORA-01555 caused by SQL statement below (SQL ID: g7q54917afkpz, Query Duration=3 sec, SCN: 0x09dd.7ae8e1ef):

ORA-01555 caused by SQL statement below (SQL ID: 5tc204kdq343z, Query Duration=3 sec, SCN: 0x09dd.7ae8e1af):

b9ft5m4krysfm g7q54917afkpz 5tc204kdq343z 这是3条不同的SQL，虽然查询时间只有3秒，它们开始查询的SCN 很接近：
0x09dd.7ae8e1af

T_ALARM_DATA_CURRENT

把这张表的DDL列出来：

set serveroutput on;

declare
  res varchar2(4000);
begin
  res := dbms_metadata.get_ddl(object_type => 'TABLE',
                           name       => 'T_ALARM_DATA_CURRENT',
                           schema    => '&OWNER');
  dbms_output.put_line(res);
end;
/

6^#

发表于 2012-4-1 16:10:09

select dbms_metadata.get_ddl('TABLE','T_ALARM_DATA_CURRENT') FROM DUAL;

  CREATE TABLE "GSM"."T_ALARM_DATA_CURRENT"
(
"ALARMID" VARCHAR2(50),

"ALARMCODE" VARCHAR2(50),

"ALARMNAME" VARCHAR2(100),

"ALARMTYPE" VARCHAR2(500),

"ALARMLEVEL" VARCHAR2(50),

"ALARMSTATE" VARCHAR2(50),

"ALARMTIME" VARCHAR2(200),

"CONFIRMTIME" VARCHAR2(50),

"ENDTIME" VARCHAR2(50),

"LOCATION" VARCHAR2(200),

"NETTYPE" VARCHAR2(50),

"NETID" VARCHAR2(50),

"FACTORY" VARCHAR2(50),

"ENTERTIME" VARCHAR2(50),

"DISTRICT" VARCHAR2(50),

"BSC" VARCHAR2(50),

"BAND" VARCHAR2(50),

"RING" VARCHAR2(50),

"TRX" VARCHAR2(50),

"SUPPLINFO" VARCHAR2(255),

"USERINFO" VARCHAR2(255),

"OBJECTINFO" VARCHAR2(255),

"ALARM_UID" VARCHAR2(300)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "GSM"

7^#

发表于 2012-4-1 18:15:52

# Unexpired # Expired ORA-1555 Out-Of-space Max Query Auto-Ajusted Tuned
INST_ID BEGIN_TIME Stolen Reused Error Error Length Undo Retention (HRs)
---------- ---------------- ------------ ------------ ------------ ------------ ------------ -------------- --------
1 03/31/2012 02:25 0 0 0 0 2,977 3,821 1
1 03/31/2012 02:35 0 0 0 0 3,584 4,428 1
1 03/31/2012 02:45 0 0 0 0 1,527 2,371 1
1 03/31/2012 02:55 0 0 0 0 0 900 0
1 03/31/2012 03:05 0 0 0 0 0 900 0
1 03/31/2012 03:15 0 0 0 0 0 900 0
1 03/31/2012 03:25 0 0 3 0 361 1,142 0

复制代码

可以观察 3月31日问题发生时段的  v$UNDOSTAT视图信息：

在03:25 集中发生了3次ORA-1555 snapshot too old 问题，

观察 Auto-Ajusted Undo Retention 自动undo 调优可以发现从2:55 开始自动undo retention 从 2,371下降到 900s 又上升到 1142s  ，这说明在这个时间段存在大规模的undo 使用，可能是短期内大的事务造成了undo snapshot 被覆盖。

UNDOTBS1 ==>  这是否是RAC系统？

建议：

1. 设置 undo tablespace guarantee

2.

查询以下信息并贴出结果

select unxpstealcnt a, expstealcnt b,
  unxpblkreucnt c, expblkreucnt d
from v$undostat
where (unxpstealcnt > 0 or expstealcnt > 0)
and end_time > sysdate-2
/

set serveroutput on;

DECLARE
tablespacename       varchar2(30);
tablespacesize       number;
autoextend          boolean;
autoextendtf       char(5);
undoretention       number;
retentionguarantee boolean;
retentionguaranteetf  char(5);
autotuneenabled    boolean;
autotuneenabledtf    char(5);
longestquery       number;
requiredretention    number;
bestpossibleretention number;
requireundosize    number;

problem             varchar2(100);
recommendation       varchar2(100);
rationale          varchar2(100);
retention          number;
utbsize             number;
nbr                number;
undoadvisor          varchar2(100);
instancenumber       number;
ret                boolean;
rettf                char(5);
BEGIN
dbms_output.put_line('--x--x--x--x--x--x--x');
dbms_output.put_line('--undo_info');
dbms_output.put_line('--x--x--x--x--x--x--x');
ret := dbms_undo_adv.undo_info(tablespacename,
tablespacesize,
      autoextend, undoretention,
retentionguarantee);
if ret then rettf := 'TRUE'; else rettf :=
'FALSE'; end if;
if autoextend then autoextendtf := 'TRUE';
   else autoextendtf := 'FALSE'; end if;
if retentionguarantee then retentionguaranteetf
:= 'TRUE';
   else retentionguaranteetf := 'FALSE'; end if;
dbms_output.put_line ('Information Valid :
'||rettf);
dbms_output.put_line ('Tablespace Name    :
'||tablespacename);
dbms_output.put_line ('Tablespace Size    :
'||tablespacesize);
dbms_output.put_line ('Extensiable       :
'||autoextendtf);
dbms_output.put_line ('undo_retention    :
'||undoretention);
dbms_output.put_line ('Guaranteed Retention :
'||retentionguaranteetf);

dbms_output.put_line('--x--x--x--x--x--x--x');
dbms_output.put_line('--undo_health');
dbms_output.put_line('--x--x--x--x--x--x--x');
nbr := dbms_undo_adv.undo_health(problem,
recommendation, rationale, retention, utbsize);
dbms_output.put_line ('Information Valid :
'||nbr);
dbms_output.put_line ('Problem             :
'||problem);
dbms_output.put_line ('Recommendation    :
'||recommendation);
dbms_output.put_line ('Rationale          :
'||rationale);
dbms_output.put_line ('Retention          :
'||retention);
dbms_output.put_line ('UTBSize             :
'||utbsize);

dbms_output.put_line('--x--x--x--x--x--x--x');
dbms_output.put_line('--undo_advisor');
dbms_output.put_line('--x--x--x--x--x--x--x');
select instance_number into instancenumber from
v$instance;
undoadvisor :=
dbms_undo_adv.undo_advisor(instancenumber);
dbms_output.put_line ('Undo Advisor       :
'||undoadvisor);

dbms_output.put_line('--x--x--x--x--x--x--x');
dbms_output.put_line('--undo_autotune');
dbms_output.put_line('--x--x--x--x--x--x--x');
ret :=
dbms_undo_adv.undo_autotune(autotuneenabled);
if autotuneenabled then autotuneenabledtf :=
'TRUE';
   else autotuneenabledtf := 'FALSE'; end if;
dbms_output.put_line ('Auto Tuning Enabled  :
'||autotuneenabledtf);

dbms_output.put_line('--x--x--x--x--x--x--x');
dbms_output.put_line('--longest_query');
dbms_output.put_line('--x--x--x--x--x--x--x');
longestquery :=
dbms_undo_adv.longest_query(sysdate-1,sysdate);
dbms_output.put_line ('Longest Run Query :
'||longestquery);

dbms_output.put_line('--x--x--x--x--x--x--x');
dbms_output.put_line('--required_retention');
dbms_output.put_line('--x--x--x--x--x--x--x');
requiredretention :=
dbms_undo_adv.required_retention;
dbms_output.put_line ('Required Retention :
'||requiredretention);

dbms_output.put_line('--x--x--x--x--x--x--x');

dbms_output.put_line('--best_possible_retention');
dbms_output.put_line('--x--x--x--x--x--x--x');
bestpossibleretention :=
dbms_undo_adv.best_possible_retention;
dbms_output.put_line ('Best Retention    :
'||bestpossibleretention);

dbms_output.put_line('--x--x--x--x--x--x--x');
dbms_output.put_line('--required_undo_size');
dbms_output.put_line('--x--x--x--x--x--x--x');
requireundosize := dbms_undo_adv.required_undo_size(444);
dbms_output.put_line ('Required Undo Size :
'||requireundosize);

END;
/

8^#

发表于 2012-4-2 11:36:06

现在上不了数据库，过了节发上。谢谢，Liu

9^#

发表于 2012-4-5 09:44:33

SQL> select unxpstealcnt a, expstealcnt b,
  2 unxpblkreucnt c, expblkreucnt d
  3  from v$undostat
  4  where (unxpstealcnt > 0 or expstealcnt > 0)
  5  and end_time > sysdate-10
  6  ;

      A       B       C       D
---------- ---------- ---------- ----------

--x--x--x--x--x--x--x
--undo_info
--x--x--x--x--x--x--x
Information Valid :
TRUE
Tablespace Name    :
UNDOTBS1
Tablespace Size    :
32768
Extensiable       :
TRUE
undo_retention    :
900
Guaranteed Retention :
FALSE
--x--x--x--x--x--x--x
--undo_health
--x--x--x--x--x--x--x
Information Valid :
0
Problem             :
No problem found
Recommendation    :

Rationale          :

Retention          :
0
UTBSize             :
0
--x--x--x--x--x--x--x
--undo_advisor
--x--x--x--x--x--x--x
Undo Advisor       :
Finding 1:还原表空间正常。
--x--x--x--x--x--x--x
--undo_autotune
--x--x--x--x--x--x--x
Auto Tuning Enabled  :
TRUE
--x--x--x--x--x--x--x
--longest_query
--x--x--x--x--x--x--x
Longest Run Query :
3031
--x--x--x--x--x--x--x
--required_retention
--x--x--x--x--x--x--x
Required Retention :
5442
--x--x--x--x--x--x--x
--best_possible_retention
--x--x--x--x--x--x--x
Best Retention    :
82382
--x--x--x--x--x--x--x
--required_undo_size
--x--x--x--x--x--x--x
Required Undo Size :
1428

liu脚本好多哦。。。

10^#

发表于 2012-4-5 09:58:15

Tablespace Name    :
UNDOTBS1
Tablespace Size    :
32768
Extensiable       :
TRUE
undo_retention    :
900
Guaranteed Retention :
FALSE

建议你先设置 undotbs1 为 retention guarantee 再观察一段时间：

alter tablespace undotbs1 retention guarantee;