Re: Question about Archivemode

When I taught the Backup and Recovery course, I usually demonstrated that yes you can still fully recover the database in noarchivelog mode providing you haven't cycled around and overwritten the initial redo log since your last backup. And yes in theory if you have enough redo logs online and they can store all changes made to the DB between backups, you could in theory have a protected database without archiving.

But it's a question of certainty and guaranteeness (another of my made up words). Such a "backup strategy" would have to battle against Murphy's Law which means on the one day when load was much greater than normal and all the redo logs have been overwritten, you lose a component of your database. Ooops, stuffed now and no sympathy from me.

If a database is in noachivelog mode, you're basically saying I'm quite happy to wear the loss of data and have a full database restore as my only recovery method. If you're *not* happy with this scenario, then achivelog mode is a *must* for you.

However, if you are in noarchivelog mode, it's still worth attempting a complete recovery. You might be *lucky* and get away with it.

Regards

Richard
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:ah531j$l0s$1_at_lust.ihug.co.nz...
> OK, here's the test. As far as I understood Sean's hypothesis, it was that
> you couldn't use redo stored in the online logs to do a media recovery on
a
> noarchivelog database, because noarchivelog databases can only have
instance
> recoveries performed to them (paraphrasing wildly).
>
> Here goes (and apologies for the number of datafiles and typos involved,
but
> I wanted this verbatim):
>
> SQL> startup force
> ORACLE instance started.
>
> Total System Global Area 135338868 bytes
> Fixed Size 453492 bytes
> Variable Size 109051904 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 667648 bytes
> Database mounted.
> Database opened.
> SQL> archive log list
> Database log mode No Archive Mode
> Automatic archival Disabled
> Archive destination D:\oracle\ora92\RDBMS
> Oldest online log sequence 114
> Current log sequence 115
>
> [NOTE: not in archivelog mode, and no ARCH]
>
> SQL> alter system switch logfile;
> System altered.
> SQL> /
> System altered.
>
> [Just to clear the decks, you understand]
>
> SQL> shutdown immediate;
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
> SQL> exit
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> [Time to do a clean, cold backup]
>
> C:\>copy z:\oracle\xxxx\yyy\*.dbf e:\backup
> z:\oracle\xxxx\yyy\BLAH01.DBF
> z:\oracle\xxxx\yyy\BREAK01.DBF
> z:\oracle\xxxx\yyy\CMPBLOB.DBF
> z:\oracle\xxxx\yyy\CMPDATA.DBF
> z:\oracle\xxxx\yyy\CMPINDX.DBF
> z:\oracle\xxxx\yyy\CWMLITE01.DBF
> z:\oracle\xxxx\yyy\DOCS01.DBF
> z:\oracle\xxxx\yyy\DRSYS01.DBF
> z:\oracle\xxxx\yyy\EXAMPLE01.DBF
> z:\oracle\xxxx\yyy\INDX01.DBF
> z:\oracle\xxxx\yyy\ODM01.DBF
> z:\oracle\xxxx\yyy\SYSTEM01.DBF
> z:\oracle\xxxx\yyy\TEMP01.DBF
> z:\oracle\xxxx\yyy\TEMPTEST01.DBF
> z:\oracle\xxxx\yyy\TEST01.DBF
> z:\oracle\xxxx\yyy\TOOLS01.DBF
> z:\oracle\xxxx\yyy\UNDOTBS01.DBF
> z:\oracle\xxxx\yyy\USERS01.DBF
> z:\oracle\xxxx\yyy\USERS01.DBFold
> z:\oracle\xxxx\yyy\USUAL01.DBF
> z:\oracle\xxxx\yyy\XDB01.DBF
> 21 file(s) copied.
>
> [Now, re-start the database, do a transaction, move on to a fresh log, and
> shutdown immediate]
>
> SQL> startup
> ORACLE instance started.
>
> Total System Global Area 135338868 bytes
> Fixed Size 453492 bytes
> Variable Size 109051904 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 667648 bytes
> Database mounted.
> Database opened.
>
> SQL> update scott.emp set sal=1234;
> 15 rows updated.
> SQL> commit;
> Commit complete.
>
> SQL> alter system switch logfile;
> System altered.
>
> [So my update to 1234 is now no longer in the current log]
>
> SQL> shutdown immediate;
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
>
> [Note: clean shutdown, therefore no possibility of an instance recovery
> being required]
>
> SQL> exit
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> C:\>rm z:\oracle\xxxx\yyy\USERS01.DBF
> 'rm' is not recognized as an internal or external command,
> operable program or batch file.
>
> [Ooops. Mus remember this is not Unixland any more]
>
> C:\>del z:\oracle\xxxx\yyy\USERS01.DBF
>
> C:\>sqlplus /nolog
>
> SQL*Plus: Release 9.0.1.0.1 - Production on Thu Jul 18 10:37:57 2002
>
> (c) Copyright 2001 Oracle Corporation. All rights reserved.
>
> SQL> connect / as sysdba
> Connected to an idle instance.
> SQL> startup
> ORACLE instance started.
>
> Total System Global Area 135338868 bytes
> Fixed Size 453492 bytes
> Variable Size 109051904 bytes
> Database Buffers 25165824 bytes
> Redo Buffers 667648 bytes
> Database mounted.
> ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
> ORA-01110: data file 9: 'D:\ORACLE\ORA92\DB9\USERS01.DBF'
>
> [As expected]
>
> SQL> host copy e:\backup\users01.dbf z:\oracle\ora92\db9
> 1 file(s) copied.
>
> [Restore...]
>
> SQL> recover datafile 9
> Media recovery complete.
>
> [...and recover....]
>
> SQL> alter database open;
> Database altered.
>
> [open the database....]
>
> SQL> select sal from scott.emp;
>
> SAL
> ----------
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
> 1234
>
> 15 rows selected.
>
> [...and I get my committed transaction back, even though it wasn't in the
> current online log].
>
> So, you *can* perform media recovery on a noarchivelog database, so long
as
> all the required redo is available in the online logs. All of 'em, and not
> just the current one.
>
> Regards
> HJR
>
>
>
> "Sean M" <smckeownNO_at_BACKSIESearthlink.net> wrote in message
> news:3D360194.D10E40F1_at_BACKSIESearthlink.net...
> > "Howard J. Rogers" wrote:
> > >
> > > There is one scenario where, despite not being in archivelog mode, you
> might
> > > get away with a complete and perfect recovery: if the continuous
stream
> of
> > > redo you require still happens to be sitting in the online logs. If
you
> have
> > > 5 online redo log groups, which switch every half hour, then you have
> 2.5
> > > hours of redo available to you. So if you backed up last night, opened
> the
> > > database at 9.00am, and the intruder swiped the system tablespace at
> > > 11.00am, you're doomed. But if he swiped it at 9.45am, the redo
required
> to
> > > recover the backup of the system datafile is still available in the
> online
> > > logs, so you could just restore that one file and do a recovery on it.
> >
> > Interesting... I'm not sure I agree, though I've never actually tested
> > it, so please take what I'm about to say with a grain of salt. (I'm not
> > talking about your timing thing - I saw your other post re: noon.) I'm
> > talking about the ability to take a datafile from a cold backup of a
> > noarchivelog database, insert it into the mix, and recover using only
> > online redo (assuming, of course, that you haven't cycled through the
> > onlines since the cold backup so you have an unbroken stream of redo
> > available). The problem (at least, I suspect, but I don't have
> > noarchivelog mode databases handy to test) is that although you have all
> > the redo in online logs still available since the backup, Oracle only
> > lets you do instance recovery (as opposed to media recovery) for a
> > noarchivelog mode database. And during instance recovery, Oracle only
> > applies redo since the last checkpoint to the datafile. Now, in your
> > scenario, that datafile has likely been checkpointed more than once
> > since you started up after the cold backup. So even though Oracle
> > hasn't yet overwritten the redo generated since the startup, it's only
> > smart enough to apply the redo since the last checkpoint. So if you
> > replace a file with one who's checkpoint in its header doesn't match
> > what the controlfile thinks it should be, I suspect Oracle would freak
> > out during instance recovery. Oracle would look at the controlfile and
> > say "cool, I need to apply changes since the last checkpoint at 11:30 to
> > this guy". But the datafile's header says "but that's not right - my
> > last checkpoint is actually from 9:00 a.m." Now, maybe if you recreated
> > your controlfile so that the only info Oracle has to go on is the
> > datafile header you could fake it out... hmm... I smell a test
> > coming... Howard, do you have a noarchivelog test database handy?
> >
> > > Clearly, there's no guarantees, though, that the required redo would
be
> > > available. Only archivelog mode can make that guarantee.
> >
> > Definitely.
> >
> > Regards,
> > Sean
>
>
Received on Thu Jul 18 2002 - 21:14:44 CDT