Restore until time rman script

If flashback Database is not available (Flashback off or timeframe too large), you will have to restore your Oracle database with Recovery Manager (RMAN).

################################################
#
# Compatible: Oracle 8i 9i 10g 11g 12c
#
################################################
#
# Restore a database until time...
# Database must be in "startup mount" mode
#
################################################

. oraenv 
==> my_ORACLE_SID

# Shutdown the database and put on mounted state (cluster_database = FALSE)
sqlplus / as sysdba
select INSTANCE_NAME from v$instance;   # verify you are on the right Oracle instance
shutdown abort
startup mount
exit

#
connect target /
connect catalog rman/***@rman   # if you use a catalog

RUN
{
  # if restoring from tape (else Allocate type disk):
  ALLOCATE CHANNEL chan1 TYPE 'SBT_TAPE';
  ALLOCATE CHANNEL chan2 TYPE 'SBT_TAPE';
  # SEND 'NSR_ENV=(NSR_SERVER=xxxxx, NSR_DATA_VOLUME_POOL=xxxxx)';

  set until time= "to_date('02/09/2018 17:00:00','dd/mm/yyyy hh24:mi:ss')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  RELEASE CHANNEL chan1;
}

When the restore is finished, in SQL*Plus:
alter database open resetlogs;

To monitor RMAN restore progress:

 -- Progress percentage:
select
sid,
start_time,
totalwork
sofar,
(sofar/totalwork) * 100 pct_done
from
v$session_longops
where
totalwork > sofar
AND
opname NOT LIKE '%aggregate%'
AND
opname like 'RMAN%';

-- Waits:
select
client_info,
event,
seconds_in_wait
from
v$process p,
v$session s
where
p.addr = s.paddr
and
client_info like 'rman channel=%';

-- More infos:
select 
to_char(sysdate,'DD-MON HH24:MI:SS') Collection_DateStamp, sid, opname, target, sofar, totalwork,
    units, to_char(start_time,'HH24:MI:SS') StartTime,
    time_remaining, message, username
    from
 v$session_longops
    where
 sofar != totalwork
   order by
 start_time;
 

Author: Vincent Fenoll – Oracle DBA Montreal