Oracle Program Global Area (PGA)

Premessa

Questo post è un esperimento di importazione da un documento scritto su google docs. Ho cominciato a raccogliere un po’ di appunti per fare un po’ di ordine sull’argomento nella mia testa, poi, visto che sono riuscito a scrivere qualcosa che mi sembra accettabile ho deciso di pubblicarlo, come ho fatto molte volte in passato. Ci sono sicuramente alcuni problemi di formattazione, da tempo ho alcune difficoltà con la piattaforma wordpress ma non ho tempo ne’ voglia di approfondire e risolvere questi dettagli.

In questo articolo farò una panoramica sulla componente “Program Global Area”, riferita normalmente con la sigla PGA. La versione del database a cui faccio riferimento è la 12.1 e quindi anche i riferimenti ai manuali puntano a quella versione.

La Program Global Area in Oracle è un’area di memoria di cui riporto direttamente la definizione dal manuale “Administrators Guide” tradotta alla meglio.

Una Program Global Area (PGA) è una area di memoria che contiene dati e informazioni di controllo per un processo server. Essa è una memoria non condivisa creata dal Database Oracle quando un processo server viene avviato. L’accesso alla PGA è esclusivo per quel processo, c’è una PGA per ogni processo server. Anche in processi di background allocano la loro PGA. La PGA allocata per i processi di background e i processi server attaccati a un Database Oracle vengono chiamati “total instance PGA memory” mentre l’insieme di tutte le PGA individuali è chiamato “total instance PGA” o solo “instance PGA”

Per la verità le ultime definizioni mi sembrano un po’ ambigue ma è un dettaglio irrilevante.

Riporto poi dal manuale “Performance Tuning Guide”

Un esempio di informazioni scritte nella PGA per un processo è la “run-time area” di un cursore. Ogni volta che un cursore viene eseguito una nuova “run-time area” viene creata per quel cursore nella area di memoria PGA del processo server che sta eseguendo quel cursore.

Per query complesse una grossa porzione della area “run-time” è dedicata a “work area” allocate da operatori che fanno uso intensivo di memoria, inclusi ad esempio ORDER BY, GROUP BY, ROLLUP, HASH JOIN. Un operatore di ordinamento (sort operator) usa una “work area” (la “sort area”) per eseguire l’ordinamento in memoria di un insieme di righe. Analogamente , un operatore HASH JOIN usa la “work area” (hash area) per costruire una tabella hash dal suo input (left input).

 

Viste le definizioni generiche di PGA andiamo a vedere un po’ nel dettaglio come viene gestita la PGA.

Dimensioni delle “Work Area”

Oracle permette il controllo e la regolazione delle dimensioni delle “work area”. Generalmente “work area” più grandi possono migliorare significativamente le prestazioni di un particolare operatore al costo di un più alto consumo di memoria. Le dimensioni di “work area” possono essere classificate in :

• Optimal – quando la dimensione è sufficientemente grande da contenere i dati di ingresso e le strutture di memoria ausiliarie allocate dall’operatore SQL associato. Questa è la dimensione ideale
• One-pass – quando la dimensione della “work area” è al di sotto di quella ottimale e richiede un passaggio in più sui dati di ingresso
• Multi-pass – quando la “work area” non è sufficiente e occorrono più passaggi sui dati di ingresso

Tunable e Untunable

Oracle classifica, o almeno espone questa classificazione della PGA in sei categorie:

• SQL
• PL/SQL
• Java
• Olap
• Other
• Freeable

La parte dedicata alle work area è quella denominata “SQL”, viene anche definita “tunable” in contrapposizione alle altre che nel complesso sono definite “untunable”.

Dimensionamento della Program Global Area

Abilitando la gestione automatica della PGA (tramite il parametro PGA_AGGREGATE_TARGET con un valore maggiore di zero) Oracle dimensiona automaticamente la PGA correggendo dinamicamente la porzione di PGA dedicata alle “work area”. Se non specificato il valore di default per PGA_AGGREGATE_TARGET è il 20% delle dimensione della SGA. Il valore minimo è 10 MB. Il dimensionamento automatico gestito sulla base del parametro PGA_AGGREGATE_TARGET riguarda solo la parte definita “tunable”, quindi solo quella dedicata alle work area che in un certo senso è quella più dinamica

PGA_AGGREGATE_LIMIT

Dalla versione 12c Oracle ha introdotto il nuovo parametro PGA_AGGREGATE_LIMIT che permette di limitare l’allocazione globale di memoria per la PGA. Per default il valore di questo parametro è il valore più grande fra:

• 2 GB
• 2 * PGA_AGGREGATE_TARGET
• 3MB*PROCESSES

Può essere inferiore al 2* PGA_AGGREGATE_TARGET se tale valore supera il 90% della memoria ram totale disponibile meno la dimensione della SGA. Oracle raccomanda di non impostare PGA_AGGREATE_TARGET a un valore inferiore al suo default e per farlo l’unico modo è attraverso pfile o spfile (PGA_AGGREGATE_LIMIT). Su questa affermazione ho delle perplessità, infatti su una installazione con Oracle 12.1.0.2.0 ho fatto il seguente test:

SYSTEM@svil121 > show parameter pga
NAME TYPE VALUE
-------------------- ----------- -----
pga_aggregate_limit big integer 2856M
pga_aggregate_target big integer 2700M
SYSTEM@svil121 > alter system set pga_aggregate_limit=2900 m;
System SET modificato.
SYSTEM@svil121 > alter system set pga_aggregate_limit=2800 m;Errore con inizio alla riga : 1 nel comando -
alter system set pga_aggregate_limit=2800 m
Report error -
ORA-02097: impossibile modificare il parametro perchÚ il valore specificato non Þ valido
ORA-00093: pga_aggregate_limit deve essere compreso tra 2856M e 100000G
02097. 00000 - "parameter cannot be modified because specified value is invalid"
*Cause: Though the initialization parameter is modifiable, the modified
value is not acceptable to the parameter.
*Action: Check the DBA guide for range of acceptable values for this
parameter.
SYSTEM@svil121 > show parameter processes
NAME TYPE VALUE
------------------------- ------- -----
processes integer 1200

La RAM totale del sistema è 8100 MB e la SGA 4096 MB, quindi il valore di default dovrebbe essere almeno 3600 MB (che rientra nel 90% della memoria del sistema meno la SGA)

Sospetto possa essere un bug (di cui non ho trovato traccia sul supporto) in quanto su una installazione 18.3 con parametro processes a 1000, target a 512 MB il messaggio mi dice che il valore deve essere tra 3000 M e 100000 G, dove 3000 M è coerente con l’indicazione 3MB*PROCESSES.

Nella documentazione relativa al parametro PGA_AGGREGATE_LIMIT si dice che nel caso la dimensione della PGA superi il valore impostato dal parametro le sessioni che stanno usando la maggior quantità di “untunable memory” avranno le chiamate annullate (ORA-04036). Se la PGA sarà ancora sopra al limite tali sessioni verranno terminate.

Sul manuale Performance Tuning vengono riportate tutte le informazioni, c’è però una piccola differenza in quanto si afferma che il valore di PGA_AGGREGATE_LIMIT non supererà il 120% della dimensione della RAM disponibile meno la dimensione totale della SGA.

La nota del supporto Oracle “Limiting process size with database parameter PGA_AGGREGATE_LIMIT” (Doc id 1520324.1) riporta qualche dettaglio in più. Fra l’altro riporta che per Oracle 12.1 c’è il limite del 120% sulla dimensione della memoria fisica meno la SGA mentre per Oracle 12.2 spiega che se il parametro viene impostato a zero il suo valore sarà pari al 90% della dimensione della memoria fisica meno la dimensione della SGA.

A controllare il rispetto del parametro PGA_AGGREGATE_LIMIT ci pensa il processo CKPT ogni 3 secondi. Viene spiegato come nell’intervallo di tempo di tre secondi fra un controllo e l’altro di CKPT può essere superato il limite e se la sessione finisce la chiamata prima non ci sono conseguenze o azioni da parte di CKPT.

Controllo dell’uso e allocazione della PGA

Per controllare quanta PGA è stata allocata e come viene usata si possono usare diverse viste di sistema che possono essere interrogate via SQL, le descriverò sinteticamente una ad una.

V$PGASTAT

Questa vista mostra le statistiche globali sulla PGA, su 12.1 conto 19 righe. Viene riportato il valore di PGA_AGGREGATE_TARGET e poi, una statistica chiamata “aggregate PGA auto target”, questo è il valore derivato da PGA_AGGREGATE_TARGET che indica lo spazio PGA che oracle cerca di dedicare alle work area. Questo valore tendenzialmente è molto vicino alla differenza tra il target e la quantità di PGA “untunable” (PL/SQL ecc).

Abbiamo poi i valori di “total PGA allocated”, “total PGA inuse” e “maximum PGA allocated” che indicano, come è facile comprendere, la dimensione totale di PGA attualmente allocata, quella effettivamente in uso e quella massima allocata dall’avvio dell’istanza.

Può essere interessante il valore di “maximum PGA used for auto workareas” che indica il massimo volume di PGA consumato in una volta da work area. Vi sono poi altre statistiche che permettono di avere una indicazione dell’efficienza della PGA per poter capire se il dimensionamento è adeguato.

V$PROCESS

Se si vuole avere il dettaglio di utilizzo della PGA da parte di un processo (e quindi relativa sessione, supponendo di usare “dedicated servers”) questa vista riporta le quattro colonne:

• PGA_USED_MEMORY
• PGA_ALLOC_MEMORY
• PGA_FREEABLE_MEMORY
• PGA_MAX_MEM

Per esempio posso usare la query:

select s.sid, s.username,
pga_used_mem/1024/1024 pga_used_mem,
pga_alloc_mem/1024/1024 pga_alloc_mem,
pga_freeable_mem/1024/1024 pga_freeable_mem,
pga_max_mem/1024/1024 pga_max_mem,
row_number() over (order by pga_max_mem) rn
from
v$session s
join v$process p on (p.addr=s.paddr)
order by 6

Per ricavare la lista delle sessioni con relativo uso di PGA. In una applicazione che usa un POOL di connessioni questo dettaglio non è secondo me molto utile, occorre aggregare per avere valori medi, massimi e totali.

V$PROCESS_MEMORY

Per scendere ulteriormente nel dettaglio abbiamo a disposizione questa vista che per ciascun processo riporta il dettaglio di uso di PGA diviso nelle categorie:

• Java
• PL/SQL
• OLAP
• SQL
• Freeable
• Other

Non sempre sono tutte presenti, nel mio caso non ho mai righe relative a Java e OLAP. Se non ho capito male nella categoria SQL ricadono le work area, il resto dovrebbe essere tutto “untunable”. Riporto un esempio:

USER@prod > select category,sum(allocated)/1024/1024 a_mb,sum(used)/1024/1024 u_mb, max(max_allocated)/1024/1024 ma_mb from V$process_memory group by category;
CATEGORY A_MB U_MB MA_MB
--------------- ---------- ---------- ----------
Freeable 606,5 0
PL/SQL 64,0559082 21,6882935 5,46675873
Other 7871,91492 502,54974
SQL 14,4066696 2,82536316 1554,26163

Anche in questo caso ho fatto un’aggregazione

V$SESSTAT

La cito solo per completezza perché non mi sembra che dia informazioni che non possiamo avere già con le viste descritte in precedenza. Questa vista di sistema mostra per ogni sessione i valori per una lunga lista di statistiche, le due che ci possono interessare qui sono:

• session pga memory
• session pga memory max

La descrizione sul manuale spiega come queste statistiche abbiano senso solo sulla V$SESSTAT e non sulla V$SYSSTAT dove evidentemente non viene riportato il dato aggregato

DBA_HIST_ACTIVE_SESS_HISTORY

Premesso che questa tabella fa parte di AWR e quindi è soggetta a licenza del “tuning e diagnostics” Pack che si paga come extra sulla Enterprise Edition, riporto questa tabella non perché io la usi di frequente ma perché mi è stata indicata come fonte per individuare query che hanno richiesto più PGA. In effetti, dall’esempio che ho riportato sopra per la V$PROCESS_MEMORY si può vedere che risulta un processo che ha occupato oltre 1500 MB di work area. A meno di vedere in tempo reale le viste V$SESSION e V$PROCESS (o catturare a intervalli regolari in stile ASH il loro contenuto) non ho trovato modi per ricavare per statement/query l’utilizzo di PGA. Una alternativa è V$SQL_WORKAREA ma questa ha solo il campo LAST_MEMORY_USED, non c’è un MAX_MEMORY_USED. Per cui sembrerebbe che facendo un raggruppamento per SQL_ID, SQL_EXEC_ID (sul quale invito alla lettura di questo) si possa avere una indicazione degli statement (sql_id) che hanno richiesto maggiore PGA in un dato momento. Facendo query su snapshot limitati (se si va oltre un certo intervallo temporale il SQL_EXEC_ID potrebbe essere lo stesso ma non necessariamente l’esecuzione, ad esempio fra un giorno e l’altro…) in effetti ASH fotografa la situazione delle sessioni attive, per ciascuna sessione ricava sia la PGA_ALLOCATED che il SQL_ID dello statement in esecuzione, quindi lo statement dovrebbe essere quello che causa l’allocazione di PGA.

select sql_id,sql_exec_id,count(*),max(pga_alloc_mem) from dba_hist_active_sess_history where snap_id>=x and snap_id<=y group by sql_id,sql_exec_id;

Conclusioni

Partendo dall’esigenza di analizzare l’utilizzo di PGA in una ambiente di produzione e di individuare i “colpevoli” di elevato uso di PGA ho fatto un ripasso di tutti gli strumenti/mezzi che Oracle mette a disposizione facendo una piccola panoramica su cosa è la PGA, su come e perché viene utilizzata e infine su dove visualizzarne le informazioni e le statistiche di uso.

Riferimenti:

1. https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=20851480822242&id=1520324.1&_afrWindowMode=0&_adf.ctrl-state=rze9i5k0x_4
2. https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/
3. https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328
4. https://blog.tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/
5. https://oracle-base.com/articles/12c/resource-manager-per-process-pga-limits-12cr2
6. https://fritshoogland.wordpress.com/2014/12/15/oracle-database-operating-system-memory-allocation-management-for-pga/

LOB e temporary tablespace in Oracle

I LOB (Large OBject) sono un tipo dato introdotto nei database relazionali per estenderne l’utilizzo per gestire tipi dato non strutturato quali grandi file binari e grandi file di testo. La caratteristica principale del tipo dato LOB, come indica il nome, è quella di poter immagazzinare oggetti molto grossi, quindi si può trattare di file pdf, fotografie digitali, file eseguibili o qualunque altro tipo di file binario di piccole o grosse dimensioni; possono essere anche file di testo come file html e xml. I file binari e i file di testo vengono gestiti in modo leggermente differente, per cui ci sono due tipi di LOB: BLOB (Binary LOB) per i dati binari e CLOB (Character LOB) per i dati formato testo. Non posso e non voglio fare qui una descrizione dettagliata dei LOB nei database relazionali, dico solo che sono una estensione che permette di consolidare la gestione anche di file all’interno di un database relazionale.

In Oracle il tipo dato LOB ha un limite sulla dimensione massima piuttosto alto, come spiegato qui il limite è i (2^32-1)*<CHUNK SIZE> byte e in condizioni di default la chunk size è pari alla dimensione del blocco. Con la classica dimensione del blocco da 8 KB fa un limite di quasi 32 TB.

I LOB, vista la possibilità che siano di grosse dimensioni, devono essere gestiti attraverso delle funzioni specifiche. Esiste una interfaccia PL/SQL ed essendo un tipo dato “standard” esiste una implementazione delle funzioni di gestione anche nei driver per Java JDBC. Siccome dove lavoro si usa quest’ultima interfaccia su questa mi sono dedicato un po’ e la uso per descrivere l’argomento oggetto di questo post.

Tutto è nato dal fatto che su una installazione del cliente abbiamo verificato che le sessioni aperte sul database dall’applicazione mantengono una quota di spazio nella temporary tablespace dell’utente Oracle allocata. Dopo aver fatto un classica ricerca, prima genericamente su internet e poi sul supporto Oracle ho trovato qualche indizio ed ho cominciato a fare qualche test con lo scopo di circoscrivere le possibili cause e individuare delle soluzioni.

l riferimento per utilizzare LOB su un database Oracle in programmi java è il manuale “Oracle JDBC Developer’s Guide“. Ho cercato di leggerlo ma piuttosto frettolosamente quindi non ho ancora ben chiari tutti i meccanismi. La parte che mi interessa sembra essere quella del paragrafo 14.5 dove si parla di “Temporary LOB” i quali sono fatti per dati di passaggio (transient data). Quando in java si utilizza un metodo che implementa l’interfaccia java.sql.Connection.createBlob viene creato un temporary lob. Il temporary blob è di fatto un variabile di tipo LOB che utilizza spazio allocato sulla temporary tablespace configurata per l’utente. Ho verificato che se da un programma java “prelevo” un campo blob con questo tipo di codice:

Blob blob_ris;
rs.next();
blob_ris = rs.getBlob(“contenuto”);

non viene creato un temporary lob, in questo caso si ottiene un puntatore direttamente al LOB interessato. Non viene allocato spazio sulla TEMP (interrogando la v$tempseg_usage non esce nulla). Utilizzando la chiamata ((BLOB)blob_aris).isTemporary() se ne ha la conferma. Viceversa, se si deve creare un dato di tipo LOB da zero da un programma Java è possibile utilizzare la chiamata del codice simile a questo:

Blob myblob =conn.createBlob();

Come scritto sopra si tratta di una chiamata di un metodo che implementa l’interfaccia java.sql.Connection.createBlob, che crea un temporary lob che utilizza spazio allocato sulla temporary tablespace.

Uno dei problemi che può capitare con i temporary lob è che lo spazio allocato nella temp non venga rilasciato. La nota del supporto Oracle 802897.1 spiega come in effetti se all’interno della sessione si effettua la chiamata al metodo “free” (o freeTemporary) lo spazio viene rilasciato all’interno della sessione e può essere riutilizzato all’interno della stessa sessione ma non è disponibile per le altre sessioni. Per liberarlo definitivamente occorre chiudere la sessione. La stessa nota dice che dalla versione 10.2.0.4 è stato introdotto l’evento 60025 che permette di liberare completamente lo spazio senza dover chiudere la sessione. Si deve quindi eseguire il comando:

alter session set events ‘60025 trace name context forever’;

L’evento si dovrebbe poter attivare a livello di sistema, però questo non l’ho provato.

La nota 1384829.1 riporta un programmino java che dimostra come funziona l’evento 60025. La premessa nella nota è che il test è stato fatto con driver jdbc 11.2.0.3 su datatabase 11.2.0.3. Io ho cominciato a giocare con il programma e fare delle prove con il driver che utilizziamo al momento maggiormente, versione 12.1 con una patch. Con questo driver il programma non funziona come descritto, cioè lo spazio nella temporary tablespace non viene liberato se non con la chiusura della connessione. Ho provato con varie versioni di driver 12.1, con java 6 e java 7 ma il risultato è sempre lo stesso, lo spazio non viene rilasciato. Poi mi sono deciso a provare con il driver 18.3; dalla 12.2 in poi il driver jdbc viene fornito solo per jdk 8, se si vuole usare versioni jdk precedenti Oracle suggerisce di usare driver precedenti. Con la versione 18.3 l’evento 60025 torna a funzionare, ovvero la chiamata lob.free() libera lo spazio definitivamente sulla temporary tablespace. Presumo quindi che ci sia qualche problema con i driver 12.1 e l’evento 60025; anche con un driver 11.2.0.3 il programma di test funziona. Ho provato a fare una ricerca più mirata sul supporto Oracle e la cosa più pertinente che ho trovato è stata la nota 2297060.1 che fa riferimento a questo problema e indica fra le versioni interessate dall 9.2 alla 12.2. Quello che viene scritto nelle conclusioni e nella sezione “solution” non mi è affatto chiara. Ho provato allora a fare delle varianti del programmino java per capirne di più. Il risultato è che a un certo punto non capivo proprio nulla perché sembrava che anche con i driver 12.1 funzionasse la liberazione dello spazio TEMP.  La verità è che effettivamente con l’evento 60025 attivo la chiamata del metodo lob.free() libera definitivamente lo spazio occupato dal temporary LOB. Il motivo per cui sembra che l’esempio non funzioni con i driver 12.1 è la riga di codice:

System.out.println(rs.getString(1));

che si trova prima dell’instanziazione della variabile/oggetto lob. Commentando via quella riga di codice l’esempio funziona anche con i driver 12.1.

Riassumento quello che ho capito. Se eseguo una query come questa:

select to_clob(‘bla’) from dual; 

con il codice:

ResultSet rs = stmt.executeQuery(SQL1_syntax);

viene gia creato un temporary lob, viene allocato dello spazio nella temporary tablespace. Anche chiudendo il ResultSet lo spazio non viene liberato; l’unico modo per liberarlo è instanziare l’oggetto, ad esempio con una semplice chiamata come questa:

my_clob = rs.getClob(1);

e poi chiamare il metodo free dell’oggetto:

my_clob.free();

Se l’evento 60025 è stato attivato lo spazio nella temporary tablespace viene liberato.

La chiamata rs.getString(1) probabilmente crea un riferimento che impedisce per motivi che ignoro che lo spazio nella temp venga liberato.

Riferimenti:

1. How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1)
2. How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)

 

SQL*Plus 18 e login.sql

Ieri (questo post in verità è stato scritto fra il 24 e il 25 Ottobre) ho scaricato l’ultima versione del client Oracle “instant client”, versione 18.3 per windows. Volevo prima di tutto vedere come funzionava la gestione dinamica della lunghezza della linea come descritto qui. In verità io non sono riuscito a ottenere grandi risultati, nel senso che con tabelle con diverse colonne non mi ridimensiona le colonne per farle stare su una riga, si limita ad allargarsi allo spazio disponibile sulla finestra. Quello però su cui sono rimasto bloccato prima di indagare ulteriormente sull’impaginazione dei risultati è il fatto che sqlplus non mi carica la configurazione iniziale con il file login.sql. La mia memoria è un po’ arruginita e solo dopo un po’ mi sono ricordato che era un argomento che avevo gia affrontato; in effetti è passato un po’ di tempo. Partendo dalla fine di quel post ho scoperto che c’è una patch (25804573) che dovrebbe correggere il fatto che sqlplus su windows (erroneamente distribuiscono la patch per tutte le piattaforme ma sembra riguardi solo windows come segnalato anche in questo commento) non prende in considerazione la variabile d’ambiente SQLPATH. Io ho provato la patch (si tratta dell’eseguibile sqlplus.exe, quindi ho potuto usarla con l’instant client sostituendo a mano il file) e apparentemente non ho visto differenze, nel senso che comunque al login non mi lancia il mio login.sql. La differenza è che con la patch il richiamo del file login.sql specificando l’estensione funziona:

SQL> @login.sql

PL/SQL procedure successfully completed.
Session altered.

Ora, sul sqlplus 18.3 dell’instant client il comportamento è leggermente cambiato:

SQL*Plus: Release 18.0.0.0.0 – Production on Thu Oct 25 09:30:44 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Last Successful login time: Thu Oct 25 2018 09:27:54 +02:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0

SQL> @login.sql
SP2-0310: unable to open file “c:\ORACLE\conf\LOGIN.SQL♂”
SQL> @c:\oracle\conf\login.sql

PL/SQL procedure successfully completed.
Session altered.

.

Devo dire che qui sono rimasto un po’ confuso e per essere sicuro ho rifatto la prova più e più volte perché il carattere ♂ pensavo di averlo messo io facendo confusione con la tastiera e mi era sembrato che richiamando il comando con la “history, ovvero scorrendo con le freccie della tastiera, cancellando la “l” e ridigitandola funzionasse ma poi “ripulendo” l’ambiente e ripedento il test ho avuto finalmente un comportamento ripetibile che è quello sopra. La variabile d’ambiente SQLPATH è impostata a C:\ORACLE\conf e gli altri script presenti in quel percorso vengono trovati senza problemi:

SQL> @banner

BANNER_FULL
——————————————————————————–
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0
SQL> @banner.sql

BANNER_FULL
——————————————————————————–
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0

Quindi c’è una restrizione solo su login.sql.

Una evoluzione che ho trovato sul 18.3 è che se SQLPATH punta a un percorso sotto quello in cui sta l’eseguibile di SQLPLUS il caricamento automatico di login.sql funziona:

C:\ORACLE\sqlplus>set SQLPATH=C:\ORACLE\instantclient_18_3\conf

C:\ORACLE\sqlplus>sqlplus testuser/testuser@svil183p1.overit.it

SQL*Plus: Release 18.0.0.0.0 – Production on Thu Oct 25 10:02:28 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0
Session altered.
PL/SQL procedure successfully completed.
Session altered.

TESTUSER@svil183p1.overit.it > @banner
SP2-0310: unable to open file “banner.sql”

 

sinceramente non ho trovato menzione di questa restrizione. Ciò scombussola un pochino la mia configurazione con tante versioni del client e il “repository” di script unico; per ovviare devo copiare o meglio spostare tutti gli script sotto la directory di installazione dell’instantclient 18.3 e passare all’uso esclusivo di quella versione.

Ho fatto una veloce prova sul server Linux dove ho installato Oracle 18c, impostando ad esempio ORACLE_PATH=/home/oracle  e ficcando li dentro il file login.sql viene caricato senza problemi.

 

 

SQLcl, UTF-8 e windows

Oggi ho provato a scaricare e vedere com’è l’ultima versione di SQL Developer, la 18.2. Ho dato una rapida occhiata e non ho visto cose stratosferiche. Il problema è che più invecchio più mi sento distante dall’informatica moderna dove tutti vogliono interfacce grafiche, mentre io mi ostino a usare  e preferire per molte attività interfacce a linea di comando. Continuo a non vedere grandi vantaggi. D’altra parte continuo a ritenere che i migliori sistemi sono quelli che hanno la possibilità di essere controllati con comandi a linea di comando che permettono di prepare script che rendono le attività ripetitive una cosa assai facile. Poi sopra ci si può implementare interfacce grafiche che aiutano per attività più sporadiche o per dare delle panoramiche di un sistema. Anche Oracle implementa tutto o quasi via SQL, poi c’è la console grafica Cloud Control che permette senz’altro una gestione globale e generale più facile. Ho un po’ divagato rispetto al tema che voglio trattare in questo post. Su SQL Developer ho visto un link a SQLcl, una sorta di sostituto di SQL*Plus, ovvero un client a linea di comando SQL. SQLcl mi sembra per molte cose compatibile con SQL*Plus ma con in più qualche miglioria, anche qui mi vien da dire non cose stratosferiche. Ho provato a vedere se c’è la “dynamic linesize”, come su SQL*Plus 18.1, ma non l’ho trovato, con un certo sconforto. Poi mi è venuto in mente di controllare come gestisce la codifica in UTF-8, al riguardo negli anni ho avuto sempre qualche problema ed ho scritto una serie di post, l’ultimo dovrebbe essere questo. Quel post è del 2013, siamo arrivati al 2018 e poco è cambiato. Ho fatto un rapido test per vedere come SQLcl gestisce la codifica e il risultato è stato negativo. Ho quindi fatto una rapida ricerca, arrivando qui. Credo di esserci gia passato qualche tempo fa ma probabilmente non ho avuto modo di approfondire ne’ scrivere qualcosa, ho quindi deciso di farlo oggi. Senza farla tanto lunga, per gestire un po’ la codifica di caratteri particolari in UTF-8 occorre, sotto windows sempre settare il font a “Lucida Console”, settare il codepage a 65001 (chcp 65001) e poi, non basta. A quanto pare SQLcl non usa le variabili d’ambiente, quindi settare o meno NLS_LANG=.AL32UTF8 come faccio con SQL*Plus non sembra fare differenza e lo stesso Jeff Smith afferma che non vengono lette variabili d’ambiente per settare gli NLS. Siccome quanto affermato nel post con la mia installazione di SQLcl su win7 non funziona,

-----------
Luned�
Marted�
Mercoled�
Gioved�
Venerd�
Sabato
Domenica

ho scorso i commenti, fino ad arrivare a questo. Le cose migliorano:

 Lunedì

 Martedì

 Mercoledì

 Giovedì

 Venerdì

 Sabato
 Domenica

C’è una anomalia nella formattazione che sembra che in coda al carattere accentato venga messo un accapo, anche se non è così… boh.

Le cose se facciamo una prova con una cosa tipo: “select ‘è’ from dual;” non vanno benissimo:


CRISTIAN@svil > select '�' from dual;

‘
–
è

Che poi è sempre meglio di SQL*Plus:

CRISTIAN@svil > select 'è' from dual;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

 

La risposta secondo me sta tutta in questo commento in cui Jeff Smith aspetta da MSFT un terminale decente…secondo me non c’è speranza

Oracle Resource Manager con CDB e PDB

Con l’introduzione dell’architettura “multitenant” in Oracle 12 si sono stati degli aggiornamenti e delle estensioni al funzionamento di Oracle Resource Manager che è lo strumento per ripartire in base a determinate esigenze le risorse disponibili su un server.  Confesso di non aver mai potuto testare e vedere in azione veramente il resource manager, credo di avere fatto solo delle piccole prove per gestire sessioni inattive e bloccanti ma non sono andato oltre. Oltre dieci anni fa (!) ho scritto un mio riassunto di cui ho parlato in questo post.  Dopo tanto tempo ho l’ambizione di fare un post riassuntivo delle novità del resource manager nell’ambito di Container database e Pluggable database.

Con l’architettura multitenant si è aggiunto un livello ulteriore su cui gestire le risorse in un database Oracle. Per cui in Oracle 12 su un database CDB il resource manager lavora a due livelli:

• a livello CDB, in cui smista le risorse fra i PDB
• a livello di PDB che come nella versione precedente smista le risorse fra gli utenti del singolo PDB.

Faccio riferimento a questa sezione del manuale Oracle.

CDB Resource Plans

un CDB resource plan alloca le risorse ai PDB in base al suo insieme di direttive (resource plan directives).

C’è una relazione padre-figlio tra un CDB resource plan e le sue direttive. Ogni direttiva fa riferimento a un PDB e non ci possono essere due direttive nel piano correntemente attivo che fanno riferimento allo stesso PDB.

Le direttive controllano l’allocazione delle seguenti risorse ai PDB:

• CPU
• Parallel execution server.

Le direttive di un CDB resource plan sono molto semplici, hanno tre parametri:

• SHARE: rappresenta la fetta di CPU e di parallel server riservata al PDB
• UTILIZATION_LIMIT: è un limite superiore alla percentuale di CPU utilizzabile dal PDB
• PARALLEL_SERVER_LIMIT: è un limite superiore percentuale (sul valore impostato tramite il parametro PARALLEL_SERVERS_TARGET)

Esiste una direttiva di default per i PDB per cui non è stata definita una direttiva e assegna uno share e un limite del 100% sia per UTILIZATION_LIMIT e PARALLEL_SERVER_LIMIT.   Questa direttiva può essere modifica con la procedura dedicata del package DBMS_RESOURCE_MANAGER UPDATE_CDB_DEFAULT_DIRECTIVE.

C’è poi un’altra direttiva predefinita, chiamata AUTOTASK che si applica ai task di gestione automatica (automatic maintenance tasks) che girano nel root duranto le finestre di manutenzione. Questa direttiva può essere modificata con la procedura UPDATE_CDB_AUTOTASK_DIRECTIVE

Una cosa curiosa, scritta sul manuale, è che se un PDB viene scollegato “unplugged” la direttiva che lo riferisce viene mantenuta e nel caso il PDB venga ricollegato viene riutilizzata. Qui ho una lacuna da colmare perché mi sembra di ricordare dai miei test che dopo l’unplug l’unica cosa che si può fare con un PDB e la rimozione, dopodiché si riesce a ricollegare; mi sembra strano che venga mantenuto un riferimento a un PDB rimosso… spero di poter fare una prova al riguardo.

PDB Resource Plans

A livello di PDB si definiscono dei piani in maniera analoga a quanto si faceva prima su database non-CDB, ci sono però dei limiti:

• un PDB resource plan non può avere sottopiani
• un PDB resource plan può avere un massimo di otto consumer group
• un PDB resource plan non può avere politiche di schedulazione multi-livello (multiple-level scheduling policy)

Se si collega come PDB un database non-CDB che ha dei resource plan che non rispettano i vincoli sopra Oracle modificherà i piani per farli rientrare nei vincoli.

P.S. 18/06/2018

sono riuscito a fare una prova riguardo al definire un CDB plan con delle direttive che fanno riferimento a un PDB. Ho fatto successivamente l’unplug e poi il drop del PDB ed effettivamente la direttiva che riferisce il pluggable database rimosso rimane.

Copiare, spostare o rinominare un datafile in Oracle

Breve post promemoria sui concetti che si possono trovare su questa parte dei manuali Oracle. Il motivo per cui lo faccio è che faccio fatica a memorizzare la sintassi dei vari comandi Oracle e con la versione 12.1 c’è la possibilità di copiare o spostare o rinominare i file “online”.  Per supportare questa nuova funzionalità Oracle ha introdotto una nuova sintassi, il comando è:

ALTER DATABASE MOVE DATAFILE ( ‘filename’ | ‘ASM_filename’ | file_number )
[ TO ( ‘filename’ | ‘ASM_filename’ ) ]
[ REUSE ] [ KEEP ]

Il bello di questo comando è che si occupa lui anche di copiare “fisicamente” il file. L’unica restrizione che trovo sul manuale è che il datafile deve essere in modalità “online” altrimenti il comando restituisce errore. Pare quasi una banalità…

Nella modalità pre-12 c’erano due possibili comandi:

• ALTER DATABASE RENAME FILE…

• ALTER TABLESPACE <tbsname> RENAME DATAFILE …

 

in entrambi in casi occorre prima portare i datafile offline in qualche modo (nel secondo si può mettere offline solo la tablespace interessata, nel primo si può mettere in stato mount l’intero database, questo nel caso la rinomina o lo spostamento riguardi più file…) in questo caso poi, dopo aver portato in modalità offline i file occorre, prima di eseguire i comandi copiare/spostare/rinominare i file “manualmente”,  il comando si limita ad aggiornare le informazioni registrate sul controlfile.

Gestione Audit su database Oracle

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

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.

Standby database per Oracle standard edition 11gR2

Sono passati oltre quatto anni da quando scrissi questo post in cui parlai dell’implementazione del sistema di disaster recovery basato sullo standby database su Oracle standard edition. Qualche tempo fa mi è stato chiesto se eravamo in grado di mettere in piedi questa soluzione, siccome però sono passati gli anni  però, ora la nostra versione di riferimento è oracle 11.2 e non più 10.2 con la quale feci i test a suo tempo. C’è da dire che a suo tempo ho anche messo in piedi una implementazione completa, con tutta la gestione automatizzata del trasferimento degli archived log e della loro applicazione sullo standby database su una installazione con Oracle 9.2.

Con un po’ di pazienza sono riuscito a crearmi un ambiente virtualizzato (con virtual box, che per questi test sembra funzionare molto bene). Ho creato quindi una macchina virtuale con Oracle Linux 6.3 64 bit, vi ho installato Oracle Database 11.2.0.3. A questo punto ho clonato la macchina e in qualche modo ho sistemato il nome e l’indirizzo IP. Ho creato un database sulla prima macchina, poi ho seguito i miei appunti usati a suo tempo per Oracle 9.2 per creare lo standby database e fare qualche prova: il risultato è che funziona tutto allo stesso modo in cui funzionava su Oracle 9.2 e Oracle 10.2. L’unico inghippo l’ho riscontrato quando ho fatto un test che forse a suo tempo non avevo fatto: l’aggiunta di una nuova tablespace con relativo database; in questo caso mi sono imbattuto nel problema ben descritto qui, così ho scoperto l’esistenza del parametro STANDBY_FILE_MANAGEMENT. Per rendermi la vita facile quello che ho fatto è stato di modificare il parametro e ricreare da zero lo standby database.

Ieri sono riuscito a fare un ulteriore test, il cui esito al momento pare positivo, cioè applicare l’aggiornamento alla versione 11.2.0.4.  Mi sono trovato un po’ spiazzato con questo aggiornamento, per due motivi: il primo è che non ho trovato al suo interno le solite istruzioni (ho trovato la guida “Upgrade” fra la documentazione ufficiale ma questa, a voler essere pignoli, si riferisce alla versione 11.2.0.3; il secondo motivo di spiazzamento è che fare l’aggiornamento “in-place” è stato reso complicato da Oracle. Ho trovato comunque lo spazio per fare l’aggiornamento “out-of-place” come richiesto, quindi creando una nuova Oracle Home. Alla fine della parte di aggiornamento della parte “software” l’installer lancia automaticamente DB Upgrade Assistant (DBUA) che nonostante i miei timori ha eseguito la parte di aggiornamento del database senza errori.

Il bello però viene nell’aggiornamento dello standby database. La mia idea era quella di aggiornare il software e sperare che la sincronizzazione tramite l’applicazione degli archived logs funzionasse aggiornando anche lo standby database. Ciò che è accaduto è che lanciando anche sullo standby l’installer di 11.2.0.4 è andato tutto bene fino alla parte di aggiornamento del software, poi è partito DBUA il quale però manco trovava le informazioni sul database da aggiornare, in quanto in effetti non aveva le informazioni registrate sul file /etc/oratab. Poco male, anzi stavo per aggiungerle quando mi sono reso conto che comunque DBUA non avrebbe potuto fare nulla perché lo standby database non è apribile in scrittura (se non con una switch-over) . Io qui ho interrotto la procedura ed ho provveduto manualmente ad applicare gli archived logs generati sul primary e tutto pare aver funzionato bene, ho fatto nuovi test e pare funzionare come atteso (l’unica cosa che non ho detto è che ho copiato il contenuto della directory “dbs” dalla vecchia alla nuova Oracle Home.

Prossimamente conto  di fare lo stesso test con Oracle 12.1

Ancora Character Set, ORA-00600 [kafspa:columnBuffer2]

Non c’è dubbio che la gestione del Character Set sui database Oracle sia una cosa tutt’altro che banale e poco chiara ai più. Ieri controllando i log di un database interno utilizzato per i test ho trovato il seguente errore:

ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kafspa:columnBuffer2], [4053], [4000], [], [], [], [], []

Il database è un 10.2.0.5 su linux 64 bit, recentemente migrato da una vecchia macchina dove c’era però una versione 10.2.0.3. Ho passato i dati dal vecchio database al nuovo tramite expdp/impdp dei vari schemi (approfittando per fare un po’ di pulizia). L’errore veniva generato durante il calcolo delle statistiche notturno e nell’alert.log c’era rimando a un file di trace su cui ho trovato anche dettagli sulla tabella che provocava l’errore. Il secondo e terzo argomento dell’errore ORA-00600 mi ha fatto subito sospettare il tipo di problema (che fra l’altro in forme diverse ho gia incontrato altre volte. In effetti ho individuato nella tabella un campo definito come VARCHAR2(4000) facendo una query simile a questa:

SELECT NOMECAMPO FROM NOMETABELLA WHERE LENGTH(NOMECAMPO)>4000

ottenevo sempre l’errore  ORA-00600.

Facendo una ricerca sul supporto Oracle ho trovato poco, facendone una Google sono arrivato qui, come dire: a volte ritornano. Infatti in coda al post che non ricordavo di aver gia letto ho trovato dei miei commenti. Avendo anche cambiato il titolo del blog non mi ricordavo neanche chi fosse l’autrice 🙂

In realtà ho scoperto con un po’ di sorpresa che lo schema proveniva in effetti da un dump fornitoci dal cliente, che però io credevo avesse database con character set UTF-8 solo di recente sono riuscito a scoprire che in realtà il cliente utilizza charset ISO8859-15 e che quindi ero incappato sullo stesso problema. Il bello è che per trovare una soluzione, dopo alcuni banali tentativi per isolare i record con stringhe lunghe più di 4000 byte all’interno del campo incriminato, ho provato a fare un export con data pump e importare la tabella su un’altro schema dello stesso database, con una certa sorpresa mi sono trovato sul nuovo schema una copia della tabella con lo stesso problema.

Dopo vari esperimenti, alla fine, sfruttando la chiave primaria della tabella, che è un id generato da sequence,  ho isolato un record con il problema; in realtà ho trovato anche altri 6 record che su un campo varchar2(600) avevano stringhe che occupavano ben più di 600 byte ma questi record creavano problemi solo nei tentativi di CREATE TABLE AS …

In teoria ho risolto facendo un update sul campo per chiave:

UPDATE NOMETABELLA SET NOMECAMPO=SUBSTR(NOMECAMPO,1,4000) WHERE CAMPOCHIAVE=XXX

che con un certo mio sconcerto ha funzionato (apparentemente), in seguito ho fatto alcune prove, compreso il ricalcolo delle statistiche, senza riscontrare problemi, stamattina però nell’alert.log ho ritrovato l’errore sulla stessa tabella, in realtà mi aspettavo di trovare l’errore sulla copia che ho mantenuto con l’errore per fare ulteriori verifiche è così è stato anche se in un primo momento non era chiaro guardando il trace.

Stamattina ho avuto modo di fare ricerche più approfondite e mirate sul problema, leggendo nota del supporto Oracle intitolata:

“Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET (Doc ID 1283764.1)”

al paragrafo 1.d ho trovato la seguente frase:

Do NOT use Expdp/Impdp when going from (AL32)UTF8 to a single byte NLS_CHARACTERSET on ALL 10g versions lower then 10.2.0.4 (including 10.1.0.5). Also 11.1.0.6 is affected.
It will provoke data corruption unless Patch 5874989 is applied on the Impdp side. Expdp is not affected, hence the data in the dump file is correct.Also the “old” exp/imp tools are not affected.
This problem is fixed in the 10.2.0.4 and 11.1.0.7 patch set.
Fixed in 11.2.0.1 and up

 

che mi ha un po’ illuminato. In realtà ho dovuto un po’ ricostruire la storia. Sul nostro nuovo database di test 10.2.0.5 (che dovrebbe essere esente dal bug) ho due schemi dello stesso cliente, uno importato direttamente dal dump fornito dal cliente e su cui non si presenta il problema, infatti nei log dell’import trovo:

ORA-02374: conversion error loading table “SCHEMA2″.”NOMETABELLA”
ORA-12899: value too large for column NOMECAMPO (actual: 3995, maximum: 4000)
ORA-02372: data for row:

L’altro schema, sui ho trovato il problema, proveniva dallo stesso dump, ma era passato prima dal vecchio database 10.2.0.3, quindi era stato importato sul vecchio db, dove si era generata la corruzione (senza che io me ne accorgessi), poi da li è stato esportato  e importato sul nuovo database, senza errori nei log di data pump. Mi pare quindi che Oracle abbia corretto parzialmente il problema, perché impdp non genera più la corruzione sulla tabella ma scarta i dati in importazione, però, se la tabella è gia corrotta essa viene esportata e importata da datapump tale e quale, corruzione compresa. Prossimamente conto di provare a fare dei test su un Oracle 11.2.0.3, magari se ne trovo uno con character set ISO8859 posso fare dei test più completi.

Riguardo la gestione del character set su Oracle riporto qui un paio di riferimenti a note Oracle che vale la pena leggere:

• AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1)
• Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET (Doc ID 1283764.1)
• NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)