xcellent
vel, December 20, 2001 - 7:37 am UTC
i like this site very much.it very usefull for me
thax tom
Jayakumar, October 14, 2002 - 9:17 am UTC
Simply Great ...
A reader, February 04, 2003 - 2:48 pm UTC
"In hot backup mode, the entire block is logged the FIRST TIME. "
--Can you please explain what you mean by FIRST TIME above?
February 04, 2003 - 3:52 pm UTC
the first time a block is modified AFTER a hot backup for the tablespace it is in starts causes the full block image to be logged.
Subsequent modifications to that block will go back to changes only logging. Until the next hot backup of course.
A reader, February 04, 2003 - 10:58 pm UTC
Xcellent. Know I got the clear idea what inside is happening at hotbackup time.
Very thankful to u..............
Reader
A reader, February 16, 2003 - 8:02 pm UTC
During Hot Backup Mode, file is being backed up (unix copy)
1. the block being copied may be in the buffer pool and
being changed
2. The block may be read from the disk
How does Oracle handle the redo in these cases. I am sure
you have explained this , in another posing, I just
couldn't locate it
February 17, 2003 - 9:59 am UTC
when you place a tablespace in hot backup mode, oracle will log extra information for a block the first time it is modified whilst the tablespace it belongs to is in hot backup mode.
Say tablespace X containing file 55 is put into hot backup mode.
You modify block 123 in file 55 -- this generates redo. Oracle will log the ENTIRE block image instead of just changed bytes.
You commit.
Someone else modifies blocks 123 and 124 in file 55. Oracle will log just changed bytes for block 123 but a full block image copy for 124.
Confusing Term
Amarjyoti Dewri, February 21, 2003 - 6:07 am UTC
Hi,
The line
-------------------
the datafile headers which contain the SCN of the last completed checkpoint
are NOT updated while a file is in hot backup mode. This lets the recovery
process understand what archive redo log files might be needed to fully recover
this file.
-------------------
is some what confusing? What i want to ask is "How does this lets the recovery
process understand what archive redo log files might be needed to fully recover
this file." a similar question was asked by some one and what i answered was
"When you give alter tablespace begin backup command, checkpoint occurs for all datafiles in the HOT BACKUP. the datafile header checkpoints are advanced to the SCN captured when the begin backup command is issued. And this SCN goes in the backup file as well.
Alter tablespace end backup command creates a redo record containing the begin backup checkpoint SCN. Also the datafile checkpoint is advanced to the database checkpoint.
So from this redo record and the initial SCN in the backup datafile, oracle knows which all redo needs to be applied to the restored backup file to make it consistent.
Also my understanding is that when we give the alter tablespace end backup command all the redo generated during the backup is applied to the datafile to advance the datafile SCN to database SCN.
The same has to be done to the backup file to bring it to consistent state, hence the header is freezed so that oracle knows that redo generated between the SCN at begin backup and end backup need to be applied. (More ever the blocks changed during the backup are rewritten from the redo)
Or in other words if the SCN were not freezed, they would also be copied in the backup datafile. So how would oracle know if the datafile is inconsistent when its restored, as the SCN of the backup datafile would match the SCN at the alter tablespace end backup command. And Oracle uses SCN numbers to apply the redo."
Am i right or wrong??
February 21, 2003 - 9:57 am UTC
this:
<quote>
Also my understanding is that when we give the alter tablespace end backup
command all the redo generated during the backup is applied to the datafile to
advance the datafile SCN to database SCN.
</quote>
is absolutely wrong -- have you ever had an end backup ask you for an archive? Nope, never will - we constantly write to the datafiles during the hot backup. The SCN recorded in the header tells us how far back in the redo stream one needs to go to recover this file.
Read the answer above again -- things like:
...
Lets say they are and the OS blocking read factor is
512bytes (the OS reads 512 bytes from disk at a time). The backup program goes
to read an 8k Oracle block. The OS gives it 4k. Meanwhile -- DBWR has asked to
rewrite this block. the OS schedules the DBWR write to occur right now. The
entire 8k block is rewritten. The backup program starts running again
(multi-tasking OS here) and reads the last 4k of the block.
.....
should definitely make it clear that the datafiles are WRITTEN do whilst they are being backed up (else we would not have this redo issue at all -- the logging of the full block image)
Thanks that clears my doubts.
Amarjyoti Dewri, February 21, 2003 - 11:14 am UTC
What a relief... :-)
hot backup and datafile writing
gaurav, May 29, 2003 - 12:44 pm UTC
Hello Tom,
As you mentioned that while the hot backup is running oracle will keep on writing to datafiles. However, I am unable to understand this , my doubt is-
1. I started the hot backup of tbs X
2. Some one has inserting a large number of records (say more than 10,000) .
3. Where will oracle write these records . Will it be redo logs and data files both ? what is the reason of writing on both places?
3. If some one has issued the query from where oracle will read the records?
Regards
Gaurav
May 29, 2003 - 1:54 pm UTC
oracle writes them to the datafiles as normal.
everything happens "just as normal".
Oracle deals with fuzzy files all of the time, it understands what to do with a file upon recovery. the only thing hot backup mode does really is to freeze the datafile header and generate a little extra redo for a block the first time it is modified.
otherwise, everything happens "as normal"
some confusion
Anurag, September 22, 2003 - 12:47 pm UTC
Dear Tom,
Please clear my doubts. Why - during online backup we put tablespace in a backup mode. I wrote a script to insert and delete few thousand records in 'X' table. during execution I simply copied the datafiles in bkup folder then after committing and shutting db. I corrupted relevant datafile Then startup ...error message.....copied dbf from bkup and recover datafile 8 ......auto archive applied....log applied...media recovery complete.
When backup and recovery is done...why then begin backup and end backup.....
September 22, 2003 - 1:36 pm UTC
you got lucky -- hope you don't do that for real.
Oracle works with database blocks in 2, 4, 8, 16, or 32k pieces. Say 8k is what you are using.
operating systems read and write files using their own chunk size. say 512bytes (1/2 k) for example -- a common size.
So, you are copying a datafile -- you are using the OS to do that. You are reading it in 512byte chunks.
You start reading block 55 in file 5 using "copy". You got 1/2 of the way through it (meaning you read 4k of data). You get pre-empted and along comes DBWR who decides to lay down a new version of that block on disk (checkpoint, flush dirty buffers). DBWR was lucky enough to write all 8k. Your copy picks back up and reads the next 4k of the block -- but -- it is 4k of the block at a totally different point in time.
This is known as a fractured block -- happens easily on a system with many people reading and writing the same file at the same time.
Now, you restore this copy -- we have a block that is half at time T0 and half at time T1 -- we cannot recover that block -- UNLESS we just happened to keep the full block image from the first change after starting the backup elsewhere -- which is what we did. So, we are able to recover this block using that image.
Note that if you use RMAN, this isn't an issue. RMAN is not affected by OS vs DB block sizes -- it knows how to read an Oracle datafile safely, with RMAN backups, you don't put a tablespace into backup mode, it is not necessary.
hot backup ....
Shankar, October 11, 2003 - 7:10 pm UTC
Tom, If the OS block size and Oracle block size are the same, does oracle still log entire block image when the tablespace is in backup mode? Thanks.
October 11, 2003 - 7:46 pm UTC
yup.
Excellent
Venkata krishnan, November 26, 2003 - 7:12 am UTC
Very nice questions and answers. Cleared my most of the doubts regarding Hot backup.
During hot backup
Ramesh, November 26, 2003 - 11:50 am UTC
If the Tablespace in hot backup mode and the files are deleted while in backup mode,
Still we can create table, insert rows into that table..
Is that mean, that the data is not written to Datafile while in backup mode ?
Thanks
Ramesh
November 26, 2003 - 11:57 am UTC
no, you've got it wrong. files are continously written to, in hot backup mode or not.
first, under unix, if a process has a file opened and you erase it elsewhere -- that process still has a file!
second, the file need not be accessed right away. DBWR does the writes to the files in the background.
third, did you think to try the test NOT in backup mode (which would "prove" that files are apparently NEVER written to :)
Why fractured blocks only matter the first time?
Doug, February 27, 2004 - 7:44 pm UTC
Tom - the first time a block is updated - is the update suspended/blocked until we can log a complete block image so that the block is consistent? Why doesn't a fractured block matter the second or third time it is updated. In other words, let's say that File 2, block 1 is updated but the copy command hadn't gotten there yet. When it does get there, it is the 3rd or 4th time it's been updated and it is fractured but we're not logging a completely consistent block image to redo like we did say a minute ago. How come this doesn't matter? Thanks.
February 28, 2004 - 10:26 am UTC
No, it is just that instead of logging into the redo log buffer "just enough to redo that change", we log the block.
It doesn't matter on the second and third and nth times because of how recovery would proceed.
Lets say "block 5" in file "55" was backed up "fractured".
Further, we know the first time block 5 was modified AFTER the backup began we logged the modified image of block 5 in the redo log buffer (and hence into the online redo log files upon commit and hence into the archives upon log switch).
Now, say block 5 is updated 500 times -- we only log the minimum data to restore block 5 after that initial write.
Now, we fail and need to restore this file 55. We goto the backups and we lay down this old copy of file 55. We start processing the archives/online redo logs to catch this file up. We hit the first change for block 5 after the backup was taken and get the entire block image for block 5. So, even if block 5 was totally corrupt on disk -- fractured, binary zeros, whatever -- we have block 5 as it should be after that first update. We lay down that entire block and then apply the incremental updates from the arch/redo log files to catch it up.
Hot Backup
Mahesh Kumar, May 13, 2004 - 6:28 am UTC
Hi Tom
This is really fentastic. This clear all doubts and queries regarding Hot backup and Oracle internal behaviour of generating Extra Redo.
Keep giving drops from ocean.
Thanks
Instance Crash during a Hot Backup
Reader, May 13, 2004 - 8:31 am UTC
Dear Tom
Please clear this 1 doubt. The Scenario is as follows
1. Database has two online redo logs of 10MB each(sequence 32,33) 33 is the current online log.
2. Database running in Archivelog Mode
3. Issued "Alter tablespace users begin backup".Tablespace users has 1 datafile , users.dbf. Now at this moment a checkpoint takes place and datafile headers are updated with a SCN, No more SCN's will be recorded in the datafile header even though the datafile will be written to by both commited and uncommited transactions.
4. A big transaction comes along (taking place on some table in the users.dbf datafile) and fills up the online redo log. The transaction is not yet committed.
5. Log switch occurs and hence a checkpoint takes place , dbwr writes these uncommited entries to the datafile users.dbf.
6. The log file is archived by archiver process.(ie log number 33)
7. Now we switch to redo log 32 which will now be overwritten assuming it has already been archived.
8. Now we have sequence 34 as current online log. The transaction is still going along without commiting and fills up this log also.
9. Log switch occurs and hence a checkpoint takes place , dbwr writes these uncommited entries to the datafile users.dbf.
10. The log file is archived by archiver process.(ie log number 34)
11.If the system crashes now, then during the next startup our tablespace will still be in backup mode. We will first have to issue "Alter Tablespace users end backup" and the database would rollback the previously uncommited transactions written to the datafile from the online redo logs.
But suppose if we skip point 11 and instead of a system/database crash we continue further as follows
11. Now we have sequence 35 as current online log. The transaction is still going along without commiting and fills up this log also.
12. So the redo log 33 is now being overwritten.
13. The system crashes now.
How will the recovery proceed next time the database is started.?? Will the Archived redo logs become a part of recovery since we have the datafile with uncommited transaction. If yes then will this be a media recovery
May 13, 2004 - 10:48 am UTC
you have the first step 11 wrong, the database would rollback the uncommitted transactions regardless.
At all points in time -- regardless of the backup mode of any tablespace -- we only need the contents of the current online redo logs to perform instance recovery. Before we reuse an online log, dbwr will have written the blocks protected by that log to their datafiles (including UNDO!) -- we no longer need that log for instance recovery -- no matter the backup state.
A Little Confused
Reader, May 14, 2004 - 1:57 am UTC
Dear Tom
-----------------------------
Before we reuse an online log, dbwr will have written the blocks
protected by that log to their datafiles (including UNDO!) -- we no longer need
that log for instance recovery -- no matter the backup state.
-------------------------------
So please correct me if I am wrong. From the Above Scenario, The recovery will take place as follows :
1. Even though we have the Redo Log Number 33 which contains the start of our long transaction overwritten and not available to us during the startup after the failure, the changes made by this transaction (ie the before images) will be there recorded in the Undo Datafile when the log switched happened and thus firing a checkpoint.
2. So these changes made by the transaction in the Undo Datafile will have some sort of associated entry with them that this particular changes are not commited.
3. Hence during the rollback phase of the Crash Recovery these changes will be undone.
Hope I am right??
May 14, 2004 - 10:11 am UTC
1) yes, the undo protected by the log file is safely on disk now
2) yes, as all uncommitted transactions do.
3) yes.
Some more doubts ...
Reader, May 15, 2004 - 9:56 am UTC
1.During Log switch DBWR writes to the Datafile(DF) and ckpt updates the DF headers and control file.
Until this completed a new logfile will not be allocated.
Yes or No ?
2. A large transaction is going on.
At checkpoint all dirty blocks are written to the Datafiles.The header of all the uncommitted blocks will still be pointing to a RBS .
a) at this point is the block still in the SGA or flushed out to make room for new ones.
b) a new session starts and needs to read some rows from this block .Will it read from SGA or DF ?
c)I assume that it will go to the RBS and get a image before this transaction.
3.Assuming this transaction modified more than 10% of the SGA .So only the transaction table in the RBS will be updated to indicate that the transaction is complete.
A lot of things happened and this RBS header has been overwritten.
A new session wants to read some of the blocks which belonged to this transaction .
It sees that it is an uncommited transaction.
Goes to RBS .
can not find it .
Generates ORA-1555 .
Now,
When will the block header be updated with a commit SCN and which process will do that and what will be the SCN .(i read this from your book but you have not mentioned about this part.)
Thanks ,
May 15, 2004 - 12:48 pm UTC
1) at a log switch, we fire a checkpoint. until that checkpoint completes, we will not reuse the logfile we switched out of.
it does not prevent us from going forward, it prevents us from reusing. If the act of going forward involves "reuse of a log file" and the checkpoint that log file triggered is not complete, we cannot go forward.
But normally, a log switch is a triggering event and waits for no one.
2) the dirty blocks are flushed to disk. it is impossible to say whether the new session will read from the datafiles or the buffer cache. They will absolutely read from the buffer cache (no doubt), the question is whether they have to perform physical IO to the rollback segments or the data segment in order to load it into the buffer cache. that depends on whether the data is there OR NOT.
it will read the data block to find out "opps, too new" and then read rbs data to roll it back in general.
3) your timeline here is very unclear. it seems that the "new session wanting to read" would be able to read everything and would not need anything from the rbs.
but if you are talking about block cleanout -- i did menation (and proved) that the reader of the data will generate the cleaned out block (as the select generates REDO at that point)
one more clarification
Reader, May 17, 2004 - 12:20 am UTC
This has reference to my earlier question and your answer above regrading delayed block cleanout.
eg at 9 AM a large transaction happens and completes
at 11.00 a query comes to access some of these rows .This fails because it is unable to find info in the RBS .
at 11.30 another query comes --This will suceed .
Why ?
From where will the earlier session (11.00AM) get the commit SCN to update the headers of the blocks as there is no trace of the transaction table containing the commit SCN .
I hope I am able to express my doubt clearly .
Thanks,
May 17, 2004 - 7:30 am UTC
at 11:00am -- why would the query even be looking in the RBS, the data in the data segment is what it wants.
if you have my book, Expert One on One Oracle -- I cover this block cleanout stuff in detail with examples. It tells you when you can get 1555's due to it, and why. takes a couple of pages and examples to get the material across.
Continued ...
Reader, May 17, 2004 - 2:31 pm UTC
This has reference to the above post.
I read your Books(2) almost every day .
The confusion is --
Delayed Block Cleanout :
Since the transaction was big only the RBS --Transaction Table was marked as complete. All the related block headers (ITL) were left still pointing the the RBS.
It has an SCN which is not current ie much before the big transaction began.Ideally it should be the commit SCN from the big transaction.
My question is :
The query at 11.00 will see the ITL and go to the RBS and since many commits have happened in between it will fail with ORA-1555.
How and when these blocks get the commit SCN and when will it stop pointing to the RBS .
Thanks ,
May 17, 2004 - 4:11 pm UTC
those blocks get the commit scn when someone else reads them and cleans them out. that is the very act of the block cleanout (and why a select can generate redo, redo for the cleanout)
last doubt !
Reader, May 17, 2004 - 8:17 pm UTC
Now that you have clarified most of my doubts .
The last doubt is ---
from where does this session get this commit SCN because the actual commit SCN is gone ?
Also,If this select puts the commit SCN then why can it use the information in the Blocks instead of generating these errors ?
Thanks Again,
"Backup and Recovery -- why extra redo is generated.", version Version 7.3.2.1/8.0.4
Zeeshan Zafar, May 18, 2004 - 11:55 am UTC
useful working explanation.
Datafile and block SCN's -- confusion.
A reader, July 19, 2004 - 2:11 am UTC
Hi Tom,
From manuals I read -
" Oracle assigns every committed transaction a system change
number (SCN). Each commit will advance the SCN forward. Each time Oracle performs a checkpoint ,all changed data is written to the disk. And each time there is a checkpoint, the thread checkpoint in the control file is updated by Oracle. During this thread checkpoint, Oracle makes all the
datafiles and the control files consistent to the same SCN.
A consistant database means that the SCN's stored in all the data file headers are identical and are also the same as the data file header information held in the control files."
The important thing is that the same SCN must appear in all the data files and control files. Fine... but
I really can't understand how all the datafile headers will have the same SCN's !!
1) Does that mean that the datafile headers have a different SCN stored than all the block headers SCN's??? Because every commit will increment the block headers if a row has changed and committed there. So if a block has had the rows committed( after modification) 10 times then the SCN of the block will be different from another block which has had one row changed and committed in it.
2) Do control files have the latest SCN of not only the blocks but also the datafies maintained and how are they maintained?
Appreciate if you could show with some examples.
Thanks lots,
Regards,
RD.
July 19, 2004 - 7:41 am UTC
the scn in the datafile header simply says "all of the blocks are good upto this scn, you won't need any redo that predates this scn to recover this file"
the blocks may all have different scn's, but the scn in the datafile header tells us how far back in redo we would have to go to recover that file.
say a block in file 1 was modified at scn=100
at scn=200 a checkpoint happens.
that block is flushed from the cache to disk and the datafile is stamped with "200".
file 1 needs redo that is generated AFTER scn=200 in the event of recovery happening (say after a system crash). the fact that the block was modified at scn=100 isn't meaningful here.
Any indication that a tablespace in backup mode?
Peter Tran, February 07, 2005 - 5:47 pm UTC
Hi Tom,
Great explanations above. I would like to know if there's any Catalog views or Data dictionary views that will let me know if a tablespace in backup mode?
E.g.
alter tablespace USERS begin backup;
How do I find other than looking at the alert log that that USERS tablespace is currently in backup mode?
If the database instance crashes or the database is shutdown while a tablespace is in backup mode, is it still in backup mode when the database is started up?
Thanks,
-Peter
February 08, 2005 - 1:19 am UTC
sys@ORA9IR2> select * from v$backup
2 /
FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 0
7 NOT ACTIVE 0
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
14 NOT ACTIVE 0
12 rows selected.
sys@ORA9IR2> alter tablespace users begin backup;
Tablespace altered.
sys@ORA9IR2> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 0
7 ACTIVE 8.2049E+12 08-FEB-05
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
14 NOT ACTIVE 0
12 rows selected.
sys@ORA9IR2> alter tablespace users end backup;
Tablespace altered.
sys@ORA9IR2> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 0
7 NOT ACTIVE 8.2049E+12 08-FEB-05
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
14 NOT ACTIVE 0
12 rows selected.
So, v$backup tells you file by file (since files can be in backup mode or not as well...)
sys@ORA9IR2> alter tablespace users begin backup;
Tablespace altered.
sys@ORA9IR2> shutdown abort;
ORACLE instance shut down.
as good as a 'crash'
sys@ORA9IR2> startup
ORACLE instance started.
Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 201326592 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/home/ora9ir2/oradata/ora9ir2/users01.dbf'
sys@ORA9IR2> alter database recover;
Database altered.
sys@ORA9IR2> alter database open;
Database altered.
sys@ORA9IR2> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 0
7 NOT ACTIVE 8.2049E+12 08-FEB-05
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
14 NOT ACTIVE 0
12 rows selected.
Awesome!!!
Peter Tran, February 08, 2005 - 10:59 am UTC
As always thank you very much for a clear example.
-Peter
Why do you recover?
reader, February 08, 2005 - 7:59 pm UTC
Tom, in your demo above, I could do "alter database end backup" instead of "alter database recover". right?
February 09, 2005 - 2:43 am UTC
try it, see what you see :)
but the correct response to:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/home/ora9ir2/oradata/ora9ir2/users01.dbf'
is generally "recover"
When does a fragmented block rebuilds if Server is not restarted
Deepak Haldiya, March 07, 2005 - 5:45 pm UTC
Hi Tom,
I have the following query on Hot BackUp:
1. Assume that we have a fragmented block in the datafile because of a HotBackUp. After the alter tablespace end backup, the database is still running. Now after some time (at this point, we no longer have this block in buffer cache), a user queries the data which belongs to this fragmented block. What happens? How does Oracle provides the user correct data?
2. I read somewhere that the Hot Back generates less redo if performed through RMAN and generates more when performed manually.
The only explanation I can think is RMAN does not issue Begin Backup Statement and check the Block Header and Block Footer while copying the datafile in its special format. If a mismatch is found, its re-read from the datafile. Is this correct?
Thanks
March 08, 2005 - 7:45 am UTC
1) the fragmented block ONLY exists in the backed up file, it never exists on disk like that. the fragmented block happens when your backup tool is reading a block at exactly the same moment as dbwr is writing that block. dbwr will finish writing the block -- and the block will not be fragmented on disk - but the backed up version could be fragmented and that is what the additional redo generated during a backup is used to correct.
2) rman, which understands it is backing up oracle database blocks and not just a file like your OS copy tools, does not suffer from fractured blocks since if it sees one -- it simply re-reads the block from disk. So the backup file will never have a fractured block in it.
RE: When does a fragmented block rebuilds if Server is not restarted
Deepak Haldiya, March 08, 2005 - 7:10 am UTC
Hi Tom,
After thinking a little more on what I asked you, I figured that the Database Blocks is the current Datafiles will always be consistent. Its only the backup datafiles which may have fragmented Blocks while performing Hot Backups.
You may disregard my previous question.
Thanks
Deepak
Crystal!!
Arul, March 08, 2005 - 11:27 pm UTC
Excellent Tom!!
Does Online Backup have any impact on Data Buffer?
Manoj, May 09, 2005 - 8:41 am UTC
Dear Tom,
The forum over Online Backup is indeed very useful.
I like to request you to please tell me, is Online backup have any impact on Data buffer cache.
If Databuffer cache is full while hot backup is in progress then what should be done?
Many thanks in advance!
Best regards,
Manoj
May 09, 2005 - 9:15 am UTC
nothing, the cache is used by SQL. rman reads data from disk. cp reads from disk. whatever you use will read from disk.
Some More Doubts.....
Kishor Bhalwankar, September 15, 2005 - 3:22 am UTC
Hi tom,
Can you explain me follwoing ..
After Begin Backup, I updated one row and commited.
(This is the FIRST dirty block after begin backup)
This will be copied in redo..
After some time if I update the same block (Same or other row) and commited.
It will record only the change vector in redo...
Q : How oracle knows that this block is copied previously in redo. (Where oracle stores this information ) ?
OR
Will oracle copy the full block in redo again ?
Thanks in advance.
Correct me If I am wrong.
September 15, 2005 - 7:54 am UTC
There is a ton of bookeeping information on every block header - the same sort of information we use for read consistency (to determine if a block is "old" enough for you to read .
the extra redo is generated for the first change to that block after a begin backup. after that, it is logged as it was outside of a begin backup.
little confusion
A Reader, October 12, 2005 - 3:38 am UTC
Let's have an example of Before starting with Hot backup( begin backup):
SELECT T.NAME, H.FILE#, H.CHECKPOINT_CHANGE#
FROM V$TABLESPACE T, V$DATAFILE_HEADER H
WHERE T.TS#=H.TS#;
NAME FILE# CHECKPOINT_CHANGE#
------------------------------ ---------- ------------------
SYSTEM 1 121923
UNDOTBS 2 121923
INDX 3 121923
TOOLS 4 121923
USERS 5 121923
So, all tablespaces (and all data files) start off synchronised with the same checkpoint change number.
Now we will put USERS tablespace in backup mode.
alter tablespace users begin backup;
Again fire the same query:
select t.name, h.file#, h.checkpoint_change#
from v$tablespace t, v$datafile_header h
where t.ts#=h.ts#;
NAME FILE# CHECKPOINT_CHANGE#
------------------------------ ---------- ------------------
SYSTEM 1 121923
UNDOTBS 2 121923
INDX 3 121923
TOOLS 4 121923
USERS 5 121925
We can notice here that USERS tablespace is slightly ahead of rest of the tablespaces.
Now suppose it took 1hr to complete the backup of USERS tablespace datafile. And in between so many transaction has happened and checkpoint also occurred.
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
Now we will fire the same query again to check the cheakpoint# :
select t.name, h.file#, h.checkpoint_change#
from v$tablespace t, v$datafile_header h
where t.ts#=h.ts#;
NAME FILE# CHECKPOINT_CHANGE#
------------------------------ ---------- ------------------
SYSTEM 1 121939
UNDOTBS 2 121939
INDX 3 121939
TOOLS 4 121939
USERS 5 121925
Here we can see that all tablespaces are now ahead of USERS tablespace because during hot backup , header was locked and did not get updated of USERS tablespace.
Now my confusion starts here:
When Oracle will bring the USERS datafile equal to the rest of the datafiles (in terms of CHECKPOINT_CHANGE#)? Just after putting the tablespace out of backup mode (end backup) OR at the time of recovery (when db would crash and we would restore it from backup and apply arch files). What mechanism Oracle will follow?
Please explain it, you can give exampls also.
Thanks in advance for your support to Oracle community.
October 12, 2005 - 7:27 am UTC
after you take the tablespace out of backup mode, it is free to be "caught up" to the rest of the database - it would not wait for recovery to take place because recovery might *never* take place.
some clarification
amit poddar, October 12, 2005 - 10:23 am UTC
Hi,
Is changes to datafile header (ie. during checkpoint, begin backup, end backup etc) protected by redo ?
October 12, 2005 - 2:05 pm UTC
there might be some - but in general, the file header is used to figure out what redo might be needed for recovery - not recovered itself from redo.
one more question
amit poddar, October 12, 2005 - 1:23 pm UTC
</code>
http://www.freelists.org/archives/oracle-l/10-2005/msg00375.html <code>
Please take a look at the above link.
It says that increasing the SGA makes "begin backup" take longer since it takes longer to do a tablespace checkpoint.
Question is does begin backup result in a tablespace checkpoint before freezing the datafile header?
If yes then, why would a checkpoint needed
If no then why would increasing the size of SGA result in longer time for doing "begin backup" for a tablespace ?
October 12, 2005 - 2:27 pm UTC
it does a checkpoint, yes.
gets the file to a known point, good for figuring out what redo you need to have included with the backup in order to be able to restore it and make it consistent.
Little confusion
A Reader, October 12, 2005 - 11:25 pm UTC
Hi Tom,
I got your point that after putting the tablespace out of backup mode (i.e. end backup), it will catch up with rest of database datafiles.
NOW, Suppose we are taking backup of USERS tablespace.
alter tablespace USERS begin backup;
During hot backup so many transaction are going on and due to split blocks , redo is getting generated in excess and redolog file gets full and log switch happen. Then in this case the first log file get archived ( archive log file) and control gets to second log file. Suppose backup take the longer and in between 2-3 cycle happens for log switch.
I mean to say that redo information for transaction goes to the archive log file and redo logfile overwritten.
Now what will happen when we will take USERS tablespace out of backup mode.
Alter tablespace USERS end backup;
As you said that datafile automatically catch up with database, so Will it recover the datafile (split blocks) from archive log file?
Pls. explain how Oracle will handle it.
Thanks.
October 13, 2005 - 10:29 am UTC
the same thing that would happen if NO redo were generated.
The datafile is constantly written to during a hot backup, checkpoints happen as normal, blocks are read and written all of the time - only the data file header is treated special.
At the end of the backup, we fix up the data file header and continue business as normal.
The split (fractured) blocks would be in the BACKUP - not in the datafile. They happen because the backup program (cp, copy, whatever) reads using a read size of "X" and we use a block size of "Y" and "X" <> "Y". Fractured blocks would be in the backup set - not in the datafiles.
Nothing at all...
Howard J. Rogers, October 12, 2005 - 11:38 pm UTC
Nothing will happen when you say 'end backup'. What's there to have happen? The split blocks you're talking about are in the *copy* of the data file you just made, not in the original file that the database is using. The database therefore is suffering no problem, and if there's no problem, there's no need to do anything.
If the original data file was now to be lost or damaged in some way, then you would restore the backup copy, and *now* you would have a split blocks problem: the file that you restored contains split blocks and is therefore internally inconsistent and unusable.
But the *archive logs* contain whole, pristine images of those blocks which, in the datafile copy, have split. That is what putting the datafile into hot backup mode in the first place made happen. So, as we perform database recovery, if we encounter a whole-image of a block in the redo stream, we over-write the equivalent block image in the restored datafile. That then gives us a 'clean slate' (i.e., no splits) for each individual block encountered, and a consistent block image which can therefore be rolled forward to catch up with the time of the rest of the database.
So when you say 'end backup', nothing (much) happens. When you perform a recovery: yes, the whole-block images in the archives are used to allow recovery to take place successfully.
Confusion cleared
A Reader, October 14, 2005 - 3:07 am UTC
Tom, Thanks for clearing me about split blocks.
Horward, thanks to you also for very good explanation on this.
Proof!
Richard, November 15, 2005 - 5:39 am UTC
Not that I doubt your word - but where in the Oracle documentation is the proof of your statement,
"the first time a block is changed in a datafile that is in hot backup mode, the ENTIRE BLOCK is written to the redo log files, not just the changed bytes"?
November 15, 2005 - 8:59 am UTC
Not all details as minute as this are necessarily documented fully - however...
You could of course set up a test to confirm this (dumping redo streams - not much fun but it would be convincing)
(talks about fractured blocks and that extra redo is generated)
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14220/backrec.htm#sthref2329
(i believe this entry to be erroneous in part as it says "every time", not the first time - it is only the first time)
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1012.htm#sthref360
Here is compelling evidence to that effect:
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p
2 authid current_user
3 as
4 l_value number;
5 begin
6 select b.value into l_value
7 from v$statname a, v$mystat b
8 where a.statistic# = b.statistic#
9 and a.name = 'redo size';
10
11 for i in 1 .. 100
12 loop
13 update t set x = x+1;
14 commit;
15 end loop;
16
17 select b.value-l_value into l_value
18 from v$statname a, v$mystat b
19 where a.statistic# = b.statistic#
20 and a.name = 'redo size';
21
22 dbms_output.put_line( 'redo size = ' || l_value );
23 end;
24 /
Procedure created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p
redo size = 51544
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec p
redo size = 51568
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec p
redo size = 51568
PL/SQL procedure successfully completed.
<b>so, about 50/51k of redo generated each time, put tablespace into backup mode:</b>
ops$tkyte@ORA9IR2> alter tablespace users begin backup;
Tablespace altered.
ops$tkyte@ORA9IR2> exec p
redo size = 59784
PL/SQL procedure successfully completed.
<b>increase of 8k the first time (my block size is 8k). But note that it is NOT 100*8k, just 1*8k - first time...
Just to make sure it is not a funky thing with plsql and commit optimizations, we'll do it again and again:</b>
ops$tkyte@ORA9IR2> exec p
redo size = 51568
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec p
redo size = 51600
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> alter tablespace users end backup;
Tablespace altered.
<b>and we have the same amount of redo as before - even though we are in backup mode</b>
Marvellous!!!
Richard, November 15, 2005 - 9:18 am UTC
A reader, December 01, 2005 - 7:24 am UTC
Hi Tom,
Excellent site ...the least I can say. Please excuse me for asking soem very basic question. In teh article (</code>
http://www.dizwell.com/oracle/articles/hot_backups.html <code>, I can't understand the following line (third para on the first page) :
"For example, suppose my data files checkpoint change number reads 1000, but there are actually blocks of data in that file which are actually from time 900."
Does this mean that eac block also contains teh SCN?
Thanks a lot fo ryour useful feedback.
Santosh
December 01, 2005 - 12:46 pm UTC
conceptually, not literally (unless you have row dependencies...). information to figure out that information is there.
You can always ask HJR what he meant as well:
</code>
http://dizwell.com/forum <code>
Hot backup/Rman Backup
A reader, February 24, 2006 - 7:30 am UTC
Tom,
Execellent site!
Does all above means that the alter system archive log all(current) following with backup of archive logs should be always the very last command in the scripts for hot backup?
If I start with backing up archivelog files and then do hot backup by swithing TS to backup state and coping OS file with OS copy command, but not backup logs which oracle archived during backup - does this means that I can't use my very last backup (not open with reset log). Is such backup really unusable?
When in the script should I backup controlfiles?
What is different if I use rman. Is the order of backup database/backup achivelog/current control file is important?
For me it is important to understand whether is such backup (database without backing up the archive logs done during backup) can be used to restore the database to the time of backup?
Thanks in advance
Olaf
February 24, 2006 - 8:55 am UTC
In order to have a backup that is restorable to the point in time of the backup itself, you need to have everything you need to make it consistent.
So yes, it would end with a "switch logfile" (to get off of the current online redo log), an "archive them all" and then a "backup these archives"
You now have
o the datafiles
o all archives needed to make these datafiles consistent as of THIS POINT IN TIME.
Every other archive you end up throwing into that backup set will just allow you to take that backup further in time.
It is not that your backup is unusable, it is that "unless you get access to the archives generated during your backup at the very least, your backup won't be restorable".
You want to understand? Practice makes perfect. Set up a test environment and play, play, play, play.
A reader, February 24, 2006 - 9:17 am UTC
that means that if I did not backed up achivelogs done during hot backup and did not saved it together with datafile backup (or copied them away from the server), and it was the only one backup copy - there is no way to restore the database if my prod db blew up?
February 24, 2006 - 10:46 am UTC
that is likely the case - yes.
Confusion
A Reader, May 04, 2006 - 3:40 pm UTC
Tom,
Please clear the point why - "We should not take the backup of Online redo logs in Hot backup."
Please give the example to clarify it. Why and how it will corrupt the database if we restore the online redo logs from backup.
Thanks.
May 04, 2006 - 5:13 pm UTC
tell me why you would?
it just does not make sense - to restore a hot backup you
a) restore the hot backed up file
b) apply ARCHIVES TO IT
and if they exist - let the system roll forward through the online redo logs it finds on disk
If you restore from a backup - the online redo logs from 2 days ago - the ONLY thing you could achieve is ----- losing the ability to recover the data that was in your current online redo logs.
confusion
A Reader, May 05, 2006 - 10:51 am UTC
Hi Tom,
I think i couldn't explain my problem in previous post. Now pls let me explain it in more detail.
Suppose We take a hotbackup on 10th Mar06 and that time we copy the online redolog files also.
Now suppose we have to restore the full database on some other system(m/c) on 15thMar06.
Q1. Can we copy the full database (all datafiles,controlfiles and redolog files of 10thMar06) and apply the archive log file from 10th06 to 15thMar06.
Q2. And if we can not copy the online redolog files of 10Mar06, pls explain why with example.
Thanks.
May 05, 2006 - 2:48 pm UTC
you cannot copy the online redo logs - they are full of garbage - we use them in a circular fashion - they are a big round buffer.
some of what you copy is in the archives.
YOU DO NOT copy online redo logs, they are not USEFUL.
you take a hot backup, you switch log files, you archivelog all, you backup archives.
that is what you do.
Very Useful info...
KVR Kishore, October 23, 2006 - 5:57 am UTC
Excellent
Simply brilliant
Vijay Bhaskar, June 16, 2007 - 6:33 pm UTC
Dear Tom,
Having been in this field for last 2 years or so,I thought of having fair bit of understanding
on this topic (tablespace hot backup).But,now it's became apparent, how much I got it wrong ?
Thanks Tom for a fantastic explanation of how Oracle internally handles the tablespace hot backup operations.
Simply brilliant & keep on continuing this work.Only we can understand how much useful it is proving out to be
for the entire Oracle DBA/developer fraternity.
Kindest regards,
Vijay
a reader
raman, September 21, 2007 - 3:26 pm UTC
i have confusion when we start user online backup the block may be in fractured mode when copying block from datafile,but why the block is not in fractured mode when copying block from archive log file.The reason is that before changes in a block LGWR writes whole block to the log file and after that again any changes in the same block will record only those chnages.
ok it is clear to me.
confusion now come here
suppose i start online user backup and start copying datafile and archivelog file from os ,
1)i modify a block 'A', LGWR first time writes that whole block 'A' to log file before changes.
2)now copying the archivelog file is in progress for that block 'A' it copy the block 'A' first half due to os capability .
3)during that i again modify the block 'A' its second time modifiation and only changes are applied to log file.
4)after that OS comes to copy the remaining part of block 'A' now this block 'A' second part changed.
now how it will be consistent?why it is not fractured like it is fractured with the case of copying datafile but now with archive log file why not it is frctured?
i have confusion when we start online backup the block may be in fractured mode
when copying block from datafile,but why the block is not in fractured mode when
copying block from archive log file.The reason is that before changes in a block
LGWR writes whole block to the log file and after that again any changes in the
same block will record only those chnages.
ok it is clear to me.
confusion now come here
suppose i start online backup and start copying datafile and archivelog file from os ,
1)i modify a block 'A', LGWR first time writes that whole block 'A' to log file before changes.
2)now copying the file is in progress for that block 'A' it copy the block 'A'
first half due to os capability .
3)during that i again modify the block 'A' its second time modifiation and only changes are applied to log file.
4)OS comes to copy the remaining part of block 'A' now this block 'A' second part changed.
I am confused about fractured block ,fractured block which is with datafile block but not with arhivelog data blcok both are copied by os coommand.
raman
September 26, 2007 - 1:21 pm UTC
since you never backup online redo (ever)
and since you never copy an archive until it is an archive and it is not an archive until we wrote the last bit...
you never ever see a fractured block in archive - by definition, you are not reading it while we are writing it. datafiles, during a hot backup, you read while we write.
you are not reading any archives we have not finished. you do not read online redo, hence the situation cannot exist.
not only the first time...
Franck Pachot, October 02, 2007 - 3:24 am UTC
Hi Tom,
It seems that the extra logging (i.e the full block image) occurs not only on the first change since the tablespace is in hot backup mode, but also when the block has been aged out from the buffer cache and has been read again.
Here I used your testcase to show that the extra logging occurs again the first time after a buffer pool flush:
SQL> exec p
redo size = 54412
PL/SQL procedure successfully completed.
SQL> exec p
redo size = 53916
PL/SQL procedure successfully completed.
SQL> exec p
redo size = 54416
PL/SQL procedure successfully completed.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> exec p
redo size = 64484
PL/SQL procedure successfully completed.
SQL> exec p
redo size = 56332
PL/SQL procedure successfully completed.
SQL> exec p
redo size = 56288
PL/SQL procedure successfully completed.
SQL> exec p
redo size = 56256
PL/SQL procedure successfully completed.
SQL> alter system flush buffer_cache;
system altered.
SQL> exec p
redo size = 65508
PL/SQL procedure successfully completed.
SQL> exec p
redo size = 56448
PL/SQL procedure successfully completed.
SQL> exec p
redo size = 56300
PL/SQL procedure successfully completed.
Regards,
Franck.
At the "End Backup" how the SCN at the header of datafile being matched
Vinay Pandey, January 02, 2008 - 10:22 am UTC
With Reference to your
Followup October 13, 2005 - 10am US/Eastern:
Your remarks
<<<< At the end of the backup, we fix up the data file header and continue business as normal. >>>>
I understands from your explanations that at the "end backup" the header block of the datafile (say 5) is released and the current SCN (which is located on other datafiles header) is updated in that datafile no. 5.
I need little indeapth clarification: (elaboration on "end backup" phase)
1. Please remark if my assumptions are right about the current SCN, it is the highest SCN on other consistent datafiles before which all the dirty bufferes are written to datafiles from buffer cache?
2. Who prevents the SCN updation in the datafile during begin backup mode?
3. Who updates (it should be CKPT) the SCN in the datafile at "end backup" and from where it finds the SCN to be updated. (either Controlfile or other consistent datafiles) ?
I appriciate the whole disussion regarding this question as it made me confident about my assumption about what happens during hot backup (begin backup mode).
January 02, 2008 - 2:36 pm UTC
1) depends on your context, what "current scn" do you mean. In the context of a file - the scn recorded in there will be the scn at which recovery would need to be started from.
2) the database itself, it works together as a whole.
3) ckpt in general does this stuff, but we really do not need to know truthfully, you just need to know:
a) when you alter tablespace begin backup - the scn for that file for recovery is sort of frozen
b) but the datafile is continuously written to during this period
c) when we end the backup, the state of the datafile would go back to the way it was before the begin backup
and in the year 2007, we use rman and skip all of this stuff - it is legacy information now.
Hot Backup
Srujana, October 20, 2008 - 9:10 am UTC
excellent explanation by tom.
Thanx tom
Hot backup
A reader, April 09, 2009 - 9:57 am UTC
greetings thomas,
and thanks like always.
i just want to make sure i understand what happened on the hot backup process correctly.
------------
The datafile is constantly written to during a hot backup, checkpoints happen as normal, blocks are
read and written all of the time - only the data file header is treated special.
At the end of the backup, we fix up the data file header and continue business as normal.
and oracle writes the ENTIRE BLOCK to the redo log files
-----------------------------
so we do not have a fractured blocks.
then this block will be written to the datafile even if the tablespace in "begin backup" mode because every thing is working normal except for the datafile header.???????
April 13, 2009 - 3:39 pm UTC
yes, I don't know how to phrase it differently - but this:
The datafile is constantly written to during a hot backup, checkpoints happen
as normal, blocks are
read and written all of the time
*says it all*.
Good explanation Tom
suresh, April 13, 2009 - 9:16 am UTC
Great... Had a doubt but while RMAN hot backup why not a excessive redo generated. Can u please explain the same with your typical examples.
-Thanks
Suresh
April 13, 2009 - 5:34 pm UTC
"U" isn't available, "U" is dead as far as I know. Look it up, it is true.
http://en.wikipedia.org/wiki/U_of_Goryeo RMAN knows all about Oracle. RMAN knows that blocks can be fractured (the head does not match the tail, ctl-f on this page and search for fractured to read more about that). RMAN knows how to detect a fractured block. When RMAN does detect a fractured block - it retries the IO to get a good copy of it (this is what the OS utilities will not do, this is why the OS utilities MUST have us in backup mode first! They do not understand Oracle, they do not get what an Oracle block is).
So, RMAN avoids the fractured block - whereas an OS copy utility cannot.
Good explanation TOM
A reader, April 21, 2009 - 6:56 am UTC
Hi,
Thanks
-Regards
Suresh
Scofield, June 14, 2009 - 3:17 am UTC
Tom
Thanks a lot for this beneficial thread. I just want to ask a small thing regarding fractured blocks.How does the oracle know the time of the block? You mentioned previously "we have a block that is half at time T0 and half at time T1...."
June 15, 2009 - 12:06 pm UTC
the head of the block has information that has to match the tail of the block - we put data on the top and the bottom ('time' related data)
if they do not match, then the block is fractured.
reader
reader, August 07, 2009 - 4:21 pm UTC
HI Tom
As I know, while taking RMAN backup, if we change (udpapte, insert,delete) something in the tables, all information are written to redo logs, not to datafiles. Is it correct?
August 07, 2009 - 4:57 pm UTC
no, datafiles are constantly written to during a hot backup.
that is why a hot backup is known as a fuzzy or inconsistent backup, when we restore we KNOW we'll have to apply some redo to make everything consistent.
What about documentation?
Hopkins, August 08, 2009 - 2:28 am UTC
Hi Tom
In 10gR2 documentation, in "Backup and Recovery basics".pdf page 20 its written that:
"In this example a full backup of a database (copies of its datafiles and control file) is taken at SCN 100. Redo logs generated during the operation of the database capture all changes that occur between SCN 100 and SCN 500. Along the way, some logs fill and are archived. At SCN 500, the datafiles of the database are lost due to a media failure. The database is then returned to its transaction-consistent state at SCN 500, by restoring the datafiles from the backup taken at SCN 100, then applying the transactions captured in the archived and online redo logs and undoing the uncomitted transaction "
It seems that datafiles are in SCN 100 state, but all information are in redo log files. Is it correct? How to understand this process?
Thank you
August 11, 2009 - 12:52 pm UTC
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/intro003.htm#sthref40 is the link to the text.
there would be two cases to consider, and the text doesn't say which is the "case"
case 1: database was shutdown normally and the last recorded transaction was scn 100. then the data files are consistent, it was a cold backup.
case 2: the database was up and running. As of the point in time the backup started, the last recorded checkpoint place-marker was scn 100 (meaning in the datafiles, there will be blocks from before and after scn 100 - the files are inconsistent - but we KNOW for a fact that everything from scn 100 and BEFORE is recorded in the datafiles, things that happened AFTER scn 100 - we do not know if they are in the datafiles or not - nor do we really care).
So, we back the files up knowing that if we need to restore them, we need ALL redo generated after scn=100 happened. We might not need to apply all of it (some of the blocks might be checkpointed after scn 100 and backed up after scn 100) but we know that there is a change there is a block that was committed with scn=101 that was NOT backed up in the datafile since it wasn't in the datafile (it was in redo) when we copied that file.
a question
Hopkins, August 09, 2009 - 2:33 am UTC
Hi Tom
1. If all information are written to redo log files, why Oracle writes data to datafiles during RMAN???
2. If I make change to a table "during RMAN" and add some lines. After RMAN finishes, I see those lines in my table. Then I drop my datafiles and restore the files from RMAN backup. NExt, by recovering until cancel I opened the database with NORESETLOGS and I DON'T see those lines in my datafiles because they were NOT in datafiles, they were in redo log files and I DIDN'T applied it
Please explain this issue more briefly
Thank you very much!!! You, your books and your answers almost changed my life
August 11, 2009 - 1:30 pm UTC
1) not sure what you mean?
2) ditto for this. If you do a backup and restore to a point in time BEFORE you committed your changes (which is basically what you said you did - "i didn't apply it") - why do you think your changes would be there???!?!?!
If you
a) start a (hot) backup
b) make some changes to a table and commit;
c) finish backup (but datafile backups don't contain the changes in (b))
d) restore backup taken at point (a) and APPLY ALL REDO - all archives generated since (a) and all online redo log files
then your changes will be there, anything less than that - you might not see them, you have to recover PAST the commit point.
Alexander, October 28, 2009 - 10:56 am UTC
Tom, how do we know when we are restoring a database how much space to have in our archive log directory for all the logs it's going to pull back?
October 28, 2009 - 11:21 am UTC
rman doesn't need to restore all of them simultaneously, you can
<quote src=Oracle® Database Backup and Recovery Advanced User's Guide>
How RMAN Searches for Archived Redo Logs During Recovery
If RMAN cannot find an incremental backup, then it looks in the repository for the names of archived redo logs to use for recovery. The database records an archived log in the control file whenever one of the following occurs:
*
The archiver process archives a redo log
*
RMAN restores an archived log
*
The RMAN BACKUP AS COPY command copies a log
*
The RMAN CATALOG command catalogs a user-managed backup of an archived log
If you use a recovery catalog, then RMAN propagates archived log data into the recovery catalog during resynchronization, classifying archived logs as image copies.
You can view the log information using the LIST ARCHIVELOG command or the V$ARCHIVED_LOG control file view.
During recovery, RMAN looks for the needed logs using the filenames specified in the V$ARCHIVED_LOG view. If the logs were created in multiple destinations or were generated by the BACKUP AS COPY, CATALOG, or RESTORE commands, then multiple, identical copies of each log sequence number exist on disk. All copies of a log sequence number listed as AVAILABLE are candidates for use in recovery, regardless of how or where they are stored.
Logs that have been deleted or uncataloged through RMAN are not considered available for recovery. For example, assume that the database archives log 100 to directories /dest1 and /dest2. The RMAN repository indicates that /dest1/log100.arc and /dest2/log100.arc exist. If you delete /dest1/log100.arc with the DELETE command, then the repository indicates that only /dest2/log100.arc is available for recovery.
If the RMAN repository indicates that no copies of a needed log sequence number exist on disk, then RMAN looks in backups and restores archived redo logs as needed to perform the media recovery. By default, RMAN restores the archived redo logs to the flash recovery area, if one of the archive log destinations is set to USE_DB_RECOVERY_FILE_DEST. Otherwise, it restores the logs to the first local archiving destination specified in the initialization parameter file. You can run the SET ARCHIVELOG DESTINATION command to specify a different restore location. If you specify the DELETE ARCHIVELOG option on RECOVER, then RMAN deletes the archived logs after restoring and applying them. If you also specify MAXSIZE integer on the RECOVER command, then RMAN restores archived logs until the disk space allowed by MAXSIZE is consumed, then applies redo from the logs and deletes the restored logs to free space, until there is room enough to restore another archived log. RMAN continues restoring, applying and deleting logs, within the MAXSIZE limit, until recovery is complete.
</quote>
block fracture and incomplete media recovery
Vijay, August 11, 2010 - 1:56 am UTC
Dear Tom,
Thanks for excellent explanation on block fracture, tablespace hot backup modes, rman and etc. After having gone through this thread, start to end, following questions came to my mind:
Question 1) Scenario (T4>T3>T2>T1):
Tablespace "users" has one datafile (say, file 10).
At time T1, tablespace "users" has been put in hot-backup mode (begin backup).
At time T2, DBWR flushed dirty block (block id 55,pertaining to file 10). And at the same time, that datafile 10 (coincidentally, block 55) is being copied across to backup disk using UNIX "cp" command. And this "fractured" copied data block in backup datafile (OS and database blocks sizes are different).
So, now, we have a backup of datafile 10 having a fractured block (55) in it, whereas original/primary datafile is consistent.
At T3, "users" tablespace has been taken out of backup mode (end backup).
At T4, datafile 10 got corrupted, and we had to perform media recovery.
Media recovery of datafile 10 started, and for some reasons (unavailability of all archive logs post T1/"begin backup" command), I could only perform "incomplete" recovery till T1. That is,
--> Just restored backup datafile 10 (taken at T1).
--> recvoered datafile until cancel (cancelled straightaway). No archives/online logs applied!
--> opened database using resetlogs.
Now, we have opened database, but block 55 (of "now" primary file 10) is "fractured".
Now my question is, Won't next read call for this block 55 fail (or at least reflect inconsistent data) because, of corrupted/inconsistent/fractured block 55?
Question 2)
If OS and database (DB) block sizes are same and if we are taking user-managed backups then, I believe, block fracture will not occur. Because, both OS (DBWR writes) and DB (reads) will now write/read entire block at one go and not in chunks.
Am I right?
Question 3)
You said that block fracture is not an issue, if we use RMAN for taking backups and you also explained, why it is so?
But, I am still struggling to understand, how?
Even if we use RMAN and assuming, OS/DB block sizes are different then shouldn't block fracture occur here as well?
After all, no mater what tool one uses to take backup (RMAN, cp and etc), underlying IO will awlays be controlled via OS.
Please correct me, if I am wrong.
And last but not the least, thanks for your invaluable help to entire global Oracle DBA/deveoper community.
I do not have words to explain how important it is proving out to all of us, since a decade!
Regards,
Vijay
August 18, 2010 - 11:49 pm UTC
... Media recovery of datafile 10 started, and for some reasons (unavailability of
all archive logs post T1/"begin backup" command), I could only perform
"incomplete" recovery till T1. ...
then you are hosed, you cannot fix that tablespace - I stopped reading right there. You cannot do media recovery. The best you can do is restore the entire database to the point in time of T1.
dought in dba
rajesh, August 23, 2010 - 11:06 am UTC
what are the different ways that you can invoke parallelism?
August 26, 2010 - 10:02 am UTC
in short, you can do it automatically via
parallel query
parallel modification (pdml)
parallel ddl
parallel recovery
etc...
you can do it manually via
programming it yourself.
suggest you read the data warehousing guide, it covers the automagic stuff in detail:
http://docs.oracle.com/docs/cd/E11882_01/server.112/e10810/toc.htm
scenerio for online redolog file correpted
rajesh, November 03, 2010 - 11:58 pm UTC
how to restore the active redo log file
November 04, 2010 - 3:13 am UTC
are you suggesting you have a backup of an online redo log - you don't, you cannot, it is currently being written to.
to avoid issues with online redo log file errors like that you would use multiplexing of redo logs - you would have more than one member per group on different devices.
Changing ways.
Sayeed Salahuddin, November 05, 2010 - 1:10 pm UTC
Hi Tom,
If it was better to have one table space in backup mode at a time then why does Oracle 10g provide a feature to do:
alter database begin backup;
Regards,
Sayeed Salahuddin.
November 06, 2010 - 7:23 pm UTC
because version 6 provided it.
because version 7 kept it up.
and so on..
For a small database - it would be a "lazy but perfectly OK" approach. For a database of any size - you probably would not want to do it.
we provide triggers, when others, autonomous transactions too - they are in general "a really bad idea"
Caveat emptor
Very Informative !!
Sayeed Salahuddin, November 23, 2010 - 6:41 am UTC
I did not realize this feature existed in the early versions of Oracle, thanks for the information Tom.
You are an intelligent Man, may Allah guide you to the straight path, the path of Moses, Jesus and Mohammed (p.b.u.t). Ameen.
Regards,
Sayeed Salahuddin.
Tablespace Recovery
A reader, March 17, 2011 - 1:41 am UTC
Hi Tom,
We are on 10.2.0.4 with 2 node RAC on Solaris 10 64 bit system.
I was performing tablespace backup-recovery testing for the database and found very strange database behaviour (well atleast for me!). Can you please explain what is going on here?
Here is the test case:
Information about the tablespace & datafile
--------------------------------------------
SQL> l
1* select tablespace_name,file_name from dba_data_files where tablespace_name='ISHI_TEST'
SQL> /
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------
ISHI_TEST /ora_data/NEWS2PP/dbs/ishi_test01.dbf
Created Table in the tablespace:
------------------------------------
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
VORAI T ISHI_TEST
SQL> select count(*) from vorai.t
2 /
COUNT(*)
----------
2
Now, I deleted datafile of the tablespace:
-> rm -i /ora_data/NEWS2PP/dbs/ishi_test01.dbf
rm: remove /ora_data/NEWS2PP/dbs/ishi_test01.dbf (yes/no)? yes
Aftre that, I connected as the owner of the table and tried selecting rows from the table, hoping that Oracle would throw an error as datafile for the tablespace where the table resides, has been deleted. But Oracle did not produce error:
SQL> connect vorai/vorai
Connected.
SQL> select count(*) from t
2 /
COUNT(*)
----------
2
So I thought, the blocks might be available in buffer cache thus Oracle was able to read the block from cache. Now I tried to insert data into the table. Again hoping that Oracle will give error but I was able to insert data into the table..
SQL> insert into t values (20);
1 row created.
SQL> insert into t values (200);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
A
----------
1
2
20
200
I checked datafile & tablespace status and was showing it was AVAILABLE & ONLINE!
SQL> select status from dba_data_files where tablespace_name='ISHI_TEST';
STATUS
---------
AVAILABLE
SQL> select status from dba_tablespaces where tablespace_name='ISHI_TEST';
STATUS
---------
ONLINE
So I decided to perform checkpoint...
SQL> alter system checkpoint
2 /
System altered.
After checkpoint, I tried to insert data into the table again and data was inserted and I was able to commit the change!
SQL> insert into t values (1);
1 row created.
SQL> commit;
Commit complete.
I also checked GV$DATAFILE_HEADER & GV$RECOVER_FILE but there are no errors reported by these views!
Thus, I decided to perform recovery of the tablespace and select data from the table.
RMAN> connect target /
connected to target database: NEWS2PP (DBID=1551998897)
RMAN> run
2> {
3> sql 'alter tablespace ishi_test offline';
4> RESTORE TABLESPACE ishi_test;
5> RECOVER TABLESPACE ishi_test;
6> SQL 'ALTER TABLESPACE ishi_test ONLINE';
7> }
using target database control file instead of recovery catalog
sql statement: alter tablespace ishi_test offline
Starting restore at 16-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=762 instance=NEWS2PP1 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to /ora_data/NEWS2PP/dbs/ishi_test01.dbf
channel ORA_DISK_1: reading from backup piece /ora_arch/NEWS2PP/rmanbackup/NEWS2PP_20110316_ISHI_tblspace.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_arch/NEWS2PP/rmanbackup/NEWS2PP_20110316_ISHI_tblspace.bkp tag=TAG20110316T152000
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-MAR-11
Starting recover at 16-MAR-11
using channel ORA_DISK_1
starting media recovery
…
…
archive log filename=/ora_arch/NEWS2PP/arch/arch_NEWS2PP_3560_1_739797937.arc thread=1 sequence=3560
archive log filename=/ora_arch/NEWS2PP/arch/arch_NEWS2PP_2631_2_739797937.arc thread=2 sequence=2631
archive log filename=/ora_arch/NEWS2PP/arch/arch_NEWS2PP_3561_1_739797937.arc thread=1 sequence=3561
archive log filename=/ora_arch/NEWS2PP/arch/arch_NEWS2PP_2632_2_739797937.arc thread=2 sequence=2632
archive log filename=/ora_arch/NEWS2PP/arch/arch_NEWS2PP_3562_1_739797937.arc thread=1 sequence=3562
archive log filename=/ora_arch/NEWS2PP/arch/arch_NEWS2PP_2633_2_739797937.arc thread=2 sequence=2633
archive log filename=/ora_arch/NEWS2PP/arch/arch_NEWS2PP_3563_1_739797937.arc thread=1 sequence=3563
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-MAR-11
sql statement: ALTER TABLESPACE ishi_test ONLINE
SQL> connect vorai/vorai
Connected.
SQL> select count(*) from t;
COUNT(*)
----------
5
SQL> select * from t;
A
----------
1
2
20<- Value inserted after deleting datafile
200<- Value inserted after deleting datafile
1<- Value inserted after deleting datafile
So my question is, if datafile was not available how did Oracle allowed to insert data into the table? I understand that changed blocks were saved into online REDO log file but then why did Oracle not complain about the missing datafile at any stage (not even in GV$DATAFILE_HEADER & GV$RECOVER_FILE)?
Is it an expected behaviour? Oracle only produced error when I offline the tablespace and tries to online it.
Thanks for your time
March 17, 2011 - 8:07 am UTC
Nothing to do with Oracle, everything to do with the way unix works.
Files, when you "rm" them, are not 'erased', they are simply unlinked from the directory and become "invisible" - but they still exist for as long as any process still has the file open.
http://stackoverflow.com/questions/5219896/how-do-the-unix-commands-mv-and-rm-work-with-open-files when you offlined the tablespace, we closed the files - when you onlined it we tried to open it again, but it wasn't there to be opened anymore. When the last process that had it opened - closed it - the file actually disappeared for the first time.
Tablespace Recovery
A reader, March 18, 2011 - 5:59 am UTC
Thanks Tom. I have been working with UNIX systems for long time now but did not know this! Well, you live you learn.
Again, thanks for you time. Keep on good work.
Cheers,
Ishi
Hot backup/Recovery
chakravarthi thangaveluc, April 19, 2011 - 9:49 am UTC
hi Tom,
alter tablespace users begin backup;
SCN# is freezed for users tablespace's datafile( assume there is only one datafile in users)
now O/S level copy is going on.
now i am doing some update on this tablespace's datafile, so fractured block's will be created.
now copying is finished. Again i am inserting some records in the same users tablespace. then
alter tablespace users end backup;
after 2 days my users tablespace is correpted.
now i have restored my backed up users tablespace (this tablespace's SCN# is lagging compared to other tablespace in the database.) now if i issue the recovery command, the fractured blocks are recovered from the archive log. My question is
1) since the recovered datafile is 2 days old one, so how will it recover the datafile point in time?
2) please explain the mechanism of this recovery?
April 19, 2011 - 6:59 pm UTC
1) by using your archived redo logs and then online redo logs and apply all of the changes from the last two days.
2) ? we read files, process data in them, and apply them to other files.
Hot backup/recovery
chakravarthi thangaveluc, April 19, 2011 - 10:01 am UTC
please add below point to above question
3) How will oracle know from where to start the recovey of fratured blocks.
4) will oracle refers the control file SCN# to recover? if so, since we are recovering the datafile in db open stage only, how will it refer the control file from where to start the recovery?
please help me to solve my doubt by explainiing from starting point of the recovery till end.
April 19, 2011 - 7:01 pm UTC
3) we don't have to worry about that, the first time a block is modified after the alter begin backup, we log the entire block image in the redo log.
4) when you restore, you restore a file, a datafile. We can then inspect that datafile to figure out "how old is it, what redo MIGHT need be applied to it" and we start from there.
Hot recovery
chakravarthi thangaveluc, April 21, 2011 - 4:40 am UTC
from the above you have mentioed about "We can then inspect that datafile to figure out "how old is it, what redo MIGHT need be applied to it", my Question is,
HOw do oracle knows, that this file is older than 2 days, from where will it comes to knwo that point of recovery is to be done from xxx SCN#.
Will oracle come to know from Contro file? ( from there it will know from where to start the recovery?)
April 25, 2011 - 8:04 am UTC
data file header records have everything we need.
"Substantial" Additional Redo?
Matt Miller, October 23, 2012 - 6:32 pm UTC
According to "OCA/OCP Oracle Database 11g All-in-One Exam Guide (Exam 1Z0-051, 1Z0-052, and 1Z0-053)" by Oracle Press, in chapter 18 on user-managed backup:
in backup mode, no matter how small the change vector
is, the entire block is written out to the redo
stream ... the rate of redo generation for all DML
against objects in the tablespace will increase
substantially ... you will find that the database is
log-switching much more frequently
I thought it was interesting that no mention is made that the entire block is written only the first time, and that such fear of backup mode is fostered, regardless of how frequently the same block is being updated. This is particularly interesting since the example the book uses to show how a fractured block can be created is almost identical to the example you opened this discussion with a dozen years ago! Maybe they didn't read the whole post...
SCN values after end backup
A reader, December 04, 2012 - 2:20 am UTC
As the datafile has been updated with the new data but the SCN has been frozen. If I fire the below command.
alter tablespace xxx end backup;
how will oracle manage the frozen header value once the backup is end.how will it sync the SCN value with the new updated data in the datafile header.
Thanks!!!
December 07, 2012 - 4:43 am UTC
it doesn't sync the SCN with the datafile header, it will sync the datafile header with the SCN.
The last completed checkpoint operation would be recorded in the datafile - so that during recovery of that file in the future - we would know what redo we might possibly need in order to fully recover the file.
Thanks to elaborate
Garima, February 01, 2013 - 12:50 pm UTC
Very Useful information. Really given a clear understanding how's the oracle works while in HOT BACKUP MODE.
Recover using the backup controlfile
V V, March 07, 2013 - 12:41 pm UTC
Tom,
I have this lingering doubt for long. Can you please clear it for me ?
When the Oracle DBMS can figure out from the SCN#s in the controlfile and the datafile headers that the controlfile that we are using is older than the Datafile, then why does it asks the user to provide explicitly "backup controlfile" while doing a recover ? It knows the Start SCN from the Datafile, and the End SCN from the redologs and the remaining from the archivelogs and it can move smoothly without the user intervention, right ?
If incomplete recovery is the main idea then anyway incomplete recovery can be done from any controlfile ( new or old ), correct ?
Thanks
V V
March 07, 2013 - 1:10 pm UTC
it is a way to telling us "this is not the current control file" - that is pretty much it. You are saying "do not recover this database up to the point in time in the control file this is a backup we are going to be applying redo until we cancel"
RMAN/insert/select
Chakra, October 07, 2013 - 7:22 pm UTC
Hi Tom,
1) RMAN online full backup is running
2) Insert into user tables is also happening same time/
3) some select query is also running
so will RMAN wait until insert/select complete?
will RMAN wait until insert complte?
what exactly RMAN do (mechanism) on data during backup?
October 09, 2013 - 5:47 pm UTC
No, rman does hot backups with the database open. It will backup constantly even while modifications are happening.
it will back up "fuzzy" datafiles that need redo (archived and online) to restore with. It will just read what it sees on disk and later during recovery would use redo information to restore the files to the same point in time so they are all in sync with each other.