point in time recovery failed

[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
--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???

[marist89]

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.

[lake612]

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!

[mvander2]

And .. make sure that the controlfile you are using is the good one.

[lake612]

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?

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

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

which would have reset the controlfile to the current point.

[jmodic]

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?

[marist89]

I assumed the deletes were committed when they were deleted. This would make sense, though...

[marist89]

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.

[lake612]

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.