untilClause

Syntax

untilClause::=

Description of the illustration untilclause.gif

Purpose

A subclause that specifies an upper limit by time, SCN, restore point or log sequence number for various RMAN operations.

See Also:

Oracle Database Backup and Recovery Basics to learn how to set the date format used by RMAN

Restrictions and Usage Notes

When specifying dates in RMAN commands, the date string must be either:
- A literal string whose format matches the NLS_DATE_FORMAT setting.
- A SQL expression of type DATE, for example, 'SYSDATE-10' or "TO_DATE('01/30/1997', 'MM/DD/YYYY')". Note that the second example includes its own date format mask and so is independent of the current NLS_DATE_FORMAT setting.
Following are examples of typical date settings in RMAN commands:
```
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-31' UNTIL TIME 'SYSDATE-14';
RESTORE DATABASE UNTIL TIME "TO_DATE('09/20/00','MM/DD/YY')";
```
SET UNTIL RESTORE POINT can only be used when the database is mounted, because the defined restore points are recorded in the control file. For example, you cannot use SET UNTIL RESTORE POINT to specify the target point in time for a RESTORE CONTROLFILE operation.

Keywords and Parameters

Syntax Element	Description
`UNTIL` `SCN` `=` `integer`	Specifies an SCN as an upper limit. RMAN selects only files that can be used to restore or recover up to but not including the specified SCN. For example, `RESTORE` `DATABASE` `UNTIL` `SCN` `1000` chooses only backups that could be used to recover to SCN 1000.
`UNTIL` `RESTORE` `POINT` `restore_point_name`	Specifies a restore point, so that the SCN at which the restore point was created is the upper limit. RMAN selects only files that can be used to restore or recover up to but not including the corresponding SCN. Note: The database must be mounted when using `SET` `UNTIL` `RESTORE` `POINT`.
`UNTIL` `SEQUENCE` `=` `integer` `THREAD` `=` `integer`	Specifies a redo log sequence number and thread as an upper limit. RMAN selects only files that can be used to restore or recover up to but not including the specified sequence number. For example, `REPORT` `OBSOLETE` `UNTIL` `SEQUENCE` `8000` `THREAD` `1` reports only backups that could be used to recover through log sequence 7999.
`UNTIL` `TIME` `=` `'date_string'`	Specifies a time as an upper limit. RMAN selects only files that can be used to restore and recover up to but not including the specified time. For example, `LIST` `BACKUP` `UNTIL` `TIME` `'SYSDATE-7'` lists all backups that could be used to recover to a point one week ago.

Examples

Performing Incomplete Recovery Until a Log Sequence Number: Example This example assumes that log sequence 1234 was lost due to a disk failure and the database needs to be recovered by using available archived redo logs.

RUN
{

SET UNTIL SEQUENCE 1234 THREAD 1;
  RESTORE CONTROLFILE ;
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;  # recovers through log 1233
  ALTER DATABASE OPEN RESETLOGS;
}

Performing Incomplete Recovery to a Specified SCN: Example This example (which assumes a mounted database) recovers the database until a specified SCN:

RUN
{
  ALLOCATE CHANNEL ch1 TYPE sbt;
  RESTORE DATABASE;
  RECOVER DATABASE UNTIL SCN 1000;  # recovers through SCN 999
  ALTER DATABASE OPEN RESETLOGS;
}

Performing Incomplete Recovery to a Restore Point: Example This example (which assumes that the database starts out not mounted) recovers the database until a specified restore point:

STARTUP MOUNT;
RUN
{
  SET UNTIL RESTORE POINT 'before_batch';
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}

Reporting Obsolete Backups: Example This example assumes that you want to be able to recover to any point within the last week. It considers as obsolete all backups that could be used to recover the database to a point one week ago:

REPORT OBSOLETE UNTIL TIME 'SYSDATE-7';