Many times, as a DBA we would be required to create database restore points so that if something messed up in the database be it due to a database upgrade or patching, application upgrade touching the database objects, etc., it would be easy to go back to the time when the operation was started without the need of restoring the database.
To facilitate this easy feature, one would need to enable or create the restore point be it a normal restore point or a guaranteed restore point. I will always prefer the guaranteed restore point if I want to secure the database of unexpected mess as a roll back plan. The flashback logs of a normal restore point can be resued if there is a space pressure in the FRA or it is no longer needed to satisfy the flashback retention target and hence the recovery window might be limited.
One downside of creating restore point is that Oracle logs another type of log file apart from the standard archive logs called the flashback logs. Until version 21c, if we enable restore point or to use flashback feature, Oracle will always use the Fast Recovery Area (FRA) space to create the flashback logs. FRA is a shared space for all the database restore and recovery related files such as archive log, control file, backup piece and also the flashback log.
Below is an example of the FRA contents from a 19c database
You can see with the default settings, both archived logs and flashback log uses the same FRA and when there is a growth in flashback log, there is a possibility of database stalling due to insufficient space when either of the logs fills the FRA.
In Oracle 23c, there are 2 new parameters introduced as below
db_flashback_log_dest
db_flashback_log_dest_size
These 2 parameters define where the flashback logs are stored outside of the FRA making the management of flashback logs easier.
SQL> alter system set db_flashback_log_dest_size=4G scope=both; System altered. SQL> alter system set db_flashback_log_dest='/media/sf_Oracle/Linux-823' scope=both; System altered. SQL> sho parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_log_dest string /media/sf_Oracle/Linux-823 db_flashback_log_dest_size big integer 4G db_flashback_retention_target integer 1440 SQL>Though we have enabled the new parameters to have a separate location for flashback logs, we still have to define FRA location and size before we start utilizing the new location for flashback logs. Otherwise, if you try to create restore point we will get the error as below.
SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 recovery_parallelism integer 0 remote_recovery_file_dest string SQL> SQL> CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE; CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE * ERROR at line 1: ORA-38784: Cannot create restore point 'FREEPDB1_RP'. ORA-38786: Recovery area is not enabled. SQL>Now, let's enable FRA by allocating space and location and then check the contents before and after restore point creation.
SQL> alter system set db_recovery_file_dest_size=4G; System altered. SQL> alter system set db_recovery_file_dest='/opt/oracle/FRA'; System altered. SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /opt/oracle/FRA db_recovery_file_dest_size big integer 4G recovery_parallelism integer 0 remote_recovery_file_dest string SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 0 0 0 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected. SQL> CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE; Restore point created. SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 0 0 0 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 7 rows selected. SQL>We can see that once the restore point is created, FLASHBACK LOG entry from v$flash_recovery_area_usage has disappeared. So how do we check the details of the flashback logs? We do have a new view GV$/V$FLASHBACK_LOG_DEST to check the details.
SQL> select * from V$FLASHBACK_LOG_DEST; NAME SPACE_LIMIT SPACE_USED NUMBER_OF_FILES CON_ID -------------------------------------------------- ----------- ---------- --------------- ---------- /media/sf_Oracle/Linux-823 4294967296 419430400 2 0 SQL>Okay, so what is the use case of having a location outside of FRA?
Very well written article, thank you
ReplyDeleteThank you! Hope it helped you..
DeleteI'm glad it helped! :)
ReplyDeleteNice article.
ReplyDeleteThis would be useful
ReplyDelete