Category: Tuning / Performance

Calculando el tamaño actual de la Shared Pool



Me hicieron una consulta con respecto al tamaño actual de la Shared Pool cuando Oracle trabaja con ASMM (Automatic Shared Memory Management) o manejo automático de memoria.

Y el primer pensamiento fue casi instintivo

"Dentro de la gv$parameter2"



mm , pero dando una vuelta al tema, en esa vista aparece la Shared Pool Size en 0 , ¿el porqué? , pues con ASMM 5 buffers de memoria son trabajados de forma automática


DB_CACHE_SIZE
JAVA_POOL_SIZE
STREAMS_POOL_SIZE
LARGE_POOL_SIZE
SHARED_POOL_SIZE


Siempre los mostrará en 0 , ya que internamente el maneja un tamaño en esos buffers total y absolutamente dinámico

La pregunta calza de inmediato ¿Cómo veo el valor actual de la Shared Pool ?, no importanto si tengo o no ASMM |-|

Y añadiendo mas complejidad

¿Cómo obtengo el tamaño de la Shared Pool en una ambiente con ASMM y sin ASMM en versiones Oracle9i, Oracle10g y Oracle11g? CON EL MISMO SCRIPT??? :crazy:

Pues aca esta el ensayo de esa pequeña problemática

SI sga_target > 0 ENTONCES

Valor actual dentro de la SGA

select name ,
bytes/1024/1024 MB
from v$sgainfo
where upper(name) like '%SHARED POOL SIZE%'

Válido para : Oracle10g , Oracle11g
No válido para : Oracle9i

o

Valor actual del parámetro , como resize del MMAN y MMON

select component ,
current_size/1024/1024 MB
from v$sga_dynamic_components
where component like '%shared pool%'


Válido para : Oracle9i , Oracle10g , Oracle11g

0

Esta forma de consultar la shared pool , es la mas interna ya que no pasamos por ninguna vista del diccionario de datos

select b.ksppinm "Parametro re-oculto",
a.KSPFTCTXVL "Bytes",
a.KSPFTCTXDVL "Mb"
from X$KSPPCV2 a ,
x$ksppi b
where b.indx + 1 = a.kspftctxpn
and b.KSPPINM like '__shared_pool_size'

Válido para : Oracle10g , Oracle11g
No válido para : Oracle9i

o

Ejecutando el comando CREATE PFILE FROM SPFILE
Y dentro del init creado , buscar el parámetro __shared_pool_size

SQL> CREATE PFILE FROM SPFILE;

Válido para : Oracle10g , Oracle11g
No válido para : Oracle9i


SINO SI sga_target = 0 ENTONCES (con y sin bajada de instancia)

Valor actual dentro de la SGA

select name ,
bytes/1024/1024 MB
from v$sgainfo
where upper(name) like '%SHARED POOL SIZE%'

Válido para : Oracle10g , Oracle11g
No válido para : Oracle9i


o

Valor actual del parámetro , como resize del MMAN y MMON

select component ,
current_size/1024/1024 MB
from v$sga_dynamic_components
where component like '%shared pool%'

Válido para : Oracle9i , Oracle10g , Oracle11g

0

Esta forma de consultar la shared pool , es la mas interna ya que no pasamos por ninguna vista del diccionario de datos

select b.ksppinm "Parametro re-oculto",
a.KSPFTCTXVL "Bytes",
a.KSPFTCTXDVL "Mb"
from X$KSPPCV2 a ,
x$ksppi b
where b.indx + 1 = a.kspftctxpn
and b.KSPPINM like '__shared_pool_size'


Válido para : Oracle10g , Oracle11g
No válido para : Oracle9i


o

Mediante el parámetro seteado en el archivo de inicialización

SELECT value/1024/1024||' [MB]' alias_shared_pool_size
FROM gv$parameter2
WHERE name = 'shared_pool_size'
AND value <> '0'


Válido para : Oracle9i , Oracle10g , Oracle11g

FIN SI.

Saquen ustedes sus propias conclusiones para obtener ese maldito valor :>>

by Ligarius
07.08.09. 16:23:44. 562 words, 7600 views. Categories: Tuning / Performance ,

Aplicando el primer PSU (Patchet Set Update) de mi vida



Por fin ha salido el esperado PSU (Patch Set Updates) que es una recopilación de Bugs dados a conocer por clientes de Oracle a nivel mundial que utilizan base de datos 10g (10.2.0.4 y 10.2.0.5).

No es igual el Critical Patch Update, sino , que son los Bugs conocidos por todos, si quieres saber más detalles del los PSU , debes ver esta nota AQUI

Para ubicar los PSU , debemos irnos a los parches recomendados de Oracle

Note 756671.1 : Oracle Recommended Patches -- Oracle Database

El parche lo podemos ubicar como parche número 8576156



Una vez que bajamos el parche procedemos a instalarlo.

1.- Como primer requisito , se valida la existencia del OPatch 10.2.0.4.7

Lo validamos en nuestro motor Oracle
Lamentablente (era de suponer) hay que actualizar el OPatch

[oracle@oracle10g 8576156]$ opatch version
Invoking OPatch 10.2.0.4.2

OPatch Version: 10.2.0.4.2

OPatch succeeded.
[oracle@oracle10g 8576156]$

2.- Actualizando el OPatch a 10.2.0.4.7

Para ello , debemos descargar el parche 6880880 , seleccionando el release 10.2.0.0.0

La instalación es simplemente descomprimir el parche en la ruta $ORACLE_HOME con esto se sobreescribe la carpeta Opatch

Validamos nuevamente nuestra versión actualizada de OPatch

[oracle@oracle10g 8576156]$ opatch version
Invoking OPatch 10.2.0.4.7

OPatch Version: 10.2.0.4.7

OPatch succeeded.
[oracle@oracle10g 8576156]$

Y ahora sí!!!! :)

3.- Determinar los posibles conflictos con parches ya existentes

Se debe descomprimir el parche y quedará algo así

[oracle@oracle10g PSU]$ ls -ltr
total 16484
-rw-r--r-- 1 oracle oinstall 16847645 Jul 16 2009 p8576156_10204_Linux-x86-64.zip
drwxrwxr-x 5 oracle oinstall 4096 Jul 15 21:00 8576156
[oracle@oracle10g PSU]$ pwd
/home/oracle/PSU
[oracle@oracle10g PSU]$

Desde la ruta donde vemos la carpeta del parche, ejecutamos el siguiente comando

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./8576156

Invoking OPatch 10.2.0.4.7

Oracle Interim Patch Installer version 10.2.0.4.7
Copyright (c) 2009, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u01/app/oracle/product/10.1.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.7
OUI version : 10.2.0.4.0
OUI location : /u01/app/oracle/product/10.1.0/db_1/oui
Log file location :

/u01/app/oracle/product/10.1.0/db_1/cfgtoollogs/opatch/opatch2010-07-16_04-15-45AM.log

Patch history file: /u01/app/oracle/product/10.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

4.- Aplicando el parche

Para aplicar el parche, simplemente ejecutamos el comando opatch apply dentro de la carpeta del parche , pero deben recordar que la base de datos debe estar abajo

[oracle@oracle10g 8576156]$ opatch apply
Invoking OPatch 10.2.0.4.7

Oracle Interim Patch Installer version 10.2.0.4.7
Copyright (c) 2009, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/10.1.0/db_1
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.7
OUI version : 10.2.0.4.0
OUI location : /u01/app/oracle/product/10.1.0/db_1/oui
Log file location : /u01/app/oracle/product/10.1.0/db_1/cfgtoollogs/opatch/opatch2010-07-16_09-21-52AM.log

Patch history file: /u01/app/oracle/product/10.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '8576156' to OH '/u01/app/oracle/product/10.1.0/db_1'


Running prerequisite checks...
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: hector.ulloa@gmail.com
Provide your My Oracle Support password to receive security updates via your My Oracle Support account.
Password (optional):


Unable to establish a network connection to Oracle. If your systems require a
proxy server for outbound Internet connections, enter the proxy server details
in this format:
[@][:] If you want to remain uninformed of critical security issues in your configuration, enter NONE Proxy specification: NONE

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/10.1.0/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '8576156' for restore. This might take a while...
Backing up files affected by the patch '8576156' for rollback. This might take a while...
Execution of 'sh /home/oracle/PSU/8576156/custom/scripts/pre -apply 8576156 ':

Return Code = 0

ms.rsf, 10.2.0.4.0...
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libgeneric10.a" with "lib/libgeneric10.a/qcodfdef.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libgeneric10.a" with "lib/libgeneric10.a/qcdo.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libgeneric10.a" with "lib/libgeneric10.a/qcdo.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libgeneric10.a" with "lib/libgeneric10.a/kghs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libgeneric10.a" with "lib/libgeneric10.a/kgcc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libgeneric10.a" with "lib/libgeneric10.a/kgh.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libcommon10.a" with "lib/libcommon10.a/kd4.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libcommon10.a" with "lib/libcommon10.a/kdb4.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libcommon10.a" with "lib/libcommon10.a/kdr4.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libcommon10.a" with "lib/libcommon10.a/kdr9ir24.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libcommon10.a" with "lib/libcommon10.a/ttcpip.o"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/rdbms/jlib/qsma.jar" with "/rdbms/jlib/qsma.jar/oracle/qsma/QsmaDataManager.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/rdbms/jlib/qsma.jar" with "/rdbms/jlib/qsma.jar/oracle/qsma/QsmaDataReports.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/rdbms/jlib/qsma.jar" with "/rdbms/jlib/qsma.jar/oracle/qsma/QsmaFileManager.class"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/lib/env_rdbms.mk"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/lib/rfsd.o"

Patching component oracle.rdbms.util, 10.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 10.2.0.4.0...
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtdefr.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtbpp.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtdmsu.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtbstr.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtsum.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtsms.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtjob.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtaqds.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtlmc.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtlmd.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtlsby.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/prvtbcap.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/dbmssum.sql"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/e1001000.sql"


Patching component oracle.rdbms, 10.2.0.4.0...
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kupp.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kwqn.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ctc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qccpub.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kkxe.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kkxs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kzvdve.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/sldbg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kkz.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/updexe.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qerhj.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ain.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qerlxt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qkexr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/atb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qol.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kksc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qke.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kzrt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/opiprs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kxto.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kaf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kaf9ir2.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kd.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kd9ir2c.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kd9ir2t.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdb9ir2o.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdblc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kds.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdst.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdst00.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdst01.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdst10.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdst11.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qertb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/nsoqbc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/vop.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kksl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/evar2v.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbz.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbw.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbv.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbo.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbk.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfafo.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfcfg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfipc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfmon.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfmon1.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfra.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrdb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrla.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrld.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrpa.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrpd.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrst.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfrxpt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfsicd.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfsl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfsx.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/rfupg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knld.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdo.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kdt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ktu.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/koklm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kokeg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvi.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvrda.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvu.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvx.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvxb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvxp.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvxr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvxs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knaha.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knahf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knahs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knals.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knasp.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knlc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knlcf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knlci.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ktuq.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krfr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krff.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kct.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kccr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kck.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcp.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcra.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcrf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcrfr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcrlc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcrp.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcrr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcv.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krd.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krfg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krfw.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krsc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krsf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krsl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krsm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksct.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kspt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/tbsdrv.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/dbsdrv.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfd.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfgb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfk.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfk0.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kqf.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjfc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjfd.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjfm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksp.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kslt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kji.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjbr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfcb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfcl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfrb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kwqmn.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksu.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksq.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ktc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbz.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbw.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbv.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbo.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbk.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjbl.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksxp.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjdd.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjdr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjb.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kjbm.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcrfw.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/krvg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/knld.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/prsc.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/prsg.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/prssz.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kzrt.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kpolon.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kxfx.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ktss.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/qertqo.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kxfq.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksws.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/rdbms/lib/libperfsrv10.a" with "rdbms/lib/libperfsrv10.a/qcodfdef_PERF.o"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/cpu/view_recompile/recompile_precheck_jan2008cpu.sql"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/cpu/view_recompile/view_recompile_jan2008cpu.sql"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/psu/10.2.0.4.1/catpsu.sql"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/psu/10.2.0.4.1/catpsu_rollback.sql"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/bundledata_PSU.xml"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/catbundle.sql"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/lib/jox.o"

Patching component oracle.javavm.server, 10.2.0.4.0...
Copying file to "/u01/app/oracle/product/10.1.0/db_1/lib/libjox10.so"

Patching component oracle.sysman.repository.core, 10.2.0.4.0a...
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emDB.jar" with "/sysman/jlib/emDB.jar/oracle/sysman/db/rsc/inst/RsrcMonitorMsg.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emDB.jar" with "/sysman/jlib/emDB.jar/oracle/sysman/db/rsc/inst/RsrcMonitorMsgID.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emDB.jar" with "/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/rsrcmgr/RsrcmgrMonController.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emDB.jar" with "/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/MemoryController.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emDB.jar" with "/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/SessionDetailsController.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emDB.jar" with "/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/SitemapController.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emDB.jar" with "/sysman/jlib/emDB.jar/oracle/sysman/emo/perf/bean/sesn/SessionDetails.class"

Patching component oracle.sysman.console.db, 10.2.0.4.0...
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_instance/_sitemap/_healthgif.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_instance/_sitemap/_healthgif$__jsp_StaticText.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_instance/_sitemap/_health.class"
Updating jar file "/u01/app/oracle/product/10.1.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_instance/_sitemap/_health$__jsp_StaticText.class"

Patching component oracle.ovm, 10.2.0.4.0...
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/owmctrg.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/owmadmb.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/owmadms.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/owmasrtb.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/owmltb.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/owmutlb.plb"
Copying file to "/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/owmutls.plb"

Patching component oracle.network.rsf, 10.2.0.4.0...
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/lib/libn10.a" with "lib/libn10.a/nacom.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/network/lib/libnlsnr10.a" with "network/lib/libnlsnr10.a/nsglgr.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/network/lib/libnlsnr10.a" with "network/lib/libnlsnr10.a/nsglro.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/network/lib/libnlsnr10.a" with "network/lib/libnlsnr10.a/nsgcs.o"
Updating archive file "/u01/app/oracle/product/10.1.0/db_1/network/lib/libnlsnr10.a" with "network/lib/libnlsnr10.a/nsglsn.o"

Patching component oracle.network.listener, 10.2.0.4.0...
Running make for target iextjob
Running make for target iextjobo
Running make for target client_sharedlib
Running make for target idgmgrl
Running make for target ioracle
Running make for target client_sharedlib
Running make for target itnslsnr
ApplySession adding interim patch '8576156' to inventory

Verifying the update...
Inventory check OK: Patch ID 8576156 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8576156 are present in Oracle Home.

--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
** ATTENTION **
** **
** Please note that the Patch Set Update Installation (PSU Deinstallation) **
** is not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this PSU, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************

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

Execution of 'sh /home/oracle/PSU/8576156/custom/scripts/post -apply 8576156 ':


Return Code = 0

The local system has been patched and can be restarted.


OPatch succeeded.

5.- Parchar si se utiliza el job Scheduler

Si utilizamos job scheduler en nuestro sistema, hay que ejecutar la shell psu_root.sh como usuario root


[oracle@oracle10g 8576156]$ su root
Password:
[root@oracle10g 8576156]# sh psu_root.sh
[root@oracle10g 8576156]# exit

6.- Se debe ejecutar un último archivo sql, llamado catbundle.sql
De la siguiente forma


SQL> start ?/rdbms/admin/catbundle.sql psu apply

SQL> PROMPT Processing Oracle Data Mining...
Processing Oracle Data Mining...
SQL> ALTER SESSION SET current_schema = dmsys;

Session altered.

SQL> @?/rdbms/admin/prvtdmsu.plb
SQL> CREATE OR REPLACE PACKAGE dmp_sec wrapped
2 a000000
3 1
4 abcd
...
...
...
SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '10.2.0.4',
9 1,
10 'PSU',
11 'PSU 10.2.0.4.1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/product/10.1.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2010Jul16_10_18_59.log
SQL> quit

La salida de este archivo es enorme :0
0000"> 7.- Se deben recompilar las vistas de la base de datos

Si quieres verificar que se han recompilado las vistas de la base de datos, se puede ejecutar la siguiente consulta , la base de datos se debe abrir en modo UPGRADE

SQL> conn / as sysdba
Connected.
SQL>

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2139095041 bytes
Fixed Size 12666562 bytes
Variable Size 838891844 bytes
Database Buffers 1258291205 bytes
Redo Buffers 29245440 bytes
Database mounted.
Database opened.

SQL>
SQL> SELECT * FROM registry$history where campo_id = '6452863';

no rows selected

SQL> quit

Si no devuelve datos, es que se debe ejecutar el siguiente archivo

/home/oracle/PSU/8576156/files/cpu/view_recompile/recompile_precheck_jan2008cpu.sql

SQL> start recompile_precheck_jan2008cpu.sql

Running precheck.sql...

Number of views to be recompiled :2081
-----------------------------------------------------------------------

Number of objects to be recompiled :4170
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

Una vez hecho el prechequeo , se efectua la actualizacion

SQL> start view_recompile_jan2008cpu.sql

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.

Commit complete.

No. of Invalid Objects is :1582
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_ORCL_16Jul2010_10_00_02.log

Contamos los objetos inválidos

SQL> select count(*) from dba_objects where status <> 'VALID';

COUNT(*)
----------
1582

Y como estos cuadran con lo que indica el compilador del PSU , procedemos a ejecutar el utlrp.sql

SQL> start ?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2010-07-16 10:10:05

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2010-07-16 10:15:48

PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0

PL/SQL procedure successfully completed.

SQL> SQL> SQL>

8.- Parche exitosamente instalado y nuestra base de datos en una nueva versión :)

SQL> r
1* select version, comments from registry$history

VERSION COMMENTS
------------------------------ ----------------------------------------
view recompilation
10.2.0.4 PSU 10.2.0.4.1

En todo caso , nuestros componentes quedaron todos en la misma versión original

SQL> r
1* select comp_name , version , status from dba_registry

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rules Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.

Espero les sirva

by Ligarius
16.07.09. 21:11:35. 6040 words, 6072 views. Categories: Base de datos, Oracle 10g, Tuning / Performance ,

Export Datapump : No tan maravilloso pero.... comprime :)



Hace un tiempo hice un comentario, en parte infundado , en parte con fundamento sobre el export datapump.



Pueden revisar la nota acá

Era el problema de no poder contar con PIPE , tuberías o como le quieran llamar, al momento de exportar, lo que hacía que todos esos queridos códigos de export que comprimían on-line los respaldos, ya no funcionan con la versión Export Datapump.

Pues realizando un pequeño ejemplo, me encontre con la siguiente tabulación de ejemplos de export y export datapump, incluso con tuberías de por medio.
Comando % Compresion Tamaño MB
exp sin compress 0 574
exp con compress 0 574
exp sin compress y PIPE 87.5 77.5
expdp sin compress 11 512
expdp con compress 83.5 95

Los comandos

exp sin compress
exp system/oracle file=FULLDB11g full=y buffer=1000000

exp con compress
exp system/oracle file=FULLDB11g full=y buffer=1000000 compress=y

Nota : El COMPRESS del export , no está relacionado a la compresión de bloques Oracle, sino, más bien, a la cantidad de extensiones que conformarán un segmento :) , pero como sonaba a compresión lo añadí :P

exp sin compress y con PIPE
mknod pipes p
gzip < pipes> FULDB11gPipes.dmp.gz &
exp system/oracle file=pipes full=y buffer=1000000

expdp sin compress
expdp system/oracle DIRECTORY=data_pump_dir FULL=y DUMPFILE=FULLDB11gexpdp.dmp

expdp con compress
expdp system/oracle DIRECTORY=data_pump_dir FULL=y DUMPFILE=FULLDB11gexpdp.dmp COMPRESSION=ALL

Como pueden ver el ya famoso PIPE con export tiene un grado de compresión altísimo cercano al 87,5% , o sea , de cada 100MB de tamaño, deja un archivo en 12,5MB aproximadamente :)

Pero el Export Datapump , no es tan malo después de todo , pues logra un óptimo 83,5% de compresión , o sea, de cada 100MB de tamaño generaría un archivo de 16,5MB

Todo esto es en base a una base de datos de pruebas, todo varía de acuerdo a la porosidad de la base de datos (fragmentación)

Espero les sirva y claro.. me forme una mala impresión sobre el nuevo y populoso Export DataPump

PD : Gracias Luis Farías y Waldo Rojas ;)

PPD : La compresión está disponible desde 10g, pero para esta versión sólo es medatada, para la compresión total se debe usar Oracle 11g, gracias a mi amigo Juan Díaz (jada) por el comentario


by Ligarius
15.07.09. 19:30:39. 400 words, 25627 views. Categories: Base de datos, Oracle 11g, Oracle 10g, Tuning / Performance ,

BBDD : Como tracear una sesión que se conecta/procesa/desconecta en 2 segundos



0.-Situación
La situación es que hay una sesión que está hecha en un programa (C) , este programa hace una conexión a la base de datos y ejecuta ciertas rutinas en Pro*C , y se desconecta, el inconveniente es que se demora entre que se conecta, procesa y desconecta , entre 3 a 5 segundos.
Por ende hacerle un trace es bastante complicado, ya que no se alcanza a conocer el SID ,SPID o #SERIAL para incluirlo en el package
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESION(SID,#SERIAL,TRUE)

Tampoco podríamos utilizar el package DBMS_SUPPORT.START_TRACE_IN_SESSION() ya que también debiesemos conocer el SID y #SERIAL

Por ende se debe desarrollar algo alternativo para poder rescatar el proceso de esa sesión



1.-Solución
Crear un trigger on-logon para detectar cuando hace la conexión ese usuario a través de ese programa (Pro*C)

2.-Código
CREATE OR REPLACE TRIGGER
tracea_audit_trigger
AFTER LOGON ON DATABASE
DECLARE
var_session VARCHAR2(100);
var_module VARCHAR2(100);
BEGIN
SELECT sys_context('USERENV','SESSIONID')
INTO var_session
FROM dual
;


BEGIN
SELECT module
INTO var_module
FROM v$session
WHERE audsid = var_session;
EXCEPTION
WHEN OTHERS THEN
var_module := 'NO';
END;


IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
END IF;
END;
/


Hay que tener cuidado de dar los privilegios de select sobre la vista v$session, ya que un usuario que no posea los permisos de lectura no podrá realizar la conexión, por eso se recomienda que este código sea probado en ambientes de test, previo paso a producción

3.-Permisos necesarios

Grant select on v_$session to public;

¿Porque un privilegio a Public? Pues desde el momento en que se cree el trigger cualquier usuario que se conecte a la base de datos pasara por el trigger on-logon y si no tiene acceso a la vista v$session pues simplemente no se conectará y saldrá un error de privilegios.

Grant alter system to nombre_usuario;
Grant alter session to nombre_usuario;


Los permisos para alterar la sesión y el system debiesen ser revocados una vez finalizado el proceso de captura de estadísticas

Revoke alter system from ;
Revoke alter session from ;


4.-Si no esta activada la recolección de estadísticas
Si el parámetro TIMED_STATISTICS en la base de datos posee el valor FALSE, la traza no contendrá datos relevantes por ejemplo CPU, disk , etc.

Para ello se puede alterar el trigger y modificarlo de la siguiente forma

Código anterior
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
END IF;


Código nuevo
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
EXECUTE IMMEDIATE 'ALTER SYSTEM SET TIMED_STATISTICS=TRUE';
END IF;


5.-Si el archivo de trace generado es demasiado grande
Si el archivo de trace generado es demasiado grande, se puede disminuir su tamaño modificando el trigger

Código anterior
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
END IF;


Código nuevo
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
EXECUTE IMMEDIATE 'ALTER SYSTEM SET max_dump_file_size=5120';
END IF;


El tamaño asignado al parámetro max_dump_file_size está dado por Kb y el valor por defecto es UNLIMITED


6.- ¿Qué pasa si hay muchos usuarios, en la misma máquina, ejecutando el mismo Pro*C?
Pues una solución es realizar una copia de programa , por ejemplo si el programa se llama EjecutaProc, llamarlo de una forma que sea identificable , ejemplo , prueba_ejecucion y otorgar los permisos necesario para su ejecución

Con lo anterior , este programa va a ser inequivocamente ubicado en la vista v$session , bajo el campo module, y solamente a esta sesión le realizaremos un traceo

7.- Archivo de resultados
El archivo de resultados debe estar ubicado en la ruta que está predefinida en el parámetro de inicialización user_dump_dest

select value from v$parameter where name like 'user_dump_dest' , por defecto tiene el valor $ORACLE_HOME/rdbms/trace

El archivo de resultados del traceo , no es algo que sirva mucho a primera vista, a no ser que tengamos una memoria y ojos extraordinarios para hacer un formateo visual

Ejemplo del archivo :
PARSING IN CURSOR #6 len=67 dep=1 uid=0 oct=3 lid=0 tim=19191305310633 hv=2889900621 ad='f6f19668'
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
END OF STMT
PARSE #6:c=10000,e=9470,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=19191305310617
EXEC #6:c=0,e=918,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305311971
FETCH #6:c=0,e=408,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305312494
FETCH #6:c=0,e=24,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305312632
FETCH #5:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305312816
EXEC #6:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313060
FETCH #6:c=0,e=42,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305313186
FETCH #6:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313283
FETCH #5:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305313402
EXEC #6:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313660
FETCH #6:c=0,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305313788
FETCH #6:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313888
FETCH #5:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305314015
EXEC #6:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305314229
FETCH #6:c=0,e=39,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305314347
FETCH #6:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305314452


8.- Formateo de archivo, salida de trace
Como es algo complicado el análisis del archivo anterior, existe un utilitario llamado tkprof que realiza el formateo de este archivo, para utilizarlo se debe ejecutar lo siguiente

tkprof [explain=usuario/password] [sys=no] [insert=archivo]

Con el parámetro "explain=usuario/password" indicamos que nos muestre el plan de ejecución de todas las instrucciones, conectándose para ello al usuario/password indicados.

Con el parámetro "sys=no" indicamos que no nos muestre las instrucciones realizadas por el usuario SYS.

Con el parámetro "insert=archivo" , dejamos toda la información mostrada por tkprof en el archivo de salida,como sentencias insert en una tabla llamada tkprof_table, con lo cual podemos realizar consultas de forma más óptima

Después de haber formateado nuestro archivo en user_dump_dest , se visualizaría de la siguiente forma

select USER into :b0
from
DUAL


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1


Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL


by Ligarius
26.06.09. 18:15:21. 1151 words, 6702 views. Categories: Base de datos, Oracle 11g, Oracle 10g, Tuning / Performance ,

Export DataPump, no tan maravilloso como se dice :(

Al leer esta nota quede impactado... de verdad impactado 88|

Simplemente no se pueden utilizar PIPEs en DataPump :'(:'(

Se acuerdan cuando en versiones anteriores de Oracle10g ocupabamos el export e import pero con las llamadas tuberías (mknod,pipe,etc), las usabamos pues el archivo dmp que se generaba era tan grande que obviamente había que comprimirlo en "caliente" pues no había espacio donde dejarlo.

Era un código más o menos así



mknod /u01/backup/exp/export_pipe p

nohup /usr/bin/gzip < /u01 /backup/exp/export_pipe> /u01/backup/exp/dbaceh.dmp.gz 2> /u01/backup/logs/gzip.log &

$ORACLE_HOME/bin/exp user/senha@dbaceh file=/u01/backup/exp/export_pipe buffer=40000000 log=/u01/backup/logs/dbaceh.log full=y >$ARQLOG 2>$ARQLOG
comprimiamos un archivo de muchos Gigas en algo de verdad mas razonable

Todo esto hasta que nacio Oracle10g, ¿por qué?, pues Oracle nos decía "Usen el export DataPump es mejor y más seguro" , la verdad es que sí, es más seguro, ya que no dependemos del cliente que está ejecutando el export, ahora todo es en el Servidor, incluso como Job.

Pero cual fue la sorpresa al saber que Oracle Export Datapump, no funciona con "tuberías", así de fuerte.

Lo anterior implica que el archivo de 300GB queda en 300GB , o sea, seguiremos utilizando export e import como siempre :(

En todo caso en Oracle dicen que la modificación al Datapump, ya viene... sería para Oracle12g u Oracle20g?, quien sabe...

Notas relacionadas

to use EXPDP directly with GZIP ? Doc ID: 463336.1

Oracle10g Export/Import DataPump Does Not Work with Tapes or UNIX Named Pipes Doc ID: 276521.1

Espero les sirva.


by Ligarius
23.06.09. 18:28:04. 287 words, 8309 views. Categories: Base de datos, Oracle 11g, Tuning / Performance ,

<< 1 2 3 4 5 6 7 8 >>