Competenza e Certificazione

lunedì 16 aprile 2018 alle 16:39 | Pubblicato su Diario | Lascia un commento

Nell’intento di riprendere a scrivere qualcosa con una certa regolarità continuo con un altro post poco tecnico, inspirato dal mio attuale impegno per aggiornare la mia certificazione Oracle alla versione 12c e questo post di Connor McDonald. Penso valga la pena sottolineare e ribadire i concetti espressi in quel post in quanto io li condivido (in tutti i sensi). Se c’è un motivo per cui in tre anni ancora non ho fissato una data per fare l’esame di certificazione, è perché non sono riuscito ad approfondere ogni argomento oggetto dell’analisi fino ad avere acquisito confidenza con quella particolare caratteristica ed averla almeno collaudata e toccata con mano. Il cosiddetto “hand on”. Questo perché la certificazione per me non è tanto un fine ma un mezzo. Di fatto molti degli argomenti che ho studiato sulla guida per me erano veramente nuovi, molti tutt’ora non riescono a trovare applicazione pratica nel mio attuale impiego. Anche cose che però oggi non vengono impiegate a livello pratico possono essere oggetto di valutazione perché un cliente potrebbe esprimere determinate esigenze e allora è meglio avere gia una conoscenza per poter dare una consulenza.

Per me studiare ha sempre significato anche provare, sbattere la testa su esempi concreti, questo quantomeno aiuta a memorizzare le cose, anche perché io confesso che leggendo un libro faccio molta fatica a memorizzare molti dettagli, al contrario di quello che mi accade facendo le prove direttamente su un database. Se vogliamo poi, uno dei due libri che ho acquistato tre anni fa è apparantemente molto completo ma contiene un sacco di piccoli errori che mi costringono a verificare sui manuali Oracle e fare le prove pratiche per vedere qual’è la situazione reale.

Quindi, nel mio caso vedo una scarsa utilità della certificazione come fine ma una grande utilità come mezzo per un aggiornamento serio delle mie competenze di amministratore di database Oracle.

Annunci

Ritorno di fiamma

venerdì 13 aprile 2018 alle 13:20 | Pubblicato su Diario | 2 commenti

Ho iniziato a scrivere su questo blog  oltre 11 anni fa, facendo una piccola ricerca ho individuato il primo post scritto il 23 novembre 2006. C’è stato un periodo in cui il mio lavoro era dedicato prevalentemente alla gestione di database Oracle e in quel periodo ho imparato un sacco di cose. Per tenere traccia di ciò che imparavo ho scritto tanti post sul blog, dove poi c’è la possibilità di venire corretti e di scambiare opinioni tramite i commenti. A me è servito molto per dare un ordine alle cose nella mia mente e per fissarle e verificarle. Poi gli anni sono passati e le cose sono cambiate, il lavoro e cambiato, dandomi meno opportunità di approfondire nuove tematiche e nuove funzionalità e caratteristiche del mondo Oracle Database e dirottando il mio tempo su altre attività. Questo ha fatto si che anche la mia passione nel tempo si sia un po’ spenta. Anche la mia vita privata ha subito drastici cambiamenti e questo ha ridotto drasticamente il tempo libero che dedicavo agli approfondimenti e alla scrittura dei post che come ribadisco sono anche un modo per riordinare le idee. Nel tempo ho avuto però dei ritorni di fiamma più o meno forti.  Di fatto questi ritorni di fiamma non sono casuali, sono associati a periodi in cui sul lavoro riesco ad essere meno pressato su attività che non riguardano database o attività banali e quindi a dedicarmi ad aggiornarmi sulle novità o a cercare di recuperare argomenti su cui sono rimasto indietro. Ora sono in uno di quei periodi, da qualche giorno ho ripreso con un minimo di serietà a studiare sui libri per provare ad aggiornare la mia certificazione Oracle alla versione 12c. Sui due libri che uso annotai la data di quando iniziai a leggerli: 22/05/2015. Sono passati quasi tre anni, un tempo decisamente alto, definirei assurdo. Ricordo che prima di prenderli avevo il dubbio se puntare alla certificazione su Oracle 11g o passare direttamente alla 12c, dubbio che ora non può più sussistere visto che 11gR2 è sia ancora molto in uso almeno dai nostri clienti ma è decisamente obsoleta come versione. C’è di buono che almeno ho finito una prima lettura dei testi e quindi degli argomenti oggetto dell’esame 1Z0-060 per l'”upgrade” della certificazione. Quindi la mia speranza è di portare a termine questo obiettivo. Non tanto per la certificazione in se quanto per riportarmi in pari con le conoscenze sul database Oracle.

Constato che fuori il mondo dei blog su Oracle è sicuramente cambiato,  però alcuni esperti continuano a scrivere e condividere conoscenza, ad esempio Connor McDonald e Jonathan Lewis che nei miei feed sembrano essere, fra quelli storici, i più attivi. Ce n’è invece un’altro che con mio dispiacere qualche tempo fa a annunciato di volersi dedicare dopo tanti anni ad altre cose, Howard Rogers. Questo mi dispiace anche perché prossimamente anche il suo materiale non sarà più pubblicamente disponibile.

Stamattina ho letto un paio di interessanti post di Connor McDonald che fra l’altro sono anche datati ma comunque interessanti e questo mi ha un po’ eccitato e fatto riflettere. Per questo ho scritto questo post che di tecnico non ha nulla ma che spero stabilisca un nuovo punto di partenza per me.

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.

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/

Pagina successiva »

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.