[Oracle10g] d'où viennent tous ces fichiers trace dans udump ?

**pulcopulpo** · 06/07/2010, 14h14

Bonjour,

J'administre une bdd de production Oracle 10.1.0.3.0, et je suis régulièrement (pratiquement chaque semaine) obligé d'effacer tous les fichiers trace du répertoire user_dump_dest.

La particularité de mon problème, c'est qu'aucune erreur ORA n'apparait dans mon alert_$SID.log.

Ces fichiers se recréent automatiquement après avoir été effacés, et leur taille augmente rapidement. Une semaine après avoir vidé udump, je me retrouve avec un répertoire de 9 Go !

Voici un exemple de trace file généré:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
/u01/app/oracle/admin/prod/udump/prod_ora_18396.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/10.1.0/prod
System name:    Linux
Node name:      mercedes
Release:        2.6.5-7.244-smp
Version:        #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine:        x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 115
Unix process pid: 18396, image: oracleprod@mercedes
 
*** SERVICE NAME:(prod) 2010-07-05 12:13:08.220
*** SESSION ID:(110.53793) 2010-07-05 12:13:08.220
Before in kgllkdl(), hd=dbf32a70.
Before in kgllkdl(), hd=dabb94c8.
Before in kgllkdl(), hd=dabc7a38.
Before in kgllkdl(), hd=b6990fe0.
/u01/app/oracle/admin/prod/udump/prod_ora_18396.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/10.1.0/prod
System name:    Linux
Node name:      mercedes
Release:        2.6.5-7.244-smp
Version:        #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine:        x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 79
Unix process pid: 18396, image: oracleprod@mercedes
 
*** SERVICE NAME:(prod) 2010-07-05 12:26:19.605
*** SESSION ID:(115.50683) 2010-07-05 12:26:19.605
Before in kgllkdl(), hd=dbf32a70.
Before in kgllkdl(), hd=dabb94c8.
Before in kgllkdl(), hd=dabc7a38.
Before in kgllkdl(), hd=b168d3a0.
/u01/app/oracle/admin/prod/udump/prod_ora_18396.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/10.1.0/prod
System name:    Linux
Node name:      mercedes
Release:        2.6.5-7.244-smp
Version:        #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine:        x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 125
Unix process pid: 18396, image: oracleprod@mercedes
 
*** SERVICE NAME:(prod) 2010-07-05 12:42:13.793
*** SESSION ID:(222.45362) 2010-07-05 12:42:13.793
Before in kgllkdl(), hd=dbf32a70.
Before in kgllkdl(), hd=dabb94c8.
Before in kgllkdl(), hd=dabc7a38.
Before in kgllkdl(), hd=b5f6ca50.
/u01/app/oracle/admin/prod/udump/prod_ora_18396.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/10.1.0/prod
System name:    Linux
Node name:      mercedes
Release:        2.6.5-7.244-smp
Version:        #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine:        x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 92
Unix process pid: 18396, image: oracleprod@mercedes
 
*** SERVICE NAME:(prod) 2010-07-05 12:56:47.875
*** SESSION ID:(235.18015) 2010-07-05 12:56:47.875
Before in kgllkdl(), hd=dbf32a70.
Before in kgllkdl(), hd=dabb94c8.
Before in kgllkdl(), hd=dabc7a38.
Before in kgllkdl(), hd=cf883948.
/u01/app/oracle/admin/prod/udump/prod_ora_18396.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/10.1.0/prod
System name:    Linux
Node name:      mercedes
Release:        2.6.5-7.244-smp
Version:        #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine:        x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 122
Unix process pid: 18396, image: oracleprod@mercedes
 
*** SERVICE NAME:(prod) 2010-07-05 13:15:32.753
*** SESSION ID:(103.64883) 2010-07-05 13:15:32.753
Before in kgllkdl(), hd=dbf32a70.
Before in kgllkdl(), hd=dabb94c8.
Before in kgllkdl(), hd=dabc7a38.
Before in kgllkdl(), hd=c720a970.
/u01/app/oracle/admin/prod/udump/prod_ora_18396.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/10.1.0/prod
System name:    Linux
Node name:      mercedes
Release:        2.6.5-7.244-smp
Version:        #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine:        x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 116
Unix process pid: 18396, image: oracleprod@mercedes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SQL> show parameter trace
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace                    integer     0                             
sql_trace                            boolean     FALSE                         
trace_enabled                        boolean     TRUE                          
tracefile_identifier                 string

Les paramètres de la base:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
SQL> show parameter
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE                         
__db_cache_size                      big integer 992M                          
__java_pool_size                     big integer 16M                           
__large_pool_size                    big integer 16M                           
__shared_pool_size                   big integer 1008M                         
active_instance_count                integer                                   
aq_tm_processes                      integer     1                             
archive_lag_target                   integer     0                             
asm_diskgroups                       string                                    
asm_diskstring                       string                                    
asm_power_limit                      integer     1                             
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/product/10.1.0
                                                 /prod/rdbms/audit             
audit_sys_operations                 boolean     FALSE                         
audit_trail                          string      TRUE                          
background_core_dump                 string      partial                       
background_dump_dest                 string      /u01/app/oracle/admin/prod/bdu
                                                 mp                            
backup_tape_io_slaves                boolean     FALSE                         
bitmap_merge_area_size               integer     1048576                       
blank_trimming                       boolean     FALSE                         
buffer_pool_keep                     string                                    
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string                                    
circuits                             integer                                   
cluster_database                     boolean     FALSE                         
cluster_database_instances           integer     1                             
cluster_interconnects                string                                    
commit_point_strength                integer     1                             
compatible                           string      10.1.0.2.0                    
control_file_record_keep_time        integer     7                             
control_files                        string      +PDATA/prod/controlfile/contro
                                                 l01.ctl, +PDATA/prod/controlfi
                                                 le/control02.ctl, +PDATA/prod/
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
                                                 controlfile/control03.ctl     
core_dump_dest                       string      /u01/app/oracle/admin/prod/cdu
                                                 mp                            
cpu_count                            integer     4                             
create_bitmap_area_size              integer     8388608                       
create_stored_outlines               string                                    
cursor_sharing                       string      EXACT                         
cursor_space_for_time                boolean     FALSE                         
db_16k_cache_size                    big integer 0                             
db_2k_cache_size                     big integer 0                             
db_32k_cache_size                    big integer 0                             
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size                     big integer 0                             
db_8k_cache_size                     big integer 0                             
db_block_buffers                     integer     0                             
db_block_checking                    boolean     FALSE                         
db_block_checksum                    boolean     TRUE                          
db_block_size                        integer     8192                          
db_cache_advice                      string      ON                            
db_cache_size                        big integer 0                             
db_create_file_dest                  string      +PDATA                        
db_create_online_log_dest_1          string                                    
db_create_online_log_dest_2          string                                    
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_3          string                                    
db_create_online_log_dest_4          string                                    
db_create_online_log_dest_5          string                                    
db_domain                            string                                    
db_file_multiblock_read_count        integer     16                            
db_file_name_convert                 string                                    
db_files                             integer     200                           
db_flashback_retention_target        integer     1440                          
db_keep_cache_size                   big integer 0                             
db_name                              string      prod                          
db_recovery_file_dest                string      /u03/app/oracle/flash_recovery
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
                                                 _area                         
db_recovery_file_dest_size           big integer 700G                          
db_recycle_cache_size                big integer 0                             
db_unique_name                       string      prod                          
db_writer_processes                  integer     1                             
dbwr_io_slaves                       integer     0                             
ddl_wait_for_locks                   boolean     FALSE                         
dg_broker_config_file1               string      /u01/app/oracle/product/10.1.0
                                                 /prod/dbs/dr1prod.dat         
dg_broker_config_file2               string      /u01/app/oracle/product/10.1.0
                                                 /prod/dbs/dr2prod.dat         
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE                         
disk_asynch_io                       boolean     TRUE                          
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=prodXD
                                                 B)                            
distributed_lock_timeout             integer     60                            
dml_locks                            integer     1232                          
drs_start                            boolean     FALSE                         
enqueue_resources                    integer     1452                          
event                                string                                    
fal_client                           string                                    
fal_server                           string                                    
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0                             
fast_start_mttr_target               integer     900                           
fast_start_parallel_rollback         string      LOW                           
file_mapping                         boolean     FALSE                         
fileio_network_adapters              string                                    
filesystemio_options                 string      none                          
fixed_date                           string                                    
gc_files_to_locks                    string                                    
gcs_server_processes                 integer     0                             
global_context_pool_size             string                                    
global_names                         boolean     FALSE                         
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hash_area_size                       integer     131072                        
hi_shared_memory_address             integer     0                             
hs_autoregister                      boolean     TRUE                          
ifile                                file                                      
instance_groups                      string                                    
instance_name                        string      prod                          
instance_number                      integer     0                             
instance_type                        string      RDBMS                         
java_max_sessionspace_size           integer     0                             
java_pool_size                       big integer 0                             
java_soft_sessionspace_limit         integer     0                             
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10                            
large_pool_size                      big integer 0                             
ldap_directory_access                string      NONE                          
license_max_sessions                 integer     0                             
license_max_users                    integer     0                             
license_sessions_warning             integer     0                             
local_listener                       string      PROD_LISTENER                 
lock_name_space                      string                                    
lock_sga                             boolean     TRUE                          
log_archive_config                   string                                    
log_archive_dest                     string                                    
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string                                    
log_archive_dest_10                  string                                    
log_archive_dest_2                   string                                    
log_archive_dest_3                   string                                    
log_archive_dest_4                   string                                    
log_archive_dest_5                   string                                    
log_archive_dest_6                   string                                    
log_archive_dest_7                   string                                    
log_archive_dest_8                   string                                    
log_archive_dest_9                   string                                    
log_archive_dest_state_1             string      enable                        
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_10            string      enable                        
log_archive_dest_state_2             string      enable                        
log_archive_dest_state_3             string      enable                        
log_archive_dest_state_4             string      enable                        
log_archive_dest_state_5             string      enable                        
log_archive_dest_state_6             string      enable                        
log_archive_dest_state_7             string      enable                        
log_archive_dest_state_8             string      enable                        
log_archive_dest_state_9             string      enable                        
log_archive_duplex_dest              string                                    
log_archive_format                   string      %t_%s_%r.dbf                  
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_local_first              boolean     TRUE                          
log_archive_max_processes            integer     2                             
log_archive_min_succeed_dest         integer     1                             
log_archive_start                    boolean     FALSE                         
log_archive_trace                    integer     0                             
log_buffer                           integer     1048576                       
log_checkpoint_interval              integer     0                             
log_checkpoint_timeout               integer     1800                          
log_checkpoints_to_alert             boolean     FALSE                         
log_file_name_convert                string                                    
logmnr_max_persistent_sessions       integer     1                             
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_commit_propagation_delay         integer     700                           
max_dispatchers                      integer                                   
max_dump_file_size                   string      UNLIMITED                     
max_enabled_roles                    integer     150                           
max_shared_servers                   integer                                   
nls_calendar                         string                                    
nls_comp                             string                                    
nls_currency                         string                                    
nls_date_format                      string                                    
nls_date_language                    string                                    
nls_dual_currency                    string                                    
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_iso_currency                     string                                    
nls_language                         string      AMERICAN                      
nls_length_semantics                 string      BYTE                          
nls_nchar_conv_excp                  string      FALSE                         
nls_numeric_characters               string                                    
nls_sort                             string                                    
nls_territory                        string      AMERICA                       
nls_time_format                      string                                    
nls_time_tz_format                   string                                    
nls_timestamp_format                 string                                    
nls_timestamp_tz_format              string                                    
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
object_cache_max_size_percent        integer     10                            
object_cache_optimal_size            integer     102400                        
olap_page_pool_size                  big integer 0                             
open_cursors                         integer     300                           
open_links                           integer     4                             
open_links_per_instance              integer     4                             
optimizer_dynamic_sampling           integer     2                             
optimizer_features_enable            string      10.1.0.3                      
optimizer_index_caching              integer     0                             
optimizer_index_cost_adj             integer     100                           
optimizer_mode                       string      ALL_ROWS                      
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$                          
os_roles                             boolean     FALSE                         
parallel_adaptive_multi_user         boolean     TRUE                          
parallel_automatic_tuning            boolean     FALSE                         
parallel_execution_message_size      integer     2152                          
parallel_instance_group              string                                    
parallel_max_servers                 integer     80                            
parallel_min_percent                 integer     0                             
parallel_min_servers                 integer     0                             
parallel_server                      boolean     FALSE                         
parallel_server_instances            integer     1                             
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu             integer     2                             
pga_aggregate_target                 big integer 1200M                         
plsql_code_type                      string      INTERPRETED                   
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG        
plsql_debug                          boolean     FALSE                         
plsql_native_library_dir             string                                    
plsql_native_library_subdir_count    integer     0                             
plsql_optimize_level                 integer     2                             
plsql_v2_compatibility               boolean     FALSE                         
plsql_warnings                       string      DISABLE:ALL                   
pre_page_sga                         boolean     FALSE                         
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     250                           
query_rewrite_enabled                string      TRUE                          
query_rewrite_integrity              string      enforced                      
rdbms_server_dn                      string                                    
read_only_open_delayed               boolean     FALSE                         
recovery_parallelism                 integer     0                             
remote_archive_enable                string      true                          
remote_dependencies_mode             string      TIMESTAMP                     
remote_listener                      string                                    
remote_login_passwordfile            string      EXCLUSIVE                     
remote_os_authent                    boolean     FALSE                         
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_roles                      boolean     FALSE                         
replication_dependency_tracking      boolean     TRUE                          
resource_limit                       boolean     FALSE                         
resource_manager_plan                string                                    
resumable_timeout                    integer     0                             
rollback_segments                    string                                    
serial_reuse                         string      disable                       
service_names                        string      prod                          
session_cached_cursors               integer     50                            
session_max_open_files               integer     10                            
sessions                             integer     280                           
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 2G
sga_target                           big integer 2G
shadow_core_dump                     string      PARTIAL
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 52848230
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       integer     1
skip_unusable_indexes                boolean     TRUE
smtp_out_server                      string
sort_area_retained_size              integer     0
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536
sp_name                              string      prod
spfile                               string      +PDATA/prod/spfileprod.ora
sql92_security                       boolean     FALSE
sql_trace                            boolean     FALSE
sql_version                          string      NATIVE
sqltune_category                     string      DEFAULT
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
star_transformation_enabled          string      FALSE
statistics_level                     string      TYPICAL
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0
tape_asynch_io                       boolean     TRUE
thread                               integer     0
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
trace_enabled                        boolean     TRUE
tracefile_identifier                 string
transactions                         integer     308
transactions_per_rollback_segment    integer     5
undo_management                      string      AUTO
undo_retention                       integer     900
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1
use_indirect_data_buffers            boolean     FALSE
user_dump_dest                       string      /u01/app/oracle/admin/prod/udu
                                                 mp
utl_file_dir                         string
workarea_size_policy                 string      AUTO

Est-il possible qu'une application cliente connectée à la base active un de ces paramètres trace pour chaque session ouverte ? C'est la seule explication que je vois pour le moment...