Re: Recreating Controlfile During Database Restore
Date: Fri, 1 Sep 2017 17:00:11 -0500
Message-ID: <CAEueRAX-fgxm0-E_8RgYmCo-=HjOPcK2JM73zFqr-7KQanfhfQ_at_mail.gmail.com>
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';
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;
Seth
On Fri, Sep 1, 2017 at 4:48 PM, Ram Raman <veeeraman_at_gmail.com> wrote:
> haaa! talk about a DBA's nightmare.
FILE# TABLESPACE_NAME FUZ
---------- ------------------------------ ---
2 SYSAUX YES
FHSCN FHAFS FILE# NAME
---------------- ---------------- ----------
----------------------------------------------------------------------------------------------------
22883553 22883604 2
+DATA/orcl/datafile/sysaux.258.903974045
>
> 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-lReceived on Sat Sep 02 2017 - 00:00:11 CEST