Upgrade Oracle10gR2 para Oracle11gR2

Objetivo:

Precisamos fazer um upgrade de uma base Oracle 10gR2 para 11gR2, existem vários procedimentos que podemos utilizar para realizar este upgrade, neste exemplo vamos realizar um upgrade manual utilizando alguns scripts da Oracle disponíveis no diretório $ORACLE_HOME/rdbms/admin de uma instalação do Oracle 11gR2.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0

Tópicos:

1. Instalando os binários do Oracle 11gR2;
2. Verificando os pré-requisitos;
3. Preparando o banco para Upgrade;
4. Realizando o Upgrade da base;
5. Tarefas após o Upgrade da base;

1. Instalando os binários do Oracle 11gR2

• 
  
  Unzip dos arquivos de instalação em um filesystem no servidor que hospeda a base 10gR2, neste exemplo /u02/install;
• 
  
  Instale os binários do Oracle 11gR2 utilizando o OUI no diretório: /u01/app/oracle/product/11.2.0/lab1;

2. Verificando os pré-requisitos;

2.1 Crie um diretório para o Upgrade:
[oracle@odbsrv02 oracle]$ mkdir /u01/app/oracle/upgrade

2.2 Copie o script pré-upgrade (utlu112i.sql) do $ORACLE_HOME/rdbms/admin para o diretório de Upgrade:

[oracle@odbsrv02 upgrade]$ cd /u01/app/oracle/product/11.2.0/lab1/rdbms/admin
[oracle@odbsrv02 admin]$ cp utlu112i.sql /u01/app/oracle/upgrade
[oracle@odbsrv02 admin]$ ll /u01/app/oracle/upgrade
total 188
-rw-r--r-- 1 oracle oinstall 185744 Jul 9 00:10 utlu112i.sql
[oracle@odbsrv02 admin]$

2.3 Conecte-se a base 10g e execute o script de pré-upgrade gravando o resultado num arquivo de log:

[oracle@odbsrv02 admin]$ cd /u01/app/oracle/upgrade
[oracle@odbsrv02 upgrade]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 9 00:12:16 2010

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utlu112i;

2.4 Abaixo podemos vizualizar o resultado do script:

Oracle Database 11.2 Pre-Upgrade Information Tool    07-09-2010 00:17:12
.
**********************************************************************
Database:
**********************************************************************
--> name:          LAB1
--> version:       10.2.0.5.0
--> compatible:    10.2.0.1.0
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 764 MB
.... AUTOEXTEND additional space required: 224 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 76 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 545 MB
.... AUTOEXTEND additional space required: 215 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 31 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 94 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by
"diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by
"diagnostic_dest"
--> core_dump_dest               11.1       DEPRECATED   replaced by
"diagnostic_dest"
.
**********************************************************************
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 Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   OLAPSYS
....   SYSMAN
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
.... USER HR has dependent objects.
.... USER ORACLE_OCM has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 1 object(s).  It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command:  PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.

PL/SQL procedure successfully completed.

SQL>

.

3. Preparando o banco para Upgrade;

3.1 Antes de iniciar o upgrade verifique as recomendações apontadas no relatório gerado pelo script pré-upgrade e realize as modificações necessárias para atender aos pré-riquisitos do upgrade.

3.2 Atualize as estatísticas apontadas no relatório:

SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('CTXSYS',DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OLAPSYS',DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYSMAN',DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS();

PL/SQL procedure successfully completed.

SQL>

.

3.3 Desative a fila de processos Batch:

SQL> alter system set job_queue_processes=0 scope=BOTH;

System altered.

SQL>

Obs: Se houver algum script agendado na crontab do Linux, tambem devemos desativa-lo durante o processo de upgrade.

.

3.4 Altere o valor do parametro java_pool_size:

SQL> alter system set java_pool_size = 70M scope=BOTH;

.

3.4 Ao realizar um upgrade em uma base de produção temos que verificar uma série de situações que podem comprometer o processo de upgrade, o note ID 837570.1 disponibiliza uma lista completa das verificações necessárias.

4. Realizando o Upgrade da base

4.1 Faça um backup do spfile e copie o arquivo de initlab1.ora do ORACLE_HOME/dbs (10g) para o ORACLE_HOME/dbs ( 11g);

4.2 O parametro de inicialização DIAGNOSTIC_DEST substituiu os parametros USER_DUMP_DEST e BACKGROUND_DUMP_DEST que se tornaram obsoletos, altere o arquivo de inicialização ajustando os diretórios para o novo ORACLE_HOME (11g):

[oracle@odbsrv02 dbs]$ cat initlab1.ora
lab1.__db_cache_size=255852544
lab1.__java_pool_size=75497472
lab1.__large_pool_size=4194304
lab1.__shared_pool_size=192937984
lab1.__streams_pool_size=4194304
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/product/11.2.0/lab1/diagnostics/adump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/control01.ctl',
'/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/control02.ctl',
'/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/control03.ctl'
*.core_dump_dest='/u01/app/oracle/product/11.2.0/lab1/diagnostics/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='lab1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=lab1XDB)'
*.java_pool_size=75497472
*.job_queue_processes=0
*.nls_length_semantics='BYTE'
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sga_max_size=536870912
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog'
*.diagnostic_dest='/u01/app/oracle/product/11.2.0/lab1/diagnostics'

4.3 Desative a geração de archive para a realização do upgrade, isto permitirá que o upgrade seja realizado num intervalo de tempo menor;

4.4 Desative o EM DBConsole se estiver ativo;

4.5 Desative o banco: shutdown immediate;

4.6 Desative o Listener;

4.7 Faça um backup full da base antes do upgrade;

4.8 Copie o arquivo de password do diretório $ORACLE_HOME/dbs do ORACLE_HOME 10g para o ORACLE_HOME 11g;

4.9 Configure as variáveis de ambiente para a base 11g:

[oracle@odbsrv02 dbs]$ export ORACLE_BASE=/u01/app/oracle
[oracle@odbsrv02 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/lab1
[oracle@odbsrv02 dbs]$ export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/lab1/bin

4.10 Altere o arquivo /etc/oratab para o ORACLE_HOME (11G):

EXEMPLO /etc/oratab
#lab1:/u01/app/oracle/product/10.2.0/db_1:N
lab1:/u01/app/oracle/product/11.2.0/lab1:N

4.11 Execute o script de upgrade da base:

[oracle@odbsrv02 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@odbsrv02 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 9 07:12:01 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             218105480 bytes
Database Buffers          310378496 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
SQL> @catupgrd;

.

5. Tarefas após o Upgrade da base;

5.1 Após o upgrade a base é encerrada e torna-se inativa, vamos ativar a base e verificar a integridade dos objetos:

[oracle@odbsrv02 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 9 08:19:41 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             218105480 bytes
Database Buffers          310378496 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
SQL> @utlrp.sql
.
.
SQL> select count(*) from dba_objects where status <> 'VALID';

COUNT(*)
----------
0

SQL>
SQL> @utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           08-05-2010 08:40:05
.
Component                                Status         Version  HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.1.0  00:19:46
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.1.0  00:04:47
Oracle Workspace Manager
.                                         VALID      11.2.0.1.0  00:01:02
OLAP Analytic Workspace
.                                         VALID      11.2.0.1.0  00:00:28
OLAP Catalog
.                                         VALID      11.2.0.1.0  00:01:20
Oracle OLAP API
.                                         VALID      11.2.0.1.0  00:01:15
Oracle Enterprise Manager
.                                         VALID      11.2.0.1.0  00:12:49
Oracle XDK
.                                         VALID      11.2.0.1.0  00:03:40
Oracle Text
.                                         VALID      11.2.0.1.0  00:01:14
Oracle XML Database
.                                         VALID      11.2.0.1.0  00:05:51
Oracle Database Java Packages
.                                         VALID      11.2.0.1.0  00:01:06
Oracle Multimedia
.                                         VALID      11.2.0.1.0  00:07:51
Spatial
.                                         VALID      11.2.0.1.0  00:07:49
Oracle Expression Filter
.                                         VALID      11.2.0.1.0  00:00:32
Oracle Rule Manager
.                                         VALID      11.2.0.1.0  00:00:19
Gathering Statistics
.                                                                00:07:40
Total Upgrade Time: 01:17:41

PL/SQL procedure successfully completed.

SQL>

.

5.2 Confirmado que a base esta integra podemos executar o script que realizará as tarefas finais com a base aberta em modo normal:

SQL> spool /u01/app/oracle/upgrade/catuppst.log;
SQL> @catuppst.sql
SQL> @utlrp.sql
SQL> select count(*) from dba_objects where status <> 'VALID';

COUNT(*)
----------
0

SQL>

.

5.3 Copie o arquivo listener.ora do ORACLE_HOME 10g para o 11g, modifique o arquivo alterando o ORACLE_HOME para 11g e inicie o Listener:

[oracle@odbsrv02 admin]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@odbsrv02 admin]$ cp listener.ora /u01/app/oracle/product/11.2.0/lab1/network/admin/
[oracle@odbsrv02 admin]$ ll /u01/app/oracle/product/11.2.0/lab1/network/admin/
total 12
-rw-r--r--  1 oracle oinstall  516 Aug  5 08:49 listener.ora
drwxr-xr-x  2 oracle oinstall 4096 Aug  5 05:52 samples
-rw-r--r--  1 oracle oinstall  187 May  9  2007 shrept.lst
[oracle@odbsrv02 admin]$ cd /u01/app/oracle/product/11.2.0/lab1/network/admin
[oracle@odbsrv02 admin]$ vi listener.ora
[oracle@odbsrv02 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/lab1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/lab1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = odbsrv02.localdomain)(PORT = 1521))
)
)

[oracle@odbsrv02 admin]$
[oracle@odbsrv02 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-AUG-2010 08:55:16

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/lab1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/lab1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/odbsrv02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=odbsrv02.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                05-AUG-2010 08:55:19
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/lab1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/odbsrv02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=odbsrv02.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@odbsrv02 admin]$

.

5.4 Atualize os arquivo “/etc/oratab” e “/home/oracle/.bash_profile” com o ORACLE_HOME 11g.