Wednesday, February 28, 2007
DST Are you ready?
Voce está pronto? DST significa Daylight Saving Time, o nosso tradicional Horário de Verão, mas em inglês fica mais chique. E é porque o início do horário de verão nos Estados Unidos mudou que estou escrevendo este artigo. O governo norte americano estendeu o tempo do horário de verão a partir de 2007, portanto "patches" a vista e vários. Tente "DST Impact" no google. O aviso que gostaria de deixar é que, basicamente, no Oracle o DBA precisa cuidar de dois aspectos - se no banco estiver instalado o JVM e se existem campos com datatype que usa TIME ZONE. A nota 397281.1 do metalink é um excelente início para um melhor entendimento dos impactos. Uma das perguntas/respostas me chamou atenção, vou reproduzí-la abaixo:
Do I need a patch?
The short answer is: YES. Because timezone data is embedded not only in Database tables but also in arguments to PL/SQL functions and Java code, there is no way for Oracle to definitively tell any customer that they do NOT need to patch. The only way to eliminate risk is to patch all your systems. We have provided a great deal of information in the documents which help you establish which of your systems need to be patched as a matter of priority. Based on the information provided, your analysis may lead you to believe that the impact on a system is low. It is ultimately your business decision to patch or not based on your analysis. Oracle recommends that you patch all your systems.
Eu preciso do patch?
Resposta curta é SIM! ...
Então decidimos pelo lado prudente da vida: aplicar em todas as bases. E dizem que paraquedismo é esporte-radical - vai ser DBA para ver o que é emoção ;-) Ok, mãos a obra que no banco aqui de casa eu ainda não apliquei o patch.
Metalink, nota 359145.1 tem uma matriz com o número do patch para cada versão e plataforma. No meu caso, Linux o tamanho do patch 72k. Vejamos como está meu banco.
Portanto, meu banco está desatualizado com relação ao patch do DST. Gostaria de chamar a atenção para o OPatch, ele precisa estar presente e seu Inventory deve estar consistente porque a aplicação do patch depende do OPatch. A partir deste ponto, vou copiar todos os passos que fiz para a aplicação do patch.
Ok, nenhum serviço oracle no ar. Isso é importante para a aplicação do patch.
Prontinho, rápido... não toma nem 20 minutos. Minha base está instalada sobre o ASM, se a sua não... melhor, menos serviços para baixar. ;-)
Do I need a patch?
The short answer is: YES. Because timezone data is embedded not only in Database tables but also in arguments to PL/SQL functions and Java code, there is no way for Oracle to definitively tell any customer that they do NOT need to patch. The only way to eliminate risk is to patch all your systems. We have provided a great deal of information in the documents which help you establish which of your systems need to be patched as a matter of priority. Based on the information provided, your analysis may lead you to believe that the impact on a system is low. It is ultimately your business decision to patch or not based on your analysis. Oracle recommends that you patch all your systems.
Eu preciso do patch?
Resposta curta é SIM! ...
Então decidimos pelo lado prudente da vida: aplicar em todas as bases. E dizem que paraquedismo é esporte-radical - vai ser DBA para ver o que é emoção ;-) Ok, mãos a obra que no banco aqui de casa eu ainda não apliquei o patch.
Metalink, nota 359145.1 tem uma matriz com o número do patch para cada versão e plataforma. No meu caso, Linux o tamanho do patch 72k. Vejamos como está meu banco.
[oracle@anakin patches]$ $ORACLE_HOME/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /oracle/10g
Central Inventory : /oracle/oraInventory
from : /oracle/10g/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Feb_28_22-10-21-BRT_Wed.log
Lsinventory Output file location : /oracle/10g/cfgtoollogs/opatch/lsinv/lsinventory-2007_Feb_28_22-10-21-BRT_Wed.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@anakin patches]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:11:30 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@LX10G> select case to_number(to_char(to_timestamp_tz
2 ('20070311 00:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR')
3 + to_dsinterval('0 08:00:00'),'HH24'))
4 when 8 then 'The patch has not been applied'
5 when 9 then 'The patch has been applied correctly'
6 else 'Error' end "TZTEST (RUN FROM DB HOME)"
7 from dual;
TZTEST (RUN FROM DB HOME)
------------------------------------
The patch has not been applied
1 row selected.
Portanto, meu banco está desatualizado com relação ao patch do DST. Gostaria de chamar a atenção para o OPatch, ele precisa estar presente e seu Inventory deve estar consistente porque a aplicação do patch depende do OPatch. A partir deste ponto, vou copiar todos os passos que fiz para a aplicação do patch.
[oracle@anakin patches]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:14:16 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@LX10G> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@LX10G> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin patches]$ export ORACLE_SID=+ASM
[oracle@anakin patches]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:14:50 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> shutdown
ASM diskgroups dismounted
ASM instance shutdown
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin patches]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 28-FEB-2007 22:15:05
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
The command completed successfully
[oracle@anakin patches]$ /oracle/http/opmn/bin/opmnctl stopall
opmnctl: stopping opmn and all managed processes...
[oracle@anakin patches]$
[oracle@anakin patches]$
[oracle@anakin patches]$ ps -fu oracle
UID PID PPID C STIME TTY TIME CMD
oracle 4801 3716 0 Feb27 ? 00:00:00 /oracle/10g/bin/ocssd.bin
oracle 8868 8865 0 21:04 pts/1 00:00:00 -bash
oracle 9329 8868 0 22:15 pts/1 00:00:00 ps -fu oracle
[marcio@anakin init.d]$ su -
Password:
[root@anakin ~]#
[root@anakin ~]# cd /etc/init.d/
[root@anakin init.d]# ./init.cssd stop
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
Shutdown has begun. The daemons should exit soon.
[root@anakin init.d]# exit
logout
[oracle@anakin patches]$ ps -fu oracle
UID PID PPID C STIME TTY TIME CMD
oracle 8868 8865 0 21:04 pts/1 00:00:00 -bash
oracle 9550 8868 0 22:17 pts/1 00:00:00 ps -fu oracle
Ok, nenhum serviço oracle no ar. Isso é importante para a aplicação do patch.
[oracle@anakin patches]$ ll -tr
total 84
-rw-r--r-- 1 oracle dba 360 Feb 28 21:06 test_patch.sql
-rw-r--r-- 1 oracle dba 73788 Feb 28 22:21 p5632264_10202_LINUX.zip
[oracle@anakin patches]$ unzip p5632264_10202_LINUX.zip
Archive: p5632264_10202_LINUX.zip
creating: 5632264/
creating: 5632264/files/
creating: 5632264/files/oracore/
creating: 5632264/files/oracore/zoneinfo/
inflating: 5632264/files/oracore/zoneinfo/timezone.dat
inflating: 5632264/files/oracore/zoneinfo/timezlrg.dat
inflating: 5632264/files/oracore/zoneinfo/readme.txt
creating: 5632264/etc/
creating: 5632264/etc/config/
inflating: 5632264/etc/config/inventory
inflating: 5632264/etc/config/actions
creating: 5632264/etc/xml/
inflating: 5632264/etc/xml/GenericActions.xml
inflating: 5632264/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 5632264/README.txt
[oracle@anakin patches]$ cd 5632264/
[oracle@anakin 5632264]$ $ORACLE_HOME/OPatch/opatch apply
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /oracle/10g
Central Inventory : /oracle/oraInventory
from : /oracle/10g/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Feb_28_22-21-36-BRT_Wed.log
ApplySession applying interim patch '5632264' to OH '/oracle/10g'
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 = '/oracle/10g')
Is the local system ready for patching?
Do you want to proceed? [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 '5632264' for restore. This might take a while...
Backing up files affected by the patch '5632264' for rollback. This might take a while...
Patching component oracle.oracore.rsf, 10.2.0.2.0...
Copying file to "/oracle/10g/oracore/zoneinfo/timezone.dat"
Copying file to "/oracle/10g/oracore/zoneinfo/timezlrg.dat"
Copying file to "/oracle/10g/oracore/zoneinfo/readme.txt"
ApplySession adding interim patch '5632264' to inventory
Verifying the update...
Inventory check OK: Patch ID 5632264 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 5632264 are present in Oracle Home.
The local system has been patched and can be restarted.
OPatch succeeded.
[oracle@anakin 5632264]$ $ORACLE_HOME/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /oracle/10g
Central Inventory : /oracle/oraInventory
from : /oracle/10g/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Feb_28_22-21-59-BRT_Wed.log
Lsinventory Output file location : /oracle/10g/cfgtoollogs/opatch/lsinv/lsinventory-2007_Feb_28_22-21-59-BRT_Wed.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :
Patch 5632264 : applied on Wed Feb 28 22:21:44 BRT 2007
Created on 14 Feb 2007, 21:37:17 hrs US/Pacific
Bugs fixed:
5632264
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@anakin 5632264]$ su -
Password:
[root@anakin ~]# cd /etc/init.d/
[root@anakin init.d]# ./init.cssd start
Startup will be queued to init within 90 seconds.
[root@anakin init.d]# exit
logout
[oracle@anakin 5632264]$
[oracle@anakin 5632264]$ ps -fu oracle
UID PID PPID C STIME TTY TIME CMD
oracle 8868 8865 0 21:04 pts/1 00:00:00 -bash
oracle 10041 9500 0 22:23 ? 00:00:00 /oracle/10g/bin/ocssd.bin
oracle 10083 8868 0 22:25 pts/1 00:00:00 ps -fu oracle
[oracle@anakin 5632264]$ export ORACLE_SID=+ASM
[oracle@anakin 5632264]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:26:28 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1259264 bytes
Variable Size 57460992 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin 5632264]$ export ORACLE_SID=lx10g
[oracle@anakin 5632264]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:26:48 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1262224 bytes
Variable Size 247467376 bytes
Database Buffers 348127232 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
idle> @../test_patch
idle> select case to_number(to_char(to_timestamp_tz
2 ('20070311 00:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR')
3 + to_dsinterval('0 08:00:00'),'HH24'))
4 when 8 then 'The patch has not been applied'
5 when 9 then 'The patch has been applied correctly'
6 else 'Error' end "TZTEST (RUN FROM DB HOME)"
7 from dual;
TZTEST (RUN FROM DB HOME)
------------------------------------
The patch has been applied correctly
1 row selected.
idle> set echo off
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin 5632264]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 28-FEB-2007 22:27:47
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.2.0 - Production
System parameter file is /oracle/10g/network/admin/listener.ora
Log messages written to /oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.2.0 - Production
Start Date 28-FEB-2007 22:27:47
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/10g/network/admin/listener.ora
Listener Log File /oracle/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "lx10g" has 1 instance(s).
Instance "lx10g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@anakin 5632264]$ /oracle/http/opmn/bin/opmnctl startall
opmnctl: starting opmn and all managed processes...
Prontinho, rápido... não toma nem 20 minutos. Minha base está instalada sobre o ASM, se a sua não... melhor, menos serviços para baixar. ;-)
Labels: how to
