It's possible to limit redo generation for specific operations on Oracle databases, which provide higher performance. These operations include bulk inserts, creation of tables as select operations, and index creations. When we work using the NOLOGGING
clause, redo will not include all the changes to data on the related segments. This means if we perform a restore/recovery of the related datafile, or of the whole database after the NOLOGGING
operations, it'll not be possible to recover the data created with the NOLOGGING
option.
The same problem exists with Data Guard. When the NOLOGGING
operation is executed in the primary database, Data Guard is not able to reflect all the data changes in the standby database. In this case, when we activate a standby database or open it in the read-only mode, we'll see the following error messages:
ORA-01578: ORACLE data block corrupted (file # 1, block # 2521) ORA-01110: data file 1: '/u01/app/oracle2/datafile/INDIAPS/system01.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
For this reason, Data Guard installation requires putting the primary database in the FORCE LOGGING
mode before starting redo transport between the primary and standby database. The FORCE LOGGING
mode guarantees the writing of redo records even if the NOLOGGING
clause was specified in the SQL statements. The default mode of an Oracle database is not FORCE LOGGING
, so we need to put the database in this mode using the following statement:
SQL> ALTER DATABASE FORCE LOGGING;
In this section, we'll assume that the primary database is not in the FORCE LOGGING
mode, and some NOLOGGING
changes were made in the primary database. One method to fix this situation in the standby database is restoring the affected datafiles from backups taken from the primary database after the NOLOGGING
operation. However, in this method we have to work with backup files that are most likely much bigger in size than the amount of data that needs to be recovered. A method that uses the RMAN BACKUP INCREMENTAL FROM SCN
statement is more efficient because the backup files will include only the changes from the beginning of the NOLOGGING
operation.
We'll now see two scenarios. We'll use the BACKUP INCREMENTAL FROM SCN
statement for an incremental datafile backup in the first scenario, and use the same statement for an incremental database backup in the second one. For a small number of affected datafiles and relatively less affected data, choose the first scenario. However, if the number of affected datafiles and amount of data are high, use the second scenario that takes an incremental backup of the whole database.