How to prevent an oracle block corruption from duplicating a database with a few nologging tables

corruptionoracleoracle-11g-r2

I got a few data corruptions in a test db when I performed a duplicate from a production db where a few tables have the nologging option.

Oracle support suggested to turn on the force logging option when perform a duplicate. I wish that i do not have to change the production settings in order to make a duplicate db in Test.

Any suggestions to prevent data block corruption in the target db from my next duplicate? 

ORA-01110: data file 14: '+DATA/xxxxx/datafile/xxxxx01_02.dbf' 
ORA-26040: Data block was loaded using the NOLOGGING option 
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426036): 

ORA-01578: ORACLE data block corrupted (file # 15, block # 27467) 
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf' 

ORA-26040: Data block was loaded using the NOLOGGING option 
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426037): 

ORA-01578: ORACLE data block corrupted (file # 15, block # 27019) 
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf' 

ORA-26040: Data block was loaded using the NOLOGGING option 
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426038): 

ORA-01578: ORACLE data block corrupted (file # 15, block # 27883) 
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf'

Oracle enterprise version 11.2.0.3.
Here is the rman script: 

SPOOL LOG to '/home/oracle/scripts/REFRESH/target_db_duplicate.150601_1019.log' 
run { 
allocate channel D1 type sbt_tape; 
allocate auxiliary channel T1 type sbt_tape 
    parms='ENV=(NB_ORA_CLIENT=server_name,NB_ORA_COPY_NUMBER=3)'; 
set until time="to_date('01-JUN-2015 02:00:00','DD-MON-YYYY HH24:MI:SS')";
duplicate target database to target_db 
    pfile=$ORACLE_HOME/dbs/inittarget_db.ora 
    nofilenamecheck; 
} 
SPOOL LOG OFF;

Best Answer

The reason for the databa block corruption is that ther was data inserted in the table with nologging in the timespan between the begin of the backup and '01-JUN-2015 02:00:00'. So the information needed to fully restore the datafiles (by applying the archive log) is not contained in the archives log. Mainly there are two possibilities for you:

1) you try to avoid corrupt blocks
2) you repair the tables

1) To avoid corrupt blocks you can
1.1) schedule you duplicate so, that there is no nologging operation in the critical time interval
1.2) you can change the nologging operation to an operation with loggin
1.3) reduce the size of the critical time interval by doing an incremental backup before the until-point-of-time, e.g. '01-JUN-2015 02:00:00'. (but this has to be done on the production database)

2) you can truncate the affected tablels after the duplicate. If you don't need this tables on the your duplicate then you are done. Otherwise you have to fill them with the correct data , e.g. by loading the table with the same data as the production table. Of course this kind of repairing a table depends of the special situation.

Related Question