23ai, additional details for DB_FLASHBACK_LOG_DEST_SIZE and DB_FLASHBACK_LOG_DEST

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” 

Leave a Reply

Your email address will not be published. Required fields are marked *