Este artigo tem como objetivo mostrar como aplicar o patch set update 10.2.0.5.6 (patch 13343471) para o Banco de Dados 10.2.0.5.
Estou utilizando o Sistema Operacional Enterprise Linux 5.7 32 Bits, então devemos fazer o download do patch set update para o banco de dados Oracle deste sistema operacional.
OBS: Para fazer o download é necessário obter as credenciais do Suporte da Oracle.
Download do Patch 13343471
Suporte Oracle – Tela de Download do Patch Set Update 13343471
Após fazer o download do Patch em questão, basta descompactar o mesmo no servidor de Banco de Dados.
OBS: Sempre antes de aplicar o Patch no Banco de Dados, fazer o backup do seu Produto Oracle. Ao aplicar o mesmo, certificar se todos os serviços Oracle esteja baixados.
No caso, os serviços estão todos baixados (Listener, Banco de Dados, Enterprise Manager).
Vamos realizar o Backup do Oracle Base.
[oracle@serveroracle ~]$ cd /oraprd01/ [oracle@serveroracle oraprd01]$ tar -zvcf backup_oracle_home_10.2.0.5.tar.gz app/ app/ app/oracle/ . . . app/oracle/scritps/backup/bkp_manutencao_rman.sh [oracle@serveroracle oraprd01]$ ls -l total 786924 drwxrwxr-x 3 oracle dba 4096 Mar 29 11:10 app -rw-r--r-- 1 oracle dba 804997841 Apr 18 16:06 backup_oracle_home_10.2.0.5.tar.gz drwxrwxr-x 2 oracle dba 16384 Mar 13 15:17 lost+found [oracle@serveroracle oraprd01]$ mv backup_oracle_home_10.2.0.5.tar.gz /orabackup/ [oracle@serveroracle oraprd01]$
Vamos descompactar o arquivo do Patch já feito download e iniciar a aplicação do Patch Set Update 10.2.0.5.6.
[oracle@serveroracle oraprd01]$ cd /orabackup/ [oracle@serveroracle orabackup]$ ls -l total 796316 -rw-r--r-- 1 oracle dba 804997841 Apr 18 16:06 backup_oracle_home_10.2.0.5.tar.gz drwx------ 2 oracle dba 16384 Mar 13 15:17 lost+found drwxr-xr-x 5 oracle oinstall 4096 Apr 17 11:45 orcl -rw-r--r-- 1 oracle oinstall 9557216 Apr 18 15:32 p13343471_10205_LINUX.zip -rw-r--r-- 1 oracle oinstall 40487 Apr 17 17:17 p8350262_10205_Generic.zip [oracle@serveroracle orabackup]$ unzip p13343471_10205_LINUX.zip Archive: p13343471_10205_LINUX.zip creating: 13343471/ inflating: 13343471/README.html . . inflating: 13343471/custom/scripts/pre inflating: 13343471/patchmd.xml [oracle@serveroracle orabackup]$ cd 13343471 [oracle@serveroracle 13343471]$ ls -l total 64 drwxr-xr-x 3 oracle oinstall 4096 Dec 21 01:32 custom drwxr-xr-x 4 oracle oinstall 4096 Dec 21 01:32 etc drwxr-xr-x 9 oracle oinstall 4096 Dec 21 01:32 files -rw-r--r-- 1 oracle oinstall 2872 Dec 21 01:32 patchmd.xml -rw-rw-r-- 1 oracle oinstall 43766 Jan 16 03:07 README.html -rw-r--r-- 1 oracle oinstall 21 Dec 21 01:32 README.txt [oracle@serveroracle 13343471]$
Já no diretório do Patch, vamos aplicar o Patch Set Update seguindo o arquivo “README.HTML” que vem no pacote do Patch.
[oracle@serveroracle 13343471]$ /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-04-18_16-47-50PM.log Patch history file: /oraprd01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt ApplySession applying interim patch '13343471' to OH '/oraprd01/app/oracle/product/10.2.0/db_1' Interim patch 13343471 is a superset of the patch(es) [ 8350262 ] in the Oracle Home OPatch will rollback the subset patches and apply the given patch. Running prerequisite checks... Patch 13343471: Optional component(s) missing : [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ] , [ oracle.network.cman, 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 '13343471' for restore. This might take a while... Backing up files affected by the patch '8350262' for restore. This might take a while... ApplySession rolling back interim patch '8350262' from OH '/oraprd01/app/oracle/product/10.2.0/db_1' Patching component oracle.sysman.agent.core, 10.2.0.5.0a... Updating jar file "/oraprd01/app/oracle/product/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/oraprd01/app/oracle/product/10.2.0/db_1/.patch_storage/8350262_Sep_13_2010_14_09_12/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class" Updating jar file "/oraprd01/app/oracle/product/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/oraprd01/app/oracle/product/10.2.0/db_1/.patch_storage/8350262_Sep_13_2010_14_09_12/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class" . . . Verifying the update... Inventory check OK: Patch ID 13343471 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 13343471 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 13343471 is a superset of the patch(es) [ 8350262 ] in the Oracle Home -------------------------------------------------------------------------------- OPatch Session completed with warnings. OPatch completed with warnings. [oracle@serveroracle 13343471]$
Vamos iniciar os serviços do Banco de Dados e terminar de aplicar o Patch Set Update.
[oracle@serveroracle 13343471]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 18-APR-2012 16:49:57 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=serveroracle.localdomain)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serveroracle.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production Start Date 18-APR-2012 16:49:57 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=serveroracle.localdomain)(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 "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@serveroracle 13343471]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Wed Apr 18 16:50:04 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 1277796 bytes Variable Size 415236252 bytes Database Buffers 654311424 bytes Redo Buffers 2916352 bytes Database mounted. Database opened. SQL>
Vamos executar o script “catbundle.sql” e aplicar o Patch Set Update.
SQL> @?/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_ORCL_GENERATE_2012Apr18_16_50_57.log Apply script: /oraprd01/app/oracle/product/10.2.0/db_1/rdbms/admin/catbundle_PSU_ORCL_APPLY.sql Rollback script: /oraprd01/app/oracle/product/10.2.0/db_1/rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql PL/SQL procedure successfully completed. Executing script file... . . . 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_ORCL_APPLY_2012Apr18_16_51_00.log SQL>
Vamos executar o script “utlrp.sql” para recompilar os objetos inválidos.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2012-04-18 16:51:32
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-04-18 16:51:33
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 conferir o registro do Banco de Dados após o Patch Set Update 10.2.0.5.6 aplicado.
SQL> col COMP_NAME for a35 SQL> select VERSION, STATUS, MODIFIED, STARTUP, COMP_NAME from dba_registry; VERSION STATUS MODIFIED STARTUP COMP_NAME ------------------------------ ----------- -------------------- -------- ----------------------------------- 10.2.0.5.0 VALID 17-APR-2012 17:24:57 Oracle Enterprise Manager 10.2.0.5.0 VALID 18-APR-2012 16:51:38 Oracle interMedia 10.2.0.5.0 VALID 18-APR-2012 16:51:38 Oracle XML Database 10.2.0.5.0 VALID 18-APR-2012 16:51:35 Oracle Expression Filter 10.2.0.5.0 VALID 18-APR-2012 16:51:38 Oracle Rule Manager 10.2.0.5.0 VALID 18-APR-2012 16:51:34 Oracle Workspace Manager 10.2.0.5.0 VALID 18-APR-2012 16:51:33 Oracle Database Catalog Views 10.2.0.5.0 VALID 18-APR-2012 16:51:34 Oracle Database Packages and Types 10.2.0.5.0 VALID 18-APR-2012 16:51:35 JServer JAVA Virtual Machine 10.2.0.5.0 VALID 18-APR-2012 16:51:35 Oracle XDK 10.2.0.5.0 VALID 18-APR-2012 16:51:35 Oracle Database Java Packages 11 rows selected. SQL> col COMMENTS for a30 SQL> select ACTION, NAMESPACE, VERSION, BUNDLE_SERIES, COMMENTS from dba_registry_history; ACTION NAMESPACE VERSION BUNDLE_SERIES COMMENTS ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ VIEW RECOMPILE view recompilation UPGRADE SERVER 10.2.0.5.0 Upgraded from 10.2.0.1.0 APPLY SERVER 10.2.0.5 PSU PSU 10.2.0.5.6 SQL>
Pronto aplicamos o Patch Set Update 10.2.0.5.6.
Mais informações acesse Documentação Oracle.
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.
