eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 传CNET将购CSDN和ChinaUnix Itpub离收购还有多远 | Blog首页 | 中文表名-导入导出-字符集 »

Statspack ORA-00001 错误的解决

有朋友遇到Statspack ORA-00001错误。
Errors in file /oracle/app/oracle/admin/shyz/bdump/shyz1_ora_2588734.trc:
ORA-12012: error on auto execute of job 328
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1
Sun Oct 16 00:43:39 2005
这个错误此前从未遇到,但是既然是主键冲突,那肯定是存在重复主键的数据。

肯定能暂时解决问题方法就是暂时禁用唯一约束检查:
ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

然后观察数据来发现根本问题,最后彻底解决之。

到Metalink搜索了一下,发现存在一个相关Bug,Bug号为:2784796.
在设置了cursor_sharing为similar或者force之后,可能触发此Bug,导致主键冲突。

此bug据说在Oracle10g中已经修正。

历史上的今天...
    >> 2010-10-16文章:
    >> 2009-10-16文章:
    >> 2008-10-16文章:
    >> 2007-10-16文章:
           Baby、恩墨-我的儿子
    >> 2006-10-16文章:
    >> 2004-10-16文章:

无觅

By eygle on 2005-10-16 23:08 | Comments (9) | Special | 477 |

9 Comments

还应当提醒他建一个同样结构的非维一性的索引, 否则snap多了, 要生成一个report很慢的.

我们已经这样做了好多次了.

恩,这个错误我是第一次见到,Metalink上好多说明,懒得看了。

你们出问题的系统是RAC环境么?

我们是在做statspack升级时发现的

我们一个crm库,非rac,
cursor_sharing string EXACT
一样还是遇到了同样的问题。

metalink有个workaround

重建view

create or replace view STATS$V_$SQLXS as
select max(sql_text) sql_text
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
, sum(fetches) fetches
, sum(executions) executions
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(buffer_gets) buffer_gets
, sum(rows_processed) rows_processed
, max(command_type) command_type
, address address
, hash_value hash_value
, count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
from v$sql
where ( plan_hash_value > 0
or executions > 0
or parse_calls > 0
or disk_reads > 0
or buffer_gets > 0
)
group by hash_value, address;

create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

我们是由于v$sqlarea里存在4句相同的语句导致,第一次遇到这么奇怪的情况,sigh,浪费了一点eygle的电话费,呵呵。

当时正在参加F5的一个技术研讨会,啥都没帮上:)

如果是rac环境,提示ora-00001错误,但是在单机情况下不报错,会是什么原因呢?

我的环境 rac 9.2.0.5 一般在一台数据库重启后就会出现该错误 我一般把历史数据清掉就ok了 有时过几天不去管他 又好了 我怀疑跟内存数据有关系

$ sqlplus perfstat

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Feb 27 10:34:21 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.5.0 - Production

SQL> exec statspack.snap
BEGIN statspack.snap; END;

*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2442
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1
过几分钟

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

oracle 10gR2 里面还有一个bug ,今天遇到,ft

Errors in file /opt/oracle/admin/forum/bdump/forum11_j000_17729.trc:
ORA-12012: error on auto execute of job 62
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5264
ORA-06512: at "PERFSTAT.STATSPACK", line 104

SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';

INDEX_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
STATS$MUTEX_SLEEP_PK
SNAP_ID

STATS$MUTEX_SLEEP_PK
DBID

STATS$MUTEX_SLEEP_PK
INSTANCE_NUMBER


INDEX_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
STATS$MUTEX_SLEEP_PK
MUTEX_TYPE

STATS$MUTEX_SLEEP_PK
LOCATION


SQL> alter table STATS$MUTEX_SLEEP disable constraint STATS$MUTEX_SLEEP_pk;

Table altered.

SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';

no rows selected

SQL> create index STATS$MUTEX_SLEEP_pk on STATS$MUTEX_SLEEP(snap_id,dbid,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION) tablespace users;

Index created.

SQL> select next_date from user_jobs;

NEXT_DATE
-------------------
2006-08-16 10:29:08
2006-08-16 12:00:00

SQL> exec dbms_job.run(62);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from STATS$MUTEX_SLEEP;

COUNT(*)
----------
5

SQL> select * from STATS$MUTEX_SLEEP;

SNAP_ID DBID INSTANCE_NUMBER MUTEX_TYPE LOCATION
---------- ---------- --------------- -------------------------------- ----------------------------------------
SLEEPS WAIT_TIME
---------- ----------
5298 1834086304 1 Cursor Parent kkspsc0 [KKSPRTLOC26]
2 100

5298 1834086304 1 Cursor Pin kksLockDelete [KKSCHLPIN6]
1881 10384899

5298 1834086304 1 Cursor Pin kkslce [KKSCHLPIN2]
877 6442099


SNAP_ID DBID INSTANCE_NUMBER MUTEX_TYPE LOCATION
---------- ---------- --------------- -------------------------------- ----------------------------------------
SLEEPS WAIT_TIME
---------- ----------
5298 1834086304 1 Cursor Pin kksfbc [KKSCHLFSP2]
296 1699393

5298 1834086304 1 Cursor Pin kksfbc [KKSCHLFSP2]
5835 116531144


SQL>


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com