Flashback database/table to a specified time or SCN

Firstly you must enable flashback features in mount mode. You can check url https://www.roamway.com/2792.html for details.

Example: Flashback database to specified time

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0155E+10 bytes
Fixed Size 2262008 bytes
Variable Size 7818185736 bytes
Database Buffers 2315255808 bytes
Redo Buffers 19296256 bytes
Database mounted.

SQL> flashback database to timestamp to_timestamp(‘2019-11-14 18:14:00′,’yyyy-mm-dd hh24:mi:ss’);
Flashback complete.

SQL> alter database open;
Database altered.

Related flashback Logs

flashback database to timestamp to_timestamp(‘2019-11-14 18:15:00′,’yyyy-mm-dd hh24:mi:ss’)
Flashback Restore Start
Fri Dec 06 14:03:39 2019
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 40 slaves
Flashback Media Recovery Log /home/oracle/archivelog/thread_2_seq_3212.9393.1024328389
Flashback Media Recovery Log /home/oracle/archivelog/thread_1_seq_3166.9382.1024318427
Flashback Media Recovery Log /home/oracle/archivelog/thread_1_seq_3167.10153.1024328285
Fri Dec 06 14:04:37 2019
Flashback Media Recovery Log /home/oracle/archivelog/thread_1_seq_3168.9405.1024344047
Fri Dec 06 14:05:53 2019
Flashback Media Recovery Log /home/oracle/archivelog/thread_2_seq_3213.9377.1024339487
Fri Dec 06 14:07:43 2019
Incomplete Recovery applied until change 5984486155041 time 11/14/2019 18:15:01
Fri Dec 06 14:07:44 2019
Flashback Media Recovery Complete
Completed: flashback database to timestamp to_timestamp(‘2019-11-14 18:15:00′,’yyyy-mm-dd hh24:mi:ss’)

Example: Flashback database to specified SCN

We can also flashback database to a specified SCN. But SCN must be converted from a spedifice tome.
For example convert “2019-12-06 10:20:00” to it’s SCN.

SQL> set line 500;
SQL> select timestamp_to_scn(to_timestamp(‘2019-12-06 10:20:00′,’YYYY-MM-DD HH24:MI:SS’)) from dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP(‘2019-12-0610:20:00′,’YYYY-MM-DDHH24:MI:SS’))
5.9865E+12
SQL> set numwidth 20;
SQL> select timestamp_to_scn(to_timestamp(‘2019-12-06 10:20:00′,’YYYY-MM-DD HH24:MI:SS’)) from dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP(‘2019-12-0610:20:00′,’YYYY-MM-DDHH24:MI:SS’))
5986520769578

So, we execute the following command to flashback database.

SQL> flashback database to scn 5986520769578;
flashback database to scn 5986520769578
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 4394725 to SCN 5986520769578
ORA-38761: redo log sequence 114 in thread 1, incarnation 2 could not be accessed

You can flashback database by RMAN if you encountered the above error.

RMAN> flashback database to scn 5986520769578;
Starting flashback at 2019:12:06 11:27:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5860 device type=DISK

Example: Flashback a table to specified time

Start feature of “row movement”
SQL>ALTER TABLE YOUR_TABLENAME ENABLE ROW MOVEMENT;

Flashback table to a specified time
SQL>FLASHBACK TABLE YOUR_TABLENAME TO TIMESTAMP
TO_TIMESTAMP(‘2018-04-23 16:06:00′,’yyyy-mm-dd hh24:mi:ss’);

But you must ensure that the database can be read and written
Otherwise you’ll encounter the following error.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Stop feature of “row movement”
SQL>ALTER TABLE YOUR_TABLENAME DISABLE ROW MOVEMENT;

Attachment

Check earliest SCN that database can flashback

SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
5984398987742 09-NOV-19 57600 188668223488 0

Check details of flashback logfile

SQL> select * from v$flashback_database_stat;
BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
07-DEC-19 08-DEC-19 8192 9342976000 0 0

Check the details of flashback logs

SQL> select * from v$flashback_database_logfile;
………………
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIM TYPE
/path../flashback/o1_mf_7t11_.flb 28 1 28 16777216000 0 10-NOV-19 NORMAL
/path../flashback/o1_mf_t2o3_.flb 29 1 29 33554432000 0 11-NOV-19 NORMAL
/path../flashback/o1_mf_v7ml_.flb 30 1 30 42949672960 0 12-NOV-19 NORMAL
………………