Re: Recreating Controlfile During Database Restore

Hey Ram, thanks! Unfortunately this was not done before the restore... :(

On Sat, Sep 2, 2017 at 9:05 PM, Ram Raman <veeeraman_at_gmail.com> wrote:

> 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.blogspot
>> .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