Manually Upgrading to Oracle Database 12c

No post anterior, vimos como atualizar para o Oracle 12c Release 2 utilizando o utilitário dbua.

Vamos agora atualizar de forma manual o nosso banco, da versão 12.1.0.2 para a 12.2.0.1.

Antes de iniciar, da mesma fora, execute a ferramenta de pre-upgrade para fazer as verificações necessárias e assim poder corrigir qualquer coisa que tenha que ser feita antes do upgrade. Após isso, pare o instância.

$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar
Preupgrade generated files:
    /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
    /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
    /u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
$ cat /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  ORCL
     Container Name:  ORCL
       Container ID:  0
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID


==============
BEFORE UPGRADE
==============

  Run <preupgradeLogDirPath>/preupgrade_fixups.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             550 MB  DISABLED      500 MB  None
     SYSTEM                             700 MB  ENABLED      1033 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                           305 MB  ENABLED       400 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.






=============
AFTER UPGRADE
=============

  Run <preupgradeLogDirPath>/postupgrade_fixups.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 18 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 27 16:41:38 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Copie o spfile e o arquivo de senha, orapw, da ORACLE_HOME da versão anterior para a mais nova. Modifique também o arquivo /etc/oratab para apontar para a nova ORACLE_HOME. E modifique as variáveis de ambiente para apontar para a nova ORACLE_HOME.

$ cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileorcl.ora /u01/app/oracle/product/12.2.0/dbhome_1/dbs/
$ cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapworcl /u01/app/oracle/product/12.2.0/dbhome_1/dbs/
$ echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0/dbhome_1

Conecte ao banco e inicie o banco em modo upgrade. Esse modo permite abrir um banco em uma versão anterior do Oracle. Além disso, restringe as sessões apenas a sysdba, desabilita system triggers e executa operações adicionais que preparam o ambiente para a atualização.

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 27 16:51:22 2019

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

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1543503872 bytes
Fixed Size                  8621040 bytes
Variable Size             989856784 bytes
Database Buffers          536870912 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$

Agora, a partir da versão 12.2, temos o utilitário dbupgrade, que executa o Parallel Upgrade Utility(catctl.pl). One “n” é número de processos utilizados para o upgrade em paralelo e l é onde o arquivo de log será salvo.

$ dbupgrade -n 2 -l /u01/app/

Argument list for [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /u01/app/
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/dbhome_1]
/u01/app/oracle/product/12.2.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/12.2.0/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0/dbhome_1]

Analyzing file /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/app/]

catcon: ALL catcon-related output will be written to [/u01/app//catupgrd_catcon_25913.lst]
catcon: See [/u01/app//catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app//catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 2
Database Name         = orcl
DataBase Version      = 12.1.0.2.0
Parallel SQL Process Count            = 2
Components in [orcl]
    Installed [CATALOG CATJAVA CATPROC CONTEXT JAVAVM OWM XDB XML]
Not Installed [APEX APS DV EM MGW ODM OLS ORDIM RAC SDO WK XOQ]

------------------------------------------------------
Phases [0-115]         Start Time:[2019_11_27 17:06:50]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [orcl] Files:1    Time: 58s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl] Files:5    Time: 82s
Restart  Phase #:2    [orcl] Files:1    Time: 0s

...


Suprimu um pouco a saída do script por que é enorme. Depois é só abrir a base normalmente, verificar o resultado do upgrade, recompilar os objetos inválidos e executar o post-upgrade script.

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 27 17:45:56 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1543503872 bytes
Fixed Size                  8621040 bytes
Variable Size             989856784 bytes
Database Buffers          536870912 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu122s.sql




Oracle Database 12.2 Post-Upgrade Status Tool           11-27-2019 17:47:08

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.2.0.1.0  00:16:56
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:05:58
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:00:43
Oracle XDK                             UPGRADED      12.2.0.1.0  00:01:21
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:49
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:01:42
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:15
Final Actions                                                    00:02:29
Post Upgrade                                                     00:06:33

Total Upgrade Time: 00:36:58

Database time zone version is 18. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /u01/app//upg_summary.log

@?/rdbms/admin/utlrp.sql

...

@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
17:58:10 SQL> REM
17:58:10 SQL> REM    Oracle POST-Upgrade Fixup Script
17:58:10 SQL> REM
17:58:10 SQL> REM    Auto-Generated by: Oracle Preupgrade Script
17:58:10 SQL> REM                             Version: 12.2.0.1.0 Build: 1
17:58:10 SQL> REM    Generated on:            2019-11-27 16:41:09
17:58:10 SQL> REM
17:58:10 SQL> REM    Source Database:         ORCL
17:58:10 SQL> REM    Source Database Version: 12.1.0.2.0
17:58:10 SQL> REM    For Upgrade to Version:    12.2.0.1.0
17:58:10 SQL> REM
17:58:10 SQL>
17:58:10 SQL> REM
17:58:10 SQL> REM    Setup Environment
17:58:10 SQL> REM
17:58:10 SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;

Session altered.

Elapsed: 00:00:00.00

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

Package created.

Elapsed: 00:00:00.00
No errors.

Package body created.

Elapsed: 00:00:00.00
No errors.




1 row selected.

Elapsed: 00:00:00.01

Package created.

Elapsed: 00:00:00.22
No errors.

Package body created.

Elapsed: 00:00:01.37
No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2019-11-27 16:41:09

For Source Database:     ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
old_time_zones_exist      Failed  Manual fixup recommended.
post_dictionary           Passed  None

PL/SQL procedure successfully completed.

Elapsed: 00:01:18.24

Session altered.

Elapsed: 00:00:00.00
17:59:30 SQL>

Perceba que o único problema foi o time zone file. Vejamos como atualizar o mesmo manualmente.

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        18

SQL>SELECT DBMS_DST.get_latest_timezone_version from dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         26

SQL> DECLARE
  2    v_tz_version PLS_INTEGER;
  3  BEGIN
  4  v_tz_version := DBMS_DST.get_latest_timezone_version;
  5  DBMS_DST.begin_prepare(v_tz_version);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_DST.find_affected_tables;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_DST.end_prepare;

PL/SQL procedure successfully completed.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1543503872 bytes
Fixed Size                  8621040 bytes
Variable Size            1023411216 bytes
Database Buffers          503316480 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL> DECLARE
  2    v_tz_version PLS_INTEGER;
  3  BEGIN
  4    SELECT DBMS_DST.get_latest_timezone_version
  5    INTO v_tz_version
  6    FROM DUAL;
  7    DBMS_DST.begin_upgrade(v_tz_version);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    v_falhas PLS_INTEGER;
  3  BEGIN
  4  DBMS_DST.upgrade_database(v_falhas);
  5  DBMS_OUTPUT.put_line(v_falhas);
  6  DBMS_DST.end_upgrade(v_falhas);
  7  DBMS_OUTPUT.put_line(v_falhas);
  8  end;
  9  /
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
0
An upgrade window has been successfully ended.
0

PL/SQL procedure successfully completed.

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat              26          0

Nossa base esta atualizada e pronta!

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação OCA 12c, Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Marcado com: , , , , , ,
Publicado em UPGRADE

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Esse Blog é reconhecido pela
Sou um
Certificações
Sou articulista

Clique para seguir este blog e receber notificações via email de novos posts.

Tércio Costa

Tércio Costa

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação OCA 12c, Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Links Pessoais

Serviços verificados

Visualizar Perfil Completo →

Total de Visualizações da Página
  • 180.695 Visualizações
%d blogueiros gostam disto: