RE: Recovery until point in time...

From: Elliott, Patrick <Patrick.Elliott_at_bestbuy.com>
Date: Wed, 5 Jul 2000 13:42:03 -0500
Message-Id: <10549.111254@fatcity.com>

Here are the instructions from the manual for 8i: Performing Time-Based Recovery
This section describes how to perform the time-based media recovery procedure in these stages:

Back up the database as a precaution and correct any media failures.

	Restore backup control files (if necessary) and backup datafiles. 
	Perform media recovery on the restored backup using the RECOVER

DATABASE statement with the UNTIL TIME option. Note: If you are performing time-based, incomplete recovery using a backup control file and have read-only tablespaces, contact Oracle Support before attempting this procedure.

To prepare for time-based recovery:
Follow the same preparation procedure described in the section Performing Cancel-Based Recovery
To restore the files necessary for time-based recovery and bring them online:

If the current control files do not match the physical structure of the database at the intended time of recovery, restore a backup control file that reflects the database's physical file structure at the point at which incomplete media recovery should finish. To determine which control file backup to use:
* Review the list of files that corresponds to the current control file and each control file backup to determine the correct control file to use.
* If necessary, replace all current control files of the database with the correct control file backup.
* Alternatively, create a new control file to replace the missing one.

Note: If a database control file cannot function or be replaced with a control file backup, take it out of the CONTROL_FILES parameter list in the parameter file associated with the database.

Restore backups of all the datafiles of the database. All backups used to replace existing datafiles must have been taken before the intended time of recovery. For example, if you intend to recover to January 2 at 2:00 p.m., then restore all datafiles with backups completed before this time. Follow these guidelines:

If... 	Then... 	
You do not have a backup of a datafile 	Create an empty replacement file,
which can be recovered. 	
A datafile was added after the intended time of recovery 	Do not

restore a backup of this file, since it will no longer be used for the database after recovery completes.
The hardware problem causing the failure has been solved and all datafiles can be restored to their default locations Restore the files and skip Step 5 <performi.htm> of this procedure. A hardware problem persists Restore damaged datafiles to an alternative storage device.

Note: Files in read-only tablespaces should be offline if you are using a control file backup. Otherwise, the recovery will try to update the headers of the read-only files.

Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

% sqlplus sys/change_on_install_at_prod1

	Start a new instance and mount the database: 
		STARTUP MOUNT
		 
	If one or more damaged datafiles were restored to alternative

locations in Step 2 <performi.htm>, indicate the new locations of these files to the control file of the associated database. For example, enter:

ALTER DATABASE RENAME FILE "/oracle/dbs/df2.f" TO "/oracle/newloc/df2.f";

	Obtain the names of all datafiles requiring recovery by: 
*	Checking the list of datafiles that normally accompanies the control
file being used. 
*	Querying the V$DATAFILE view. 
	Make sure that all datafiles of the database are online. All

datafiles of the database must be online unless an offline tablespace was taken offline normally. For example, to guarantee that a datafile named user1 (a fully specified filename) is online, enter the following statement:

ALTER DATABASE DATAFILE 'users1' ONLINE;

If a backup of the control file is being used with this incomplete recovery (that is, a control file backup or re-created control file was restored), indicate this in the dialog box or command used to start recovery. If a specified datafile is already online, Oracle ignores the statement.
To perform time-based recovery:

Issue the RECOVER DATABASE UNTIL TIME statement to begin time-based recovery. The time is always specified using the following format, delimited by single quotation marks: 'YYYY-MM-DD:HH24:MI:SS'. The following statement recovers the database up to a specified time using a control file backup:

RECOVER DATABASE UNTIL TIME '1992-12-31:12:47:30' USING BACKUP CONTROLFILE Apply the necessary redo log files to reconstruct the restored datafiles. Unless the application of files is automated, Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST and requests you to stop or proceed with applying the log file. If the control file is a backup, you must supply names of online logs.

Apply redo log files until the last required redo log file has been applied to the restored datafiles. Oracle automatically terminates the recovery when it reaches the correct time, and returns a message indicating whether recovery is successful.

> -----Original Message-----
> From: Rocky Welch [SMTP:rockyw_99_at_yahoo.com]
> Sent: Wednesday, July 05, 2000 1:46 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Recovery until point in time...
>
> Hi Gang,
> Is it possible to recover a database from cold backup using archived logs
> that were generated after the cold backup? In other words, applying logs
> that were generated on Friday to a cold backup that was taken on Thursday
> by manually telling the database the name of the log to use to recover to
> a time on Friday <Whew, that hurt to type in!>?
>
> TIA,
>
> =====
> Rocky Welch
> Oracle DBA Consultant
>
> rockyw_99_at_yahoo.com
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages & get email alerts with Yahoo! Messenger.
> http://im.yahoo.com/
> --
> Author: Rocky Welch
> INET: rockyw_99_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Jul 05 2000 - 13:42:03 CDT