Salah satu cara utk melakukan recovery data adalah dengan
menggunakan flashback query.
Hal ini hanya dapat dilakukan dgn syarat:
1. Versi Database yg dipakai 9i/10G
2. Menggunakan Automatic Undo Management
3. Data yg dibisa direcover adalah data2 transaksi yg masih ada
didalam undo tablespace. Lama waktu maksimum "tersimpan" di dalam
undo tablespace = setingan nilai utk parameter UNDO_RETENTION. Atau
bisa lebih lama jika tdk ada transaksi lain yg menggunakan
block/extent yg dipakai data2 lama tsb di dalam undo tablespace.
4. Database dalam keadaan open, dan tidak ada physical corruption
(media failure)
5. Yang bisa direcover hanyalah data yang berubah akibat perintah
DML (Data Manipulation Language, spt INSERT, UPDATE, DELETE dan
MERGE) dan sudah terlanjur dicommit. Sedangkan kalau disebabkan
karena perintah2 DDL (Data Definition Language, spt DROP,
TRUNCATE, ...) data tidak bisa direcover dengan menggunakan
flashback query (gunakan alternatif lain : FLASHBACK DROP, FLASHBACK
DATABASE).
Contoh FLASHBACK QUERY:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
SQL> connect scott/tiger
SQL> create table dept1 as select * from dept;
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD
-------------------
22-01-2006 10:59:15
SQL> delete from dept1;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from dept1;
no rows selected
SQL> rollback;
SQL> select * from dept1;
no rows selected
SQL> select * from dept1
as of timestamp TO_TIMESTAMP('22-01-2006 10:59:1','dd-mm-yyyy
hh24:mi:ss');
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Recover data using FLASHBACK QUERY
-----------------------------------
SQL> insert into dept1
select * from dept1
as of timestamp TO_TIMESTAMP('22-01-2006 10:59:1','dd-mm-yyyy
hh24:mi:ss');
4 rows created.
SQL> select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> COMMIT;
Bowo
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.lizt.org (NEW)
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.vze.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
