Oracle DBA – A lifelong learning experience

Why not to use ‘_disable_logging’=true

Posted by John Hallas on April 15, 2008

Do not use _disable_logging=true in an 11G database and Oracle themselves state

Oracle does *NOT* support the use of _disable_logging=true
 but this parameter is sometimes used for bulk load operations.
 No customer system should be running with this parameter
 set as it totally invalidates any backup / recovery and 
 instance crash recovery options.

So why did I want to use the parameter? We were stress testing load through a RAC database using a solid-state disk and we were trying to determine whether we were CPU or I/O bound (we were pretty certain it was CPU). Following a very good article by Kevin Closson we followed his recommendation

Did you know that if you set the initialization parameter _disable_logging=TRUE LGWR does everything it normally does except it omits performing the I/O for a redo flush. Don’t do this if you like your database because anything other than a shutdown normal is going to leave your database unrecoverable. However, this is a great test because if performance doesn’t increase when you set this parameter in this fashion, then you have a LGWR I/O processing problem and not a LGWR I/O problem

So the test was first of all to ensure that I could set it and it was a dynamic parameter.

There was no data activity on the database at that time other than normal background processes. Looking at the alert log below you can see that the parameter was enabled for only 40 seconds and lo and behold, we get an ORA-353 log corruption near block 653 change 308225628 time 04/11/2008 12:30:57, which was the second before we enabled the parameter. Not a co-incidence.

Fri Apr 11 12:17:22 2008

Thread 1 advanced to log sequence 3176

Current log# 22 seq# 3176 mem# 0: +DATA/test/onlinelog/group_22.277.651745459

Fri Apr 11 12:30:58 2008


Fri Apr 11 12:31:29 2008


Fri Apr 11 12:32:09 2008

Thread 1 advanced to log sequence 3177

Current log# 23 seq# 3177 mem# 0: +DATA/test/onlinelog/group_23.279.651745507

Fri Apr 11 12:32:09 2008

ARCH: Log corruption near block 653 change 308225062 time ?

Errors in file /u00/app/oracle/diag/rdbms/test/TEST1/trace/TEST1_ora_14797.trc (incident=1065945):

ORA-00353: log corruption near block 653 change 308225062 time 04/11/2008 12:17:22

ORA-00312: online log 22 thread 1: ‘+DATA/test/onlinelog/group_22.277.651745459’

I managed to get around the issue by throwing away log 22 with the command


Over the next 2 days of testing we had the ORA-353 error every time we turned the parameter on but managed to get the database going each time, although once did require a full recovery.

It was a worthwhile exercise as it did confirm our suspicions that we were I/O bound.

Bugs are logged against this in both 9i (3868748, fixed in and 10g (fixed in Metalink document 391301.1 states that the corruption can occur in any version.

The executive summary is that the parameter can be useful, but only for a throwaway database

Note that it is safe to use this parameter if you stop and start the database after updating the init.ora or spfile. My issues were caused by altering the parameter dynamically.

7 Responses to “Why not to use ‘_disable_logging’=true”

  1. Set it in your init.ora. If you are issuing an alter system command you are talking to an instance that is already up (presumably with logging enabled)… I’m surprised it took 40 seconds to corrupt your database.

    In the end, I should not have presumed readers of my blog would have naturally set it at instance boot time.

  2. John Hallas said

    Thanks for the comment Kevin.

    If it is so obvious to set it at boot time why is it a dynamic parameter? TBH in the test above I was surprised it allowed me to set it, I expected to have to restart the database.

  3. John,

    It isn’t obvious. I’m re-reading my comment and to me that looks like I’m saying it is anything but obvious. As for dynamic, I’m surprised it even let you do that. I am reading the code (kcr) right now and to be honest, I’m surprised it didn’t immediately barf. But then, I’m just speed-reading the code as I do have my day job🙂

    Anyway, it is an underscore parameter and you never know what you are going to get with them. I do know this one thing for a fact: if you set it at boot time you will not see the problems you are seeing. Just make sure you do a normal shutdown when you are done…in fact, to be safe, do a couple of log switches, then shutdown normal.

  4. John Hallas said

    Cheers Kevin, we have finished with that testing now. I think we have proved we are CPU bound so we are getting the loan of a system with quad core’s to see how far we can push throughput.

    My reply seemed a bit aggressive on re-reading it, it was not meant to be. We are using a spfile and I did as I normally do, set scope=both and if it is not dynamic it barfs and I restart the instance.

    Thanks for taking the time to comment though


  5. I still don’t think you have pointed out here clearly that if you BOOT with this value you ***don’t*** have a problem.

  6. _disable_logging=true … why not?…

    A collegue of mine suggested me that using _disable_logging=true performs a very fast import during database migration with export/import technique. I’ve already heard in past this parameter but it’s not interested me till now. I can easily…

  7. Ravinder Bahadur said

    Hi ,

    I have heard that this parameter still makes redo entires , someone has suggested that make your redo size huge. How do I know how much redo size will be required.

    Thanks & Regards

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: