Real-Time Database Operation Monitoring

giovedì 7 giugno 2018 alle 07:24 | Pubblicato su 11g, 12c, Diario, Performance Tuning | Lascia un commento

Con la versione 11.1 Oracle ha introdotto un nuovo strumento di diagnosi e analisi delle prestazioni chiamato “Real-Time SQL Monitoring”. Si tratta di uno strumento che permette di controllare o monitorare in tempo reale l’esecuzione di query o statement SQL che durano a lungo e consumano risorse in modo significativo. Oracle in determinate condizioni, quando in una sessione gira una query che attiva l’esecuzione in parallelo o consuma più di 5 secondo di tempo CPU o I/O, comincia a registrare le principali statistiche di esecuzione dello statement quali tempo CPU, letture su disco, letture da buffer ecc e le espone tramite due viste chiamate V$SQL_MONITOR e V$SQL_PLAN_MONITOR.  Le statistiche vengono aggiornate ogni secondo e successivamente mantenute per almeno 5 minuti (qui dice così, qui dice un minuto), dopodiché se l’area di memoria in SGA dove risiedono queste informazioni è in esaurimento le statische possono venir cancellate. Si tratta di una evoluzione significativa rispetto alle analisi che si potevano e si possono ancora fare sulle classiche viste V$SQL e V$SQL_PLAN perché ad esempio le statistiche esposte sulle nuove viste sono legate alla singola esecuzione e non cumulative come nelle tradizionali V$SQL e V$SQL_PLAN. Vi è poi la possibilità, tramite procedure del package DBMS_SQLTUNE, di   avere dei report dettagliati e completi sull’esecuzione di una singola query: troviamo piano di esecuzione, valori delle bind variables. Ci sono  statistiche come il numero di righe attese e quello reale che prima era difficile avere. Naturalmente queste informazioni sono accessibili in modo comodo dalle console grafiche Cloud Control e, per 12c,  EM Express.

Con la versione 12.1 il concetto di Real-Time SQL Monitoring è stato esteso ed è diventato “Real-Time Database operation monitoring”. E’ possibile dalla versione 12c avere le stesse informazioni ma non più solo a livello di singolo statement SQL o blocco PL/SQL ma anche per un blocco di istruzioni delimitato temporalmente all’interno di una sessione che è definito appunto Database Operation. Ad esempio è possibile così monitorare complesse procedure ETL o altri tipo di procedure batch per tenere traccia dell’esecuzione ed individuare problemi di prestazioni o controllarne il consumo di risorse. Con oracle 12c è stato introdotto un nuovo package PL/SQL chiamato DBMS_SQL_MONITOR che fornisce le procedure “BEGIN_OPERATION” e “END_OPERATION” che servono appunto a delimitare le istruzioni che compongono la “database operation” che si vuole monitorare e analizzare. Oltre a quelle due procedure nel package si ritrovano le procedure “REPORT_SQL_MONITOR” e “REPORT_SQL_MONITOR_LIST” che si trovano ancora nel package DBMS_SQLTUNE, non so quale sia la differenza.

Posso solo segnalare una mancanza nel report, che da un lato da indicazioni in tempo reale anche sul punto in cui si trova l’esecuzione, dall’altro però non include le “predicate information” che si hanno ad esempio con la funzione “dbms_xplan.display_cursor”. Questo è un peccato perché costringe a fare una analisi con più strumenti per avere tutte le informazioni che possono essere utili ad analizzare le prestazioni di una query.

“Real-Time Database operation monitoring” è una “feature” del pacchetto “Oracle Database Tuning Pack” (per il quale quindi serve apposita licenza).

 

Annunci

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.

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/

Il mistero di ORA-00333

lunedì 12 gennaio 2015 alle 12:18 | Pubblicato su 11g, Backup and Recovery, Diario | 3 commenti

Un paio di nostri database server si appoggiano a una NAS, usando iscsi, per lo spazio disco. Uno è un server linux e l’altro window, entrambi hanno dei dischi iscsi su questa Nas esterna e su questi dischi ci sono dei database di sviluppo e test interni. Ogni tanto, per svariati motivi capita che la NAS ha un mancamento, in questa situazione quello che accade sulla macchina linux è che il filesystem, quanto la NAS si riprende, viene automaticamente rimontato in modalità read-only, cosa che naturalmente non piace ad Oracle. Sulla macchina Windows il disco sembra venga rimontato normalmente, ciò nonostante Oracle non la prende comunque bene.

Questa mattina è successo di nuovo, in precedenza è andata sempre abbastanza bene, sulla macchina linux basta fermare Oracle, smontare e rimontare in modalità read-only il disco e riavviare Oracle, sulla macchina Windows basta solo riavviare Oracle, fino ad oggi tali passi erano stato sufficienti. Stamattina però le cose sono andate storte, sulla macchina Windows i due database segnalavano in fase di apertura l’errore

ORA-00333: errore lettura redo log blocco 34873 conteggio 7758

Naturalamente cambiavano i numeri di “blocco” e “conteggio”, quello su linux data un errore di disallineamento dei controlfile:

ORA-00214: control file
‘/opt/oracle/storage/recovery_area/marte112/control02.ctl’ version 1093680
inconsistent with file ‘/opt/oracle/storage/data/marte112/control01.ctl’
version 1093666

Quest’ultimo problema l’ho  affrontato copiando il file con il numero di versione più alto sopra l’altro. Fatto ciò anche qui in fase di apertura del database ho ricevuto il messaggio:

ORA-00333: redo log read error block 55748 count 8192

Ho cercato di mantenere la calma e vedere cosa si poteva fare. Per due database avevo a disposizione un backup della notte prima, quindi ero abbastanza tranquillo, per l’altro non c’è backup per motivi di spazio e perché non è così importante. Ho fatto delle ricerce sull’errore, senza capire però effettivamente la sua origine, perché parrebbe dovuto al fatto che si sia corrotto un online redolog, però facendo una ricerca su internet si trova, anche sul forum Oracle come soluzione un recovery incompleto e la successiva apertura del database con l’opzione RESETLOGS. Messa così la cosa ha senso, si ferma il recovery senza applicare il redo contenuto negli online redo, che a quanto pare sono corrotti e si riesce a far ripartire il database perdendo solo quanto contenuto negli online redolog.

Le cose però non stanno proprio così, perché quello che ho fatto io è stato di applicare anche il redo degli online redolog:

SQL> recover database until cancel;
ORA-00279: change 10685641157734 generated at 01/11/2015 01:14:20 needed for
thread 1
ORA-00289: suggestion :
/opt/oracle/storage/recovery_area/MARTE112/archivelog/2015_01_12/o1_mf_1_28964_%
u_.arc
ORA-00280: change 10685641157734 for thread 1 is in sequence #28964

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/storage/data/marte112/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00333: redo log read error block 55748 count 8192

 

Ecco, siccome questo era uno dei database di cui avevo il backup ho deciso di fare un piccolo azzardo, pensando non fosse tanto azzardato, ho cercato di aprire il database con l’opzione NORESETLOGS, così da vedere cosa succedeva. Il punto è che il mio recovery mi sembrava completo, non incompleto, perché aveva terminato da solo, non in seguito a un mio “CANCEL”, il redo che ho indicato era l’unico che dalla vista V$LOG risultava attivo e contenente la sequenza richiesta dalla procedura di recovery (gli altri due risultavano anche archiviati), quindi in questa fase Oracle è riuscito ad aprire e leggere l’online redolog senza problemi, senza darmi ORA-00333, come mai? Io non l’ho capito.

Torniamo adesso al mio azzardo/esperimento di aprire il database con l’opzione NORESETLOGS, i miei successivi tentativi sono stati questi:

SQL>  alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/opt/oracle/storage/data/marte112/system01.dbf’

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/opt/oracle/storage/data/marte112/system01.dbf’

 

Al che ho cominciato a preoccuparmi perché mi scocciava dover ripristinare tutto il backup. Al che ho deciso di fare un disperato tentativo, disperato perché per la verità senza cognizione di causa:

SQL>  recover database using backup controlfile until cancel;
ORA-00279: change 10685641181646 generated at 01/11/2015 03:06:33 needed for
thread 1
ORA-00289: suggestion :
/opt/oracle/storage/recovery_area/MARTE112/archivelog/2015_01_12/o1_mf_1_28964_%
u_.arc
ORA-00280: change 10685641181646 for thread 1 is in sequence #28964

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/storage/data/marte112/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

 

Ho cioè deciso di provare il recovery incompleto aggiungendo l’opzione “using backup controlfile”, e mi è andata bene 🙂

Il mio controlfile era sempre lo stesso, non era stato ripristinato da un backup. Che cosa significhi veramente la clausola “using backup controlfile” non sono sicuro di averlo ancora capito bene, la migliore spiegazione che ho trovato è questa. Però mi sfugge cosa abbia fatto il mio primo tentativo si recovery… mi piacerebbe capirne di più

P.S.

naturalmente sugli altri due database è stato più semplice, è bastato usare subito  l’opzione RESETLOGS

Ricerche case sensitive in Oracle 11gR2 e 12cR1

venerdì 22 agosto 2014 alle 22:03 | Pubblicato su 11g, 12c, Diario, Performance Tuning | Lascia un commento
Tag: , ,

Questo post è un aggiornamento del post “Database case sensitive su 10.2.0.3“,  aggiornamento necessario, essendo il post risalente ad oltre sette anni fa. In realtà ho fatto qualche test in questi sette anni ma non ben formalizzato, quando poi mi è stato chiesto per l’ennesima volta se e come era possibile fare ricerche case insensitive su database Oracle (versione 11.2) ho prima dato la solita risposta, poi però mi è venuto lo scrupolo di ricontrollare se fosse cambiato qualcosa sulla versione che attualmente usiamo maggiormente (11.2.0.3 o 11.2.0.4) e sulla nuova 12.1 che installai qualche mese fa per i primi test sulle novità della versione. Riporto sotto il test completo che ho eseguito rispettivamente su un Oracle 10.2.0.5, un 11.2.0.3 e un 12.1.0.1, prima però anticipo alcune conclusioni: con la 11.2.0.3 le cose sono migliorate rispetto alla 10.2, mentre fra la 11.2 e la 12.1 non noto differenze rilevanti, neanche confrontanto velocemente lo stesso capitolo della documentazione nelle tre versioni:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 15:57:31 2014

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@geotutf8 > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@geotutf8 > create sequence stest_ci_search;

Sequence created.

CRISTIAN@geotutf8 > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search where id<=100000
;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > commit;

Commit complete.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@geotutf8 >  set autotrace on
CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 18365057

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |    46 |  3772 |   913   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| TEST_CI_SEARCH |    46 |  3772 |   913   (1)| 00:00:11 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STRING1" LIKE 'cris%')

Note
-----
- dynamic sampling used for this statement

&nbsp;


C:\tmp>sqlplus cristian/cristian@//10.110.3.52/salespdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 13:08:07 2014

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

ERROR:
ORA-28002: the password will expire within 7 days

Last Successful login time: Wed Apr 09 2014 15:21:53 +02:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@//10.110.3.52/salespdb > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@//10.110.3.52/salespdb > create sequence stest_ci_search;

Sequence created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search wh
ere id<=100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > commit;

Commit complete.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@//10.110.3.52/salespdb > set autotrace on
ERROR:
ORA-28002: the password will expire within 7 days

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 1977262958

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |   780 |   159K|  1125   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_CI_SEARCH     |   780 |   159K|  1125   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_CI_SEARCH_IDX |  1404 |       |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(NLSSORT("STRING1",'nls_sort=''BINARY_CI''')>=HEXTORAW('6372697300') AND
NLSSORT("STRING1",'nls_sort=''BINARY_CI''')<HEXTORAW('6372697400'))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

In sintesi, fino a Oracle 10.2.0.5 era possibile abilitare ricerche su stringhe in modalità case insensitive, il problema era però che dall’uso dell’indice linguistico erano escluso l’operatore LIKE, dalla versione 11.2 in poi invece anche con l’operatore LIKE viene usato l’indice. Ho riportato solo i test per 10.2.0.5 e 12.1.0.1, per 11.2.0.3 il risultato è uguale a quello di 12.1.0.1

Novità sulla gestione dello spazio in Oracle 11gR2

venerdì 30 maggio 2014 alle 30:22 | Pubblicato su 11g | 1 commento

Sono un po’ indietro,  questo spiega un post su una novità introdotta con Oracle 11gR2 (in realtà parte con 11.2.0.2) a diversi mesi dall’uscita di oracle 12c. Sto cercando di non rimanere indietro e di non perdermi informazioni, quindi sto ripassando il manuale “Oracle® Database New Features Guide” versione 11gR2, quando avrò finito con quello potrò permettermi di passare all’equivalente manuale versione 12c R1. La maggior parte delle nuove funzionalità e caratteristiche introdotte da Oracle non mi interessa, cerco di contentrarmi su quelle che posso sfruttare utilmente, senza sprecare risorse a studiare a tappeto tutto. Una su cui secondo me vale la pena soffermarsi, o meglio due, sono la gestione dello spazio, tramite l’uso del comando “TRUNCATE” e tramite l’uso del package DBMS_SPACE_ADMIN.

Il comando TRUNCATE è stato esteso per poter richiedere o  meno il rilascio dello spazio allocato dalla tabella. Il comando usato come nelle versioni precedenti, ad esempio:


TRUNCATE TABLE test_truncate;

Si comporta come nelle versioni precedenti di Oracle (lasciando allocati gli extent eventualmente indicati con il parametro MINEXTENT). Quanto sopra è equivalente, con la nuova estensione a:


TRUNCATE TABLE test_truncate DROP STORAGE;

Vi sono poi due possibilità nuove, la prima è lasciare lo spazio allocato per la tabella allocato, in tal caso il comando usa l’opzione “REUSE STORAGE”:


TRUNCATE TABLE test_truncate REUSE STORAGE;

All’estremo opposto vi è la possibilità di rilasciare tutto, ma proprio tutto lo spazio allocato dalla tabella eliminando il segmento, in questo caso l’opzione è “DROP ALL STORAGE”:


TRUNCATE TABLE test_truncate DROP ALL STORAGE;

Cerco di riportare un po’ di esempi per fare capire meglio.


SVILUPPO40@SVIL112_METHONE > create table test_truncate(a number, b varchar2(4000));

Table created.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name = 'TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE > begin
2  for i in 1..10000 loop
3  insert into test_truncate values (1,rpad('testxx',4000,'z'));
4  end loop;
5  end;
6  /

PL/SQL procedure successfully completed.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                             80

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate REUSE STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                             80

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate ;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                          .0625

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate REUSE STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                          .0625

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate DROP ALL STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE > DROP TABLE TEST_TRUNCATE PURGE;

Table dropped.


SVILUPPO40@SVIL112_METHONE > create table test_truncate(a number, b varchar2(4000)) STORAGE (MINEXTENTS 100);

Table created.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE > INSERT INTO test_truncate values (1,'a');

1 row created.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                            104

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                            104

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate DROP ALL STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE >

Parallelamente al package DBMS_SPACE_MANAGEMENT sono state aggiunte due procedure:

  1. DROP_EMPTY_SEGMENTS
  2. MATERIALIZE_DEFERRED_SEGMENTS

Il primo può essere utile in caso di database migrati, in cui ci sono magari un sacco di tabelle vuote per cui, provenendo da versioni Oracle precedenti alla 11.2 dove non esisteva la “deferred segment creation”, vengono comunque creati i segmenti. Va detto che nei miei casi si tratta comunque di briciole di spazio per cui non so quanto valga la pena rompersi, ma se uno vuole raschiare il fondo così può farlo 🙂 Il package DBMS_SPACE_ADMIN anche se non scritto in modo chiarissimo sul manuale, mi risulta utilizzabile solo da utente SYS, invocando la procedure DROP_EMPTY_SEGMENTS specificando solo il primo parametro (lo schema) viene spazolato tutto lo schema e per le tabelle vuote vengono eliminati i segmenti, altrimenti è possibile specificare le tabelle per cui si vuole agire.

L’utilità della procedura MATERIALIZE_DEFERRED_SEGMENTS mi viene ancora più difficile da immaginare, comunqu, per completezza fa esattamente il contrario della precedente, crea il segmento di partenza per tutte le tabelle.

Standby database per Oracle standard edition 11gR2

mercoledì 15 gennaio 2014 alle 15:36 | Pubblicato su 11g, Installation and Configuration | Lascia un commento
Tag: , ,

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

Inghippi con il Character Set – Conclusione

giovedì 1 agosto 2013 alle 01:23 | Pubblicato su 11g, Diario, SQL, Varie | Lascia un commento
Tag:

Un titolo decisamente ottimista per un post su questo argomento, la mia speranza è comunque di chiudere l’argomento ripreso non molto tempo fa con il post “Inghippi con il Charcter SET“.  L’ispirazione mi è venuta leggendo la nota del supporto Oracle

NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)

che a sua volta mi ha rimandato alla nota:

The correct NLS_LANG in a Microsoft Windows Environment (Doc ID 179133.1)

Entrambe le note precisano che per gestire in modo corretto il character set è meglio utilizzare SQL Developer, però danno anche altri spunti interessanti. Ad esempio viene specificato:

The “Unicode” “chcp 65001” and “chcp 65000” values are NOT supported with sqlplus.exe.

Poi usano un character set che forse non avevo mai notato: WE8PC850 che pare andare molto d’accordo con il CP 850 settato di default sulle macchine Windows.

Sempre adottando i corretti settaggi ho verificato che si può aggirare i limiti di sqlplus adottando comandi registrati su file e poi invocandoli da sqlplus tramite il comando START, bisogna però fare attenzione alla codifica del file su cui si salvano i comandi, ecco  un esempio:

C:\tmp>chcp 65001
Tabella codici attiva: 65001

C:\tmp>set NLS_LANG=.AL32UTF8

C:\tmp>more insert_test_charset.sql
Memoria insufficiente.

C:\tmp>SQLPLUs sviluppo50/sviluppo50@svil112_methone

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 1 13:17:23 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
PL/SQL procedure successfully completed.
Session altered.

SVILUPPO50@svil112_methone > delete from test_charset;

8 rows deleted.

SVILUPPO50@svil112_methone > @insert_test_charset.sql

1 row created.
1 row created.

SVILUPPO50@svil112_methone > SELECT * FROM TEST_CHARSET;

A
—————————————————————————————————-
KKKKà
NNNè

SVILUPPO50@svil112_methone >

Il file è formato utf-8 (senza il famigerato BOM) e si noti come il comando more fallisca con il messaggio “memoria insufficente”

Il contenuto del file insert_test_charset.sql è:

INSERT INTO TEST_CHARSET VALUES (‘KKKKà’);
insert into test_charset values (‘NNNè’);

In realtà poi, lavorando con caratteri compresi nella codifica West-Europe, usare UTF-8 lato client nel mio caso è superfluo, credo che d’ora in poi userò WE8PC850

Pagina successiva »

Blog su WordPress.com.
Entries e commenti feeds.