Today , I have come across a good article. I have worked and modify it to explained in detail . Hope you all appreciate it . Before discussing this topic, let's have an overview of user-managed hot backup i.e, what happen during begin backup mode and end backup mode.
When we begin backup, it freezes the header of the datafiles (means the SCN number will not increment any more until the backup is ended ) . It also instructs LGWR to write whole blocks to redo the first time a block is touched. Writes still occur to the datafile, just the SCN is frozen. This occurs so that at recovery time, Oracle will know that it must overwrite all blocks in the backup file with redo entries due to fracturing. The original datafiles remain up-to-date, but the backup files will not be because they are being changed during backup. When we end backup, it unfreezes the header of the datafiles and allows SCNs to be recorded properly during checkpoint.
Question: The oracle documentation tells us that when we put a tablespace in backup mode , the first DML in the session logs the entire block in the redo log buffer and not just the changed vectors.
1.) Can we simulate an example to see this happening?
2.) What can be the purpose of logging the entire block the first time and not do the same subsequently?
Answer: Below, I’ve created a simulation. Pay attention to the “redo size” statistic in each. First, I have updated a single row of the employees table.
SQL> set autotrace trace stat
SQL> update employees set first_name='Stephen' where employee_id = 100;
1 row updated.
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1 consistent gets
0 physical reads
292 redo size
669 bytes sent via SQL*Net to client
598 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> rollback;
Rollback complete.
Notice the redo size was only 292 bytes, not a very large amount. Now, let’s put the USERS tablespace into hot backup mode.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> update employees set first_name = 'Stephen' where employee_id = 100;
1 row updated.
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
1 consistent gets
0 physical reads
8652 redo size
670 bytes sent via SQL*Net to client
598 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Wow! Quite a bit of a difference. This time, we can see that atleast an entire block was written to redo; 8,652 bytes total. Let’s run it one more time, with the tablespace still in hot backup mode.
SQL> /
1 row updated.
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1 consistent gets
0 physical reads
292 redo size
671 bytes sent via SQL*Net to client
598 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
This time , it only used 292 bytes, the same as the original amount. However, to address the second question, we’re going to attempt changing a different block, by changing a record in the departments table instead of employees.
SQL> update departments set department_name = 'Test Dept' where department_id = 270;
1 row updated.
Statistics
----------------------------------------------------------
17 recursive calls
1 db block gets
5 consistent gets
1 physical reads
8572 redo size
673 bytes sent via SQL*Net to client
610 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
The result is that another entire block was written to redo. In the question, we stated: “The oracle documentation tells us that when we put a tablespace in backup mode , the first DML in the session logs the entire block in the redo log buffer and not just the changed vectors” . This is close, but not right on the mark.
It is not the first DML of the session, but the first DML to a block that is written to redo . However, when Oracle writes the first DML for the block, it ensures that the redo logs/archive trail contains at least one full representation of each block that is changed. Subsequent changes will therefore be safe.
It is not the first DML of the session, but the first DML to a block that is written to redo . However, when Oracle writes the first DML for the block, it ensures that the redo logs/archive trail contains at least one full representation of each block that is changed. Subsequent changes will therefore be safe.
This process exists to resolve block fractures. A block fracture occurs when a block is being read by the backup, and being written to at the same time by DBWR . Because the OS (usually) reads blocks at a different rate than Oracle, the OS copy will pull pieces of an Oracle block at a time. What if the OS copy pulls half a block, and while that is happening, the block is changed by DBWR? When the OS copy pulls the second half of the block it will result in mismatched halves, which Oracle would not know how to reconcile .
This is also why the SCN of the datafile header does not change when a tablespace enters hot backup mode. The current SCNs are recorded in redo, but not in the datafile. This is to ensure that Oracle will always recover over the datafile contents with redo entries. When recovery occurs, the fractured datafile block will be replaced with a complete block from redo, making it whole again. After Oracle can be certain it has a complete block, all it needs are the vectors.
Enjoy :-)
