Re: Incomplete recovery question

Maureen,

I'm just trying to make sense of what you are saying:

• your goal is to find when a database change was made and who made it.
• you can't use Log Miner because it is a chained row.
• you are attempting to recover the database to a point in time.

I'm just offering a couple of suggestions in the spirit of helping you achieve your goals. If I've misunderstood your situation, feel free to ignore my ideas.

1. It seems to me that recovering the database will only confirm your date theory, but not give you an exact time (not without a lot of trial-and-error). It won't tell you who made the change.
2. I am not familiar with a Log Miner restriction related to chained rows. To me it seems that Log Miner is more likely to provide the information you seek than is database recovery. I agree that Log Miner often isn't easy to use, but it does record exact times and users. Maybe if you explained what you perceive as a Log Miner restriction related to chained rows, someone on this list could either confirm your statement or maybe offer a way around the restriction.

Dennis Williams

On 11/7/07, Shamsudeen, Riyaj <RS2273_at_att.com> wrote:
>
> Maureen
>
>
>
> ORA-1152 suggests that some files are not from sufficiently
> old backup or SCN you are trying to use before the datafile SCN. Did you
> restore the control file also and if yes, what is the date associated with
> that control file backup ?
>
> If it is due to recent version of controlfile, then you should
> be able to restore the control file alone as of July 6th (which shouldn't
> have these three new datafiles), then do 'recover database using backup
> controlfile until change xxxxx' and open the database with resetlogs option.
> Also query v$datafile and v$datafile_header to see if you see any datafile
> with higher SCN then what you supply in the recover command.
>
> If you don't have or can't get controlfile, just get
> controlfile script from production, remove these new files, recreate control
> file in test recovery db and then recover as above.
>
>
>
> Thanks
>
> Riyaj
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Maureen English
> Sent: Wednesday, November 07, 2007 1:10 PM
> To: oracle-l_at_freelists.org
> Subject: Incomplete recovery question
>
>
>
> We've been trying to find information about when a value got
>
> changed in our database, and who changed it. Logminer didn't
>
> help since the change was a chained row. So, we've been
>
> restoring the database from different backups to a point in
>
> time when the change *could* have occurred. Our most recent
>
> attempt to find the change resulted in an ora-01152 error.
>
>
>
> I understand the reason for the error...our restore was to
>
> a point in time (Aug 7th), but three datafiles that were
>
> created after the backup we used (July 6th), were restored
>
> from a backup on Aug 17th. So, these three datafiles have
>
> a date in the future of when we want to recover to.
>
>
>
> I believe that I can just restore the datafiles, and while
>
> I'm still at the point where I received the error, I can
>
> then reissue my 'recover database until change xxxxxxx'
>
> command.
>
>
>
> Does anyone know if this will really work, or am I going
>
> to need to restore the original datafiles yet again? Is
>
> there another option, like taking these three datafiles
>
> offline and then issuing my 'alter database open resetlogs'
>
> command?
>
>
>
> TIA.
>
>
>
> - Maureen
>
> University of Alaska
>
> Fairbanks, AK
>
> --
>
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l