Re: Recreating Controlfile During Database Restore

Ronan,

Generally yes, a consistent backup should not require recovery. But there are some exceptions and using a backup control file makes it a lot harder to diagnose the problem.

One possibility is that there was a data file in backup mode when the backup was taken.

Regardless, the queries I sent should tell you if you have a consistent database or not.

Seth

On Fri, Sep 1, 2017 at 5:27 PM, Ronan Merrick <merrickronan1_at_gmail.com> wrote:

> _at_Seth,
>
> Thanks for taking the time to reply.
>
> If the backup was a cold backup, would this also be the case? A file
> should only be fuzzy in the case of a hot backup right?
>
> _at_Rodrigo
>
> Thanks for the suggestion.
>
> I'd be hesitant to do this. The word corruption scares me! I'm gonna read
> up on it. Thanks!
>
>
>
>
> On Fri, Sep 1, 2017 at 11:00 PM, Seth Miller <sethmiller.sm_at_gmail.com>
> wrote:
>
>> Ronan,
>>
>> The checkpoint number of a data file does not necessarily reflect the
>> latest database changes that have been written to it. The error message
>> means that newer database changes have been written to one or more data
>> files since the checkpoint number.
>>
>> The checkpoint process writes the checkpoint SCN to every data file and
>> control file in the database. However, it is a common misconception that
>> this checkpoint SCN reflects the latest SCN contained anywhere within a
>> data file. While a data file is being backed up, there are no checkpoints
>> written to that file, but database changes may continue to be written to
>> it. Once the backup of the data file is complete, it will receive a
>> checkpoint consistent with the rest of the database files.
>>
>> Once a database has been recovered to a point-in-time, we can determine
>> that the data files are all recovered to the same checkpoint SCN.
>>
>> select distinct checkpoint_change# from v$datafile_header;
>>
>> CHECKPOINT_CHANGE#
>> ------------------
>> 22883553
>>
>> However, that does not mean recovery is complete. If there are database
>> changes written to a data file beyond the checkpoint SCN, a data file is
>> considered “fuzzy”.
>>
>> select file#, tablespace_name, fuzzy from v$datafile_header where fuzzy =
>> 'YES';
>>
>> FILE# TABLESPACE_NAME FUZ
>> ---------- ------------------------------ ---
>> 2 SYSAUX YES
>>
>> To find out what is causing a data file to be fuzzy, we can query the
>> kernel cache recovery component file headers (KCVFH). FHSCN is the
>> checkpoint SCN and FHAFS is the upper limit SCN of changes that have been
>> written beyond the checkpoint.
>>
>> select FHSCN, FHAFS, file#, name from X$KCVFH join v$datafile on (hxfil =
>> file#) where fhafs > fhscn;
>>
>> FHSCN FHAFS FILE# NAME
>> ---------------- ---------------- ----------
>> ------------------------------------------------------------
>> ----------------------------------------
>> 22883553 22883604 2
>> +DATA/orcl/datafile/sysaux.258.903974045
>>
>>
>>
>> Seth
>>
>> On Fri, Sep 1, 2017 at 4:48 PM, Ram Raman <veeeraman_at_gmail.com> wrote:
>>
>>> haaa! talk about a DBA's nightmare.
>>>
>>> I dont know how critical this database is to your business. I have heard
>>> about some of my colleagues using DUDE, DUL tools from someone in Belgium
>>> or Holland.
>>>
>>> Oracle support couldnt help?
>>>
>>>
>>> On Wed, Aug 30, 2017 at 2:14 PM, Ronan Merrick <merrickronan1_at_gmail.com>
>>> wrote:
>>>
>>>> Hi Listers,
>>>>
>>>> Database is 12.1.0.2 RAC on Linux.
>>>>
>>>> Somebody restored the database, then discovered there are no
>>>> controlfile or archivelog backups and the archivelogs from the time of the
>>>> backup are gone.
>>>>
>>>> The backup appears to have been a consistent backup. V$datafile_header
>>>> shows all files with the same checkpoint_change#. RMAN shows all files
>>>> having the same SCN in the backup.
>>>>
>>>> I thought it would be possible to recreate the controlfile and open the
>>>> database but when I try to open with RESETLOGS, it fails, saying datafile 1
>>>> needs more recovery. I tried simulating recovery with CANCEL but it still
>>>> failed.
>>>>
>>>> Have I missed something? Shouldn't it be possible in this case to
>>>> recreate the controlfile and open resetlogs if the backup was consistent?
>>>>
>>>> Any insights would be greatly appreciated.
>>>>
>>>> Regards,
>>>>
>>>> Ronan
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>>
>>>
>>>
>>
>

--
http://www.freelists.org/webpage/oracle-l