ORA-01578: ORACLE data block corrupted in Active Standby Database

My database is Active Data Guard environment and it was running in 11.2.0.3 version.

The Standby alert log file was throwing the error message.

ORA-01578: ORACLE data block corrupted (file # 4, block # 171)
ORA-01110: data file 4: '/data01/oradata/edcreport_f01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

The root cause was,  accidentally  we enabled NO LOGGING mode in primary database and we are creating the reporting temp table with nologging mode for mid night report. This caused data file corruption  in standby database.

I use DEV environment to demonstrate this exercise.

My primary database is devdb12 and standby database is devdb12_dg.

Step 1 Change the primary database to NO FORCE LOGGING  mode.

sys@devdb12> alter database no force logging;

Database altered.

Step 2 create the table in primary database with unrecoverable mode.

sys@devdb12> CREATE TABLE scott.GT UNRECOVERABLE AS (SELECT * FROM dba_objects) ;

Table created.

Step 3  Read the table in standby database.

sys@devdb12_dg> select count(*) from scott.gt;

select count(*) from scott.gt

                           *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 171)

ORA-01110: data file 4: '/data01/oradata/devdb12/users01.dbf'

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

sys@devdb12_dg>

How do we fix the above issue?

Step 1 On primary database, copy the file to /data01/stage location. The tablespace should be in backup mode.

Alter tablespace users begin backup;

# copy the file to /data01/stage

# cp /data01/oradata/devdb12/users01.dbf /data01/stage

Alter tablespace users end backup;

Step 2    scp the data file from primary database to standby database.

scp /data01/stage/users01.dbf oracle@10.156.24.169:/data01/stage

Step 3 Move the data file to correct file system on standby database. Login to standby database(devdb12_dg) and follow the below steps.

mv /data01/oradata/devdb12/users01.dbf /data01/oradata/devdb12/users01.dbf.x

mv /data01/stage/users01.dbf /data01/oradata/devdb12/users01.dbf

Step 4 Restart the standby database.

shutdown immediate
startup mount
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Step 5   Verify that the block corruption is resolved in standby database.

Note :  The above steps works when the standby data file is deleted by human error. One important note is, when the broker is enabled, the MRP will start automatically when the standby database is mounted. After mount the standby, need to watch out the standby alert log and see if broker enable the Managed recovery. Ignore the below RED part if you don’t have broker.

shutdown immediate
startup mount
Watch the alert log and see MRP is enabled by broker. If so, then
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Lesson learned :  Primary database should be FORCE LOGGING mode. Otherwise, it will correct the data file in standby database if application creates the table with  NO LOGGING option.