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

Query 1

Run the below query to Confirm on which datafiles NOLOGGING operation had happened on the Old Primary(New standby)

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> ..... ;
}

 

Please note :-

10.2.0.4 and lower 11.1.0.6 ,11.1.0.7
The Backup validate command reports NO LOGGING blocks in v$database_block_corruption with Corruption_type=LOGIGAL

In version 10.2.0.5 or in 11.2.0.1 and forward, RMAN has been enhanced to report it with CORRUPTION_TYPE=NOLOGGING. Reference Doc ID 7396077.8 :
10.2.0.5 and 11.2.0.1+:

The VALIDATE/BACKUP VALIDATE RMAN command reports the NOLOGGING blocks in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING

In version 12c and forward RMAN validate no longer populates view v$database_block_corruption; instead the new view v$nonlogged_block is updated:
12c:
RMAN validate reports it in v$nonlogged_block

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

Takes these datafile’s offline
SQL>Alter database datafile <fileno> offline ;
If on 12c ensure you connect to correct pdb
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 )

 

 480 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply