Microsoft Word - What does the begin backup by rocker12

What does the "Begin Backup" command do?

Backup and Recovery Tips

What exactly does the "begin backup" command do? The command alter tablespace blah begin backup does two key things. First, it effectively locks the SCN stored in the header of the Data Files associated with that tablespace. Second, it starts the generation of block-level redo for every transaction.

Locking the SCN How it actually achieves this is simple: first, a checkpoint is issued against the tablespace so that any blocks in the Buffer Cache that come from that tablespace are flushed to disk, and the CKPT process updates the SCN in the headers of that tablespace's Data Files. Second, once the tablespace checkpoint is completed, a bit is set in the Control File to prevent CKPT from making any future updates to the headers of that tablespace's Data Files. We can see this happening in the following short demo: SQL> SELECT T.NAME, H.FILE#, H.CHECKPOINT_CHANGE# 2 FROM V$TABLESPACE T, V$DATAFILE_HEADER H 3 WHERE T.TS#=H.TS#; NAME FILE# CHECKPOINT_CHANGE# ------------------------------ ---------- -----------------SYSTEM 1 121923 UNDOTBS 2 121923 121923 INDX 3 TOOLS 4 121923 USERS 5 121923 Note at this point how all tablespaces have the same checkpoint change number in their headers (as selected from v$datafile_header). SQL>ALTER TABLESPACE USERS BEGIN BACKUP; SQL> SELECT T.NAME, H.FILE#, H.CHECKPOINT_CHANGE# 2 FROM V$TABLESPACE T, V$DATAFILE_HEADER H 3 WHERE T.TS#=H.TS#; NAME FILE# CHECKPOINT_CHANGE# ------------------------------ ---------- -----------------SYSTEM 1 121923 UNDOTBS 2 121923 INDX 3 121923 121923 TOOLS 4 USERS 5 121925 Now notice that by putting just one tablespace into hot backup mode, that tablespace's checkpoint number has advanced, but the others' remain at the same checkpoint change number as previously. Only the affected tablespace has been checkpointed, therefore. Copyright ÂŠ Howard Rogers 2001

24/10/2001

Page 1 of 7

What does the "Begin Backup" command do?

Backup and Recovery Tips

Now we'll do some transactional activity on the database: INSERT INTO SCOTT.EMP VALUES INSERT INTO SCOTT.EMP VALUES INSERT INTO SCOTT.EMP VALUES

.AND

(1,'SMITH',500); (2,'MOZART',1500); (3,'BEETHOVEN',700);

SO ON AND ON..

COMMIT;

COMMIT

COMPLETE.

I've abbreviated this for simplicity's sake, but the point is that we've been doing some insert activity on the database, and thus advancing the database's checkpoint number. SQL> ALTER SYSTEM SYSTEM ALTERED.

CHECKPOINT;

This command is issued purely for demo purposes: it forces whatever checkpoint number we've reached to be written to every Data File in the system... SQL> SELECT T.NAME, H.FILE#, H.CHECKPOINT_CHANGE# 2 FROM V$TABLESPACE T, V$DATAFILE_HEADER H 3 WHERE T.TS#=H.TS#; NAME FILE# CHECKPOINT_CHANGE# ------------------------------ ---------- -----------------121939 SYSTEM 1 UNDOTBS 2 121939 121939 INDX 3 TOOLS 4 121939 USERS 5 121925 ...except, as we see, for the one tablespace that was put into hot backup mode earlier, which is still stubbornly pretending that its SCN is 121925, and not what the rest of the database actually knows it to be(121939). It's important to realise that the SCN reported in v$datafile_header is a complete lie as far as the actual contents of the tablespace are concerned. By that I mean that whilst the header reports the SCN to be 121925, the actual contents of the tablespace are actually at SCN 121939 -in this particular demo, that's definitely the case, since all the inserts shown in the text were actually performed to a table contained within the USERS tablespace itself. The point of course is that if you are performing Operating System hot backups, the O/S will copy which ever O/S block happen to spin into view under the disk read head at any one time -and that might not be the file's Header Block (in fact, the chances of it ever Copyright ÂŠ Howard Rogers 2001

24/10/2001

Page 2 of 7

What does the "Begin Backup" command do?

Backup and Recovery Tips

being the Header Block are pretty small -in the order of thousands to one against). That's a problem for recovery purposes, since to pull off a recovery we need to know, reliably, what the earliest possible contents of our Data File backup might be, and take that as the point from which to start applying redo. By simply freezing the SCN in the header, in the way I've shown here, at the time when the tablespace entered hot backup mode, we guarantee to be able to know this "earliest possible time" from which to start applying redo, and hence ensure a successful recovery.

Block-level Redo An Oracle Block is usually made up of several O/S blocks (an 8K block will usually contain 16 O/S blocks, for example, since most file systems seem to use 512-byte O/S blocks). That poses another potential problem for hot O/S backups: we might copy 1 of the O/S blocks in an Oracle block before a User performs an update, and the other O/S blocks after that update. This is a fractured or a "split block": the various components of a single Oracle block don't agree with each other. Fractured blocks are unusable, and we need a way of preventing them from happening -yet we have no control over the order in which the O/S copies the constituent O/S blocks. The solution is to generate more or less a complete image of the Oracle block in the redo logs every time a piece of DML affects it. That way, although the block contained within the copied Data File might be fractured, we have a "snapshot" image of the block in the redo stream which we can use to patch up the mess. This mechanism certainly works, and will mean that hot backups can be used reliably to recover a database, regardless of how much DML activity is taking place on the database at the time the backup is taken. But there's a catch: Normally, redo contains before and after images of the actual data we are changing by issuing DML. If we update a person's salary, we store a relatively small amount of redo describing just that one record's changes. But in hot backup mode, the same update requires us to store a complete image of the entire Oracle block -and such blocks are several Kilobytes in size. What that means is that to avoid block fracturing, we are reduced to making the rate at which we generate redo during a hot backup shoot through the roof. Again, a small demo will indicate the size of the potential problem. For the purposes of this demo, be aware that the X$KCCCP table contains a column called CPODR_BNO, which shows us the Redo block number we are currently writing to. Redo blocks are effectively identical to O/S blocks, so the smallest piece of redo is going to take up an entire O/S block -i.e., 512 bytes on most systems.

24/10/2001

Page 3 of 7

What does the "Begin Backup" command do?

Backup and Recovery Tips

So, to begin with, we identify where we're sitting in the current redo log: SQL> COL CPODR_BNO HEADING 'BLOCK NUMBER' SQL> SELECT P.CPODR_BNO FROM SYS.X$KCCCP P; BLOCK NUMBER ---------357 SQL> 1 ROW

INSERT INTO SCOTT.EMP VALUES

(8,'HARRIS',450);

CREATED.

SQL> COMMIT; COMMIT COMPLETE. SQL> COL CPODR_BNO HEADING 'BLOCK NUMBER' SQL> SELECT P.CPODR_BNO FROM SYS.X$KCCCP P; BLOCK NUMBER ---------359 And we see at this point that a simple insert of a single record has advanced the block where we're now writing compared to where we were originally by 2 O/S blocks -or 1K. Now let's re-run the same test, this time in hot backup mode. We'll begin by deleting the row just inserted, so that we can insert exactly the same data (and hence exactly the same amount of data) in the new test: SQL> 1 ROW

DELETE FROM SCOTT.EMP WHERE EMPNO=8; DELETED.

SQL> COMMIT; COMMIT COMPLETE. SQL> COL CPODR_BNO HEADING 'BLOCK NUMBER' SQL> SELECT P.CPODR_BNO FROM SYS.X$KCCCP P; BLOCK NUMBER ---------363 SQL> ALTER TABLESPACE TABLESPACE ALTERED. SQL> INSERT INTO 1 ROW CREATED. SQL> COMMIT; COMMIT COMPLETE.

USERS BEGIN BACKUP;

SCOTT.EMP VALUES

(8,'HARRIS',450);

24/10/2001

Page 4 of 7

What does the "Begin Backup" command do?

Backup and Recovery Tips

SQL> COL CPODR_BNO HEADING 'BLOCK NUMBER' SQL> SELECT P.CPODR_BNO FROM SYS.X$KCCCP P; BLOCK NUMBER ---------376 And we see now that the write block has advanced from block 363 to block 376 -that's 13 O/S blocks, or around 6.5K. So, in hot backup mode, we're generating 6 to 7 times the amount of redo compared to what would normally be generated for the identical transaction. For the record, on a 4K-Oracle block database, I've measured deletes also generating about 7 times the amount of redo when performed in hot backup mode compared to normal. And I've measured updates generating an astonishing 30 times the amount of redo. This discrepancy between inserts/deletes (7 times the redo) and updates (30 times) is easily understandable: one half of the redo entry for a delete or insert usually consists of the entire row affected (the before image of a delete is the entire row, as is the after image of an insert). But the before and after image of an update is only the field(s) actually affected by the update -and hence is relatively tiny. Yet in hot backup mode, all three types of DML now must cause an entire Oracle block to be written into the redo stream. Updates (which are usually relatively small) are thus going to be proportionately much more affected than inserts or deletes (which are usually comparatively large). (Incidentally, you might wonder, if block-level redo is enabled by issuing the 'begin backup' command, why my insert above generated 6.5K-worth of redo, when the database involved was using 4K Oracle blocks. Shouldn't the test have shown 4K of redo being generated? The answer is that there is always some additional house-keeping data required whenever redo is generated, and we're dealing with a minimum granularity of 512 bytes, because even 1 byte of redo must use an entire 512 byte O/S block. For the record, a single insert on a 2K Oracle Block system (using Solaris) caused 4K of redo to be generated during hot backup mode, which again suggests the Oracle block-sized redo itself, plus housekeeping overhead and O/S-block size rounding issues are all coming into play). Whatever the precise numbers from these simplified tests show us, the implication is clear: switching into hot backup mode is going to flood your redo sub-system with anything from 6 or 7, up to 30 or so, times the amount of data its usually having to cope with. The precise "inflation rate" will vary from database to database, but the general point should be plain. Hot O/S-based backups therefore need to take this into account, since if redo logs fill up 30 times more quickly than they otherwise would do, there is a real risk of them wanting to switch back to the first Online log before that log has been successfully checkpointed

24/10/2001

Page 5 of 7

What does the "Begin Backup" command do?

Backup and Recovery Tips

and/or archived. That would result in a temporary database-wide hang (i.e., the suspension of all DML activity for all Users) until ARCH, DBWR and/or CKPT have caught up. This is the main reason to avoid placing all tablespaces into hot backup mode simultaneously, and attempting to back up all Data Files in one pass: one tablespace generating 30 times its normal amount of redo might be bearable. For the entire database to be doing so is almost certainly going to cause performance problems and temporary hangs. One final question springs to mind: if you were to perform multiple DMLs affecting the one block, do we generate multiple block images in the redo stream, or just one when the final commit is issued? The answer appears to be that even when multiple updates are issued by the one session, only one block image is generated, as this simple test shows: SQL> SELECT P.CPODR_BNO BLOCK NUMBER ---------433 SQL> ALTER TABLESPACE TABLESPACE ALTERED. SQL> 1 ROW

FROM SYS.X$KCCCP P;

USERS BEGIN BACKUP;

UPDATE SCOTT.EMP SET SAL=250 WHERE EMPNO=1; UPDATED.

SQL> SELECT P.CPODR_BNO BLOCK NUMBER ---------446

FROM SYS.X$KCCCP P;

Clearly, the 13 O/S block redo entry has been made by doing a single update. SQL> 1 ROW

UPDATE SCOTT.EMP SET SAL=250 WHERE EMPNO=2; UPDATED.

SQL> UPDATE SCOTT.EMP 2 ROWS UPDATED.

SET SAL=250 WHERE EMPNO=3;

SQL> SELECT P.CPODR_BNO BLOCK NUMBER ---------447

FROM SYS.X$KCCCP P;

24/10/2001

Page 6 of 7

What does the "Begin Backup" command do?

Backup and Recovery Tips

Note that three extra rows have been updated, but the amount of redo written has not increased significantly as a result of the additional updates SQL> 1 ROW

UPDATE SCOTT.EMP SET SAL=250 WHERE EMPNO=5; UPDATED.

SQL> SELECT P.CPODR_BNO BLOCK NUMBER ---------447

FROM SYS.X$KCCCP P;

SQL> COMMIT; COMMIT COMPLETE. SQL> SELECT P.CPODR_BNO BLOCK NUMBER ---------449

FROM SYS.X$KCCCP P;

And again, at the end of the entire transaction, the amount of redo written has not increased dramatically from the point it had reached after the very first update was generated. Now all the above tests were done within the one session, but it remains the case even when two separate sessions start performing transactions that affect different records housed within the same Oracle block: the block image is produced in the redo stream when the first update takes place, and subsequent updates do not add significantly to the amount of redo generated, regardless of which session is performing them.

24/10/2001

Page 7 of 7