Long backup I had asked with Ritesh (http://education.oracle.com/education/otn/rsingh.htm)
about the hidden parameter “_LOG_BLOCKS_DURING_BACKUP’ and
he explain what exactly happened when any tablespace in taken in the begin
backup mode.
Below are the highlights:
Step 1 : Freeze the Checkpoint for the requested datafiles,
this allows the identification of backup mode for the datafile.
Step 2 : Any block writing / updating to the datafiles by
DBWn will also copy the same block to Redo Log Files.
Step 1 is required for identifying the backup mode and
initiating the image copy to be written to redo log files.
Step 2 is required for Fractured block recovery at a later
stage, when the backup copy will be used for restore.
Let’s take a test case
e.g : I started a backup at 9:30 in the morning, backup
started by putting USERS tablespace into backup mode, during the backup there
were some batch process running simultaneously. Since the block size of my
database is 8K, an update to Table (T)will affect a change inblocks of 8K Size,
but since the backup is done by CP command, OS will copy the block of 512b as
per OS Blocksize in each call.
When cp command will start copying, there would be a
possibility that in order to copy one oracle block it has to make 16 calls and
in between each call if the entire block of oracle is changed by the batch
process, the outcome backup copy will be fractured. To avoid this oracle marks
the same copy to Redo log, thus generating lots of redo.
SQL>
conn scott/tiger
Connected.
SQL>
set autotrace trace stat
SQL>
update test set ename = 'test' where empno = 7369;
1
row updated.
Statistics
----------------------------------------------------------
0
recursive calls
3
db block gets
3
consistent gets
0
physical reads
348
redo size
671
bytes sent via SQL*Net to client
617
bytes received via SQL*Net from client
3
SQL*Net roundtrips to/from client
1
sorts (memory)
0
sorts (disk)
1
rows processed
SQL>
rollback;
Rollback
complete.
We
can see, the redo is generated in minimum amount of time. After then I put the uses tablespace in begin
backup mode and executed the same statement:
SQL>
update test set ename = 'test' where empno = 7369;
1
row updated.
Statistics
----------------------------------------------------------
0
recursive calls
2
db block gets
3
consistent gets
0
physical reads
8636
redo size
671
bytes sent via SQL*Net to client
617
bytes received via SQL*Net from client
3
SQL*Net roundtrips to/from client
1
sorts (memory)
0
sorts (disk)
1
rows processed
But
this time it generated large amount of redo. But when I executed the same
statement again, the redo generation is normal.
SQL>
update test set ename = 'test' where empno = 7369;
1
row updated.
Statistics
----------------------------------------------------------
0
recursive calls
1
db block gets
3
consistent gets
0
physical reads
348
redo size
671
bytes sent via SQL*Net to client
617
bytes received via SQL*Net from client
3
SQL*Net roundtrips to/from client
1
sorts (memory)
0
sorts (disk)
1
rows processed
The
first time ORACLE has generated the maximum redo (copying the entire block in
the redo ) in order to recover from the fuzziness of data file at time of
recovery if happen. But why ORACLE won’t generate the redo for second time, I
have executed the same statement but for the different block:
SQL>
update test set ename = 'test' where empno = 7934;
1
row updated.
Statistics
----------------------------------------------------------
1
recursive calls
1
db block gets
3
consistent gets
0
physical reads
8636
redo size
673
bytes sent via SQL*Net to client
617
bytes received via SQL*Net from client
3
SQL*Net roundtrips to/from client
1
sorts (memory)
0
sorts (disk)
1
rows processed
SQL>
This
time, ORACLE has again generated the lage amount of redo. So what I concludes
is ORACLE generate of redo by copying the entire block in redo logfile. But time
happens for one time only. Even if any row changes in the same block, it won’t
be copied again.
