14 Replies Latest reply: Jun 8, 2009 7:44 AM by Aman.... RSS

    Hot Backup Mechanism

    ice_cold_aswin
      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
          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....
            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
              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....
                Thanks Mark :) .

                regards
                Aman....
                • 5. Re: Hot Backup Mechanism
                  Girish Sharma
                  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....
                    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
                      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....
                        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
                          Thank you Aman.

                          Best Regards
                          Girish Sharma
                          • 10. Re: Hot Backup Mechanism
                            Nicolas.Gasparotto
                            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....
                              :)

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

                                    Cheers
                                    Aman....