Purger les problèmes dans ADRCI

Le souci

J’utilise la commande purge d’adrci mais mes problèmes restent présents

La commande

adrci> delete from PROBLEM

L’autre souci

L’autre souci c’est que ça purge tous les problèmes et que j’aimerais bien garder ceux qui sont “récents”.

C’est pas interdit de demander de l’aide …

adrci> help delete

  Usage: DELETE FROM <relation> [WHERE <predicate_string>]
       Arguments:
         <relation_name>: Name of the relation to delete from.

      [WHERE <predicate_string>]: The predicate string must be double-quoted.

      Examples:
       delete from incident where 'incident_id > 1'
adrci> show problems

ADR Home = /opt/oracle/diag/rdbms/dbun/DBNAME:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 600 [psdnopGetACL-4]                                    81105                2016-03-27 03:53:10.648000 +02:00
2                    ORA 600 [ktcccod-1]                                         3960130              2016-04-22 06:25:53.408000 +02:00
3                    ORA 600 [kdBlkCheckError]                                   8600040              2016-05-24 11:14:17.489000 +02:00

Allons-y essayons

adrci> delete from PROBLEM where lastinc_time < to_date('2016-03-27 04:00:00', 'YYY-MM-DD HH24:MI:SS' )
DIA-48260: Function to_date not found

Mince, c’est pas du vrais SQL Oracle avec les fonctions qui vont bien !

adrci> delete from PROBLEM where lastinc_time < sysdate-60
DIA-48230: Expression arguments must match types
DIA-48249: Type mismatch - lhs = 7, rhs = 6, result = 8, op = 0

Mince, ça ne sait pas transtyper !

adrci> delete from PROBLEM where lastinc_time < systimestamp - 60

1 Row Deleted

adrci> show problems

ADR Home = /opt/oracle/diag/rdbms/dbun/DBNAME:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2                    ORA 600 [ktcccod-1]                                         3960130              2016-04-22 06:25:53.408000 +02:00
3                    ORA 600 [kdBlkCheckError]                                   8600040              2016-05-24 11:14:17.489000 +02:00
2 rows fetched

C’était pourtant pas si compliqué

Créer un package ADRCI

[oracle@serveur ~/work]$ adrci

ADRCI: Release 11.2.0.4.0 - Production on Thu Jan 7 14:14:24 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle"
adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/titi/titi:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/rdbms/serveur/DB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
208129               ORA 600 [ktcccod-1]                                         2016-01-05 06:45:45.367000 +01:00
592465               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:27:32.712000 +01:00
592481               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:29:22.188000 +01:00
592466               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:08:52.179000 +01:00
592457               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:11:18.073000 +01:00
592449               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:22:53.246000 +01:00
592467               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:57:21.733000 +01:00
592441               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:20:34.972000 +01:00
592442               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:36:02.718000 +01:00
592443               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:43:26.175000 +01:00



adrci> IPS CREATE PACKAGE PROBLEM 592443
DIA-48448: This command does not support multiple ADR homes


adrci> show homes
ADR Homes:
diag/rdbms/titi/titi
diag/rdbms/serveur/DB

adrci> SET HOME diag/rdbms/serveur/DB
adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/serveur/DB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
208129               ORA 600 [ktcccod-1]                                         2016-01-05 06:45:45.367000 +01:00
592465               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:27:32.712000 +01:00
592481               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:29:22.188000 +01:00
592466               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:08:52.179000 +01:00
592457               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:11:18.073000 +01:00
592449               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:22:53.246000 +01:00
592467               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:57:21.733000 +01:00
592441               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:20:34.972000 +01:00
592442               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:36:02.718000 +01:00
592443               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:43:26.175000 +01:00
10 rows fetched

adrci> IPS CREATE PACKAGE PROBLEMKEY "ORA 600 [kdBlkCheckError]"
Created package 1 based on problem key ORA 600 [kdBlkCheckError], correlation level typical

adrci> show problem

ADR Home = /opt/oracle/diag/rdbms/serveur/DB:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 600 [ktcccod-1]                                         208129               2016-01-05 06:45:45.367000 +01:00
2                    ORA 600 [kdBlkCheckError]                                   592443               2016-01-07 13:43:26.175000 +01:00
2 rows fetched

adrci> IPS ADD FILE /opt/oracle/diag/rdbms/serveur/DB/trace/DB_s001_217994.trc PACKAGE 2
DIA-49404: No such package [2]

adrci> IPS SHOW PACKAGE
   PACKAGE_ID             1
   PACKAGE_NAME           ORA600kdB_20160107142052
   PACKAGE_DESCRIPTION
   DRIVING_PROBLEM        2
   DRIVING_PROBLEM_KEY    ORA 600 [kdBlkCheckError]
   DRIVING_INCIDENT       592465
   DRIVING_INCIDENT_TIME  2016-01-07 11:27:32.712000 +01:00
   STATUS                 New (0)
   CORRELATION_LEVEL      Typical (2)
   PROBLEMS               1 main problems, 1 correlated problems
   INCIDENTS              6 main incidents, 2 correlated incidents
   INCLUDED_FILES         41

adrci> IPS GENERATE PACKAGE 1 IN /opt/oracle/work
Generated package 1 in file /opt/oracle/work/ORA600kdB_20160107142052_COM_1.zip, mode complete
adrci> exit

system state dump

Lancer un system state dump

Ma base est bloquée et je ne sais pas pourquoi … J’ouvre une SR, et forcé”ment pour qu’il y ait un peu de répondant il faut que j’envoie un hanganalyze et un systemstate dump. Les instructions :

connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- wait 90 seconds
oradebug hanganalyze 3
oradebug dump systemstate 266
-- wait 90 seconds
oradebug dump systemstate 266
-- wait 90 seconds
oradebug dump systemstate 266

C’était pourtant pas si compliqué