Este artigo tem como objetivo mostrar como aplicar o patch set 10.2.0.5 (patch 8202632) para o Banco de Dados 10.2.0.1.
Estou utilizando o Sistema Operacional Enterprise Linux 5.7 32 Bits, então devemos fazer o download do patch 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 8202632
Download Patch Set 10.2.0.5
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 acessar o diretório do produto Oracle e vamos realizar o Backup do mesmo.
[oracle@serveroracle oraprd01]$ tar -zvcf backup_oracle_home.tar.gz app/ app/ app/oracle/ app/oracle/product/ . . . app/oracle/scritps/backup/bkp_mensal.rcv app/oracle/scritps/backup/bkp_manutencao_rman.sh [oracle@serveroracle oraprd01]$ ls -l total 646196 drwxrwxr-x 3 oracle dba 4096 Mar 29 11:10 app -rw-r--r-- 1 oracle oinstall 661030969 Apr 17 16:03 backup_oracle_home.tar.gz drwxrwxr-x 2 oracle dba 16384 Mar 13 15:17 lost+found [oracle@serveroracle oraprd01]$
Vamos aplicar o Patch já descompactado no servidor.
[oracle@serveroracle ~]$ /orabackup/Disk1/./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-04-17_04-04-29PM. Please wait ...[oracle@serveroracle ~]$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.
[oracle@serveroracle ~]$
Seguir as imagens abaixo para a instalação passo à passo do Patch.
Tela Inicial de Instalação do Patch Set 10.2.0.5
Instalar no atual Oracle Home.
Tela de Credenciais para o Suporte Oracle
Tela de Aviso das Credenciais do Suporte da Oracle
Tela de checagem dos Pré-Requisitos para a Instalação do Patch Set.
Tela do Sumário do Patch a ser Instalado.
Tela de Progresso de Instalação do Patch
Tela para rodar o Script para permissão do usuário "root". Copie o endereço e coloque num terminal com o usuário "root".
Com o usuário root, vamos executar o script da imagem acima.
[oracle@serveroracle ~]$ su -
Password:
[root@serveroracle ~]# /oraprd01/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle 10g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oraprd01/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@serveroracle ~]# exit
logout
[oracle@serveroracle ~]$
Tela de finalização a instalação do Patch. Clique em Exit.
Tela de finalização de instalação do Patch.
Após executar o script da permissão do usuário “root”, com o usuário oracle, vamos iniciar os serviços do Banco de Dados Oracle.
OBS: Seguir o README.html do Patch para aplicar o mesmo.
[oracle@serveroracle ~]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 17-APR-2012 16:14:02 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 17-APR-2012 16:14:02 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 ~]$ sqlplus sys/*********** as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Tue Apr 17 16:16:09 2012 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 1277796 bytes Variable Size 390070428 bytes Database Buffers 679477248 bytes Redo Buffers 2916352 bytes Database mounted. Database opened. SQL>
Seguindo o documento do Patch, vamos aplicar o mesmo executando o primeiro script “utlu102i.sql” (Descreve a informação da Atualização).
OBS: Seguindo o Documento o mesmo solicita executar um spool para verificação após executar o script mencionado.
SQL> SPOOL upgrade_info.log SQL> @$ORACLE_HOME/rdbms/admin/utlu102i.sql Oracle Database 10.2 Upgrade Information Utility 04-17-2012 16:17:23 . ********************************************************************** Database: ********************************************************************** --> name: ORCL --> version: 10.2.0.1.0 --> compatible: 10.2.0.1.0 --> blocksize: 8192 . ********************************************************************** Tablespaces: [make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size: 448 MB .... AUTOEXTEND additional space required: 8 MB --> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 402 MB .... AUTOEXTEND additional space required: 102 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size: 206 MB .... AUTOEXTEND additional space required: 6 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 58 MB .... AUTOEXTEND additional space required: 38 MB . ********************************************************************** Update Parameters: [Update Oracle Database 10.2 init.ora or spfile] ********************************************************************** -- No update parameter changes are required. . ********************************************************************** Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile] ********************************************************************** -- No renamed parameters found. No changes are required. . ********************************************************************** Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile] ********************************************************************** -- No obsolete parameters found. No changes are required . ********************************************************************** Components: [The following database components will be upgraded or installed] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Expression Filter [upgrade] VALID --> EM Repository [upgrade] VALID --> Rule Manager [upgrade] VALID . PL/SQL procedure successfully completed. SQL> SPOOL OFF
Vamos executar o script “catupgrd.sql” para aplicar o Patch no Banco de Dados (Dicionário de Dados).
SQL> SPOOL patch.log SQL> @?/rdbms/admin/catupgrd.sql DOC>###################################################################### DOC>###################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the user running this script is not SYS. Disconnect DOC> and reconnect with AS SYSDBA. DOC>###################################################################### DOC>###################################################################### DOC># no rows selected DOC>###################################################################### DOC>###################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database server version is not correct for this script. DOC> Shutdown ABORT and use a different script or a different server. DOC>###################################################################### DOC>###################################################################### DOC># no rows selected . . . Total Upgrade Time: 00:18:38 DOC>####################################################################### DOC>####################################################################### DOC> DOC> The above PL/SQL lists the SERVER components in the upgraded DOC> database, along with their current version and status. DOC> DOC> Please review the status and version columns and look for DOC> any errors in the spool log file. If there are errors in the spool DOC> file, or any components are not VALID or not the current version, DOC> consult the Oracle Database Upgrade Guide for troubleshooting DOC> recommendations. DOC> DOC> Next shutdown immediate, restart for normal operation, and then DOC> run utlrp.sql to recompile any invalid application objects. DOC> DOC>####################################################################### DOC>####################################################################### DOC># SQL> SQL> spool off
Após a execução do script “catupgrd.sql”, vamos reiniciar o Banco de Dados.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 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 “utlrp.sql” e recompilar os possíveis objetos inválidos pós aplicação do Patch no Dicionário de Dados.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2012-04-17 16:40:48
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>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2012-04-17 16:41:33
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
SQL>
Vamos verificar o registro após aplicação do Patch Set 10.2.0.5.
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 16:35:28 Oracle Enterprise Manager 10.2.0.5.0 VALID 17-APR-2012 16:41:35 Oracle interMedia 10.2.0.5.0 VALID 17-APR-2012 16:41:34 Oracle XML Database 10.2.0.5.0 VALID 17-APR-2012 16:41:34 Oracle Expression Filter 10.2.0.5.0 VALID 17-APR-2012 16:41:35 Oracle Rule Manager 10.2.0.5.0 VALID 17-APR-2012 16:41:33 Oracle Workspace Manager 10.2.0.5.0 VALID 17-APR-2012 16:41:33 Oracle Database Catalog Views 10.2.0.5.0 VALID 17-APR-2012 16:41:33 Oracle Database Packages and Types 10.2.0.5.0 VALID 17-APR-2012 16:41:34 JServer JAVA Virtual Machine 10.2.0.5.0 VALID 17-APR-2012 16:41:34 Oracle XDK 10.2.0.5.0 VALID 17-APR-2012 16:41:34 Oracle Database Java Packages SQL> !
Vamos inciar o Enterprise Manager.
[oracle@serveroracle ~]$ emctl start dbconsole TZ set to Brazil/East Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. http://serveroracle.localdomain:1158/em/console/aboutApplication Starting Oracle Enterprise Manager 10g Database Control .......................... started. ------------------------------------------------------------------ Logs are generated in directory /oraprd01/app/oracle/product/10.2.0/db_1/serveroracle.localdomain_orcl/sysman/log [oracle@serveroracle ~]$ emctl status agent TZ set to Brazil/East Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. --------------------------------------------------------------- Agent Version : 10.1.0.6.0 OMS Version : 10.1.0.6.0 Protocol Version : 10.1.0.2.0 Agent Home : /oraprd01/app/oracle/product/10.2.0/db_1/serveroracle.localdomain_orcl Agent binaries : /oraprd01/app/oracle/product/10.2.0/db_1 Agent Process ID : 11833 Parent Process ID : 9626 Agent URL : http://serveroracle.localdomain:3938/emd/main Started at : 2012-04-17 16:51:04 Started by user : oracle Last Reload : 2012-04-17 16:51:04 Last successful upload : (none) Last attempted upload : (none) Total Megabytes of XML files uploaded so far : 0.00 Number of XML files pending upload : 16 Size of XML files pending upload(MB) : 4.40 Available disk space on upload filesystem : 71.39% --------------------------------------------------------------- Agent is Running and Ready [oracle@serveroracle ~]$
Tela do Enterprise Manager após aplicar o Patch
Como podemos perceber na imagem acima, o Enterprise Manager está mostrando o Banco de Dados 10.2.0.1.
Para ajuste deste, devemos reconfigurar o Enterprise Manager (dbconsole).
Vamos baixar o serviço do Enterpise Manager e reconfigurar o mesmo.
Para reconfigurar o Enterprise Manager acesse o artigo do Anderson Graf Recriando repositório do Oracle Enterprise Manager.
Vamos seguir o artigo acima.
A configuração pode não ser completada, pois ao subir o serviço do Enterprise Manager o mesmo pode ocorrer um erro de certificado SSL.
Caso haja erro ao recriar o repositório do Enterprise Manager, verifique o status do serviço do mesmo conforme abaixo.
[oracle@serveroracle ~]$ emctl status dbconsole TZ set to Brazil/East Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. https://serveroracle.localdomain:1158/em/console/aboutApplication Oracle Enterprise Manager 10g is not running. ------------------------------------------------------------------ Logs are generated in directory /oraprd01/app/oracle/product/10.2.0/db_1/serveroracle.localdomain_orcl/sysman/log [oracle@serveroracle ~]$ emctl status agent TZ set to Brazil/East Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. --------------------------------------------------------------- Agent is Not Running [oracle@serveroracle ~]$
Para solicionar este, devemos aplicar o Patch 8350262 para o Enterpise Manager.
Para aplicar o mesmo, vamos seguir o artigo Aplicação do Patch 8350262 para Correção do Enterprise Manager..
Seguindo o artigo acima, vamos aplicar o Patch para o Enterprise Manager.
Após aplicado o Patch 8350262 para o Enterprise Manager, vamos verificar o status do serviço do mesmo.
[oracle@serveroracle 8350262]$ emctl status dbconsole TZ set to Brazil/East Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. https://serveroracle.localdomain:1158/em/console/aboutApplication Oracle Enterprise Manager 10g is running. ------------------------------------------------------------------ Logs are generated in directory /oraprd01/app/oracle/product/10.2.0/db_1/serveroracle.localdomain_orcl/sysman/log [oracle@serveroracle 8350262]$ emctl status agent TZ set to Brazil/East Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. --------------------------------------------------------------- Agent Version : 10.1.0.6.0 OMS Version : 10.1.0.6.0 Protocol Version : 10.1.0.2.0 Agent Home : /oraprd01/app/oracle/product/10.2.0/db_1/serveroracle.localdomain_orcl Agent binaries : /oraprd01/app/oracle/product/10.2.0/db_1 Agent Process ID : 31928 Parent Process ID : 29766 Agent URL : https://serveroracle.localdomain:3938/emd/main Started at : 2012-04-17 17:30:02 Started by user : oracle Last Reload : 2012-04-17 17:30:02 Last successful upload : 2012-04-17 17:30:36 Total Megabytes of XML files uploaded so far : 4.00 Number of XML files pending upload : 0 Size of XML files pending upload(MB) : 0.00 Available disk space on upload filesystem : 71.29% --------------------------------------------------------------- Agent is Running and Ready [oracle@serveroracle 8350262]$
Tela do Enterprise Manager após aplicado Patch para o mesmo e recriado o repositório do mesmo
Pronto aplicamos o Patch Set 10.2.0.5 (8202632) no Banco de Dados 10.2.0.1.
Mais informações sobre o Patch Set acessar o Suporte da 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.
