1 2 Previous Next 17 Replies Latest reply: Feb 11, 2010 9:32 AM by UweHesse RSS

    doubts on hot backup

    poorna
      Hi,

      Oracle Version:10.2.0.1

      Here my doubt is what happen to the database when we forget to give the statement "ALTER DATABASE END BACKUP " after putting the database in the "ALTER DATABASE BEGIN BACKUP".

      Thanks & Regards,
      Poorna Prasad.
        • 1. Re: doubts on hot backup
          Deepak_DBA
          hi,

          nothing will happen the datafile will be in backup mode.. so more redos , archives.. if archive dest full then DB hang...



          regards,
          Deepak
          • 2. Re: doubts on hot backup
            VijayaraghavanKrishnan
            Hi,

            For example when we a tablespace in backup mode (BEGIN BACKUP) command is issued, the SCN is forzen at the datafile headers, from that point onwards till you put the tablespace in (END BACKUP), the changes will go to the redo log files, the redo log files will hold the entire oracle block. So at in mean time you can see lots of redo generation happening. Once the END BACKUP is issued the changes will be written to the tablespace.

            if there are any mistakes in this, experts please correct me.

            [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:271815712711]
            [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:894628342039]

            Regards,
            vijayaraghavan K
            • 3. Re: doubts on hot backup
              arpit sinha
              If a tablespace is put into backup mode what happens in the
              background.

              When a tablespace is put into backup mode, all datafiles header will be frozen
              and there will be no checkpoint updates. Any updates and deletes and insert happens
              normally as it does and DBWn writes Data buffer blocks to disks. The only thing that
              doesn't happen inside tablespace is checkpoint updates which says that whatever data
              that is written is having a duplicate entry in redo log files.

              Oracle writes the same block of information to redo log files as in datafile when
              tablespace is put into backup mode. This is the reason why high redo is generated
              when tablespace is put into backup mode.the reason of writing to redo log files is
              to maintain a copy of block for fractured block recovery.


              Fractured block recovery, when tablespace is put into backup mode, redo log files
              contain the same block, when a copy of datafile happens and then DML activity may
              create fractured block copy by datafiles. Reason is OS and Oracle block size difference.
              If OS process copies a block is copies a part of block.

              e.g Oracle Block size = 8192
              OS Block size = 512

              it takes 8192/512 = 16 calls to copy one oracle block which means that, there are
              chances that oracle blocks which is getting copied may get changed while OS process is
              copying the same.

              e.g if OS process has executed 9 calls and copied 4602 blocks, and in the mean time
              if some DMLs has updated oracle block than It becomes fractured while copying to disk.



              Thus the overall idea is to freeze the checkpoint and allow files to be copied with same
              blocks getting copied to redo log files ( Archived Log Files ) as well, which is used in
              case of recovery when a fractured block is detected.

              when you end backup, current checkpoint is updated and no more entry is made to redo log
              files.


              A R P I T S I N H A
              Oracledba.in
              • 4. Re: doubts on hot backup
                Deepak_DBA
                hi,

                >
                Once the END BACKUP is issued the changes will be written to the tablespace.
                When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files.

                When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.


                regards,
                Deepak
                • 5. Re: doubts on hot backup
                  Aman....
                  Deepak, Arpit, Vijay,

                  Please note that only for the first time, the complete block is going to be copied into the log buffer, not all the time! The excessive redo, contrary to what people believe, is not always but for the first time only. And checkpoint freeze(which Arpit said) won't stop the behavior of fractured block. Its simply done to be told next time that from where the recovery should be started in the form of the application of transactional logs.

                  HTH
                  Aman....
                  • 6. Re: doubts on hot backup
                    Deepak_DBA
                    hi Aman,,


                    thnx for the info...


                    th
                    Deepak
                    • 7. Re: doubts on hot backup
                      Hemant K Chitale
                      You will have to expand your Recovery !!

                      Say :
                      1. At 10:00 ALTER DATABASE BEGIN BACKUP
                      2. At 10:05 OS (user managed , hot ) backup begins
                      3. At 11:35 OS (user managed , hot ) backup completes
                      4. At 11:36 SWITCH LOGFILE issued by backup script
                      5. At 12:05 OS backup of ArchiveLogs completes
                      6. At 18:45 DBA remembers and issues an END BACKUP

                      If you were to attempt to RESTORE and RECOVER that backup any time later, you CANNOT Recovery to 11:36. You will have to have all the ArchiveLogs upto 18:45 in your backup and you will need to RECOVER to 18:45 or later !
                      Since your backup script is likely to have backed up archivelogs till 12:05 only, if you attempt a restore and recovery later, you will find that this backupset is missing archivelogs ! Oracle will need all the archivelogs till 18:45 but those from 12:05 to 18:45 are not likely in this backup -- they might be in the next day's backup (if it does backup all archivelogs)

                      Bottom Line : The database doesn't suffer much (except for slightly more Redo). The DBA suffers.

                      Hemant K Chitale
                      http://hemantoracledba.blogspot.com
                      • 8. Re: doubts on hot backup
                        VijayaraghavanKrishnan
                        Hi,

                        That means, The datafile headers are not updated until the 'alter tablespace end backup' command is issued but the data will be written to the tablespace?

                        thanks AMAN & Deepak....



                        Thanks,
                        Vijayaraghavan K

                        Edited by: Vijayaraghavan Krishnan on Feb 11, 2010 12:22 PM
                        • 9. Re: doubts on hot backup
                          P.Forstmann
                          If instance crashes in backup mode, you will have some problems:
                          SQL>
                          SQL> alter database begin backup;
                          
                          Database altered.
                          
                          SQL> shutdown immediate;
                          ORA-01149: cannot shutdown - file 1 has online backup set
                          ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'
                          SQL> shutdown abort
                          ORACLE instance shut down.
                          SQL> startup
                          ORACLE instance started.
                          
                          Total System Global Area  805306368 bytes
                          Fixed Size                  1289996 bytes
                          Variable Size             213909748 bytes
                          Database Buffers          587202560 bytes
                          Redo Buffers                2904064 bytes
                          Database mounted.
                          ORA-01113: file 1 needs media recovery
                          ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'
                          
                          
                          SQL> recover database until cancel;
                          ORA-00279: change 3183957 generated at 02/11/2010 07:52:19 needed for thread 1
                          ORA-00289: suggestion :
                          C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_02_11\O1_MF_1_53_%
                          
                          U_.ARC
                          ORA-00280: change 3183957 for thread 1 is in sequence #53
                          
                          
                          Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                          auto
                          ORA-00308: cannot open archived log
                          'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_02_11\O1_MF_1_53_
                          
                          %U_.ARC'
                          ORA-27041: unable to open file
                          OSD-04002: ouverture impossible du fichier
                          O/S-Error: (OS 2) Le fichier spécifié est introuvable.
                          
                          
                          ORA-00308: cannot open archived log
                          'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_02_11\O1_MF_1_53_
                          
                          %U_.ARC'
                          ORA-27041: unable to open file
                          OSD-04002: ouverture impossible du fichier
                          O/S-Error: (OS 2) Le fichier spécifié est introuvable.
                          
                          
                          ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                          ORA-01195: online backup of file 1 needs more recovery to be consistent
                          ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'
                          
                          
                          SQL> alter database end backup;
                          
                          Database altered.
                          
                          SQL> alter database open;
                          alter database open
                          *
                          ERROR at line 1:
                          ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
                          
                          
                          SQL> alter database open noresetlogs;
                          
                          Database altered.
                          
                          SQL>
                          • 10. Re: doubts on hot backup
                            Aman....
                            Vijayaraghavan Krishnan wrote:
                            Hi,

                            That means, The datafile headers are not updated until the 'alter tablespace end backup' command is issued but the data will be written to the tablespace?
                            Yep and that's why its called hot backup!

                            Aman....
                            • 11. Re: doubts on hot backup
                              arpit sinha
                              Thanx Aman.... Sir

                              For information

                              A R P I T S I N H A
                              • 12. Re: doubts on hot backup
                                Hemant K Chitale
                                If there is a shutdown abort / instance crash (when in BACKUP mode) but no loss of datafiles and redo log files :

                                In 10g, you can issue an "ALTER DATABASE END BACKUP;" after a STARTUP MOUNT.
                                In 9i, you would have to issue an "ALTER DATABASE DATAFILE n END BACKUP;" for each datafile.

                                That way you do not need to issue the "recover database until cancel;"


                                Hemant K Chitale
                                http://hemantoracldba.blogspot.com
                                • 13. Re: doubts on hot backup
                                  poorna
                                  Hi All,

                                  Thanks for all your valuable quick replays .

                                  Here one more doubt when i start taking hot backup and issued "alter database begin backup" at 2:00 am and copied 4 datafiles at the same time some transactions are going on on datafile 3 and after some time i am copying datafile 6 .But unfortunately the datafile 3 has lost at 3:00 am and still not issue "alter database end backup" then how can i recover the datafile 3 with the transactions happnen between 2:00 am to 2:55 am .And what happen if i issue "alter database endbackup" after copying datafile 6.

                                  Thanks & Regards,
                                  Poorna Prasad.
                                  • 14. Re: doubts on hot backup
                                    Hemant K Chitale
                                    You would restore datafile 3 from a previous backup and recover upto the point when you issued the END BACKUP -- ie 6pm. A datafile recovery has to be consistent with the whole database. So it has to be a complete recovery but being done for only 1 datafile.


                                    Hemant K Chitale
                                    1 2 Previous Next