Re: Question about Archivemode

Howard,

You should get MKS Toolkit ($$) or any number of other freeware UNIX environments for Windows.

Nice proof.

-Kevin

"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 - 10:53:13 CDT