Monday, 23 February 2015

ORACLE FLASHBACK TECHNOLOGY- USAGE & FEATURES

Oracle Flashback technology was introduced in Oracle 9i and added on to higher version. It provides a set of features using which we can view as well as recover data from Logical corruptions. Most of the Flashback technologies depend on the available UNDO data to retrieve older data.
Some of the Advantages of Flashback Technology are faster database point in time recovery and less performance overhead.Also we need to take care of the parameters that should be set for Flashback which are  given below:

·        DB_FLASHBACK_RETENTION_TARGET: Time limit for the deleted data to be retained
Alter System Set DB_FLASHBACK_RETENTION_TARGET=4320

·        DB_RECOVERY_FILE_DEST_SIZE: Size limit for the maximum data that can be retained.
Alter System Set DB_RECOVERY_FILE_DEST_SIZE=536870912

·        DB_RECOVERY_FILE_DEST: Location where the data needs to be retained
Alter System Set DB_RECOVERY_FILE_DEST='/Source/File/’


Flashback technology gives six different ways to fix logical corruption.

a) Oracle Flashback Query:
The Flashback query feature is used to views set of row at a specified point of time in the past. This feature uses the UNDO data (stored based on the undo retention parameter) to view the historical data and reconstruct the data if it was lost accidentally.
Useful when you accidentally delete number of rows and commit the changes.

Syntax:
Select .. As Of SCN | Timestamp

SELECT * FROM DEPT 
AS OF TIMESTAMP (Mention The Timestamp Here)

SELECT * FROM Ftest  As Of SCN (Mention The SCN Here)

b) Oracle Flashback Version Query 
This features helps to view all the versions of all the rows that ever existted in one or more tables in between two points in time or system change numbers (SCN).This feature also depends on UNDO data.
Syntax:
Select .. Versions Between SCN | Timestamp And SCN | Timestamp
 Select Version_Xid,Dept_Id
From Dept
Version Between SCN (Referencing A Start And End SCN)

Select Version_Xid,Dept_Id
From Dept
Version Between TIMESTAMP (Referencing A Start And End Timestamp)

) Oracle Flashback Drop 
Flashback drop is used to restore tables and depended objects accidentally dropped. After restoring the table will be renamed as its original whereas the indexes will have system generated names.

Syntax:
FLASHBACK TABLE DEPT TO BEFORE DROP;

Before doing flashback confirm that the dropped object has not been purged

Select OBJECT_NAME,ORIGINAL_NAME From User_Recyclebin;

d) Oracle Flashback Database 
This feature is used to recover the database from logical corruptions to specified point in time/scn. To flashback the database you must have the SYSDBA privilege.

Syntax:
Flashback Database To SCN  | Restore Point

FLASHBACK DATABASE TO TIMESTAMP(Timestamp value)

FLASHBACK DATABASE TO SCN (SCN valule)

We can also create a restore point and restore the database to the restore point.
CREATE RESTORE POINT dept_restore;
FLASHBACK DATABASE TO RESTORE POINT dept_restore;


e) Oracle Flashback Transaction Query
 Flashback Transaction query allows viewing changes made by single transaction or all transactions during a period of time .This feature is used during perform analysis, audit transactions to check data at transaction level.

Transaction Query Features uses the FLASHBACK_TRANSACTION_QUERY view for retrieving transaction information.

Syntax:
SELECT Logon_User, Operation, Start_Timestamp, Undo_Sql
FROM Flashback_Transaction_Query
WHERE Xid In (
SELECT  Versions_Xid
FROM  Emp BETWEEN TIMESTAMP (Systimestamp - Interval '6' Minute) AND Systimestamp);

f) Oracle Flashback Table
Flashback Table feature helps to recover table or set of tables to particular timestamp or SCN to reverse unwanted updates. 
To recover the table we need to set the below things.
  • Requires undo data.
  • Row movement must be enabled.
  • Must have SELECT, INSERT, DELETE, and ALTER privileges on the table
Syntax:
Flashback Table .. To Scn | Timestamp
Flashback Table Dept To Timestamp (Timestamp Value)


Read the most discussed topics of DATAWAREHOUSE CONCEPTS- TOP 10 TOPICS OF 2014




**********Please share your valuable comments on Oracle Flashback technology

READ ONLY TABLES In Oracle 11g


Since Oracle 11g we can place a table in read -only mode with the ALTER TABLE …READ ONLY statement. Read-Only tables are like normal tables, the only dissimilarity being it disallows any attempt to modify the data. Any DML (insert/update/delete/truncate) and certain DDL statement that affects the data are not allowed on these tables.

SYNTAX
ALTER TABLE SALES_TEST READ ONLY;
To Alter a table to read-only mode you must have ALTER ANY TABLE privilege on the table.

If you try to insert a record into this table now,
INSERT INTO SALES_TEST VALUES (100);

Error:
Update operation not allowed on the table.

Even though the table is read –only mode, index creation statement is possible.
CREATE INDEX SALES_TEST_IND ON SALES_TEST (ID);
Index Created.

  • To Revert back the table to READ/WRITE mode.
SYNTAX
ALTER TABLE SALES_TEST READ WRITE;


Prior 11g Read-only mode could only be associated to database and tablespace level. Previously to make a table read-only we have to grant SELECT privilege to appropriate users on the table. But still the table will be available for read and write to the creator of the table. With the new Oracle 11g enhancement no DML will be allowed on the table even by the owner of the table.

  • How To Check A Table Is In Read Only Or Not?
To identify the mode of the table you can use the READ_ONLY column. Its set to YES if table is read only and NO when table is not read-only.

SELECT TABLE_NAME ,READ_ONLY FROM USER_TABLES WHERE TABLE_NAME=’SALES_TEST ‘

The best example for tables which makes sense in read-only mode is the configuration tables that are not to be modified after installation. The same is applicable for tables that store historical or reference data.

Operations like DML, TRUNCATION, DROP, and FLASHBACK are not permitted on READ ONLY TABLE.

Read the most discussed topics of DATAWAREHOUSE CONCEPTS- TOP 10 TOPICS OF 2014




Related Posts Plugin for WordPress, Blogger...

ShareThis