ORA-38729: Not enough flashback database log data to do FLASHBACK.

I have faced an interesting issue a month before. When I try to perform a flashback database, I was getting the below error message.

I had enough flashback logs but still I have been thrown the below error message. 

Error message

RMAN> FLASHBACK DATABASE TO SCN 69979801;

Starting flashback at 10-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1252 instance=RMAN> FLASHBACK DATABASE TO SCN 69979801;

Starting flashback at 10-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1252 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1377 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1502 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1628 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=1753 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=1876 instance=BHU_1 device type=DISK


starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 11/10/2011 16:42:04
ORA-38729: Not enough flashback database log data to do FLASHBACK.

RMAN> 

BELOW ERROR MESSAGE FOR THE SAME ISSUE
In the last error you had, the problem was that the archive logs weren't available, so you got an error

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 39221435 to SCN 39240345
ORA-38761: redo log sequence 56 in thread 1, incarnation 7 could not be accessed 

This is slightly different than if the flashback log is not available
ORA-38729: Not enough flashback database log data to do FLASHBACK.

My environment è we had 2 node RAC database. When I try to flashback the database but I got the above error.

Did the flashback is enabled for the database

SQL> select flashback_on from gv$database;

FLASHBACK_ON
------------------
YES

YES

What is the size & retention set for the flashback?

SQL> show parameter db_rec

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 270G
db_recycle_cache_size big integer 0

SQL> show parameter db_flash

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 1440  à 1 day

When I try to check the flashback log, do I have enough flashback logs to perform a flashback?

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
84854010 09-NOV-11 1440 3698974720 935731200

We can able to check, till the second of last flashback log that is present in the flashback log.

SQL> select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss') from v$flashback_database_log;

TO_CHAR(OLDEST_FL
-----------------
11/09/11 22:00:12

What is the size occupied by the flashback logs

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)
---------------------- -----------------------
3709 276480

Then I come to know that flashback database command requires both flashback logs and archive logs for period which we mention in the “FLASHBACK DATABASE SCN/TIME/ SEQUENCE XXXXXXXXXX”.

DIFFERENT OPTION OF PERFORMING FLASHBACK

SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 2/24);

SQL> FLASHBACK DATABASE TO SCN n;

SQL> FLASHBACK DATABASE TO SEQUENCE=M THREAD=N;

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2012-01-25 13:54:00';

SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2012-01-25 13:54:00', 'YYYY-MM-DD HH24:MI:SS');

Flashback database use the flashback logs to bring the database files to a SCN before the time/SCN you want to flashback. This is an approximate point based on the flashback snapshots taken to the flashback logs. Oracle then uses the archives to apply redo to get to the exact SCN or time you want to flashback.

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#CFHCEIHB

"When you use Flashback Database to rewind a database to a past target time, the command determines which blocks changed after the target time and restores them from the flashback logs. The database restores the version of each block that is immediately before the target time. The database then uses redo logs to reapply changes that were made after these blocks were written to the flashback logs.

Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs. For example, if the flashback retention target is 1 week, then you must ensure that online and archived redo logs that contain all changes for the past week are accessible. In practice, redo logs are typically needed much longer than the flashback retention target to support point-in-time recovery."

hope this helps you & happy learning