Re: Recreating Controlfile During Database Restore

_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