Flashback Data Archive - New Feature Oracle Database 11g

By -

Set Up and Use a Flashback Data Archive

Retaining a historical record of all changes to rows in critical tables is error prone and requires custom applications or database triggers to maintain repositories for the historical changes. Every time you create a new application or update a table in an application that requires historical tracking, you must make changes to your tracking application as well. You can use Flashback Data Archive to save historical changes automatically to all key tables for as long as possible.

Understanding Flashback Data Archive

In Flashback Data Archive you create one or more repository areas (dedicated tablespace) (one of which can be the default), assign a default retention period for objects in the repository, and then mark the appropriate tables for tracking.

A Flashback Data Archive acts much like an undo tablespace. However, a Flashback Data Archive records only UPDATE and DELETE statements but not INSERT statements. In addition, undo data is typically retained for a period of hours or days for all objects. Rows in Flashback Data Archives can span years or even decades.

Creating an Archive

You can create one or several Flashback Data Archives in existing tablespaces using the CREATE FLASHBACK ARCHIVE command. However, Oracle best practices recommend that you use dedicated tablespaces. All archives must have a default retention period using the RETENTION clause and can optionally be identified as the default archive using the DEFAULT keyword. The disk quota in an archive is limited by the disk space within the tablespace, unless you assign a maximum amount of disk space in the archive using the QUOTA keyword.

In this example, you first create a dedicated tablespace for your Flashback Data Archive:

 CREATE tablespace sample datafile 'C:\sample01.dbf' SIZE 10g;

Or you can use the inbuild tablespace USERS

Next you create your flashback archive in the desired tablespace as shown below.

 create flashback archive test tablespace users retention 1 year;

Data Dictionary Views of Flashback Data Archive

 col FLASHBACK_ARCHIVE_NAME format a20  
 select flashback_archive_name, flashback_archive#, retention_in_days, status from dba_flashback_archive;  
 FLASHBACK_ARCHIVE_NA      FLASHBACK_ARCHIVE#      RETENTION_IN_DAYS      STATUS  
 ---------------------------------------------------------------------------------------------------------------------------  
 TEST                                     1                 365  


 select * from dba_flashback_archive_ts;  
 FLASHBACK_ARCHIVE_NA     FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB  
 TEST                           1             USERS       


 alter table scott.emp flashback archive test;  


 col owner_name format a5  
 col ARCHIVE_TABLE_NAME format a30  
 select * from dba_flashback_archive_tables;  
 TABLE_NAME   OWNER       FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS  
 ----------------------------------------------------------------------------------------------------------------------  
 EMP          SCOTT       TEST              SYS_FBA_HIST_81465      ENABLED  




 select to_char(current_timestamp-2/1440,'DD-MON-YYYY HH24:MI:SS') from dual;  
 TO_CHAR(CURRENT_TIME  
 --------------------------------------  
 18-DEC-2014 13:48:51
  
 delete from scott.emp where empno = 1;  
 commit;


If we want to see the deleted row again.


www.unisoftindia.org

Comments

Post a Comment

Popular posts from this blog

Oracle Architecture - Explained In Detailed - Administration I

By -
Image
Figures shown in these notes are from Oracle® Database Concepts 11 g Release 2 Objectives These notes introduce the Oracle server architecture.   The architecture includes physical components, memory components, processes, and logical structures. Primary Architecture Components The figure shown above details the Oracle architecture. Oracle server :   An Oracle server includes an Oracle Instance and an Oracle database .   ·         An Oracle database includes several different types of files:   datafiles, control files, redo log files and archive redo log files.   The Oracle server also accesses parameter files and password files.   ·         This set of files has several purposes.   o    One is to enable system users to process SQL statements.   o    Another is to improve system performance.   o    Still another is to ensure the database can be recovered if there is a software/hardware failure. ·         The database server must manage large amo
Read more

Oracle Database SQL - Practise - Question - Scott Schema Examples

By -
Image
Oracle SQL Practice Questions for Students All Question are Based on Scott Schema Exhibits EMP TABLE Exhibits DEPT TABLE Exhibits SALGRADE TABLE Question 1) Display all the records in emp table? 2) Display all the records in emp table where employee belongs to deptno 10? 3) Display all the records in emp table where employee does not belong to deptno 30? 4) Display total number of records in Emp table? 5) Display emp table with salary descending order? 6) Display first five records in employee table? 7) Display all the records in emp table order by ascending deptno, descending salary? 8) Display all employees those who were joined in year 1981? 9) Display COMM in emp table. Display zero in place of null. 10) Display the records in emp table where MGR in 7698,7566 and sal should be greater then 1500 11) Display all employees where employees hired before 01-JAN-1981 12) Display all employees with
Read more

Step by Step - How to resolve redo log file corruption using ALTER DATABASE CLEAR UNARCHIVED LOGFILE command

By -
Image
Connect to sys using sqlplus in admin command prompt Check whether your database is in archive log mode or not. Run command ARCHIVE LOG LIST. It will show you the following details: It is always preferable to have your database in archive log mode. The reason being if your database is in archive log mode you can recover from all committed changes in the event of an OS or disk failure. This query will display the path of redo log files, their group and their status. It is always recommended to have a minimum of two members in one group. As you can see I have only 1 member in group 6 whose current status is INACTIVE. I intentionally have 1 member to generate a scenario for the sake of this practical. Now go to the specified path where the redo log member of group 6 is located and open it in a notepad. It will show some symbolic data. Delete couple of lines and save it the original location which is the one we got as a result of query. Why did we do the above step i.
Read more