ENABELING FLASHBACK DATABASE ON 19C RAC

Using below mentioned Steps we can ENABLE FLASHBACK as Database Level.

Step1 => Enabling the Fast Recovery Area 

You enable the fast recovery area by setting two initialization parameters.
These parameters enable the fast recovery area with or without having to shut down and restart the database instance.



To enable the fast recovery area:

=> Set the size of the fast recovery area with the parameter DB_RECOVERY_FILE_DEST_SIZE.
=> Set the physical location of the flash recovery files with the parameter DB_RECOVERY_FILE_DEST.


Step2 => Enabling Flashback Database 

To enable flashback logging:

=> Configure the recovery area as described in Step1
=> Ensure the database instance is open or mounted. If the instance is mounted, then the database must be shut down cleanly unless it is a physical standby database.
    Other Oracle Real Application Clusters (Oracle RAC) instances can be in any mode.
=> Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=720 SCOPE=BOTH; # 12 Hours

By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes).

Enable the Flashback Database feature for the whole database:

ALTER DATABASE FLASHBACK ON;



DEMO

Let we start doing on server as below.

=> Check database status as below,

SQL> select log_mode,flashback_on from gv$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO
ARCHIVELOG   NO

=> Set rdb_recovery_file_dest_size and then db_recovery_file_dest

SQL> alter system set db_recovery_file_dest_size=9000M;

System altered.

SQL>  alter system set db_recovery_file_dest='+FRA';

System altered.
=> Check values

SQL> show parameter db_reco

NAME                                 TYPE   VALUE

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

db_recovery_file_dest                string  +FRA

db_recovery_file_dest_size         big integer 9000M

=> Set Flashback_retention as below

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=720;

System altered.

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME                                                           TYPE   VALUE

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

db_flashback_retention_target                   integer               720

=> Enable DATABASE Flashback LOGGING as below

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

=> Check database Logging status

SQL>  select log_mode,flashback_on from gv$database;

LOG_MODE     FLASHBACK_ON

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

ARCHIVELOG   YES

ARCHIVELOG   YES

SQL>
=> Check of any Restore Point we have

SQL> select * from v$restore_point;

no rows selected

SQL>

=> Check FLASHBACK Log contents and status below scripts.

SQL> PROMPT How Far Back Can We Flashback To (Time)?
PROMPT
select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
 from v$flashback_database_log;

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

PROMPT
PROMPT Flashback Area Usage
SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)
 from  v$RECOVERY_FILE_DEST;How Far Back Can We Flashback To (Time)?
SQL> 
SQL>   2  
Oldest Flashback Time
-----------------------------
16-dec-2022 12:29:23

SQL> SQL> 
SQL> How Far Back Can We Flashback To (SCN)?
SQL> 
SQL> SQL> 
       OLDEST_FLASHBACK_SCN
---------------------------
		    3786337

SQL> SQL> 
SQL> Flashback Area Usage
SQL> 
FILE_TYPE		PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	 CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE			       .12			   0		   1	      0
REDO LOG			      8.93			   0		   4	      0
ARCHIVED LOG			      7.18			   0		  14	      0
BACKUP PIECE			     15.49			 .61		  11	      0
IMAGE COPY			      3.73			   0		   1	      0
FLASHBACK LOG			      8.93			   0		   4	      0
FOREIGN ARCHIVED LOG			 0			   0		   0	      0
AUXILIARY DATAFILE COPY 		 0			   0		   0	      0

8 rows selected.

SQL> SQL>   2  

Flashback Location			 Space Allocated (MB) Space Used (MB)
---------------------------------------- -------------------- ---------------
+FRA							 9000		 3996

SQL> 
SQL> 

=> Check Alert Log and Background Process as below.

2022-12-16T12:20:57.818346+05:30
ALTER SYSTEM SET db_recovery_file_dest='+FRA' SCOPE=BOTH;
2022-12-16T12:21:24.420699+05:30
ALTER SYSTEM SET db_flashback_retention_target=720 SCOPE=BOTH;
2022-12-16T12:28:23.617618+05:30
ALTER DATABASE FLASHBACK ON
2022-12-16T12:28:24.166583+05:30
Starting background process RVWR
2022-12-16T12:28:24.177258+05:30
RVWR started with pid=64, OS id=16598
2022-12-16T12:28:44.029130+05:30
Already allocated 15933248 bytes in shared pool for flashback generation buffer.
2022-12-16T12:29:23.854734+05:30
Flashback Database Enabled at SCN 3786338
Completed: ALTER DATABASE FLASHBACK ON

Our Rac DB is now configured to Use FLASHBACK feature .