Re: How to apply archived redo logfiles on a consistent database

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 6 Mar 2004 10:38:18 +0100
Message-ID: <40499bde$0$290$626a14ce@news.free.fr>

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> a écrit dans le message de news:l4mh40pabpmu5ni1pp257f0idtunpnebqh_at_4ax.com...
> On Fri, 5 Mar 2004 19:58:40 +0100, "Michel Cadot"
> <micadot{at}altern{dot}org> wrote:
>

<snip>
> >
> >I don't test it but it seems to me there is 2 ways:
> >1- don't restore the control file, use the current one and "recover database"
> >2- if you lose your control file, after the restore, don't open the database,
> >dump the control file: alter database backup control file to trace,
> >recreate the control file with the file generated and use
> >"recover database using backup control file".
> >
> >May be there is a simpler way.
> >
> >Regards
> >Michel Cadot
> >
>
> 1 will definitely work
> 2 if you still have an older controlfile, restoring it, dumping it,
> and issue recover database using backup controlfile doesn't make
> sense.
> The only difference between a physical backup and a backup to trace is
> the actual database state. So you don't gain anything by dumping the
> controlfile.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

I'm afraid i'm not agree with you, the main difference physical backup and dump into trace is that there is no information of the current state and thank for that Oracle can recover after the date of the original database. I do the following test (8.1.7 on WinNT4):

1- Make a cold backup at 8:52 (datafiles and control files, scn 1891366, seq 3406)
2- Work until 8:58 (scn 1891618, seq 3426)
3- Crash at 8:58 the db files (datafiles, redo, control), keep archived between seq 3406 and 3425

(3426 was not yet archived)
4 - Restore the 8:52 files (data and control) and do the following:

SVRMGR> connect internal
Connected.
SVRMGR> startup mount
ORACLE instance started.

Total System Global Area                          8001564 bytes
Fixed Size                                          75804 bytes
Variable Size                                     5799936 bytes
Database Buffers                                  2048000 bytes
Redo Buffers                                        77824 bytes

Database mounted.
SVRMGR> alter database backup controlfile to trace resetlogs; Statement processed.
SVRMGR> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup nomount
ORACLE instance started.

Total System Global Area                          8001564 bytes
Fixed Size                                          75804 bytes
Variable Size                                     5799936 bytes
Database Buffers                                  2048000 bytes
Redo Buffers                                        77824 bytes
SVRMGR> @createCF            <-- script from the trace file keeping only the create controlfile

statement
Statement processed.
SVRMGR> recover database until change 1891615 using backup controlfile;

ORA-00279: change 1891389 generated at 03/06/2004 08:52:13 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVES\MIKE\ARCHIVE\001_03406.ARC
ORA-00280: change 1891389 for thread 1 is in sequence #3406

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto
Log applied.

ORA-00279: change 1891420 generated at 03/06/2004 08:57:06 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVES\MIKE\ARCHIVE\001_03407.ARC
ORA-00280: change 1891420 for thread 1 is in sequence #3407
ORA-00278: log file 'D:\ORACLE\ARCHIVES\MIKE\ARCHIVE\001_03406.ARC' no longer needed for this

recovery
Log applied.
<snip>

ORA-00279: change 1891611 generated at 03/06/2004 08:58:06 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVES\MIKE\ARCHIVE\001_03425.ARC
ORA-00280: change 1891611 for thread 1 is in sequence #3425
ORA-00278: log file 'D:\ORACLE\ARCHIVES\MIKE\ARCHIVE\001_03424.ARC' no longer needed for this

recovery
Log applied.
Media recovery complete.
SVRMGR> alter database open resetlogs;
Statement processed.
SVRMGR> select * from v$database;
DBID NAME CREATED RESETLOGS_ RESETLOGS PRIOR_RESE PRIOR_RES LOG_MODE CHECKPOINT ARCHIVE_CH CONTROL CONTROLFI CONTROLFIL CONTROLFIL CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE

---------- --------- --------- ---------- --------- ---------- --------- ------------ ---------- ---
------- ------- --------- ---------- ---------- --------- ----------- --------- ----------
1181413965 MIKE      06-MAR-04    1891616 06-MAR-04          1 09-SEP-03 ARCHIVELOG      1891617
0 CURRENT 06-MAR-04       9858    1891644 06-MAR-04 NOT ALLOWED 06-MAR-04 READ WRITE

1 row selected.
SVRMGR> exit
Server Manager complete.

So i recovered my database till the last change in the archived logs (1891615).

Regards
Michel Cadot Received on Sat Mar 06 2004 - 03:38:18 CST