Incomplete Recovery   «Prev 

Performing a time-based recovery

  1. If the database is open, shut it down with IMMEDIATE or the NORMAL option.
  2. Perform a whole closed database backup.
  3. Restore all the datafiles from the most recent backup (not the one you made in Step 2).
  4. Open the database in mount mode.
  5. If you need to restore the archived logs, restore them to the LOG_ARCHIVE_DEST location. If there is not enough disk space available at that location, you can use another location if you inform Oracle by issuing the 
    ALTER SYSTEM ARCHIVE LOG 
START TO <LOCATION>;
    statement.
  6. Recover the database by issuing the RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI;SS'; statement and enter AUTO when you are prompted with the suggestion of which log you need to apply.
  7. To synchronize datafiles with redo logs and control file, open the database using the RESETLOGS option by issuing the ALTER DATABASE OPEN RESETLOGS; statement.
  8. To make sure the database has been recovered to the state you expected, you should check whether you got all the objects back.
  9. Perform a whole closed database backup and notify the user that any data entered after the recovery time should be manually re-entered.

Oracle RMAN Backup and Recovery

How time-based Works

Time-based restore and recovery is commonly used when you know approximately the date and time to which you want to recover your database. For example, you may know approximately the time you want to stop the recovery process but not a particular SCN.
  1. Log sequence–based and cancel-based recovery work well in situations where you have missing or damaged log files. In such scenarios, you can recover only up to your last good archived redo log file.
  2. SCN-based recovery works well if you can pinpoint the SCN at which you want to stop the recovery process. You can retrieve SCN information from views such as V$LOG and V$LOG_HISTORY.
  3. You can also use tools such as LogMiner to retrieve the SCN of a particular SQL statement.
  4. Restore point recoveries work only if you have established restore points. In these situations, you restore and recover up to the SCN associated with the specified restore point.
  5. Tablespace point-in-time recovery is used in situations where you can restore and recover just a few tablespaces.
  6. You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.
  7. Table point-in-time recovery may be appropriate if you accidentally dropped a table or erroneously deleted data.
  8. Flashing back your database works only if you have enabled the flashback database feature. DBAs often use this feature in environments where the database needs to be rolled back to a baseline (like a test environment), or also in production environments to capture the state of the database right before an application upgrade takes place. This allows you to restore the database back to a point in time in the event the upgrade doesn’t go as planned.