RMAN can perform recovery of the whole database to a specified past time, SCN, or log sequence number. This type of recovery is sometimes called incomplete recovery because it does not completely use all of the available redo. Incomplete recovery of the whole database is also called database point-in-time recovery (DBPITR).

To recover the database until a specified time, SCN, or log sequence number:

Note:

·      The database must be closed to perform database point-in-time recovery.

·      If you are recovering to a time, then you should set the time format environment variables before invoking RMAN.

·      The following are sample Globalization Support settings

NLS_LANG = american_america.us7ascii

NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"

Step 1 After connecting to the target database and, optionally, the recovery catalog database, ensure that the database is mounted. If the database is open, shut it down and then mount it:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

Step 2  Determine the time, SCN, or log sequence that should end recovery. For example, if you discover that a user accidentally dropped a tablespace at 9:02 a.m., then you can recover to 9 a.m.--just before the drop occurred. You will lose all changes to the database made after that time.

You can also examine the alert.log to find the SCN of an event and recover to a prior SCN.

Alternatively, you can determine the log sequence number that contains the recovery termination SCN, and then recover through that log. For example, query V$LOG_HISTORY to view the logs that you have archived.

Note: If you want to know drop date and time detail, u must to enable audit trail feature.

Step 3  Perform the following operations within a RUN command:

The following example performs an incomplete recovery until November 15 at 9 a.m.

RUN

{

  SET UNTIL TIME 'Nov 15 2002 09:00:00';

  # SET UNTIL SCN 1000;       # alternatively, specify SCN

  # SET UNTIL SEQUENCE 9923; # alternatively, specify log sequence number

  RESTORE DATABASE;

  RECOVER DATABASE;

}

Step 4 If recovery was successful, then open the database and reset the online logs:

ALTER DATABASE OPEN RESETLOGS;