Re: Question about Archivemode

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 18 Jul 2002 10:43:08 +1000
Message-ID: <ah531j$l0s$1@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

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

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 Wed Jul 17 2002 - 19:43:08 CDT