And thanks for this, too. I've long been writing about 'recovery means
applying redo', and on a couple of occasions, I've had people 'correct' me
saying things like 'you mean archived redo' or 'you mean online redo logs'.
The point I've been trying to make over the years is redo is redo is redo,
wherever it comes from. There's nothing special about archive logs, in other
words: they're just repositories of redo.
What's more, there's nothing special about being in archivelog mode: all
that basically does is set a bit in the controlfile that says 'thou shalt
not overwrite an online log until its been archived'.
And there's nothing special about ARCH. All that does is to automatically
copy the online logs, which you can do yourself (as you've done here) or you
can get a server process to do it (as happens with an alter system archive
log all, for example).
And since the trigger for media recovery is just non-synched datafile
headers; and since the way of re-synching those headers is to apply redo
(from somewhere, anywhere); it follows that media recovery is possible under
all sorts of conditions, provided a continuous stream of redo is available
somewhere. Being in archivelog mode just happens to *guarantee* such a
continuous stream is available (and is, of course, the most convenient way
of doing it, too).
But this is the most convincing demonstration that you can make your own
guarantees in all sorts of ways that I've seen in a long, long time (and I
intend to expropriate it mercilessly for teaching purposes).
So again, thanks for this.
Regards
HJR
"Sean M" <smckeownNO_at_BACKSIESearthlink.net> wrote in message
news:3D371FE4.8BA7DE8E_at_BACKSIESearthlink.net...
> "Howard J. Rogers" wrote:
> >
> > 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).
>
> Nah, sounds about right.
>
> > Here goes (and apologies for the number of datafiles and typos involved,
but
> > I wanted this verbatim):
>
> <snip good disproof of my hunch>
>
> > [...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.
>
> Ah well, so much for my grain of salt, nice job. Thanks for working it
out. Got me thinking though... if that's possible, then
> theoretically it's possible to manually mimmick archivelog mode in a
noarchivelog mode database by copying the non-current online
> redo's somewhere safe before they're overwritten, and restoring datafiles
that were taken from a cold backup. Yeah, I know, totally
> unsupported, but it *should* work given the above. So, here's my little
test (I bit the bullet and converted a test instance on
> workstation to noarchive - curiosity killed the cat...):
>
> oracle_at_carabiner> sqlplus internal
>
> SQL*Plus: Release 8.1.7.0.0 - Production on Thu Jul 18 13:26:21 2002
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> With the Partitioning option
> JServer Release 8.1.7.0.0 - Production
>
> SEANTEST> archive log list
> Database log mode No Archive Mode
> Automatic archival Enabled
> Archive destination /u01/app/oracle/admin/seantest/arch
> Oldest online log sequence 1884
> Current log sequence 1886
>
> [Note: Not in archivelog mode]
>
> SEANTEST> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
> ---------- ---------- ---------- ---------- ---------- --- ---------------
- ------------- ---------
> 4 1 1885 10485760 2 NO INACTIVE
591869 17-JUL-02
> 5 1 1884 10485760 2 NO INACTIVE
591868 17-JUL-02
> 6 1 1886 10485760 2 NO CURRENT
591908 17-JUL-02
>
> [So 1886 is current, now lets find a suitable test tablespace with only
one datafile and a test table.]
>
> SEANTEST> select file_name
> 2 from dba_data_files
> 3 where tablespace_name = 'STUFF';
>
> FILE_NAME
> -------------------------------------
> /u02/oradata/seantest/stuff.dbf
>
> SEANTEST> select tablespace_name
> 2 from dba_tables
> 3 where table_name = 'FOOBAR';
>
> TABLESPACE_NAME
> ------------------------------
> STUFF
>
> SEANTEST> select * from foobar;
>
> EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
> ---------- ---------- --------- ---------- --------- ---------- ----------
> 7369 SMITH CLERK 7902 17-DEC-80 5555
20
> 7499 ALLEN SALESMAN 7698 20-FEB-81 5555 300
30
> 7521 WARD SALESMAN 7698 22-FEB-81 5555 500
30
> 7566 JONES MANAGER 7839 02-APR-81 5555
20
> 7654 MARTIN SALESMAN 7698 28-SEP-81 5555 1400
30
> 7698 BLAKE MANAGER 7839 01-MAY-81 5555
30
> 7782 CLARK MANAGER 7839 09-JUN-81 5555
10
> 7788 SCOTT ANALYST 7566 09-DEC-82 5555
20
> 7839 KING PRESIDENT 17-NOV-81 5555
10
> 7844 TURNER SALESMAN 7698 08-SEP-81 5555 0
30
> 7876 ADAMS CLERK 7788 12-JAN-83 5555
20
> 7900 JAMES CLERK 7698 03-DEC-81 5555
30
> 7902 FORD ANALYST 7566 03-DEC-81 5555
20
> 7934 MILLER CLERK 7782 23-JAN-82 5555
10
>
> 14 rows selected.
>
> SEANTEST> update foobar set sal = (select sequence# from v$log where
status = 'CURRENT');
>
> 14 rows updated.
>
> [Just as way to keep track of the sequence# for a specifc committed
transaction.]
>
>
> SEANTEST> commit;
>
> Commit complete.
>
> SEANTEST> select distinct sal from foobar
> 2 /
>
> SAL
> ----------
> 1886
>
>
> SEANTEST> shutdown immediate;
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
> SEANTEST> !cp /u02/oradata/seantest/stuff.dbf
/u02/oradata/seantest/stuff.dbf.bck
>
> [Take clean, cold backup of the tablespace we'll be dealing with]
>
> SEANTEST> startup
> ORACLE instance started.
>
> Total System Global Area 149659808 bytes
> Fixed Size 73888 bytes
> Variable Size 92061696 bytes
> Database Buffers 57344000 bytes
> Redo Buffers 180224 bytes
> Database mounted.
> Database opened.
> SEANTEST> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
> ---------- ---------- ---------- ---------- ---------- --- ---------------
- ------------- ---------
> 4 1 1885 10485760 2 NO INACTIVE
591869 17-JUL-02
> 5 1 1884 10485760 2 NO INACTIVE
591868 17-JUL-02
> 6 1 1886 10485760 2 NO CURRENT
591908 17-JUL-02
>
> SEANTEST> alter system switch logfile;
>
> System altered.
>
> [OK, should be on 1887 now]
>
> SEANTEST> select * from v$logfile
> 2 /
>
> GROUP# STATUS
> ---------- -------
> MEMBER
> ----------------------------------------------------
> 5
> /u01/app/oracle/oradata/seantest/redo05.rdo
>
> 5
> /u02/oradata/seantest/redo05.rdo
>
> 6
> /u01/app/oracle/oradata/seantest/redo06.rdo
>
> 6
> /u02/oradata/seantest/redo06.rdo
>
> 4
> /u01/app/oracle/oradata/seantest/redo04.rdo
>
> 4
> /u02/oradata/seantest/redo04.rdo
>
>
> 6 rows selected.
>
> SEANTEST> !cp /u02/oradata/seantest/redo06.rdo
/u02/oradata/seantest/redo06_1886.arc
>
> [Manually "archive" group 6, sequence 1886 for later]
>
> SEANTEST> update foobar set sal = (select sequence# from v$log where
status = 'CURRENT');
>
> 14 rows updated.
>
> SEANTEST> commit
> 2 ;
>
> Commit complete.
>
> SEANTEST> select distinct sal from foobar;
>
> SAL
> ----------
> 1887
>
> SEANTEST> alter system switch logfile;
>
> System altered.
>
> SEANTEST> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
> ---------- ---------- ---------- ---------- ---------- --- ---------------
- ------------- ---------
> 4 1 1888 10485760 2 NO CURRENT
591990 18-JUL-02
> 5 1 1887 10485760 2 NO INACTIVE
591986 18-JUL-02
> 6 1 1886 10485760 2 NO INACTIVE
591908 17-JUL-02
>
> SEANTEST> !cp /u02/oradata/seantest/redo05.rdo
/u02/oradata/seantest/redo05_1887.arc
>
> [Another manual archive]
>
>
> SEANTEST> update foobar set sal = (select sequence# from v$log where
status = 'CURRENT');
>
> 14 rows updated.
>
> SEANTEST> commit;
>
> Commit complete.
>
> SEANTEST> select distinct sal from foobar;
>
> SAL
> ----------
> 1888
>
> SEANTEST> alter system switch logfile;
>
> System altered.
>
> SEANTEST> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
> ---------- ---------- ---------- ---------- ---------- --- ---------------
- ------------- ---------
> 4 1 1888 10485760 2 NO ACTIVE
591990 18-JUL-02
> 5 1 1887 10485760 2 NO INACTIVE
591986 18-JUL-02
> 6 1 1889 10485760 2 NO CURRENT
591994 18-JUL-02
>
> SEANTEST> !cp /u02/oradata/seantest/redo04.rdo
/u02/oradata/seantest/redo04_1888.arc
>
> [And another manual archive]
>
>
> SEANTEST> update foobar set sal = (select sequence# from v$log where
status = 'CURRENT');
>
> 14 rows updated.
>
> SEANTEST> commit;
>
> Commit complete.
>
> SEANTEST> select distinct sal from foobar;
>
> SAL
> ----------
> 1889
>
> SEANTEST> alter system switch logfile;
>
> System altered.
>
> SEANTEST> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
> ---------- ---------- ---------- ---------- ---------- --- ---------------
- ------------- ---------
> 4 1 1888 10485760 2 NO INACTIVE
591990 18-JUL-02
> 5 1 1890 10485760 2 NO CURRENT
591998 18-JUL-02
> 6 1 1889 10485760 2 NO INACTIVE
591994 18-JUL-02
>
>
> [So, our original sequence (1886) and the one right after it (1887), which
were clearly written to *after* the cold backup with new
> transactions, have been overwritten and are *gone* from the online redo
log pool.]
>
> SEANTEST> shutdown
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
> SEANTEST> !mv /u02/oradata/seantest/stuff.dbf
/u02/oradata/seantest/stuff.dbf.current
>
> ["Trash" the datafile.]
>
> SEANTEST> startup
> ORACLE instance started.
>
> Total System Global Area 149659808 bytes
> Fixed Size 73888 bytes
> Variable Size 92061696 bytes
> Database Buffers 57344000 bytes
> Redo Buffers 180224 bytes
> Database mounted.
> ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
> ORA-01110: data file 13: '/u02/oradata/seantest/stuff.dbf'
>
> [Expected - datafile is gone. So restore the backup...]
>
> SEANTEST> !mv /u02/oradata/seantest/stuff.dbf.bck
/u02/oradata/seantest/stuff.dbf
>
> SEANTEST> alter database open;
> alter database open
> *
> ERROR at line 1:
> ORA-01113: file 13 needs media recovery
> ORA-01110: data file 13: '/u02/oradata/seantest/stuff.dbf'
>
>
> [So, try recovery - not only is instance recovery possible, recovery from
any online redo log group is impossible since they've all
> been overwritten...]
>
> SEANTEST> recover datafile 13
> ORA-00279: change 591957 generated at 07/18/2002 13:30:38 needed for
thread 1
> ORA-00289: suggestion :
/u01/app/oracle/admin/seantest/arch/arch_1_1886.arc
> ORA-00280: change 591957 for thread 1 is in sequence #1886
>
>
> Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
>
> [Looking for 1886, just have to tell it where to find it]
>
> /u02/oradata/seantest/redo06_1886.arc
> ORA-00279: change 591986 generated at 07/18/2002 13:34:14 needed for
thread 1
> ORA-00289: suggestion :
/u01/app/oracle/admin/seantest/arch/arch_1_1887.arc
> ORA-00280: change 591986 for thread 1 is in sequence #1887
> ORA-00278: log file '/u02/oradata/seantest/redo06_1886.arc' no longer
needed for this recovery
>
>
> Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
>
> [Looking for 1886, just have to tell it where to find it]
>
> /u02/oradata/seantest/redo05_1887.arc
> Log applied.
> Media recovery complete.
> SEANTEST> alter database open;
>
> Database altered.
>
> SEANTEST> select * from v$log
> 2 ;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM
> ---------- ---------- ---------- ---------- ---------- --- ---------------
- ------------- ---------
> 4 1 1888 10485760 2 NO INACTIVE
591990 18-JUL-02
> 5 1 1890 10485760 2 NO CURRENT
591998 18-JUL-02
> 6 1 1889 10485760 2 NO INACTIVE
591994 18-JUL-02
>
> SEANTEST> select distinct sal
> 2 from foobar;
>
> SAL
> ----------
> 1889
>
> SEANTEST>
>
>
>
>
> So, well, I certainly learned something new from all of this - wouldn't
have thought Oracle would play this game. I would have
> assumed that Oracle would see NOARCHIVELOG mode and just not even let you
*try* to apply logs that were long gone. I stand
> corrected.
>
> Thanks Howard,
> Sean
Received on Thu Jul 18 2002 - 15:39:34 CDT
