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.

Note:

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.

• Oracle