ORA-01578/ORA-26040 : Data Block was loaded using the NOLOGGING option

[former_member227281]

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.

Conclusion

• 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.

FAQ

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.