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."

How to enable and disable Flashback in oracle database 11g And ORA-38701 Solution

Flashback in Oracle Database

Flashback technology is a set of features in Oracle database that make your work easier to view past states of data or to move your database objects to a previous state without using point in time media recovery.

View past states of data or move database objects to previous state means you have performed some operations like  DML + COMMIT and now you want to rollback that operation, this can be done easily through FLASHBACK technology without using point in time media recovery.

How to enable FLASHBACK in Oracle Database 11G R1 and below versions

For this process database has to be in ARCHIVELOG mode.
 
Flash Recovery Area has to be configured. To configure PFB steps :-

SQL> show parameter db_recovery_file_dest

NAME                                                 TYPE               VALUE
------------------------------------               ---------             -----------------------------
db_recovery_file_dest                         string
db_recovery_file_dest_size                  big integer       0

Set flashback database parameters:-
-----------------------------------------------
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='D:\APP\BACKUP\FLASHBACK' SCOPE=SPFILE;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SCOPE=SPFILE;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
SELECT FLASHBACK_ON FROM V$DATABASE;

select name, time,guarantee_flashback_databse from v$restore_point;
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
 
For a non RAC environment use the following commands to flashback up to  restore point: -

-----------------------------------------------------------------------------------------------------------------

Shut immediate;

startup mount;

flashback database to restore point <restore point name>;

alter database open resetlogs;

For RAC instances use the following commands: -

--------------------------------------------------------------

One one of the nodes run, srvctl stop database -d <database name> -o immediate

Shut immediate;

startup mount;

flashback database to restore point <restore point name>;

alter database open resetlogs;

Shut immediate;

quit

srvctl start database -d <database name>

Run crs_stat -t to confirm that the database is backup okay.

To monitor Flashback logs: -

----------------------------------

select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

Create an Undo Tablespace with enough space to keep data for flashback operations. More often users update the database more space is required.

By default automatic Undo Management is enabled, if not enable it. In 10g release 2 or later default value of UNDO management is AUTO. If you are using lower release then PFB to enable it:-

SQL> alter system set undo_management=auto scope=spfile;

System altered

select flashback_on from v$database;

select name, time,guarantee_flashback_databse from v$restore_point;

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

---------------------------Create Normal Restore Point----------------------------------------

CREATE RESTORE POINT before_upgrade;

--------------------------------------------------------------------------------------------------------

---------------------------Create Guarantee Restore Point------------------------------------

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

---------------------------------------------------------------------------------------------------------

COL NAME FOR A25
COL TIME FOR A35
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

FLASHBACK DATABASE TO RESTORE POINT 'before_upgrade';

FLASHBACK DATABASE TO SCN 202381;

select file_name,file_id from dba_data_files where tablespace_name='TEST';

alter database datafile 5 offline;

ALTER TABLESPACE test flashback off;

recover datafile 5;

alter database datafile 5 online;

To monitor Flashback logs: -

----------------------------------

select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

Find the earliest flashback point: -

------------------------------------------

alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_
-------------------- -----------------
             4804277 24/12/14 14:06:22

To flashback the database to specific time: -

----------------------------------------------------------

flashback database to timestamp to_timestamp('dd/mm/yy HH24:MI:SS', 'DD/MM/YYYY HH24:MI:SS');

To drop a restore point: -

------------------------------

select NAME, SCN, TIME from V$RESTORE_POINT;

DROP RESTORE POINT before_app_upgrade;

To disable flashback: -

----------------------------

alter system set db_recovery_file_dest=" " scope= both sid='*';

-------------------------------------------------------------------------------

SQL> select FLASHBACK_ON from v$database;

Find the earliest flashback point: -

------------------------------------------

alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

To flashback the database to specific time: -

----------------------------------------------------------

flashback database to timestamp to_timestamp('dd/mm/yy HH24:MI:SS', 'DD/MM/YYYY HH24:MI:SS');

To drop a restore point: -

------------------------------

select NAME, SCN, TIME from V$RESTORE_POINT;

DROP RESTORE POINT before_app_upgrade;

To Resolve ORA-38701 Flashback error: -

----------------------------------------------
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

--------------------------------

RESTORE POINT ONLY

SQL> SELECT * FROM V$RESTORE_POINT;

select * from v$restore_point

ORA-38701: Flashback database log 1 seq 1 thread 1: "D:\App\Backup\Flashback\filename.flb"

SQL> SELECT NAME FROM V$RESTORE_POINT;

NAME
-------------------------
BEFORE_UPGRADE

SQL> DROP RESTORE POINT BEFORE_UPGRADE;

Restore point dropped.

SQL> ALTER DATABASE OPEN;

Database altered.