RE: Recovery scenario -- ArchiveLog pointing to the Next ArchiveLog
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: "D'Hooge Freek" <Freek.D'Hooge@xxxxxxxxx>, "niall.litchfield@xxxxxxxxx" <niall.litchfield@xxxxxxxxx>, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- Date: Sat, 02 Feb 2008 16:04:13 +0800
"I would think that oracle finds the next scn and timestamp in the
archived redo log it just has applied. This way oracle is capable to
just follow the chain of archived redo logs."
Yes, that would certainly be the case.
I did a dump of an archivelog and could see the entry pointing to the
SCN and timestamp of the next logfile.
And that information relating to the next log is in the header.
{We know that there is no difference in the format of an ArchiveLog
or an Online Redo Log}
That would mean that at every log switch from group#1 to
group#2, Oracle increments the
SCN and updates the header of group#1 with this as the "Next scn:"
and the header of group#2
with this value as the "Low scn:"
Thus the "Next scn:" entry in one file provides the first scn and
timestamp of the next file.
(whether the file is an archivelog or the online redo image that
hasn't yet been overwritten
by the logwriter) [see the dumps below and the entiries marked
as "---->> ".
The entry "Enabled SCN:" in my dumps is the time of the last OPEN RESETLOGS
{as I also simulated a recovery scenario} !]
That is how Oracle constructs the message [using the "Next scn:"
entry from the header of
the last {current} file just applied] :
"ORA-00279: change <scnnumber> generated at <timestamp> needed for thread 1"
As for the question of the first file name, it constructs the file
name from the log_archive_destination
and log_archive_format. Thereafter, for each subsequent file, it
merely increments the sequence
number in the log_archive_format. In an RAC environment, when doing
a recovery, it gets the
file names for the "local" instance {"thread"} properly but doesn't
know the file name for the other instance[s]
{threads} and prompts you to enter the first file name for the
archivelog. Once you enter the correct first file
name for the other instance[s], it proceeds to construct the
filenames for subsequent sequence numbers
These points are explained [at least] in the 9i "User Managed Backup
and Recovery Guide",
Chapter 4. [10g doesn't even seperate User Managed Backup and
Recovery Guide relegating it to
a seperate part of the "Backup and Recovery Advanced User's Guide].
I even tested this by changing the log_archive_format and seeing
that Oracle prompts me for
archivelogs with the new filename format.
As for Niall's question :
"Where we were told that the backups including the controlfile backup
were from several hours before 5 in the morning. The recovery process
seems to know when this change was created - but I don't really see
how it can - unless as Andrew suggested the controlfile was actually
from after 5am."
if we look at Ram's email we see that : log seq#11592 had already been applied
at his first RECOVER attempt without the "UNTIL CANCEL". Mind you,
the head of the file #11592 specified the SCN and timestamp of #11593.
----------------------------------------------------------------------------------------
ORA-00279: change 11805525061 generated at 01/30/2008 00:40:01 needed
for thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11592_620315068.dbf
ORA-00280: change 11805525061 for thread 1 is in sequence #11592
16:50:26 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 11805558903 generated at 01/30/2008 01:23:32 needed
for thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11593_620315068.dbf
ORA-00280: change 11805558903 for thread 1 is in sequence #11593
ORA-00278: log file
'/thrundo/oradata/HRDEV/arch/1_11592_620315068.dbf' no longer
needed for this recovery
----------------------------------------------------------------------------------------
At his next attempt , again without the "UNTIL CANCEL",
it proceeded to log seq#11593 which then pointed to seq#11594
[because of the entry
in the header of file #11593], even though 11594 was of 5am
ORA-00279: change 11805558903 generated at 01/30/2008 01:23:32 needed
for thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11593_620315068.dbf
ORA-00280: change 11805558903 for thread 1 is in sequence #11593
16:54:31 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 11805815756 generated at 01/30/2008 05:00:15 needed
for thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11594_620315068.dbf
ORA-00280: change 11805815756 for thread 1 is in sequence #11594
ORA-00278: log file
'/thrundo/oradata/HRDEV/arch/1_11593_620315068.dbf' no longer
needed for this recovery
Having applied all the minimum archivelogs to bring the datafiles to
a consistent state
(ie at least upto the point of the last "END BACKUP" or any time
thereafter), the
OPEN RESETLOGS worked. In order to be able to do that, he had to
tell Oracle that
he wanted an Incomplete Recovery which he did so with the "UNTIL CANCEL".
=============================================================================
DUMP OF REDO FROM FILE 'C:\OR10G2DB\ARCH\MY_ARC_6_645627350_1.BAK'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=138573118=0x842753e, Db Name='OR10G2DB'
Activation ID=195152594=0xba1cad2
Control Seq=6381=0x18ed, File size=16384=0x4000
File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000006, SCN 0x0000001780b3-0x000000178184"
thread: 1 nab: 0x3df5 seq: 0x00000006 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x267b7dd6 scn: 0x0000.00177396 (1536918)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x267aaf08 scn: 0x0000.0016ff67 (1507175)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.001780b3 (1540275) 02/02/2008 15:12:30
---->> Next scn: 0x0000.00178184 (1540484) 02/02/2008 15:12:31
Enabled scn: 0x0000.00177396 (1536918) 02/02/2008 12:55:50
Thread closed scn: 0x0000.001780b3 (1540275) 02/02/2008 15:12:30
Disk cksum: 0x58a3 Calc cksum: 0x58a3
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 3062 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x11
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
=============================================================================
DUMP OF REDO FROM FILE 'C:\OR10G2DB\ARCH\MY_ARC_7_645627350_1.BAK'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=138573118=0x842753e, Db Name='OR10G2DB'
Activation ID=195152594=0xba1cad2
Control Seq=6386=0x18f2, File size=16384=0x4000
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000007, SCN 0x000000178184-0x00000017823d"
thread: 1 nab: 0x3df5 seq: 0x00000007 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x267b7dd6 scn: 0x0000.00177396 (1536918)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x267aaf08 scn: 0x0000.0016ff67 (1507175)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
---->> Low scn: 0x0000.00178184 (1540484) 02/02/2008 15:12:31
---->> Next scn: 0x0000.0017823d (1540669) 02/02/2008 15:12:39
Enabled scn: 0x0000.00177396 (1536918) 02/02/2008 12:55:50
Thread closed scn: 0x0000.00178184 (1540484) 02/02/2008 15:12:31
Disk cksum: 0x4adf Calc cksum: 0x4adf
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 5661 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x11
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
==================================================================
DUMP OF REDO FROM FILE 'C:\OR10G2DB\REDO01.DBF'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=138573118=0x842753e, Db Name='OR10G2DB'
Activation ID=195152594=0xba1cad2
Control Seq=6389=0x18f5, File size=16384=0x4000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000008, SCN 0x00000017823d-0x000000178318"
thread: 1 nab: 0x2763 seq: 0x00000008 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x267b7dd6 scn: 0x0000.00177396 (1536918)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x267aaf08 scn: 0x0000.0016ff67 (1507175)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
---->> Low scn: 0x0000.0017823d (1540669) 02/02/2008 15:12:39
---->> Next scn: 0x0000.00178318 (1540888) 02/02/2008 15:12:40
Enabled scn: 0x0000.00177396 (1536918) 02/02/2008 12:55:50
Thread closed scn: 0x0000.0017823d (1540669) 02/02/2008 15:12:39
Disk cksum: 0xc4f Calc cksum: 0xc4f
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 5500 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
==================================================================
DUMP OF REDO FROM FILE 'C:\OR10G2DB\REDO02.DBF'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=138573118=0x842753e, Db Name='OR10G2DB'
Activation ID=195152594=0xba1cad2
Control Seq=6389=0x18f5, File size=16384=0x4000
File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000009, SCN 0x000000178318-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000009 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x267b7dd6 scn: 0x0000.00177396 (1536918)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x267aaf08 scn: 0x0000.0016ff67 (1507175)
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
---->> Low scn: 0x0000.00178318 (1540888) 02/02/2008 15:12:40
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.00177396 (1536918) 02/02/2008 12:55:50
Thread closed scn: 0x0000.00178318 (1540888) 02/02/2008 15:12:40
Disk cksum: 0xad3d Calc cksum: 0xad3d
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
==================================================================
At 11:31 PM Thursday, D'Hooge Freek wrote:
> the RECOVER command then actually _builds_ the archivelog
> file names [using log_archive_dest and log_archive_format].
>
> It wasn't the file name per se but the creation time as in the message
>
> ORA-00279: change 11805815756 generated at 01/30/2008 05:00:15
needed for > thread 1
>
> Where we were told that the backups including the controlfile
backup were > from several hours before 5 in the morning. The
recovery process seems to > know when this change was created - but
I don't really see how it can -
> unless as Andrew suggested the controlfile was actually from after 5am.
> I'm sure I used to know this once, but I'm feeling really old and stupid
> right now.
>
I would think that oracle finds the next scn and timestamp in the
archived redo log it just has applied. This way oracle is capable to
just follow the chain of archived redo logs.
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Niall Litchfield
Sent: donderdag 31 januari 2008 16:18
To: Hemant K Chitale
Cc: jeremiah@xxxxxxxxxxx; raindoctor@xxxxxxxxx; veeeraman@xxxxxxxxx;
Oracle List
Subject: Re: Recovery scenario
On Jan 31, 2008 2:58 PM, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx> wrote:
A "using Backup Controlfile" implicitly means
"the controlfile is older then the datafiles"
I've always thought of it as 'ignore the scn in the controlfile' not
the controlfile is older..
the RECOVER command then actually _builds_ the archivelog
file names [using log_archive_dest and log_archive_format].
It wasn't the file name per se but the creation time as in the message
ORA-00279: change 11805815756 generated at 01/30/2008 05:00:15
needed for thread 1
Where we were told that the backups including the controlfile backup
were from several hours before 5 in the morning. The recovery
process seems to know when this change was created - but I don't
really see how it can - unless as Andrew suggested the controlfile
was actually from after 5am. I'm sure I used to know this once, but
I'm feeling really old and stupid right now.
Niall
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com
"There is more to life than increasing its speed."
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: Recovery scenario -- ArchiveLog pointing to the Next ArchiveLog
