Migration on SCM using DMO to SAP HANA DB failed with ORA-01578/ORA-26040 : Data Block was loaded using the NOLOGGING option

Migration on SCM using DMO to SAP HANA DB terminated in downtime phase.

The error was “ORA-26040 data Block was Loaded using the NOLOGGING option” during export of tables.

This type of error occurred only in BW system. The problem occurred in BW/SCM Quality system after system refresh using restore / recovery Oracle database from BW/SCM Production system.

The reason for this termination is due to ORACLE Data block got corrupted as shown below.

Execute brtool in ora<sid> to verify whether error occurred pertaining to nologging.

Select “6” – Check and verification.

Select “1) Database System check.

After Check database was completed, then view the check log file as shown in the figure below.

As shown above ORA-01578 oracle data block corruption error was shown. Normally block corruption is often due to a hardware error and there is a likely trace file or log entry that was created when it happened.

Open the trace file as shown below

I was greatly relieved that from the trace file as shown in the above figure the error ORA-26040 block corruption was the result of NOLOGGING option.

The ORA-26040 error says that the “index” was loaded using the “nologging” option and then possibly recovered from a backup before the nologging load.

The nologging option is a great way to speed-up inserts and index creation. It bypasses the writing of the Primary Redo log, greatly improving performance.

However, this approach is very dangerous if you need to roll-forward during this time period during a database recovery.

Therefore, before system refresh to BW Quality system, one must take a backup both before and after all nologging operations.

It is not possible to roll-forward through a point in time since there are no images in the archived redo logs for this NOLOGGING operation. Hence full backup after performing any NOLOGGING operation is must.

Unfortunately, database backup team was not aware whether NOLOGGING was enabled for some of the BW / SCM objects.

In SAP BW/SCM systems, ‘nologging’ for index creation from SAP level is the default.

Indexes for BW objects “/BI*/F*” and “/BI*/E*” are created with NOLOGGING.

The procedure for identifying the index name, partition table name is described below

System is in downtime phase. No need to drop the affected indexes.

There is no need to do any restore or recovery process.

You can avoid this type of soft corruption if you rebuild the relevant indexes.

Recreation of the indexes with ONLINE rebuild is necessary because the drop and create cannot solve the issue always because only the reference is deleted but the block will be reused and not newly created.

OSS Note 332677 – Rebuilding fragmented indexes – SAP ONE Support Launchpad describes the procedure to rebuild the index online.

alter index “/BIC/FZDPBACKUP~01” REBUILD partition “/BIC/FZDPBACKUP0000000352”

use brspace command as shown below.

brspace -f idrebuild -i /BIC/FZDPBACKUP~01 -e 4 -a rebpart -ip /BIC/FZDPBACKUP0000000352;

brspace -f idrebuild -i /BIC/FZDPBACKUP~02 -e 4 -a rebpart -ip /BIC/FZDPBACKUP0000000352;

brspace -f idrebuild -i /BIC/FZDPBACKUP~04 -e 4 -a rebpart -ip /BIC/FZDPBACKUP0000000352;

Repeat the above command for other affected indexes.

After that run with option -f collect update stats or execute brtool in ora<sid> to verify whether nologging block corruption was cleared or not.

Select “6” – Check and verification. Then Select “1″ Database System check.

After Check database completed, then view the check log file as shown in the figure below.

From the check log file, nologging block corruption for those objects was cleared i.e., ORA-01578 was not appearing in the check log file.

Then resume migration. Downtime phase for SCM migration completed as shown below.

Summary

NOLOGGING option was enabled on couple of “/BI*/F*” and “/BI*/E*” objects in SAP BW production system. These are the infocube objects. The indexes for these infocube objects had been created with “nologging”

If you create an index with “nologging” option, Oracle database does not write the changes made to the index blocks to the online redo logs. As a result, the information is not written to the archive logs either.

After restoring to SAP Quality system during system refresh and apply recovery, many block corruption for infocube objects were reported after executing brtools for database check or database statistics. That is an expected behavior.

Rebuild all the infocube indexes immediately after system refresh and the error will be gone.

Best Practice to prevent the NOLOGGING Block corruption after system refresh from SAP BW/SCM Production to SAP BW /SCM Quality

Perform offline full database backup in SAP BW production system– so no need to use recovery option after restoring to Quality system.

In case offline database backup is not possible, then use online full database backup both before and after all nologging operations.

NOLOGGING for index creation must be turned off with transaction code “SPRO” in BW /SCM production system. See the OSS note 1812719.

SAP strongly recommend performing database backup / restore / recovery using standard SAP BRBACKUP / BRRESTORE / BRRECOVERY tools.

Use latest version and patch level of BR* tool for your operating system. BRRESTORE and BRRECOVERY tool automatically repairs nologging indexes during BRRECOVERY