Gestione audit su database Oracle – parte 2, 12c architettura multitenant

martedì 27 febbraio 2018 alle 27:34 | Pubblicato su Diario | Lascia un commento

Torno sull’argomento gia trattato in un paio di post precedenti per riportare alcuni dettagli emersi ricontrollando la configurazione sui database interni. In particolare nei miei vari tentativi ho un po’ pasticciato sui database versione 12.1 in modalità multitenant. Come ho scritto in chiusura del post precedente sull’argomento, occorre fare attenzione che sulla versione 12 a tutte le procedure del package PL/SQL DBMS_AUDIT_MGMT è stato aggiunto il parametro “CONTAINER” che ha come default il valore CONTAINER_CURRENT. Quello che ho combinato io è stato di invocare, collegandomi al container root la procedura DBMS_AUDIT_MGMT.INIT_CLEANUP senza specificare il parametro, cosa che quindi ha significato l’inizializzazione solo per il container corrente, però ho invocato la procedura di creazione del job di puliza, DBMS_AUDIT_MGMT.PURGE_JOB con il parametro container valorizzato a DBMS_AUDIT_MGMT.CONTAINER_ALL. In questo modo la creazione del job non da errore, però durante l’esecuzione del job di pulizia c’erano degli errori che venivano registrati sull’alert con il messaggio: “ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB”. Nel file di trace specifico del job c’era la spiagazione chiara con un messaggio come questo:

“KZAM_TRC:Exception encountered in “PDBXXXP1″
KZAM_TRC:Exception is ORA-46258, message is: ORA-46258: Cleanup not initialized for the audit trail”

Fin qui niente di che, il problema sta nel trovare il modo di rattoppare, infatti secondo me in questa versione di Oracle il package manca di una gestione seria degli errori, per cui, nella mia situazione descritta sopra, invocando nuovamente DBMS_AUDIT_MGMT.INIT_CLEANUP con parametro CONTAINER=>DBMS_AUDIT_MGMT.CONTAINER_ALL si ottiene l’errore “ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB”, invocando la procedura DBMS_AUDIT_MGMT.DEINIT_CLEANUP sempre con parametro CONTAINER=>DBMS_AUDIT_MGMT.CONTAINER_ALL  si ottiene un errore che dice che in uno dei PDB il “cleanup” non è inizializzato (vado a memoria in questo caso con il messaggio perché ora ho sistemato tutto e mi sono perso la console con il messaggio). Collegandosi sui singoli container ho trovato una situazione un po’ strana, risultava inizializzata la pulizia per gli audit su file (così per lo meno risultava con una chiamata alla funzione “DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED”) però se cercavo di deinizializzare questi specifici canali mi dava errore. Alla fine ho risolto invocando per ciascun PDB in sequenza le procedure DBMS_AUDIT_MGMT.DEINIT_CLEANUP e DBMS_AUDIT_MGMT.INIT_CLEANUP con parametro CONTAINER=>DBMS_AUDIT_MGMT.CONTAINER_CURRENT. A quel punto la situazione era pulita la configurazione per la pulizia degli audit su tutti i container ha funzionato senza problemi. La soluzione non era difficile ma ho dovuto trovarla per tentativi.

Annunci

Oracle Database 18c

venerdì 23 febbraio 2018 alle 23:43 | Pubblicato su Diario | Lascia un commento

Ieri, andando a cercare una cosa sui manuali oracle mi sono accorto di una novità, fra le versioni compariva un “Oracle Database 18c”, cosa che mi ha un po’ impressionato. Sono andato allora alla pagina principale del sito Oracle e quindi a questo annuncio che ho letto molto frettolosamente. Chiaramente il “18” è riferito all’anno e come spiega il comunicato il salto di nome nella versione è più legato alla politica di rilascio di nuove versioni che sarà d’ora in avanti annuale, con aggiornamenti trimestrali. Il comunicato spiega che di fatto, tecnicamente questa versione può essere vista come un aggiornamento della versione 12cR2. I tre pilastri su cui sembra voglia concentrarsi Oracle sono “in-memory”, che non ho mai visto; poi c’è l’architettura multitenant che sta progredendo e infine un’altra cosa per me lo “sharding” che dovrebbe essere una architettuttura di database distribuito. Prima ho dato un’occhiata e nelle mie fonti non ho trovato nessun annuncio, o le mie fonti sono vecchie ed abbandonate oppure effettivamente la novità è solo nel nome e poco nei contenuti per cui poco interessante.

Gestione Audit su database Oracle

giovedì 1 febbraio 2018 alle 01:05 | Pubblicato su 11g, 12c, Diario, Installation and Configuration | Lascia un commento
Tag:

Il titolo del post è pretenzioso e di fatto non rispecchia il vero contenuto in quanto coprire l’argomento “Audit” in Oracle è una cosa abbastanza difficile per la sua complessità. Il manuale “Security Guide” della versione 12.1 copre l’argomento con tre capitoli. Vi sono poi un po’ di novità sulla versione 12.1 rispetto alla 11.2. E’ un argomento su cui faccio fatica a raccapezzarmi, quindi provo ad affrontarlo per gradi.

Il post precedente è un esempio di quanto faccia fatica a dedicarmi all’approfondimento di nuovi (per me) temi. Cerco di rimediare alla confusione di quel post con un post più specifico e spero più ordinato e preciso in cui parlo esclusivamente della gestione della pulizia degli “audit” generati da Oracle.

Comincio con una informazione certificata dal supporto Oracle con la nota 308066.1 la quale spiega che anche con i parametri audit_trail=NONE e audit_sys_operations=FALSE vengono generati sotto il percorso indicato dal parametro audit_file_dest dei file .aud. In particolare vengono sempre e comunque generati dei file in cui si registrano le connessioni con utenti SYSDBA o SYSOPER e le operazioni di avvio e arresto dell’istanza. Questo vale sia per Oracle 11.2 che 12.1

Ora, i file sono piccoli ma possono diventare numerosi, nel mio caso ci sono procedure che si collegano con utenze privilegiate per fare dei controlli automatici e quindi vengono generati centinaia di file al giorno. Dopo qualche mese i file diventano migliaia. Da notare che se il filesystem si riempie non si riesce neppure ad accedere al database con l’utenza SYSDBA perché il sistema si lamenta di non riuscire a scrivere il file di audit. Ora, per gestire tutti i tipi di Audit Oracle mette a disposizione un package PL/SQL che si chiama DBMS_AUDIT_MGMT. I tipi di Audit che Oracle gestisce sono diversi, con la versione 12 è stato introdotto lo UNIFIED Audit Trail per mettere in un unico posto tutte le informazioni ma per retrocompatibilità esistono i tipi presenti nella versione precedente e questi sono:

  • File xml (AUDIT_TRAIL_XML)
  • File normali (AUDIT_TRAIL_OS
  • Record standard sulla tabella SYS.AUD$
  • record di Fine Grained Audit (FGA) sulla tabella SYS.FGA_LOG$)
  • Per la 12 record sulla vista UNIFIED_AUDIT_TRAIL

Ora per ciascuno di questi “canali” di audit c’è una costante nel package DBMS_AUDIT_MGMT.

Se ci si vuole limitare alla pulizia dei file .aud senza alcun criterio particolare il modo più semplice è l’uso della procedura DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL in questo modo:

BEGIN
 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

Con l’aiuto della procedura DBMS_AUDIT_MGMT.CREATE_PURGE_JOB si può pianificare una pulizia automatica periodica, ad esempio:

begin
 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, 
 AUDIT_TRAIL_PURGE_INTERVAL => 24,
 AUDIT_TRAIL_PURGE_NAME => 'OS_Audit_Trail_PJ',
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

Con gli esempi sopra vengono eliminati solo gli audit su file, se si vuole eliminare tutto, quindi ad esempio anche il contenuto della tabella AUD$ occorre fare un passo preliminare di inizializzazione. Occorre utilizzare la procedura DBMS_AUDIT_MGMT.INIT_CLEANUP che stando alla documentazione semplicemente sposta le tabelle dalla tablespace SYSTEM a SYSAUX, cosa che forse è sempre e comunque utile (al punto da chiedersi perché Oracle non le metta direttamente li in automatico. Infatti con lo unified audit trail è così). Quindi, ad esempio:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
 DEFAULT_CLEANUP_INTERVAL => 12 );
END;
/

La nota del supporto Oracle 1243324.1 riferendosi a Oracle 10.2.0.5 riferisce che il parametro DEFAULT_CLEANUP_INTERVAL è di fatto inusato, non mi è chiaro se cambia qualcosa sulle versioni 11 e 12.  Di sicuro l’intervallo da indicare sulla procedura CREATE_PURGE_JOB è obbligatorio e quello viene usato.

begin
 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 
 AUDIT_TRAIL_PURGE_INTERVAL => 24,
 AUDIT_TRAIL_PURGE_NAME => 'ALL_Audit_Trail_PJ',
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

 

Su Oracle 12 le procedure hanno dei parametri in più per gestire l’architettura multitenant, quindi nel mio caso alla procedura CREATE_PURGE_JOB ho aggiunto il parametro:

 container=>DBMS_AUDIT_MGMT.CONTAINER_ALL

Il default del parametro “container” è CONTAINER_CURRENT.

Combinando in maniera saggia i parametri o usando il parametro LAST_ARCHIVE_TIMESTAMP si possono organizzare politiche più elaborate; alla fine, capiti i concetti di base le cose sono abbastanza semplici e facili da capire

Manutenzione log e audit in Oracle

martedì 30 gennaio 2018 alle 30:35 | Pubblicato su 11g, 12c, Diario, Installation and Configuration | Lascia un commento
Tag:

Una cosa che non ho mai realmente approfondito, prima di adesso, era la manutenzione e pulizia dei vari file di log che un database server Oracle genera. In realtà, in condizioni normali, non c’è questo gran bisogno di fare manutenzione, per questo non me ne sono mai curato seriamente.  Ultimamente però mi è capitato spesso di dover fare pulizia in emergenza, in particolare ho avuto difficoltà con i file generati dal sistema di “Auditing” di Oracle. Il sistema di Auditing di Oracle fra l’altro è una cosa che nelle ultime versioni si è evoluto ed è abbastanza complesso.  Senza entrare quindi troppo nel dettaglio mi limito a dire che sembra che in qualunque condizione Oracle generi sempre e comunque un nuovo file .aud sotto $ORACLE_BASE/admin/<dbname>/adump ogni volta che avviene un accesso con una utenza SYSDBA. A causa di sistemi che fanno accessi automatici con questa utenza mi sono trovato quindi dopo un breve periodo di attività un numero esorbitante di file .aud. Il fatto è che oltre un certo numero anche rimuoverli con un semplice “rm” diventa difficile perché si ottiene un errore perché il sistema si lamenta che la lista dei file è troppo lunga…. Per un po’ ho sopperito come potevo manualmente, poi ho deciso di affrontare la questione in modo un po’ più  serio. Ho verificato quanto si riporta qui, ovvero che la generazione di questi file non può essere disattivata, infatti confermo che continua anche impostando il parametro AUDIT_TRAIL al valore “NONE”. Quello che è più importante secondo me di quel post è il primo commento, il quale riporta una procedura abbastanza semplice per gestire in modo automatico la pulizia di questi file direttamente da Oracle. Si tratta di usare il package PL/SQL DBMS_AUDIT_MGMT. Io al momento ho semplificato con queste tre chiamate:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
 DEFAULT_CLEANUP_INTERVAL => 1 );
END;
/
exec sys.dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type => sys.dbms_audit_mgmt.audit_trail_os,last_archive_time => sysdate - 3);
BEGIN
 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 
 AUDIT_TRAIL_PURGE_INTERVAL => 1,
 AUDIT_TRAIL_PURGE_NAME => 'ALL_Audit_Trail_PJ',
 USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

 

Di fatto viene schedulato un “job” di pulizia dei file di audit. Prima di poter creare il job vero e proprio occorre fare una inizializzazione. Secondo la documentazione uno dei passi dell’inizializzazione è spostare le tabelle su cui vengono registrati i dati di audit dalla tablespace SYSTEM a SYSAUX. In effetti ho avuto su un paio di database, durante la chiamata della procedura INIT_CLEANUP, errori di spazio sulla tablespace SYSAUX. La cosa bizzarra è che anche se la tablespace era in autoestensione la procedura mi dava errore comunque, l’unico modo quindi è allocare manualmente lo spazio espandendo il datafile.

La procedura CREATE_PURGE_JOB crea il “job” che si occuperà della pulizia dei file (e per come l’ho impostato io anche dei dati a db) più vecchi. Il punto è stabilire il periodo di ritenzione. Infatti la procedura di pulizia posso confermare gira appena si crea il job, poi dovrebbe girare secondo la schedulazione impostata, nel mio caso ogni ora (forse è un po’ esagerato ma sono ancora in fase sperimentale).  Senza la chiamata di SET_LAST_ARCHIVE_TIMESTAMP non si otterrà molto. Infatti il principio è che bisogna prima “archiviare” i file, quindi si dice a Oracle fino a che data e ora  i dati sono stati archiviati e così Oracle con il job schedulato li cancellerà….. ora il dubbio che mi è venuto scrivendo è che se non aggiorno almeno periodicamente il timestamp con la chiamata di quella procedura il job di cancellazione dopo la prima chiamata non cancellerà più nulla. Ipotizzo sia così ma mi riservo di verificare nei prossimi giorni. In ogni caso va detto che uno dei pregi di queste procedure è che sono molto rapide, nel mio caso con centinaia di migliaia di file l’operazione è stata molto più rapida che non a farla cancellando i file a mano.

Molto banalmente, leggendo la documentazione ho capito che, se non mi interessa un tubo degli audit, basta chiamare la procedura di creazione del job con il parametro USE_LAST_ARCH_TIMESTAMP  a false, secondo la documentazione il job in questo modo cancellerà tutto.

Un’altra parte importante di gestione log è la gestione del cosiddetto ADR, Automatic Diagnostic Repository, il posto dove Oracle scrive log e trace vari.  In questo caso Oracle prevede gia per default delle procedure di pulizia di questa area. Utilizzando l’utility ADRCI con il comando “show control” si possono estrapolare i due parametri SHORTP_POLICY e LONGP_POLICY che indicano il periodo di ritenzione in ora per rispettivamente per quelli che Oracle definisce file di “breve vita” e “lunga vita”. Per default i valori sono rispettivamente 30 giorni e 365 giorni. A parte l’inghippo derivante alla possibilità di avere più “homes” i valori si possono facilmente modificare con i comandi “set control” ad esempio nel mio caso:

set control (shortp_policy=100)
set control (longp_policy=300)

 

In automatico sembra che la pulizia giri o due giorni dopo l’avvio dell’istanza o ogni 7 giorni (note oracle 1196437.1 e 1446242.1). Per forzare una pulizia immediata basta il semplice comando “purge”

L’unico inghippo è il file alert in formato testo che si trova sotto la directory trace, quello non viene gestito da adrci, quindi va gestito a “mano”, si può fare tranquillamente a caldo facendo da sistema operativo un “mv”. (conferma qui)

Rileggendo velocemente il post, scritto abbastanza in fretta mi rendo conto sia un po’ confuso, come confusa è stata la mia ricerca a causa della scarsità di tempo a disposizione. Siccome però il tempo continuerà a scarseggiare per ora lo pubblico così e spero poi di poter fare qualche approfondimento.

Prestazioni della DBA_FREE_SPACE

giovedì 28 dicembre 2017 alle 28:20 | Pubblicato su Diario | Lascia un commento

Erano mesi, quasi anni che avevo un problema su un paio di database interni di test e sviluppo su cui sono presenti un discreto numero di schemi e di tablespace. Il problema era che la query che utilizzo normalmente per monitorare lo spazio libero aveva tempi di esecuzione strazianti, superiori al minuto. La query va sulle viste DBA_DATA_FILES e DBA_FREE_SPACE e su quest’ultima c’era il problema di prestazioni, anche un banale count() impiegava decine e decine di secondi. Avevo provato a cercare qualcosa in rete tempo fa senza trovare nulla di risolutivo. Oggi, sarà che ero un po’ più tranquillo, ho trovato un indizio nuovo, qui. L’indizio è stato quello sul cestino. Ho quindi fatto un primo tentativo, da system con il comando “purge recyclebin”, il quale non ha dato nessu risultato soddisfacente. Ho allore provato con “purge dba_recyclebin”; questo comando non può essere eseguito neppure dall’utenza SYSTEM per mancanza di privilegi, ho provato allora da utente SYS e magicamente il problema sembra essere stato risolto. Ho fatto la prova su due database 11.2.0.4 ed il risultato sembra ottimo e senza effetti collaterali. Il prossimo passo sarà provarlo su un 12.1.0.2.

Per la verità ho poi visto che al link sopra si parla anche della vista DBA_LMT_FREE_SPACE che però non riporta il campo tablespace_name ma un campo tablespace_id che non ha un corrispondente in dba_data_files e dba_tablespace. Approfondendo ho risolto (almeno per prova) con una join sulla v$tablespace, c’è però ancora qualcosa che non mi torna e che devo approfondire.

Oracle 12.1.0.2 Datapump import ORA-00001: unique constraint (SYS.PK_COL_GROUP_USAGE$) violated

lunedì 4 dicembre 2017 alle 04:40 | Pubblicato su 12c, Diario | Lascia un commento

Mi è uscito un titolo piuttosto lungo per questo post ma volevo riportare più informazioni possibili sul problema che ho incontrato e che qui voglio descrivere.

Qualche tempo fa, dopo che ci pensavo da mesi ho deciso di implementare delle procedure PL/SQL per gestire importazioni ed esportazioni con Oracle Datapump. Preso poi dall’entusiamo ho implementato una procedura per “duplicare” schemi db, operazione che mi viene richiesta sempre più frequentemente. Devo precisare che lavoro sia con Oracle 11.2 che con Oracle 12.1  per cui mi sono basato sulla documentazione della 11.2. Non mi risulta poi che a livello di funzionalità base, come quelle che uso io, ci siano differenze sostanziali fra le due versioni. Ho fatto qualche test su Oracle 11.2 e non ho avuto problemi, su Oracle 12.1 invece mi sono trovato sui log il seguente messaggio:

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]
MARKER
ORA-00001: unique constraint (SYS.PK_COL_GROUP_USAGE$) violated
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.KUPW$WORKER”, line 11265
—– PL/SQL Call Stack —–
object line object
handle number name
0xfa0df780 27116 package body SYS.KUPW$WORKER
0xfa0df780 11286 package body SYS.KUPW$WORKER
0xfa0df780 24286 package body SYS.KUPW$WORKER
0xfa0df780 24415 package body SYS.KUPW$WORKER
0xfa0df780 20692 package body SYS.KUPW$WORKER
0xfa0df780 10206 package body SYS.KUPW$WORKER
0xfa0df780 13381 package body SYS.KUPW$WORKER
0xfa0df780 3173 package body SYS.KUPW$WORKER
0xfa0df780 12035 package body SYS.KUPW$WORKER
0xfa0df780 2081 package body SYS.KUPW$WORKER
0xca7f92f8 2 anonymous block
DBMS_STATS.EXPORT_STATS_FOR_DP
In STATS_LOAD with process_order 3538
Fixing up the name in the impdp stat table
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]
ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPW$WORKER”, line 11259
—– PL/SQL Call Stack —–
object line object
handle number name
0xfa0df780 27116 package body SYS.KUPW$WORKER
0xfa0df780 11286 package body SYS.KUPW$WORKER
0xfa0df780 24286 package body SYS.KUPW$WORKER
0xfa0df780 24415 package body SYS.KUPW$WORKER
0xfa0df780 10105 package body SYS.KUPW$WORKER
0xfa0df780 13381 package body SYS.KUPW$WORKER
0xfa0df780 3173 package body SYS.KUPW$WORKER
0xfa0df780 12035 package body SYS.KUPW$WORKER
0xfa0df780 2081 package body SYS.KUPW$WORKER
0xca7f92f8 2 anonymous block
DBMS_STATS.EXPORT_STATS_FOR_DP
In STATS_LOAD with process_order 3538
Fixing up the name in the impdp stat table
Job “EXPDPUSER”.”TESTCRIDP_IMP” stopped due to fatal error at Mon Dec 4 13:49:14 2017 elapsed 0 00:37:36

Fra l’altro la procedura non ha dato alcun errore. Da una prima ricerca sono capitato su questa discussione sul forum oracle, dalla quale emerge gia il suggerimento di escludere le statistiche Oracle, cosa che io per la verità non ho mai sentito il bisogno di fare e quindi non ho mai fatto. Poi non ricordo più come, sono arrivato alla nota del supporto Oracle: “DataPump Import (IMPDP) Reports ORA-1 When Processing SCHEMA_EXPORT/STATISTICS/MARKER (Doc ID 2277109.1)

Quindi ho deciso che era ora di escludere almeno dall’importazione le statistiche  e così cercando aiuto nell’implementazione di questa modifica sono anche capitato su questo interessante post di Mike Smithers che suggerisce anche un altro valido motivo per escludere le statistiche. Devo dire che questo aspetto delle prestazioni sull’importazione delle statistiche non ho approfondito anche se credo meriterebbe.

Ho riprovato tutte le combinazioni e io sono riuscito a riprodurre il problema solo con la procedura PL/SQL di duplicazione  la quale utilizza un database link che punta a se stesso. La stessa cosa avviene se uso il comando impdp sempre con il dtabase link.  L’import da un dump su file, sia interfaccia PL/SQL che da comando “impdp” non mi ha mai dato questo problema.

 

MAX/MIN con KEEP (DENSE_RANK FIRST/LAST ORDER BY …)

venerdì 5 maggio 2017 alle 05:23 | Pubblicato su 11g, 12c, Diario, SQL | Lascia un commento

Devo dire che mi mancava questo aspetto del mio lavoro, trovare soluzioni a problemi, scoprendo ed approfondendo nuove cose. Partendo dalle fonti che ho trovato per il mio post precedente (al riguardo ho aggiunto al “blogroll” l’interessante blog di Stewart Ashton, dal quale sono partito oggi) ho avuto modo di leggere a approndire un post che ha cinque anni, cinque anni in cui non ricordo di aver mai visto l’operatore di cui parla. La cosa effettivamente molto bizzarra è che la sezione KEEP… oggetto di quel post non viene menzionata nella documentazione in corrispondenza delle funzioni MIN e MAX, neanche nella sezione dedicata alle funzioni analitiche (anche perché non sembra rientrare in quella categoria) ma come indicato da Rob Van Vijk nel post viene descritta (sommariamente direi) alle funzioni FIRST e LAST  che sono in realtà solo una parte della clausola KEEP. I link alla documentazione Oracle riportati nel post del 2012 nel frattempo sono stati invalidati da un cambiamento fatto da Oracle, c’è sicuramente lo stesso problema anche in molti miei vecchi post.

Ho poco da aggiungere all’ottimo post di Rob Van Vijk che riporta un semplice caso di test e le sue prove. Io direi che l’utilizzo delle funzioni MAX/MIN(..) KEEP (DENSE_RANK FIRST/LAST ORDER BY ..) mi pare possa snellire la query nelle casistiche interessate rispetto all’uso della funzione analitica. Ovviamente non prendo neanche in considerazione l’approccio con il NOT EXIST che aveva senso il secolo scorso prima che fossero introdotte le funzioni analitiche

CAST e MULTISET

mercoledì 3 maggio 2017 alle 03:33 | Pubblicato su 11g, 12c, Diario, SQL | Lascia un commento

Oggi partendo da una procedura commissionatami  che doveva fare un aggiornamento dei dati sono arrivato ad approfondire un operatore che forse ho visto di sfuggita in passato ma certamente non ho usato ne’ capito, ragion per cui oggi, avendo un attimo di tempo ho deciso di dedicarmici.

Il punto di partenza è una organizzazione dei dati di cui ignoro le motivazioni alla base ma che mi sembra, in un database relazionale, poco corretta; si tratta infatti di un campo di tipo VARCHAR2 che contiene degli id numerici che fanno riferimento ad un campo di un’altra tabella separati da una virgola. Concedo il beneficio del dubbio ma non ho capito perché non si è usato una tabella di relazione come in un database relazionale si dovrebbe fare. Il fatto è che a livello di SQL fare anche una banale join per estrapolare i dati diventa una cosa assai complicata.  Per cercare di spiegarmi cerco di riportare un caso di test:

create table T1 (
 t1_id number,
 t1_desc varchar2(30)
);
create table T2 (
 t2_id number,
 t2_t1ids varchar2(90),
 t2_desc varchar2(30)
);

insert into T1 values (1,'a');
insert into T1 values (2,'b');
insert into T1 values (3,'c');
insert into T1 values (4,'d');
insert into T2 values (1,'1,2','uno');
insert into T2 values (2,'2,3,4','due');
insert into T2 values (3,'1,4','tre');
insert into T2 values (4,'4','quattro');

Ora sul campo T2.T2_T1IDS ho degli id separati da virgola in un campo di tipo stringa che dovrebbero fare riferimento al campo T1.T1_ID. In un database relazionale ci sarebbe una tabella T3 che come struttura avrebbe due campi, uno che punta a T1.T1_ID e uno che punta a T2.T2_ID, se poi voglio estrarre i dati li ricostruisco con una join sulle tre tabelle; qui non è così facile, magari a me sfugge qualcosa ma di sicuro se scrivo questo non va bene:

SVILUPPO@svil112 > select * from t2 join t1 on (t1_id in t2_t1ids);
select * from t2 join t1 on (t1_id in t2_t1ids)
 *
ERROR at line 1:
ORA-01722: invalid number

Io sono giunto alla conclusione che bisogna in qualche modo fare un parsing del campo T2.T2_T1IDS. In passato ho fatto delle procedurine per fare una cosa simile ma siccome non mi piacevano molto e mi sembravano più complesse del necessario ho pensato di fare una nuova ricerca su internet, in pratica ho cercato una funzione inversa rispetto a LISTAGG e sono arrivato a questa discussione su ORAFAQ. Qui ho trovato due interessanti soluzioni che ho deciso di approfondire e capire e questo è il motivo di questo post. La prima, applicata al mio caso di test dovrebbe essere più o meno così:

SVILUPPO@svil112 > with test as
 2 (select * from t2 where t2_desc='due' )
 3 select * from
 4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
 5 from test
 6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
 7 join t1 on t1_id=t1id;

 T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 2 2 2 b
 2 3 3 c
 2 4 4 d

Da quello che ho capito io la funzione regexp_substr(t2_t1ids, ‘[^,]+’, 1, level) prende una occorrenza di tutto ciò che non contiene al suo interno una virgola, “level” indica quale occorrenza, quindi la sottoquery restituisce un record per ogni id nel campo t2_t1ids.  Nella discussione viene fatto un intervento che spiega come quella prima soluzione non funziona nel caso di più record estratti dalla query nella sezione “WITH”, infatti:

SVILUPPO@svil112 > with test as
 2 (select * from t2 where t2_desc='due' or t2_desc='tre' )
 3 select * from
 4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
 5 from test
 6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
 7 join t1 on t1_id=t1id;

  T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 3 1 1 a
 2 2 2 b
 2 3 3 c
 2 3 3 c
 2 4 4 d
 3 4 4 d
 2 4 4 d
 2 4 4 d
 3 4 4 d
 2 4 4 d

La “connect by level<….” provoca una moltiplicazione dei record nel risultato che non va bene. Anche qui avrei da approfondire perché confesso che non ho chiarissimi alcuni dettagli del funzionamento delle query gerarchiche. Se non ho capito male al primo livello ci sono tutti i valori della tabella diciamo n, al secondo livello per ogni valore ci sono  n figli e così via; qui la cosa si complica e richiederà altri approfondimenti.

La variante suggerita per risolvere il problema nel caso più generico è un po’ più complessa ed applicata al mio caso di test dovrebbe essere così:

SVILUPPO@svil112 > WITH test AS
 2 (SELECT * FROM t2 WHERE t2_desc='due' or t2_desc='tre' )
 3 SELECT * FROM
 4 (SELECT t2_id,REGEXP_SUBSTR(t2_t1ids, '[^,]+', 1, b.column_value) t1id
 5 FROM test a CROSS JOIN
 6 TABLE
 7 (
 8 CAST
 9 (
 10 MULTISET
 11 (
 12 SELECT LEVEL FROM DUAL
 13 CONNECT BY LEVEL <= REGEXP_COUNT(a.t2_t1ids, '[^,]+')
 14 )
 15 AS SYS.odciNumberList
 16 )
 17 ) b
 18 )
 19 join t1 on t1_id=t1id;

T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 2 2 2 b
 2 3 3 c
 2 4 4 d
 3 1 1 a
 3 4 4 d

Così mi pare corretta, seppur un po’ lunga.

Nell’ultima soluzione proposta ci sono alcune cose che io non conoscevo molto bene, la prima è l’utilizzo della sintassi CROSS JOIN che serve a generare un prodotto cartesiano con la sintassi ANSI della JOIN. La seconda cosa è quello che sembra un operatore MULTISET, in realtà è parte dell’operatore CAST. Come dice la documentazione MULTISET informa Oracle di prendere il risultato della sottoquery e restituire un valore “collection”. Questa “collection” viene convertita nel tipo predefinito SYS.odciNumberList che è documentato qui. A suo volta questo tipo può essere dato in pasto all’operatore TABLE e così di fatto la lista di valori è trasformata al volo in una tabella.

Non credo e spero di dover ritornare su situazioni analoghe a questa, in ogni caso quando capiterà avrò pronto qui qualche appunto che mi aiuterà.

 

Riferimenti:

Tipi predefiniti (Database Data Cartridge Developer’s Guide 11.2) , per 12.1 qui.

OraFaq

 

P.S.

2017/05/04: credo valga la pena di riportare qui un altro post interessante che ho trovato oggi: https://stewashton.wordpress.com/2016/06/22/new-improved-in-lists/

SQL*Plus 12.2

giovedì 20 aprile 2017 alle 20:10 | Pubblicato su 12c, SQL, Varie | 1 commento
Tag:

Oggi ho provato a installare la versione 12.2 del nuovo instant client Oracle versione 12.2 sul mio pc aziendale con sistema operativo Win7. Non ci sono motivi particolari per cui ci sia la necessità di passare ad usare questa versione se non al puro scopo di testarla e tal proposito mi sono ricordato di un post di Mike Dietrich (anche perché l’ho letto ieri 🙂 ) dove si parla che una fra le novità di questa versione di SQL*Plus è la diversità di comportamento nel caricamento del file “login.sql”. Io normalmente uso sul mio pc una configurazione per cui lancio uno script .cmd che a sua volta chiama un’altro script che setta le opportune variabili d’ambiente, fra cui SQLPATH affinché punti a una directory dove trovo una serie di script di utilità (per molti dei quali vanno i ringraziamenti a Tanel Poder)  e l’utilissimo login.sql che imposta l’ambiente iniziale per SQL*Plus come garba a me. Con la nuova versione di SQL*Plus effettivamente l’esecuzione automatica di login.sql che si trova sotto il percorso puntato dalla variabile d’ambiente SQLPATH non funziona. Infatti il post di Franck Pachot cui fa riferimento Dietrich fa tutti i test dettagliati su ambiente Linux, peccato però che come scrive Dietrich alla fine del suo post:

On Windows please be aware of (credits to Tim Hall):

  • Bug 25804573SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL

 

Ti pareva se su Winzoz non c’era qualche fastidio…

Sinceramente non mi è chiaro come aggirare il problema, la soluzione che ho adottato io sembra bruttina ma è l’unica che ho trovato in modo veloce: ho rinominato sqlplus.exe in sqlplus_orig.exe, ho creato un file sqlplus.cmd il cui contenuto è:

sqlplus_orig.exe %1 @login

Ora, prima ho provato a non rinominare il file .exe confidando che la logica di ricerca degli eseguibili mettesse prima i .cmd e dopo i .exe ma non sembra esssere così. La cosa che invece mi perplede abbastanza è il fatto che se metto @login.sql non funziona, @login si. Anche se invoco successivamente @login.sql non funziona, funziona se lo invoco con il percorso completo

SYSTEM@svil121 > @login.sql
SYSTEM@svil121 > @c:\oracle\conf\login.sql

PL/SQL procedure successfully completed.


Session altered.

Questa particolarità vale solo per il file login.sql, gli altri script funzionano sia che metta l’estensione  e sia che non la metta

 

 

Oracle Data Pump – parametri QUERY e EXCLUDE

giovedì 13 aprile 2017 alle 13:48 | Pubblicato su Diario | 1 commento

Oggi mi è capitato di dovermi cimentare con un esportazione di uno schema dati Oracle “selettiva”: per portare via un dump con schema e dati alleggerito. Non è la prima volta che mi capita ma questa volta avevo l’esigenza di introdurre diversi filtri sui dati e sugli oggetti. In realtà i filtri non erano particolarmente complessi ma sono incappato in un paio di particolarità che mi avevano gia messo in difficoltà altre volte ma che in tali occasioni avevo deciso di non approfondire rinunciando alla granularità dei filtri.

I parametri di Oracle Data Pump Export con cui ho lavorato sono QUERY, per filtrare i dati su alcune tabelle e EXCLUDE per escludere alcune tabelle che in realtà sono state introdotte come backup o prove o chissacosa nel tempo e che non avevo nessuna intenzione di portarmi dietro.

Query

il parametro query da manuale (11.2) prevede questa sintassi:

QUERY = [schema.][table_name:] query_clause

La parte “query_clause” è tipicamente la sezione “WHERE” di una query ma può essere qualunque clausola SQL, nel manuale riporta l’esempio di un “ORDER BY”. Su quest’ultima parte vado in fiducia perché ho avuto necessità solo di WHERE e solo quella ho provato.  Prima della query_clause è prevista come opzionale la specifica della tabella su cui applicare la clausola, se non viene specificata la clausola deve essere valida per tutte le tabelle esportate, quindi nella pratica deve riferire a qualche campo con un nome che si trova in tutte le tabelle esportate. Nel mio caso le tabelle esportate sono tante e il campo su cui filtrare ha sempre un nome diverso, quindi ho dovuto scrivere più occorrenze del parametro QUERY, una per ogni tabella, in questo modo:

QUERY=(tabella1:" WHERE t1campoa='a')
QUERY=(tabella2:" WHERE t2campoa='a')
QUERY=(tabella3:" WHERE t3campoa='a')
.....

 

Si possono tranquillamente mettere più occorrenze del parametro QUERY, naturalmente io uso un file di parametri, come consigliato anche dal manuale per non avere problemi di escape degli apici doppi. La cosa si complica se si vogliono clausole WHERE più complesse, qui mi limito a riportare il collegamento ad un ottimo post di Laurent Schneider che spiega come gestirli, un aiuto lo si trova anche fra i commenti.

Exclude

Questa la sintassi indicata sul manuale:

EXCLUDE=object_type[:name_clause] [, ...]

questo parametro mi ha fatto un po’ penare perché io avevo necessità di escludere tabelle il cui nome termina con “_O” oppure con “_BCK”.  Se avessi scritto:

EXCLUDE=TABLE:" LIKE '%_O'"

non avrebbe funzionato come volevo perché “_” in SQL è analogo a “%” e indica un carattere qualsiasi, quindi di fatto avrei escluso tutte le tabelle il cui nome termina con “O”. In SQL da lungo tempo (se non erro dalla versione 10 di oracle) si usare un carattere per fare l'”escape” quindi solitamente io scrivo:

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE '%\_O' ESCAPE '\';

Ecco, scrivendo

EXCLUDE=TABLE:" LIKE '%\_O' ESCAPE '\' "

ottenevo l’errore:

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-01425: escape character must be character string of length 1

Confesso che al primo giro non ho letto con attenzione la terza riga dell’errore, quindi ho fatto un paio di tentativi ed ho affinato le mie ricerche su internet fin quando finalmente sono arrivato a questo post che nella sua parte finale da un lato mi ha confermato che la sintassi che usavo era corretta dall’altro mi ha fatto venire il dubbio che il mio problema fosse solo il carattere di escape scelto, quindi, prima ancora di cercare di capire come mai il mio “\” non gli piacesse l’ho sostituito con un “!” ed ho riprovato

EXCLUDE=TABLE:" LIKE '%!_O' ESCAPE '!' "

magicament ha funzionato.

Mi sono imposto di segnarmi questa cosa in modo da non scordarla più, dunque quale posto migliore se non un nuovo post qui!

 

Riferimenti:

  1. http://www.usn-it.de/index.php/2009/05/22/oracle-impdp-wildcard-hacking/
  2. http://laurentschneider.com/wordpress/2011/07/datapump-table-like-foo-or-like-bar.html

 

 

« Pagina precedentePagina successiva »

Blog su WordPress.com.
Entries e commenti feeds.