ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,pmucalm coll)

Problematica diffusa quando si ha una procedura PL/SQL o Packages che divorano memoria.
Nel mio caso lanciando un package di archiviazione di un software mi sono imbattuto in questo errore in una virtual machine Windows 2003 32bit con 3 GB di RAM, Oracle 10G R2.
Questo argomento è descritto anche nel doc Metalink ID 1325100.1.
Nel Metalink fanno eseguire un trace dove nel dump si vede benissimo che è un problema di allocazione di memoria.
Il_realfree_heap_pagesize_hint nella 10g fa sì che il processo di  private memory (PGA) usi pagine di allocazione più grandi, riducendo così TLB/TSB.
In questo caso occorre controllare due parametri nell'instanza:_use_realfree_heap e _realfree_heap_pagesize_hint che sono impostati per default.

select i.ksppinm name , v.ksppstvl cur_val,

v.ksppstdf default_val, v.ksppstvf

from x$ksppi i, x$ksppcv v

where i.indx = v.indx

and i.ksppinm in ('_realfree_heap_pagesize_hint', '_use_realfree_heap')



NAME                                     CUR_VAL    DEFAULT_V   KSPPSTVF

—————————————- ———- ——— ———-

_realfree_heap_pagesize_hint             65536      TRUE               0

_use_realfree_heap                       TRUE       TRUE               0

A questo punto provvedo a modificare solo il _realfree_heap_pagesize_hint :

alter system set "_realfree_heap_pagesize_hint" = 262144 scope=spfile;

e riavviare il db.

Una volta riavviato controllo se ha preso il nuovo parametro sempre con:

select i.ksppinm name , v.ksppstvl cur_val,

v.ksppstdf default_val, v.ksppstvf

from x$ksppi i, x$ksppcv v

where i.indx = v.indx

and i.ksppinm in ('_realfree_heap_pagesize_hint', '_use_realfree_heap')



NAME                                     CUR_VAL    DEFAULT_V   KSPPSTVF

—————————————- ———- ——— ———-

_realfree_heap_pagesize_hint             262144     TRUE               0

_use_realfree_heap                       TRUE       TRUE               0

Ci sono moltissimi esempi tra l'altro ben documentati, per quanto riguarda macchine ovviamente Solaris o Linux, ma in ambiente Windows purtroppo non ho trovato quasi nulla.

Aspetto feedback....

How to Convert Database from Standard to Enterprise Edition and viceversa ? [ID 117048.1]

In questo articolo vorrei illustrare il modo per poter convertire il database Oracle dalla versione Enterprise Edition alla versione Standard e viceversa, trattato dal documento ID 117048.1 del metalink.
Questa procedura permette la conversione senza in alcun modo che l’istanza installata con una o con l’altra versione venga modificata.
Nel mio caso converto una istanza dalla Enterprise Edition in una Standard ma funziona anche viceversa.
Il test è stato fatto in ambiente Solaris 10.5 con Oracle EE 11.0.2.3.
L’installazione della versione Enterprise è stata eseguita nel path /app/oracle/product/11.2.0.3/dba_1/dbs.
Il primo passo è quello di installare la versione Stardard con le relative librerie, in un’altra ORACLE_HOME, senza ovviamente creare nessuna altra istanza, praticamente solo il software.
…
Ho diviso per punti per maggior chiarezza.

1) Una volta installata, eseguire lo shutdown normal o immediate del database EE e fermare il LISTENER relativo.
La versione Standard è stata installata nel path /app/oracle/product/11.2.0.3/dba_se/dbs.

2) Copiare tutto il contenuto dal percorso della EE /oracle11g/app/oracle/product/11.2.0.3/dba_1/dbs

alla Standard /app/oracle/product/11.2.0.3/dba_se/dbs, dove ci sono tutte le informazioni di base dell’istanza come per esempio SP file e altro.
3) Copiare  tutto il contenuto /app/oracle/product/11.2.0.3/db_1/network/admin/ della EE in /app/oracle/product/11.2.0.3/db_se/network/admin/ alla SE, per allineare le impostazioni del LISTENER.
4) A questo punto eseguire alcune modifiche nel profile file .profile dell’utente specifico che esegue tutti i servizi oracle, che nel mio caso si chiama oracle.
Modificare l’enviroment dell’utente oracle relative alla ORACLE_HOME modificando :
ORACLE_HOME=/oracle11g/app/oracle/product/11.2.0.3/db_1 con ORACLE_HOME=/oracle11g/app/oracle/product/11.2.0.3/db_se; export ORACLE_HOME
E il listener:
TNS_ADMIN=$ORACLE_BASE/product/11.2.0.3/db_1/network/admin  con
TNS_ADMIN=$ORACLE_BASE/product/11.2.0.3/db_se/network/admin/; export TNS_ADMIN
5) Eseguire un altro login con l’utente oracle oppure ricaricare le nuove variabili e fare ripartire il LISTENER con il commando:
LSNRCTL start
Poi verificare con lo stato con  LSNRCTL status

LSNRCTL for Solaris:Version 11.2.0.3.0 - Production on 07-DEC-2012 17:31:27
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Solaris: Version 11.2.0.3.0 - Production

Start Date                07-DEC-2012 17:26:44

Uptime                    0 days 0 hr. 4 min. 46 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle11g/app/oracle/product/11.2.0.3/db_se/network/admin/listener.ora

Listener Log File         /oracle11g/app/oracle/diag/tnslsnr/jdesun/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=acme)(PORT=1523)))

The listener supports no services

The command completed successfully

LSNRCTL> exit

Notiamo che il LISTENER ha caricato le impostazione dale nuove librerie della SE.
Prima di eseguire lo start del database ho provveduto a modificare il path della versione EE per verificare se l’istanza viene caricata con le nuove library della versione SE.
Quindi dalla shell Solaris eseguo il comando:
mv -R /app/oracle/product/11.2.0.3/dba_1  /oracle11g/app/oracle/product/11.2.0.3/dba_ee
6) Adesso posso eseguire lo startup dell’istanza eseguendo il login come SYS.

sqlplus “/ as sysdba”

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 9 14:52:55 2012

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

Connected to:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Startup

Verificare con il commando l’esito con:

SELECT BANNER FROM V$VERSION;

BANNER                                                                       

-------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production                       

PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0
TNS for Solaris: Version 11.2.0.3.0 - Production      
NLSRTL Version 11.2.0.3.0 - Production     

Enterprise Manager Console

Ho verificato inoltre la disponibilità del EMC di Oracle , facendo emctl stop dbconsole e successivamente emctl start dbconsole.
Compare un errore per la mancanza della cartella JDESUN_JDE900 nella nuova ORACLE_HOME SE.
copio da /oracle11g/app/oracle/product/11.2.0.3/db_1_ee/JDESUN_JDE900 in /oracle11g/app/oracle/product/11.2.0.3/db_se/ con
cp -R  JDESUN_JDE900 /oracle11g/app/oracle/product/11.2.0.3/db_se/

Faccio un controllo per un eventuale cambio di permission e di owner da parte  OS.

emctl start dbconsole

OC4J Configuration issue. /oracle11g/app/oracle/product/11.2.0.3/db_se/oc4j/j2ee/OC4J_DBConsole_JDESUN_JDE900 not found.

Copio /oracle11g/app/oracle/product/11.2.0.3/db_1_ee/oc4j/j2ee in /oracle11g/app/oracle/product/11.2.0.3/db_se/oc4j/j2ee
cp -R OC4J_DBConsole_JDESUN_JDE900 /oracle11g/app/oracle/product/11.2.0.3/db_se/oc4j/j2ee

emctl start dbconsole



https://JDESUN:5500/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ............................................................................................. failed.

------------------------------------------------------------------

Logs are generated in directory /oracle11g/app/oracle/product/11.2.0.3/db_se/JDESUN_JDE900/sysman/log

Apro il log per verificare l'errore:

2012-12-10 11:33:16,255 Thread-85 ERROR engine: [host,JDESUN,PagingActivity] : nmeegd_GetMetricData failed : /oracle11g/app/oracle

/product/11.2.0.3/db_1/bin/nmupm: not found

2012-12-10 11:33:16,255 Thread-85 WARN  collector: <nmecmc.c> Error exit. Error message: /oracle11g/app/oracle/product/11.2.0.3/db

_1/bin/nmupm: not found

2012-12-10 11:33:16,256 Thread-85 ERROR command:  "/oracle11g/app/oracle/product/11.2.0.3/db_1/bin/nmupm" file not found, launch aborted due to (errno=2: No such file or directory)

Andando a verificare ho visto che EM puntava ancora ai vecchi binari della EE nel path ..../db_1/...che però precedentemente per sicurezza avevo modificato in db_1ee
Ad istanza già avviata ho rinominato da .../db_1ee/... in .../db_1/... e rilanciato:

$ emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0

Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.

https://JDESUN:5500/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started.

------------------------------------------------------------------

Logs are generated in directory /oracle11g/app/oracle/product/11.2.0.3/db_se/JDESUN_JDE900/sysman/log

Eseguo il login con l’utente per esempio SYS creato  precedentemente.
Noto che effettivamente trattandosi di un SE non posso entrare nel menu Performance abilitato solo per la versione EE.



Ovviamente questo si tratta di un test manuale su come cambiare EMC dalla EE alla SE, esiste sempre e comunque la soluzione classica eseguendo:

emca -deconfig dbcontrol db -repos drop

e successivamente

emca -config dbcontrol db -repos create

Aspetto commenti e ulteriori test...

Beginning Performance Tuning

Volevo consigliare la lettura di questi due articoli di Arup Nanda in merito a "Beginning Performance Tuning".
Scritti come sempre in modo chiaro e preciso per un buon inizio della tecnica di tuning delle performance a 360 gradi.

Beginning Performance Tuning

Beginning Performance Tuning: Diagnose the Past

WM_CONCAT Function Problem

Un rapido aggiornamento per questa funzione che sembra avere dei problemini con la temporary  tablespace in 10GR2 e 11GR2.
Ottimi test cases ed info nel blog di Asanga.
Da leggere.

ORA-00904: WM_CONCAT: invalid identifier

Utilizzando Sql Developer nel modulo DBA nella versione 3.1.07, andando a vedere i backupset nel menu di RMAN, mi sono imbattuto nel seguente errore:ORA-00904: WM_CONCAT invalid identifier.
Ho notato però che in 1 istanza funzionava correttamente, mentre nelle altre appariva questo errore.
Premetto che questo è un Bug di Sql Developer conosciuto, risolto a quanto dicono nella versione 3.2.
Da una ricerca in rete, ho letto che questo problema si verifica solo nella versione Oracle 11g con Sql Developer, mentre nella 10G il problema non era menzionato.
Questo era molto strano, visto che nel mio caso si verificava proprio nella 10G.

La funzione WM_CONCAT è una funzione di aggregazione presente fino alla 10G, anche se non supportata da Oracle ma molto usata e diffusa dagli sviluppatori, visto che è stata utilizzata anche per SqlDeveloper.

Dalla versione 11G è stata introdotta invece una nuova funzione di aggregazione, che si chiama LISTAGG  pienamente supportata da Oracle.

La funzione WM_CONCAT viene installata solo con  l'Oracle Workspace Manager, infatti facendo un controllo ho visto che nel database dove funziona questa feature è stata installata, mentre negli altri non c'era.

Molte volte succede che alcune applicazioni che si basano su database Oracle non installino di proposito il WorkSpace, visto che quest'ultimo viene utilizzato maggiornamente dagli sviluppatori in quanto contiene moltissime funzioni e che in ambiente di produzione non essendo magari necessarie, non vengono utilizzate.

Infatti eseguendo

SELECT COMP_ID, COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

CATALOG Oracle Database Catalog Views 10.2.0.3.0 VALID

CATPROC Oracle Database Packages and Types 10.2.0.3.0 VALID

JAVAVM JServer JAVA Virtual Machine 10.2.0.3.0 VALID

XML Oracle XDK 10.2.0.3.0 VALID

CATJAVA Oracle Database Java Packages 10.2.0.3.0 VALID

EM Oracle Enterprise Manager 10.2.0.3.0 VALID

il Workspace Manager risulta essere non installato mentre negli altri

CATALOG Oracle Database Catalog Views 10.2.0.3.0 VALID

CATPROC Oracle Database Packages and Types 10.2.0.3.0 VALID

JAVAVM JServer JAVA Virtual Machine 10.2.0.3.0 VALID

XML Oracle XDK 10.2.0.3.0 VALID

CATJAVA Oracle Database Java Packages 10.2.0.3.0 VALID

EM Oracle Enterprise Manager 10.2.0.3.0 VALID

OWM Oracle Workspace Manager 10.2.0.1.0 VALID 

L'Oracle Workspace Manager si può installare successivamente come SYS lanciando

$ORACLE_HOME/rdbms/admin/owminst.plb

Una volta finito lo script andiamo a verificare con la solita query

SELECT COMP_ID, COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

CATALOG Oracle Database Catalog Views 10.2.0.3.0 VALID

CATPROC Oracle Database Packages and Types 10.2.0.3.0 VALID

JAVAVM JServer JAVA Virtual Machine 10.2.0.3.0 VALID

XML Oracle XDK 10.2.0.3.0 VALID

CATJAVA Oracle Database Java Packages 10.2.0.3.0 VALID

EM Oracle Enterprise Manager 10.2.0.3.0 VALID

OWM Oracle Workspace Manager 10.2.0.1.0 VALID

Adesso andando con Sql Developer a lanciando tramite il modulo DBA la lista dei backup set nella apposita sezione funziona correttamente.

ORA-25254: time-out in LISTEN while waiting for a message

Tramite un trigger LOG_ERRORS con caratteristica di "AFTER EVENT" presente nel DB insieme con altri packages, scrivo tutti gli errori in una tabella in modo tale che posso verificare ogni giorno lo stato del DB.
Nel mio caso si verificava ripetutamente ORA-25254.

Verificando attraverso il metalink, ho scoperto che si tratta del Bug 3498760.
Questo errore è relativo all'applicazione AQ che è configurata con un timeout di DEQUEUE che specifica il tempo effettivo di attesa per l'arrivo dei messaggi, se non ci sono messaggi in questo timeout il sistema genera questo errore.
Questo errore non è classificato come crittical e si potrebbe tranquillamente tralasciare, ma nel mio caso visto che scrivo tutti gli errori nella mia tabella di controllo è un problema, perchè la fa crescere a dismisura.

Ci sono un paio di risoluzione proposte in merito la prima è quella di interrompere il job che esegue EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS() ma questo potrebbe avere delle ripercussioni nel EM.
La seconda è quella meno drastica e preferibile, si cambia semplicemente il valore di timeout di DBMS_AQ.LISTEN.

Andiamo in $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/notification e modifichiamo il file notification_pkgbodys.sql nella sezione:

DBMS_AQ.LISTEN (agents, qtimeout_in, agent)

cambiandola in: 

DBMS_AQ.LISTEN (agents, 1200, agent);

entrare come utente SYSMAN con SQLPlus ed eseguire lo script notification_pkgbodys.sql.

Eventualmente per sicurezza è possibile fare una copia del file notification_pkgbodys.sql originale prima di modificarlo.

ORA-25228: TIMEOUT OR END-OF-fetch during message dequeue FROM SYS.ALERT_QUEUE

Dopo una analisi di tutti i log relativi OEM mi sono imbattuto nel file emagent.trc.
In questo file trace ho trovato un errore ripetuto moltissime volte: ORA-25228: TIMEOUT OR END-OF-fetch during message dequeue FROM SYS.ALERT_QUEUE.
Come prima cosa ho fatto una verifica nel Metalink per un rapido controllo .
Infatti si tratta del Bug 6484482.
Quest'ultimo si verifica quando il trigger "after servererror" contiene errori di codice SQL, ossia non sta funzionando come dovrebbe e scrivendo nella tabella "log_servererr" genera degli errori:

2007-09-18 12:15:14 Thread-134875 ERROR vpxoci: Error on dequeue from SYS.ALERT_QUE: ORA-00604:error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 30
ORA-25228: timeout or end-of-fetch during message dequeue from SYS.ALERT_QUEUE

Il metalink fornisce anche la soluzione così:

SQL> select trigger_name from dba_triggers where trigger_type = 'AFTER EVENT' and triggering_event ='ERROR ';
TRIGGER_NAME
LOG_ERRORS

Nel mio caso per poi disabilitarlo con

ALTER TRIGGER LOG_ERRORS DISABLE;

Facendo ripartire i DB e l'agent non dovrebbero più esserci questi errori, ma  trovando questa risoluzione un pò radicale anche perchè il trigger nel caso mio è utile al posto di disabilitarlo ho provveduto a modificarlo inserendo:

if ora_login_user not in ('DBSNMP','SYSMAN') then...

else...

End if;

In questo modo il trigger resta valido per tutti a parte gli utenti DBSNMP e SYSMAN, proprio quelli di OEM.
Nel mio caso ho trovato questa soluzione ottimale.