Oracle Flashback Primeri

Flashback upit omogućava pregled i vracanje podataka na prvobitnu vrednost tj. stanje pre promene. Vracanje istorijskih podataka se može vršiti:

· tako da se podaci vrate na vrednost koju su imali u određenom vremenskom trenutku, ili

· na vrednost koju su imali do odredjene transakcije koju predstavlja transakcioni broj (SCN System Change Number).

Potrebne privilegije za flashback:

FLASHBACK, SELECT, INSERT, DELETE, ALTER privilegije na obektu, ili

FLASHBACK ANY TABLE systemska privilegija.

Premestanje redova (row movement) mora biti omogucen za sve tabele iz Flashback liste. Izuzetak je operacija flash back TO BEFORE DROP. Ova operacija zapravo oporavlja tabelu iz recyclebin a ne iz undo podataka.

Tokom Flashback transakcije rdbms zahteva ekskluzivno zakljucavanje tabele(a) iz Flashback liste (acquires exclusive DML locks).

SQL> CREATE TABLE TEST

( TABLE_NAME VARCHAR2(30 BYTE),

COMMENTS VARCHAR2(4000 BYTE))

ENABLE ROW MOVEMENT

(Opciona komanda koja omogucava premestanja redova (row movement) za vec postojece tabele

SQL> alter table test1 enable row movement;)

Flash Back Before Drop

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin

where ORIGINAL_NAME='TEST'

no rows selected

SQL> drop table TEST;

Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin

where ORIGINAL_NAME='TEST'

OBJECT_NAME ORIGINAL_NAME

------------------------------ --------------------------------

BIN$8jQtE2IWTe+ArefjM2KW0w==$0 TEST

SQL> flashback table TEST TO BEFORE DROP;

Flashback complete.

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin

where ORIGINAL_NAME='TEST'

Flashback u vremenskom intervalu

SQL> select count(*) from TEST;

COUNT(*)

----------

835

SQL> select to_char(sysdate,'dd-mon-yyyy:hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYY:

-----------------------------

14-apr-2011:11:39:40

--obrisati 4 reda tabele

delete from test where rownum<4;

commit;

SQL> select count(*) from test;

COUNT(*)

----------

832

--vracanje 4 reda obrisana o vremenskom okviru od 2 minuta

FLASHBACK TABLE test

TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' minute);

SQL> select count(*) from test;

COUNT(*)

----------

835

Flashback do SCN broja

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database

CURRENT_SCN SYSTIMESTAMP

-----------------------------------------------------------

4126566 14-APR-11 10.41.09.171000 AM +02:00

SQL> insert into test values ('MyNewTable', 'Testing Flasback With SCN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

CURRENT_SCN SYSTIMESTAMP

-----------------------------------------------------------

4126650 14-APR-11 10.43.57.687000 AM +02:00

SQL> select * from test where TABLE_NAME='MyNewTable';

TABLE_NAME COMMENTS

-----------------------------------------------------------

MyNewTable Testing Flasback With SCN

SQL> flashback table TEST to scn 4126566;

Flashback complete.

SQL> select * from test where TABLE_NAME='MyNewTable';

no rows selected

Pregled Fashback transakcija

Ukoliko koristimo specificnog db usera za pregled potrebno mu je dodeliti privilegije.

Dakle da bi mogli da vidimo navene transakcije iz view-a flashback_transaction_query potrebno je

--obazrivo sa privilegijama [ID 266536.1]

Grant grant SELECT ANY TRANSACTION to scott;

Bez ove privilegije javlja se:

SQL> select * from flashback_transaction_query;

select * from flashback_transaction_query

ERROR at line 1:

ORA-01031: insufficient privileges

Dakle ostaje da se uverimo da su ove dve transakcije zabelezene

SQL> select start_scn,start_timestamp,commit_scn,commit_timestamp

from flashback_transaction_query where

logon_user='SCOTT' and TABLE_NAME='TEST'

order by commit_timestamp desc

START_SCN START_TIM COMMIT_SCN COMMIT_TI

---------- --------- ---------- ---------

4127828 14-APR-11 4127830 14-APR-11

4126564 14-APR-11 4126644 14-APR-11

Ove I druge transakcije je moguce videti I iz

Enterprise Manager-a npr.

Transaction ID	09000300FC090000	Start SCN	4303657	Start Time	Apr 14, 2011 12:38:37 PM
DB User	SCOTT	Commit SCN	4303659	Commit Time	Apr 14, 2011 12:38:39 PM
OS User	FSC2658\darko	Machine Name	WORKGROUP\FSC2658

SCN	Operation	Schema	Table	SQL Redo
4303657	START			set transaction read write;
4303657	INSERT	SCOTT	TEST	insert into "SCOTT"."TEST" values "TABLE_NAME" = 'MyNewTable', "COMMENTS" = 'Testing Flasback With SCN';
4303659	COMMIT			commit;

Napomena

Transakcije je moguce analizirati ukoliko je baza u archivelog modu, sa dodatnim logovanjem transakcija (supplemental logging enabled). Prelazak iz jednog u drugi mod dat je u primeru.

-- prelaz u noarchivelog

shutdown immediate

startup mount

alter database noarchivelog;

alter database open;

select log_mode from v$database;

-- prelaz u archivelog & supplemental logging

shutdown immediate

startup mount

alter database archivelog;

alter database open;

select log_mode from v$database;

--Ako se javi greska The database must have at least minimal supplemental logging enabled.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;