Rollback after commit:Flashback Architecture

 FlashBack  Architecture

There are a number of flashback levels

row level	flashback query, flashback versions query, flashback transaction query
table level	flashback table, flashback drop
database level	flashback database

What is FLASHBACK ?

past experience

1.) Why we need Flashback query?

Oracle introduced flashback features in Oracle 9i and 10g to address simple data recovery needs .Retrieves data from a past point in time Backup and recovery capabilities are provided by the database management systems which ensure the safety and protection of valuable enterprise data in case of data loss however, not all data-loss situations call for a complete and tedious recovery exercise from the backup.

2.) What needs to be done for the flash back query enable ?

Configuration before using Flashback Queries

In order to use this feature, the database instance has to be configured. 

SQL> show parameter UNDO;

NAME                    TYPE            VALUE
----------------------- --------------  -----------    
undo_management         string           AUTO
undo_retention          integer          900
undo_suppress_errors    boolean          TRUE
undo_tablespace         string           UNDO_TBSPC

What Is Undo?

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

·         Roll back transactions when a ROLLBACK statement is issued

·         Recover the database

·         Provide read consistency

·         Analyze data as of an earlier point in time by using Oracle Flashback Query

·         Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction.

What DBA has to do ?

To be able to use flashback query, we require the following system elements:

• undo_management=auto (set in pfile/spfile);
• undo_retention=n (set in pfile/spfile, where n is a positive number of seconds);
• undo_tablespace=[undo tablespace name] (set in pfile/spfile);
• FLASHBACK or FLASHBACK ANY system privilege; and
• EXECUTE on DBMS_FLASHBACK.

(Show Parameter UNDO)

This command displays all the necessary parameters for using the Flashback Queries

undo_retention 

Property	Description
Parameter type	String
Syntax	UNDO_MANAGEMENT = { MANUAL | AUTO }
Default value	MANUAL
Modifiable	No
Basic	Yes
Real Application Clusters	Multiple instances must have the same value.

After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo. If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

WARNING:

Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASEor CREATE UNDO TABLESPACE statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You disable retention guarantee with theRETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace. A column named RETENTION contains a value ofGUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace).

Setting the Undo Retention Period

Do one of the following:

• Set UNDO_RETENTION in the initialization parameter file.

·         UNDO_RETENTION = 1800

·          

• Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

·         ALTER SYSTEM SET UNDO_RETENTION = 2400;

The effect of an UNDO_RETENTION parameter change is immediate, but it can only be honored if the current undo tablespace has enough space.

undo_management

Property	Description
Parameter type	String
Syntax	UNDO_MANAGEMENT = { MANUAL | AUTO }
Default value	MANUAL
Modifiable	No
Basic	Yes
Real Application Clusters	Multiple instances must have the same value.

UNDO_MANAGEMENT specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.

undo_suppress_errors   

Parameter type	Boolean
Default value	False
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false

UNDO_SUPPRESS_ERRORS enables users to suppress errors while executing manual undo management mode operations (for example, ALTER ROLLBACK SEGMENT ONLINE) in automatic undo management mode. Setting this parameter enables users to use the undo tablespace feature before all application programs and scripts are converted to automatic undo management mode. For example, if you have a tool that uses SET TRANSACTION USE ROLLBACK SEGMENT statement, you can add the statement "ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true" to the tool to suppress the ORA-30019 error.

If you want to run in automatic undo management mode, ensure that your tools or applications are updated to run in automatic undo management mode.

undo_tablespace        

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. In this management mode, you create an undo tablespace. A default undo tablespace is then created at database creation. An undo tablespace can also be created explicitly,referred to as automatic undo management, for managing undo information and space. In this management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions.

When the instance starts, the database automatically selects the first available undo tablespace.If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.

If the database contains multiple undo tablespaces, you can optionally specify at startup that you want to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter, as shown in this example:

UNDO_TABLESPACE = undotbs_01

UNDO_TABLESPACE

An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

Managing Undo Tablespaces

This section describes the various steps involved in undo tablespace management and contains the following sections:

·         Creating an Undo Tablespace

·         Altering an Undo Tablespace

·         Dropping an Undo Tablespace

·         Switching Undo Tablespaces

·         Establishing User Quotas for Undo Space

In addition to the above your DBA will have to grant:

1.    FLASHBACK privilege to the user for all or a subset of objects

2.    Execute privileges on the dbms_flashback package

How it will work ? HANDS ON.

Using the Flashback Query with AS OF clause:

Using the AS OF clause with time

--1.Create table the employee table.

CREATE TABLE EMPLOYEE(EMP_ID  NUMBER,

NAME    VARCHAR2 (30)                ,         

AGE     NUMBER

)

--2.Have some records entered into the employee table.

INSERT INTO EMPLOYEE VALUES(1,'AAAAA',11);

INSERT INTO EMPLOYEE VALUES(2,'AAAAA',22);

INSERT INTO EMPLOYEE VALUES(3,'AAAAA',33);

INSERT INTO EMPLOYEE VALUES(4,'AAAAA',44);

INSERT INTO EMPLOYEE VALUES(5,'AAAAA',55);

COMMIT;

SELECT * FROM EMPLOYEE;

--3.Delete or update some or all of the records from the employee table and commit the transaction.

DELETE EMPLOYEE WHERE EMP_ID = 4;

COMMIT;

UPDATE EMPLOYEE SET EMP_ID = 100 WHERE EMP_ID = 3;

COMMIT;

SELECT * FROM EMPLOYEE;

--4 Select using Flashback query

  SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:38:58','DD-MON-YY HH24: MI: SS');

--My table created at time '14-MAY-12 10:38:58'

    

 5)

 SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:39:58','DD-MON-YY HH24: MI: SS'); 

PREVIOUS DATA RECOVERED '14-MAY-12 10:39:58'

  SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:40:58','DD-MON-YY HH24: MI: SS');

SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:42:48','DD-MON-YY HH24: MI: SS');

SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:42:58','DD-MON-YY HH24: MI: SS');

Using the AS OF clause - with SCN

(SELECT * FROM EMPLOYEE AS OF SCN 203836360);

Systems Change Number (“SCN”)

The scn is an ever-increasing number. It can be used to determine the "age" of the database and its component datafiles.

Restrictions

the server must be configured to use Automatic Undo Management.

• No DDL or DML can be issued when using Flashback Query.
• Flashback Query does not reverse DDL operations such as DROP, only DML alterations to the data.
• Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback data.

Oracle Flashback Version Query

 Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval.

How Row Version will be created ?

A row version is created whenever a COMMIT statement is executed.

Pseudocolumn Name	Description
VERSIONS_STARTSCN VERSIONS_STARTTIME	Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This pseudocolumn identifies the time when the data first had the values reflected in the row version. Use this pseudocolumn to identify the past target time for Oracle Flashback Table or Oracle Flashback Query. If this pseudocolumn is NULL, then the row version was created before start.
VERSIONS_ENDSCN VERSIONS_ENDTIME	SCN or TIMESTAMP when the row version expired. If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation.
VERSIONS_XID	Identifier of the transaction that created the row version.
VERSIONS_OPERATION	Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation. For user updates of an index key, Oracle Flashback Version Query might treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I VERSIONS_OPERATION.

HANDS ON

Step 1.    Create Table

 Step 2 insert values

 Step 3 Update the existing table

Step 4) After updating select from CHILD_LOCATOR

Step 5)Version Query  when we run we will get Previous  all version(all changes in the table)

Flashback Transaction query

Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more
information about the data versions. This includes the SQL required to reverse each change. Queries
against this view are documented as "flashback transaction queries" and require the
SELECT ANY TRANSACTION system privilege. The view definition is as follows.

Row selection