Indietro nel tempo con Flashback Query

PROGRAMMING
Database
Nella versione 9i del database più diffuso al mondo
sono state aggiunte molte nuove funzionalità,
una di queste consente di visualizzare i dati
com’erano in un determinato istante del passato
Indietro nel tempo
con
Flashback Query
Oracle9i
S
ono le dieci di sera, state impazzendo sul database di un cliente perché c’è qualcosa che non
vi torna nei dati. Dopo avere fatto decine di
select, pregato in molti modi il Santo protettore dei programmatori e pianto lacrime amare, vi
giunge l’illuminazione: <<Eureka! Ecco i record di
troppo!>>.
Pensate subito al programma, o all’utente, che sbagliando ha inserito quei dati.
OK, niente panico, cancello questi record e sono
a cavallo! Il comando da eseguire si scrive in pochi
secondi:
Delete from tabella
Where condizione;
Commit;
«A posto! Controlliamo, per sicurezza… ma… che
succede… perché non trovo più…? NO! HO CANCELLATO LE RIGHE SBAGLIATE!».
Dite la verità, se lavorate su database da un po’ di
tempo sicuramente vi è capitato. Dato il comando
di Commit, il danno è fatto ed irreparabile. Bisogna
recuperare il backup (se è disponibile) e questo comporta comunicare l’errore al cliente e, soprattutto, al
proprio capo. Problemi in vista!
Il database è Oracle 9i? Ok, non ci sono problemi,
c’è modo per recuperare subito le righe cancellate
mediante una flashback query. In quest’articolo
vedremo cos’è e come si utilizza il package
DBMS_FLASHBACK.
In generale…
La logica è piuttosto semplice: i dati modificati
vengono conservati in un apposito tablespace, detto
di Massimo Ruocchio
[email protected]
È laureato in matematica ed è certificato Oracle
Application Developer. Si occupa di analisi, progettazione e sviluppo di applicazioni software.
UNDO Tablespace, anziché nei classici Rollback Segment. Si stabilisce un ammontare di tempo (chiamiamolo periodo di conservazione) durante il quale il
DBMS deve conservare i dati modificati in questo
tablespace. Scaduto il periodo di conservazione, i dati
potranno essere sovrascritti.
In qualunque momento all’interno del periodo di
conservazione, è possibile leggere i dati com’erano in
un istante precedente.
I dati vecchi possono, ovviamente, essere reinseriti nelle tabelle attuali in modo da ripristinare, a
tutti gli effetti, la situazione antecedente all’istruzione da annullare.
La lettura dei dati dal tablespace di UNDO è effettuata mediante il package DBMS_FLASHBACK
che vedremo nel dettaglio più avanti. Se il periodo di conservazione è scaduto ma i dati non sono
stati sovrascritti nel tablespace di UNDO, allora è
comunque possibile effettuare la lettura dei vecchi
dati.
Ma come si individua l’istante temporale a cui si
intende ritornare? In due modi: mediante un system
change number oppure mediante un timestamp.
Un system change number non è altro che un numero
attribuito automaticamente da Oracle ad ogni modifica occorsa al sistema. Nel Listato 1 c’è un esempio
57
CP 115
PROGRAMMING
Database
Il compito del DBA
LISTATO 1 Esempio di assegnazione dei System Change Number
SQL*Plus: Release 9.0.1.0.1 - Production on Mar
Mar 19 11:45:19 2002
(c) Copyright 2001 Oracle Corporation.
All rights reserved.
La prima cosa da fare è creare il tablespace di
UNDO. L’istruzione è semplice:
Create UNDO tablespace nome
Datafile percorso
SIZE Dimensione;
Connesso a:
Oracle9i Personal Edition Release 9.0.1.1.1 - Production
JServer Release 9.0.1.1.1 - Production
A questo punto bisogna impostare tre parametri
di inizializzazione:
SQL> Select * from pippo;
PLUTO
---------1
2
3
4
5
SQL> declare
2
xscn number;
3 begin
4
5 for i in 1..5 loop
6
select dbms_flashback.get_system_change_number
7
into xscn
8
from dual;
9
dbms_output.put(‘OLD SCN=’||xscn);
10
delete pippo where rownum=1;
11
12
commit;
13
14
select dbms_flashback.get_system_change_number
15
into xscn
16
from dual;
17
dbms_output.put_line(‘ - NEW SCN=’||xscn);
18
19 end loop;
20
21 end;
22 /
OLD SCN=209928 - NEW SCN=209930
OLD SCN=209930 - NEW SCN=209931
OLD SCN=209931 - NEW SCN=209932
OLD SCN=209932 - NEW SCN=209933
OLD SCN=209933 - NEW SCN=209935
Procedura PL/SQL completata correttamente.
UNDO_MANAGEMENT deve essere impostato ad AUTO per consentire la gestione automatica del tablespace di UNDO;
UNDO_TABLESPACE deve essere valorizzato
con il nome del tablespace di UNDO creato;
UNDO_RETENTION deve essere valorizzato
con il numero di secondi che definiscono il
periodo di conservazione, per default vale 900
(15 minuti).
Dopo avere impostato il parametro UNDO_
MANAGEMENT bisogna riavviare il Database,
perché il parametro viene letto solo nella fase di
start-up. Infine bisogna dare all’utente che intende
utilizzare il package DBMS_FLASHBACK il permesso di farlo, quindi connessi con l’utente SYS
bisogna dare il comando
GRANT EXECUTE ON DBMS_FLASHBACK TO
nomeutente;
A questo punto il compito del DBA è finito (non
c’è voluto molto, in verità) e comincia il compito
dello sviluppatore.
Per di più nell’installazione standard di Oracle9i
i passi che abbiamo elencato (tranne la concessione della GRANT) vengono eseguiti automaticamente. Nel prossimo paragrafo descriviamo nel
dettaglio il package DBMS_FLASHBACK.
SQL>
Il package DBMS_FLASHBACK
58
CP 115
per capire il funzionamento del system change number.
La tabella PIPPO contiene cinque record. Lo script
effettua la delete di una riga alla volta e, per ogni
cancellazione, mostra il system change number prima e
dopo l’operazione.
Come si vede, il system change number cambia per
ogni istruzione di delete eseguita.
È possibile tornare ad uno stato precedente del database passando un timestamp (cioè una data munita di
ore, minuti, secondi e frazioni di secondo), ma c’è un
problema di precisione di cui parleremo più avanti.
Ovviamente, affinché il package DBMS_
FLASHBACK possa essere utilizzato, il database deve
essere opportunamente parametrizzato. L’attività di
impostazione e parametrizzazione tocca al DBA e sarà
discussa nel prossimo paragrafo.
Il package è costituito dalle seguenti procedure e
funzioni:
Procedure enable_at_time(query_time
in TIMESTAMP);
Procedure enable_at_system_change_number
(query_scn in NUMBER);
Procedure disable;
Function get_system_change_number
return NUMBER;
La funzione GET_SYSTEM_CHANGE_NUMBER
ritorna il system change number corrente. L’abbiamo
vista utilizzata nel Listato 1.
Per far tornare il sistema ad una situazione
antecedente bisogna utilizzare una delle procedure
ENABLE_AT, passando un istante (timestamp) oppure
un system change number.
Mediante la procedura DISABLE si fa ritornare il
sistema allo stato corrente.
Dopo una ENABLE_AT, e prima di una DISABLE,
non è possibile modificare i dati né effettuare un’ulteriore ENABLE_AT. Si tratta a tutti gli effetti di una
sessione di sola lettura dei dati così com’erano in un
momento precedente.
Facciamo qualche esempio.
Un po’ di pratica
LISTATO 2 Ripristino dei dati
SQL*Plus: Release 9.0.1.0.1 - Production on Mar
Mar 19 12:42:49 2002
(c) Copyright 2001 Oracle Corporation.
All rights reserved.
Connesso a:
Oracle9i Personal Edition Release 9.0.1.1.1 Production
JServer Release 9.0.1.1.1 - Production
SQL> select * from pippo;
Nessuna riga selezionata
Abbiamo cancellato queste benedette righe e dobbiamo reinserirle. Abbiamo imparato a leggere i dati
come erano ad un certo istante, ma abbiamo visto che
durante una sessione di FLASHBACK non è possibile
effettuare inserimenti.
Vediamo come fare per ripristinare i dati. Dal
Listato 1 vediamo che, prima della prima cancellazione dalla tabella PIPPO, il system change number
era 209928.
Nel Listato 2 ripristiniamo la tabella come era in
quel momento.
Si può tornare ad un
istante oppure ad un
System Change Number
Dichiariamo un cursore che prende i dati dalla
tabella PIPPO e lo apriamo all’interno di una sessione
FLASHBACK relativa al system change number desiderato. Prima di effettuare gli inserimenti in tabella
chiudiamo la sessione di FLASHBACK, tanto ormai i
vecchi dati sono stati caricati in memoria. Scorriamo
il cursore dei vecchi dati ed inseriamo nella tabella
attuale.
Se proprio non riusciamo a fare a meno dei cicli
For, possiamo fare come indicato nel Listato 3.
Abbiamo dichiarato una PL/SQL Table chiamata TN
e basata sul tipo TAB_N (che è un vettore di variabili numeriche). All’interno della sessione FLASHBACK abbiamo caricato il vettore e, dopo essere tornati alla situazione attuale, lo abbiamo scorso per
ripopolare la tabella. Il risultato è lo stesso.
Il problema dei Timestamp
Cos’è un timestamp? Timestamp(N) è un nuovo tipo
di dato presente in Oracle9i che aggiunge alla data
classica le frazioni di secondo. N indica il numero
di cifre decimali dei secondi e può valere da 0 a 9,
dunque la precisione del timestamp può arrivare fino
al miliardesimo di secondo. Per consentirci di gestire
SQL> Declare
2
Cursor PIPPO_OLD is
3
select * from pippo;
4
N Number;
5 Begin
6
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_
CHANGE_NUMBER(209928);
7
OPEN PIPPO_OLD;
8
DBMS_FLASHBACK.DISABLE;
9
loop
10
FETCH PIPPO_OLD INTO N;
11
Exit When PIPPO_OLD%NOTFOUND;
12
Insert into pippo values(N);
13
End loop;
14
Commit;
15 End;
16 /
Procedura PL/SQL completata correttamente.
SQL> Select * from pippo;
PLUTO
---------1
2
3
4
5
SQL>
meglio i timestamp, Oracle ha fornito una funzione
di conversione (TO_TIMESTAMP) ed una funzione
che restituisce il timestamp di sistema (SYSTIMESTAMP).
Per intuito, quando si sente parlare di “tornare
indietro ad una situazione precedente”, si pensa ad un
particolare istante.
È quindi ovvio che ci debba essere la possibilità di
vedere il sistema com’era, ad esempio, il giorno 18 di
Marzo 2002 alle ore 10, 45 minuti, 23 secondi e 275
millesimi (a patto che questa data ricada nel periodo
di conservazione).
Il package DBMS_FLASHBACK mette a disposizione la funzione ENABLE_AT_TIME che, preso in
input un timestamp, cerca il system change number corrispondente ed effettua una chiamata alla procedura
ENABLE_AT_SYSTEM_CHANGE_NUMBER. C’è,
però, un problema: per associare il timestamp al system
change number, Oracle conserva automaticamente
ogni cinque minuti il timestamp ed il system change
number correnti. Quando l’utente richiede di tor-
59
CP 115
PROGRAMMING
Database
LISTATO 3 Ripristino dei dati utilizzando due cicli FOR
SQL> select * from pippo;
Nessuna riga selezionata
SQL> Declare
2
Cursor PIPPO_OLD is
3
select * from pippo;
4
type TAB_N is table of Number
index by binary_integer;
5
TN TAB_N;
6
i number := 1;
7 Begin
8
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_
CHANGE_NUMBER(209928);
9
10
For rec in PIPPO_OLD loop
11
TN(i):= rec.pluto;
12
i := i + 1;
13
End loop;
14
15
DBMS_FLASHBACK.DISABLE;
16
17
For i in 1..TN.count loop
18
Insert into pippo values(TN(i));
19
End loop;
20
21
Commit;
22
23 End;
24 /
In due minuti e mezzo su un Database può succedere di tutto, quindi i dati potrebbero essere molto
diversi da quelli che ci aspettiamo.
Come risolvere questo problema? Si può creare una
tabella applicativa che conserva le coppie (timestamp,
system change number) ad una frequenza superiore ad
una volta ogni cinque minuti oppure ad ogni modifica delle tabelle critiche. La struttura della tabella
potrebbe essere la seguente
create table TAB_SCN_TIME
(TSTAMP
Timestamp(9),
SYSTEMCN
Number);
Poi basterebbe scrivere una semplice procedura
del tipo:
create or replace procedure
TORNA_ALLE (Istante in TIMESTAMP) is
xSCN number;
Begin
DBMS_FLASHBACK.DISABLE;
Select SYSTEMCN
into xSCN
from TAB_SCN_TIME
where TSTAMP = ( Select Max(TSTAMP)
From TAB_SCN_TIME
Where TSTAMP <= Istante);
Procedura PL/SQL completata correttamente.
SQL> Select * from pippo;
DBMS_FLASHBACK.ENABLE_AT_
SYSTEM_CHANGE_NUMBER(xSCN);
PLUTO
---------1
2
3
4
5
Exception
When no_data_found then Null;
End;
/
SQL>
nare ad un certo istante, Oracle determina il system
change number registrato più prossimo al timestamp
richiesto, ma questo system change number potrebbe
essere relativo ad una situazione dati che si era verificata due minuti e mezzo prima o dopo rispetto
all’istante che avevamo richiesto.
che fa all’incirca le stesse operazioni della procedura ENABLE_AT_TIME associando, però, all’istante
richiesto dall’utente, un system change number più
significativo.
Nella procedura precedente abbiamo inserito una
DISABLE prima di ogni ENABLE per evitare l’errore
ORA-08184 che corrisponde al tentativo di riabili-
TABELLA 1 Errori collegati al Package DMBS_FLASHBACK
60
CP 115
Errore
Descrizione
ORA-8180
Si è cercato di tornare indietro oltre il periodo di conservazione ed i dati sono stati già sovrascritti nel tablespace di UNDO.
ORA-8181
Si è specificato un system change number non valido.
ORA-8182
Quando si è in modalità Flashback non è possibile eseguire istruzioni che modificano i dati o le strutture dati
(DML e DDL).
ORA-8183
Prima di entrare in modalità Flashback bisogna chiudere la transazione facendo Commit oppure Rollback.
ORA-8184
Quando si è in modalità Flashback non è possibile aprire una nuova sessione con ENABLE prima di avere
chiuso la precedente con DISABLE.
ORA-8185
L’utente SYS non è abilitato a lavorare in modalità Flashback.
tare la modalità Flashback mentre si è già in modalità Flashback. Gli altri errori collegati all’utilizzo
del package DBMS_FLASHBACK sono elencati in
Tabella 1.
Per quanto visto in questo paragrafo possiamo affermare che conviene fare riferimento, laddove possibile,
ai system change number invece che ai timestamp.
Conclusioni
La possibilità di vedere i dati com’erano in un
istante del passato, e poi di poterli riutilizzare, stuzzica
molto la fantasia dello sviluppatore.
Non solo per riparare agli inevitabili errori.
Ad esempio si potrebbe aggiungere, ad una funzione che visualizza delle statistiche su dati che cambiano velocemente, la possibilità di vedere com’era
la situazione in un istante precedente scelto dall’utente (ovviamente all’interno del periodo di conservazione).
Molti altri utilizzi possono essere trovati per il
package DBMS_FLASHBACK, ma bisogna tenere
conto delle limitazioni e della logica dell’oggetto.
Meglio, allora, scrivere codice ragionando in termini di system change number anziché di timestamp. Se
in un certo istante occorre una modifica strutturale di
una tabella, anche solo l’allargamento di un campo, i
dati di quella tabella precedenti a tale modifica non
saranno più recuperabili, quindi conta molto anche la
stabilità del database.
DBMS_FLASHBACK ed il datatype TIMESTAMP
sono solo due delle modifiche migliorative apportate
ad Oracle9i.
Per maggiori informazioni su questi strumenti si possono consultare le risorse indicate in Bibliografia e nei
Riferimenti. Altre novità introdotte nell’ultima versione di Oracle saranno trattate nei prossimi numeri
di Computer Programming.
BIBLIOGRAFIA
[1] Oracle – “Oracle9i Flashback Query”, Oracle
Corp., 2001
[2] Oracle – “Oracle9i Supplied PL/SQL Packages
and Types Reference, Cap 16”, Oracle Corp.,
2001
RIFERIMENTI
[3] http://technet.oracle.com (Oracle Technology
Network)
61
CP 115