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
This was really helpful for me today. Saved me a lot of time getting a restore initiated and later monitoring it.
Thanks for writing this up.