Oracle Flashback technology | ||
---|---|---|
Logical Flashback (do not depend on RMAN, rely on undo data)
|
- 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 |
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 tablespaceUNDO_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 honorUNDO_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 |
---|
|
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
andSELECT
privileges on that object.
(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 2900The 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 theFLASHBACK_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 |
---|
|
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
orFLASHBACK
object privilege on the table - Must have
SELECT, INSERT, DELTE and ALTER
privileges on the table - Must have
SELECT ANY DICTIONARY
orFLASHBACK ANY TABLE
system privilege orSELECT_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