In my previous post, I talked about why use the new parameters DB_FLASHBACK_LOG_DEST_SIZE and DB_FLASHBACK_LOG_DEST for Oracle 23ai. I spoke about how to configure them and the benefits. Here you will find additional details about these two parameters and what they change for internal views and the restore points.
Restore Points
If you already used the fast recovery area, you will notice that when it becomes full the database freezes until you clean it. Sometimes when you use restore points you easily reach the point when you have a lot of flashback logs that can’t be deleted. The famous “db_recovery_file_dest_size of 2147483648 bytes is 100.00% used” in alertlog.
For the scenario below, I already set the two parameters, DB_FLASHBACK_LOG_DEST_SIZE and DB_FLASHBACK_LOG_DEST. And the size for DB_FLASHBACK_LOG_DEST_SIZE is 1GB. Below you can see that I created a guarantee restore point for the PDB and executed some inserts:
SQL> alter system set db_flashback_log_dest_size = 1G scope = both sid = '*'; System altered. SQL> CREATE RESTORE POINT restp_flsize FOR PLUGGABLE DATABASE pdbdc1 GUARANTEE FLASHBACK DATABASE; Restore point created. SQL> alter session set container = PDBDC1; Session altered. SQL> SQL> insert /*+ append */ into t1 … … 23 ; 1600000 rows created. SQL> SQL> insert /*+ append */ into t1 … … 23 ; 1600000 rows created. SQL>
While running several of these inserts, the flashback log was filled we can see this new friendly message at alertlog (The database may hang soon due to guaranteed restore points):
--ATTENTION-- The database may hang soon due to guaranteed restore points. 2024-06-16T22:23:55.841970+02:00 Thread 1 advanced to log sequence 73 (LGWR switch), current SCN: 2421137 Current log# 1 seq# 73 mem# 0: /opt/oracle/oradata/FREE/redo01.log 2024-06-16T22:23:59.872047+02:00 ARC2 (PID:12053): Archived Log entry 72 added for B-1171730502.T-1.S-72 LOS:0x000000000024d26e NXS:0x000000000024f191 NAB:400567 ID 0x560e8f84 LAD:1 [krse.c:4872]
And if we continue the database will freeze because the space is exhausted (Recovery Writer (RVWR) is hung until more space is available in Flashback destination):
2024-06-16T22:27:48.764841+02:00 Thread 1 advanced to log sequence 76 (LGWR switch), current SCN: 2432519 Current log# 1 seq# 76 mem# 0: /opt/oracle/oradata/FREE/redo01.log 2024-06-16T22:27:52.505980+02:00 ARC1 (PID:12051): Archived Log entry 75 added for B-1171730502.T-1.S-75 LOS:0x0000000000250a32 NXS:0x0000000000251e07 NAB:400563 ID 0x560e8f84 LAD:1 [krse.c:4872] 2024-06-16T22:27:55.276963+02:00 --ATTENTION-- Recovery Writer (RVWR) is hung until more space is available in Flashback destination. Unable to allocate flashback log of 6400 blocks from Flashback destination of size 1073741824 bytes. … … 2024-06-16T22:28:56.582626+02:00 --ATTENTION-- Recovery Writer (RVWR) is hung until more space is available in Flashback destination. Unable to allocate flashback log of 6400 blocks from Flashback destination of size 1073741824 bytes. 2024-06-16T22:29:56.837691+02:00 --ATTENTION-- Recovery Writer (RVWR) is hung until more space is available in Flashback destination. Unable to allocate flashback log of 6400 blocks from Flashback destination of size 1073741824 bytes. 2024-06-16T22:30:57.208698+02:00 --ATTENTION-- Recovery Writer (RVWR) is hung until more space is available in Flashback destination. Unable to allocate flashback log of 6400 blocks from Flashback destination of size 1073741824 bytes. 2024-06-16T22:31:58.381068+02:00 --ATTENTION-- Recovery Writer (RVWR) is hung until more space is available in Flashback destination. Unable to allocate flashback log of 6400 blocks from Flashback destination of size 1073741824 bytes. 2024-06-16T22:32:59.518920+02:00 --ATTENTION-- Recovery Writer (RVWR) is hung until more space is available in Flashback destination. Unable to allocate flashback log of 6400 blocks from Flashback destination of size 1073741824 bytes. 2024-06-16T22:33:28.784126+02:00
This was caused because I have the restore point. It is a soft limit by the parameter and caused as well because there is one restore point active. The options to solve are adding more space (if possible), removing the restore point, or changing the value of parameterDB_FLASHBACK_LOG_DEST_SIZE.
New view v$flashback_log_dest
Another interesting point is about the v$recovery_area_usage. Now, when using the dedicated parameters we need to use another view, the v$flashback_log_dest. As you see below, the information about the flashback logs are over another view:
SQL> Select file_type, percent_space_used as used_pct, percent_space_reclaimable as reclaimable_pct, number_of_files as numfl from v$recovery_area_usage order by USED_PCT; FILE_TYPE USED_PCT RECLAIMABLE_PCT NUMFL ----------------------- ---------- --------------- ---------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 IMAGE COPY 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 BACKUP PIECE .18 0 1 FLASHBACK LOG 9.77 0 5 8 rows selected. SQL> … --After set the new parameters … SQL> Select file_type, percent_space_used as used_pct, percent_space_reclaimable as reclaimable_pct, number_of_files as numfl from v$recovery_area_usage order by USED_PCT; FILE_TYPE USED_PCT RECLAIMABLE_PCT NUMFL ----------------------- ---------- --------------- ---------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 IMAGE COPY 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 BACKUP PIECE .18 0 1 7 rows selected. SQL> select NAME, SPACE_LIMIT, SPACE_USED, NUMBER_OF_FILES, CON_ID from V$FLASHBACK_LOG_DEST; NAME SPACE_LIMIT SPACE_USED NUMBER_OF_FILES CON_ID ---------------------------------------- ----------- ---------- --------------- ---------- /opt/oracle/oradata/FREE/flashlog 1073741824 419430400 2 0 SQL>
So, when using the new parameters, the information about the flashback log moves from v$recovery_area_usage to v$flashback_log_dest.
Flashback ON and OFF
The next one is when you want to move between using the fast recovery area and the new DB_FLASHBACK_LOG_DEST to have a dedicated path for flashback logs. In this case, you need, first, to disable the flashback mode of the database. This is needed to clean the old flashback logs from the system:
[oracle@o23fdc21 ~]$ mkdir /opt/oracle/oradata/FREE/recovery [oracle@o23fdc21 ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Sat Jun 15 18:11:06 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> alter system set db_recovery_file_dest_size = 2G scope = spfile sid = '*'; System altered. SQL> alter system set db_recovery_file_dest='/opt/oracle/oradata/FREE/recovery' scope = spfile sid = '*'; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1603726344 bytes Fixed Size 5360648 bytes Variable Size 419430400 bytes Database Buffers 1174405120 bytes Redo Buffers 4530176 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SQL> show parameter dest_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_log_dest_size big integer 0 db_recovery_file_dest_size big integer 2G SQL> SQL> alter session set container = PDBDC1; Session altered. SQL> -- GENERATE SOME DATA LOAD TO CREATE FLASHBACK LOGS at FAST RECOVERY AREA SQL> exit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 [oracle@o23fdc11 ~]$ [oracle@o23fdc11 ~]$ mkdir /opt/oracle/oradata/FREE/flashlog [oracle@o23fdc11 ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Sun Jun 16 21:44:36 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> alter system set db_flashback_log_dest = '/opt/oracle/oradata/FREE/flashlog' scope = spfile sid = '*'; System altered. SQL> alter system set db_flashback_log_dest_size = 2G scope = spfile sid = '*'; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> startup; ORACLE instance started. Total System Global Area 1603726344 bytes Fixed Size 5360648 bytes Variable Size 553648128 bytes Database Buffers 1040187392 bytes Redo Buffers 4530176 bytes Database mounted. ORA-38760: This database instance failed to turn on flashback database Help: https://docs.oracle.com/error-help/db/ora-38760/ SQL>
So, above you can see that I set the Fast Recovery Area first, and enabled the flashback mode. After, I generated some load to create archivelogs and flashback logs. To finish, I created the new directory and tried to move to the new path, but it failed. And in the alertlog you will see ORA-38896:
Successful mount of redo thread 1, with mount id 1443903902 2024-06-16T21:47:10.136457+02:00 Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_12036.trc: ORA-38896: can not specify DB_FLASHBACK_LOG_DEST in INIT.ORA file after flashback database has been enabled without it 2024-06-16T21:47:10.136567+02:00 Database mounted in Exclusive Mode Lost write protection mode set to "auto" Completed: ALTER DATABASE MOUNT ALTER DATABASE OPEN 2024-06-16T21:47:10.241081+02:00 Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_12036.trc: ORA-38760: This database instance failed to turn on flashback database ORA-38760 signalled during: ALTER DATABASE OPEN...
It is not needed to stop the database before making the change. You just need to put the flashback mode off, move to the new path, and later enable flashback mode again:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 261
Next log sequence to archive 261
Current log sequence 260
SQL>
SQL> select LOG_MODE, FLASHBACK_ON from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
SQL>
SQL> set linesize 255
SQL> col name format a70
SQL> SELECT log# as lognum , name, bytes/1024/1024 as size_mb from v$flashback_database_logfile order by first_time;
LOGNUM NAME SIZE_MB
---------- ---------------------------------------------------------------------- ----------
1 /opt/oracle/oradata/FREE/recovery/FREE/flashback/o1_mf_m82q1rwd_.flb 200
2 /opt/oracle/oradata/FREE/recovery/FREE/flashback/o1_mf_m82q1w0g_.flb 200
SQL>
SQL> show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest string
db_flashback_log_dest_size big integer 0
db_flashback_retention_target integer 1440
SQL>
SQL> alter system set db_flashback_log_dest_size=1G scope = both;
System altered.
SQL> alter system set db_flashback_log_dest='/opt/oracle/oradata/FREE/flashlog' scope = both;
alter system set db_flashback_log_dest='/opt/oracle/oradata/FREE/flashlog' scope = both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-38895: can not set DB_FLASHBACK_LOG_DEST dynamically after flashback database is on
Help: https://docs.oracle.com/error-help/db/ora-02097/
SQL> alter database flashback off;
Database altered.
SQL> alter system set db_flashback_log_dest='/opt/oracle/oradata/FREE/flashlog' scope = both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> SELECT log# as lognum , name, bytes/1024/1024 as size_mb from v$flashback_database_logfile order by first_time;
LOGNUM NAME SIZE_MB
---------- ---------------------------------------------------------------------- ----------
1 /opt/oracle/oradata/FREE/flashlog/FREE/flashback/o1_mf_m82qkqm9_.flb 200
2 /opt/oracle/oradata/FREE/flashlog/FREE/flashback/o1_mf_m82qkt6h_.flb 200
SQL>
Cleaning the parameters
If, after you set the parameters, you want to clean the configuration or come back to Fast Recovery Area you need to plan for a bounce to remove the config. The point is that setting the DB_FLASHBACK_LOG_DEST_SIZE does not allow to remove it, even with flashback mode off:
SQL> select LOG_MODE, FLASHBACK_ON from v$database; LOG_MODE FLASHBACK_ON ------------ ------------------ ARCHIVELOG YES SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_log_dest string db_flashback_log_dest_size big integer 0 db_flashback_retention_target integer 1440 SQL> alter system set db_flashback_log_dest_size=1G scope = both; System altered. SQL> alter system set db_flashback_log_dest='/opt/oracle/oradata/FREE/flashlog' scope = both; System altered. SQL> alter database flashback off; Database altered. SQL> alter system reset db_flashback_log_dest scope = both; System altered. SQL> alter system reset db_flashback_log_dest_size scope = both; alter system reset db_flashback_log_dest_size scope = both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-38893: Parameter DB_FLASHBACK_LOG_DEST_SIZE is out of range (1 - 18446744073709551614) Help: https://docs.oracle.com/error-help/db/ora-02097/ SQL> alter system set db_flashback_log_dest_size = 0 scope = both; alter system set db_flashback_log_dest_size = 0 scope = both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-38893: Parameter DB_FLASHBACK_LOG_DEST_SIZE is out of range (1 - 18446744073709551614) Help: https://docs.oracle.com/error-help/db/ora-02097/ SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_log_dest string db_flashback_log_dest_size big integer 1G db_flashback_retention_target integer 1440 SQL> SQL> alter system reset db_flashback_log_dest_size scope = spfile; System altered. SQL> SQL> startup; ORACLE instance started. Total System Global Area 1603726344 bytes Fixed Size 5360648 bytes Variable Size 939524096 bytes Database Buffers 654311424 bytes Redo Buffers 4530176 bytes Database mounted. Database opened. SQL>
Above you can see that I set the parameters, and tried to remove it. Even disabling the flashback mode I couldn’t remove the DB_FLASHBACK_LOG_DEST_SIZE (reset or equal to 0). Just after reset at spfile was accepted.
This is not a real issue because you can have the parameter set, even if your flashback logs go to the Fast Recovery Area. The path is more important than the size (as a limiting/constraint parameter) when disabling it:
SQL> show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest string /opt/oracle/oradata/FREE/flash
log
db_flashback_log_dest_size big integer 1G
db_flashback_retention_target integer 1440
SQL> select LOG_MODE, FLASHBACK_ON from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
SQL> alter database flashback off;
Database altered.
SQL> SELECT log# as lognum , name, bytes/1024/1024 as size_mb from v$flashback_database_logfile order by first_time;
no rows selected
SQL> alter system reset db_flashback_log_dest scope = both;
System altered.
SQL> show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest string
db_flashback_log_dest_size big integer 1G
db_flashback_retention_target integer 1440
SQL> select LOG_MODE, FLASHBACK_ON from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
SQL> alter database flashback on;
Database altered.
SQL> SELECT log# as lognum , name, bytes/1024/1024 as size_mb from v$flashback_database_logfile order by first_time;
LOGNUM NAME SIZE_MB
---------- ---------------------------------------------------------------------- ----------
1 /opt/oracle/oradata/FREE/recovery/FREE/flashback/o1_mf_m82rpc4d_.flb 200
2 /opt/oracle/oradata/FREE/recovery/FREE/flashback/o1_mf_m82rph9w_.flb 200
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 261
Next log sequence to archive 261
Current log sequence 260
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/oradata/FREE/recov
ery
db_recovery_file_dest_size big integer 10G
SQL>
Different than the first example, above you can see that I was using the parameter, disabled the flashback mode, reset the parameter DB_FLASHBACK_LOG_DEST to clean it, and enabled flashback mode again. You can see that the flashback mode was redirected to the Fast Recovery Area. But please do not forget to clean the parameter DB_FLASHBACK_LOG_DEST_SIZE to return it to the default value.
Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies, or opinions. The information here was edited to be useful for general purposes, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright”