I ran into an interesting issue today with ORA-26040. I was using Data Pump to export/import from one database to another database. The export/import scripts (using parameter files) were developed by another team.
After import there were some issues so I decided to restore/recover. But when doing recover, I changed my mind and just recovered to the end with “recover database” instead of recovering to a point before the import with “recover database until“.
Everything seemed to be fine after I opened the database. However, when I was poking around, I noticed I was not able to login into a webpage which I knew the user information was stored in the database. Querying the table gave me the following error:
SQL> select id, login_name from web_user where user_name =''; select id, login_name from web_user where user_name =''; * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 89285) ORA-01110: data file 5: '/data/oracle/PROD/DATA_IND1.dbf' ORA-26040: Data block was loaded using the NOLOGGING option [oracle@joedb01 /tmp]$oerr ORA 26040 26040, 00000, "Data block was loaded using the NOLOGGING option\n" //* Cause: Trying to access data in block that was loaded without //* redo generation using the NOLOGGING/UNRECOVERABLE option //* Action: Drop the object containing the block.
So it turned out that import script used the TRANSFORM clause (TRANSFORM=DISABLE_ARCHIVE_LOGGING=Y) where by default it will be “N”.
DISABLE_ARCHIVE_LOGGING has been introduced in Oracle 12C to disable redo generation (NOLOGGING). It can reduce the frequence of log switches and speed up the import process.
In a DataGuard environment which normally Force Logging is enabled at the database level, DISABLE_ARCHIVE_LOGGING is ignored. However if Force Logging is not enabled in a rare case, DISABLE_ARCHIVE_LOGGING will have adverse impact.