Oracle database can be upgraded directly to the new release from the following releases:
- 11.2.0.4
- 12.1.0.2
- 12.2.0.1
- 18
If your current Oracle Database is a release earlier than 11.2.0.4, then you cannot directly upgrade your Oracle Database
to the latest release. In this case, you are required to upgrade to an intermediate release before upgrading to
There are many methods available for upgrading database to 19c . A few of them are mentioned below:-
- Database Upgrade Assistant(DBUA)
- Manual Upgrade
- Full Transportable Export Import
- Transportable Tablespace
- Datapump Export Import
Here we will be demonstrating how to upgrade database from 12c to 19c using DBUA utility.
Environment
- Step 1.
Pre-requisites for Upgrade:-
1.Install Oracle Binary 19c on a different home other than 12c home. in our case the 19c db home is
/u01/app/oracle/product/19.0.0/dbhome_1
The installation of 19c can be found here.
2. Take full DB Backup before performing the upgrade.
3. Check the number of invalid objects.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
If invalid objects exist, try to validate them by running utlrp.sql from the 12c
SQL>@?/rdbms/admin/utlrp.sql
4. Purge Recyclebin.
SQL> PURGE DBA_RECYCLEBIN;
5.Stop the running 12c listener
lsnrctl stop LISTENER
6. To enable restore point , set db_recovery_file_dest location.
SQL>alter system set db_recovery_file_dest_size=20G scope=both;
SQL>alter system set db_recovery_file_dest=’/u01/archive_log’ scope=both;
- Step 2.
Running the Pre-Upgrade Information Tool
To check your system and database to see if it is ready for upgrade, we use the Pre-Upgrade Information Tool (preupgrade.jar)
[oracle@gg1 12.2.0.1]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
[oracle@gg1 ~]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-06-03T12:18:10
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL12C
Container Name: orcl12c
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
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
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
================
None
RECOMMENDED ACTIONS
===================
1. (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.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
2. 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 470 MB 500 MB
SYSTEM 800 MB 912 MB
TEMP 32 MB 150 MB
UNDOTBS1 65 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
3. 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 ORCL12C
which are identified above as BEFORE UPGRADE “(AUTOFIXUP)” can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
4. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 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.
5. 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.
6. (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.
7. 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.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL12C
which are identified above as AFTER UPGRADE “(AUTOFIXUP)” can be resolved by executing the following
SQL>@/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl12c/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/orcl12c/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-06-03T12:18:12
- Step 3.
Check the tablespace information reported in preupgrade log, and make sure the autoextenstion is enabled for those
tablespace.
SQL> select tablespace_name,autoextensible from dba_data_Files;
TABLESPACE_NAME AUT
—————————— —
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
USERS YES
- Step 4.
Run preupgrade_fixups.sql
export ORACLE_SID=orcl12c
[oracle@gg1 ~]$ sqlplus / as sysdba
SQL> @/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/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-06-03 12:17:40
For Source Database: ORCL12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- ----------------------------
1. pre_fixed_objects YES None.
2. tablespaces_info NO Informational only.
Further action is optional.
3. 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.
- Step 5.
Run the DBUA and start upgrade.
Please note the source database should be up and running.
[oracle@gg1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@gg1 ~]$ export ORACLE_SID=orcl12c
[oracle@gg1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@gg1 ~]$ dbua
select the source database, and provide password for sys user,then click the “Next” button.
This screen shows the result for prerequisite checks . We need to fix the errors reported any in this screen before proceeding.
Choose the option shown below and click on next
We chose the option to use flashback and guaranteed restore point in case of any upgrade failure occurs
Choose the listener and click on next
We are not going to configure EM, hence we didnt select it. Click next
Click Next
This concludes the database installation..
- Step 6.
Run “postupgrade_fixups.sql” to check post upgrade status.
[oracle@gg1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@gg1 ~]$ export ORACLE_SID=orcl12c
[oracle@gg1 ~]$ sqlplus / as sysdba
SQL> @/u01/app/oracle/cfgtoollogs/orcl12c/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-06-03 12:18:07
For Source Database: ORCL12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
—— ———————— ———- ——————————–
4. old_time_zones_exist YES None.
5. dir_symlinks YES None.
6. post_dictionary YES None.
7. post_fixed_objects 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 upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_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.
Session altered.
- Step 7.
Verify the DB Version
SQL> select VERSION,name,open_mode from v$instance,v$database;
VERSION NAME OPEN_MODE
—————– ——— ——————–
19.0.0.0.0 ORCL12C READ WRITE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
——————– ———- ———-
timezlrg_32.dat 32 0
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
———-
32
Re validate any invalid objects
SQL>@?/rdbms/admin/utlrp.sql
Leave a Reply