Migrando Banco de Dados Oracle 10g para 11g com OUI (Oracle Universal Installer) non-RAC.

Este artigo tem como objetivo realizar o upgrade do Banco de Dados Oracle 10g (10.2.0.5) para 11g (11.2.0.1) via OUI (Oracle Universal Installer).

Porque migrar para uma nova versão?
Tecnologia Nova e Bugs fixados que ocorrem na versão 10g.

Vamos iniciar a nossa atualização de versão do Banco de Dados Oracle.

Vamos verificar a nossas variáveis de ambiente.

oracle=dbprod-> cat /etc/ora_ambiente.sh
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=oracle1011gserver.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=dbprod; export ORACLE_UNQNAME
ORACLE_BASE=/oraprd01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=dbprod; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
PATH=$ORACLE_HOME/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/opt/dell/mdstoragesoftware/mdstoragemanager/jre/bin:$ORACLE_HOME/OPatch:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

export PS1="oracle=$ORACLE_SID-> "
oracle=dbprod-> echo $ORACLE_HOME
/oraprd01/app/oracle/product/10.2.0/db_1
oracle=dbprod->

Vamos criar um novo ORACLE_HOME para o produto da Versão 11g.

oracle=dbprod-> mkdir -p /oraprd01/app/oracle/product/11.2.0/db_1

Após criado o diretório para a ORACLE_HOME para a versão 11g, copiar o software Oracle para o servidor.
Vamos criar novas variáveis de ambiente para a versão 11g do Banco de Dados. Com o usuário “root” vamos criar o novo arquivo para o .bash_profile do usuário “oracle”.

oracle=dbprod-> su -
Password:
[root@oracle1011gserver ~]# vi /etc/ora_ambiente11gr2.sh
[root@oracle1011gserver ~]# chmod 755 /etc/ora_ambiente11gr2.sh
[root@oracle1011gserver ~]# exit
logout
oracle=dbprod-> cat /etc/ora_ambiente11gr2.sh
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=oracle1011gserver.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=dbprod; export ORACLE_UNQNAME
ORACLE_BASE=/oraprd01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=dbprod; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
PATH=$ORACLE_HOME/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/opt/dell/mdstoragesoftware/mdstoragemanager/jre/bin:$ORACLE_HOME/OPatch:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

export PS1="oracle=$ORACLE_SID-> "
oracle=dbprod->

Vamos editar o .bash_profile para o usuário “oracle” com as novas variáveis de ambientes.

oracle=dbprod-> vi .bash_profile
oracle=dbprod-> cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

. /etc/ora_ambiente11gr2.sh
oracle=dbprod-> . /etc/ora_ambiente11gr2.sh
oracle=dbprod-> echo $ORACLE_HOME
/oraprd01/app/oracle/product/11.2.0/db_1
oracle=dbprod->

Vamos atualizar os pacotes do Linux 5.7 para a versão 11g.

oracle=dbprod-> su -
Password:
[root@oracle1011gserver ~]# cd /media/OL5.7\ i386\ dvd\ 20110728/Server/
[root@oracle1011gserver Server]#
rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh compat-libstdc++-33*.i386.rpm
rpm -Uvh elfutils-libelf*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgomp-4.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
rpm -Uvh numactl-devel-*warning: binutils-2.17.50.0.6-14.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
        package binutils-2.17.50.0.6-14.el5.i386 is already installed
[root@oracle1011gserver Server]# rpm -Uvh compat-libstdc++-33*
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
[root@oracle1011gserver Server]# rpm -Uvh compat-libstdc++-33*.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
[root@oracle1011gserver Server]# rpm -Uvh elfutils-libelf*
warning: elfutils-libelf-0.137-3.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
        package elfutils-libelf-0.137-3.el5.i386 is already installed
        package elfutils-libelf-devel-static-0.137-3.el5.i386 is already installed
        package elfutils-libelf-devel-0.137-3.el5.i386 is already installed
[root@oracle1011gserver Server]# rpm -Uvh gcc-4.*
warning: gcc-4.1.2-51.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
        package gcc-4.1.2-51.el5.i386 is already installed
[root@oracle1011gserver Server]# rpm -Uvh gcc-c++-4.*
warning: gcc-c++-4.1.2-51.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
        package gcc-c++-4.1.2-51.el5.i386 is already installed
[root@oracle1011gserver Server]# rpm -Uvh glibc-2.*
warning: glibc-2.5-65.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
warning: package glibc = 2.5-65 was already added, skipping glibc < 2.5-65
Preparing...                ########################################### [100%]
        .
		.
		.
[root@oracle1011gserver Server]#

Com os serviços do Banco de Dados já iniciados, vamos realizar o upgrade do Banco de Dados executando o OUI num Terminal com o usuário “oracle”.

Após o upgrade da versão do Banco de Dados Oracle, vamos executar o script “root.sh” com o usuário “root”.

[root@oracle1011gserver Server]# /oraprd01/app/oracle/product/11.2.0/db_1/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /oraprd01/app/oracle/product/11.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.
Finished product-specific root actions.
[root@oracle1011gserver Server]#

Conforme abaixo ainda estamos o listener da versão 10g.

oracle=dbprod-> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-AUG-2012 03:04:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1011gserver.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                03-AUG-2012 23:45:19
Uptime                    0 days 3 hr. 19 min. 39 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=oracle1011gserver.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 "dbprod" has 2 instance(s).
  Instance "dbprod", status UNKNOWN, has 1 handler(s) for this service...
  Instance "dbprod", status READY, has 1 handler(s) for this service...
Service "dbprodXDB" has 1 instance(s).
  Instance "dbprod", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle=dbprod->

Vamos verificar a versão do Banco de Dados após a atualização da Versão e alterar o parâmetro “compatible” para versão atualizada.

oracle=dbprod-> sqlplus sys/********* as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 4 03:06:09 2012

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

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - Production

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.1.0
SQL> set lines 155
SQL> col COMP_NAME for a35
SQL> select VERSION, STATUS, MODIFIED, STARTUP, COMP_NAME from dba_registry;

VERSION                        STATUS      MODIFIED             STARTUP  COMP_NAME
------------------------------ ----------- -------------------- -------- -----------------------------------
11.2.0.1.0                     VALID       04-AUG-2012 02:10:32          Oracle Enterprise Manager
11.2.0.1.0                     VALID       04-AUG-2012 02:45:25          Oracle Multimedia
11.2.0.1.0                     VALID       04-AUG-2012 02:45:23          Oracle XML Database
11.2.0.1.0                     VALID       04-AUG-2012 02:45:23          Oracle Expression Filter
11.2.0.1.0                     VALID       04-AUG-2012 02:45:24          Oracle Rule Manager
11.2.0.1.0                     VALID       04-AUG-2012 02:45:22          Oracle Workspace Manager
11.2.0.1.0                     VALID       04-AUG-2012 02:45:22          Oracle Database Catalog Views
11.2.0.1.0                     VALID       04-AUG-2012 02:45:22          Oracle Database Packages and Types
11.2.0.1.0                     VALID       04-AUG-2012 02:45:23          JServer JAVA Virtual Machine
11.2.0.1.0                     VALID       04-AUG-2012 02:45:23          Oracle XDK
11.2.0.1.0                     VALID       04-AUG-2012 02:45:23          Oracle Database Java Packages

11 rows selected.

SQL> alter system set compatible='11.2.0.1.0' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
oracle=dbprod->
37 150x150 Migrando Banco de Dados Oracle 10g para 11g com OUI (Oracle Universal Installer) non RAC.

Enterprise Manager versão 10g.

Vamos parar e recriar o Enterprise Manager, parar e reconfigurar o Listener para a versão 11g.

oracle=dbprod-> emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://oracle1011gserver.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...

all attemps to stop oc4j failed... now trying to kill 9
--- Failed to shutdown DBConsole Gracefully ---
 ...  Stopped.
oracle=dbprod-> lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-AUG-2012 03:12:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1011gserver.localdomain)(PORT=1521)))
The command completed successfully
oracle=dbprod->

Vamos ajustar o Listener para a versão 11g.

oracle=dbprod-> pwd
/oraprd01/app/oracle/product/11.2.0/db_1
oracle=dbprod-> cd network/admin/
oracle=dbprod-> ls -l
total 20
-rw-r--r-- 1 oracle oinstall  346 Aug  4 01:28 listener.ora
drwxr-xr-x 2 oracle oinstall 4096 Aug  4 01:12 samples
-rw-r--r-- 1 oracle oinstall  187 May  9  2007 shrept.lst
-rw-r--r-- 1 oracle oinstall  218 Aug  4 01:28 sqlnet.ora
-rw-r----- 1 oracle oinstall  353 Aug  4 02:42 tnsnames.ora
oracle=dbprod-> cat listener.ora
# listener.ora Network Configuration File: /oraprd01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1011gserver.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /oraprd01/app/oracle

oracle=dbprod-> cd /oraprd01/app/oracle/product/10.2.0/db_1/network/admin/
oracle=dbprod-> cat listener.ora
# listener.ora Network Configuration File: /oraprd01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oraprd01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = dbprod)
      (ORACLE_HOME = /oraprd01/app/oracle/product/10.2.0/db_1)
      (SERVICE_NAME = dbprod)
    )
  )

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

oracle=dbprod-> cd ../../../../11.2.0/db_1/network/admin/
oracle=dbprod-> vi listener.ora
oracle=dbprod-> cat listener.ora
# listener.ora Network Configuration File: /oraprd01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dbprod)
      (ORACLE_HOME = /oraprd01/app/oracle/product/11.2.0/db_1)
      (SERVICE_NAME = dbprod)
    )
  )

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

ADR_BASE_LISTENER = /oraprd01/app/oracle
oracle=dbprod->

Vamos verificar o “tnsnames.ora” para a versão 11g.

oracle=dbprod-> cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oraprd01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1011gserver.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbprod)
    )
  )

oracle=dbprod->

Vamos iniciar os serviços do Banco de Dados Oracle ew verificar o “compatible”.
– Iniciando o Listener;

oracle=dbprod-> lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-AUG-2012 03:19:06

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

Starting /oraprd01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1011gserver.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                04-AUG-2012 03:19:07
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/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oraprd01/app/oracle/diag/tnslsnr/oracle1011gserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1011gserver.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "dbprod" has 1 instance(s).
  Instance "dbprod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle=dbprod->

– Iniciando a Instância Oracle;

oracle=dbprod-> sqlplus sys/******** as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 4 03:20:43 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  824442880 bytes
Fixed Size                  1339600 bytes
Variable Size             503320368 bytes
Database Buffers          314572800 bytes
Redo Buffers                5210112 bytes
Database mounted.
Database opened.
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.1.0
SQL> !
oracle=dbprod->

Agora basta iniciar e recriar o Enterprise Manager.
Para este, acessar Recriando o repositório Enterprise Manager.

38 150x150 Migrando Banco de Dados Oracle 10g para 11g com OUI (Oracle Universal Installer) non RAC.

Enterprise Manager versão 11g.

Após de tudo atualizado e migrado para a versão 11g, basta remover os binários do Banco de Dados 10g.

oracle=dbprod-> cd /oraprd01/app/oracle/product/
oracle=dbprod-> ls -l
total 8
drwxr-xr-x 3 oracle oinstall 4096 Jul 28 17:49 10.2.0
drwxr-xr-x 3 oracle oinstall 4096 Aug  4 00:46 11.2.0
oracle=dbprod-> rm -rf 10.2.0

Pronto migramos o Banco de Dados da versão 10g para 11g via OUI.

%name Migrando Banco de Dados Oracle 10g para 11g com OUI (Oracle Universal Installer) non RAC.

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.