Friday, March 10, 2006

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.

4 Comments:

At April 22, 2010 at 9:04:00 PM PDT, Blogger Herrien said...

Nice post, thanks for sharing this wonderful and useful information with us.

Green Tea
Green Tea Diet

 
At May 20, 2010 at 10:20:00 PM PDT, Anonymous India oracle programmers said...

Thanks for such a informatory stuff

 
At January 11, 2011 at 12:02:00 AM PST, Anonymous Hire PHP Development said...

The information mentioned in the post regarding the v$datafile on a standby db is not that easy to understand. I need to read more about it, then only i will be able to get it completely.

 
At April 4, 2013 at 3:53:00 AM PDT, Blogger Synsoft Global said...

hello,
nice blog

 

Post a Comment

Links to this post:

Create a Link

<< Home