DB recovery in quality data-file in FUZZ status

[Former Member]

Hi All,

Recently we have upgraded Database from 10g to 11g.

before upgrade we have refreshed the 10g and successfully restored.

Now after upgrading the Database in PRD from 10g to 11g, we have taken online consistent backup with Data protector and restored in quality server.

followed by control file generation and RDBMS was in Mount phase.

But, i'm not able to recover the Database.

Please find the log file attached. during recovery it keep on requesting for archive until we don't have any of the new number that it is requesting.

(not even in PRD)

we have observed that all files are in FUZZ status.

I have checked in PRD server the FUZZ status is YES even in PRD server.

I request you to help me in understanding this FUZZ status and how to recover the DB in quality.

Currently even in PRD server the data files are in FUZZ status. will this be a problem ?

Kind regards,

Harika

SNC_2.txt.zip

[stefan_koehler · Accepted solution]

Hi Harika,

> Currently even in PRD server the data files are in FUZZ status. will this be a problem ?

No, the data files are "fuzzy" as soon as your database is OPEN. Here is just a short verification on my Oracle playground (11.2.0.3)

SQL> startup mount;
SQL> select fuzzy,count(*) from v$datafile_header group by fuzzy;
FUZ   COUNT(*)
--- ----------
NO               4
SQL> alter database open;
SQL> select fuzzy,count(*) from v$datafile_header group by fuzzy;
FUZ   COUNT(*)
--- ----------
YES               4

Oracle documentation "fuzzy file": http://docs.oracle.com/cd/E11882_01/backup.112/e10642/glossary.htm#BRADV90174

... or in other words

FUZZY bit in datafile header means that there may have been writes into a datafile after the last checkpoint. E.g. there may be changes written to datafile with higher SCN than checkpoint_change# stored in datafile header (seen from v$datafile_header.checkpoint_change#).
So any online read-write datafile is essentially a fuzzy one as writes could have happened there. That's the primary thing which is checked when opening up datafiles, if file is fuzzy, then we go to checkpoint_change# and see from which SCN we need to start applying redo.

Regarding your recovery issue on QA. There are some strange errors by running script "@QUA_Control.sql". What kind of RECOVER command (exact syntax) have been used there?

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thanks for your reply.

your question:-

Regarding your recovery issue on QA. There are some strange errors by running script "@QUA_Control.sql". What kind of RECOVER command (exact syntax) have been used there?

please see the below image:-

Kind regards,

Harika

[Former Member · Accepted solution]

Hi all,

Thanks for your effort in helping me in fixing the error.

finally i have restored with yesterday's backup and the post-restore process went fine.

I still wonder, why it didn't work during my last trail.

anyway the latest backup worked

Kind regards,

Harika

[Reagan]

Hello

Here is what Oracle says:

In order for any database to open resetlogs, three conditions have to be met:

1. All online, read-write datafiles must be checkpointed at the same time.

2. No datafiles can be fuzzy. That means that none of the fuzzy flags can be set.

3. No datafiles can be in hot backup mode

Only when the above conditions are met, are you allowed to open the database with

resetlogs. The column OPEN_RESETLOGS in V$DATABASE can also be used returning either

REQUIRED, NOT ALLOWED or ALLOWED.

To recover the database you need to apply all the archived logs in between these two commands.

alter database begin backup;

alter database end backup;

Cheers

RB

[Former Member]

i'm taking online_cons backup, means this should and will pack along with the archive logs necessary for recovering the DB.

i have used "recover database using backup controlfile until cancel; "

first i have selected Cancel, later after putting all archive logs and renaming i have selected AUTO.

that kept on checking and with return of "no longer needed for this recovery" and at last it is prompting the latest archive log number... which yet to be created in PRD.

Is there any other procedure for applying offline redolog (archive) files ?

i'm able to start RDBMS and make MOUNT.

secondly, i can see the Data files status in PRD with FUZZ status as "YES".

Daily backup should completed with online_cons.

is there any mandatory condition that we need to Take offline backup after DB upgrade to 11g so this FUZZ status will change to NO ?

kind regards,

Harika

[Former Member]

Hi

How to find out the required logs that are needed to open the restored database in to OPEN state ?

once, after knowing the log sequence; hope i can use the following command after DB in MOUNT phase:

example: SQL> restore archivelog scn between 65324000 and 65330000;

Kind regards,

Harika

[Reagan]

Hello

Quick question, have you restored and applied all the archived logs from the time the backup started until the end of the backup ?

What is the output of this query ?

select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header;

Check the alert log of the PRD system to find out the logs required to recover the database.

Cheers

RB

[Reagan]

Hello

secondly, i can see the Data files status in PRD with FUZZ status as "YES".
Daily backup should completed with online_cons.
is there any mandatory condition that we need to Take offline backup after DB upgrade to 11g so this FUZZ status will change to NO ?

If the database is Online and in Read-Write mode then the Fuzzy column will be YES.

This is a normal behavior of the database from 9i onwards.

I just happened to check the log you have attached and at the bottom of the log I can see this:

SQL> select status,count(*) from v$backup group by status;
STATUS               COUNT(*)
------------------ ----------
NOT ACTIVE                 85
SQL> select fuzzy,count(*) from v$datafile_header group by fuzzy;
FUZ   COUNT(*)
--- ----------
YES         85

I am wondering how did you have the field FUZ in YES when you havent opened the database yes as the database is still in mount status.

Or is it from the production system ?

Cheers

RB

[Former Member]

Hi RB,

SQL> select status,count(*) from v$backup group by status;

STATUS               COUNT(*)

------------------ ----------

NOT ACTIVE                 85

SQL> select fuzzy,count(*) from v$datafile_header group by fuzzy;

FUZ   COUNT(*)

--- ----------

YES         85

I am wondering how did you have the field FUZ in YES when you havent opened the database yes as the database is still in mount status.

Or is it from the production system ?

This is form the Quality system only.

We have restored PRD database and created control file and during "SQL> recover database using backup controlfile until cancel;" with our response as cancel its giving a problem.

The DB is in Mount phase and the FUZZY status is YES.

Kind regards,

Harika

[Former Member]

Hi RB,

Thanks for your reply.

Quick question, have you restored and applied all the archived logs from the time the backup started until the end of the backup ?

- Yes, we have restored the PRD Database backup on QUA system (DB refresh).

- Control file created with the help script file generated from PRD server. we have used the section with RESETLOGS up to last.

What is the output of this query ?

select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header;

Out put of the above command:

SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)

----------------------- -----------------------

             1363557199              1363557199

Check the alert log of the PRD system to find out the logs required to recover the database.

- in PRD alert log, the check point details as below:-

Tue May 07 18:07:48 2013

Beginning log switch checkpoint up to RBA [0x11d49.2.10], SCN: 1363557199

Thread 1 advanced to log sequence 73033 (LGWR switch)

  Current log# 1 seq# 73033 mem# 0: D:\ORACLE\PRD\ORIGLOGA\LOG_G11M1.DBF

  Current log# 1 seq# 73033 mem# 1: D:\ORACLE\PRD\MIRRLOGA\LOG_G11M2.DBF

Tue May 07 18:07:48 2013

Archived Log entry 72136 added for thread 1 sequence 73032 ID 0x650ff336 dest 1:

Tue May 07 18:12:49 2013

Completed checkpoint up to RBA [0x11d49.2.10], SCN: 1363557199

These are recent archive log files in PRD.
How come this is asking for the latest archive files, when i have used "online_cons" backup of PRD restored to QUA server ?

Kind regards,

Harika