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.