Transcript FLASHBACK QUERY

ORACLE
FLASHBACK
Flashback
•
•
•
•
•
•
Flashback a nivel de base de datos
Flashback a nivel de tabla
Flashback a nivel de tabla borrada
Flashback query
Flashback query versions
Flashback transaction query
Flashback Query
• Oracle Flashback Query allows users 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.
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 user 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 user 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 administrators 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.
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 USER;
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)
;EXECUTE 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-2009
11:16:04)  11:16:14  commit;
• Select * from SCOTT.EMPLEADOS AS OF TIMESTAMP
TO_TIMESTAMP('13-03-2009 11:16:14')
Flashback Query Sample ORACLE 9iR2
• Ver que datos no se encuentran o fueron borrados
de la otra tabla:
• Select * from EMPLEADOS AS OF TIMESTAMP
TO_TIMESTAMP('13-03-2009 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-03-2009 11:16:14')
MINUS
Select * from EMPLEADOS)
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('1303-2009 01:18:06 PM')
• Select * from Empleados;
• Ejecutar Script
• Deshabilitar Flashback
• EXECUTE Dbms_Flashback.disable;
Flashback Query Sample 9iR1
Flashback Query Sample ORACLE 9iR1
• Para conocer el ultimo SCN:
– DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
Flashback Options ORACLE 10g
Flashback Version Query
Flashback Transaction Query