Thanks very much Seth!
Ronan
On Fri, Sep 1, 2017 at 11:37 PM, Seth Miller <sethmiller.sm@xxxxxxxxx>
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@xxxxxxxxx>
wrote:
@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?
@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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx
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
--
