ANBOB™

Flashback Database Feature introduced in Oracle 10g used for Flashback the database to “point in time (SCN)” in the Past. Flashback creates Flashback Logs to write Flashback Data which is stored at “Recovery_file_dest” and managed by “Recovery_file_dest_size”. RVWR is the background Process writes the Flashback data. The parameter “DB_flashback_retention_Target” Specifies upper limit on how far back in time database may be flashed back. We have 2 types of Restore points namely Normal & Guaranteed Restore points (GRP). If we chose GRP, then the flashback Logs and Archive logs are retained until GRP dropped.

Not every data block change is saved in the flashback log because of the trade-offs involved in maintaining performance and storage efficiency within Oracle Database’s architecture.

Flashback database architecture. Once Flashback Database is enabled, images of altered blocks are copied from time to time from the database buffer cache to a new memory area within the SGA, the flashback buffer. This flashback buffer is flushed to disk, to the flashback logs, by a new background process: the Recovery Writer, or RVWR. There is no change to the usual routine of writing changes to the log buffer, which the LGWR then flushes to disk; flashback logging is additional to this. Unlike the redo log, flashback logging is not a log of changes—it is a log of complete block images.

Critical to performance is that not every change is copied to the flashback buffer— only a subset of changes. If all changes to all blocks were copied to the buffer, then the overhead in terms of memory usage and the amount of extra disk I/O required to flush the buffer to disk would be crippling for performance. Internal algorithms limit which versions of which blocks are placed in the flashback buffer, in order to restrict its size and the frequency with which it will fill and be written to disk. These algorithms are intended to ensure that there will be no negative performance hit when enabling Flashback Database: they guarantee that even very busy blocks are logged only infrequently.

When conducting a database flashback, Oracle will read the flashback logs to extract the versions of each changed database block, and copy these versions back into the datafiles. As these changes are applied to the current database in reverse chronological order, this has the effect of taking the database back in time, by reversing the writes that the DBWn process has done.

But since not every datablock change version is saved in the flashback log. For example, the datablock is changed 8 times but only 4 version is saved. So you will not be able to flashback the table to a precise point of time. So you will flashback the table to a previsoue point of time, then use incomplete finnish the rest.

So Flashback Database is in fact a combination of several processes and data structures. First, you must allocate some memory in the SGA (which will be automatic— you cannot control how large the buffer is) and some space on disk to store the flashback data, and start the RVWR process to enable flashback logging. When doing a flashback, Oracle will use the flashback logs to take the database back in time to before the time you want, and then apply redo logs (using whatever archive redo log files and online redo log files are necessary) in the usual fashion for incomplete recovery to bring the datafiles forward to the exact time you want. Then the database can be opened with a new incarnation, in the same manner as following a normal incomplete recovery.

Content of Flashback Log Vs Redo Logs
As per Oracle Document, “Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks”.

The Undo Data is at transaction level and Flashback Data is “Undo Data” at Block Level. Also, Flashback Log Contains the DDL Operations which is not captured in Undo data. Now you may understand why… DDL`s executed at Block Level…

If Undo data not carried over to Flashback Log, where does the flashback Log gets the data… There comes the Flashback Buffer which is part of SGA Memory which gets allocated when flashback enabled on the database. The Mechanism is similar as Log Buffer which writes Redo Changes to Redo Logs through LGWR Process and Flashback Buffer write Past-Image data to Flashback Logs through RVWR Process.

Then, why do we need to preserve Redo/Archive logs for Flashback Operation…

It is by the way RVWR Process writes the data Asynchronously into Flashback Log. LGWR data is synchronously written. Because, Redo Log buffer (LGWR) updated with Every Change (Transaction Level). Rather, Flashback Buffer (RVWR) not updated for every change (Block Level).

During normal flashback log maintenance operations, the RVWR process periodically inserts flashback markers into the flashback database logs. Flashback markers tell Oracle how to flashback to a previous point in time when flashing back to the database. During the flashback operation, Oracle uses the information in the flashback markers to determine how much of the flashback database log is needed to restore the block image; Oracle then uses archivelog forward recovery to traverse the database to the user-specified SCN or time point.

Let’s Explain in Detail…

When we perform a flashback operation, Oracle needs all flashback logs from now on up until the desired time. The Flashback Logs will be applied consecutively starting from the newest to the oldest. For instance, if we want to flashback the database to SCN 4123376440 Corresponds to “10:00AM”, Oracle will read flashback log file section in control file and will check for the availability of all needed flashback log files.

Consider the respective Flashback Log O1_MF_26YSTQ6S_.FLB consists of Rollback data of SCN from 4123374373 to 4123376446 and Archive log file (Roll Forward data) of Change data from 4123361850 until 4123380675.

The flashback operation will not succeed without archived redo log. The reason is: when we perform flashback operation to SCN 4123376440, Oracle cannot apply all needed flashback logs Exactly until Specific SCN as it applying before-images of data. Oracle needs to restore each data block copy to its state at a closest possible point in time before SCN 4123376440. This will guarantee that the subsequent “redo apply” operation will forward the database to SCN 4123376440 and the database will be in consistent state. In Other words, if desired restore point of time is 10:00 AM, then the Flashback Log will help to data block until 09:47 AM and then we will need all archived log files that contain redo data for the time interval between 09:47 AM and 10:00 AM. Without that redo data, the flashback operation cannot succeed.

Curious about New Features of Flashback on 19c…

Flashback Standby Database when Primary Database is Flashed Back

We usually have to flashback/Rebuild the Standby database during primary Flashback. Since Oracle 19c, a DBA can put the standby database in MOUNT mode with no managed recovery (or Read Only) and then flash back the primary database. When you restart the MRP after closing the physical standby, the recovery process automatically flashes back the standby database and continues to apply the new branch of redo.

parameters about flashback in 23c

SQL> @pd flashback
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      2172   87C _siop_flashback_scandepth                                20                             Shared IO pool flashback io completion scan depth
      2394   95A _standby_auto_flashback                                  TRUE                           Standby automatic flashback when primary flashed back
      2895   B4F db_flashback_retention_target                            1440                           Maximum Flashback Database log retention time in minutes.
      2896   B50 _validate_flashback_database                             FALSE                          Scan database to validate result of flashback database
      2897   B51 _flashback_allow_noarchivelog                            FALSE                          Allow enabling flashback on noarchivelog database
      2898   B52 _verify_flashback_redo                                   TRUE                           Verify that the redo logs needed for flashback are available
      2899   B53 _flashback_verbose_info                                  FALSE                          Print verbose information about flashback database
      2900   B54 _flashback_dynamic_enable_failure                        0                              Simulate failures during dynamic enable
      2901   B55 _flashback_dynamic_enable                                TRUE                           enable flashback enable code path
      2903   B57 _flashback_marker_cache_size                             328                            Size of flashback database marker cache
      2904   B58 _flashback_marker_cache_enabled                          TRUE                           Enable flashback database marker cache
      2905   B59 _flashback_database_test_only                            FALSE                          Run Flashback Database in test mode
      2909   B5D _fb_recovery_slave_count                                 0                              Number of recovery slaves to be used by the FLASHBACK DATABASE
      2911   B5F _max_flashback_shrink_size                               1048576                        maximum size(in KBs) a datafile can be shrunk with flashback DB
      2912   B60 _test_flashback_shrink                                   0                              test flashback datafile shrink feature
      2913   B61 _flashback_logfile_enqueue_timeout                       600                            flashback logfile enqueue timeout for opens
      2914   B62 _fb_log_max_rereads                                      100                            Maximum number of rereads after reading a corrupt flashback block
      2915   B63 db_flashback_log_dest                                                                   Separate creation directory for flashback database logs
      2916   B64 db_flashback_log_dest_size                               0                              Size limit of separate creation directory for flashback             database logs
      2917   B65 _flashback_barrier_interval                              1800                           Flashback barrier interval in seconds
      2918   B66 _flashback_standby_barrier_interval                      1                              Flashback standby barrier interval in seconds
      2919   B67 _flashback_stby_support_mira                             TRUE                           Flashback database on standby supports MIRA
      2920   B68 _flashback_standby_check_barrier_MIRA                    20                             Flashback standby check barrier generation in seconds
      2921   B69 _flashback_standby_check_minpfh_MIRA                     15                             Flashback standby check minimum high fuzzy scn in seconds
      2922   B6A _flashback_max_standby_sync_span                         300                            Maximum time span between standby recovery sync for flashback
      2923   B6B _flashback_fuzzy_barrier                                 TRUE                           Use flashback fuzzy barrier
      2924   B6C _disable_kcb_flashback_blocknew_opt                      FALSE                          Disable KCB flashback block new optimization
      2925   B6D _disable_kcbl_flashback_blocknew_opt                     FALSE                          Disable KCBL flashback block new optimization
      2926   B6E _disable_flashback_wait_callback                         FALSE                          Disable flashback wait callback
      2927   B6F _check_block_new_invariant_for_flashback                 FALSE                          check block new invariant for flashback
      2929   B71 _minimum_db_flashback_retention                          60                             Minimum flashback retention
      2930   B72 _flashback_delete_chunk_MB                               128                            Amount of flashback log (in MB) to delete in one attempt
      2931   B73 _flashback_11_1_block_new_opt                            FALSE                          use 11.1 flashback block new optimization scheme
      2932   B74 _flashback_marker_for_every_grp                          FALSE                          generate a new flashback marker for every GRP
      2933   B75 _flashback_log_size                                      1000                           Flashback log size
      2934   B76 _flashback_log_min_size                                  100                            Minimum flashback log size
      2935   B77 _db_flashback_log_min_size                               16777216                       Minimum flashback database log size in bytes
      2936   B78 _db_flashback_log_min_total_space                        0                              Minimum flashback database log total space in bytes
      2937   B79 _flashback_copy_latches                                  10                             Number of flashback copy latches
      2938   B7A _flashback_n_log_per_thread                              128                            Desired number of flashback logs per flashback thread
      2939   B7B _flashback_max_n_log_per_thread                          2048                           Maximum number of flashback logs per flashback thread
      2940   B7C _flashback_generation_buffer_size                        67108864                       flashback generation buffer size
      2941   B7D _allocate_flashback_buffer                               FALSE                          Allocate flashback buffer at mount time even if flashback is off
      2942   B7E _flashback_max_log_size                                  0                              Maximum flashback log size in bytes (OS limit)
      2943   B7F _flashback_log_io_error_behavior                         0                              Specify Flashback log I/O error behavior
      2944   B80 _flashback_prepare_log                                   TRUE                           Prepare Flashback logs in the background
      2945   B81 _flashback_size_based_on_redo                            TRUE                           Size new flashback logs based on average redo log size
      2946   B82 _flashback_validate_controlfile                          FALSE                          validate flashback pointers in controlfile for 11.2.0.2 database
      2947   B83 _flashback_format_chunk_mb                               4                              Chunk mega-bytes for formatting flashback logs using sync write
      2948   B84 _flashback_format_chunk_mb_dwrite                        16                             Chunk mega-bytes for formatting flashback logs using delayed write
      2949   B85 _flashback_log_rac_balance_factor                        10                             flashback log rac balance factor
      2950   B86 _flashback_write_max_loop_limit                          10                             Flashback writer loop limit before it returns
      2951   B87 _flashback_hint_barrier_percent                          20                             Flashback hint barrier percent
      2952   B88 _drop_flashback_logical_operations_enq                   FALSE                          Drop logical operations enqueue immediately during flashback marker generation
      2953   B89 _percent_flashback_buf_partial_full                      50                             Percent of flashback buffer filled to be considered partial full
      2954   B8A _flashback_reclaim_monitor_window                        7                              Proactive flashback logs reclaimation window
      2955   B8B _flashback_reclaim_speed_up                              FALSE                          Proactive flashback logs reclaimation speed up
      2969   B99 _db_flashback_iobuf_size                                 1                              Flashback IO Buffer Size
      2970   B9A _db_flashback_num_iobuf                                  64                             Flashback Number of IO buffers
      2971   B9B _flashback_enable_ra                                     TRUE                           Flashback enable read ahead
      3130   C3A _disable_flashback_data_archive                          FALSE                          disable pdb-level flashback archiving
      3131   C3B _disable_fba_qrw                                         0                              disable flashback archiver query rewrite
      3132   C3C _disable_fba_wpr                                         0                              disable flashback archiver wait for prepared transactions
      3133   C3D _flashback_archiver_partition_size                       0                              flashback archiver table partition size
      3134   C3E _fbda_busy_percentage                                    0                              flashback archiver busy percentage
      3135   C3F _fbda_inline_percentage                                  0                              flashback archiver inline percentage
      3136   C40 _fbda_debug_mode                                         0                              flashback archiver debug event for testing
      3137   C41 _fbda_debug_assert                                       0                              flashback archiver debug assert for testing
      3138   C42 _fbda_global_bscn_lag                                    0                              flashback archiver global barrier scn lag
      3139   C43 _fbda_rac_inactive_limit                                 0                              flashback archiver rac inactive limit
      3140   C44 _fbda_adv_gbl_bscn_bcast_interval                        0                              flashback archiver broadcast interval to advance global barrier SCN
      3141   C45 _fbda_tcrv_cleanup_lag                                   3600                           flashback archiver tcrv cleanup lag in secs
      3251   CB3 _disable_flashback_recyclebin_opt                        TRUE                           Don't use the Flashback Recyclebin optimization
      3256   CB8 _enable_securefile_flashback_opt                         TRUE                           Enable securefile flashback optimization
      3534   DCE _kdli_sio_fbwrite_pct                                    35                             percentage of buffer used for direct writes in flashback-db
      6259  1873 _create_pdb_flashback_barrier                            FALSE                          force flashback barrier for create pdb

76 rows selected.

Flashback Logging Internals

Use Oracle-Managed Files (OMF)
Flashback log size same as database block size
Initial size is 1001 x block size
In Linux records written to disk using pwrite64 Multi block writes

-- Flashback Log Files Controlfile Dumps
SQL> ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';

-- To dump the last 2000 flashback records 
SQL> ORADEBUG DUMP FBTAIL 1;
Statement processed.

-- All flashback records for a thread can be dumped using:
SQL> ALTER SYSTEM DUMP FLASHBACK THREAD [thread_number]

--All flashback records for a specific flashback logfile can be dumped using
SQL> ALTER SYSTEM DUMP FLASHBACK LOGFILE [log_file_number]

--To dump flashback generation status use:
SQL> ORADEBUG DUMP FLASHBACK_GEN 1

-- To dump flashback logfile headers use:
SQL> ORADEBUG DUMP FBHDR 1

-- To dump all logical flashback records in the current flashback incarnation use:
SQL> ORADEBUG DUMP FBINC 1

-- To check flashback log tail use:
SQL> ALTER SESSION SET EVENTS 'immediate trace name controlf level 2';

Every flashback record has a type

you can read flashback log的写入机制研究_ITPUB博客 test case .

References:
https://tekdba.wordpress.com/2019/07/18/oracleflashbackdatabase/

打赏

微信扫一扫，打赏作者吧～