Pagi,
terima kasih atas jawabannya pak bowo, sekarang saya recovery juga
dengan flashback yang menggunakan timestamp koq muncul error:
SQL> select count(*) from d_master.t_bank AS OF timestamp to_timestamp('06-04-20
06 12:00:00','dd-mm-yyyy hh24:mi:ss');
select count(*) from d_master.t_bank AS OF timestamp to_timestamp('06-04-2006 12
:00:00','dd-mm-yyyy hh24:mi:ss')
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$"
too small
saya sudah melakukan penambahan undo_retention menjadi 30000
ini hasil pengecekan terhadap undo:
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 30000
undo_tablesp string UNDOTBS1
SQL> show parameter session;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 170
shared_server_sessions integer
rgds
betty
On 4/6/06, Yulius Wibowo <[EMAIL PROTECTED]> wrote:
> Harus tahu, kira2 kapan transaksinya dilakukan ...
> Setelah itu translate waktunya ke SCN menggunakan fungsi
> TIMESTAMP_TO_SCN.
>
> Kalau mau tahu detail, sampai dengan jam menit detik kapan
> transaksinya dilakukan, gunakan technology Log miner, yaitu dengan
> melihat isi LOG FILEs dan ARCHIVEd LOG Files.
>
> Bowo
>
>
>
> --- In [email protected], "betabetha ." <[EMAIL PROTECTED]>
> wrote:
> >
> > siang,
> >
> > sorry salah pengetikkan,
> > yang benar: bagaimana saya dapat mengetahui scn sebelum perintah
> > delete dicommit?
> >
> > rgds
> > betty
> >
> >
> > On 4/6/06, betabetha . <[EMAIL PROTECTED]> wrote:
> > > Siang,
> > >
> > > Bagaimana jika saya mengetahui SCN perintah delete dilakukan?
> > >
> > > rgds
> > > betty
> > >
> > >
> > > On 4/6/06, Yulius Wibowo <[EMAIL PROTECTED]> wrote:
> > > > Kenapa count(*) ==> 0 ?
> > > > Karena SCN yg dipakai adalah SCN SETELAH perintah delete/commit
> > > > dijalankan (=current SCN).
> > > > Padahal data yg akan dibaca adalah data masa lalu, dengan SCN
> masa
> > > > lalu juga, yaitu SCN sebelum commit.
> > > >
> > > > Contoh:
> > > >
> > > > SQL> SELECT COUNT(*) FROM test_tbl;
> > > >
> > > > COUNT(*)
> > > > ----------
> > > > 4
> > > >
> > > > SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
> > > >
> > > > GET_SYSTEM_CHANGE_NUMBER
> > > > ------------------------
> > > > 447239
> > > >
> > > > SQL> DELETE FROM test_tbl;
> > > >
> > > > 4 rows deleted.
> > > >
> > > > SQL> COMMIT;
> > > >
> > > > Commit complete.
> > > >
> > > > SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
> > > >
> > > > GET_SYSTEM_CHANGE_NUMBER
> > > > ------------------------
> > > > 447247
> > > >
> > > > SQL> SELECT COUNT(*) FROM test_tbl;
> > > >
> > > > COUNT(*)
> > > > ----------
> > > > 0
> > > >
> > > > --SCN sebelum delete/commit
> > > > SQL> SELECT COUNT(*) FROM test_tbl AS OF SCN 447239;
> > > >
> > > > COUNT(*)
> > > > ----------
> > > > 4
> > > >
> > > >
> > > > --SCN setelah delete/commit
> > > > SQL> SELECT COUNT(*) FROM test_tbl AS OF SCN 447247;
> > > >
> > > > COUNT(*)
> > > > ----------
> > > > 0
> > > >
> > > >
> > > > CMIIW,
> > > > Bowo
> > > >
> > > >
> > > > --- In [email protected], "betabetha ." <betabetha@>
> > > > wrote:
> > > > >
> > > > > Siang,
> > > > >
> > > > > Saya membaca bahwa metode flasback untuk pencarian data
> (recovery
> > > > > data) ini selain menggunakan waktu (AS OF timestamp) bisa
> juga
> > > > dengan
> > > > > menggunakan SCN (System_Change_Number). Ternyata recovery
> dengan
> > > > > menggunakan SCN ini tidak berhasil, kenapa ya? berikut ini
> saya
> > > > > tampilkan query saya:
> > > > > SQL> select count(*) from d_master.t_bank;
> > > > > COUNT(*)
> > > > > ----------------
> > > > > 177
> > > > > SQL> delete d_master.t_bank;
> > > > > 177 rows deleted.
> > > > > SQL> select DBMS_FLASHBACK. GET_SYSTEM_CHANGE_NUMBER from
> dual;
> > > > >
> > > > > GET_SYSTEM_CHANGE_NUMBER
> > > > > -----------------------------------------------------
> > > > > 3138345
> > > > > dan ini hasilnya record tidak ditemukan:
> > > > > SQL> select count(*) from d_master.t_bank AS OF SCN 3138345;
> > > > >
> > > > > COUNT(*)
> > > > > ----------------
> > > > > 0
> > > > >
> > > > > salam
> > > > > betty
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > -----------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
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> >
>
>
>
>
>
>
>
> --
> -----------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
>
>
>
>
>
>
>
>
--
-----------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/
