ORA-01578/ORA-26040 : Data Block was loaded using the NOLOGGING option
I have received complaint from many users that short dump “DBIF_RSQL_SQL_ERROR” error occured in BI (i.e. BW) Quality system.
The error was “ORA-26040 data Block was Loaded using the NOLOGGING option”.
This type of error occurred only in BW system. The problem occurred in BW Quality system after system refresh using restore / recovery Oracle database from BW Production system.
Tx code ST22
Execute brtool in ora<sid> to verify whether error occured 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
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 objects .
In SAP BW systems, ‘nologging’ for index creation from SAP level is the default.
Indexes for BW objects “/BI*/F*” and “/BI*/E*” are created with NOLOGGING . Hence drop only these indexes “/BI*/F*” and “/BI*/E*”
The procedure for identifying the index name, partition table name is described below
select segment_name,partition_name,segment_type ,block_id,blocks from dba_extents where (147445 between block_id and (block_id + blocks – 1)) and file_id=392 and rownum < 2
As shown above, the nologging was set on the creation of index “/BIC/FGBIPC010~080” for the given partition table name “/BIC/FGBIPC0100000000005”. Hence it is advisable to drop all the indexes for the given partition table name rather than dropping single index ““/BIC/FGBIPC010~080” .
The sql script to identify all the indexes for the given partition table name is as shown below
Then drop all the indexes associated with the given partition table as shown in the figure below.
Recreate the dropped indexes
The method to recreate the drop indexes can be done either using sql command or ABAP report.
SAPLogin to BW Quality system. Enter tx code se38 and report name “SAP_INFOCUBE_INDEXES_REPAIR “
Click execute (i.e. Press F8 key). New screen appeared. Scroll down continuously till you find green colour
highlighted as shown the figure below
Another method to to re-create the index using the following sql command
SQL> alter index “<SAP-Schema>”.”<index_name>” rebuild partition “<SAP_SCHEMA>”. “<partition_name>” online logging;
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. This does not mean that the issue was resolved permanently. You need to execute database statistics and / or check database statistics frequently till no more error reported for other infocube objects.
- NOLOGGING option was enabled on couple of “/BI*/F*” and “/BI*/E*” objects in SAP BW production system. These are the infocubes objects. The indexes for these infocubes 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 redologs. As a result the information is not written to the archive logs either.
- After restore 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 behaviour.
- Either drop or rebuild all the infocubes indexes immediately after system refresh and the error will be gone.
Best Practise to prevent the NOLOGGING Block corruption after system refresh from SAP BW Production to SAP BW Quality
- Perform offline full database backup in SAP BW production system– so no need to use recovery option after restore 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 production system. See the OSS note 1812719.
- SAP strongly recommend to perform 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
- DO not use any other tool except SAP standard BR* tool.
Question : Why ORA-26040 data block corruption was not reported in SAP BW Production system after executing database check or database statistics using brtools?
Ans: Because the SAP BW production system was never restored. Incase if the SAP BW Production system crashed or or migration to new hardware (Homogeneous System Migration), then data block corruption due to NOLOGGING on some tables might be reported in SAP BW Production system after restore and recovery via incorrect version of brtools.
Author : A Prasad Rao
Company: Tata Consultancy Services Ltd
Eleven years experience as an Unix System and Oracle database Administration and sixteen years experience as SAP Basis Consultant.
Executed more than 10 SAP Technical Upgrade, combine Upgrade and Unicode conversion and SAP OS/DB Migration in different clients in geographically locations across the world.
Experience in SAP Basis troubleshooting, SAP Oracle performance issue.
Certified SAP OS/DB migration, certified OCP (Oracle Certification Professional) 9i Database and certified SAP WBE AS 640.