Este artigo tem como objetivo explicar como aplicar o Patch PSU 10.2.0.5.4 para o Banco de Dados Oracle 10.2.0.5.0.
Estou usando um Banco de Dados Single Instance com ASM, ou seja, non-RAC.
Primeiramente devemos baixar o Patch 12419392. Estou usando Oracle Enterprise Linux 5.7 32 Bits, então devemos baixar o Patch para este Sistema Operacional.
Para baixar o Patch terás que obter as o suporte da Oracle.
Ao baixar o Patch copia o mesmo para o servidor de Banco de Dados e descompacta o mesmo com “unzip”.
Segue abaixo os passos para aplicar o Patch PSU 10.2.0.5.4.
[oracle@serveroracle ~]$ cd 12419392/ [oracle@serveroracle 12419392]$ ls -l total 64 drwxr-xr-x 3 oracle oinstall 4096 May 19 2011 custom drwxr-xr-x 4 oracle oinstall 4096 May 19 2011 etc drwxr-xr-x 9 oracle oinstall 4096 May 19 2011 files -rw-r--r-- 1 oracle oinstall 2872 May 18 2011 patchmd.xml -rw-rw-r-- 1 oracle oinstall 42226 Jul 19 2011 README.html -rw-rw-r-- 1 oracle oinstall 21 May 25 2011 README.txt [oracle@serveroracle 12419392]$
Ao entrar no diretório do Patch, vamos aplicar o Patch.
OBS: Para aplicar o Patch, deve-se certificar que todos os serviços do Banco de Dados esteja baixados (Listener, Banco de Dados, Enterprise Manager, ISQLPlus).
OBS: Sempre faz backup do produto Oracle antes aplicar qualquer Patch da Oracle para voltar o mesmo caso a aplicação do Patch não ocorra com Sucesso.
Vamos verificar se está faltando algum pacote do Linux para a aplicação do Patch.
[oracle@serveroracle 12419392]$ rpm -q binutils gcc glibc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc make compat-db compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel openmotif openmotif21 setarch pdksh libaio libaio-devel binutils-2.17.50.0.6-14.el5 gcc-4.1.2-51.el5 glibc-2.5-65 glibc-headers-2.5-65 package glibc-kernheaders is not installed glibc-devel-2.5-65 package compat-libstdc++ is not installed cpp-4.1.2-51.el5 package compat-gcc is not installed make-3.81-3.el5 compat-db-4.2.52-5.1 package compat-gcc-c++ is not installed package compat-libstdc++ is not installed package compat-libstdc++-devel is not installed openmotif-2.3.1-5.el5_5.1 package openmotif21 is not installed setarch-2.0-1.1 package pdksh is not installed libaio-0.3.106-5 package libaio-devel is not installed [oracle@serveroracle 12419392]$
Com o usuário “root” vamos instalar alguns pacotes do Linux que não estão instalados.
OBS: Estes são pacotes complementais para instalação e gerenciamento dos aplicativos.
Em um outro terminal com o usuário “root” vamos instalar os pacotes do Linux.
[root@serveroracle Server]# ls -l glibc-headers-*
-rw-r--r-- 1 root root 618371 Jun 14 2011 glibc-headers-2.5-65.i386.rpm
[root@serveroracle Server]# rpm -ivh glibc-headers-2.5-65.i386.rpm
warning: glibc-headers-2.5-65.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
package glibc-headers-2.5-65.i386 is already installed
[root@serveroracle Server]# ls -l glibc-devel-2.5-65*
-rw-r--r-- 1 root root 2143738 Jun 14 2011 glibc-devel-2.5-65.i386.rpm
[root@serveroracle Server]# rpm -ivh glibc-devel-2.5-65.i386.rpm
warning: glibc-devel-2.5-65.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
package glibc-devel-2.5-65.i386 is already installed
[root@serveroracle Server]# ls -l cpp-4.1.2-51.el5*
-rw-r--r-- 1 root root 2779549 Jun 14 2011 cpp-4.1.2-51.el5.i386.rpm
[root@serveroracle Server]# rpm -ivh cpp-4.1.2-51.el5.i386.rpm
warning: cpp-4.1.2-51.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
package cpp-4.1.2-51.el5.i386 is already installed
[root@serveroracle Server]# ls -l compat-gcc*
-rw-r--r-- 1 root root 4217682 Nov 17 2010 compat-gcc-34-3.4.6-4.1.i386.rpm
-rw-r--r-- 1 root root 11392445 Nov 17 2010 compat-gcc-34-c++-3.4.6-4.1.i386.rpm
-rw-r--r-- 1 root root 2171879 Nov 17 2010 compat-gcc-34-g77-3.4.6-4.1.i386.rpm
[root@serveroracle Server]# rpm -ivh compat-gcc-34-c++-3.4.6-4.1.i386.rpm
warning: compat-gcc-34-c++-3.4.6-4.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
package compat-gcc-34-c++-3.4.6-4.1.i386 is already installed
[root@serveroracle Server]# ls -l make-3.81-3.el5*
-rw-r--r-- 1 root root 477805 Nov 17 2010 make-3.81-3.el5.i386.rpm
[root@serveroracle Server]# rpm -ivh make-3.81-3.el5.i386.rpm
warning: make-3.81-3.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
package make-3.81-3.el5.i386 is already installed
file /usr/bin/make from install of make-3.81-3.el5.i386 conflicts with file from package make-3.81-3.el5.i386
[root@serveroracle Server]# ls -l compat-libstdc++*
-rw-r--r-- 1 root root 92219 Nov 17 2010 compat-libstdc++-296-2.96-138.i386.rpm
-rw-r--r-- 1 root root 237144 Nov 17 2010 compat-libstdc++-33-3.2.3-61.i386.rpm
[root@serveroracle Server]# rpm -ivh compat-libstdc++-33-3.2.3-61.i386.rpm
warning: compat-libstdc++-33-3.2.3-61.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
package compat-libstdc++-33-3.2.3-61.i386 is already installed
file /usr/lib/libstdc++.so.5.0.7 from install of compat-libstdc++-33-3.2.3-61.i386 conflicts with file from package compat-libstdc++-33-3.2.3-61.i386
[root@serveroracle Server]# rpm -ivh compat-libstdc++-296-2.96-138.i386.rpm
warning: compat-libstdc++-296-2.96-138.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
package compat-libstdc++-296-2.96-138.i386 is already installed
file /usr/lib/libstdc++-3-libc6.2-2-2.10.0.so from install of compat-libstdc++-296-2.96-138.i386 conflicts with file from package compat-libstdc++-296-2.96-138.i386
[root@serveroracle Server]# ls -l openmotif*
-rw-r--r-- 1 root root 1374499 Nov 17 2010 openmotif22-2.2.3-18.i386.rpm
-rw-r--r-- 1 root root 1610158 Nov 3 2010 openmotif-2.3.1-5.el5_5.1.i386.rpm
-rw-r--r-- 1 root root 3102338 Nov 3 2010 openmotif-devel-2.3.1-5.el5_5.1.i386.rpm
[root@serveroracle Server]# rpm -ivh openmotif22-2.2.3-18.i386.rpm
warning: openmotif22-2.2.3-18.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:openmotif22 ########################################### [100%]
[root@serveroracle Server]# ls -l pdksh*
-rw-r--r-- 1 root root 203568 Nov 17 2010 pdksh-5.2.14-36.el5.i386.rpm
[root@serveroracle Server]# rpm -ivh pdksh-5.2.14-36.el5.i386.rpm
warning: pdksh-5.2.14-36.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:pdksh ########################################### [100%]
[root@serveroracle Server]# ls -l libaio-devel*
-rw-r--r-- 1 root root 11790 Nov 17 2010 libaio-devel-0.3.106-5.i386.rpm
[root@serveroracle Server]# rpm -ivh libaio-devel-0.3.106-5.i386.rpm
warning: libaio-devel-0.3.106-5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:libaio-devel ########################################### [100%]
[root@serveroracle Server]#
Vamos aplicar o Patch 12419392.
[oracle@serveroracle 12419392]$ /oraprd01/app/oracle/product/10.2.0/db_1/OPatch/opatch apply Invoking OPatch 10.2.0.5.1 Oracle Interim Patch Installer version 10.2.0.5.1 Copyright (c) 2010, Oracle Corporation. All rights reserved. Oracle Home : /oraprd01/app/oracle/product/10.2.0/db_1 Central Inventory : /oraprd01/app/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.5.1 OUI version : 10.2.0.5.0 OUI location : /oraprd01/app/oracle/product/10.2.0/db_1/oui Log file location : /oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2012-03-15_17-44-00PM.log Patch history file: /oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt ApplySession applying interim patch '12419392' to OH '/oraprd01/app/oracle/product/10.2.0/db_1' Interim patch 12419392 is a superset of the patch(es) [ 12419392 ] in the Oracle Home OPatch will rollback the subset patches and apply the given patch. Running prerequisite checks... Patch 12419392: Optional component(s) missing : [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ] 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: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y 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 = '/oraprd01/app/oracle/product/10.2.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 '12419392' for restore. This might take a while... Backing up files affected by the patch '12419392' for restore. This might take a while... ApplySession rolling back interim patch '12419392' from OH '/oraprd01/app/oracle/product/10.2.0/db_1' Execution of 'sh /oraprd01/app/oracle/product/10.2.0/db_1/.patch_storage/12419392_May_25_2011_01_06_08/original_patch/custom/scripts/pre -rollback 12419392 ': Return Code = 0 Patching component oracle.rdbms, 10.2.0.5.0... Updating archive file "/oraprd01/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kcbl.o" . . . Patching component oracle.rdbms.rman, 10.2.0.5.0... Running make for target ioracle Running make for target iwrap Running make for target client_sharedlib Running make for target proc Running make for target irman Running make for target client_sharedlib ApplySession adding interim patch '12419392' to inventory Verifying the update... Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 12419392 are present in Oracle Home. The local system has been patched and can be restarted. -------------------------------------------------------------------------------- The following warnings have occurred during OPatch execution: 1) OUI-67620:Interim patch 12419392 is a superset of the patch(es) [ 12419392 ] in the Oracle Home -------------------------------------------------------------------------------- OPatch Session completed with warnings. OPatch completed with warnings. [oracle@serveroracle 12419392]$
Após o Patch Aplicado vamos continuar o processo de aplicação no Banco de Dados. Vamos subir os serviços do Banco de Dados.
Subindo o Listener.
[oracle@serveroracle 12419392]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 15-MAR-2012 17:52:43 Copyright (c) 1991, 2010, Oracle. All rights reserved. Starting /oraprd01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.5.0 - Production System parameter file is /oraprd01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Log messages written to /oraprd01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.104)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.104)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production Start Date 15-MAR-2012 17:52:43 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oraprd01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /oraprd01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.104)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "dbprod" has 1 instance(s). Instance "dbprod", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@serveroracle 12419392]$
Vamos subir a Instância ASM.
[oracle@serveroracle 12419392]$ export ORACLE_SID=+ASM [oracle@serveroracle 12419392]$ sqlplus sys/***** as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 15 17:55:10 2012 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup mount ASM instance started Total System Global Area 83886080 bytes Fixed Size 1272120 bytes Variable Size 57448136 bytes ASM Cache 25165824 bytes ASM diskgroups mounted SQL> exit Disconnected from Oracle Database 10g Release 10.2.0.5.0 - Production [oracle@serveroracle 12419392]$ export ORACLE_SID=dbprod [oracle@serveroracle 12419392]$ sqlplus sys/***** as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 15 17:55:57 2012 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 1277824 bytes Variable Size 541065344 bytes Database Buffers 524288000 bytes Redo Buffers 7110656 bytes Database mounted. Database opened. SQL>
Com o Banco de Dados aberto, vamos continuar a aplicar o Patch no Banco de Dados.
SQL> @$ORACLE_HOME/rdbms/admin/catbundle.sql psu apply
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_DBPROD_GENERATE_2012Mar15_18_12_45.log
Apply script: /oraprd01/app/oracle/product/10.2.0/db_1/rdbms/admin/catbundle_PSU_DBPROD_APPLY.sql
Rollback script: /oraprd01/app/oracle/product/10.2.0/db_1/rdbms/admin/catbundle_PSU_DBPROD_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> PROMPT Processing Oracle Enterprise Manager...
Processing Oracle Enterprise Manager...
SQL> ALTER SESSION SET current_schema = SYSMAN;
Session altered.
SQL> @?/sysman/admin/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql
.
.
.
SQL> SET TERMOUT on
SQL> SET ECHO off
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
Updating registry...
1 row created.
Commit complete.
Check the following log file for errors:
/oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_DBPROD_APPLY_2012Mar15_18_12_46.log
SQL>
Após rodar o “catbundle.sql” vamos rodar o “utlrp.sql” para recompilar os objetos.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2012-03-15 18:20:24
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 2012-03-15 18:20:30
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>
Vamos verificar se aplicamos o Patch PSU corretamente.
SQL> col COMMENTS for a30 SQL> select ACTION, NAMESPACE, VERSION, BUNDLE_SERIES, COMMENTS from dba_registry_history; ACTION NAMESPACE VERSION BUNDLE_SERIES COMMENTS ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ APPLY SERVER 10.2.0.5 PSU PSU 10.2.0.5.4 SQL>
Conforme informações no DBA_REGISTRY_HISTORY podemos concluir que aplicamos o Patch corretamente.
Mais informações sobre aplicação do Patch acompanhar o arquivo README que vem junto no pacote do Patch 12419392.
Autor: Maycon Tomiasi
Formado em Tecnologia da Informação na FIPP (Faculdade de Informática de Presidente Prudente), Analista DBA Oracle pela Teiko Soluções em Tecnologia da Informação, residente em Blumenau/ SC, Certificado OCP 10g/11g/12c, OCS 11g Implementation, OCE 11g Performance Tuning, OCE 11g RAC & GRID e OPN Specialist. Conhecimentos em PHP.
3 thoughts on “Aplicando Patch PSU 10.2.0.5.4 no Banco de Dados Oracle 10.2.0.5.0 non-RAC”