| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Question about Archivemode
"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
SEANTEST> select tablespace_name
2 from dba_tables
3 where table_name = 'FOOBAR';
TABLESPACE_NAME
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 bytesDatabase mounted.
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 bytesDatabase mounted.
[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:07:00 CDT
 |
 |