Ø
Steps
For Upgrading Oracle Database To 19c Using DBUA
·
Database preinstall 19c
·
19c software install
·
Pre-upgrade check
·
Run the pre-upgrade fixup script
·
Run utlrp.sql ( to compile invalid objects)
·
Check database component status
·
To enable restore , in case of failure, enable
flashback option
·
Start DBUA
·
Post Check
·
Updating sqlnet.ora file
·
Updating compatible parameter post upgrade
[root@ranesh ~]# yum install -y
https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
Loaded plugins: refresh-packagekit,
security
Setting up Install Process
oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
| 18 kB 00:00
Examining
/var/tmp/yum-root-3mPcvL/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm:
oracle-database-preinstall-19c-1.0-1.el7.x86_64
Marking
/var/tmp/yum-root-3mPcvL/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm to
be installed
Resolving Dependencies
--> Running transaction check
---> Package
oracle-database-preinstall-19c.x86_64 0:1.0-1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=======================================================================================================================================================================
Package
Arch Version Repository
Size
=======================================================================================================================================================================
Installing:
oracle-database-preinstall-19c x86_64 1.0-1.el7 /oracle-database-preinstall-19c-1.0-1.el7.x86_64 55 k
Transaction Summary
=======================================================================================================================================================================
Install 1 Package(s)
Total size: 55 k
Installed size: 55 k
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : oracle-database-preinstall-19c-1.0-1.el7.x86_64
1/1
Verifying : oracle-database-preinstall-19c-1.0-1.el7.x86_64
1/1
Installed:
oracle-database-preinstall-19c.x86_64 0:1.0-1.el7
Complete!
unzip the binary and run
runInstaller.sh
mkdir
-p /oracle/app/oracle/product/19.0.0.0/dbhome_1
./runInstaller.sh
PREUPGRADE CHECK
[oracle@new19c
dbhome_1]$ cd jdk/bin/
[oracle@new19c bin]$
java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar
TEXT TERMINAL
output
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-02-27T19:23:07
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to
upgrade
=======================================
Database Name: PROD
Container Name: prod
Container ID: 0
Version: 12.1.0.2.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: ARCHIVELOG
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
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Set
DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4706
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4560 MB. There is currently 4472 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area
: /u01/app/oracle/fast_recovery_area
Limit
: 4560 MB
Used
: 88 MB
Available
: 4472 MB
The database has
archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.
RECOMMENDED ACTIONS
===================
2. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.
The database contains APEX version 4.2.5.00.08. Upgrade APEX to at least
version 18.2.0.00.12.
Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.
3.
(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 12.1.0.2
Oracle Database SQL Tuning Guide.
4.
(AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
5. To help you keep track of your
tablespace allocations, the following
AUTOEXTEND
tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 600 MB 717 MB
SYSTEM 780 MB 1126 MB
TEMP 60 MB 150 MB
UNDOTBS1 60 MB 448 MB
Minimum
tablespace sizes for upgrade are estimates.
6. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database PROD
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved
by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/prod/
preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
7. Upgrade the database time zone file using the DBMS_DST package.
The database
is using time zone file version 18 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
8. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in
directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
9.
(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.
10. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system 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.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database PROD
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved
by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/prod/
preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/prod/
preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/prod/
preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/prod/
preupgrade/postupgrade_fixups.sql
Execute
fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the
preupgrade fixups
@/u01/app/oracle/cfgtoollogs/prod/
preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the
postupgrade fixups
@/u01/app/oracle/cfgtoollogs/prod/
preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-02-27T19:23:07
[oracle@new19c
preupgrade]$
ls
checksBuffer.tmp preupgrade_driver.sql
components.properties preupgrade_fixups.sql
dbms_registry_extended.sql preupgrade.log
oracle preupgrade_messages.properties
parameters.properties preupgrade_package.sql
postupgrade_fixups.sql upgrade
[oracle@new19c
preupgrade]$ pwd
/u01/app/oracle/cfgtoollogs/prod/preupgrade
sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Thu Feb 27 19:26:51 2020
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>
@preupgrade_fixups.sql
Executing Oracle
PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0
Build: 1
Generated on: 2020-02-27 19:22:59
For Source
Database: PROD
Source Database Version:
12.1.0.2.0
For Upgrade to
Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied
Further DBA Action
------ ------------------------ ----------
--------------------------------
1.
min_recovery_area_size NO Manual fixup required.
2.
apex_manual_upgrade NO Manual fixup recommended.
3. dictionary_stats YES None.
4.
pre_fixed_objects YES None.
5.
tablespaces_info NO Informational only.
Further action is optional.
6.
rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have
been run and resolved what they can. However,
there are still issues
originally identified by the preupgrade that
have not been remedied
and are still present in the database.
Depending on the
severity of the specific issue, and the nature of
the issue itself, that
could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by
reviewing
the
preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or
Preupgrade Action Number listed above.
There you will find the
original corresponding diagnostic message
from the preupgrade
which explains in more detail what still needs
to be done.
PL/SQL procedure
successfully completed.
INVALID COUNT
SQL> select count(*)
from dba_objects where status='INVALID';
COUNT(*)
----------
0
Check database
component status
SQL> set pagesize500
set linesize 100
select substr(comp_name,1,40)
comp_name, status, substr(version,1,10) version from dba_registry order by
comp_name;SQL> SQL>
COMP_NAME STATUS VERSION
----------------------------------------
----------- ----------
JServer JAVA Virtual
Machine VALID
12.1.0.2.0
OLAP Analytic Workspace VALID 12.1.0.2.0
Oracle Application
Express VALID
4.2.5.00.0
Oracle Database Catalog
Views VALID
12.1.0.2.0
Oracle Database Java
Packages VALID
12.1.0.2.0
Oracle Database Packages
and Types VALID
12.1.0.2.0
Oracle Database Vault VALID
12.1.0.2.0
Oracle Label Security VALID
12.1.0.2.0
Oracle Multimedia VALID
12.1.0.2.0
Oracle OLAP API VALID 12.1.0.2.0
Oracle Real Application
Clusters OPTION OFF 12.1.0.2.0
Oracle Text VALID
12.1.0.2.0
Oracle Workspace Manager VALID 12.1.0.2.0
Oracle XDK VALID
12.1.0.2.0
Oracle XML Database VALID
12.1.0.2.0
Spatial VALID 12.1.0.2.0
16 rows selected.
SQL> select * from
v$timezone_file;
FILENAME VERSION CON_ID
--------------------
---------- ----------
timezlrg_18.dat 18
0
SQL> SELECT * FROM
v$backup WHERE status != 'NOT ACTIVE';
no rows selected
SQL> SELECT * FROM
v$recover_file;
no rows selected
To enable restore
, in case of failure, enable flashback option.
SQL> alter system set
db_recovery_file_dest_size=20G scope=both;
System altered.
SQL> alter database
flashback on;
Database altered.
SQL> !date
Fri 28 Feb 11:36:42 IST
2020
[oracle@new19c
dbhome_1]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@new19c
dbhome_1]$
[oracle@new19c
dbhome_1]$ cd bin/
[oracle@new19c bin]$
./dbua
We can
pause and resume the upgrade during the process also.
cd /u01/app/oracle/cfgtoollogs/VIS/preupgrade
@postupgrade_fixup.sql
Post Check
SQL> select comp_id,status from
dba_registry;
COMP_ID STATUS
------------------------------ -----------
CATALOG VALID
CATPROC VALID
JAVAVM
VALID
XML VALID
CATJAVA VALID
RAC OPTION
OFF
XDB VALID
OWM VALID
CONTEXT VALID
ORDIM VALID
10 rowss selected.
SQL> select * from v$timezone_file;
FILENAME VERSION
CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0
Updating
sqlnet.ora file
export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1
cd $ORACLE_HOME/network/admin
cat sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
Updating
compatible parameter post upgrade
alter system set compatible='19.0.0'
scope=spfile;
shutdown immediate;
startup
SELECT name, value FROM v$parameter
WHERE name = 'compatible';
