set state=apply-off and datafile consistency

By Franck Pachot

.
In a standby database (or in any database you recover) do you think the recovery uses buffer cache? And when you stop the apply in a standby database, do you think the datafiles are in a consistent state? We know the answer, but want to prove it. This is the kind of question you may have, as I did last Friday morning in the train with Ludovico Caldara when going to the Paris Oracle Meetup.

Here is the buffer cache status summary in a Data Guard standby database instance:

SQL> select status,count(*) from v$bh group by status;
 
STATUS       COUNT(*)
---------- ----------
free             1473
memory              2
mrec             4846

The MREC status is the status of the blocks where redo from recovery is applied. It’s a current version of the block for recovery purpose only.

Let’s see what happens when we stop the apply:

DGMGRL> edit database demo12 set state=apply-off;
Succeeded.

The alert.log shows that the data files are in a consistent state, which is similar to a checkpoint:

Recovery interrupted!
Recovered data files to a consistent state at change 5683001

And here is the buffer cache:

SQL> select status,count(*) from v$bh group by status;
 
STATUS       COUNT(*)
---------- ----------
free             6344

All my MREC blocks have become free, as with any checkpoint.

It’s exactly what we expected, but sometimes I just want to prove it to myself because there can be bugs, implementation oddities, or simply something that I overlooked. Now I’m conviced: the physical standby data files are consistent after an apply-off.