ORA-26040: Data block was loaded using the NOLOGGING option

I got ” ORA-26040: Data block was loaded using the NOLOGGING option ”  error in Oracle database.

ORA-26040: Data block was loaded using the NOLOGGING option

Details of error are as follows.

ORA-26040: Data block was loaded using the NOLOGGING option

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.

Data block was loaded using the NOLOGGING option

This ORA-26040 error is related to try to access data in block that was loaded without redo generation using the NOLOGGING/UNRECOVERABLE option.

Drop the object containing the block.

Primary and Standby Configuration.

Primary database was in NOFORCE logging mode

Select Force_logging from v$database ;
 
NO

Switchover was done the first time on this Configuration.

Once Old Standby became Primary it started reporting

ORA-01578: ORACLE data block corrupted,ORA-01110: data file 12
ORA-26040: Data block was loaded using the NOLOGGING option.

Primary database (A) Switchover to Standby(B)

So Now New Primary database is B  and  New Standby database is A.

For Version < 12.2

Step1 :- On the new Primary first enable Force logging

Alter database enable force_logging ;

Step 2 :- On the New Standby(Old Primary) identify datafile’s on which No logging operation was done

SELECT FILE#,NAME, UNRECOVERABLE_CHANGE#,TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS') FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE# > 1 ;

This would only list out datafile’s on which No logging operations had occurred on the Old primary.

Now run Backup validate datafile on these datafiles

Rman

run {

 backup validate datafile <fileno>,<fileno> ..... ;

}

Once done identify if affected datafile’s are reported in v$database_block_corruption for version < 12c  and v$nonlogged_block for version >=12c

Wouldnot be if the switchover has been done for the first time and no datafiles on which no logging operation were restored  in old primary.

This would ensure that these datafiles in New standby are corruption free .

Step 3 :- On new Primary run validate on datafiles reported from query 1  :-

Run { 
allocate channel c1 device type disk ;
         allocate channel c1 device type disk ;
         allocate channel c1 device type disk ;
        allocate channel c1 device type disk ;
        Backup validate datafile <fileno reported in >,<fileno> ; 
}

Once done identify if affected datafiles are reported in v$database_block_corruption for version < 12c  and v$nonlogged_block for version >=12c

Once the file number have been identified in New primary, Since these datafiles are corruption free on New standby we can get these datafiles backup from new standby

Step 4 :- On new standby backup these datafiles

Rman> run {

backup datafile <fileno>,<fileno>,.....   format  '/u01/backup/%U' ;

}        

Scp these files to New Primary

Step 5 :- On the new Primary

SQL>Alter database datafile <fileno> offline ;

alter session set container=<pdb name>

SQL>Alter database datafile <fileno> offline ;





Rman> Connect target /

Rman> catalog backuppiece '<name and location of backuppiece >' ;

Rman> restore datafile <fileno>,<fileno>,.... ;

Rman> recover datafile         <fileno>,<fileno>,.... ;

Rman>sql 'alter database datafile <fileno> online' ;

Do these for all the files you have recovered.

Step 6 :- Re-run the Validate datafile /Backup validate datafile

This would confirm the New Primary datafiles are now Corruption free.

Read the following post related with this post.

ORA-01578: ORACLE data block corrupted (file # string, block # string) and How to Solve Block Corruption Error

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )