Re: Recreating Controlfile During Database Restore

Thought I would add one more thing, which I am sure you have done, but still worth mentioning: In a situation like this, it is usually considered a good practice to make a copy (or two) of the backup files elsewhere BEFORE you start working on the recovery process, in case if inadvertantly any backup files are deleted, overwritten or corrupted.

Ram.

On Sat, Sep 2, 2017 at 7:34 AM, Ronan Merrick <merrickronan1_at_gmail.com> wrote:

> Thanks very much for sharing Rodrigo.
>
> Ronan
>
> On 2 Sep 2017 00:58, "Rodrigo Mufalani" <rodrigo_at_mufalani.com.br> wrote:
>
> Hi Ronan,
>
>
>
> This blog post is in Portuguese from an Oracle ACE friend of mine…. It
> is a short article… (I translated using google translator)
>
>
>
> https://translate.google.com/translate?sl=pt&tl=en&js=y&prev
> =_t&hl=pt-BR&ie=UTF-8&u=http%3A%2F%2Feduardolegatti.blogspo
> t.com.br%2F2008%2F12%2Fsalvo-pelo-allowresetlogscorruption.html&edit-text=
>
>
>
> After open, you need to export your schemas, recreate a new database and
> reimport the data into this new database.
>
>
>
> Best Regards,
>
> [image: RED]
>
> *Rodrigo Mufalani* - Dir. Técnico
> rodrigo_at_mufalani.com.br
> +55 21 988 994 817
>
>
> * Mufalani*
> +55 21 3193 0326 <+55%2021%203193-0326>
> Rua Almirante Grenfall, 405, Bloco 3, Sala 310
> Centro Empresarial Washington Luiz
> Duque de Caxias - RJ
> CEP 25085-009
> www.mufalani.com.br
>
>
> [image: id:image002.png_at_01D2F4C6.8E6B3BE0]
>
>
>
>
>
> *De: *Ronan Merrick <merrickronan1_at_gmail.com>
> *Data: *sexta-feira, 1 de setembro de 2017 20:37
> *Para: *Rodrigo Mufalani <rodrigo_at_mufalani.com.br>
>
> *Assunto: *Re: Recreating Controlfile During Database Restore
>
>
>
> Thanks for the tip Rodrigo, I will consider it as a last resort. I
> appreciate you taking the time to reply.
>
>
>
> Have a good weekend
>
>
>
> On Sat, Sep 2, 2017 at 12:34 AM, Rodrigo Mufalani <rodrigo_at_mufalani.com.br>
> wrote:
>
> Hi Ronan,
>
>
>
> This trick just allows you to open the database, is a last resort. So,
> because of it, I recommended you take a backup of actual situation before
> try this solution.
>
>
>
> Best Regards,
>
> Rodrigo Mufalani
>
>
>
>
>
>
>
>
>
> *De: *<oracle-l-bounce_at_freelists.org> em nome de Ronan Merrick <
> merrickronan1_at_gmail.com>
> *Responder para: *"merrickronan1_at_gmail.com" <merrickronan1_at_gmail.com>
> *Data: *sexta-feira, 1 de setembro de 2017 19:27
> *Para: *Seth Miller <sethmiller.sm_at_gmail.com>
> *Cc: *Ram Raman <veeeraman_at_gmail.com>, oracle-l <oracle-l_at_freelists.org>
> *Assunto: *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




image/png attachment: image001.png



image/png attachment: image002.png