time-based & change-based recovery

Oracle 9i
i was reading abt these methods of recovery. In classrooms, the lecturer always simulate this sort of recovery by noticing the time (for time-based recovery), or the log seq# (for changed-based recovery). In reality, there will always be careless users who deleted a record they shldnt and now wanted those back. Eg, a programmer accidentally deletes a program source code which is located in PGMR tablespace, or a user deletes records from the HR tablespace Q1: How do i find out which datafile/datafiles stored the objects that i need to recover knowing only the tablespace? Q2: How do i find out what time the transaction occurs (usually users dont notice the time)? or How do i find out which SCN the transaction occurs? Any suggestions on how to go abt time-based, change-based recovery and any experience to share is very much welcome Background: Oracle 9.2.0 running on win2k

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you know the tablespace name then the datafiles associated with that tablespace are in the DBA_DATAFILES view. The SQL is “seelct file_name from dba_datafiles where tablespace_name = upper(‘tablespace’);”.

Recovery is almost always the same thing. You have to restore the database from a past backup and then roll the entire thing forward to the point in time you are interested in. Actual datetime is meaningless to teh database it is always talking in terms of SCN’s and all of the SCN’s across all of the datafiles & control files MUST match. But since you specified that your using Oracle 9i or better, read up on LogMiner. You should be able to find the offending statement and the undo command to recover your missing data/program. If you can’t then the possibility of your doing a point in time recovery are dicy at best.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Structsound
    If you are using an undo tablespace and a developer deletes data, use flashback query to retrieve the missing data. Otherwise a time-based recovery to another location is done and the data retrieved and placed in production (either through links or export/import). The time-based recovery can be via sequence#, SCN or set until time. Of course if you overshoot the time, you have to do the recovery again.
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: