Why not to use ‘_disable_logging’=true
April 15, 2008Do 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.
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
ALTER SYSTEM SET _disable_logging=TRUE SCOPE=BOTH;
Fri Apr 11 12:31:29 2008
ALTER SYSTEM SET _disable_logging=FALSE SCOPE=BOTH;
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
ALTER DATABASE CLEAR UNARCHIVED LOG GROUP 22
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 9.2.0.8) 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.
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.
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.
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.
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
John
I still don’t think you have pointed out here clearly that if you BOOT with this value you ***don’t*** have a problem.