Oracle M00n trace ORA-00001: unique constraint (SYS.WRH$_RSRC_PDB_METRIC_PK) Hatası

Selamlar,

Oracle 12.2.0.1 database trace dosyalarını incelerken M00n trace dosyasında (db1_m000_86776.trc) aşağıdaki gibi bir hata tespit ettik.

*** 2021-11-08T11:30:06.221963+03:00
*** SESSION ID:(5337.9158) 2021-11-08T11:30:06.221986+03:00
*** CLIENT ID:() 2021-11-08T11:30:06.221992+03:00
*** SERVICE NAME:(SYS$BACKGROUND) 2021-11-08T11:30:06.221997+03:00
*** MODULE NAME:(MMON_SLAVE) 2021-11-08T11:30:06.222002+03:00
*** ACTION NAME:(RAC Flush Slave Action) 2021-11-08T11:30:06.222008+03:00
*** CLIENT DRIVER:() 2021-11-08T11:30:06.222013+03:00

*** KEUUXS - encountered error: (ORA-00001: unique constraint (SYS.WRH$_RSRC_PDB_METRIC_PK) violated
)
  *** SQLSTR: total-len=1390, dump-len=1390,
      STR={insert into  WRH$_RSRC_PDB_METRIC  (dbid, per_pdb, con_dbid, snap_id, instance_number,    begin_time,    end_time,    intsize_csec,    sequence#,    cpu_consumed_time,    cpu_wait_time,    io_requests,    io_megabytes,    io_requests_throttle_exempt,    io_megabytes_throttle_exempt,    io_throttle_time,    pq_active_time,    pq_queued_time,    ps_active_time,    ps_queued_time,    sga_bytes,    buffer_cache_bytes,    shared_pool_bytes,    pga_bytes,    os_num_cpus,    plan_id )   select  :dbid, 0 per_pdb,  :srcdbid  con_dbid,  :snap_id, :instance_number,    cast(rm.begin_kskpdbmetric as timestamp(3)),    cast(rm.end_kskpdbmetric as timestamp(3)),    rm.intsize_csec_kskpdbmetric,    rm.seq_kskpdbmetric,    rm.cputm_kskpdbmetric,    rm.cpuwt_kskpdbmetric,    rm.ioreqs_kskpdbmetric,    rm.iomb_kskpdbmetric,    rm.ioreqs_throt_ex_kskpdbmetric,    rm.iomb_throt_ex_kskpdbmetric,    rm.iothrot_kskpdbmetric,    rm.pqacttm_kskpdbmetric,    rm.pqqdtm_kskpdbmetric,    rm.psacttm_kskpdbmetric,    rm.psqdtm_kskpdbmetric,    rm.avg_sga_bytes_kskpdbmetric,    rm.avg_bc_bytes_kskpdbmetric,    rm.avg_sp_bytes_kskpdbmetric,    rm.avg_pga_bytes_kskpdbmetric,    os.ksucpustatvalue,    pl.plan_id_kgskpft  FROM   x$kskpdbmetric   rm,  x$ksucpustat   os,  x$kgskpft   pl  WHERE (1 = 1)    and rm.begin_kskpdbmetric is not null    and os.ksucpustatname = 'NUM_CPUS'    and pl.con_id = :con_id2 }
*** KEWRAFM1: Error=13509 encountered by kewrfteh

ORA-00001: unique constraint (SYS.WRH$_RSRC_PDB_METRIC_PK) Hatası Çözümü

Bu hatayı MOS üzerinde araştırdığımızda Bug 30887989 : ORA-00001: UNIQUE CONSTRAINT karşımıza çıkıyor. Aşağıdaki çözümü uyguluyoruz.

Workaround 1:

CREATE UNIQUE INDEX "SYS"."WRH$_RSRC_PDB_METRIC_PK2" ON "SYS"."WRH$_RSRC_PDB_METRIC" ("DBID", "SNAP_ID", "INSTANCE_NUMBER", "BEGIN_TIME", "SEQUENCE#", "CON_DBID", "PLAN_ID") TABLESPACE "SYSAUX" ;

ALTER TABLE "SYS"."WRH$_RSRC_PDB_METRIC" DROP CONSTRAINT "WRH$_RSRC_PDB_METRIC_PK";

Workaround 2:

Remove Plan using DBMS_SPM.drop_sql_plan_baseline

Mevcut index’i drop edip yeni unique index create ettikten sonra sorun çözüldü.

Eğer Oracle Database trace dosyalarında hatalar almaya devam ediyorsanız destek hizmetimizden yararlanabilirsiniz.

Kaynak:

M00n Trace Shows Following Error ‘ORA-00001: unique constraint (SYS.WRH$_RSRC_PDB_METRIC_PK)’ (Doc ID 2659234.1)