Flashback Oracle 4p2rh

ORACLE FLASHBACK

Flashback 

Flashback query



Flashback query versions



Flashback transaction query



Flashback table



Flashback Drop



Flashback Database

Flashback Query



Oracle Flashback Query allows s to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.

Flashback Query 

Recovering lost data or undoing incorrect, committed changes.



Comparing current data with the corresponding data at an earlier time.



Checking the state of transactional data at a particular time.



Simplifying application design by removing the need to store some kinds of temporal data.



Oracle Flashback Query lets you retrieve past data directly from the database.



Applying packaged applications, such as report generation tools, to past data.



Providing self-service error correction for an application, thereby enabling s to undo and correct their errors.

How does the flashback query work? 

The Oracle database uses a version based read consistency mechanism by saving an image of the data prior to making any modifications.



These changes are stored in the undo tablespace (or Rollback Segments).



Should the later decide not to commit the transaction or if the operation was not successful, Oracle uses thedata saved in the undo tablespace to recreate the original data

How does the flashback query work? 

The undo data is also used to generate a snapshot of data consistent to a point in time when a query was started if another has modified the underlying data.



Flashback Query relies on the same mechanism to construct an image of the data as it existed at a time in the past.



The undo data, therefore, must be available in order for a Flashback Query to be successful.

How does the flashback query work? 

Oracle9i’s Automatic Undo Management feature allows s to specify how long they wish to retain the undo data using the UNDO_RETENTION initialization parameter.



By using this parameter and sizing the undo tablespace appropriately, DBAs can control how far back a Flashback Query can go.

Configuring Your Database for Automatic Undo Management 

To configure your database for Automatic Undo Management (AUM), you or your database must do the following:



Create an undo tablespace with enough space to keep the required data for flashback operations.



Set the following database initialization parameters: 

UNDO_MANAGEMENT



UNDO_TABLESPACE



UNDO_RETENTION



*RETENTION_GUARANTEE

PRE-REQUISITES 

Oracle Flashback Query can only be used if the server is configured to use Automatic Undo Management, rather than traditional rollback segments. The maximum time period that can be flashbacked to is defined using the UNDO_RETENTION parameter in the init.ora file. Alternatively, this parameter can be set using:



Alter system set undo_management=auto scope=spfile;



ALTER SYSTEM SET UNDO_RETENTION = <seconds>;



GRANT FLASHBACK ANY TABLE TO ;

FLASHBACK QUERY 

Enable the undo_retention



Grant privileges FLASHBACK ANY TABLE



Select * from SCHEMA.TABLE AS OF TIMESTAMP TO_TIMESTAMP(‘DD-MM-YYYY HH:MM:SS');



Select * from SCHEMA.TABLE AS OF SCN (#);



EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123);EXEC UTE Dbms_Flashback.Enable_At_Time('28-AUG-01 11:00:00');



EXECUTE Dbms_Flashback.Disable;

Flashback Query Sample 

Alter System Set undo_retention = 1200; (sys)



Shutdown immediate



Startup



Creamos una copia de la tabla para efectuar el ejemplo:



Create table Empleados as Select * from Emp; (scott)



Delete from Empleados Where Deptno = 10; (13-03-2015

11:16:04)  11:16:14  commit; 

Select * from SCOTT.EMPLEADOS AS OF TIMESTAMP TO_TIMESTAMP('13-03-2015 11:16:14')

Flashback Query 

Ver que datos no se encuentran o fueron borrados de la otra tabla:



Select * from EMPLEADOS AS OF TIMESTAMP TO_TIMESTAMP('13-032009 11:16:14') MINUS Select * from EMPLEADOS



Restauramos la información en la tabla:



INSERT INTO EMPLEADOS (Select * from EMPLEADOS AS OF TIMESTAMP TO_TIMESTAMP('13-032009 11:16:14') MINUS

Select * from EMPLEADOS)

Ejemplo

Guidelines for Oracle Flashback Query 

You can specify or omit the AS OF clause for each table and specify different times for different tables.



You can use the AS OF clause in queries to perform DDL operations (such as creating and truncating tables) or DML operations (such as inserting and deleting) in the same session as Oracle Flashback Query.



To use the result of Oracle Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.



If a possible 3-second error (maximum) is important to Oracle Flashback Query in your application, use an SCN instead of a timestamp. See General Guidelines for Oracle Flashback Technology.



You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.

Guidelines for Oracle Flashback Query 

If you specify a relative time by subtracting from the current time on the database host, the past time is recalculated for each query. For example:



You can use the AS OF clause in self-s, or in set operations such as INTERSECT and MINUS, to extract or compare data from two different times.

Flashback Query Sample ORACLE 9iR1 

Grant execute on dbms_flashback to scott;



13:17:55 SQL> Delete from empleados where deptno = 20;



13:18:06 SQL> commit;



EXECUTE Dbms_Flashback.Enable_At_Time('13-03-2009 01:18:06 PM')



Select * from Empleados;



Ejecutar Script



Deshabilitar Flashback



EXECUTE Dbms_Flashback.disable;

Flashback Query Sample ORACLE 9iR1 

Para conocer el ultimo SCN: 

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;

Using ORA_ROWSCN 

ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row; that is, the latest COMMIT operation for the row. For example:

Flashback Query Sample 9iR1

Flashback Version Query 

Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.

Flashback Transaction Query 

Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY.



This statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the responsible for the operation, and the SQL code that shows the logical opposite of the operation:

Configuring Oracle Flashback Transaction Query 

To configure your database for the Oracle Flashback Transaction Query feature, you or your database must do the following: 

Ensure that Oracle Database is running with version 10.0 compatibility.



ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;



Grant the SELECT ANY TRANSACTION privilege.

Flashback Transaction Query

Flashback Options Flashback Version Query

Flashback Transaction Query

Flash Back Table 

SELECT current_scn, SYSTIMESTAMP FROM v$database;



FLASHBACK TABLE tabla TO SCN 5607547;



select object_name,type from recyclebin;

Flash Back Table 

Recuperar una tabla que se borró: 



FLASHBACK TABLE employees TO BEFORE DROP;

Recuperar una tabla que se borró con otro nombre 

FLASHBACK TABLE tabla TO BEFORE DROP RENAME TO nuevo_nombre;

Objetos Relacionados 

Cuando se recupera una tabla los índices quedan en la papelera, se deben localizar los objetos asociados. Primero se debe recuperar la tabla.



SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM _RECYCLEBIN WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM _RECYCLEBIN WHERE ORIGINAL_NAME = '&table') AND ORIGINAL_NAME != '&table ’



ALTER TRIGGER "BIN$04LhnganfgMAAAAAANPw==$0" RENAME TO nombre;



ALTER INDEX "BIN$mWitSytjRbaG9mPqAzP5cg==$0" RENAME TO nombre;

Papelera de Reciclaje SHOW RECYCLEBIN SELECT * FROM RECYCLEBIN PURGE RECYCLEBIN PURGE DBA_RECYCLEBIN PURGE TABLE BIN$jsleilx392mk2=293$0;

PURGE TABLE employees; PURGE TABLESPACE x y ;