Oracle Flashback Technology (I)




Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:



  • Oracle Flashback features use the Automatic Undo Management to obtain metadata and transaction historical data.
  • Undo data is persistent and survives database shutdown.
  • You can use the Flashback options to
    • recover data from user errors,
    • compare table data at two points in time,
    • view transaction actions (the set of actions performed in a given transaction).
    • Undo table drops
    • Revert the entire database to a previous point in time.

Configuring the database for Flashback options
Database must be configured for Automatic Undo Management (default on 11g).
For this you need:
  • (1) Size undo tablespace; and
  • (2) Enable automatic undo management

(Step 1) Create or resize the undo tablespace appropriately
The Undo tablespace must have enough space to keep the data required for flashback operations
(a) check the size of the existing undo tablespaces

SQL> select t.tablespace_name, t.status, t.contents, t.extent_management, 
            t.allocation_type,   t.retention, s.sizemb 
     from dba_tablespaces t, 
           (select tablespace_name, sum(bytes)/1024/1024 sizemb  
              from dba_data_files
              group by tablespace_name) s
     where t.tablespace_name = s.tablespace_name
     and t.contents = 'UNDO';

TABLESPACE_NAME                STATUS    CONTENTS  EXTENT_MANAGEMENT ALLOCATION_TYPE RETENTION   SIZEMB                 
------------------------------ --------- --------- ----------------- --------------- ---------   ---------------------- 
UNDOTBS1                       ONLINE    UNDO      LOCAL             SYSTEM          NOGUARANTEE 60      

(b) Check the current retention period: 
Query v$undostat to check the amount of time (seconds) for which undo will not be recycled.
    
SQL> select to_char(begin_time, 'dd-MON  hh:mi') begin_time, 
       to_char(end_time, 'dd-MON  hh:mi') end_time, 
       tuned_undoretention undoretention_sec
from v$undostat;

BEGIN_TIME    END_TIME      UNDORETENTION_SEC      
------------- ------------- ---------------------- 
19-OCT  09:51 19-OCT  09:58 1911     --  At current workload, undo is preserved for about 30 minutes
19-OCT  09:41 19-OCT  09:51 1608                   
...
19-OCT  03:11 19-OCT  03:21 1787                   
19-OCT  03:01 19-OCT  03:11 1183       
(Step 2) Enable automatic undo management (undo parameters)
  • UNDO_MANAGEMENT = [ AUTO (default) | MANUAL ]
  • UNDO_TABLESPACE - specifies the name of the undo tablespace
  • UNDO_RETENTION - specifies the minimum amount of time that Oracle attempts to retain old undo information before overwriting it.
    • The value for UNDO_RETENTION is ignored for a fixed size undo tablespace
    • If AUTOEXTEND is enabled, database attempts to honor UNDO_RETENTION.
    • 'snapshot too old' errors indicate that undo retention is not enough for flashback operations.
    • To accomodate Oracle Flashback features, you can set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle Flashback operation.
    • If retention is very important, use ALTER TABLESPACE...RETENTION GUARANTEE. Here the database never overwrites unexpired undo data even if it means that transactions fail due to lack of undo space.
    • To set: ALTER SYSTEM SET UNDO_RETENTION = 2400;
(a) check instance parameters

SQL> select name, value, isdefault, description
     from v$parameter
     where name like '%undo%';

NAME            VALUE    ISDEFAULT DESCRIPTION
--------------- ------   --------- ---------------------------------------------------
undo_management AUTO     TRUE      instance runs in SMU mode if TRUE, else RBU mode -- default: AUTO (11g), Manual (before 11g).
undo_tablespace UNDOTBS1 FALSE     use/switch undo tablespace
undo_retention  900      TRUE      undo retention in seconds


Notes on Flashback Query
  • Flashback query can be used with remote tables (table@dblink)
  • Queries on V$ views ALWAYS return current data
  • Flashback query can be used with static dictionary views
  • To query past data at a precise time, use an SCN.
  • With timestamp, the actual time queried might be up to 3 seconds earlier than the time you specify.
  • Oracle Flashback Drop - Reverse the effects of DROP TABLE

Using FLASHBACK QUERY
  • Use SELECT.. AS OF [ TIMESTAMP timestamp| SCN scn ]
  • The query returns committed data as it existed at that point.
  • To use flashback query on a table, the user needs to have FLASHBACK and SELECT privileges on that object.
Case:
(1) Some rows from emp table are deleted by mistake at 11 AM.
(2) Error discovered at 11:20AM.
(3) Use Flashback query to see the data as it was before the 11am delete.
(1) Check table rows at 11:00AM
SQL> select current_scn, to_char(systimestamp, 'mm-dd-yy  HH24:mi:ss') time
     from v$database;

CURRENT_SCN            TIME               
---------------------- ------------------ 
1385809                12-08-10  11:00:22

SQL> select employee_id, last_name,  salary
     from hr.emp
     where department_id =30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 
114                    Raphaely                  11000                  
...
119                    Colmenares                2500    


(2) At 11:20, rows are incorrectly deleted from emp.

SQL> delete from hr.emp where department_id=30;
SQL> commit;
SQL> select current_scn, to_char(systimestamp, 'mm-dd-yy  HH24:mi:ss') time
     from v$database;
CURRENT_SCN            TIME               
---------------------- ------------------ 
1386301                12-08-10  11:20:03

SQL> select employee_id, last_name,  salary
     from hr.emp
     where department_id =30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 

(3) Use flashback query to see emp as it was at 11 AM

SQL> select employee_id, last_name, salary
     from hr.emp AS OF TIMESTAMP to_timestamp('12-08-10 11:00:00', 'mm-dd-yy HH24:MI:SS')
     where department_id = 30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 
114                    Raphaely                  11000                  
...
119                    Colmenares                2500    

(4) Alternatively you can use the SCN 

SQL> select employee_id, last_name, salary
     from hr.emp AS OF SCN 1385809
     where department_id = 30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 
114                    Raphaely                  11000                  
...
119                    Colmenares                2500 

(5) Restore the data copying it from the earlier version of the table:

SQL> insert into hr.emp 
       (select * 
        from hr.emp AS OF TIMESTAMP to_timestamp('12-08-10 11:00:00', 'mm-dd-yy HH24:MI:SS')
        where department_id = 30 );

6 rows inserted.

Using Flashback Version Query
  • You can retrieve the different versions of a given row that existed during a given time interval.
  • Use VERSIONS { BETWEEN {SCN | TIMESTAMP} start AND end}
  • Returns one row for each version of the existed at any time in the specified interval
(a) check the initial values of the row data, current scn and timestamp
SQL> select employee_id, first_name, last_name, salary, commission_pct
     from hr.emp
     where employee_id = 116;

EMPLOYEE_ID            FIRST_NAME           LAST_NAME                 SALARY                 COMMISSION_PCT         
---------------------- -------------------- ------------------------- ---------------------- ---------------------- 
116                    Shelli               Baida                     2900                                          
                     
SQL> select current_scn, to_char(systimestamp, 'mm-dd-yy  HH24:mi:ss') time
     from v$database;

URRENT_SCN            TIME               
---------------------- ------------------ 
1400261                12-08-10  16:15:20

(b) update salary, and commission
SQL> update hr.emp
     set salary = salary*1.5
     where employee_id = 116;
SQL> commit;

SQL> update hr.emp
     set commission_pct = .12
     where employee_id = 116;
SQL> commit;

(c) Query table emp pseudocolumns (generated by the supplemental logging)
SQL> select versions_startscn as ver_startscn, 
            versions_starttime as ver_startime, 
            versions_endscn as ver_endscn, 
            versions_endtime as ver_endtime,
            versions_xid as ver_xid, 
            versions_operation as oper,
            employee_id as emp_id, salary, commission_pct as comm_pct
      from hr.emp
        versions between timestamp
             to_timestamp('12-08-10 16:14:00', 'mm-dd-yy HH24:MI:SS')
        and  to_timestamp('12-08-10 16:16:30', 'mm-dd-yy HH24:MI:SS')
     where employee_id = 116;

VER_STARTSCN VERS_STARTTIME        VER_ENDSCN  VER_ENDTIME            VER_XID          OPER EMP_ID  SALARY   COMM_PCT 
------------ --------------------- ----------- ---------------------- ---------------- ---- ------- -------- -------- 
1400294      08-DEC-10 04.16.13 PM  1400301    08-DEC-10 04.16.25 PM  0A00100044030000 U    116     4350     0.12 
1400273      08-DEC-10 04.15.49 PM  1400294    08-DEC-10 04.16.13 PM  0A00030043030000 U    116     4350
                                    1400273    08-DEC-10 04.15.49 PM                        116     2900
The pseudo columns are:
VERSIONS_[ STARTSCN | STARTTIME ] Display the SCN or TIMESTAMP when row took on this value.
If the row was created before the lower bound SCN ot TIMESTAMP, a NULL is returned.
VERSIONS_[ ENDSCN | ENDTIME ] Display the last SCN and TIMESTAMP when row had this value.
If the value is still current at the upper bound SCN or TIMESTAMP a NULL is returned.
VERSIONS_XID ID of the transaction that created the row version.
VERSIONS_OPERATION Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)


Using Flashback Transaction Query
  • Extension to the Flashback Query Version option
  • Used to retrieve information for a given transaction or for all transactions in a given time interval.
  • The transaction IDs (listed in the VERSIONS_XID column) are used to query the FLASHBACK_TRANSACTION_QUERY view
  • The database must have at least minimal supplemental logging enabled
(a) Query the transaction that updated salary of employee_id 116.
col logon_user format a10
col table_name format a10
col table_owner format a10
col operation format a10
col undo_sql format a50

SQL> select logon_user, operation, table_name, 
       to_char(start_timestamp, 'mm-dd-yy hh24:MI:ss') time, row_id, undo_sql
     from flashback_transaction_query
     where xid = hextoraw('0A00030043030000');

LOGON_USER OPERATION  TABLE_NAME TIME               ROW_ID              UNDO_SQL
---------- ---------- ---------- ------------------ ------------------- --------------------------------------------------
SYSTEM     UPDATE     EMP       12-08-10 16:15:46  AAASNxAAEAAAAJHAAB  update "HR"."EMP" set "SALARY" = '2900' where
                                                                         ROWID = 'AAASNxAAEAAAAJHAAB';
SYSTEM     BEGIN

Example: check the various actions performed by a given transaction
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1424010

SQL> create table emp
     (empno number, 
      empname varchar2(10),
      salary number);

SQL> select current_scn from v$database;
SQL> insert into emp values (1, 'John', 20000);
SQL> commit;

SQL> update emp set salary = salary*1.2 where empno=1;
SQL> commit;

SQL> delete from emp where empno = 1;
SQL> commit;

SQL> insert into emp values (1, 'Jack', 45000);   -- transaction performs multiple actions 
SQL> update emp set salary = salary*1.05;
SQL> commit;

SQL> update emp set salary = salary + 3320;
SQL> commit;

SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1424312

(a) To retrieve the various versions of the row: 
SQL> select versions_xid xid, versions_startscn start_scn,
        versions_endscn end_scn, versions_operation operation,
        empname, salary
     from emp
       versions between scn minvalue and maxvalue
     where empno = 1;
/
XID              START_SCN              END_SCN                OPERATION EMPNAME    SALARY                 
---------------- ---------------------- ---------------------- --------- ---------- ---------------------- 
0200060044040000 1424201                                       U         Jack       50570                  
0600080064040000 1424197                1424201                I         Jack       47250                  
05000A006F040000 1424192                                       D         John       24000                  
                                        1424192                          John       24000


(b) The transaction '0600080064040000' inserted the new record with a wrong name. 
To audit all the changes made by this transaction: 

SQL> select xid, start_scn, commit_scn, operation, logon_user, undo_sql
     from flashback_transaction_query
     where xid = hextoraw('0600080064040000')

XID              START_SCN  COMMIT_SCN OPERATION  LOGON_USER UNDO_SQL
---------------- ---------- ---------- ---------- ---------- --------------------------------------------------
0600080064040000 1424195    1424197    UPDATE     SYSTEM     update "SYSTEM"."EMP" set "SALARY" = '45000' where
                                                             ROWID = 'AAASXdAABAAAVBJAAB';

0600080064040000 1424195    1424197    INSERT     SYSTEM    delete from "SYSTEM"."EMP" where ROWID = 'AAASXdAA
                                                            BAAAVBJAAB';

0600080064040000    1424195    1424197 BEGIN   SYSTEM


Using Flashback Table

Flashback Table x Flashback Drop
  • FLASHBACK TABLE to SCN uses undo data to revert DMLs:
    • Delete rows inserted since scn_num
    • Update back rows modified since scn_num
    • Insert rows deleted since scn_num
  • FLASHBACK TABLE to BEFORE DROP uses a recycle bin to restore dropped table.
  • None of them, however, recover TRUNCATED rows.
  • For this you need to use FLASHBACK DATABASE

Allows point-in-time recovery for a table:
  • restore point specified by timestamp or SCN
  • table can stay online while operation executes
  • maintains all table attributes (indexes, triggers, constraints, etc)
  • maintains any remote state in a distributed environment
  • maintains data integrity specified by constraints

Requisites:
  • User must have FLASHBACK ANY TABLE or FLASHBACK object privilege on the table
  • Must have SELECT, INSERT, DELTE and ALTER privileges on the table
  • Must have SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or SELECT_CATALOG_ROLE role

For an object to be eligible to be flashed back:
  • Object MUST NOT be: part of a cluster, materialized views, AQ tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual partitions or subpartitions.
  • Table structure must not have been changed between now and the point of restore
  • Row movement must be enabled (rowids will change)
  • Enough undo data to perform flashback exists on the undo tablespaces.

(a) enable row movement

SQL> alter table emp enable row movement;

SQL> select * from emp;
EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       50570        

(b) check current scn (before flashback)

SQL> select current_scn from v$database;
CURRENT_SCN            
---------------------- 
1426179

(c) Insert and update data row data

SQL> Insert into emp values (2, 'Mary', 2000);
SQL> commit;
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1426185 

SQL> insert into emp values(100, 'Joseph', 450);
SQL> commit;
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1426200   

SQL> update emp set salary = salary*2;
SQL> commit;
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1426208 

(d) ensure that enough undo data exists to flashback the table

SQL>  select name, value/60 minutes_retained
      from v$parameter
      where name = 'undo_retention';

NAME              MINUTES_RETAINED       
----------------- ---------------------- 
undo_retention    80                     
 
(e) determine whtehr the table has dependencies on other tables. 
If dependencies exist, these may need to be flashbacked also

SQL>  select other.owner, other.table_name
    from sys.all_constraints this
         sys.all_constraints other
    where this.owner = 'SYSTEM'
    and   this.table_name = 'EMP'
    and   this.r_owner = other.owner
    and   this.r_constraint_name = other.constraint_name
 and   this.constraint_type = 'R';


(f) Check new data

SQL>  select * from emp;

 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900  


(g) flashback table to point in time (SCN) before  data modification

SQL> flashback table emp to scn 1426179;

SQL> select * from emp;

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       50570                  


(h) flashback table forward to after data modification

SQL> flashback table emp to scn 1426208;

SQL>  select * from emp;
 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900  


No comments:

Post a Comment