Oracle blog

Weirdness of v$datafile on a standby db.

Here are some oddities of v$datafile on a standby

Oracle gets content of v$datafile from controlfile, but on a standby database "STATUS" column wouldn't get updated in some scenarios. All datafiles created after the standby controlfile creation timestamp would get "RECOVER" status as they get pushed to the standby database. But how to find out if the datafile is offline or online?

V$datafile_header is the answer. Whenever we query v$datafile_header, Oracle would visit all datafile header blocks, and it gives us the right "STATUS" of datafiles.

What you see in v$datafile for Checkpoint_change# and Checkpoint_time may not be correct if you refresh that datafile from the primary and the recovery on those datafiles haven't started yet, and here also v$datafile_header is an answer.

If a datafile is offline, it's tablespace number will be "0" in v$datafile_header, and you need to use v$datafile for this purpose.

Also, Unrecoverable_change# and Unrecoverable_time columns would never get updated in v$datafile on any standby database.

How do you know where to start for the recovery, you can in general use v$recover_file. But on a standby database, you may not see entry for some datafiles in v$recover_file, and v$datafile_header is an answer here also.

You may occasionally get ORA-235 "controlfile fixed table inconsistent due to concurrent update" while querying v$datafile, then again you can get most of that information with more correctness from v$datafile_header.

You may sometimes find using rule hint may help your query run faster against v$datafile, but try it only if you get ORA-235 error.

posted by Saibabu Devabhaktuni @ 10:41 PM