How to Upgrade from 11g,12c,18c to 19c
DB Upgrade Compatibility Matrix
Below is minimum version of the database that can be directly upgraded to Oracle 19c.
| Source Database | Target Database |
| 11.2.0.4 | 19c |
| 12.1.0.2 | 19c |
| 12.2.0.1 | 19c |
| 18.1.0.0 | 19c |
NOTE: In this post I will demonstrate the upgrade from 12.1.0.2 to 19c. The steps are same for any other version to 19c upgrade.
Upgrading the database from 12.1.0.2 to 19c
I will be using DBUA its really easy to use and self explanatory as well as recommended options.
It automates many things including upgrading Time zone, gathering dictionary statistics , taking restore RMAN backup or creating restore point , starting the listener with new OH etc. etc.
Although DBUA can do many thing its recommended to everything possible that can be done upfront to minimize the overall downtime.
It automates many things including upgrading Time zone, gathering dictionary statistics , taking restore RMAN backup or creating restore point , starting the listener with new OH etc. etc.
Although DBUA can do many thing its recommended to everything possible that can be done upfront to minimize the overall downtime.
General Requirement
- Ensure All DB Components and objects are valid.
- Apply Latest PSU
- Make Sure you have a valid Database Backup
- Check Database Upgrade/Downgrade compatibility Matrix
- Disable any scheduled/custom jobs
- Verify whether you OS is certified for oracle 19c version
- Execute preupgrade script and go through the recommendations
- Empty the RECYCLEBIN
- Ensure Backup is not running and no file is in recovery mode
- Cretae restore point to rollback the database in case upgrade fails
Step1> Install 19c Oracle Home And Apply latest PSU
Instructions to install 19c and Patch is provided in below post
Step2> Run preupgrade Script
$12_ORACLE_HOME/jdk/bin/java -jar $19c_ORACLE_HOME/rdbms/admin/preupgrade.jar TERMINAL TEXT
Example:
/ora_app/product/12.1.0.2/db_home1/jdk/bin/java -jar /ora_app/product/19c/db_home1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Step3> Review the prepgradelog and Take Necessary Action
Example log is provided in the bottom of the post.
Step3> Source the Environment Variable and run dbua
$ export ORACLE_HOME=/ora_app/product/19c/db_home1
$ export PATH=/ora_app/product/19c/db_home1/bin:$PATH
$ cd $ORACLE_HOME/bin/
$ ./dbua
$ export PATH=/ora_app/product/19c/db_home1/bin:$PATH
$ cd $ORACLE_HOME/bin/
$ ./dbua
Step4> Follow the screen instructions
Enter user name sys and its password and click next
On this page review all the warnings as fix.
I am here ignoring some of them because they are known to me.
click next to proceed.
Tick the check boxes and click next.
This page offers you various possibility to preserve the database state before upgrading it so you can rollback the upgrade in case it fails.
I am choosing the Restore point I created before hand itself and click next
This pages offers you the option to migrate the existing listener to new OH or create new listener from upgraded OH.
Choose appropriate option and click next
Choose appropriate option on this page and click next
Post Successful Upgrade :
Drop the restore point.
Change the compatible parameter
Trigger full database backup
===========================================================
PREUPGRADE LOG
===========================================================
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-09-21T10:39:11
19.0.0.0.0 Build: 1 on 2020-09-21T10:39:11
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL1D
Container Name: ORCL1D
Container ID: 0
Version: 12.1.0.2.0
DB Patch Level: DATABASE PATCH SET UPDATE 12.1.0.2.200714
Compatible: 12.1.0.2
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Status of the database prior to upgrade
=======================================
Database Name: ORCL1D
Container Name: ORCL1D
Container ID: 0
Version: 12.1.0.2.0
DB Patch Level: DATABASE PATCH SET UPDATE 12.1.0.2.200714
Compatible: 12.1.0.2
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] 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
---------------- -------------- --------------
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
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
================
1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
The database contains 2462 objects in the recycle bin.
The recycle bin must be completely empty before database upgrade.
RECOMMENDED ACTIONS
===================
2. Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
===================
2. Run 12.1.0.2.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;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
410 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
database upgrade.
3. Perform one of the following:
1) Expire user accounts that use only the old 10G password version and
follow the procedure recommended in Oracle Database Upgrade Guide under
the section entitled, "Checking for Accounts Using Case-Insensitive
Password Version".
2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19
SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short
term approach and is not recommended because it will retain known
security risks associated with the 10G password version.)
1) Expire user accounts that use only the old 10G password version and
follow the procedure recommended in Oracle Database Upgrade Guide under
the section entitled, "Checking for Accounts Using Case-Insensitive
Password Version".
2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19
SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short
term approach and is not recommended because it will retain known
security risks associated with the 10G password version.)
Your database system has at least one account with only the 10G password
version (see the PASSWORD_VERSIONS column of DBA_USERS).
version (see the PASSWORD_VERSIONS column of DBA_USERS).
Starting with Oracle Database release 12.2.0.1, Exclusive Mode is the new
default password-based authentication mode. All Exclusive Mode
login/authentication attempts will fail for preexisting user accounts
which only have the 10G password version and neither the 11G or 12C
password version (see DBA_USERS.PASSWORD_VERSIONS.) For more information,
refer to "Understanding Password Case Sensitivity and Upgrades" in the
Oracle Database Upgrade Guide.
default password-based authentication mode. All Exclusive Mode
login/authentication attempts will fail for preexisting user accounts
which only have the 10G password version and neither the 11G or 12C
password version (see DBA_USERS.PASSWORD_VERSIONS.) For more information,
refer to "Understanding Password Case Sensitivity and Upgrades" in the
Oracle Database Upgrade Guide.
4. Remove the SEC_CASE_SENSITIVE_LOGON instance initialization parameter
setting, to configure your system to use case sensitive password
authentication by default.
setting, to configure your system to use case sensitive password
authentication by default.
Your database system is configured to enforce case insensitive password
authentication (the SEC_CASE_SENSITIVE_LOGON instance initialization
parameter setting is FALSE).
authentication (the SEC_CASE_SENSITIVE_LOGON instance initialization
parameter setting is FALSE).
Starting with Oracle Database release 12.2, Exclusive Mode is the default
password-based authentication mode. Case insensitive password
authentication is not supported in Exclusive Mode. If your system needs
to use case insensitive password authentication, Exclusive Mode must be
switched off prior to the upgrade. See the Network Reference Manual
chapter about the SQLNET.ORA parameter
SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.
password-based authentication mode. Case insensitive password
authentication is not supported in Exclusive Mode. If your system needs
to use case insensitive password authentication, Exclusive Mode must be
switched off prior to the upgrade. See the Network Reference Manual
chapter about the SQLNET.ORA parameter
SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.
5. Remove Streams setup. For detailed steps, refer to the section "Removing
an Oracle Streams Configuration" in the Oracle Streams Concepts and
Administration Guide specific for the Oracle release from which you are
removing. For versions pre-12.1.0.2, the procedure
dbms_streams_adm.remove_streams_configuration must not be used as may
lead to unwanted results. Instead, use the other procedures
(dbms_capture_adm.drop_capture, dbms_apply_adm.drop_apply,
dbms_streams_adm.remove_queue, etc). For 12.1.0.2 and higher, procedure
dbms_streams_adm.remove_streams_configuration can be safely used.
an Oracle Streams Configuration" in the Oracle Streams Concepts and
Administration Guide specific for the Oracle release from which you are
removing. For versions pre-12.1.0.2, the procedure
dbms_streams_adm.remove_streams_configuration must not be used as may
lead to unwanted results. Instead, use the other procedures
(dbms_capture_adm.drop_capture, dbms_apply_adm.drop_apply,
dbms_streams_adm.remove_queue, etc). For 12.1.0.2 and higher, procedure
dbms_streams_adm.remove_streams_configuration can be safely used.
Oracle Streams feature is configured in the database.
Starting with Oracle Database 19, Oracle Streams is desupported. It is
strongly advised to remove any streams configuration manually.
strongly advised to remove any streams configuration manually.
6. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh. Please refer to the
Materialized View section in MOS Note 2380601.1 for more details.
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh. Please refer to the
Materialized View section in MOS Note 2380601.1 for more details.
There are one or more materialized views in either stale or invalid
state, or which are currently being refreshed.
state, or which are currently being refreshed.
Oracle recommends that all materialized views (MV's) are refreshed before
upgrading the database because this will clear the MV logs and the
sumdelta$ table and may reduce the upgrade time. If you choose to not
refresh some MVs, the change data for those MV's will be carried through
the UPGRADE process. After UPGRADE, you can refresh the MV's and MV
incremental refresh should work in normal cases.
upgrading the database because this will clear the MV logs and the
sumdelta$ table and may reduce the upgrade time. If you choose to not
refresh some MVs, the change data for those MV's will be carried through
the UPGRADE process. After UPGRADE, you can refresh the MV's and MV
incremental refresh should work in normal cases.
7. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
The database contains the following initialization parameters whose name
begins with an underscore:
begins with an underscore:
_fix_control
Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
8. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
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.
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.
Oracle Database SQL Tuning Guide.
9. (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
owner of the trigger or drop and re-create the trigger with a user that
was granted directly with such. You can list those triggers using: SELECT
OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').
owner of the trigger or drop and re-create the trigger with a user that
was granted directly with such. You can list those triggers using: SELECT
OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').
There is one or more database triggers whose owner does not have the
right privilege on the database.
right privilege on the database.
The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.
INFORMATION ONLY
================
10. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
================
10. 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
---------- ---------- -----------
SYSTEM 2100 MB 2323 MB
Tablespace Size For Upgrade
---------- ---------- -----------
SYSTEM 2100 MB 2323 MB
Minimum tablespace sizes for upgrade are estimates.
11. Consider removing the following deprecated initialization parameters.
Parameter
---------
sec_case_sensitive_logon
---------
sec_case_sensitive_logon
These deprecated parameters probably will be obsolete in a future release.
12. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
3237 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.
3237 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.
Archiving cannot proceed if the archive log destination is full during
upgrade.
upgrade.
Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : /ora00_data2/ORCL1D/arc1
Parameter : LOG_ARCHIVE_DEST_1
Destination : /ora00_data2/ORCL1D/arc1
The database has archiving enabled. The upgrade process will need free
disk space in the archive log destination(s) to generate archived logs to.
disk space in the archive log destination(s) to generate archived logs to.
13. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
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.
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.
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL1D
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
=============================
All of the issues in database ORCL1D
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/ora_app/cfgtoollogs/ORCL1D/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
================
None
RECOMMENDED ACTIONS
===================
14. (AUTOFIXUP) 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.
===================
14. (AUTOFIXUP) 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, 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.
15. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 14 and the target 19 release
ships with time zone file version 32.
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.
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.
16. Recreate directory objects to remove any symbolic links from directory
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.
Found 23 user directory objects to be checked: BACKUP, BACK_DMP,
DATA_FILE_DIR, DOC_DIR, DP_DIR_MSR1P, EXPORT, EXPORT_FINAL, EXP_DIR,
EXP_DIR_MERLIN, EXP_DIR_META, EXP_DIR_RS3, EXP_IM3607989, IMP,
LOG_FILE_DIR, MEDIA_DIR, PREUPG_OUTPUT_DIR, TASK0635096, TASK1698793,
TASK1740890, TOAD_TRACEFILE_DIR, UPLOADS, XMLFILES, backup_dmp.
DATA_FILE_DIR, DOC_DIR, DP_DIR_MSR1P, EXPORT, EXPORT_FINAL, EXP_DIR,
EXP_DIR_MERLIN, EXP_DIR_META, EXP_DIR_RS3, EXP_IM3607989, IMP,
LOG_FILE_DIR, MEDIA_DIR, PREUPG_OUTPUT_DIR, TASK0635096, TASK1698793,
TASK1740890, TOAD_TRACEFILE_DIR, UPLOADS, XMLFILES, backup_dmp.
Starting in Release 18c, symbolic links are not allowed in directory
object paths used with BFILE data types, the UTL_FILE package, or
external tables.
object paths used with BFILE data types, the UTL_FILE package, or
external tables.
17. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
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.
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.
18. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
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.
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 Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL1D
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
=============================
All of the issues in database ORCL1D
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/ora_app/cfgtoollogs/ORCL1D/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/ora_app/cfgtoollogs/ORCL1D/preupgrade/preupgrade.log
/ora_app/cfgtoollogs/ORCL1D/preupgrade/preupgrade_fixups.sql
/ora_app/cfgtoollogs/ORCL1D/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/ora_app/cfgtoollogs/ORCL1D/preupgrade/preupgrade_fixups.sql
@/ora_app/cfgtoollogs/ORCL1D/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/ora_app/cfgtoollogs/ORCL1D/preupgrade/postupgrade_fixups.sql
@/ora_app/cfgtoollogs/ORCL1D/preupgrade/postupgrade_fixups.sql
I love this blog!! The flash up the top is awesome!!delta upgrade to first class
ReplyDelete