Database Upgrade Reference

This guide is intended for Oracle^® database administrators and experienced IEE implementation teams.

Important! Read this reference guide before upgrading to Oracle 19c.

Major procedures with points of reference

This reference guide is a prerequisite to the IEE installation and upgrade guides for Oracle database. This reference guide provides information and references to help you successfully upgrade your Oracle database to Oracle 19c before upgrading the IEE database to 10.4.

Before upgrading to Oracle 19c, your software must be at release 7.0 SP4.

Step 1. Oracle database 19c upgrade

Oracle provides a wealth of information to assist in the planning and upgrading of your company’s IEE ecosystem to Oracle 19c. The following Oracle documentation is recommended as a starting point. Itron recommends that you use the My Oracle Support portal and Oracle Technology Network as the primary source of reference when planning and implementing the upgrade to Oracle 19c.

For example, when upgrading Itron's environment to Oracle 19c, Itron's DBA used the following references.

• Oracle Upgrade Companion (Doc ID 1905086.1)

• Oracle Database (RDBMS) Releases Support Status Summary (Doc ID 161818.1)

• Oracle Database Upgrades and Migrations page: https://www.oracle.com/database/upgrades/

• Oracle Database Online Documentation 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/index.html

• Oracle Database Upgrade Guide for Release 19: https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/index.html

• Oracle Database (RDBMS) on Unix AIX, HP-UX, Linux, Mac OS X, Solaris, Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (12.1) (Doc ID 1587357.1)

Critical upgrade steps

The Oracle 19c upgrade steps that are critical to a successful upgrade of IEE 10.4 are as follows:

1. Preserve configuration information, object / system statistics, and performance baselines.

   It is important to preserve the production database before starting the 19c upgrade. Preserving the production database provides a known good-state of the database that is available once the upgrade is complete.

   • Collecting sufficient performance data prior to the upgrade is vital.

   • Sufficient means: Starting at least 90 days before the upgrade.

   • Automatic Workload Repository (AWR)

     • Take snapshots every 30-60 minutes – retention: >90 days

     • Extract the AWR with: SQL> @?/rdbms/admin/awrextr.sql

     • Use AWR DIFF reports to compare before & after upgrade performance:

       » DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML

   •  Capture existing execution plans to seed SQL Plan Management (SPM).

     • HOW TO LOAD SQL PLANS INTO SPM FROM AWR [ID 789888.1]

     • HOW TO TRANSPORT A SQL TUNING SET [ID 456019.1]

   • OS System Level Statistics (OS Watcher)

   • Save old configuration information (RDA / OCM)

   • IEE major processes baselines (ARI, DRS, LDA, ABE, and so on)

2. Review and alter, as needed, the following initialization parameters. If the current value defined is larger than the value listed in the following parameters, continue to use the currently defined value. For example, if initialization parameter processes is currently set to 2500, do not alter to 2000.

   Note: All initialization parameters are baseline values. Monitor your organization’s workload and make changes as required.

   ALTER SYSTEM SET query_rewrite_integrity=trusted SCOPE=BOTH;

   ALTER SYSTEM SET cursor_sharing=EXACT SCOPE=BOTH;

   Note: The Oracle 19c cursor_sharing mode deprecates the cursor_sharing=similar setting. IEE uses the default value EXACT.

   ALTER SYSTEM SET processes=2000 SCOPE=SPFILE;

   ALTER SYSTEM SET session_cached_cursors=300 SCOPE=SPFILE;

   ALTER SYSTEM SET OPEN_CURSORS=500 SCOPE=BOTH;

   AlTER SYSTEM SET optimizer_index_cost_adj=60 SCOPE=BOTH;

   ALTER SYSTEM SET deferred_segment_creation=false SCOPE=BOTH;

   ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH;

   DO NOT Set:

   • MEMORY_TARGET (Automatic Memory Management)
   • MEMORY_MAX_TARGET (Automatic Memory Management)

3. Add the following parameters to the server sqlnet.ora to resolve error ORA-28040 ("No matching authentication protocol"):

   • SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
   • SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

For more information, see Configure Oracle Database Instance in the IEE Upgrade Guide for Oracle.

Step 2. Execute ItronEE Day in the Life operational processing

After upgrading the production database to Oracle 19c, resume ItronEE Day in the Life operational processing, monitoring the processing performance. If there is a degradation in performance, use the preserved configuration information baseline statistics to determine the cause of the degradation. If the degradation is determined to be the result of suboptimal execution plans, the execution plans captured and preserved before the 19c upgrade can be used to seed the 19c SQL Plan Management baseline plan for the identified SQL statement.

Step 3. ItronEE tasks

Refer to the IEE Upgrade Guide for Oracle.

Step 4. ItronEE upgrade

Refer to the IEE Upgrade Guide for Oracle.

Step 5. ItronEE post-upgrade tasks

Refer to the IEE Upgrade Guide for Oracle.