Pessoal, eu estou montando (para um cliente usando RDBMS 10gR2, então sem
chance de molezinhas como ADR agrupando por Incidente ou X$DBGALERTEXT) uma
rotina de verificação de alert.log - para a pesquisa/leitura do alert.log
usarei a técnica comum da external table (exemplo abaixo), que entre outras
vantagens permite fácil acoplamento em qquer tool de Reporting, mas a
análise/ordenação/agrupamento da informação eu pretendia fazer via query
também, então por isso usei & abusei de Analytics ...
Vou reportar até onde cheguei, e aonde pretendo chegar, com a intenção de
receber os sempre bem-vindos Comentários de todos :
primeiro, terei um arquivo de alert.log a ser lido , criarei uma external
table para isso :
SYSTEM:@O11201:SQL>host dir C:\users\jose.chiappa\alert_exemplo.log
Pasta de C:\users\jose.chiappa
03/02/2015 15:31 7.371 alert_exemplo.log
1 arquivo(s) 7.371 bytes
0 pasta(s) 267.606.016.000 bytes disponíveis
SYSTEM:@O11201:SQL>create directory DIR_ALERT as 'C:\users\jose.chiappa';
Diretório criado.
SYSTEM:@O11201:SQL>CREATE TABLE ALERT_LOG_EXT
2 (TEXT VARCHAR2(255)
3 ) ORGANIZATION EXTERNAL
4 (TYPE ORACLE_LOADER
5 DEFAULT DIRECTORY DIR_ALERT
6 ACCESS PARAMETERS
7 (records delimited by newline
8 nobadfile
9 nologfile
10 )
11 LOCATION ('alert_exemplo.log')
12 )
13* REJECT LIMIT UNLIMITED;
Tabela criada.
==> os dados brutos :
SYSTEM:@O11201:SQL>select rownum, text from alert_log_ext;
ROWNUM TEXT
------
-------------------------------------------------------------------------------------------------------------------------------------
1 Thu Oct 23 13:44:07 2014
2 Starting ORACLE instance (normal)
3 LICENSE_MAX_SESSION = 0
4 LICENSE_SESSIONS_WARNING = 0
5 Shared memory segment for instance monitoring created
6 Picked latch-free SCN scheme 3
7 Using LOG_ARCHIVE_DEST_1 parameter default value as
USE_DB_RECOVERY_FILE_DEST
8 Autotune of undo retention is turned on.
9 IMODE=BR
10 ILAT =27
11 LICENSE_MAX_USERS = 0
12 SYS auditing is disabled
13 Starting up:
14 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
15 With the Partitioning
16 Using parameter settings in client-side pfile
C:\APP\ORACLE\ADMIN\O11201\PFILE\INIT.ORA on machine MEUPC
17 System parameters with non-default values:
18 processes = 150
19 memory_target = 3248M
20 control_files = "C:\APP\ORACLE\ORADATA\O11201\CONTROL01.CTL"
21 control_files =
"C:\APP\ORACLE\FLASH_RECOVERY_AREA\O11201\CONTROL02.CTL"
22 db_block_size = 8192
23 compatible = "11.2.0.0.0"
24 db_recovery_file_dest = "C:\app\oracle\flash_recovery_area"
25 db_recovery_file_dest_size= 3912M
26 undo_tablespace = "UNDOTBS1"
27 remote_login_passwordfile= "EXCLUSIVE"
28 db_domain = "empresa.com.br"
29 dispatchers = "(PROTOCOL=TCP) (SERVICE=o11201XDB)"
30 local_listener = "LISTENER_O11201"
31 audit_file_dest = "C:\APP\ORACLE\ADMIN\O11201\ADUMP"
32 audit_trail = "DB"
33 db_name = "o11201"
34 open_cursors = 300
35 diagnostic_dest = "C:\APP\ORACLE"
36
37 Thu Oct 23 13:44:08 2014
38 PMON started with pid=2
39
40 Mon Jan 26 10:24:46 2015
41 Thread 1 cannot allocate new log
42 Private strand flush not complete
43 Current log# 2 seq# 65 mem# 0: C:\APP\ORACLE\ORADATA\O11201\REDO02.LOG
44 Thread 1 advanced to log sequence 66 (LGWR switch)
45 Current log# 3 seq# 66 mem# 0: C:\APP\ORACLE\ORADATA\O11201\REDO03.LOG
46
47 Fri Jan 30 00:00:42 2015
48 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_12900.trc:
49 ORA-12012: error on auto execute of job 288346
50 ORA-29278: SMTP transient error: ORA-29278: SMTP transient error: 421
Service not available
51
52 Fri Jan 30 00:10:43 2015
53 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_70292.trc:
54 ORA-12012: error on auto execute of job 288346
55 ORA-29278: SMTP transient error: ORA-29278: SMTP transient error: 421
Service not available
56
57 Fri Jan 30 02:45:20 2015
58 The value (30) of MAXTRANS parameter ignored.
59 kupprdp: master process DM00 started with pid=82
60 to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_31'
61 kupprdp: worker process DW01 started with worker id=1
62 to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_31'
63
64 Fri Jan 30 06:02:07 2015
65 The value (30) of MAXTRANS parameter ignored.
66 kupprdp: master process DM00 started with pid=53
67 to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_31'
68 kupprdp: worker process DW01 started with worker id=1
69 to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_31'
70
71 Fri Jan 30 16:40:57 2015
72 WARNING: inbound connection timed out (ORA-3136)
73
74 Fri Jan 30 16:41:05 2015
75 WARNING: inbound connection timed out (ORA-3136)
76
77 Sat Jan 31 00:50:31 2015
78 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_170620.trc:
79 ORA-12012: error on auto execute of job 226550
80 ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect
function.
81
82 Sat Jan 31 01:35:40 2015
83 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j002_42240.trc:
84 ORA-12012: error on auto execute of job 226550
85 ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect
function.
86
87 Sat Jan 31 06:13:13 2015
88 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_dw01_50528.trc:
89 ORA-00600: internal error code
90 ORA-19502: write error on file
"X:/Oracle/Logico\PROD_expdp_PROD_20153101_245.dmp"
91 ORA-27072: File I/O error
92 OSD-04008: WriteFile() failure
93 O/S-Error: (OS 112) There is not enough space on the disk.
94
95 Sat Jan 31 06:13:33 2015
96 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_dw01_50528.trc:
97 ORA-00600: internal error code
98 ORA-19502: write error on file
"X:/Oracle/Logico\PROD_expdp_PROD_20153101_245.dmp"
99 ORA-27072: File I/O error
100 OSD-04008: WriteFile() failure
101 O/S-Error: (OS 112) There is not enough space on the disk.
102
103 Sat Jan 31 07:00:09 2015
104 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_44248.trc:
105 ORA-12012: error on auto execute of job 288344
106 ORA-06502: PL/SQL: numeric or value errorORA-06502: PL/SQL: numeric or
value error
107 ORA-06512: at "TESTEPRD.SP_JOB_ANALISES_EM_ESPERA"
108 ORA-06512: at line 2
109
110 Mon Feb 02 21:38:25 2015
111
112
113 ***********************************************************************
114
115 Fatal NI connect error 12537
116 (LOCAL=NO)
117
118 VERSION INFORMATION:
119 TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
120 Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version
11.2.0.1.0 - Production
121 Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version
11.2.0.1.0 - Production
122 Time: 02-FEV-2015 21:38:29
123 Tracing not turned on.
124 Tns error struct:
125 ns main err code: 12537
126
127 TNS-12537: TNS:conexĂŁo fechada
128 ns secondary err code: 12560
129 nt main err code: 0
130 nt secondary err code: 0
131 nt OS err code: 0
132 opiodr aborting process unknown ospid (5676) as a result of ORA-609
133
134 Mon Feb 02 22:00:00 2015
135 Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN
via scheduler window
136 Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
137
138 Mon Feb 02 22:01:01 2015
139 Thread 1 cannot allocate new log
140 Private strand flush not complete
141 Current log# 1 seq# 73 mem# 0: C:\APP\ORACLE\ORADATA\O11201\REDO01.LOG
142 Thread 1 advanced to log sequence 74 (LGWR switch)
143 Current log# 2 seq# 74 mem# 0: C:\APP\ORACLE\ORADATA\O11201\REDO02.LOG
144
145 Tue Feb 03 11:29:18 2015
146 Shutting down instance (immediate)
147 Stopping background process SMCO
148 Shutting down instance: further logons disabled
149 Stopping background process QMNC
150
151 Tue Feb 03 11:29:19 2015
152 Stopping background process CJQ0
153 Stopping background process MMNL
154 Stopping background process MMON
155 License high water mark = 32
156 All dispatchers and shared servers shutdown
157 alter database close normal
158
159 Tue Feb 03 11:29:32 2015
160 Instance shutdown complete
160 linhas selecionadas.
SYSTEM:@O11201:SQL>
==> A primeira dificuldade é que a DATA da ocorrência não é repetida nas linhas
de baixo (as linhas de detalhe), então faço isso com LEAD, e quero também
eliminar linhas em branco E as linhas que contém a data do evento :
SYSTEM:@O11201:SQL>select sysdate from dual;
SYSDATE
-------------------
04/02/2015 14:57:53
SYSTEM:@O11201:SQL>select LINENO, THEDATE, trunc(thedate) DIA_DO_ERRO,
ora_message_line from (
2 select lineno, max(thedate) over (order by lineno) thedate,
3 lead(text) over (order by lineno) ora_message_line
4 from (select rownum lineno, substr( text, 1, 132 ) text,
5 case when text like '___ ___ __ __:__:__ ____%'
6 then to_date(substr(text,1,24), 'Dy Mon DD
hh24:mi:ss YYYY', 'NLS_DATE_LANGUAGE=AMERICAN' )
7 else null end thedate
8 from alert_log_ext
9 )
10 )
11 where ora_message_line not like '___ ___ __ __:__:__ ____%' and
ltrim(rtrim(ora_message_line)) is not null
12 ;
LINENO THEDATE DIA_EVENTO ORA_MESSAGE_LINE
------ ------------------- ----------
----------------------------------------------------------------------------------------------------------------------------------
1 23/10/2014 13:44:07 23/10/2014 Starting ORACLE instance (normal)
2 23/10/2014 13:44:07 23/10/2014 LICENSE_MAX_SESSION = 0
3 23/10/2014 13:44:07 23/10/2014 LICENSE_SESSIONS_WARNING = 0
4 23/10/2014 13:44:07 23/10/2014 Shared memory segment for instance
monitoring created
5 23/10/2014 13:44:07 23/10/2014 Picked latch-free SCN scheme 3
6 23/10/2014 13:44:07 23/10/2014 Using LOG_ARCHIVE_DEST_1 parameter
default value as USE_DB_RECOVERY_FILE_DEST
7 23/10/2014 13:44:07 23/10/2014 Autotune of undo retention is turned on.
8 23/10/2014 13:44:07 23/10/2014 IMODE=BR
9 23/10/2014 13:44:07 23/10/2014 ILAT =27
10 23/10/2014 13:44:07 23/10/2014 LICENSE_MAX_USERS = 0
11 23/10/2014 13:44:07 23/10/2014 SYS auditing is disabled
12 23/10/2014 13:44:07 23/10/2014 Starting up:
13 23/10/2014 13:44:07 23/10/2014 Oracle Database 11g Enterprise Edition
Release 11.2.0.1.0 - 64bit Production
14 23/10/2014 13:44:07 23/10/2014 With the Partitioning
15 23/10/2014 13:44:07 23/10/2014 Using parameter settings in client-side
pfile C:\APP\ORACLE\ADMIN\O11201\PFILE\INIT.ORA on machine MEUPC
16 23/10/2014 13:44:07 23/10/2014 System parameters with non-default values:
17 23/10/2014 13:44:07 23/10/2014 processes = 150
18 23/10/2014 13:44:07 23/10/2014 memory_target = 3248M
19 23/10/2014 13:44:07 23/10/2014 control_files =
"C:\APP\ORACLE\ORADATA\O11201\CONTROL01.CTL"
20 23/10/2014 13:44:07 23/10/2014 control_files =
"C:\APP\ORACLE\FLASH_RECOVERY_AREA\O11201\CONTROL02.CTL"
21 23/10/2014 13:44:07 23/10/2014 db_block_size = 8192
22 23/10/2014 13:44:07 23/10/2014 compatible = "11.2.0.0.0"
23 23/10/2014 13:44:07 23/10/2014 db_recovery_file_dest =
"C:\app\oracle\flash_recovery_area"
24 23/10/2014 13:44:07 23/10/2014 db_recovery_file_dest_size= 3912M
25 23/10/2014 13:44:07 23/10/2014 undo_tablespace = "UNDOTBS1"
26 23/10/2014 13:44:07 23/10/2014 remote_login_passwordfile= "EXCLUSIVE"
27 23/10/2014 13:44:07 23/10/2014 db_domain =
"empresa.com.br"
28 23/10/2014 13:44:07 23/10/2014 dispatchers =
"(PROTOCOL=TCP) (SERVICE=o11201XDB)"
29 23/10/2014 13:44:07 23/10/2014 local_listener =
"LISTENER_O11201"
30 23/10/2014 13:44:07 23/10/2014 audit_file_dest =
"C:\APP\ORACLE\ADMIN\O11201\ADUMP"
31 23/10/2014 13:44:07 23/10/2014 audit_trail = "DB"
32 23/10/2014 13:44:07 23/10/2014 db_name = "o11201"
33 23/10/2014 13:44:07 23/10/2014 open_cursors = 300
34 23/10/2014 13:44:07 23/10/2014 diagnostic_dest =
"C:\APP\ORACLE"
37 23/10/2014 13:44:08 23/10/2014 PMON started with pid=2
40 26/01/2015 10:24:46 26/01/2015 Thread 1 cannot allocate new log
41 26/01/2015 10:24:46 26/01/2015 Private strand flush not complete
42 26/01/2015 10:24:46 26/01/2015 Current log# 2 seq# 65 mem# 0:
C:\APP\ORACLE\ORADATA\O11201\REDO02.LOG
43 26/01/2015 10:24:46 26/01/2015 Thread 1 advanced to log sequence 66
(LGWR switch)
44 26/01/2015 10:24:46 26/01/2015 Current log# 3 seq# 66 mem# 0:
C:\APP\ORACLE\ORADATA\O11201\REDO03.LOG
47 30/01/2015 00:00:42 30/01/2015 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_12900.trc:
48 30/01/2015 00:00:42 30/01/2015 ORA-12012: error on auto execute of job
288346
49 30/01/2015 00:00:42 30/01/2015 ORA-29278: SMTP transient error:
ORA-29278: SMTP transient error: 421 Service not available
52 30/01/2015 00:10:43 30/01/2015 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_70292.trc:
53 30/01/2015 00:10:43 30/01/2015 ORA-12012: error on auto execute of job
288346
54 30/01/2015 00:10:43 30/01/2015 ORA-29278: SMTP transient error:
ORA-29278: SMTP transient error: 421 Service not available
57 30/01/2015 02:45:20 30/01/2015 The value (30) of MAXTRANS parameter
ignored.
58 30/01/2015 02:45:20 30/01/2015 kupprdp: master process DM00 started with
pid=82
59 30/01/2015 02:45:20 30/01/2015 to execute -
SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_31'
60 30/01/2015 02:45:20 30/01/2015 kupprdp: worker process DW01 started with
worker id=1
61 30/01/2015 02:45:20 30/01/2015 to execute -
SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_31'
64 30/01/2015 06:02:07 30/01/2015 The value (30) of MAXTRANS parameter
ignored.
65 30/01/2015 06:02:07 30/01/2015 kupprdp: master process DM00 started with
pid=53
66 30/01/2015 06:02:07 30/01/2015 to execute -
SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_31'
67 30/01/2015 06:02:07 30/01/2015 kupprdp: worker process DW01 started with
worker id=1
68 30/01/2015 06:02:07 30/01/2015 to execute -
SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_31'
71 30/01/2015 16:40:57 30/01/2015 WARNING: inbound connection timed out
(ORA-3136)
74 30/01/2015 16:41:05 30/01/2015 WARNING: inbound connection timed out
(ORA-3136)
77 31/01/2015 00:50:31 31/01/2015 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_170620.trc:
78 31/01/2015 00:50:31 31/01/2015 ORA-12012: error on auto execute of job
226550
79 31/01/2015 00:50:31 31/01/2015 ORA-27369: job of type EXECUTABLE failed
with exit code: Incorrect function.
82 31/01/2015 01:35:40 31/01/2015 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j002_42240.trc:
83 31/01/2015 01:35:40 31/01/2015 ORA-12012: error on auto execute of job
226550
84 31/01/2015 01:35:40 31/01/2015 ORA-27369: job of type EXECUTABLE failed
with exit code: Incorrect function.
87 31/01/2015 06:13:13 31/01/2015 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_dw01_50528.trc:
88 31/01/2015 06:13:13 31/01/2015 ORA-00600: internal error code
89 31/01/2015 06:13:13 31/01/2015 ORA-19502: write error on file
"X:/Oracle/Logico\PROD_expdp_PROD_20153101_245.dmp"
90 31/01/2015 06:13:13 31/01/2015 ORA-27072: File I/O error
91 31/01/2015 06:13:13 31/01/2015 OSD-04008: WriteFile() failure
92 31/01/2015 06:13:13 31/01/2015 O/S-Error: (OS 112) There is not enough
space on the disk.
95 31/01/2015 06:13:33 31/01/2015 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_dw01_50528.trc:
96 31/01/2015 06:13:33 31/01/2015 ORA-00600: internal error code
97 31/01/2015 06:13:33 31/01/2015 ORA-19502: write error on file
"X:/Oracle/Logico\PROD_expdp_PROD_20153101_245.dmp"
98 31/01/2015 06:13:33 31/01/2015 ORA-27072: File I/O error
99 31/01/2015 06:13:33 31/01/2015 OSD-04008: WriteFile() failure
100 31/01/2015 06:13:33 31/01/2015 O/S-Error: (OS 112) There is not enough
space on the disk.
103 31/01/2015 07:00:09 31/01/2015 Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_44248.trc:
104 31/01/2015 07:00:09 31/01/2015 ORA-12012: error on auto execute of job
288344
105 31/01/2015 07:00:09 31/01/2015 ORA-06502: PL/SQL: numeric or value
errorORA-06502: PL/SQL: numeric or value error
106 31/01/2015 07:00:09 31/01/2015 ORA-06512: at
"TESTEPRD.SP_JOB_ANALISES_EM_ESPERA"
107 31/01/2015 07:00:09 31/01/2015 ORA-06512: at line 2
112 02/02/2015 21:38:25 02/02/2015
***********************************************************************
114 02/02/2015 21:38:25 02/02/2015 Fatal NI connect error 12537
115 02/02/2015 21:38:25 02/02/2015 (LOCAL=NO)
117 02/02/2015 21:38:25 02/02/2015 VERSION INFORMATION:
118 02/02/2015 21:38:25 02/02/2015 TNS for 64-bit Windows: Version
11.2.0.1.0 - Production
119 02/02/2015 21:38:25 02/02/2015 Oracle Bequeath NT Protocol Adapter
for 64-bit Windows: Version 11.2.0.1.0 - Production
120 02/02/2015 21:38:25 02/02/2015 Windows NT TCP/IP NT Protocol
Adapter for 64-bit Windows: Version 11.2.0.1.0 - Production
121 02/02/2015 21:38:25 02/02/2015 Time: 02-FEV-2015 21:38:29
122 02/02/2015 21:38:25 02/02/2015 Tracing not turned on.
123 02/02/2015 21:38:25 02/02/2015 Tns error struct:
124 02/02/2015 21:38:25 02/02/2015 ns main err code: 12537
126 02/02/2015 21:38:25 02/02/2015 TNS-12537: TNS:conexĂŁo fechada
127 02/02/2015 21:38:25 02/02/2015 ns secondary err code: 12560
128 02/02/2015 21:38:25 02/02/2015 nt main err code: 0
129 02/02/2015 21:38:25 02/02/2015 nt secondary err code: 0
130 02/02/2015 21:38:25 02/02/2015 nt OS err code: 0
131 02/02/2015 21:38:25 02/02/2015 opiodr aborting process unknown ospid
(5676) as a result of ORA-609
134 02/02/2015 22:00:00 02/02/2015 Setting Resource Manager plan
SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window
135 02/02/2015 22:00:00 02/02/2015 Setting Resource Manager plan
DEFAULT_MAINTENANCE_PLAN via parameter
138 02/02/2015 22:01:01 02/02/2015 Thread 1 cannot allocate new log
139 02/02/2015 22:01:01 02/02/2015 Private strand flush not complete
140 02/02/2015 22:01:01 02/02/2015 Current log# 1 seq# 73 mem# 0:
C:\APP\ORACLE\ORADATA\O11201\REDO01.LOG
141 02/02/2015 22:01:01 02/02/2015 Thread 1 advanced to log sequence 74
(LGWR switch)
142 02/02/2015 22:01:01 02/02/2015 Current log# 2 seq# 74 mem# 0:
C:\APP\ORACLE\ORADATA\O11201\REDO02.LOG
145 03/02/2015 11:29:18 03/02/2015 Shutting down instance (immediate)
146 03/02/2015 11:29:18 03/02/2015 Stopping background process SMCO
147 03/02/2015 11:29:18 03/02/2015 Shutting down instance: further logons
disabled
148 03/02/2015 11:29:18 03/02/2015 Stopping background process QMNC
151 03/02/2015 11:29:19 03/02/2015 Stopping background process CJQ0
152 03/02/2015 11:29:19 03/02/2015 Stopping background process MMNL
153 03/02/2015 11:29:19 03/02/2015 Stopping background process MMON
154 03/02/2015 11:29:19 03/02/2015 License high water mark = 32
155 03/02/2015 11:29:19 03/02/2015 All dispatchers and shared servers
shutdown
156 03/02/2015 11:29:19 03/02/2015 alter database close normal
159 03/02/2015 11:29:32 03/02/2015 Instance shutdown complete
116 linhas selecionadas.
SYSTEM:@O11201:SQL>
=> aqui vem a dúvida : eu quero Agrupar os registros por dia E por evento,
ELIMINAR os grupos que não contiverem para a coluna ORA_MESSAGE_LINE uam string
like 'ORA-%' ou like 'TNS-%' ou '% cannot allocate%' (os grupos dos dia
23/10/2014 e 26/01/2015, por exemplo, caem nesse caso, não devendo ser
exibidos) , E para os outros grupos que possuem pelo menos uma ocorrência de
ORA- ou TNS- ou %cannot allocate%, exibir um report com todas as linhas do
grupo , contagem E data do evento, + ou - no formato :
No dia 30/01/2015, 1 Ocorrência(s) para :
em 30/01/2015 00:00:42
Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_12900.trc:
ORA-12012: error on auto execute of job 288346
ORA-29278: SMTP transient error: ORA-29278: SMTP transient error: 421
Service not available
No dia 30/01/2015, 1 Ocorrência(s) para :
em 30/01/2015 00:10:43
Errors in file
e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_70292.trc:
ORA-12012: error on auto execute of job 288346
ORA-29278: SMTP transient error: ORA-29278: SMTP transient error: 421
Service not available
(no caso acima há uma mínima diferença numa das linhas de detalhe, que é o nome
do .trc, então por isso não agrupou, considerou-se ocorrências à parte ), mas
continuando :
......
No dia 30/01/2015, 2 Ocorrência(s) para :
em 30/01/2015 16:40:57 WARNING: inbound connection timed out (ORA-3136)
em 30/01/2015 16:41:05 WARNING: inbound connection timed out (ORA-3136)
(como as linhas-detalhe são idênticas, agrupa-se), continuando :
....
No dia 31/01/2015, 1 Ocorrência para :
em 31/01/2015 07:00:09
Errors in file e:\oracle\product\10.2.0\admin\prod\bdump\prod_j000_44248.trc:
ORA-12012: error on auto execute of job 288344
ORA-06502: PL/SQL: numeric or value errorORA-06502: PL/SQL: numeric or value
error
ORA-06512: at "TESTEPRD.SP_JOB_ANALISES_EM_ESPERA"
ORA-06512: at line 2
e assim por diante.... Eu ACHO que um caminho poderia ser concatenar as
linhas-detalhe na query anterior (com um espacinho em branco no final de cada
uma), formando uma única stringona, e aí usar essa longa string como a Chave de
grupo secundária, mas não cheguei até aí ainda...
Quem tiver palpites a dar, serão bem vindos....
[]s
Chiappa
