recover from the last full hot database backup
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: recover from the last full hot database backup

  1. #1
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    56

    recover from the last full hot database backup

    Hi Guys,

    I just want to ascertain this fact since I still read many contradictories floating around. Say, I had a full hot database backup yesterday and today the database node has exploded for some reason. I have the last full HOT database backup and all the archive logs generated since. The control files were backed up along in the full backup.
    The question is : will I be able to recover the database completely without resetting logs?

    Some said we can only recover completely from a full COLD database backup. Well, I tried the scenario and found that I can recover from the HOT one without having any archived logs generated BEFORE the full backup.

    Can you guys shed some light here?

    Thanks.

    Ric
    Oracle freak & about-to-be DB2 freak
    Singapore

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    you must open resetlogs if you want to recover to point in time past the time the control file was last updated. If you simply did RECOVER database you would only recover to the time in the control file (SCN)

    If you RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE you will roll forward to the point where where you supply the last archive log.

    The control file contains the SCN and that is used as the benchmark for recovery. By saying RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE you tell oracle to ignore the SCN in the control file and keep rolling forward anyway.

    Since UNTIL CANCEL is incomplete media recovery you need to open with resetlogs. A tad confusing as you will be completely recovered by Oracle says that recovery with CANCEL, SCN, or TIME are types of incomplete media recovery.
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    56
    May be I was too extreme on this scenario. How about if the controlfiles still exist and they don't need to be replaced by the backup?
    Does that mean I can recover back using the last full HOT database backup? or Do I still need to refer back to a full COLD database backup?

    Please advise.

    Thanks.

    Ric
    Oracle freak & about-to-be DB2 freak
    Singapore

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    what's the big deal about resetting the logs?

    to answer your question, time is relative to the SCN in the control file, you can do complete media recovery up to that scn, if the scn advances give your scenario you will need to do incomplete media recovery
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: recover from the last full hot database backup

    Originally posted by sricky
    Hi Guys,

    I just want to ascertain this fact since I still read many contradictories floating around. Say, I had a full hot database backup yesterday and today the database node has exploded for some reason. I have the last full HOT database backup and all the archive logs generated since. The control files were backed up along in the full backup.
    The question is : will I be able to recover the database completely without resetting logs?


    First of all, you won't be able to recover the database completely. You will lose any data that has been written to the redo logs but has not been archived. You will only be able to recover through the last archived redo log.

    Second, you will be doing a point-in-time recovery which requires you open the database with resetlogs. That's just the way it works.


    Some said we can only recover completely from a full COLD database backup. Well, I tried the scenario and found that I can recover from the HOT one without having any archived logs generated BEFORE the full backup.

    Exactly. That's the whole point of taking a periodic hot backup. Assuming you have a valid hot backup, you only need to recover from that point until "now" during a failure.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by sricky
    May be I was too extreme on this scenario. How about if the controlfiles still exist and they don't need to be replaced by the backup?
    Does that mean I can recover back using the last full HOT database backup? or Do I still need to refer back to a full COLD database backup?
    Well, that's a little different story. If you have your current controlfiles AND your redo logs, than you can recover right up to the point of the crash. Hot backup, cold backup, it doesn't really matter. You will be restoring a file to a specific point in time and rolling it forward with the archived redo logs and the redo logs.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    56
    Thanks guys, you all have been most helpful.

    Ric
    Oracle freak & about-to-be DB2 freak
    Singapore

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width