Flashback Data Archive in Oracle 12C Database

The purpose of Flashback Data Archive is to store all DML and transaction on the important tables in a different tablespace and see the changes whenever you want.

This feature was used before the log mineral feature. But thanks to this feature we can see faster. The flashback data archiver process is running in the background while using this feature.

Let’s go to make an example about Flashback Data Archive to learn this feature very well. 

Firstly create tablespace for storing Flashback Data Archive objects.

 

CREATE TABLESPACE Archive DATAFILE

‘/u01/oracle/recovery/arc_01.DBF’ SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 10000M

Tablespace created.

 

Let’s define the tablespace as flashback data archive space and limit it to 10 Gb.

 

create flashback archive flashback_archive tablespace Archive quota 1G retention;

 

Lets enable our tables to flashback archive to be saved in this field.

 

alter table test flashback archive flashback_archive;

 

 

Let’s do an update on the table and then see how the previous version is stored and how to access it.

select owner, segment_name from test; 

 

OWNER, SEGMENT_NAME

TEST LOGMNR_I1COL$

TEST LOGMNR_I2COL$

TEST LOGMNR_I3COL$

TEST LOGMNR_I1ATTRCOL$

TEST LOGMNR_I1TS$

 

update test set owner = 'USER';

 

commit;

 

The history of all DML operations on this table will now be stored in this tablespace.

 

select owner, segment_name from deveci.test as of timestamp sysdate-2/1440;

 

OWNER SEGMENT_NAME

TEST LOGMNR_I1COL$

TEST LOGMNR_I2COL$

TEST LOGMNR_I3COL$

TEST LOGMNR_I1ATTRCOL$

TEST LOGMNR_I1TS$

 

If we you want to remove the table from flashback archive mode and delete all the history data, execute following code.

alter table deveci.test no flashback archive;

If You want to delete all of the flashback data archives, you can delete it as follows.

Alter flashback archive purge all

 

The following command is executed to delete before specific timestamp or SCN.

Alter flashback archive purge before scn | timestamp

 

Flashback restore point

Step1:Startup database and Create Table 

[oracle@trichy ~]$ sqlplus / as sysdba

 SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 11 22:41:53 2020

 Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> startup

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

SQL> create table product(id int,name varchar2(20));

 Table created.

 SQL> insert into product values(111,'inba');

 1 row created.

 SQL> insert into product values(222,'siva');

 1 row created.

 SQL> insert into product values(333,'shan');

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> select * from product;

 ID NAME

---------- --------------------

       111 inba

       222 siva

       333 shan

 Step2:To create Restore point

 SQL> desc V$RESTORE_POINT;

 Name    Null?    Type

 ----------------------------------------- -------- ----------------------------

 SCN     NUMBER

 DATABASE_INCARNATION#     NUMBER

 GUARANTEE_FLASHBACK_DATABASE     VARCHAR2(3)

 STORAGE_SIZE     NUMBER

 TIME     TIMESTAMP(9)

 RESTORE_POINT_TIME     TIMESTAMP(9)

 PRESERVED     VARCHAR2(3)

 NAME     VARCHAR2(128)

 PDB_RESTORE_POINT     VARCHAR2(3)

 CLEAN_PDB_RESTORE_POINT     VARCHAR2(3)

 PDB_INCARNATION#     NUMBER

 CON_ID

 SQL> create restore point  respt_inba  guarantee flashback database;

 Restore point created.

 Step3:To drop the Table

 SQL> drop table product purge;

 Table dropped.

 SQL> select * from product;

select * from product

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Step4:To recover the table using restore point

 SQL> startup mount

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> flashback database to restore point respt_inba;

 Flashback complete.

 SQL> alter database open resetlogs;

 Database altered.

 SQL> select * from product;

 ID NAME

---------- --------------------

       111 inba

       222 siva

       333 shan

 Screen shots

                                                               

 

 

 

 

Recover User and Table using Flashback Database

FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. 

Flashback must be enabled before any flashback operations are performed.

If the database is in NO ARCHIVE LOG it must be switched to ARCHIVE LOG mode.

To Turn on Flash back:-

[oracle@trichy ~]$ sqlplus / as sysdba

 SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 10 23:34:25 2020

 Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> startup

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

SQL> select flashback_on from v$database;

 FLASHBACK_ON

------------------

NO

 SQL> archive log list;

Database log mode        Archive Mode

Automatic archival        Enabled

Archive destination        /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch

Oldest online log sequence     14

Next log sequence to archive   16

Current log sequence        16

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT EXCLUSIVE

ORACLE instance started.

 

Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

ALTER DATABASE FLASHBACK ON

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38709: Recovery Area is not enabled.

 SQL> alter system set db_recovery_file_dest_size=10g scope=spfile;

 System altered.

 SQL> alter system set db_recovery_file_dest='/u01/arch' scope=spfile;

 System altered.

 SQL> Shut immediate

ORA-01109: database not open

 Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT EXCLUSIVE

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

 Database altered.

 SQL> ALTER DATABASE OPEN;

 Database altered.

 SQL> set time on;

23:46:53 SQL>

23:46:55 SQL> create user remo identified by remo;

 User created.

 23:47:12 SQL> grant connect,resource to remo;

 Grant succeeded.

 23:47:19 SQL> Øgrant all privileges to remo;

 Grant succeeded.

 23:47:39 SQL> conn remo/remo                   

Connected.

23:49:41 SQL> CREATE TABLE t5 (id  NUMBER(10));

 Table created.

 23:51:51 SQL> insert into t5 values(1);

 1 row created.

 23:52:01 SQL> /

 1 row created.

 23:52:03 SQL> /

 1 row created.

 23:52:03 SQL> commit;

 Commit complete.

 23:52:08 SQL> conn / as sysdba

Connected.

23:52:54 SQL>  SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6373386 2020-10-10 23:53:07

 23:53:07 SQL> drop user remo cascade;

 User dropped.

 23:53:39 SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

23:54:18 SQL> set time off

SQL> STARTUP MOUNT EXCLUSIVE

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> flashback database to timestamp to_date('2020-10-10 23:52:54','YYYY-MM-DD HH24:MI:SS');

 Flashback complete.

 SQL> ALTER DATABASE OPEN RESETLOGS;

 Database altered.

 SQL>

SQL>

SQL> conn remo/remo

Connected.

SQL> select * from t5;

 ID

----------

 1

 1

 1

 SQL>   

 Screen Shots 

                                                                       

Flashback Query & Version Query Concepts On Oracle 12c

 1)Flashback Query:(To retrieve the data from past existing Table not current data)(Don’t Drop the Table).

 Flashback Query allows the contents of a table to be queried with reference to a specific point in time,  using the AS OF clause.

 [oracle@trichy ~]$ export ORACLE_SID=test

[oracle@trichy ~]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@trichy ~]$ export ORACLE_BASE=/u01/app/oracle

[oracle@trichy ~]$ export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1

[oracle@trichy ~]$ sqlplus / as sysdba

 SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 10 00:00:43 2020

 Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> startup

ORACLE instance started.

 

Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

 SQL> create table emp(id int);

 Table created.

 SQL> SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6352947 2020-10-10 00:03:43

 SQL> insert into emp values(111);

 1 row created.

 SQL> /

 1 row created.

 SQL> /

 1 row created.

 SQL> /

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6353013 2020-10-10 00:06:26

 SQL> delete from emp;

 4 rows deleted.

 SQL> commit;

 Commit complete.

 SQL> SELECT COUNT(*) FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2020-10-10 00:03:43','YYYY-MM-DD HH24:MI:SS');

   COUNT(*)

----------

 0

 SQL> select * from emp;

 no rows selected

 SQL> SELECT COUNT(*) FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2020-10-10 00:06:26','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)

----------

 4

 SQL> SELECT * FROM emp as of scn 6352947;

 no rows selected

 SQL> SELECT * FROM emp as of scn 6353013;       

 ID

----------

       111

       111

       111

       111

       Screen shots                 

                                                 

2)Flashback Version Query

 Flashback version query allows the versions of a specific row to be tracked during a specific time period using the VERSIONS BETWEEN clause.

 SQL> CREATE TABLE emp1 (id  NUMBER(10),name  VARCHAR2(50));

 Table created.

 SQL> INSERT INTO emp1 (id, name) VALUES (1, 'dinesh');

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6356132 2020-10-10 00:41:48

 SQL> UPDATE emp1 SET name = 'raja' WHERE id = 1;

 1 row updated.

 SQL> COMMIT;

 Commit complete.

 SQL> UPDATE emp1 SET name = 'senthil' WHERE id = 1;

 1 row updated.

 SQL> COMMIT;

 Commit complete.

 SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6356817 2020-10-10 00:43:23

 SQL> 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

SET LINESIZE 200

SELECT versions_startscn, versions_starttime,

versions_endscn, versions_endtime,

versions_xid, versions_operation,

name  

FROM  emp1

VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2020-10-10 00:41:48', 'YYYY-MM-DD HH24:MI:SS')

AND TO_TIMESTAMP('2020-10-10 00:43:23', 'YYYY-MM-DD HH24:MI:SS')

WHERE  id = 1;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8  

 VERSIONS_STARTSCN VERSIONS_STARTTIME        VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID  V NAME

------------------ ------------------------ ------------------ ------------------------ ---------------- - --------------------------------------------------

   6356809 10-OCT-20 12.43.07 AM 0600020004160000 U senthil

   6356801 10-OCT-20 12.42.45 AM        6356809 10-OCT-20 12.43.07 AM 090004000D160000 U raja

   6356130 10-OCT-20 12.41.43 AM        6356801 10-OCT-20 12.42.45 AM 0200070028160000 I dinesh 

Screen shots