point in time recovery failed
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: point in time recovery failed

  1. #1
    Join Date
    Oct 2000
    Posts
    48
    Yesterday I tested a point in time recovery by doing the following:

    --Made cold backup of the database at 10:50am
    --Opened db and immediately turned on archive logging
    --Created table test1 at 3:59pm and inserted rows
    --Dropped test1 at 4:05pm
    --Shutdown immediate
    --Restored all datafiles
    --Startup mount
    --recover database until time '2001-06-06:16:02:00'
    --Oracle then applied 4 logs and said Media Recovery Complete, even though there were actually a total of 13 arch logs from time archiving was turned on.
    --Alter database open resetlogs;

    The last archive applied had a timestamp of 10:55am, but I wanted to recover until 4:02pm. Needless to say the test1 table was not there. Why did it stop applying logs so soon???

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by lake612
    Yesterday I tested a point in time recovery by doing the following:

    --Made cold backup of the database at 10:50am
    --Opened db and immediately turned on archive logging
    Here's where you made your mistake. You must put the database in archivelog mode, and THEN take a cold backup of the system. By the time you turned archivelog on, your backup was already too old.
    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."

  3. #3
    Join Date
    Oct 2000
    Posts
    48
    I had a feeling somebody was going to say that. Okay, I'll try again taking a cold backup now while the database is already in archive log mode. Then I'll try another PIT recovery using that backup.

    Thanks!

  4. #4
    Join Date
    Jun 2001
    Location
    Belgium
    Posts
    13
    And .. make sure that the controlfile you are using is the good one.


  5. #5
    Join Date
    Oct 2000
    Posts
    48
    Okay, here is what I did today to recover dropped table TEST1:

    --Shutdown db and made cold backup at 10:00am
    --Created table MANDY1 at 10:25am
    --Deleted all rows from table MANDY2 at 10:30am
    --Deleted some rows from table MANDY3 at 10:30am
    --Dropped table MANDY1 at 11:08am
    --shutdown immediate
    --copied over all datafiles (kept same control files)
    --startup mount
    --recover database until time '2001-06-07:11:00:00';
    --alter database open

    Then I checked and MANDY1 did exist, so I thought great it worked. But then I checked MANDY2 and *all* its rows were there (even though I deleted all at 10:30), and *all* of MANDY3 rows were there (even though I deleted some at 10:30).

    Can anybody explain why after the recovery MANDY2 and MANDY3 did not have the correct row counts, even though I recovered to a time past when these tables were last updated??

    As for mvander2's comment about using the "good" controlfile, to which are you referring? The current controlfile of the database just prior to the recovery, or the backed up copy of the controlfile from 10:00am?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Almost, but not quite. You're problem here is:

    Originally posted by lake612
    --recover database until time '2001-06-07:11:00:00';
    --alter database open
    Since you recovered the database until a particular time, you have done an "Incomplete" recovery. By just opening the database, Oracle automatically applied the transactions up to the point in your controlfile which made the database consistent with when you shut it down.

    The key here would have been to:
    alter database open resetlogs

    which would have reset the controlfile to the current point.
    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
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by marist89
    Almost, but not quite. You're problem here is:

    Originally posted by lake612
    --recover database until time '2001-06-07:11:00:00';
    --alter database open
    Since you recovered the database until a particular time, you have done an "Incomplete" recovery. By just opening the database, Oracle automatically applied the transactions up to the point in your controlfile which made the database consistent with when you shut it down.
    I don't think Oracle actualy perfrormed complite recovery because it was not instructed to perform open with restlogs. What I think realy happened is the following:
    Originally posted by lake612
    --Shutdown db and made cold backup at 10:00am
    --Created table MANDY1 at 10:25am
    --Deleted all rows from table MANDY2 at 10:30am
    --Deleted some rows from table MANDY3 at 10:30am
    --Dropped table MANDY1 at 11:08am
    The deletes from tables MANDY2 and MANDY3 were not commited until 11:08am, when implicit commit was fired because of DROP TABLE MANDY1.

    When performing PITR "recover database until time '2001-06-07:11:00:00';" Oracle applied all the changes from archived logs up untill 11:00am, but then rolled back all uncommited transactions. So your deletes from MANDY2 and MANDY3 were actually rolled back as they should be.

    Or am I wrong?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I assumed the deletes were committed when they were deleted. This would make sense, though...
    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."

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jmodic
    I don't think Oracle actualy perfrormed complite recovery because it was not instructed to perform open with restlogs.
    [/B]
    I'd have to check this. However, It's my understanding that if you perform an incomplete recovery and don't open with resetlogs, your database will rollforward to the current time in the controlfile.

    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."

  10. #10
    Join Date
    Oct 2000
    Posts
    48
    Actually, my latest posting was not quite right. I did do an 'alter database open resetlogs;'. It wouldn't let me open it without resetting the logs.

    Now, if those deletes weren't committed than all this would make sense to me. I didn't explicity type COMMIT. Instead, I just "cleanly" exited out of sqlplus. I've always heard that the commit is done when you type EXIT from sqlplus. Is that true? Anyway, I've done many updates, inserts, deletes and almost never type commit, I just exit sqlplus cleanly and the changes have always been there in the past.

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