This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Feb 11, 2010 7:32 AM by UweHesse RSS

doubts on hot backup

poorna Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    665239 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    hi Aman,,


    thnx for the info...


    th
    Deepak
  • 7. Re: doubts on hot backup
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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
    665239 Explorer
    Currently Being Moderated
    Thanx Aman.... Sir

    For information

    A R P I T S I N H A
  • 12. Re: doubts on hot backup
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

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