Roteiro de migração do RDBMS

Atualizando Oracle 12c em Oracle Linux 7

As etapas de instalação dos pacotes e pre-requisitos do Sistema Operacional não serão mostradas neste artigo. Nosso objetivo e mostrar o roteiro de atualização de banco 12c em um ambiente onde o 11g já está em funcionamento e portanto, a maioria do procedimentos de instalação e atualização do Oracle Linux 7 (sistema operacional hospedeiro do RDBMS) já foram executados.

Detalhes de instalação e preparação do Oracle Linux 7 para RDBMS Oracle, bem como a instalação e configuração do Oracle Grid Infrastructure 12c, também serão objetos abordados em outro artigo.

É importante e necessário consultar o manual, nele encontraremos todos os pontos de atenção para a atualização da versão, é importante que o manual seja lido e seguido, assim evitaremos surpresas desagradáveis durante e após o processo de atualização.

Oracle Database Upgrade Guide

Consultando o manual, a primeira coisa a se verificar será qual o método que escolheremos para o upgrade. Direto ou indireto.

Assim encontramos na Página 1-3:

Oracle Database Releases That Support Direct Upgrade
Review the supported options for direct upgrades to the latest Oracle Database
release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.3 or later
• 12.1.0.1 and 12.1.0.2

Demais versões anteriores a 11.2.0.3, devemos observar os intruções de upgrade dos manuais de de upgrade relativos a versão de destino intermediário para o upgrade direto para a 12.2.

por exemplo:

Upgrade release 10.2.0.5 to release 12.1.0.2 using the instructions in Oracle
Database Upgrade Guide 12c Release 1 (12.1), including running the pre-upgrade
script for 12.1.0.2.

Capítulo 1 página 4 encontramos a matriz de caminhos para upgrade:

O próximo passo é planejar o fluxo de trabalho para o upgrade, abaixo é mostrado o fluxo de upgrade, também encontrado no manual página 4 do capítulo 1.

A escolha da versão antes de fazer o download é muito importante. Evita perda de tempo precioso em downloads desnecessários e até tentativas de instalação incorretas.

Abaixo apresento a forma de identificar a versão adequada para fazer o download:

Identificado o arquivo a ser selecionado para download, é importante ajustar o parâmetro de inicialização COMPATIBLE para pelo menos 11.2.0.

A tabela abaixo mostra os valores de COMPATIBLE para as versões do 12c

  • The COMPATIBLE Initialization Parameter
    Oracle Database Release                                   Default           Minimum           Maximum
    Oracle Database 12c Release 2 (12.2)               12.2.0              11.2.0                 12.2.0
    Oracle Database 12c Release 1 (12.1)               12.0.0              11.0.0                 12.1.0
    Oracle Database 11g Release 2 (11.2)                11.2.0             10.0.0                  11.2.0

Rman is not a option to use on version upgrades.

Escolha do método de fazer o upgrade. Na página 1-2 todos os métodos são mostrados, eu escolhi o manual, por uma questão de controle e confiabilidade no processo.

Listar todos os objetos inválidos no banco, se houver.

Tentar eliminar o motivo, é recomendável que não haja objetos inválidos no banco.

SQL Plan Management

Coletar os planos de execução da aplicação antes da migração. Experiência em migrações anteriores mostra que algumas situações de uso se modificam quando se atualiza a versão do RDBMS, causando mal estar  (O cliente sempre espera uma melhoria de desempenho, nunca a queda quando da troca de versão) e queda de desempenho nas aplicações. A melhor forma de evitar situações deste tipo no projeto, é coletar o comportamento da aplicação e após a migração aplicar o mesmo plano de execução após a migração.

Esta situação deve ser mapeada com cuidado, pois, algumas oportunidades de melhoria podem ocorrer, entretanto, com esta ação, garantiremos no mínimo o mesmo desempenho que a versão anterior.

Como coletar e aplicar o SQL Plan após a virada:

No Oracle 11g os planos são coletados e armazenados através do "Outline".

Storing Outlines
Oracle stores outline data in the OL$, OL$HINTS, and OL$NODES tables. Unless you remove them, Oracle retains outlines indefinitely.

Creating an SQL Plan Baseline
================================

SQL> show parameter baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
optimizer_capture_sql_plan_baselines boolean
optimizer_use_sql_plan_baselines     boolean
SQL> show parameter out

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
create_stored_outlines               string
ddl_lock_timeout integer             0
distributed_lock_timeout integer     60
log_checkpoint_timeout integer       1800
resumable_timeout integer            1800
smtp_out_server                      string

SQL> alter system set create_stored_outlines=true;

System altered.

SQL> alter system set optimizer_capture_sql_plan_baselines=true;

System altered.

SQL> show parameter baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>
SQL>
SQL> show parameter create_stored_outlines

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
create_stored_outlines               string      TRUE
SQL>

SQL> select table_name from dba_tables where table_name in

('OL$', 'OL$HINTS', 'OL$NODES');

TABLE_NAME
------------------------------
OL$
OL$HINTS
OL$NODES
OL$
OL$HINTS
OL$NODES

6 rows selected.

SQL>

O procedimento acima irá iniciar uma coleta das SQLs com frequência de uso, armazenando em tabelas para que se possa ser possível exporta-las mais tarde ou, como é nosso objetivo, utilizar após a migração caso a queda de desempenho da aplicação ocorra. Mais tarde podemos instruir o banco a utilizar estes planos de execução.

NOTA: Não se esqueça de voltar os parâmetros pra os valores default antes da migração.

Na página 2-14, iremos encontrar um checklist das atividades de preparação para a

atualização:

          

Verifying Materialized View Refreshes are Complete Before Upgrade

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s
WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

Não podemos esquecer de verificar a atualização das MV antes de iniciar a atualização, assim como as transações distribuídas (2pc_pending) e atualização de Stand-by se houver.

Iniciando o processo de Atualização:

$ env |grep ORA
ORACLE_BASE=/u02/app/oracle
ORACLE_HOME=/u02/app/oracle/product/11.2.0
ORACLE_SID=db0012
ORACLE_UNQNAME=db001
$ java -jar /u03/app/oracle/product/11.2.0/rdbms/admin/preupgrade.jar
Preupgrade generated files:
/u02/app/oracle/cfgtoollogs/db001/preupgrade/preupgrade.log
/u02/app/oracle/cfgtoollogs/db001/preupgrade/preupgrade_fixups.sql
/u02/app/oracle/cfgtoollogs/db001/preupgrade/postupgrade_fixups.sql
$

$ cat /u02/app/oracle/cfgtoollogs/db001/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: DB001
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE

Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] INVALID
JServer JAVA Virtual Machine [to be upgraded] INVALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [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
Expression Filter [to be upgraded] VALID
Rule Manager [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
---------- ---------- -------- ---------- ------

DEVL_IAS_UMS 100 MB DISABLED 6144 KB None
PADRAO_IAS_UMS 100 MB DISABLED 6144 KB None
SYSAUX 930 MB ENABLED 1097 MB None
SYSTEM 4130 MB ENABLED 4537 MB None
TEMP 100 MB ENABLED 150 MB None
UNDOTBS2 2580 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.

+ Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

Parameter 12.2.0.1.0 minimum
--------- ------------------
db_cache_size* 50331648
java_pool_size* 104857600
shared_pool_size* 692060160

* These minimum memory/pool sizes are recommended for the upgrade process

RECOMMENDED ACTIONS
===================
+ Consider removing the following DEPRECATED initialization parameters.
They are not OBSOLETE in version 12.2.0.1.0
but probably will be OBSOLETE in a future release.

Parameter
------------------------------
db_block_buffers
sec_case_sensitive_logon

+ Run 11.2.0.4.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with

SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;

350 objects are INVALID.

There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.

+ Backup the existing ACLs and their assignments for reference. Use the
new DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views to administer
network privileges after upgrade.

The database contains network ACLs with privileges that will be migrated
to a new format in 12c.

Network access control list (ACL) privileges in 11g will be migrated to
a new format in 12c. As part of the migration, new
DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views are provided, and
privileges in the existing ACLs will be converted to the new format with
new ACL names. The old ACL names, DBMS_NETWORK_ACL_ADMIN interfaces and
dictionary views may continue to be used but are deprecated and their
use is discouraged. For further information, refer to My Oracle Support
note number 2078710.1.

+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Dictionary statistics do not exist or are stale (not up-to-date).

Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.

For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
+ Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
3077 MB of archived logs. Check alert log during the upgrade that there
is no write error to the destination due to lack of disk space. Execute
'archive log list' and query v$archive_dest for more
LOG_ARCHIVE_DEST_<N> destinations to check.

Archiving cannot proceed if the archive log destination is full during
upgrade.

Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : +DATA

The database has archiving enabled and LOG_ARCHIVE_DEST_<N> set. The
upgrade process will need free disk space in the archive log
destination(s) to generate archived logs to.

=============
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
===================
+ If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.

There are user tables dependent on Oracle-Maintained object types.

If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type
is evolved during the upgrade, any dependent tables need to be
re-validated and upgraded to the latest type version AFTER the database
upgrade completes.

+ Upgrade the database time zone version using the DBMS_DST package.

The database is using timezone datafile version 14 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.

+ Gather statistics on fixed objects two weeks after the upgrade using the
command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

This recommendation is given for all preupgrade runs.

Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.

SQL> @/u02/app/oracle/cfgtoollogs/db001/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2019-02-01 14:52:47

For Source Database: DB001
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0

Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
invalid_objects_exist Failed Manual fixup recommended. (fix with @?/rdbms/admin/utlrp)
network_acl_priv Failed Manual fixup recommended.
dictionary_stats Passed None
min_archive_dest_size Failed Manual fixup recommended.

PL/SQL procedure successfully completed.

SQL>

NOTA:

Neste caso em particular, vamos migrar mesmo com os erros de INVALID, pois se trata de componentes do Oracle que estão com o problema, não a aplicação. Nosso cliente foi informado, como retorno, obtivemos a resposta de que o problema já era conhecido e resultado de uma remoção anterior de outro produto. Estes componentes foram removidos e reinstalados após a migração com sucesso. 

Agora teremos o seguinte roteiro:

Upgrade process
===================

Chapter 3
Manual Upgrade Scenarios for Multitenant Architecture Oracle Databases

1. Back up your current database.
2. Install the Oracle Database 12c software for the new release.
3. Ensure that the Pre-Upgrade Information Tool (preupgrade.jar) has run on the
source database, and that any issues reported by the tool are addressed.
4. Start SQL*Plus From the directory ORACLE_HOME/rdbms/admin.
5. Log in to a user account with SYSDBA system privileges, and connect to the
database that you want to upgrade:
CONNECT / AS SYSDBA
6. Start the database in upgrade mode. Use the command for your configuration
type.
Multitenant container database (CDB):
SQL> startup upgrade;
SQL> alter pluggable database all open upgrade;
Non-CDB:
SQL> startup upgrade
7. Exit SQL*Plus.
8. Run the Parallel Upgrade Utility from the new Oracle home.
You can run the utility as a shell command (dbupgrade on Linux and UNIX, and
dbupgrade.cmd on Windows) or you can run it as a Perl command
(catctl.pl).
For example, on Linux and UNIX:
cd $ORACLE_HOME/bin
./dbupgrade -T

Note:

Upgrade Manual Chapter 2 page 31

To take schema-based tablespaces offline, run the Parallel Upgrade Utility
(catctl.pl) from the command line, using the -T option.

If a catastrophic upgrade failure occurs, then you can run commands in the log files
manually to bring up tablespaces.

For example, on Windows:
cd %ORACLE_HOME%\bin
dbupgrade
The Parallel Upgrade Utility starts the upgrade process.

Upgrade actions:
============================

Os comandos utilizados estão destacados na cor verde.

$ cat initdb0012.ora
#spfile='+DATA/DB001/PARAMETERFILE/spfile.387.998936491'
spfile='+DATA/DB001/PARAMETERFILE/spfile'
$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 5 09:46:51 2019

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 423624704 bytes
Fixed Size 8621328 bytes
Variable Size 360710896 bytes
Database Buffers 50331648 bytes
Redo Buffers 3960832 bytes

SQL>

srvctl upgrade database -db db001 -oraclehome /u03/app/oracle/product/12.2.0

ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

startup mount exclusive;

alter system set cluster_database=FALSE scope=spfile sid='*' ;

SQL> alter system set cluster_database=FALSE scope=spfile sid='*' ;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup upgrade;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 8620704 bytes
Variable Size 306185568 bytes
Database Buffers 50331648 bytes
Redo Buffers 3960832 bytes
Database mounted.
Database opened.
SQL>

SQL> SHOW PDBS
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ cd $ORACLE_HOME/bin
$ ./dbupgrade

Argument list for [/u03/app/oracle/product/12.2.0/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 = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
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]

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

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

Log file directory = [/tmp/cfgtoollogs/upgrade20190206110422]

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

Number of Cpus = 1
Database Name = db001
DataBase Version = 11.2.0.4.0
catcon: ALL catcon-related output will be written to [/u03/app/oracle/product/12.2.0/cfgtoollogs/db001/upgrade20190206110427/catupgrd_catcon_13290.lst]
catcon: See [/u03/app/oracle/product/12.2.0/cfgtoollogs/db001/upgrade20190206110427/catupgrd*.log] files for output generated by scripts
catcon: See [/u03/app/oracle/product/12.2.0/cfgtoollogs/db001/upgrade20190206110427/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u03/app/oracle/product/12.2.0/cfgtoollogs/db001/upgrade20190206110427]

Parallel SQL Process Count = 4
Components in [db001]
Installed [CATALOG CATJAVA CATPROC CONTEXT JAVAVM OWM RAC XDB XML]
Not Installed [APEX APS DV EM MGW ODM OLS ORDIM SDO WK XOQ]

------------------------------------------------------
Phases [0-115] Start Time:[2019_02_06 11:04:28]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [db001] Files:1
.
.
.
.
------------------------------------------------------
------------------------------------------------------
Identifier EXF 19-02-06 01:09:14
SCRIPT = [/u03/app/oracle/product/12.2.0/rdbms/admin/catnoexf.sql]
ERROR = [Uncaught exception Root of all Java exceptions: java.lang.UnsatisfiedLinkError java.lang.Throwable.fillInStackTrace
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier EXF 19-02-06 01:09:14
SCRIPT = [/u03/app/oracle/product/12.2.0/rdbms/admin/catnoexf.sql]
ERROR = [java.lang.Throwable.fillInStackTrace]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier EXF 19-02-06 01:09:36
SCRIPT = [/u03/app/oracle/product/12.2.0/rdbms/admin/catnoexf.sql]
ERROR = [ORA-29548: Java system class reported: could not identify release specified in classes.bin
ORA-06512: at "SYS.DBMS_JAVA", line 599
ORA-06512: at line 1
]
STATEMENT = [BEGIN sys.dbms_java.dropjava('-s rdbms/jlib/ExprFilter.jar'); END;
]
------------------------------------------------------
------------------------------------------------------
Identifier EXF 19-02-06 01:09:36
SCRIPT = [/u03/app/oracle/product/12.2.0/rdbms/admin/catnoexf.sql]
ERROR = [classes.bin ORA-06512: at "SYS.DBMS_JAVA", line 599
ORA-06512: at line 1
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier EXF 19-02-06 01:09:36
SCRIPT = [/u03/app/oracle/product/12.2.0/rdbms/admin/catnoexf.sql]
ERROR = [ORA-06512: at "SYS.DBMS_JAVA", line 599 ORA-06512: at line 1
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier EXF 19-02-06 01:09:36
SCRIPT = [/u03/app/oracle/product/12.2.0/rdbms/admin/catnoexf.sql]
ERROR = [ORA-06512: at line 1]
STATEMENT = [as above]
------------------------------------------------------

LOG FILES: (/u03/app/oracle/product/12.2.0/cfgtoollogs/db001/upgrade20190206110427/catupgrd*.log)

Upgrade Summary Report Located in:
/u03/app/oracle/product/12.2.0/cfgtoollogs/db001/upgrade20190206110427/upg_summary.log

Grand Total Upgrade Time: [0d:2h:40m:29s]

==================================================================Preupgrade generated files:
/u02/app/oracle/cfgtoollogs/db001/preupgrade/preupgrade.log
/u02/app/oracle/cfgtoollogs/db001/preupgrade/preupgrade_fixups.sql
/u02/app/oracle/cfgtoollogs/db001/preupgrade/postupgrade_fixups.sql

mkdir -p /home/oracle/upgrdDBA

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d \
/u02/app/oracle/cfgtoollogs/db001/preupgrade -l /home/oracle/upgrdDBA -b \
postupgrade_fixups postupgrade_fixups.sql

catcon: ALL catcon-related output will be written to [/home/oracle/upgrdDBA/postupgrade_fixups_catcon_21986.lst]
catcon: See [/home/oracle/upgrdDBA/postupgrade_fixups*.log] files for output generated by scripts
catcon: See [/home/oracle/upgrdDBA/postupgrade_fixups_*.lst] files for spool files, if any
catcon.pl: completed successfully
$

cd $ORACLE_HOME/rdbms/admin/
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

catcon: ALL catcon-related output will be written to [/u03/app/oracle/product/12.2.0/rdbms/admin/utlrp_catcon_3393.lst]
catcon: See [/u03/app/oracle/product/12.2.0/rdbms/admin/utlrp*.log] files for output generated by scripts
catcon: See [/u03/app/oracle/product/12.2.0/rdbms/admin/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully

The catcon.pl script runs utlrp.sql from the $ORACLE_HOME/rdbms/admin directory.
The script recompiles any remaining stored PL/SQL and Java code. Note the
following conditions of this use case:
• The -n parameter is set to 1, so the script runs each PDB recompilation in
sequence.
• Expect a time delay for the serial recompilation of PDBs to complete.
Depending on the number of PDBs that you are upgrading, the recompilation
can extend significantly beyond the time required for the upgrade scripts to
complete.
Run postupgrade_fixups.sql.
Non- CDB:
SQL> @rdbms/admin/postupgrade_fixups.sql

CDB:
$ORACLE_HOME/perl/bin/perl catcon.pl -n1 -e -b postupgradefixups -d '''.''' postupgradefixups.sql

19. Run utlu122s.sql to verify that all issues have been fixed.
Non-CDB:
SQL> @rdbms/admin/utlu122s.sql

CDB:
$ORACLE_HOME/perl/bin/perl catcon.pl -n1 -e -b utlu122s -d '''.''' utlu122s.sql

When you use catcon.pl to run utlu122s.sql, the log file utlu122s0.log is
generated. The log file provides the upgrade results. You can also review the
upgrade report, upg_summary.log.
To see information about the state of the database, run utlu122s.sql as many
times as you want, at any time after the upgrade is completed. If the
utlu122s.sql script returns errors, or shows components that do not have the
status VALID, or if the version listed for the component is not the most recent
release, then perform troubleshooting.

20. (Conditional) For Oracle RAC environments only, enter the following commands to
set the initialization parameter value for CLUSTER_DATABASE to TRUE, and to start
the Oracle RAC database, where dbname is the name of the Oracle RAC database:

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
srvctl start database -db db_unique_name

===========================================================
The database upgrade process is now finished.   <<<!! But we have issues
===========================================================

COMPLEMENTOS:
==================
Information On Installed Database Components and Schemas (Doc ID 472937.1)
https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=fpozessih_4&_afrLoop=476991997204276#aref_section26

column comp_id format a16
column comp_name format a36
column version format a12
column status format a7

select comp_id,COMP_NAME,VERSION,STATUS,SCHEMA from dba_registry order by 3;

COMP_ID COMP_NAME VERSION STATUS SCHEMA
------------------------------ ------------------------------------------------------------ ------------------------------ ----------- --------------------------------------------------------------------------------------------------------------------------------
EXF Oracle Expression Filter 11.2.0.4.0 REMOVED EXFSYS
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 INVALID SYS
RUL Oracle Rules Manager 11.2.0.4.0 REMOVED EXFSYS
CATJAVA Oracle Database Java Packages 12.2.0.1.0 INVALID SYS
XDB Oracle XML Database 12.2.0.1.0 VALID XDB
OWM Oracle Workspace Manager 12.2.0.1.0 VALID WMSYS
CONTEXT Oracle Text 12.2.0.1.0 VALID CTXSYS
XML Oracle XDK 12.2.0.1.0 VALID SYS
CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID SYS
CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID SYS
RAC Oracle Real Application Clusters 12.2.0.1.0 VALID SYS

11 rows selected.

How to remove and reinstall the XDK in Oracle 12 (Doc ID 2470780.1)
================================================================================
To remove the XDK, run the following as SYSDBA

@?/xdk/admin/rmxml.sql

To install the XDK, run the following as SYSDBA

@?/xdk/admin/initxml.sql

================================================================================

Oracle Database Java Packages (CATJAVA) Option Missing from DBA_REGISTRY (Doc ID 551411.1)
How to Reload the JVM in 12.1.0.x (Doc ID 1612279.1)
@?/rdbms/admin/catjava.sql   --  See Remove and Reinstall Oracle JVM notes below
===============================================

Select comp_name, status, version
from dba_registry
where upper(comp_name) like '%JAVA%' or comp_name like '%XDK%';

================================================================================

Remove and Reinstall Oracle JVM
==================================

How to Reload the JVM in 12.1.0.x (Doc ID 1612279.1)

1. Verify that the following System Requirements are available

The Shared Pool has at least 96 MB of free space.
The Java Pool has at least 50 MB of free space
The SYSTEM tablespace has at least 70 MB of free space
The SYSTEM RBS has at least 100 MB of free space

Removing JAVAVM
=================

Shutdown the database instance
Create the following REMOVAL script, full_rmjvm.sql
Run it from a new SQL*Plus session

-- Start of File full_rmjvm.sql
spool full_rmjvm.log
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;
alter database open;
@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit
-- End of File full_rmjvm.sql

Install the JVM

Be sure the REMOVAL script, full_rmjvm.sql, completed successfully
Create the following INSTALL script, full_jvminst.sql
Run it from a new SQL*Plus session

-- Start of File full_jvminst.sql
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off

exit
-- End of File full_jvminst.sql

================================================================================

How To Install/Uninstall Expression Filter Feature or EXFSYS schema along with Rules Manager (Doc ID 258618.1)

startup open;

column comp_id format a16
column comp_name format a36
column version format a12
column status format a7
select comp_id, comp_name, version, status from dba_registry;

COMP_ID COMP_NAME VERSION STATUS
---------------- ------------------------------------ ------------ -------
CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID
CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID
XML Oracle XDK 12.2.0.1.0 REMOVED
CATJAVA Oracle Database Java Packages 12.2.0.1.0 REMOVED
EXF Oracle Expression Filter 11.2.0.4.0 REMOVED <<<<<<<<<<<<<<
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 INVALID
RUL Oracle Rules Manager 11.2.0.4.0 REMOVED <<<<<<<<<<<<<<
RAC Oracle Real Application Clusters 12.2.0.1.0 VALID
OWM Oracle Workspace Manager 12.2.0.1.0 VALID
CONTEXT Oracle Text 12.2.0.1.0 VALID
XDB Oracle XML Database 12.2.0.1.0 VALID

How To Install/Uninstall Expression Filter Feature or EXFSYS schema along with Rules Manager (Doc ID 258618.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=477254112321809&id=258618.1&_adf.ctrl-state=fpozessih_141

1. Login as SYSDBA

Uninstalling the EXFSYS schema:
1. Login as SYSDBA

2. @?/rdbms/admin/catnoexf.sql

Install EXF

1. @?/rdbms/admin/catexf.sql

NOTE:
1. Installing Expression Filter does not automatically install Rules Manager.
This component has to be installed separately using the catrul.sql script

2. When reinstalling Expression Filter, you may have to run utlrp.sql to recompile some invalid objects.
if this does not help, then to validate the components follow the Note 457861.1

SQL> @?/rdbms/admin/utlrp

SQL> @?/rdbms/admin/catrul

If you dont see (or not found or missing) the Oracle Expression Filter (EXF) or Oracle Rules Manager (RUL) in the dba_registry, then you have to repeat the above steps after running the below scripts, (Refer Note 753041.1)

Restart database with "startup upgrade"
SQL> @?/rdbms/admin/exfpatch.sql

SQL> @?/rdbms/admin/rulpatch.sql

Uninstalling the EXFSYS schema:

1. Login as SYSDBA

2. @?/rdbms/admin/catnoexf.sql

NOTE:
1. Uninstalling Expression Filter implicitly uninstalls Rules Manager.

2. The CATNOEXF.SQL script may not drop public synonyms created by catexf.sql.
However, with 11.2, it was verified no PUBLIC SYNONYMS for table_owner = 'EXFSYS' were left.

SQL> @?/rdbms/admin/utlrp

If you dont see (or not found or missing) the Oracle Expression Filter (EXF) or Oracle Rules Manager (RUL) in the dba_registry, then you have to repeat the above steps after running the below scripts, (Refer Note 753041.1)

Restart database with "startup upgrade"
SQL> @?/rdbms/admin/exfpatch.sql

SQL> @?/rdbms/admin/rulpatch.sql

Uninstalling the EXFSYS schema:

1. Login as SYSDBA

2. @?/rdbms/admin/catnoexf.sql

NOTE:
1. Uninstalling Expression Filter implicitly uninstalls Rules Manager.

2. The CATNOEXF.SQL script may not drop public synonyms created by catexf.sql.
However, with 11.2, it was verified no PUBLIC SYNONYMS for table_owner = 'EXFSYS' were left.

The following script can be used (ONLY!!!!) to drop all PUBLIC SYNONYMS created for Rules Manager and Expression Filter when needed:

declare
cursor cur1 is select synonym_name from all_synonyms where owner = 'PUBLIC' and table_owner = 'EXFSYS';
begin
for c1 in cur1 loop
EXECUTE IMMEDIATE 'drop public synonym ' || dbms_assert.enquote_name(c1.synonym_name, false);
end loop;
end;
/

Verifying for invalid objects
===================================

set linesize 300

col object_name format a30
col owner format a30
col object_type format a30

select count(*),object_name,owner,object_type from dba_objects where status <> 'VALID' group by object_name,owner,object_type;

@?/rdbms/admin/utlrp

How to Locate and Compile Invalid Java Class and Resource Objects in Order to
Resolve the Error: 'Primavera has detected that there may be Invalid database objects' (Doc ID 912478.1)

Compiling Java Class Objects
Java Class objects cannot be compiled using the ALTER statement mentioned above. This instead require a special procedure. To determine if you have invalid Java objects, look at the object type for any mention of 'Java'.

OWNER OBJECT_TYPE OBJECT_NAME STATUS
----------- -------------- ----------------------- -------
EXPADMIN JAVA SOURCE ExponlineComm INVALID

If Java objects are returned in the invalid object list, a stored procedure
can be run to validate the Java Classes.

Log in to the Oracle instance with sysdba rights.
Example: sqlplus sys/password@instance as sysdba

Run the following command: Exec utl_recomp.recomp_serial ();

SQL>

column comp_id format a16
column comp_name format a36
column version format a12
column status format a7
select comp_id, comp_name, version, status from dba_registry;

SQL> SQL> SQL> SQL>

COMP_ID COMP_NAME VERSION STATUS
---------------- ------------------------------------ ------------ -------
CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID
CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID
XML Oracle XDK 12.2.0.1.0 VALID
CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID
RAC Oracle Real Application Clusters 12.2.0.1.0 VALID
OWM Oracle Workspace Manager 12.2.0.1.0 VALID
CONTEXT Oracle Text 12.2.0.1.0 VALID
XDB Oracle XML Database 12.2.0.1.0 VALID
RUL Oracle Rules Manager 12.2.0.1.0 VALID
EXF Oracle Expression Filter 12.2.0.1.0 VALID

11 rows selected.

SQL>

$

SQL> @/u02/app/oracle/cfgtoollogs/db001/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

No errors.

Package created.

No errors.

Package body created.

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-02-04 12:06:30

For Source Database: DB001
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0

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

PL/SQL procedure successfully completed.

Session altered.

SQL>

========================================================================================================================
12.1:Executing postupgrade_fixups.sql shows "OLDER TIMEZONE IN USE" . (Doc ID 2092618.1)

CAUSE
Postupgrade script (which is run after the user's upgrade) , blindly reported the same error regardless of whether the user had fixed the condition between the two points in time.

This issue is reported in the Bug 17303129 : UPGRADE DATABASE FROM 11.1.0.7 TO 12.1.0.1, "OLDER TIMEZONE IN USE" OCCURRED

SOLUTION
Compare the output of following two commands:

SQL> select version FROM v$timezone_file;

SQL> select TZ_VERSION from registry$database;

If it is the same then meesage can be ignored .

SQL> select version FROM v$timezone_file;

VERSION
----------
14

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
14

========================================================================================================================
12.2 DB Post Upgrade:postupgrade_fixups.sql shows "depend_usr_tables Failed Manual fixup recommended" (Doc ID 2266026.1)

CAUSE
Internal Bug raised for the issue.

SOLUTION
1) If Database Upgraded without using -T option,then FAILED status to be ignored. <<<<<<<<<<<<<<<<< OUR CASE

or

2) If Database Upgraded with using -T option .

Then execute $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete.

And then ignore FAILED status .
========================================================================================================================

$ srvctl status database -d db001
Instance db0012 is running on node saturno

$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
==================================================================
NOW you can consider that the migration was successfull completed.
==================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

NOTA:

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

refere-se ao parâmetro sec_case_sensitive_logon

RECOMMENDED ACTIONS
===================
+ Consider removing the following DEPRECATED initialization parameters.
They are not OBSOLETE in version 12.2.0.1.0
but probably will be OBSOLETE in a future release.

Parameter
------------------------------
db_block_buffers
sec_case_sensitive_logon

Revisado em 2019/Feb/11 11:57

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *