A detailed analysis of AWR exception handling after Oracle abnormal recovery

There is a problem with a client database that has been recovered through unconventional methods AWR cannot collect statistics (a library that was not recovered several months ago, because it was not rebuilt), and it is not easy to track database performance to help analyze the tracking problem.
Manual Collection Statistics Error RA-00001: Violation of a unique constraint (SYS. WRM$_SNAPSHOT_PK)

Sql> execute Dbms_workload_repository.create_snapshot ();

BEGIN Dbms_workload_repository.create_snapshot (); End;

*
Line 1th Error:

ORA-13509: Error updating AWR table

ORA-00001: violation of UNIQUE constraint (ORA-00001: violation of UNIQUE constraint (SYS). WRM$_SNAPSHOT_PK)

.)

ORA-06512: In the SYS. Dbms_workload_repository ", line

ORA-06512: in the SYS. Dbms_workload_repository ", line 122

ORA-06512: In line 1

The problem with the trace file analysis is as follows

TRACE FILE D:\APP\ADMINISTRATOR\DIAG\RDBMS\RAC\RAC2\TRACE\RAC2_M000_1628.TRC Oracle Database  11g enterprise edition release 11.2.0.3.0 - 64bit production with  the Partitioning, Real Application Clusters, Automatic Storage  Management, oracle label security, olap, data mining, oracle database  Vault and real application testing option windows nt version v6.1  service pack 1  cpu                  : 32 - type 8664, 32 physical cores Process  affinity    : 0x0x0000000000000000 memory  (Avail/Total):  Ph:83326M/ 131035m, ph+pgf:214386m/262068m Instance name: rac2 redo thread mounted by  This instance:&nBsp;2 oracle process number: 61 windows thread id: 1628, image: oracle. exe  (M000)     *** 2015-08-28 11:39:51.967 *** session id: (2062.93)   2015-08-28 11:39:51.968 *** client id: ()  2015-08-28 11:39:51.968 *** SERVICE  NAME: (sys$background)  2015-08-28 11:39:51.968 *** module name: (mmon_slave)  2015-08-28  11:39:51.968 *** action name: (auto-flush slave action)  2015-08-28  11:39:51.968    *** kewrocistmtexec - encountered error:  (ORA-00001:  Violation of UNIQUE constraint   (SYS. WRM$_SNAPSHOT_PK))      *** SQLSTR: total-len=342, dump-len=240,         STR={insert into   WRM$_SNAPSHOT   (snap_id,  dbid, instance_number, startup_time,begin_interval_time,  end_interval_time,&Nbsp;snap_level,    status, error_count, bl_moved, snap_flag, snap_ TimeZone)    values    (:snap_id, :d Bid, :instance_number, :sta} ***  Kewrafs: error=13509 encountered by auto flush slave.

It is clear that the statistics cannot be collected because a primary key conflict occurs when the table is wrm$_snapshot. Because the AWR data is historical and can be cleaned up, so try to delete the AWR data to see if it solves the problem.

Do 10046 trace discovery of collected snapshots

sql> oradebug setmypid

processed statements

sql> alter session set events ' 10046 Trace name Context forever, Level 12 ';

The session has changed.

sql> oradebug tracefile_name

D:\APP\ADMINISTRATOR\diag\rdbms\rac\rac2\trace\rac2_ora_5944.trc

sql> Execute Dbms_workload_repository.create_snapshot ();

BEGIN Dbms_workload_repository.create_snapshot (); End;

*

Line 1th error:

ORA-13509: Error updating AWR table

ORA-00001: violation of UNIQUE constraint (ORA-00001: violation of a unique constraint (SYS). WRM$_SNAPSHOT_PK)

.)

ORA-06512: In the SYS. Dbms_workload_repository ", line

ORA-06512: in the SYS. Dbms_workload_repository ", line 122

ORA-06512: In line 1

--trace file Analysis

parsing in cursor  #1362260992   lid=0 tim=22781405124 hv=438921370 ad= ' 148fd90590 '  sqlid= ' 15rbgh4d2ku4u ' Insert into wrm$_snapshot (snap_id, dbid, instance_ Number, startup_time,begin_interval_time, end_interval_time, Snap_level,status, error_count,  bl_moved,snap_flag, snap_timezone) VALUES (:snap_id, :d bid, :instance_number, : Startup _time, :begin_interval_time, :end_interval_time, :snap_level,    :status,  0, 0, :BIND1, :BIND2) end of stmt parse  #1362260992: c=0,e=474,p=0,cr=0,cu=0, mis=1,r=0,dep=1,og=1,plh=0,tim=22781405122 binds  #1362260992:  bind#0   oacdty=02 mxl=22 ( mxlc=00 mal=00 scl=00 pre=00   oacflg=00 fl2=0000 frm=00 csi=) 00 siz=208 off=0   kxsbbbfp=513a6bf8  bln=22  avl=03  flg=05   value=9277  bind#1   oacdty=02 mxl=22 ()  mxlc=00 mal=00 scl=00  pre=00   oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24    kxsbbbfp=513a6c10  bln=22  avl=06  flg=01   value=2429481020  Bind# 2   oacdty=02 mxl=22 ( mxlc=00 mal=00 scl=00 pre=00)   oacflg=00  fl2=0000 frm=00 csi=00 siz=0 off=48   kxsbbbfp=513a6c28  bln=22   avl=02  flg=01   value=2  bind#3   oacdty=180 mxl=11 (11)  mxlc=00 mal=00 scl=09 pre=00   oacflg=00 fl2=8000000 frm=00 csi=

00 siz=0 off=72   kxsbbbfp=513a6c40  bln=11  avl=07  flg=01   value=28-8 month  -15 10.06.53  morning  bind#4   oacdty=180 mxl=11 (One)  mxlc=00 mal=00 scl=09 pre=00   oacflg=00 fl2=8000000 frm=00  csi=00 siz=0 off=88   kxsbbbfp=513a6c50  bln=11  avl=07  flg=   value=28-8 month  -15 10.06.53  Morning  bind#5   oacdty=180 mxl=11 (11)  mxlc=00 mal=00 scl=09 pre=00   oacflg=00 fl2=8000000 frm=00 csi=

00 siz=0 off=104   kxsbbbfp=513a6c60  bln=11  avl=11  flg=01   value=28-8 month  -15 04.11.40.017000000  afternoon  bind#6   oacdty=02 mxl=22 ( )  mxlc=00 mal=00 scl=00 pre=00   oacflg=00 fl2=0000 frm=00 csi=

00 siz=0 off=120   kxsbbbfp=513a6c70  bln=22  avl=02  flg=01   value=1  bind#7   oacdty=02 mxl=22 ()  mxlc=00 mal=00 scl=00 pre=00   oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=144  

 kxsbbbfp=513a6c88  bln=22  avl=02  flg=01   value=1  Bind#8   oacdty=02 mxl=22 ( mxlc=00 mal=00 scl=00 pre=00)   oacflg=00  fl2=0000 frm=00 csi=00 siz=0 off=168   kxsbbbfp=513a6ca0  bln=22   avl=02  flg=01   value=1  bind#9   oacdty=183 mxl=11 (11)  mxlc=00 mal=00 scl=09 pre=09   oacflg=01 fl2=8000000 frm=00 csi=

00 siz=0 off=192   kxsbbbfp=513a6cb8  bln=11  avl=11  flg=01   value=Unhandled datatype  (183)  found in kxsbndinf

This can be clearly defined as the fact that AWR is collecting information when it is inserted into a value and a record conflict exists in the library, causing such problems to occur

Clean up AWR data

Sql> select max (snap_id), Min (snap_id)  from WRM$_SNAPSHOT;   MAX (snap_id)  min (snap_id)------------ ------------         9277         9081   Sql> exec dbms_workload _repository.

Drop_snapshot_range (9081,9277);

The   pl/sql  process has been successfully completed.

  sql> Sql> select max (snap_id), Min (snap_id)  from WRM$_SNAPSHOT;   MAX (snap_id)  min (snap_id)------------ ------------         9277         9277   Sql> exec dbms_workload _repository.

Drop_snapshot_range (9080,9278);

The   pl/sql  process has been successfully completed.

  Sql> select max (snap_id), Min (snap_id)  from WRM$_SNAPSHOT;   MAX (snap_id)  min (snap_id)------------ ------------         9277         9277   sql> delete from  wrm$

_snapshot where snap_id=9277; delete from  wrm$_snapshot where snap_id=9277               *  1  Line error: ora-00600:  internal error code,  parameter:  [13011],  [6653], [8456911], [2], [8456911], [3], [], [], [], [], [],  []     Sql> delete /*+ rule */ from  wrm$_snapshot where

 snap_id=9277;   deleted 0 rows.

It's kind of weird here, snap_id=9277 records can't be cleaned up, and normal deletions are reported ora-00600[13011]. Based on experience, the problem is likely due to the record of tables and index

Try Rebuild Index

sql> Analyze table Wrm$_snapshot validate structure cascade;

Analyze table Wrm$_snapshot Validate structure Cascade

*
Line 1th Error:
ORA-01499: Table/Index Cross Reference failed-see trace file

Sql> Select Index_name from dba_indexes where table_name= ' wrm$_snapshot ';

index_name

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

wrm$_snapshot_pk

sql> alter INDEX wrm$_ SNAPSHOT_PK rebuild;

The index has changed.

Sql> Select/*+ Full (t) */MAX (snap_id), Min (snap_id) from Wrm$_snapshot t;

Max (snap_id) min (snap_id)

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

sql> select Max (snap_id), Min (snap_id ) from Wrm$_snapshot;

MAX (snap_id) MIN (snap_id)

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

9277 9277

Here is a clear positioning, because the table and index records inconsistent, and through the rebuild, found that the index is still a problem

Rebuilding index

sql> set linesize 180 sql> set pages 999 SQL> set long 

90000 sql> select dbms_metadata.get_ddl (' INDEX ', ' wrm$_snapshot_pk ', ' SYS ')  from dual;   Dbms_metadata. GET_DDL (' INDEX ', ' wrm$_snapshot_pk ', ' SYS ')--------------------------------------------------------------------- -----------    CREATE UNIQUE INDEX  "SYS". " WRM$_SNAPSHOT_PK " ON " SYS "." Wrm$_snapshot "  (" DBID ", " snap_id ", " Instance_number ")   pctfree 10 initrans  2 maxtrans 255 compute statistics   storage (INITIAL 65536 NEXT  1048576 minextents 1 maxextents 2147483645   PCTINCREASE 0  Freelists 1 freelist groups 1   BUFFER_POOL DEFAULT FLASH_CACHE  Default cell_flash_cache default)   TABLESPACE  "Sysaux"     sql> drop index  "SYS". "

WRM$_SNAPSHOT_PK " ; drop index  "SYS". " WRM$_SNAPSHOT_PK "                  *  1  Line error: ora-02429:  cannot remove index     sql> alter table& for forcing a unique/primary key nbsp; " SYS "." Wrm$_snapshot " drop  constraint " SYS "."

WRM$_SNAPSHOT_PK "; alter table  "SYS". " Wrm$_snapshot " drop  constraint " SYS "." WRM$_SNAPSHOT_PK "                                                     

     *  1  Line error: ora-01735:  invalid  ALTER TABLE  option     sql> alter table  "Wrm$_snapshot" drop  constraint  "WRM$_SNAPSHOT_PK";

The   table has changed.   sql>alter table  "Wrm$_snapshot"  add constraint  "WRM$_SNAPSHOT_PK"  

Primary key ("DBID",  "snap_id",  "Instance_number");

The   table has changed.

Try to do the snapshot again sql> execute dbms_workload_repository.create_snapshot ();

Begin dbms_workload_repository.create_snapshot (); end;   *  1  line error: ora-00600:  internal error code,  parameter:  [kewrose_1], [600], [ora-00600:   Internal error code,  parameter: [6002], [6], [104],  [4], [0], [], [], [], 

[], [], [], [], [], [], [], [], [], [], [], [], [] ora-06512:  in   "SYS. Dbms_workload_repository ",  line 99 ora-06512:  in  " SYS. Dbms_workload_repository ",  line 122 ora-06512:  in  line 1

Tragedies occur again, and when the snapshot is collected, a sad and ora-00600[kewrose_1]/ora-600[6002 error is encountered. The scope of the awr of the previous data are not, but also the most extreme processing methods, navigate to the table, and then handle the

Continue with track 10046.

parsing in cursor  #1495840456  tim=24328721585 hv=4050667988 ad= ' 146f9948f8 ' sqlid = ' 84qubbrsr0kfn ' Insert into wrh$_latch (Snap_id, dbid, instance_number, latch_hash,  level#, gets, misses, sleeps,  immediate_gets,immediate_misses, spin_gets,  Sleep1, sleep2, sleep3, sleep4, wait_time)    select :snap_id, :d bid,

 :instance_number, hash, level#, gets,    misses, sleeps,  Immediate_gets, immediate_misses, spin_gets,    sleep1, sleep2, sleep3 ,  sleep4, wait_time  from    v$latch  order by     hash end of stmt parse  #1495840456: c=0,e=376,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1 , plh=0,tim=24328721584 binds  #1495840456:  bind#0   oacdty=02 mxl=22  mxlc=00& NBsp;mal=00 scl=00 pre=00   oacflg=00 fl2=0000 frm=00 csi=00 siz=72 

Off=0   kxsbbbfp=60471350  bln=22  avl=03  flg=05   value=9280  bind#1   oacdty=02 mxl=22 ( mxlc=00 mal=00 scl=00 pre=00)    Oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24   kxsbbbfp=60471368   bln=22  avl=06  flg=01   value=2429481020  Bind#2   oacdty=02  mxl=22 ( mxlc=00 mal=00 scl=00 pre=00)   oacflg=00 fl2=0000 frm= 00 csi=00 siz=0 off=48   kxsbbbfp=60471380  bln=22  avl=02   FLG=01   value=2 ora-00600:  Internal Error code,  parameters:  [6002], [6], [104], [4],  [0], [], [], [], [], [], [], []

This allows you to navigate to the problem that occurs on the insert operation of the Wrh$_latch table.

Analyze and TRUNCATE TABLE

Sql> SELECT COUNT (*) from Wrh$_latch;

COUNT (*)

--------

0

sql> truncate TABLE wrh$_latch;

Table is truncated
Collect snapshot information again

Sql> execute Dbms_workload_repository.create_snapshot ();

The

Pl/sql process has completed successfully.

Sql> @?/rdbms/admin/awrrpt.sql

--Normal work
After some column processing, finally let awr to work, especially after the abnormal recovery, AWR data may have a variety of problems caused by the work is not normal, you can consider rebuilding awr, you can also consider like me to clean up the AWR data, And then let go of the deal. Of course, for Oracle databases that are restored using unconventional methods, it is recommended that the library be logically rebuilt if conditions permit. Because there are inconsistent data dictionaries, logical bad blocks, tables and index inconsistencies, and so on, in the subsequent use of gradually exposed, resulting in a lot of trouble, Rebuild the library thoroughly to solve the problem.