This document tries to explain exactly what happens when using
ALTER TABLESPACE ... BEGIN BACKUP and ALTER TABLESPACE ... END BACKUP,
and why it is mandatory to use it when the online backup is done with a tool that is external to Oracle ( such as OS backups using cp, tar, BCV, etc. )
It also gives an answer to those frequent questions:
Does Oracle write to data files while in hot backup mode ?
What about ALTER DATABASE BEGIN BACKUP ?
Why it is not used with RMAN backups
What if you do an online backup without setting tablespaces in backup mode ?
What if the instance crashes while the tablespaces is in backup mode ?
How to check which datafiles are in backup mode
What are the minimal archive logs to keep with the hot backup ?
Why use OS backups instead of RMAN ?
Why BEGIN BACKUP takes a long time ?
A cold OS backup is simple: the database has been cleanly shut down (not crashed, not shutdown abort) so that:
all datafiles are consistent (same SCN) and no redo is needed in case of restore
the datafiles are closed: they will not be updated during the copy operation
Thus, it can be restored entirely and the database can be opened without the need to recover.
An hot backup does the copy while the database is running. That means that the copy is inconsistent and will need redo applied to be usable.
Recovery is the process of applying redo log information in order to roll-forward file modifications as they were done in the original files.
When the copy is done with Oracle (RMAN), Oracle copies the datafile blocks to backupset so that it will be able to restore them and recover them.
When the copy is done from the OS (i.e with a tool that is not aware of the Oracle file structure), several issues come up:
Header inconsistency: Nothing guaranties the order in which the files are copied, thus the header of the file may reflect its state at the beginning or at the end of the copy.
Fractured blocks: Nothing guaranties that an Oracle block is read in one single i/o so that two halves of a block may reflect its state at two different points in time.
Backup consistency:As the copy is running while the datafile is updated, it reads blocks at different point in time. The recovery is able to roll forward blocks from the past, but cannot deal with blocks from the future, thus the recovery of the copy must be recovered at least up to the SCN that was at the end of the copy.
So it is all about consistency in the copy: consistency between datafiles, consistency within datafiles and consistency within data blocks, and keep this consistency in the current files (obviously) as well as in the copy (as it will be needed for a restore/recovery)
The goal of ALTER TABLESPACE ... BEGIN BACKUP and ALTER TABLESPACE ... END BACKUP is to set special actions in the current database files in order to make their copy usable, without affecting the current operations.
Nothing needs to be changed in the current datafiles, but, as the copy is done by an external tool, the only way to have something set in the copy is to do it in the current datafiles before the copy, and revert it back at the end.
This is all about having a copy that can be recovered, with no control on the program that does the copy, and with the minimal impact on the current database.
In order to deal with the 3 previous issues, the instance that will do the recovery of the restored datafiles has to know:
that the files need recovery
from which SCN, and up to which SCN it has to be recovered at least
enough information to fix fractured blocks
During backup mode, for each datafile in the tablespace, here is what happens:
1- When BEGIN BACKUP is issued:
The hot backup flag in the datafile headers is set, so that the copy is identified to be a hot backup copy.
This is to manage the backup consistency issue when the copy will be used for a recovery.
A checkpoint is done for the tablespace, so that no dirty buffer remains from modifications done before that point.
Begin backup command completes only when checkpoint is done.
2- During backup mode:
The datafile header is frozen so that whenever it is copied, it reflects the checkpoint SCN that was at the beginning of the backup.
Then, when the copy will be restored, Oracle knows that it needs to start recovery at that SCN to apply the archived redo logs.
This is to avoid the header inconsistency issue.
That means that any further checkpoints do not update the datafile header SCN (but they do update a 'backup' SCN)
Each first modification to a block in buffer cache will write the full block into the redo thread (in addition to the default behaviour that writes only the change vector).
This is to avoid the fractured block issue. There may be a fractured block in the copy, but it will be overwritten during the recovery with the full block image.
That means that everything goes as normal except for two operations:
- at checkpoint the datafile header SCN is not updated
- when updating a block, the first time it is updated since it came in the buffer cache, the whole before image of the block is recorded in redo
- direct path writes do not go through the buffer cache, but they always write full blocks and then full block is written to redo log (if not in nologging)
3- When END BACKUP is issued:
A record that marks the end of backup is written to the redo thread so that if the copy is restored and recovered, it cannot be recovered earlier than that point. This is to avoid the backup consistency issue.
The hot backup flag in the datafile headers is unset.
The header SCN is written with the current one.
Remarks:
the fractured block is not frequent as it happens only if the i/o for the copy is done at the same time on the same block as the i/o for the update. But the only mean to avoid the problem is to do that full logging of block for each block that will be written while the copy is occuring, just in case.
if the OS i/o size is multiple of the Oracle block size (e.g backup done with dd bs=1M), that supplemental logging is probably not needed because fractured blocks cannot happen.
the begin backup checkpoint is mandatory to manage the fractured block issue: if a dirty buffer remains, from a modification done before the begin backup, it would have no full-image redo, and may be subject to fractured block when written to disk .
The supplemental logging occurs when accessing the block for the first time in the buffer cache. If the same block is reloaded again in the buffer cache, supplemental logging will occur again. I haven't seen that point documented, but a testcase doing a 'flush buffer_cache' proves that.
When the copy has been done between begin backup and end backup, the copy is fully available to be restored and recovered using the archive log files that where generated since the begin backup.
After the files have been restored, Oracle sees that the SCN is older than the current one and says that the database needs recovery.
The recovery must be done up to a point in time ulterior to the end backup point in time so that we are sure that there is no blocks in the datafile that comes from the future.
All operations can be done during the backup mode.
However, as more logging is written, it should be done during a low activity period. And for the same reason, it is better to do the tablespaces one after one instead of putting all the database tablespaces in backup mode.
In addition, it is not possible to shutdown the database while a tablespace is in hot backup.
This is because, as the datafile header is frozen with a non current SCN, the datafile would be seen as if it requires recovery.
However that cannot be avoided if the instance crashes (or shutdown abort), and then the startup of the database will raise:
ORA-1113: file ... needs media recovery
This is the only case I know where instance recovery is not automatic, you need to issue 'alter database... end backup;' before opening the database.
Yes of course, it would not be called 'online' backup if it were not the case.
That command put all database tablespaces in backup mode at the same time.
As seen previously, it is a bad idea to put all tablespaces in backup mode, as it is better to do it one by one in order to minimize the supplemental redo logging overhead.
Oracle introduces this 'shortcut' for one reason only: when doing backup with a mirror split (BCV, Flashcopy, etc), the copy gets all the datafiles at the same time, and the copy lasts only few seconds. In that case, it is easier to use that command to put all tablespaces in backup mode during the operation.
RMAN is an Oracle tool, that is totally aware of the datafile structure, and the way they are written.
Then, it knows how it can read the datafiles in a way the copy is consistent: write the good version of datafile header, read the blocks with an i/o size that is multiple of the Oracle block size so that there is no fractured blocks, and check head and tail of the block to see if block is fractured (in that case, it re-reads the block to get a consistent image).
That is one advantage among many others of using RMAN for backups.
If you don't put the tablespace in backup mode, we can't be sure that the copy is recoverable. It may be fine, but it may have inconsistencies.
We can suppose that the copy is consistent if we make the copy with the following conditions
Header inconsistency: If the file copy is done from beginning to end, then the datafile header should reflect the right SCN
Fractured blocks: If the copy does i/o with a size that is multiple of the Oracle block size, then you should not have fractured blocks
Backup consistency:If you take care to recover later than the point in time of the end of the copy, you should not have inconsistency
But there may be other internal mechanisms that are not documented so that we can't be sure that this list of issues is exhaustive.
And, as it is not supported, we cannot rely on a backup done like that. Note that you will have no message
When you start the database after that, Oracle will say that it requires recovery. This is because the SCN was frozen and it is the expected behaviour because if you restore the copied file, it has to be recovered. (and the only way Oracle has to set its value in the copy is to set it in the current file while it is copied)
In that case you can can issue:
ALTER DATABASE END BACKUP;
ALTER DATABASE OPEN;
to open the database.
But your backup is not usable, you have to do it again.
The V$BACKUP view shows the datafiles that are currently in backup mode (status ACTIVE).
Some old documentation says to check V$DATAFILE_HEADER column FUZZY.
This is because in previous versions (<9i) the begin backup unsets the online fuzzy bit in the datafile header, and set it back at when end backup is issued.
Since 9i, the online fuzzy bit is unset only when datafile is offline or read-only, not for backup mode.
The backup done online is unusable if there is not at least the possibility to restore archive logs
- from the archive log that was the current redo log when the backup started,
- up to the archive log that was archived just after the backup (of the whole database) ended.
That is sufficient to do an incomplete media recovery up to the point of 'end backup'.
Subsequent archive logs will be needed to recover up to the point of failure.
The best way to do online backups is using RMAN as it has a tons of features that you cannot have with OS backups.
Yet, the OS backup are still used when using OS tools that can copy an entire database in seconds, using mirror split (BCV, Flashcopy, etc), for very large databases.
BEGIN BACKUP has to checkpoint the dirty buffers related with the tablespace, so that all future writes are protected by supplemental logging.
The duration of that checkpoint is proportional to the buffer cache size and the number of datafiles.
The performance of BEGIN BACKUP checkpoint has been improved in 10g.
#########################################################################################################################
MISCONCEPTION: HOT BACKUP MODE STOPS WRITING TO THE DATAFILES
Jeremiah Wilton
During an Oracle tablespace hot backup, a script or program puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode. Although these steps are widely understood, the idea that datafiles are not written during backup mode is probably the most alarming and widely held misconception about Oracle. So many people think this is true, that it is actually asserted as fact in the backup/recovery sections of a number of major published books on Oracle. Numerous websites on the topic also make the assertion that datafiles are not writable during backup. Many people have reported that Oracle Education DBA classes and third-party classes teach this fallacy as fact.
The erroneous descriptions have several permutations. Most claim that while the datafiles are allegedly not writable, changes are stored somewhere in the SGA, the redologs, the rollback segments or some combination thereof, then written back into the datafile when the tablespace is taken out of backup mode. The passage from Oracle Unleashed describing this supposed mechanism is representative.
When you place a tablespace in backup mode, the Oracle instance notes that a backup is being performed and internally compensates for it. As you know, it is impossible to make an authentic copy of a database file that is being written to. On receipt of the command to begin the backup, however, Oracle ceases to make direct changes to the database file. It uses a complex combination of rollback segments, buffers, redo logs, and archive logs to store the data until the end backup command is received and the database files are brought back in sync.
Simplifying a hot backup in this way is tantamount to classifying the USS Nimitz as a boat. The complexity of the actions taken by the Oracle RDBMS under a hot backup could consume an entire chapter and is beyond the scope of this book. What you should understand is the trade-off for taking a hot backup is increased use of rollback segments, redo logs, archive logs, and internal buffer areas within the SGA.
(From Oracle Unleashed, Copyright Ó SAMS/Macmillan, Inc. 1997, chapter 14)
In fact, Oracle’s tablespace hot backup does not work this way at all. Rather, it is a simple and failure-resistant mechanism. It absolutely does not stop writing the datafiles, and actually allows continued operation of the database almost exactly as during normal operation. Contrary to the characterization as “complex” in SAMS Oracle Unleashed, it can be almost completely summarized in one sentence.
· The tablespace is checkpointed, the checkpoint SCN marker in the datafile headers cease to increment with checkpoints, and full images of changed DB blocks are written to the redologs.
Those three actions are all that is required to guarantee consistency once the file is restored and recovery is applied. By freezing the checkpoint SCN in the file headers, any subsequent recovery on that backup copy of the file will know that it must commence at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the redolog file containing that SCN, and apply recovery starting there. Note that checkpoints to datafiles in hot backup mode are not suppressed during the backup, only the incrementing of the main checkpoint SCN flag. A “hot backup checkpoint” SCN marker in the file header continues to increment as periodic or incremental checkpoints progress normally.
There is a confusing side effect of having the checkpoint SCN artificially frozen at an SCN earlier than the true checkpointed SCN of the datafile. In the event of a system crash or a ‘shutdown abort’ during hot backup of a tablespace, the automatic crash recovery routine at startup will think that the files for that tablespace are quite out of date, and will actually suggest the application of old archived redologs in order to bring them back into sync with the rest of the database. It is unnecessary, in this case, to heed Oracle’s suggestion. With the database started up in mount mode, simply check v$backup and v$datafile to determine which datafiles were in backup mode at the time the database crashed. For each file in backup mode, issue an ‘alter database datafile '<file name>' end backup;’ This action will update the checkpoint SCN in the file headers to be the same as the hot backup checkpoint SCN (which is a true representation of the last SCN to which the datafile is truly checkpointed. Once this action is taken, it allows normal crash recovery to proceed during the ‘alter database open;’ command.
By initially checkpointing the datafiles that comprise the tablespace and logging full block images to redo, Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be available in the redologs in case they are ever used for a recovery.
It is well understood by much of the Oracle user community that during hot backup mode, a greater volume of redo is generated for changes to the tablespace being backed up than when the tablespace is not in backup mode. This is the result of the logging of full images of changed blocks in these tablespaces to the redologs. Normally, Oracle logs an entry in the redologs for every change in the database, but it does not log the whole image of the database block. By logging full images of changed DB blocks to the redologs during backup mode, Oracle eliminates the possibility of the backup containing irresolvable split blocks. To understand this reasoning, you must first understand what a split block is.
Typically, Oracle database blocks are a multiple of O/S blocks. For instance, most Unix filesystems have a default block size of 512 bytes, while Oracle’s default block size is 2k. This means that the filesystem stores data in 512 byte chunks, while Oracle performs reads and writes in 2k chunks, or multiples thereof. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd, cpio, or OCOPY. As it is making this copy, it is reading in O/S-block sized increments. If the database writer happens to be writing a DB block into the datafile at the same time that your script is reading that block’s constituent O/S blocks, your backup copy of the DB block could contain some O/S blocks from before the database performed the write, and some from after. This would be a split block.
By logging the full block image of the changed block to the redologs, it guarantees that in the event of a recovery, any split blocks that might be in the backup copy of the datafile will be resolved by overlaying them with the full legitimate image of the block from the redologs. Upon completion of a recovery, any blocks that got copied in a split state into the backup will have been resolved through application of full block images from the redologs.
It is important to remember that all of these mechanisms exist for the benefit of the backup copy of the files and the recovery process, and have very little effect on the datafiles of the database being backed up. The database files are read by server processes and written by the database writer throughout the backup, as they are when a backup is not taking place. The only difference manifested in the open database files is the freezing of the checkpoint SCN, and the incrementing of the hot-backup SCN.
To demonstrate the principle, we can formulate a simple proof.
· A table is created and a row of data inserted.
SQL> create table fruit (name varchar2(32)) tablespace administrator;
Table created.
SQL> insert into fruit values ('orange');
1 row created.
SQL> commit;
Commit complete.
· A checkpoint is forced, to flush dirty DB block buffers to the datafiles.
SQL> alter system checkpoint;
System altered.
· The block number in the datafile where the data resides is determined.
SQL> select dbms_rowid.rowid_block_number(rowid) blk, name from fruit;
BLK NAME
------- ----------------
3 orange
· The DB block containing an identifiable piece of the data is excised from a datafile using the Unix dd command, allowing 9 DB blocks for the raw partition header and datafile header, plus the block offset of three.
unixhost% dd ibs=8192 skip=11 count=1 \
> if=/raw-01/databases/toy/administrator-01.dbf | strings
1+0 records in
16+0 records out
orange
· The tablespace is placed into hot backup mode.
SQL> alter tablespace administrator begin backup;
Tablespace altered.
· The row is updated, committed, and a global checkpoint forced on the database.
SQL> update fruit set name = 'plum';
1 row updated
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
· The same block is extracted, showing that the DB block has been written on disk.
unixhost% dd ibs=8192 skip=11 count=1 \
> if=/raw-01/databases/toy/administrator-01.dbf | strings
1+0 records in
16+0 records out
orange,
plum
· The tablespace is taken out of backup mode
SQL> alter tablespace administrator end backup;
It is quite clear from this demonstration that datafiles receive writes even during hot backup mode.