Re: Recreating Controlfile During Database Restore

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.

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: image002.png



image/png attachment: image001.png