Error message: ORA-48913

[Brian Hogantara]

Hi erman,

in my database alert.log i get the message error like this

Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...

in SR (doc id 1153040.1) says that i must increased size MAX_DUMP_FILE_SIZE

but, i want to ask you is there any way to stop the trace file growth? as i know the trace file will only shows when you do "help->diagnostic->trace->trace with binds" and stop when you change to -> "no trace"
or the trace can make file automatically

[ErmanArslansOracleBlog]

First you need to investigate the root cause of those trace files.
Check the contents of one of those trace files.. What do you see?
Is it a regular trace file or is it a trace file that was created because of an error?
if it is a regular trace file, check contents a little bit more deeply.
See if it is related with a debuging action ? (EBS debug, Concurrent Manager trace etc...) then find the switch to turn of that trace generation.
If it is related with an error, then  check that error, try to fix it. If that error is ignorable, check oracle support to find a way to make Oracle not produce those traces.

The general approach that I used for solving this kind of a problem is above.

I hope it will help you.

[Brian Hogantara]

i believe it is a regular trace file. because we dont have any issue about any error that must be needed to running trace file.

could you please name it those contents "file name"?
because in my alert_DEVELOPMENT.log only repeated the message error like this :
Non critical error ORA-48913 caught while writing to trace file

"/u01/DEVELOPMENT/db/tech_st/11.2.0/admin/DEVELOPMENT_development-2/diag/rdbms/development/DEVELOPMENT/trace/DEVELOPMENT_ora_29179.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...
Non critical error ORA-48913 caught while writing to trace file "/u01/DEVELOPMENT/db/tech_st/11.2.0/admin/DEVELOPMENT_development-2/diag/rdbms/development/DEVELOPMENT/trace/DEVELOPMENT_ora_30241.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...

and size from database tier (db  folder) getting bigger every day and so on

[ErmanArslansOracleBlog]

Did you check the contents of your trace files?

For ex, this one -> /u01/DEVELOPMENT/db/tech_st/11.2.0/admin/DEVELOPMENT_development-2/diag/rdbms/development/DEVELOPMENT/trace/DEVELOPMENT_ora_29179.trc

What is written inside of it? What does it look like? Does it tell you anything? Why is this trace produced? Can you tell its by looking at its contents? Is it because of an error or what?

For ex: There are issues like this -> Quickly increasing trace files containing "FND_TRACE.START_TRACE", Causing Ora-48913 (Doc ID 1281069.1)

I don' directly tell you the above document addresses your issue, but you need to follow a similar approach.

Increasing the trace file limit is not a solution for this, as you say : Filesystem used space is increasing..

So you need to find the cause and make oracle stop producing these traces..

First, you need to answer the above questions..

You should also check if you have enabled any trace/debug in your application tier. Enabling sql trace in application layer also makes Oracle produce sql traces..

[Brian Hogantara]

ok,

in DEVELOPMENT_ora_29179.trc contain trace information like this:

----------------------------------------------------------------------------
2018-05-23 07:15:20.164876 : --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
2018-05-23 07:15:20.165989 : New trace stream is /u01/DEVELOPMENT/db/tech_st/11.2.0/admin/DEVELOPMENT_development-2/diag/rdbms/DEVELOPMENT/DEVELOPMENT/trace/DEVELOPMENT_ora_29179.trc
2018-05-23 07:15:20.166020 : New trace level is 16
2018-05-23 07:15:20.166044 : --- TRACE CONFIGURATION INFORMATION ENDS ---
2018-05-23 07:15:20.166067 : --- PARAMETER SOURCE INFORMATION FOLLOWS ---
2018-05-23 07:15:20.166123 : Attempted load of system pfile source /u01/DEVELOPMENT/db/tech_st/11.2.0/network/admin/DEVELOPMENT_development-2/sqlnet.ora
2018-05-23 07:15:20.166147 : Parameter source loaded successfully
2018-05-23 07:15:20.166169 :
2018-05-23 07:15:20.166190 :  -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
2018-05-23 07:15:20.166212 : Successful parameter table load
2018-05-23 07:15:20.166233 :  -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2018-05-23 07:15:20.166257 :   SQLNET.EXPIRE_TIME = 10
2018-05-23 07:15:20.166278 :   TRACE_LEVEL_SERVER = SUPPORT
2018-05-23 07:15:20.166299 :   TRACE_LEVEL_CLIENT = SUPPORT
-------------------------------------------------------------------------------

open the sqlnet.ora try to see what is written inside and compare it from production
and found a different value from production
on Development there is value like this :
++++++++++++++++++++++++
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_LEVEL_SERVER = SUPPORT
++++++++++++++++++++++++
whether this can be one cause of error?

already check application tier and i'm sure enough that trace file has been disabled from application layer. you right about "Enabling sql trace in application layer also makes Oracle produce sql traces" and there's the message error created.

[ErmanArslansOracleBlog]

Yes, this may be your issue.

Who did this? and why? (probably investigating a connection problem)

Anyways, revert this setting and see whether your problem dissapears or not.

[Brian Hogantara]

i'm not sure who did this. maybe there is some back process step who can create the issue like this on database tier or application tier.

by the way, an error is gone since i change value on sqlnet.ora from :

TRACE_LEVEL_CLIENT = SUPPORT
TRACE_LEVEL_SERVER = SUPPORT

to :

DIAG_ADR_ENABLED=ON

and bounce application & database tier.

Thanks, erman

[Brian Hogantara]

not really gone, actually, an error still reproduce

only growth from trace file is not fastest and not biggest as before

Non critical error ORA-48913 caught while writing to trace file "/u01/DEVELOPMENT/db/tech_st/11.2.0/admin/DEVELOPMENT_development-2/diag/rdbms/development/DEVELOPMENT/trace/DEVELOPMENT_m000_59593.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...

[ErmanArslansOracleBlog]

You know the meaning of this error right?

It means "I want to to trace, but I reached the limit."

ORA-48913: Writing into trace file failed, file size limit [10485760]

Again, analyze the trace and check if there is any errors an anomalies there.. But your limit is actually low.

You can increase this limit ->

Increase the setting for the parameter MAX_DUMP_FILE_SIZE or set it to unlimited
For additional information on this parameter, refer to  Oracle Database Reference 11g Release 2 (11.2) MAX_DUMP_FILE_SIZE