Be aware of "_disable_logging = TRUE" parameter

Tuesday, March 31, 2009

First to say - do not use "_disable_logging=TRUE" to speed up import!!

Usage of undocumented parameters without Oracle support is not best practice and I don't recommend it to anyone.

Nevertheless I decided to use "_disable_logging=TRUE" to speed up one db import. I've made full export of two schemas on Oracle 9.2.0.6.0 database and planed to import these files to Oracle 10.2.0.4.0 database. As the whole operation wasn't critical and I could easily go back to prior (working) situation so I decided to turn off archiving and enable on _disable_logging on 10.2.0.4.0 db.



From my previous posts:

I've seen that some DBA's use NOLOGGING or _disable_logging=TRUE parameter to speed up import. You should be very careful about this cause if instance crashes when these parameters are enabled (during Import) your database could suffer serious damage. If you still decide to use nologging - take cold backup and be sure that youre restore works.


I thought that I'll take that risk of possible instance crash and use undocumented parameter.

With _disable_logging=TRUE LGWR does everything normally except it omits performing I/O for redo log flush. So after import I must shutdown normal to have my database recoverable.


Import went without any error.
I've made shutdown normal, disabled _disable_logging=TRUE parameter, turn on archiving and opened database.

Shortly after error came up in alertlog:
ORA-00354: corrupt redo log block header
with
ORA-16038: log 1 sequence# 2530 cannot be archived


I had a serious problem with corruption of redo logs and my archiving was not working.


To resolve problem I had to clean corrupted redo log "ALTER DATABASE CLEAR LOGFILE..." and then everything worked again.
If this was critical database taking cold backup would be my next step, but this time I just prolonged migration and returned to prior situation.

Next time I will surly use ONLY documented parameters and will be very very suspicious about some blogs and Oracle web pages.

You can easily find several sites where _disable_logging=TRUE is said that improves import performance and that it is safe to use with some dosage of risk (instance ubnormal shutdown or possible crash). But this small speed up benefit is not worth of that risk.
As you can se ubnormal shutdown and instance crash are not all possible problems that can happen.


Share/Bookmark

2 comments:

  • Yes I had also same problem and raised a SR3 with oracle and finally this issue had fix after 7months :).

  • Marko Sutic

    After 7 months?! What to say - quick problem resolution ;-)

    Cheers!

  • Post a Comment