Flashback Database Logs can exceed the Retention Target

The documentation on the Flashback Retention Target in 11.2 and 12.1 states that this parameter specifies an upper limit on how far the database may be flashed back.

However, if the FRA (db_recovery_file_dest_size) is actually large enough, Oracle may retain flashback logs for a much longer duration.

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select sysdate, l.* from v$flashback_database_log l;

SYSDATE            OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET
------------------ -------------------- ------------------ ----------------
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------- ------------------------
29-OCT-16 07:42:44              6968261 28-OCT-16 22:35:50              180
     157286400                 86467584


SQL> 
SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer                                180
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size           2260088 bytes
Variable Size      750781320 bytes
Database Buffers   310378496 bytes
Redo Buffers         5517312 bytes
Database mounted.
SQL> flashback database to timestamp trunc(sysdate);

Flashback complete.

SQL>

Thus, it is useful to check the V$FLASHBACK_DATABASE_LOG, V$FLASHBACK_DATABASE_LOGFILE and V$FLASHBACK_DATABASE_STAT and V$FLASH_RECOVERY_AREA_USAGE views from time to time.

See Oracle Support Doc# 1194013.1 for a discrepancy between the first two views.

Note : If you have Standby database configured, also see John Hallas's blog post.
.
.
.

Multi-Version Read Consistency in Oracle

My Linked-In post on this topic.
.
.
.

OTN Appreciation Day : Undo and Redo

On OTN Appreciation Day, let me say I like the Undo and Redo features of Oracle.  I name them together as they work together.

Undo also supports MultiVersionReadConsistency -- a great advantage of Oracle.

Redo, with Archive Logging, also supports Online Backups -- an absolute necessity.

These features have been around for almost 30 years now.

Here are some Quick and Rough Notes on Undo and Redo .
.
.
.

Undo and Redo

Quick and Rough Notes :

Undo and Redo


Undo is where Oracle logs how to reverse a transaction (one or more DMLs in a transaction)

Redo is where Oracle logs how to replay a transaction

Undo and Redo are written to as the transaction proceeds, not merely at the end of the transaction
(imagine a transaction that consists of 1million single-row inserts,  each distinct insert is written to undo and redo)

Undo segments
Oracle dynamically creates and drops Undo segments depending on transaction volume
An undo segment consists of multiple extents.  As a transaction grows beyond the current extent, a new extent may be allocated
One undo segment can support multiple transactions but a transaction cannot span multiple undo segments
After COMMIT the undo information is retained for undo_retention or autotuned_undo_retention.  
At the end of the retention period, the undo is discarded, the extent is expired 

Undo retention
Oracle may autotune the undo retention
If the datafile(s) for the active undo tablespace are set to autoextend OFF, Oracle automatically uses the datafile to the fullest and ignores undo_retention
If the datafile(s) are set to autoextend ON, Oracle autotunes undo_retention to match query lengths
Check V$undostat for this information

Undo and Read Consistency
Oracle's implementation of MultiVersionReadConsistency relies on a user session being able to read the undo generated by another session
A session may need to read the prior image of data because the data has been modified (and may even have been commited) by another session
It clones the current version of the block it is reading and applies the undo for that block to create its read consistent version
Flashback Query is supported by reading from Undo


Isolation levels (READ COMMITTED, SERIALIZABLE, READ ONLY) 
Read Consistency with READ COMMITTED is at *statement* level by default
A session running multiple queries may each read a different version by default because Read Committed is enforced for each statement
(This also means that if you have a PLSQL block running the same SQL multiple times, each execution can see a different version of the data-- if the data is modified by another session between executions of the SQL !)
A session can choose to set it's ISOLATION LEVEL to SERIALIZABLE which means that every query sees the same version of data
This works only for short running queries and with few changes to the data or read only data.
SERIALIZABLE can update data provided that the same data hasn't been updated and committed by another session after the start (else you get ORA-08177)
READ ONLY does not allow the session to make changes 

Transactions
When a transaction is in progress, it is identified by the Transaction Address, Undo segment, slot and sequence
The ITL slot in the block header contains the reference (address) to the Undo
The SCN is assigned at commit time (therefore a transaction doesn't begin with an SCN)

Temp Undo
12c also allows temporary undo
Normally, changes to GTT generate undo which needs to be written to undo segments
With 12c temp undo, those undo entries are also, like the actual changes, temporary and can be discarded when the commit is issued
Thus, the undo doesn't need to be written to disk (remember data in a GTT is not visible to another session, so there is no need to persist the undo)
Redo also captures Undo
One transaction (or multiple concurrent transactions) may have updated multiple database blocks
So, DBWR may have written down some of the modified buffers to disk, even before the transaction COMMIT has been issued
This means that some of the blocks on disk may have uncomitted changes
What happens if the instance were to fail (e.g. a bug takes down a background process or the server crashes due to an OS bug or a CPU failure ?)
On instance recovery, Oracle must identify the uncommited transactions and roll them back
But if the undo for that was only in memory and was lost on instance/server failure, how can Oracle rollback the uncomitted transaction ?
Oracle knows that it must "undo" modified blocks
This is done by protecting the undo through the redo as well
Before a modified buffer is written to disk by DBWR, LGWR writes the redo for it
That redo also captures the undo
This ensures that, on the need to do Instance Recovery or Media Recovery, the undo is also available
The Rollforward process writes the undo to the undo segments
This allows Oracle to rollback the uncommitted transaction because the undo is now on disk (and not lost from memory)

Redo Strands
Redo consists of multiple strands 
Since 10g, Oracle has introduced private strands for single-instance databases
This allows a process to manage it's private strand of redo until it decides to commit
At commit time, the private strand is written into the public redo area and this allows LGWR to flush the redo to disk

IMU
Similarly, Oracle also manages undo "in memory" (using IMU pools).  
This means that, for a short period or small transactions, Undo is managed in memory rather than through undo segments
Therefore, Oracle doesn't have to track undo segment changes in the redo
This also allows bundling the undo for multiple changes into a single redo record, instead of separate redo records

RAC
In RAC, every instance has (a) a seperate Redo Thread  (b) a separate Undo Tablespace
However, the redo thread must be readable by every other instance -- as instance recovery by another (surviving) instance needs to read the redo
Similarly, the undo tablespace is read by any other instance because queries in instance 2 may need to read undo of instance 1 for read-consistency

Obj# and DataObj# in 12c AWR

The 12c AWR Report section on Segment Statistics now reports both Obj# (OBJECT_ID) and DataObj# (DATA_OBJECT_ID).  This is useful information when you have (table) objects undergoing TRUNCATEs during the workload.
A TRUNCATE (or a MOVE {or REBUILD for an Index}) causes a reallocation of the data segment and change of the DATA_OBJECT_ID.

Thus :

SQL> show user
USER is "HEMANT"
SQL> create table hkc_t_1 (id_col number);

Table created.

SQL> select object_id, data_object_id     
  2  from user_objects
  3  where object_name = 'HKC_T_1'
  4  and object_type = 'TABLE'
  5  /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     94422          94422

SQL> insert into hkc_t_1 values (1);

1 row created.

SQL> truncate table hkc_t_1;

Table truncated.

SQL> select object_id, data_object_id
  2  from user_objects
  3  where object_name = 'HKC_T_1'
  4  and object_type = 'TABLE'
  5  /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     94422          94423

SQL> 

The 12c AWR report shows both the Obj# and DataObj#..

In my lab, I created a workload where two tables were TRUNCATED 100 times in a loop that would insert fresh rows after each TRUNCATE.  (Those familiar with Peoplesoft Batch Jobs would know this behaviour)

Some extracts from the AWR show :

Segments by Physical Writes               DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> Total Physical Writes:         340,305
-> Captured Segments account for    0.7% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                            Physical
Object Name            Name     Type        Obj#   Dataobj#       Writes  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT               HEMANT
SOURCE_TB_1                     TABLE      94220      94220        1,575     .46
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94231          263     .08
HEMANT               USERS
WORKLOAD_LOG                    TABLE      94221      94221           71     .02
SYS                  SYSTEM
COL$                            TABLE         21          2           46     .01
SYS                  SYSTEM
SEG$                            TABLE         14          8           45     .01
                          ------------------------------------------------------

Segments by Physical Write Requests       DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> Total Physical Write Requests:         175,206
-> Captured Segments account for   22.3% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                          Phys Write
Object Name            Name     Type        Obj#   Dataobj#     Requests  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94370        1,086     .62
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94234          983     .56
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94228          981     .56
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94232          971     .55
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94218          964     .55
                          ------------------------------------------------------

Segments by Table Scans                   DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> Total Table Scans:             243
-> Captured Segments account for   18.9% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                               Table
Object Name            Name     Type        Obj#   Dataobj#        Scans  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94240            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94248            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94388            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94224            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94234            1     .41
                          ------------------------------------------------------

Segments by DB Blocks Changes             DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                            DB Block    % of
Object Name            Name     Type        Obj#   Dataobj#      Changes Capture
-------------------- ---------- ----- ---------- ---------- ------------ -------
** MISSING **        TEMP
** MISSING: -4001635 MISSING ** UNDEF 4.2550E+09    4218752       10,032    2.89
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94235        7,616    2.20
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94231        7,488    2.16
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94403        7,392    2.13
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94314        7,360    2.12
                          ------------------------------------------------------

These are the two target tables (with the current DATA_OBJECT_ID after 100 TRUNCATEs each) :

SQL> select object_name, object_id, data_object_id
  2  from user_objects
  3  where object_name like 'LIST_TB_%'
  4  and object_type = 'TABLE'
  5  order by 1
  6  /

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
LIST_TB_1                           94218          94418
LIST_TB_2                           94219          94419

SQL> 

The fact that the AWR report shows a different Dataobj# from the Obj# indicates that a TRUNCATE may have occurred sometime in the past (The TRUNCATE may have occurred before the beignning of the AWR report window !).  Multiple entries for the same table indicate that multiple TRUNCATES occurred within the AWR report window.
Note : Although both tables underwent 100 TRUNCATEs in the workload, AWR reports only 5 occurrences of activity.
.
.
.