Applying Redolog or Archivelogs?

Hi there:

In recovering, particularly from Hot backup (Incomplete Recovery), I always face a problem, I do not know if I have to apply Redologs or Archivelog. I am really confused here.

Database is in Archive Log Mode, A backup after Resetlogs is available, I am using window XP, and Oracle 9i.

1.   Table TEST@ does not exist and I dropped table TEST1as follows:
=============================================

21:41:52 SQL> spool c:\temp01.lst
21:42:08 SQL> select count(*) from test1;

  COUNT(*)
----------
     11006

21:42:33 SQL> select count(*) from test2;
select count(*) from test2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


21:42:43 SQL> select count(*) from test3;

  COUNT(*)
----------
      1098

21:42:55 SQL> drop table test1;

Table dropped.

2.   Time and Date when Test1 table is dropped.
================================

21:43:25 SQL> select sysdate from dual;

SYSDATE
---------
28-MAY-05

21:44:13 SQL>

(TABLE IS DROPPED AT 28 MAY 2005 AT 21:42:55) HENCE ANY SEQUENCE NUMBER JUST BEFORE THAT TIME ON 28TH OF MAY IF APPLIED SHOULD BE ABLE TO RECOVER TABLE TEST1? SHOULD NOT IT BE THIS WAY? PLEASE REPLY.

3.   I then created the test2 table, inserted rows, committed and then applied archive log as follows:
================================================================


21:45:32 SQL> create table test2 (num number);

Table created.

21:46:25 SQL> insert into test2 values(1);

1 row created.

21:46:43 SQL> insert into test2 values(9);

1 row created.

21:46:57 SQL> insert into test2 select rownum from dict;

1098 rows created.

21:47:24 SQL> commit;

Commit complete.

21:47:28 SQL> select count(*) from test2;

  COUNT(*)
----------
      1100

21:47:54 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle\practice\archive\CH05
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
21:48:05 SQL> alter system switch logfile;

System altered.

21:48:29 SQL> /

System altered.

21:48:31 SQL> /

System altered.

21:48:37 SQL> /

System altered.

21:48:50 SQL> /

System altered.

21:48:57 SQL> /

System altered.

21:49:03 SQL> commit;

Commit complete.

4.   Assume that I dropped table TEST1 by mistake, and I do not know when (Just assume), though some time I have some idea (As in the current situation) about the date and time when it happened. I performed the following:
===============================================================================

5.   Since unlike dropping a tablespace where it marks its inception in Alert.log, dropping a tablespace does not. Hence I submit the command:
=================================================================================
21:49:08 SQL> select sequence# from v$log where status = 'CURRENT;

SEQUENCE
------------
             8

6.   I then submit the following:
====================

21:50:11 SQL> select sequence#, first_change#, to_char(first_time, 'DD-MON-YY HH24:MI:SS') from v$log_history;

SEQUENCE#   FIRST_CHANGE#  TO_CHAR(FIRST_CHANGE
-------------    -------------------   ------------------------------
1                   215107                28-MAY-05  14:32:46
2                   235547                28-MAY-05  20:15:07
3                   235818                28-MAY-05  21:48:29
4                   235819                28-MAY-05  21:48:31
5                   235821                28-MAY-05  21:48:37
6                   235822                28-MAY-05  21:48:50
7                   235823                28-MAY-05  21:48:57

7.   I then shutdown the Database.
======================

21:54:11 SQL> Shutdown
Database Closed
Database dismounted
Oracle Instance Shutdown

8.   I then restored the database by copying the *.dbf files from the directory I backed up the files after previously Resetlogs optin (Some how I restored and recoved from an inclomplete hot back up and opened the database using resetlogs and then took a closed backup of the DB).
===============================================================================

9.   I mounted the database
==================

21:56:00 SQL> startup mount
Oracle instance started.
Total System Global Area 118255568 bytes
Fixed Size                            282576 bytes
Variable Size                    83886080 bytes
Database buffers              33554432 bytes
Redo buffers                       532480 bytes
Database Mounted.


10.   Now the problem started: The table TEST1 whic I want to recover and was dropped is not recovered. More over I was thinking that as I used Alter System Archive logfile, I will recover from an archive log file, where as the database was recovered and opened when I applied Redo log, not archived log? I used trial and error.
===============================================================================

22:16:57 SQL> recover database until cancel;    

ORA-00279: change 235548 generated at 05/28/2005 20:15:09 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00002.001
ORA-00280: change 235548 for thread 1 is in sequence #2


22:17:00 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied the Archive log and got he following: This should work because SEQUENCE# 2 with first_change# @ 28-May-2005 has a first_time column value of 20:15:07 which is just before the date and time when TEST1 was dropped. But it did not work.
*****************************************************************************************

C:\ORACLE\PRACTICE\ARCHIVE\CH05\2.arc
ORA-00279: change 235818 generated at 05/28/2005 21:48:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00003.001
ORA-00280: change 235818 for thread 1 is in sequence #3
ORA-00278: log file 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\2.arc' no longer needed for this recovery


22:17:53 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied the cancel
_______________

cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


ORA-01112: media recovery not started


22:18:18 SQL> recover database until cancel;
ORA-00279: change 235818 generated at 05/28/2005 21:48:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00003.001
ORA-00280: change 235818 for thread 1 is in sequence #3


22:18:55 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied the Archive Log:
___________________

C:\ORACLE\PRACTICE\ARCHIVE\CH05\3.arc

ORA-00279: change 235819 generated at 05/28/2005 21:48:31 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00004.001
ORA-00280: change 235819 for thread 1 is in sequence #4
ORA-00278: log file 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\3.arc' no longer needed for this recovery


22:19:30 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied Cancel:
____________

cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


ORA-01112: media recovery not started


22:19:37 SQL> recover database until cancel;
ORA-00279: change 235819 generated at 05/28/2005 21:48:31 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00004.001
ORA-00280: change 235819 for thread 1 is in sequence #4


22:19:52 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied the Archive Log:
____________________

C:\ORACLE\PRACTICE\ARCHIVE\CH05\4.arc
ORA-00279: change 235821 generated at 05/28/2005 21:48:37 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00005.001
ORA-00280: change 235821 for thread 1 is in sequence #5
ORA-00278: log file 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\4.arc' no longer needed for this recovery


22:20:17 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied the Archive log:
___________________

C:\ORACLE\PRACTICE\ARCHIVE\CH05\5.arc
ORA-00279: change 235822 generated at 05/28/2005 21:48:50 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00006.001
ORA-00280: change 235822 for thread 1 is in sequence #6
ORA-00278: log file 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\5.arc' no longer needed for this recovery


22:20:25 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied the Archive log:
___________________

C:\ORACLE\PRACTICE\ARCHIVE\CH05\6.arc
ORA-00279: change 235823 generated at 05/28/2005 21:48:57 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00007.001
ORA-00280: change 235823 for thread 1 is in sequence #7
ORA-00278: log file 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\6.arc' no longer needed for this recovery


22:20:42 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


ORA-01112: media recovery not started


22:20:57 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


22:21:13 SQL> recover database until cancel;
ORA-00279: change 235823 generated at 05/28/2005 21:48:57 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00007.001

Applied Archive log:
_________________

ORA-00280: change 235823 for thread 1 is in sequence #7


22:21:54 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00328: archived log ends at change 214697, need later change 235823
ORA-00334: archived log: 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00007.001'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


22:22:12 SQL> recover database until cancel;
ORA-00279: change 235823 generated at 05/28/2005 21:48:57 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00007.001
ORA-00280: change 235823 for thread 1 is in sequence #7


22:22:39 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied Archivelog:
_______________

C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00007.001
ORA-00328: archived log ends at change 214697, need later change 235823
ORA-00334: archived log: 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00007.001'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


22:22:53 SQL> recover database until cancel;
ORA-00279: change 235823 generated at 05/28/2005 21:48:57 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00007.001
ORA-00280: change 235823 for thread 1 is in sequence #7


22:23:09 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied Archivelog:
_______________

C:\ORACLE\PRACTICE\ARCHIVE\CH05\7.arc_back
ORA-00279: change 235824 generated at 05/28/2005 21:49:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00008.001
ORA-00280: change 235824 for thread 1 is in sequence #8
ORA-00278: log file 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\7.arc_back' no longer needed for this recovery


22:23:56 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


ORA-01112: media recovery not started


22:24:10 SQL> recover database until cancel;
ORA-00279: change 235824 generated at 05/28/2005 21:49:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00008.001
ORA-00280: change 235824 for thread 1 is in sequence #8


22:24:22 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log 'C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00008.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\PRACTICE\SYSTEM01.DBF'


22:24:23 SQL> select * from v$log_history:
22:26:32   2  ;
select * from v$log_history:
                           *
ERROR at line 1:
ORA-00933: SQL command not properly ended


22:26:35 SQL> select * from v$log_history;

     RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
---------- ---------- ---------- ---------- ------------- --------- ------------
         1  559512908          1          1        215107 28-MAY-05       235547
         2  559518509          1          2        235547 28-MAY-05       235818
         3  559518511          1          3        235818 28-MAY-05       235819
         4  559518517          1          4        235819 28-MAY-05       235821
         5  559518530          1          5        235821 28-MAY-05       235822
         6  559518537          1          6        235822 28-MAY-05       235823
         7  559518543          1          7        235823 28-MAY-05       235824

7 rows selected.

22:26:48 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          8  104857600          1 NO  CURRENT                 235824 28-MAY-05
         2          1          6  104857600          1 YES INACTIVE                235822 28-MAY-05
         3          1          7  104857600          1 YES INACTIVE                235823 28-MAY-05

22:27:14 SQL> recover database until cancel;
ORA-00279: change 235824 generated at 05/28/2005 21:49:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRACTICE\ARCHIVE\CH05\ARC00008.001
ORA-00280: change 235824 for thread 1 is in sequence #8


22:27:51 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Applied the REDOLOG not because I know that it is to be applied but just as one of the step of trial and error, and the recovery is completed, though I could not find the lost table TEST1
*****************************************************************************

c:\oracle\oradata\practice\redo01.log
Log applied.
Media recovery complete.
22:28:13 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


22:28:47 SQL> alter database open resetlogs;

Database altered.

22:29:22 SQL> select count(*) from test1;
select count(*) from test1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


22:29:38 SQL> select count(*) from test2;

  COUNT(*)
----------
      1100

22:29:48 SQL> select count(*) from test3;

  COUNT(*)
----------
      1098






Start Free Trial