trong Oracle database có 1 Undo tablespace dùng để lưu trữ undo data – dữ liệu lịch sử nhằm hỗ trợ cho các hoạt động của database (rollback transaction, read consistency, recover, …). Với việc lưu trữ dữ liệu lịch sử, Undo tablespace còn hỗ trợ thêm khả năng Flashback query – query dữ liệu tại 1 thời điểm trong quá khứ.
Ta tìm hiểu sơ qua về Undo tablespace.
Cấu hình phổ biến của Undo tablespace
| SQL> show parameter undo NAME TYPE VALUE ------------------------------------ -------------------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 |
Ý nghĩa các tham số
- Từ version 9i, Oracle chuyển sang dùng undo segment thay cho rollback segment, do đó tham số undo_management ta nên để mặc định AUTO
- Tham số undo_tablespace dùng chỉ định tablespace làm Undo tablespace. Ta có thể tạo nhiều Undo tablespace, tuy nhiên tại 1 thời điểm chỉ dùng được 1 Undo tablespace
- Tham số undo_retention xác định thời gian tối thiểu Undo tablespace cố gắng lưu giữ dữ liệu lịch sử, nếu Undo tablespace có Auto extend. Mặc định là 900 giây = 15 phút. Trường hợp Undo tablespace có kích thước cố định, Oracle bỏ qua tham số này và tự động cấu hình lưu giữ lượng dữ liệu lịch sử phù hợp với kích thước tablespace
Ngoài ra, Undo tablespace còn 1 đặc tính nữa là Guarantee
| SQL> select tablespace_name, retention 2 from dba_tablespaces 3 where tablespace_name like 'UNDO%'; TABLESPACE_NAME RETENTION ------------------------------ ----------- UNDOTBS1 NOGUARANTEE |
Nếu guarantee được bật, thời gian undo_retention luôn được đảm bảo. Do đó nếu kích thước Undo tablespace không đủ thì các câu lệnh DML rất dễ fail do không còn chỗ lưu trữ undo data. Cấu hình này nếu muốn bật cũng cần cân nhắc kỹ.
| SQL> alter tablespace undotbs1 retention guarantee; Tablespace altered. SQL> alter tablespace undotbs1 retention noguarantee; Tablespace altered. |
Flashback query
Nhờ dữ liệu lưu trữ trong Undo tablespace, ta có thể thực hiện các câu query kiểm tra lại dữ liệu cũ nếu nghi ngờ có thay đổi, bằng mệnh đề AS OF
Ví dụ ta xóa 2 employee, sau đó kiểm tra lại trước đó 5 phút:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> delete from employees where employee_id in (197, 198); 2 rows deleted. SQL> commit; Commit complete. SQL> select employee_id 2 from employees as of timestamp systimestamp - interval '5' minute 3 where employee_id not in ( 4 select employee_id from employees 5 ); EMPLOYEE_ID ----------- 197 198 |
Tất nhiên là nếu dữ liệu không còn trong Undo tablespace, ta sẽ gặp 1 lỗi quen thuộc: “snapshot too old”
| SQL> select employee_id 2 from employees as of timestamp systimestamp - interval '60' minute 3 where employee_id not in ( 4 select employee_id from employees 5 ); from employees as of timestamp systimestamp - interval '60' minute * ERROR at line 2: ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1_514581338$" too small |
Ngoài timestamp, ta cũng có thể dùng System Change Number để kiểm tra sự thay đổi dữ liệu nếu có.
Ta thử update first_name của 1 employee, sau đó kiểm tra lại first_name tại thời điểm SCN, và thời điểm hiện tại:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1670985 SQL> update employees 2 set first_name = 'Kaka' 3 where employee_id = 197; 1 row updated. SQL> commit; Commit complete. SQL> select first_name, 'OLD' STATUS from employees as of scn 1670985 where employee_id = 197 2 union 3 select first_name, 'CUR' from employees where employee_id = 197; FIRST_NAME STA -------------------- --- Kaka CUR Kevin OLD |
Flashback version query
Flashback version query là 1 tính năng khác, cũng dựa trên Undo data, tuy nhiên sẽ cung cấp thông tin chi tiết hơn Flashback query.
Giả sử ta thực hiện các thao tác update, delete trên table employees như sau
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | SQL> update hr.employees set salary = salary*1.2 where employee_id=197; 1 row updated. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1674294 SQL> delete from hr.employees where employee_id = 198; 1 row deleted. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1674313 SQL> insert into hr.departments values (660,'Security', 100, 1700); 1 row created. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1674323 SQL> update hr.employees set manager_id = 100 where employee_id = 197; 1 row updated. SQL> commit; Commit complete. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1674331 SQL> update hr.employees set department_id = 660 where employee_id = 197; 1 row updated. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1674348 SQL> commit; Commit complete. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 1674354 |
Ta có thể kiểm tra lại lịch sử thao tác cũng như dữ liệu cũ bằng cách sử dụng mệnh đề versions between trong câu query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> select versions_startscn startscn, versions_endscn endscn, 2 versions_xid xid, versions_operation oper, 3 employee_id empid, last_name name, manager_id mgrid, salary sal, department_id dep_id 4 from hr.employees 5 versions between scn 1674290 and 1674354 6 where employee_id in (197,198); STARTSCN ENDSCN XID O EMPID NAME MGRID SAL DEP_ID ---------- ---------- ---------------- - ---------- ------------------------- ---------- ---------- ---------- 1674347 06001700C7050000 U 197 Feeney 100 3600 660 1674329 1674347 0B001D000E000000 U 197 Feeney 100 3600 50 1674292 1674329 06001900C7050000 U 197 Feeney 124 3600 50 1674292 197 Feeney 124 3000 50 1674311 0400070053040000 D 198 OConnell 124 2600 50 1674311 198 OConnell 124 2600 50 6 rows selected. |
Ta có thể thấy được employee_id 198 đã bị xóa (Oper = D), employee_id 197 đã được thay đổi (Oper = U) về lương, người quản lý, cũng như bộ phận làm việc.
Tuy vậy, tính năng Flashback version query có 1 số hạn chế như:
- Không dùng được trên external table
- Không dùng được trên các table của Oracle
- Không dùng được trên temporary table, view
- Không query được về trước thời điểm 1 lệnh DDL thay đổi cấu trúc table
Các tính năng Flashback query này khá hữu ích trong trường hợp thời gian cần kiểm tra thay đổi dữ liệu ngắn, undo data vẫn còn trong Undo tablespace. Với thời gian xa hơn 1 chút, ta phải sử dụng các tính năng Flashback khác.
Không có nhận xét nào:
Đăng nhận xét