Pre-23ai, Database Flashback Logs always were created in the DB_RECOVERY_FILE_DEST location (i.e.. the well-known "Fast Recovery Area" also known as "FRA").
However, these would share space with ArchiveLogs (if you've configured ArchiveLogs to the same location) and RMAN Backups (if you aren't specifically writing RMAN Backups to a different location).
23ai introduced the DB_FLASHBACK_LOG_DEST (and corresponding DB_FLASHBACK_LOG_DEST_SIZE) parameter to allow you to separate your Database Flashback Logs to a dedicated location.
The 23ai New Features documentation has this to say :
In previous releases, you could store flashback database logs only in the fast recovery area. Now you can optionally designate a separate location for flashback logging. For example, if you have write-intensive database workloads, then flashback database logging can slow down the database if the fast recovery area is not fast enough. In this scenario, you can now choose to write the flashback logs to faster disks. Using a separate destination also eliminates the manual administration to manage the free space in the fast recovery area.
Managing flashback database logs outside the fast recovery area lowers the operational costs related to space management and guarantees the best performance for workloads that are typically impacted by flashback logging on traditional storage.
And it provides a link to the documentation on the parameter.
You might think that DB_FLASHBACK_LOG_DEST is now completely independent of DB_RECOVERY_FILE_DEST.
Here is my test run where I configured DB_FLASHBACK_LOG_DEST without configuring DB_RECOVERY_FILE_DEST :
h-4.4$ cd /opt/oracle
sh-4.4$ mkdir FBL
sh-4.4$ mkdir FRA
sh-4.4$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Sun May 5 10:26:26 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> 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 402653184 bytes
Database Buffers 1191182336 bytes
Redo Buffers 4530176 bytes
Database mounted.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/23ai/dbhom
eFree/dbs/spfileFREE.ora
SQL>
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> show parameter db_flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest string
db_flashback_log_dest_size big integer 0
SQL> alter system set db_flashback_log_dest_size=10G;
System altered.
SQL> alter system set db_flashback_log_dest='/opt/oracle/FBL';
System altered.
SQL> create restore point MY_FIRST_RP ;
Restore point created.
SQL> alter system archive log current;
System altered.
SQL>
SQL> create table x as select * from cdb_objects;
Table created.
SQL> insert into x select * from x;
141420 rows created.
SQL> delete x;
282840 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL>
SQL> alter database flashback on; -- only here I enable Flashback
Database altered.
==============================================
alert log messages :
2024-05-05T10:38:35.262274+00:00
alter database flashback on
2024-05-05T10:38:35.423698+00:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 8388608 bytes in shared pool for flashback generation buffer
Flashback Database Enabled at SCN 3124894
===============================================
SQL> create restore point MY_FIRST_RP; -- testing if I can create another RP with the same name
create restore point MY_FIRST_RP
*
ERROR at line 1:
ORA-38778: Restore point 'MY_FIRST_RP' already exists.
Help: https://docs.oracle.com/error-help/db/ora-38778/
SQL> drop restore point MY_FIRST_RP;
Restore point dropped.
SQL> create restore point MY_FIRST_RP;
Restore point created.
SQL> drop table x;
Table dropped.
SQL> create table x as select * from cdb_objects;
Table created.
SQL>
SQL> alter system archive log current;
System altered.
SQL> delete x;
141420 rows deleted.
SQL> insert into x select * from cdb_objects;
141421 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL>
SQL> select substr(name,1,32), scn, time from v$restore_point; -- identify the RP that has been created
SUBSTR(NAME,1,32)
--------------------------------------------------------------------------------------------------------------------------------
SCN TIME
---------- ---------------------------------------------------------------------------
MY_FIRST_RP
3124955 05-MAY-24 10.39.30.000000000 AM
SQL> select * from v$flashback_database_log; -- identify the FBDB Logs Size
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE CON_ID
-------------------- --------- ---------------- -------------- ------------------------ ----------
3124893 05-MAY-24 1440 419430400 0 0
SQL> select * from v$flashback_log_dest; -- identify the FB Log Dest (why isn't the view named V$FLASHBACK_DATABASE_LOG_DEST ?)
NAME
------------------------------------------------------------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED NUMBER_OF_FILES CON_ID
----------- ---------- --------------- ----------
/opt/oracle/FBL
1.0737E+10 419430400 2 0
SQL>
SQL> !sh
sh-4.4$ cd /opt/oracle/FBL
sh-4.4$ du -sh *
401M FREE
sh-4.4$ cd FREE
sh-4.4$ ls
flashback
sh-4.4$ cd flashback
sh-4.4$ ls -l
total 409620
-rw-r----- 1 oracle oinstall 209723392 May 5 10:41 o1_mf_m3grfc8t_.flb
-rw-r----- 1 oracle oinstall 209723392 May 5 10:38 o1_mf_m3grfg1v_.flb
sh-4.4$
sh-4.4$ cd $ORACLE_HOME/dbs
sh-4.4$ ls -l arch1*
-rw-r----- 1 oracle oinstall 98164736 May 5 10:31 arch1_2_1167168121.dbf
-rw-r----- 1 oracle oinstall 106480640 May 5 10:33 arch1_3_1167168121.dbf
-rw-r----- 1 oracle oinstall 37506048 May 5 10:40 arch1_4_1167168121.dbf
-rw-r----- 1 oracle oinstall 52515840 May 5 10:40 arch1_5_1167168121.dbf
sh-4.4$
sh-4.4$ exit
exit
SQL> select count(*) from x;
COUNT(*)
----------
141421
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
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> flashback database to restore point MY_FIRST_RP; -- try to Flashback the Database
flashback database to restore point MY_FIRST_RP
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
Help: https://docs.oracle.com/error-help/db/ora-38760/
============================================
alert log messages :
2024-05-05T10:45:28.380285+00:00
Successful mount of redo thread 1, with mount id 1440201864
2024-05-05T10:45:28.380506+00:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 5807328 bytes in shared pool for flashback generation buffer
RVWR could not begin generation of flashback log data because
DB_RECOVERY_FILE_DEST is not set.
2024-05-05T10:45:28.392865+00:00
Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_rvwr_2343.trc:
ORA-38776: cannot begin flashback generation - recovery area is disabled
2024-05-05T10:45:28.392899+00:00
WARNING: Cannot open the flashback thread for this instance due to the above error.
WARNING: Flashback thread open failed - to resolve this, either correct the reported error or turn off database flashbac
k.
2024-05-05T10:45:28.393060+00:00
Database mounted in Exclusive Mode
Lost write protection mode set to "auto"
Completed: ALTER DATABASE MOUNT
2024-05-05T10:46:04.458087+00:00
flashback database to restore point MY_FIRST_RP
ORA-38760 signalled during: flashback database to restore point MY_FIRST_RP...
2024-05-05T10:50:43.887137+00:00
==============================================
Explanation of the Error :
===========================
38776, 00000, "cannot begin flashback generation - recovery area is disabled"
// *Cause: During a database mount, the RVWR process discovered that the
// recovery area was disabled. DB_RECOVERY_FILE_DEST must have
// been set null or removed from the INIT.ORA file while the database
// was unmounted.
// *Action: Flashback database requires the recovery area to be enabled.
// Either enable the recovery area by setting the
// DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization
// parameters, or turn off flashback database with the
// ALTER DATABASE FLASHBACK OFF command.
So, Oracle
1. allows me to create a Restore Point
2. generates Flashback Log
3. confirms that they exist
BUT DOES NOT ALLOW ME TO FLASHBACK THE DATABASE.
DB_RECOVERY_FILE_DEST is still mandatory (even if you have ArchiveLogs being written elsewhere -- my ArchiveLogs were going to the default location under $ORACLE_HOME/dbs).
If you look at the documentation (column "Required" in Table 5-4) it does say that DB__RECOVERY_FILE_DEST is a required parameter -- although it doesnt explicitly say that this is required for the FLASHBACK LOGS. Also, my database was happy to generate ArchiveLogs in another, default, location ($ORACLE_HOME/dbs).
The CREATE RESTORE POINT command didn't give me any warning that the Restore Point would not be usable -- I would treat this as a Bug.
No comments:
Post a Comment