Re: Mimic incomplete recovery - Noarchivelog Mode

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sun, 20 Oct 2002 12:36:57 +1000
Message-ID: <etos9.57019$g9.163793@newsfeeds.bigpond.com>

Comments embedded.

"koert54" <nospam_at_spam.com> wrote in message news:qdZr9.168297$8o4.26243_at_afrodite.telenet-ops.be...
> I would like to comment on this.

Hi Koert54,

Cool, more the merrier !!

> For closed/cold DB backups (after clean shutdown - not shutdown abort) it
is
> not necessary to backup the redolog files. (This used to be documented in
> the Oracle Backup & Recovery docs)

Quite correct.

Backing up the redo logs is somewhat redundant as they contain nothing of value. However, by restoring them as well, it does simplify the recovery of the database (by a debatable teeny weeny amount).

>
> When restoring from such a backup the DB is opened using 'open resetlogs'.
> The redolog
> files are not necessary because no instance recovery is needed.

Correct

> When the DB is open with resetlogs the missing logfiles are created - the
> only thing is, the DB
> is reset to a new incarnation.

Correct

>
> So - a database in noarchivelog mode which is backed up every night by
> performing a clean
> shutdown and putting every datafile & control file on tape can be restored
> and opened
> with a resetlogs without mimic'ing an incompleted recovery and without
> dataloss.

OK. Most databases I have the pleasure to work on are archivelog mode databases so this has never been much of an issue for me. However the exceptions to this rule are my own personal databases that I use for research, play and for being a total plonker on (new term that I love more every time I hear it).

So I restored from my noarchivelog database just the data files and controls files from a clean backup and gave it a whirl using 9.2 on XP:

SQL> startup
ORACLE instance started.

Total System Global Area 51452388 bytes

Fixed Size                   453092 bytes
Variable Size              33554432 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes

Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ZIGGY\REDO01.LOG'

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> So as you can see from the above, the database (specifically the control file) doesn't like it when you attempt to open a database with resetlogs that has not had a successful incomplete recovery performed. Note also that when performing the incomplete recovery, it immediately completes as all the data files and controls files are in sync. However now, the open database with resetlogs completes successfully.

Cheers

Richard Received on Sat Oct 19 2002 - 21:36:57 CDT