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 http://kevinclosson.wordpress.com/2007/07/21/manly-men-only-use-solid-state-disk-for-redo-logging-lgwr-io-is-simple-but-not-lgwr-processing/
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.
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 18.104.22.168) and 10g (fixed in 10.2.0.1). 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.