Check for unrecoverable changes in datafiles

[Arjen Visser]

SQL query to check for unrecoverable changes in datafiles on the standby database:

SQL> select name, unrecoverable_change#, unrecoverable_time from v$datafile;

If unrecoverable_change# is not 0 that indicates that unrecoverable/nologging operations were used with object(s) in that datafile. In that case, if unrecoverable_time shows date after you created the standby database, then when you activate the standby database you will probably get the following error messages for one or more objects in that datafile:

ORA-01578: ORACLE data block corrupted (file # string, block # string)
ORA-01110: data file string: "string"
ORA-26040: Data block was loaded using the NOLOGGING option


Nologging operations should not be used with objects in that datafile.
Oracle introduced the "force logging" feature in 9.2 to avoid this problem.
SQL> ALTER DATABASE FORCE LOGGING;

In order to fix the issue follow the following steps:

To determine which datafile needs to be copied from the primary to the standby:
1.
Query the primary database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/oradata/dbs/tbs01_1.dbf 5216
/oracle/oradata/dbs/tbs01_2.dbf 0
/oracle/oradata/dbs/tbs01_3.dbf 0
/oracle/oradata/dbs/tbs01_4.dbf 0
4 rows selected.

2.
Query the standby database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/oradata/dbs/stdby/tbs_1.dbf 5186
/oracle/oradata/dbs/stdby/tbs_2.dbf 0
/oracle/oradata/dbs/stdby/tbs_3.dbf 0
/oracle/oradata/dbs/stdby/tbs_4.dbf 0
4 rows selected.
3.
Compare the query results of the primary and standby databases.
Compare the value of the UNRECOVERABLE_CHANGE# column in both query results.
If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database,
then the datafile needs to be copied from the primary site to the standby site.
In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs01_1.dbf datafile is greater, so you need to copy the tbs01_1.dbf datafile to the standby site.

When copying the datafile, use the following method:
SQL> ALTER TABLESPACE tbs01 BEGIN BACKUP;
SQL> EXIT;
% scp tbs01_1.dbf oracle@standby_server:/backup
SQL> ALTER TABLESPACE tbs01 END BACKUP;

Shutdown the standby database and replace the existing datafile with the new datafile (make a backup copy first).
Restart the standby database and run Dbvisit.

Avoid this issue in future by setting your primary database to force logging:
SQL> ALTER DATABASE FORCE LOGGING;