1 / 14

ORACLE FLASHBACK

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.

helen
Download Presentation

ORACLE FLASHBACK

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ORACLEFLASHBACK

  2. Flashback • Flashback a nivel de base de datos • Flashback a nivel de tabla • Flashback a nivel de tablaborrada • Flashback query • Flashback query versions • Flashback transaction query

  3. 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.

  4. 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. • Thesechanges are storedin 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 thedatasaved in the undo tablespaceto recreate the original data

  5. How does the flashback query work? • Theundo data isalso 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.

  6. How does the flashback query work? • Oracle9i’s AutomaticUndoManagement feature allows administrators to specify how long they wish to retain the undo data using the UNDO_RETENTION initialization parameter. • Byusingthis parameter and sizing the undo tablespace appropriately, DBAs can control how far back a Flashback Query can go.

  7. 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;

  8. 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;

  9. Flashback Query Sample • Alter System Set undo_retention = 1200; (sys) • Shutdown immediate • Startup • Creamos una copia de la tabla para efectuar el ejemplo: • Createtable Empleados as Select* fromEmp; (scott) • Deletefrom Empleados WhereDeptno = 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')

  10. 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)

  11. 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;

  12. Flashback Query Sample 9iR1

  13. Flashback Query Sample ORACLE 9iR1 • Para conocer el ultimo SCN: • DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;

  14. Flashback Options ORACLE 10g Flashback VersionQuery Flashback TransactionQuery

More Related