Flashback in Oracle Database refers to a set of powerful data recovery features that allow database administrators and users to view past states of data or restore data to a previous state without using more complex backup and restore methods.
These features are valuable for addressing logical errors, such as accidental deletions or modifications of data.

Flashback features:

Flashback Query: Retrieve data as it appeared at a previous point in time using a simple SQL query (AS OF clause).
Flashback Table: Restore a table to a specific point in time to undo unwanted changes.
Flashback Transaction Query: View changes made by a specific transaction and its history.
Flashback Transaction: Roll back a transaction and its dependent transactions to a previous state.
Flashback Drop: Recover dropped tables using the Recycle Bin.
Flashback Database: Move the entire database back to a specific time, reversing changes across the database.

Oracle Database 9i (2001):

Flashback Query was introduced as the first Flashback feature.

Oracle Database 10g (2003):

Flashback Table, Flashback Drop, Flashback Database, Flashback Version Query and Flashback Transaction Query.

Oracle Database 11g (2007):

Flashback Data Archive (also known as Total Recall): This feature introduced the ability to keep historical versions of data for long-term auditing and compliance purposes. Data retention policies could be set, and historical data could be queried as needed.

Oracle Database 12c (2013):

Improved integration with pluggable databases (PDBs) and container databases (CDBs), extending Flashback features to multi-tenant environments.

Oracle Database 19c:

Flashback PDB: A more granular approach allowing individual pluggable databases to be flashed back, without affecting the entire container database.

Oracle Database 21c and beyond:

Continued optimization in terms of flashback performance and integration with other Oracle high availability and data protection features. Better handling and automation in managing flashback logs and the flash recovery area, ensuring a smoother user experience and more effective data management.

Flashback Configuration:

select name,open_mode,FLASHBACK_ON,LOG_MODE from v$database;

Enable the Archive:

shut immediate;
startup mount;
archive log list;
alter database archivelog;
archive log list;

Setting Flashback retention in Minutes:

alter system set db_flashback_retention_target=1440;
(OR)
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=10080 SCOPE=BOTH; -- Set retention for 7 days

alter system set db_recovery_file_dest_size=10G;
alter system set db_recovery_file_dest='D:\orabase\fast_recovery_area';

Configure UNDO Retention:

alter system set undo_retention=43200;

Enable the Flashback:

alter database flashback on;
alter database open;
select name,open_mode,FLASHBACK_ON,LOG_MODE from v$database;
show parameter db_recovery_file;

Flashback Using clause:

To SCN
To Timestamp
To Restore Point

Find Current SCN:

select current_scn from v$database;

SCN to timestamp:

select scn_to_timestamp(4345444) from dual;

timestamp to SCN:

select timestamp_to_scn('09-apr-20 11:09:00 PM') from dual;

Flashback Query :

That allows users to query and view the state of data as it existed at a previous point in time.
This feature is particularly useful for auditing, investigating historical data, and recovering from logical errors without disrupting the current state of the database.

select * from flash_pearl_tab1 as of scn 10345205;
select * from flash_pearl_tab1 as of timestamp scn_to_timestamp(10345205);
select * from flash_pearl_tab1 as of timestamp to_date('2018-10-09 12:29:40','YYYY-MM-DD HH24:SS:MI');
select * from flash_pearl_tab1 as of scn timestamp_to_scn(to_date('2018-10-09 12:29:40','YYYY-MM-DD HH24:SS:MI'));
SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2024-11-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE department_id = 10;
SELECT * FROM sales AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY;
SELECT * FROM sales AS OF TIMESTAMP (sysdate - INTERVAL '10' minute;

Finally:

Insert into emp SELECT * FROM sales AS OF TIMESTAMP (sysdate - INTERVAL '10' minute;

Flashback Version Query:

Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval.
A new row version is created whenever a COMMIT statement is executed.
The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify.
Each row in the table includes pseudo columns of metadata about the row version. The pseudo columns available are

VERSIONS_XID          :Identifier of the transaction that created the row version
VERSIONS_OPERATION    :Operation Performed. I for Insert, U for Update, D for Delete
VERSIONS_STARTSCN     :Starting System Change Number when the row version was created
VERSIONS_STARTTIME    :Starting System Change Time when the row version was created
VERSIONS_ENDSCN       :SCN when the row version expired.
VERSIONS_ENDTIME      :Timestamp when the row version expired

Create table emp (empno number(5),name varchar2(20),sal number(10,2));
insert into emp values (101,'Sami',5000);
commit;
update emp set sal=sal-3000 where empno=101;
commit;
update emp set name='Smith' where empno=101;
commit;

column versions_starttime format a16
column versions_endtime format a16
set linesize 120;

select versions_xid,versions_starttime,versions_endtime,versions_operation,empno,name,sal from emp versions between timestamp to_timestamp('2007-06-19 20:30:00','yyyy-mm-dd hh:mi:ss')and to_timestamp('2007-06-19 21:00:00','yyyy-mm-dd hh:mi:ss');

Flashback Transaction Query

It is an Oracle Database feature that provides insights into past transactions by displaying detailed information about changes made by specific transactions. This feature is valuable for auditing and for understanding the impact of specific database operations over time.

Flashback Transaction Query Purpose:

It helps administrators and users analyze changes made to the database by a specific transaction.
Useful for auditing, investigating data anomalies, and tracing the origin of changes.
Can be a precursor for corrective measures, such as using Flashback Transaction to roll back transactions.

The FLASHBACK_TRANSACTION_QUERY view provides:

XID (Transaction ID): Unique identifier for the transaction.
UNDO_SQL: SQL statements to undo the changes made by the transaction.
OPERATION: Type of DML operation (INSERT, UPDATE, DELETE).
TABLE_NAME: Name of the table affected by the transaction.
START_TIMESTAMP and COMMIT_TIMESTAMP: Timestamps indicating when the transaction started and was committed.
LOGON_USER: The user who executed the transaction.

select operation,logon_user,undo_sql from flashback_transaction_query where xid=HEXTORAW('02001003C02');
update emp set sal=5000 where ROWID ='AAAKD2AABAAAJ29AAA';

SELECT xid, operation, undo_sql, table_name, logon_user, commit_timestamp FROM flashback_transaction_query WHERE table_name = 'EMPLOYEES' AND logon_user = 'HR' AND commit_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY;

Flashback Table:

It is an Oracle Database feature that allows you to restore a table to a previous state, effectively undoing changes made to it. This feature is particularly useful for correcting accidental data modifications, such as erroneous updates, deletions, or inserts, without having to restore the entire database or table from a backup.

ALTER TABLE emp ENABLE ROW MOVEMENT;

FLASHBACK TABLE table_name TO { SCN | TIMESTAMP | RESTORE POINT restore_point_name };

FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2007-06-19 09:30:00', `YYYY-MM-DD HH24:MI:SS');

FLASHBACK TABLE emp TO TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;

FLASHBACK TABLE emp TO RESTORE POINT before_update;

FLASHBACK TABLE emp TO SCN 123456789;

SELECT commit_scn, xid, operation, table_name, logon_user, commit_timestamp FROM flashback_transaction_query WHERE table_name = ‘ORDERS’ ORDER BY commit_timestamp DESC;

FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2007-06-21 14:00:00','YYYY-MM-DD HH:MI:SS') ENABLE TRIGGERS;

-- ENABLE TRIGGERS option otherwise, by default all database triggers on the table will be disabled.

Flashback Drop:

It is an Oracle Database feature that allows you to recover a table that has been accidentally dropped, using the Recycle Bin functionality. When a table is dropped in Oracle, it isn't immediately deleted from the database but rather moved to the Recycle Bin, making it possible to recover it without needing to restore from a backup. This feature is a part of Oracle's Flashback Technology and is especially useful for recovering accidentally dropped objects.

drop table emp;

show recyclebin;
FLASHBACK TABLE emp TO BEFORE DROP;
drop table emp;
purge table emp;
drop table emp purge; --Permanently Dropping Tables

select object_name, original_name, dropscn, droptime from user_recyclebin;

Restoring a Dropped Table with a Different Name:

FLASHBACK TABLE employees TO BEFORE DROP;
RENAME employees TO employees_backup;

Flashback table emp to before drop rename to emp2;

Purging Objects from Recycle Bin

If you want to recover the space used by a dropped table give the following command

SQL> purge table emp;

If you want to purge objects of logon user give the following command

SQL> purge recycle bin;

If you want to recover space for dropped objects of a particular tablespace give the command

SQL> purge tablespace hr;

You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:

SQL> PURGE TABLESPACE hr USER scott;

If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:

SQL> PURGE DBA_RECYCLEBIN;

To view the contents of Recycle Bin give the following command

SQL> show recycle bin;

Flashback Drop of Multiple Objects With the Same Original Name:

You can create, and then drop, several objects with the same original name, and they will all be stored in the recycle bin. For example, consider these SQL statements:

CREATE TABLE EMP ( ...columns ); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 3
DROP TABLE EMP;

In such a case, each table EMP is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in this example:

FLASHBACK TABLE EMP TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name. You can retrieve it and assign it a new name using a RENAME TO clause. The following example shows the retrieval from the recycle bin of all three dropped EMP tables from the previous example, with each assigned a new name:

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_1;

Flashback Database: (NON-CDB)

Quickly recover the entire database from logical data corruptions or user errors.

To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.

Once you set these parameters, From that time on, at regular intervals, the database copies images of each altered block in every datafile into flashback logs stored in the flash recovery area. These Flashback logs are use to flashback database to a point in time.

To how much size we should set the flash recovery area.

SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

To determine the earliest SCN and earliest Time you can Flashback your database, give the following query:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

shutdown immediate;
startup mount;

FLASHBACK DATABASE TO SCN 202381; ## this is for particular scn

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); ## This is for flashback to last one hour

FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2023-08-17 16:00:00', 'YYYY-MM-DD HH24:MI:SS'); ## This is to specific timestamp.

alter database open resetlogs;

Flashback database to restore point:

create restore point FLASHBACK_PREP;
create restore point FLASHBACK_PREP1 guarantee flashback database;
create restore point FLASHBACK_PREP2 guarantee flashback database;
create restore point FLASHBACK_PREP3 guarantee flashback database;

Find the list of restore points:

select * from v$restore_points; -- From SQL prompt

RMAN>LIST RESTORE POINT ALL; -- From RMAN prompt

shutdown immediate;
startup mount;

flashback database to restore point FLASHBACK_PREP2;

alter database open resetlogs;

Drop the Restore Point:

drop restore point FLASHBACK_PREP3;

Flashback Database: (CDB-Level):

-- Normal restore point.

CREATE RESTORE POINT cdb1_before_changes;
DROP RESTORE POINT cdb1_before_changes;

-- Guaranteed restore point.

CREATE RESTORE POINT cdb1_before_changes GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT cdb1_before_changes;

CONN / AS SYSDBA

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT cdb1_before_changes;
ALTER DATABASE OPEN RESETLOGS;

-- Open all pluggable databases.

ALTER PLUGGABLE DATABASE ALL OPEN RESETLOGS;

Other flashback methods:

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;
FLASHBACK DATABASE TO RESTORE POINT my_restore_point;

Flashback database: (PDB Level)

ALTER SESSION SET CONTAINER=pdb1;

-- Normal restore point.

CREATE RESTORE POINT pdb1_before_changes;
DROP RESTORE POINT pdb1_before_changes;

-- Guaranteed restore point.

CREATE RESTORE POINT pdb1_before_changes GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT pdb1_before_changes;

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT pdb1_before_changes;
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

Other Flashback methods:

FLASHBACK PLUGGABLE DATABASE pdb1 TO TIMESTAMP my_date;
FLASHBACK PLUGGABLE DATABASE pdb1 TO BEFORE TIMESTAMP my_date;
FLASHBACK PLUGGABLE DATABASE pdb1 TO SCN my_scn;
FLASHBACK PLUGGABLE DATABASE pdb1 TO BEFORE SCN my_scn;
FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT my_restore_point;

Flashback Data archive:

Flashback Data archive (FDA) feature introduced in Oracle 11g for undo-based flashback operations, and it is configured using retention time.
Flashback data archive supported for multi-tenant (12.1.0.2 and above versions) as well using local or shared undo configuration.
Using Flashback Data archive, you can track Database changes in a secure and efficient way. It provides centralized historical data store by efficiently using the resources.

create table fb_table (id number, text varchar2(20));
alter table fb_table enable row movement;
create tablespace fb_tbs datafile 'fb_tbs.dbf' size 10m;

enable the flashback archive:

create flashback archive f1_arch tablespace fb_tbs retention 1 year;

Add more flashback policy:

alter flashback archive f1_arch retention 2 year;

drop flashback archive f1_arch;

delete flashback archive files older than 10 days:

alter flashback archive f1_arch purge before timestamp (systimestamp - interval '10' day);

Add the table:

alter table fb_table flashback archive f1_arch;

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

insert into fb_table values (1,'A');
commit;

select current_scn, controlfile_time from v$database;

insert into fb_table values (2,'B');
commit;

select * from fb_table;
flashback table fb_table to scn <BEFORE_INSERT_SCN>;
select * from fb_table;

select * from fb_table;
select current_scn, controlfile_time from v$database;

insert into fb_table values (3,'C');
commit;

select * from fb_table;
flashback table fb_table to timestamp <TIME_BEFORE_INSERT>;
select * from fb_table;

select flashback_archive_name from dba_flashback_archive_tables where table_name='FB_TABLE';

drop table fb_table;

alter table fb_table no flashback archive;

drop table fb_table;

Oracle 12c new features:

1-User context tracking

This new feature allows tracking the user context and makes it easier to know which user made which changes in a table. To track user-contexts, Oracle has created two new subprograms in the DBMS_FLASHBACK_ARCHIVE package as well as a new table.

set_sys_context procedure:

This procedure allows to set the level of user-context to return. It has only one parameter.

ALL: Obtain all information from sys_context
TYPICAL: Provides the user id, the global user id and the hostname
NONE: Nothing

exec dbms_flashback_archive.set_context_level(level=> 'ALL');

Create a custom context and management package for use in the test:

CREATE OR REPLACE CONTEXT test_context USING test_ctx_api;

CREATE OR REPLACE PACKAGE test_ctx_api AS
PROCEDURE set_value (p_name IN VARCHAR2, p_value IN VARCHAR2);
END test_ctx_api;
/

CREATE OR REPLACE PACKAGE BODY test_ctx_api AS
PROCEDURE set_value (p_name IN VARCHAR2, p_value IN VARCHAR2) AS
BEGIN
DBMS_SESSION.set_context('test_context', LOWER(p_name), p_value);
END;
END test_ctx_api;
/

Example:

EXEC DBMS_SESSION.set_identifier('Peter Pan');
EXEC test.test_ctx_api.set_value('my_attribute','First Action');

INSERT INTO tab1 VALUES (1, 'ONE');
COMMIT;

EXEC DBMS_SESSION.set_identifier('Peter Parker');
EXEC test_ctx_api.set_value('my_attribute','Second Action');

UPDATE tab1
SET    description = 'TWO'
WHERE id = 1;
COMMIT;

EXEC DBMS_SESSION.set_identifier('Peter Rabbit');
EXEC test_ctx_api.set_value('my_attribute','Third Action');

UPDATE tab1
SET    description = 'THREE'
WHERE id = 1;
COMMIT;

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
COLUMN session_user FORMAT A20
COLUMN client_identifier FORMAT A20
COLUMN my_attribute FORMAT A20
SET LINESIZE 200

SELECT versions_startscn,--versions_starttime,
versions_endscn, --versions_endtime,
versions_xid,
versions_operation,
description,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute
FROM   tab1 VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24/60) AND SYSTIMESTAMP
WHERE id = 1 ORDER BY versions_startscn;

SELECT versions_startscn,versions_endscn,versions_endtime,versions_xid,versions_operation,description,DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute
FROM   tab1 VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24/60) AND SYSTIMESTAMP
WHERE id = 1 ORDER BY versions_startscn;

2)-Database hardening

With Oracle 11g, FDA had to be managed at table level. Starting with Oracle 12c, it is possible to create logical groups of tables for an application: Enabling or disabling of FDA is performed at application level and it prevents the listing of all application tables to enable or disable FDA for each one.

create and enable an application group:

exec dbms_flashback_archive.register_application(application_name=>'PRODUCTS',flashback_archive_name=>'FBA1');

select table_name, flashback_archive_name, archive_table_name, status from DBA_FLASHBACK_ARCHIVE_TABLES;

Add the table:

exec dbms_flashback_archive.add_table_to_application(application_name=>'PRODUCTS',table_name=>'TB_PRODUCTS',schema_name=>'MSC');

List out the application name and tables:

select a.appname, b.object_name, c.obj# from SYS_FBA_APP a, DBA_OBJECTS b, SYS_FBA_APP_TABLES c where a.app#=c.app# and b.object_id=c.obj#;

Enable the Application:

exec dbms_flashback_archive.enable_application(application_name=>'PRODUCTS');

select table_name, flashback_archive_name, archive_table_name, status from DBA_FLASHBACK_ARCHIVE_TABLES;

exec DBMS_FLASHBACK_ARCHIVE.REMOVE_TABLE_FROM_APPLICATION (application_name=> 'ORACLERACEXPERT', table_name=> 'EMP' , schema_name => 'ORASCH');

exec DBMS_FLASHBACK_ARCHIVE.disable_application( application_name => 'ORACLERACEXPERT');

Oracle Flashback

Flashback features:

Oracle Database 9i (2001):

Oracle Database 10g (2003):

Oracle Database 11g (2007):

Oracle Database 12c (2013):

Oracle Database 19c:

Oracle Database 21c and beyond:

Flashback Configuration:

Enable the Archive:

Setting Flashback retention in Minutes:

Configure UNDO Retention:

Enable the Flashback:

Flashback Using clause:

Find Current SCN:

SCN to timestamp:

timestamp to SCN:

Flashback Query :

Finally:

Flashback Version Query:

Flashback Transaction Query

Flashback Transaction Query Purpose:

The FLASHBACK_TRANSACTION_QUERY view provides:

Flashback Table:

Flashback Drop:

drop table emp;

Restoring a Dropped Table with a Different Name:

Purging Objects from Recycle Bin

Flashback Drop of Multiple Objects With the Same Original Name:

FLASHBACK TABLE EMP TO BEFORE DROP;

Flashback Database: (NON-CDB)

Flashback database to restore point:

Find the list of restore points:

Drop the Restore Point:

Flashback Database: (CDB-Level):

-- Normal restore point.

-- Guaranteed restore point.

-- Open all pluggable databases.

Other flashback methods:

Flashback database: (PDB Level)

-- Normal restore point.

-- Guaranteed restore point.

Other Flashback methods:

Flashback Data archive:

enable the flashback archive:

Add more flashback policy:

delete flashback archive files older than 10 days:

Add the table:

Oracle 12c new features:

1-User context tracking

set_sys_context procedure:

2)-Database hardening

create and enable an application group:

Add the table:

List out the application name and tables:

Enable the Application:

Comments

Post a Comment