FLASHBACK

FLASHBACK TECHNOLOGIES

FLASHBACK DROP

·      

            Depends on RECYCLE BIN
·         You can recover a dropped table using flashback drop
·         When you recover a table from recycle bin, the triggers ,constraints, indexes are brought back with different names,
·         Some constraints such as FK can’t be flashed back ; they are lost,
·         Bitmap join indexes are not flashed back ,
·         Materialized view are also lost ,
·         When table is dropped, related indexes are dropped whereas views are invalidated.
·         EXAMPLE –
      SQL>  flashback table job_history to before drop ;
      SQL> flashback table job_history to before drop rename to job_hist ;

   FLASHBACK QUERY

            Depends on UNDO TABLESPACE,
·         Limited by UNDO RETENTION PERIOD,
·         Returns only commited data,
·         It is implemented through the AS OF clause of the SELECT statement(either a  TIMESTAMP or an     

      SCN number).
·         EXAMPLE –
      SQL> select * from job_history as of timestamp(’08-MAY-2012  11:50:00’,’DD-MON-YYYY      

      HH24:MI:SS’) where employee_id = 102;

   FLASHBACK VERSION QUERY

·      

            Depends on UNDO TABLESPACE,
·         Limited by UNDO RETENTION PERIOD,
·         Returns only commited data,
·         You want to see what changes have been done to a column over a period of time,
·         It is implemented by adding a VERSION BETWEEN clause to a select statement,
·         Version between scn minvalue and maxvalue.
·         EXAMPLE –
      SQL> select salary from employees versions between scn minvalue and maxvalue where employee_id =    

            193;

   FLASHBACK TRANSACTION QUERY

·        

      SUPPLEMENTAL LOGGING must be enabled,
·         Limited by UNDO RETENTION PERIOD,
·         Returns only commited data,
·         Flashback Transaction Query goes a step further , allowing you to perform transactional recovery of     

             tables ,

·                      It provides you with the sql that could be used to undo the transaction,
·          Undo the changes made by a particular transaction without affecting the changes made by other   

             transactions.
·          EXAMPLE –
       SQL> select table_name, operation, undo_sql from flashback_transaction_query t , (select versions_xid     

             as xid from employees versions between scn minvalue and maxvalue where employee_id = 123) e    

             where t.xid = e.xid and operation = ‘UPDATE’ ;

              TABLE_NAME  OPERATION    UNDO_SQL

              ---------- ---------   ------------------   --------------------

              EMPLOYEES    UPDATE           update “HR”.”EMPLOYEES” set “SALARY” =

                                                                ‘2108.93’ where ROWID =

                                                                ‘AAARAgAAFAAAABYABd’;

              EMPLOYEES    UPDATE           update “HR”.”EMPLOYEES” set “SALARY” =

                                                                ‘4217.85’ where ROWID =

                                                                ‘AAARAgAAFAAAABYABd’;

              EMPLOYEES    UPDATE            update “HR”.”EMPLOYEES” set “SALARY” =

                                                                ‘4017’ where ROWID =

                                                                ‘AAARAgAAFAAAABYABd’;

               3 rows selected.

     FLASHBACK TABLE

·         

      All of the previous Flashback options allowed us to view and correct specific data elements within a table,  Flashback Table is a technology that allows you to recover an entire table ,
·         Depends on undo tablespace
·         The TO SCN clause can recover the Flashback Table to a certain SCN ,
·         The TO TIMESTAMP clause can recover the Flashback Table to a certain point in time,
·         To flashback a table , the table must have ROW MOVEMENT enabled ,using
       ALTER TABLE tablename ENABLE ROW MOVEMENT ,
·         EXAMPLE –
       SQL> flashback table emplyees to scn 623411;
·         All triggers are disabled during a Flashback Table operation,
       SQL> flashback table employees to scn 623411 enable triggers;
       When you specify the ENABLE TRIGGERS option ,all triggers that were previously enabled will be     

             reenabled after the operation is complete.

   FLASHBACK DATABASE

·         

      Not based on undo data ,
·         The database must have ARCHIVE LOG enabled ,
·         Depends on FLASHBACK DATABASE LOGS in FLASH RECOVERY AREA,
·         When the Flashback Database is enabled , the RVWR background process is started ,
·         The value DB_FLASHBACK_RETENTION_TARGET determines how far back in time you can flash            

             back the database ,
·         The values in the OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME columns  of      

             V$FLASHBACK_DATABASE_LOG give you information regarding how far you can use Flashback           

             Database,

·                      Media Failure cannot be resolved using Flashback Database ,
·         You cannot use Flashback Database if the control file has been restored or re-created,
·         EXAMPLE –
               1. First, query the V$FLASHBACK_DATABASE_LOG view to retrieve the   
                    OLDEST_FLASHBACK_SCN:

               SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

               OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME

                -------------------- ---------------------

               979720 20-JUL-08

                2. Next, shut down and start the database in MOUNT mode:

                SQL> shutdown

                Database closed.

                Database dismounted.

                ORACLE instance shut down.

                SQL> startup mount

                ORACLE instance started.

                Total System Global Area 535662592 bytes

                Fixed Size 1334380 bytes

                Variable Size 171967380 bytes

                Database Buffers 356515840 bytes

                Redo Buffers 5844992 bytes

                Database mounted.

                3. Next, issue the Flashback Database recovery command:

                SQL> flashback database to scn 979721;

                Flashback complete.

                 4. Finally, open the database with the RESETLOGS option, because you recovered to a time

                 prior to the current database:

                 SQL> alter database open resetlogs;

                 Database altered.

     FLASHBACK DATA ARCHIVE

·       

            Also known as ORACLE TOTAL RECALL,
·         AUDITING and COMPLIANCE  are two key areas where this technology can be useful ,
·         Not based on undo data ,
·         To utilize Flashback Data Archive , create one or more tablespaces as an archive ,
       EXAMPLE –
       SQL> create flashback archive audit_flash_archive
       tablespace audit_archive quota 20g retention 7 year;
·          You may alter the storage quota and retention time and add, drop, or modify tablespaces in a
       data archive using the alter flashback archive command.
 
·          Once the Flashback Data Archive is created, you can begin archiving data from specific
       tables. To enable archiving for an existing table, use the ALTER TABLE command with the
       FLASHBACK ARCHIVE clause:
 
       SQL> alter table employee_history flashback archive audit_flash_archive;
 
·          Several views are available for monitoring the Flashback Data Archive.
       DBA_FLASHBACK_ARCHIVE
       DBA_FLASHBACK_ARCHIVE_TS
       DBA_FLASHBACK_ARCHIVE_TABLES