Flashback Database -- 2 : Flashback Requires Redo (ArchiveLog)

Although Flashback Logs support the ability to execute a FLASHBACK DATABASE command, the actual Flashback also requires Redo to be applied.  This is because the Flashback resets the images of blocks but doesn't guarantee that all transactions are reset to the same point in time (any one block can contain one or more active, uncommitted transactions, and there can be multiple blocks with active transactions at any point in time).  Therefore, since Oracle must revert the database to a consistent image, it needs to be able to apply redo as well (just as it would do for a roll-forward recovery from a backup).

Here's a quick demo of what happens if the redo is not available.

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select sysdate, l.oldest_flashback_scn, l.oldest_flashback_time
  2  from v$flashback_database_log l;

SYSDATE            OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T
------------------ -------------------- ------------------
14-NOV-16 22:51:37              7246633 14-NOV-16 22:39:43

SQL> 

sh-4.1$ pwd
/u02/FRA/ORCL/archivelog/2016_11_14
sh-4.1$ date
Mon Nov 14 22:52:29 SGT 2016
sh-4.1$ rm *
sh-4.1$ 

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

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             750781320 bytes
Database Buffers          310378496 bytes
Redo Buffers                5517312 bytes
Database mounted.

SQL> flashback database to timestamp to_date('14-NOV-16 22:45:00','DD-MON-RR HH24:MI:SS');
flashback database to timestamp to_date('14-NOV-16 22:45:00','DD-MON-RR HH24:MI:SS')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 7246634 to SCN 7269074
ORA-38761: redo log sequence 70 in thread 1, incarnation 5 could not be
accessed


SQL> 
SQL> l
  1  select sequence#, first_change#, first_time
  2  from v$archived_log
  3  where resetlogs_time=(select resetlogs_time from v$database)
  4  and sequence# between 60 and 81
  5* order by 1
SQL> /

 SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ------------- ------------------
        60       7245238 14-NOV-16 22:27:35
        61       7248965 14-NOV-16 22:40:46
        62       7250433 14-NOV-16 22:40:52
        63       7251817 14-NOV-16 22:41:04
        64       7253189 14-NOV-16 22:41:20
        65       7254583 14-NOV-16 22:41:31
        66       7255942 14-NOV-16 22:41:44
        67       7257317 14-NOV-16 22:41:59
        68       7258689 14-NOV-16 22:42:10
        69       7260094 14-NOV-16 22:42:15
        70       7261397 14-NOV-16 22:42:22
        71       7262843 14-NOV-16 22:42:28
        72       7264269 14-NOV-16 22:42:32
        73       7265697 14-NOV-16 22:42:37
        74       7267121 14-NOV-16 22:42:43
        75       7269075 14-NOV-16 22:48:05
        76       7270476 14-NOV-16 22:48:11
        77       7271926 14-NOV-16 22:48:17
        78       7273370 14-NOV-16 22:48:23
        79       7274759 14-NOV-16 22:48:32
        80       7276159 14-NOV-16 22:48:39
        81       7277470 14-NOV-16 22:48:43

22 rows selected.

SQL> 

Note how the error message states that Redo(Archive)Log Sequence#70 is required but provides a range of SCNs that span Sequence#60 to Sequence#74 !

Bottom Line : Flashback Logs alone aren't adequate to Flashback database.  You also need the corresponding Redo.

Just to confirm that I can continue with the current (non-Flashbacked Database) state (in spite of the failed Flashback)  :

SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             750781320 bytes
Database Buffers          310378496 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    89
Next log sequence to archive  90
Current log sequence          90
SQL> select current_scn from v$database; 

CURRENT_SCN
-----------
    7289329

SQL> 

.Bottom Line : *Before* you attempt a FLASHBACK DATABASE to the OLDEST_FLASHBACK_TIME (or SCN) from V$FLASHBACK_DATABASE_LOG, ensure that you *do* have the "nearby"  Archive/Redo Logs. !
.
.
.