Re: Recreating Controlfile During Database Restore

Thanks very much Seth!

Ronan

On Fri, Sep 1, 2017 at 11:37 PM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:

> 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