This discussion is archived
14 Replies Latest reply: Jun 8, 2009 5:44 AM by Aman.... RSS

Hot Backup Mechanism

ice_cold_aswin Newbie
Currently Being Moderated
Hi all,

I know that Oracle will not update the datafile headers with SCN when they are in backup mode. But, I have a question, if at all the committed transactions will be written to the datafile during backup? I thought that Oracle will hold all committed data in the DBBC (Entries will be made in Redo Logs though) until tablespaces are taken out of backup mode.

Please correct me if I am wrong.

Thanks,
Aswin.
  • 1. Re: Hot Backup Mechanism
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Here are nice explanation how work hot backup :
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:271815712711

    Nicolas.
  • 2. Re: Hot Backup Mechanism
    Aman.... Oracle ACE
    Currently Being Moderated
    Aswin,

    You are ABSOLUTELY wrong. I am not sure how did you pick up the concept like this but this is plain wrong. Have a look here,
    E:\Documents and Settings\aristadba>sqlplus / as sysdba
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 8 14:36:44 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area  167772160 bytes
    Fixed Size                  1247900 bytes
    Variable Size              75498852 bytes
    Database Buffers           88080384 bytes
    Redo Buffers                2945024 bytes
    Database mounted.
    SQL> alter database archivelog;
    alter database archivelog
    *
    ERROR at line 1:
    ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
    
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> shut imm
    SP2-0717: illegal SHUTDOWN option
    SQL> shut immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area  167772160 bytes
    Fixed Size                  1247900 bytes
    Variable Size              75498852 bytes
    Database Buffers           88080384 bytes
    Redo Buffers                2945024 bytes
    Database mounted.
    SQL> alter database archivelog;
    
    Database altered.
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               1762853
               1762853
               1762853
               1762853
               1762853
               1762853
    
    6 rows selected.
    
    SQL> select checkpoint_change#, last_change# from V$datafile;
    
    CHECKPOINT_CHANGE# LAST_CHANGE#
    ------------------ ------------
               1762853
               1762853
               1762853
               1762853
               1762853
               1762853
    
    6 rows selected.
    
    SQL> 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
    
    6 rows selected.
    
    SQL> select name from V$tablespace';
    ERROR:
    ORA-01756: quoted string not properly terminated
    
    
    SQL> select name from V$tablespace;
    
    NAME
    ------------------------------
    SYSTEM
    UNDOTBS1
    SYSAUX
    USERS
    TEMP
    EXAMPLE
    SONY
    
    7 rows selected.
    
    SQL> alter tablespace sony begin backup;
    
    Tablespace altered.
    
    SQL> select checkpoint_change#, last_change# from V$datafile;
    
    CHECKPOINT_CHANGE# LAST_CHANGE#
    ------------------ ------------
               1762853
               1762853
               1762853
               1762853
               1762853
               1762999
    
    6 rows selected.
    
    SQL> 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 ACTIVE                1762999 08-JUN-09
    
    6 rows selected.
    
    SQL> create table sony_table ( a number) tablespace sony;
    
    Table created.
    
    SQL> begin
      2  for i in 1..100 loop
      3  insert into sony_table values(100);
      4  end loop;
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> alter system flush buffer_cache;
    
    System altered.
    
    SQL> alter tablespace sony end backup;
    
    Tablespace altered.
    
    SQL> select * from sony_table;
    
             A
    ----------
           100
           100
           100
           100
           100
           100
           100
           100
           100
    ....
    You can see here that the tablespace got a table created into it. In that table, we insserted some data which surely was smaller than the cache itself. So we flushed the buffer cache, making sure that the data of the buffer cache is going to be into the data file. After that we got the tablespace out from the backup and we got our data.

    The only thing that does get frozen down in the begin backup mode is that the file headers get freezed at that point when the backup got started. Makes sense in doing so as this point would be remembered by the ocontrol file to identify that when the tablespace would be undergoing recovery, from what point the redo vectors need to get applied. The data does NOT go in to the file header but into the object blocks which has the System Change Number in them. This number gets frozen. So even when your tablespace is in the backup mode, the buffers stilll move as they were moving when the tablespace was in the normal mode. With that. there is no point what so ever to keep the committed in the data buffer cache . Even if you say that your logic is correct, still there can be a situation where your logic would be proved wrong. Buffer cache wont ever be equl to that amount of data in size which is changed by the users. So if we keep the committed data in the cache which is of , say 10meg and you changed 1000 meg, what about this data? Where would we keep it if we wont let it be flished to the data file?

    HTH
    Aman....
  • 3. Re: Hot Backup Mechanism
    mbobak Oracle ACE
    Currently Being Moderated
    Aswin,

    Aman did a fine job answering this question.

    I would just point out, in many cases, like this one, it's quite easy to run a simple test to prove or disprove the question.

    I encourage you to try it yourself. It's really not that hard....

    -Mark
  • 4. Re: Hot Backup Mechanism
    Aman.... Oracle ACE
    Currently Being Moderated
    Thanks Mark :) .

    regards
    Aman....
  • 5. Re: Hot Backup Mechanism
    Girish Sharma Guru
    Currently Being Moderated
    Aman, please correct me if i am wrong.
    It means, we can use the tablespace even in begin backup mode as normal mode; only data file header got freeze to avoid to fracture the block header of data file. Backup may contain fractured block; but header of datafile will never be contained fractured block.

    Regards
    Girish Sharma
  • 6. Re: Hot Backup Mechanism
    Aman.... Oracle ACE
    Currently Being Moderated
    Girish Sharma wrote:
    Aman, please correct me if i am wrong.
    It means, we can use the tablespace even in begin backup mode as normal mode; only data file header got freeze to avoid to fracture the block header of data file. Backup may contain fractured block; but header of datafile will never be contained fractured block.
    Sir , with the release 8i(I guess its the correct version) , the fractured block issue wont' come. For this only , the extra redo is generated which is actually nothing but the actual whole block being logged in teh redo stream before letting the change vectors logged into it. Data file headers have nothing to do wit the fractured block AFAIK. Backup also won't contain the fractured blocks and so would be the header of the data file. How come this thought originated in the first place?

    HTH
    Aman....
  • 7. Re: Hot Backup Mechanism
    Girish Sharma Guru
    Currently Being Moderated
    Aman,

    First let me thanks for clearning doubt.
    "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." reply by Steve Karam sir.

    got from http://www.oraclealchemist.com/oracle/hot-backups-redo-and-fractured-blocks/
    So, i concluded that there may be fractured block in the backup.

    Calling me "Sir" by YOU; it totally out of scope; i am not having any knowledge in any version; just trying to get; before my hair gets white...!

    Best Regards
    Girish Sharma
  • 8. Re: Hot Backup Mechanism
    Aman.... Oracle ACE
    Currently Being Moderated
    Girish,

    Yes Karam is correct. As the backup is done using the dumb commands of o/s , surely there can be fractured blocks in the backups. I overlooked this part and I guess, this caused confusion for you. Here is what is there, you pushed a file for the backup by cp command. The cp commandis the o/s command so it doesn't know what the data file of oracle is . It reads the file in its own way, thus leading to the concept of the fractured block happening. To over come this, the whole block is logged in the redo stream before the change vectors of it. Once recovery starts, this block gets applied first , making the block unfractured and than the application of the change vectors start , bringing the block to the mist current point.

    Hope now its clear. Apologies for any confusion!

    regards
    Aman....
  • 9. Re: Hot Backup Mechanism
    Girish Sharma Guru
    Currently Being Moderated
    Thank you Aman.

    Best Regards
    Girish Sharma
  • 10. Re: Hot Backup Mechanism
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Girish, you really should read the Tom Kyte explanations in the thread I linked earlier, it is quite clear and helpful to remove doubts.

    Nicolas.
  • 11. Re: Hot Backup Mechanism
    Aman.... Oracle ACE
    Currently Being Moderated
    :)

    Aman....
  • 12. Re: Hot Backup Mechanism
    Girish Sharma Guru
    Currently Being Moderated
    Yes sir.
    Thank & Best Regards
    Girish Sharma
  • 13. Re: Hot Backup Mechanism
    ice_cold_aswin Newbie
    Currently Being Moderated
    Aman...
    Your demo was really helpful. Thanks a lot!
  • 14. Re: Hot Backup Mechanism
    Aman.... Oracle ACE
    Currently Being Moderated
    Glad that I could help.

    Cheers
    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points