Oracle 23ai — Storing Flashback Logs Outside FRA

By -

 Prior to Oracle 23ai, Flashback Logs had to be stored exclusively in the Fast Recovery Area (FRA). If FRA was not configured, the Flashback Database feature could not be enabled, as illustrated below:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
SQL> alter database flashback on;
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

With Oracle 23ai, two new parameters have been introduced that allow Flashback Logs to be stored outside FRA:

  • db_flashback_log_dest
  • db_flashback_log_dest_size

Configuring Flashback Logs Outside FRA

First, check the status of the parameters related to FRA:

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

Next, configure the new parameters and enable Flashback Database:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter system set db_flashback_log_dest_size=800m;
System altered.

SQL> alter system set db_flashback_log_dest='/oracle23ai/FlashbackLog';
System altered.

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

After enabling the feature, Flashback Logs with the .flb extension are created in the specified path:

SQL> select NAME, SPACE_LIMIT/1024/1024 as "SPACE_LIMIT_MB", 
SPACE_USED/1024/1024 as "SPACE_USED_MB", NUMBER_OF_FILES 
from V$FLASHBACK_LOG_DEST;

NAME                           SPACE_LIMIT_MB SPACE_USED_MB NUMBER_OF_FILES
------------------------------ -------------- ------------- ---------------
/oracle23ai/FlashbackLog                   800           200               1

The logs are stored as shown below:

[oracle@target ~]$ cd /oracle23ai/FlashbackLog/FREE/flashback/
[oracle@target flashback]$ ls -l
-rw-r----- 1 oracle oinstall 209723392 Jul 26 11:32 o1_mf_ld1k8lnr_.flb
-rw-r----- 1 oracle oinstall 209723392 Jul 26 11:33 o1_mf_ld1k8p4m_.flb
-rw-r----- 1 oracle oinstall 209723392 Jul 26 11:32 o1_mf_ld1k8xfv_.flb

Relationship Between Flashback Logs and Redo Logs

There is an apparent correlation between the size of Flashback Logs and the size of redo log files. For instance, with the above configuration, each Flashback Log is approximately 200 MB, matching the redo logfile size. To explore this further, new redo log groups with a different size are created:

SQL> alter database add logfile group 13 size 800m;
Database altered.

SQL> alter database add logfile group 14 size 800m;
Database altered.

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> select group#, bytes from v$log;

GROUP#      BYTES
---------- ----------
13          838860800
14          838860800

After creating new log files, enable and disable Flashback Database to generate new Flashback Logs with updated sizes:

SQL> alter database flashback off;
Database altered.

SQL> alter database flashback on;
Database altered.

The newly generated logs now reflect the updated size:

[root@target flashback]# ls -l
-rw-r----- 1 oracle oinstall 838868992 Jul 26 13:34 o1_mf_ld1lcpjx_.flb

Restore Point Logs

When configuring the Flashback Database parameters, logs related to restore points are also moved to the specified path:

SQL> alter database flashback off;
Database altered.

SQL> create restore point test guarantee flashback database;
ORA-38784: Cannot create restore point 'TEST'.
ORA-38786: Recovery area is not enabled.

SQL> alter system set db_recovery_file_dest_size=10g;
System altered.

SQL> alter system set db_recovery_file_dest='/oracle23ai/fra';
System altered.

SQL> create restore point test guarantee flashback database;
Restore point created.

[oracle@target flashback]$ ls -l
-rw-r----- 1 oracle oinstall 209723392 Jul 26 15:03 o1_mf_ld1x4vfk_.flb

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more