Posts from the ‘upgrade’ Category

Oracle 19c – Complete Checklist for Upgrading to Oracle Database 19c (19.x) using DBUA

PURPOSE

The purpose of this article is to help DBA’s / Support Teams to perform the upgrade of a database using DBUA to 19.x.

SCOPE

DBA, Support

DETAILS

Database Upgrade Assistant (DBUA)

Database Upgrade Assistant (DBUA) provides a graphical user interface to guide you through the upgrade of Oracle Database.

About Read-Only Oracle Homes

Upgrade Path / Compatibility Matrix for 19.x Oracle Database

DBUA can upgrade only supported versions of direct upgrade.

Direct Upgrade to 19.x:

Indirect Upgrade to 19.x:

Requirements and Recommendation for source database

Requirements and Recommendations for Target database

Prerequisites for Preparing Oracle Home on Windows

Pre Upgrade

 
FILE|TERMINAL – Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then defaultr is FILE.
TEXT – Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR – Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

Check for Invalid Objects / Components

If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps:

Run utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS   procedure to gather these statistics. For example, enter the following SQL statement:

Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.

Check of TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 19c is version 32.

Case 1 Timezone version of source database is lower or equal 32.

If the source database is using a timezone file lower than version 32 then there is no DST patch to apply in source oracle home or target 19c home.

Case 2 Timezone version of source database is higher than 32.

If the source database uses a Timezone version higher than 32 then BEFORE the upgrade you MUST patch the target 19c $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.

Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Purging Recycle Bin before upgrade

we can purge the recycle bin via DBUA.

Databases That Use Oracle Label Security and Oracle Database Vault

Audit Table Preupgrade and Archive Requirements

For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade.

If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move.

Running the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database.

Oracle Database Vault and Upgrades of Oracle Database Release 11.2

If Oracle Label Security is installed in the earlier release that you are upgrading, then grant the DV_PATCH_ADMIN role to SYS.

To run the OLS preprocess script on a release 11.2 database before upgrading:

1.    Copy the following scripts script from the newly installed Oracle home (19c) to the Oracle home of the database that needs to be upgraded (11.2):

2.    Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.

3.    Run the following statement:

4.    At the system prompt, connect SYS as SYSDBA:

5.    Run the preprocess scripts for Data Vault

You may continue to run your applications on the database while the preprocess scripts are running.

6.    After the olspreupgrade.sql completes its run successfully, start SQL*Plus and connect to the database as DVOWNER.

7.    Run the following SQL statement:

For 12.x Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault

If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts – olspreupgrade.sql, emremove.sql, catnoamd.sql

Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.

Run the following statement:

Save OEM DB Control Configuration (For Oracle Database releases earlier than 12.1)

If you plan to downgrade database after upgrading to 18.x, then before starting the upgrade save the DB Control configuration and data using emdwgrd utility, so that you can restore the files after downgrading.

Steps to Save data:

1. Install the software for the new Oracle Database 12c release.
2. Set ORACLE_HOME to your old Oracle home.
3. Set ORACLE_SID to the SID of the database being upgraded.
4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the upgraded Oracle Database Oracle home.
5. Change directory to the new Oracle Database release Oracle home.
6. Run emdwgrd

Single-instance databases:

emdwgrd[sh|bat] -save -sid old_SID -path save_directory

Oracle Real Application Clusters (Oracle RAC) databases:

Remote copy must be enabled across all cluster member nodes. Use EM_REMCP environment variable, to indicate which remote copy is configured, for example: export EM_REMCP /usr/bin/scp

7. Enter the SYS password for the database that you want to upgrade.

Removing DB Control with emremove.sql (For Oracle Database releases earlier than 12.1)

After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.

Desupport of OLAP Catalog (OLAP AMD) (For Oracle Database releases earlier than 12.1)

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is desupported and will be removed from the database during the database upgrade. This step can be manually performed before the upgrade to reduce downtime. It can be removed by running the SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script. Do not run the script in UPGRADE mode.

Copying Transparent Encryption Oracle Wallets

If you use Oracle wallet with Transparent Data Encryption (TDE), and you use Database Upgrade Assistant (DBUA) to upgrade the database, then copy the sqlnet.ora and wallet file to the new 18.x Oracle home.

You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
3. Open the Oracle wallet in mount.

Check the accounts use Case-Insensitive password version

Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.

About Password Case Sensitivity

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.

Requirement for Upgrading Databases that Use Oracle Warehouse Builder (OWB)

Oracle Warehouse Builder must be installed separately. Starting with Oracle Database 12c, Oracle Warehouse Builder (OWB) is not installed as part of the software for Oracle Database. An installer for Oracle Warehouse Builder is available on Oracle Technology Network. OWB components that may exist from earlier releases are not upgraded as part of the Oracle Database upgrade process.

Invoke DBUA

DBUA ( Step 1 of 9 )

Select the database to be upgrade , we have selected test11204

DBUA ( Step 2 of 9 )

DBUA will execute the preupgrade script (preupgrade.jar) and return warnings / errors.

DBUA ( Step 3 of 9 )

After the pre-upgrade warnings has been addressed, DBUA shows various options like Enable Parallel Upgrade, Recompile invalid objects in post upgrade phase, upgrading timezone, gather statistics before the upgrade, setting the user tablespaces to read only during upgrade. Also, you can provide any custom SQL scripts before or after the upgrade.

DBUA ( Step 4 of 9 )

This screen will show various Recovery options to choose from like creating a Guaranteed Restore Point, or Take an RMAN backup or if the backup is already taken outside DBUA then “I have my own backup and restore stratege ” option can be chosen.

DBUA ( Step 5 of 9 )

in this screen, we can create a new listener or upgrade the existing listener to 19c.

DBUA ( Step 6 of 9 )

This screen is for configure EM express or register the upgraded database with EM Cloud control.

DBUA ( Step 7 of 9 )

This is the summary screen before the actual upgrade starts. Click on Finish to proceed with the upgrade.

DBUA ( Step 8 of 9 )

The DBUA will start the upgrade process of orcl database. It will perform pre-upgrade, database upgrade and post upgrade steps.

DBUA will perform the upgrade of database components like Oracle server, XML etc.

DBUA ( Step 9 of 9 )

The final screen will show the result of upgrade.

It will show the log file location and the time taken by various steps of upgrade.

Post Upgrade

Execute dbupgdiag.sql script to verify status of objects and components. If there are invalid objects then run utlrp.sql to recompile the invalid objects as follows:

Oracle 19c – Complete Checklist for upgrading Oracle 12c, 18c Container Database (CDB) to Oracle 19c Release using DBUA

PURPOSE

The purpose of this article is to perform upgrade of 12c, 18c container databases (CDB with one or more pluggable databases) using DBUA to Oracle 19c release.

SCOPE

DBA, Support

DETAILS

About Database Upgrade Assistant (DBUA)

Upgrade Path / Compatibility Matrix for 12.2 Oracle Container Database.

Minimum version of the database that can be directly upgraded to Oracle 19c.

DBUA can be used to upgrade Oracle 12.1.0.2 or higher container (CDB) databases to 19c release.

Requirements and recommendations for source database

Requirements and Recommendations for Target database

Prerequisites for Preparing Oracle Home on Windows

Your system must meet these requirements before you can upgrade Oracle Database on Microsoft Windows platforms. For security reasons, different Microsoft Windows user accounts configured as Oracle home users for different Oracle homes are not allowed to share the same Oracle Base.

Preupgrade

FILE|TERMINAL – Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then default is FILE.

TEXT – Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.

DIR – Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

Examine the preupgrade.log file and follow the recommendation. Execute the preupgrade_fixups_<pdb_name>.sql against all or respective pluggable database.

The below command will run the preupgrade_fixups.sql against all the pluggable databases.

To run the preupgrade fixups script against a particular pluggable database, below command can be used:

Check for Invalid Objects / Components:

Verify the below queries against the database and all the pluggable databases.

If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps:

Run $ORACLE_HOME/rdbms/admin/utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.

From source home,execute the utlrp.sql against the container and all the pluggable databases.

using -b utlrp, the log file utlrp0.log is generated as the script is run. The log file provides results of the recompile.

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following command:

Above command gather dictionary statistics for all PDBs in a container database.

you can use the below command to gather statistics against a particular PDB.

Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.

It will verify against all the pluggable databases or below query can be used to verify against particular PDB:

Doc ID 1406586.1 – How to Handle Materialized Views When You Upgrade or Clone a Database

Check of TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 19c release is version 32.

Case 1 Timezone version of source database is lower or equal 32.

If the source database is using a timezone file lower than version 32 then there is no DST patch to apply in source oracle home or target 12cR2 home.

Case 2 Timezone version of source database is higher than 32.

If the source database uses a Timezone version higher than 32 then BEFORE the upgrade you MUST patch the target 19c Oracle Home with a timezone data file of the SAME version as the one used in the source release database.

Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Execute below query to check for the status of the backup:

Purging Recycle Bin before upgrade

below command can be used to purge from all the pdbs:

Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault.

If your target Oracle Database release is 12.2 or later, then you can upgrade without disabling Oracle Database Vault.
If you have Oracle Database Vault enabled in your source Oracle Database release, then you can upgrade Oracle Database to Oracle Database 18c and later releases without first disabling Oracle Database Vault. After the upgrade, if your source Oracle Database release is Oracle Database 12c release 1 (12.1) or later, then Oracle Database Vault is enabled with the same enforcement settings that you had in place before the upgrade.

For example, if your source database is Oracle Database release 12.1, and Oracle Database Vault was disabled in that release, then it remains disabled after you upgrade.

If your source Oracle Database release 12.1 database had Oracle Database Vault enabled before the upgrade, then Oracle Database Vault is enabled after the upgrade.

If you manually disable Oracle Database Vault before the upgrade, then you must enable Oracle Database Vault manually after the upgrade

Schema-Only Accounts and Upgrading EXPIRED Password Accounts

Before starting your upgrade, determine if you want to use password authenticate to default Oracle Database accounts where their passwords are in EXPIRED status, and their account is in LOCKED status

During upgrades to Oracle Database 19c, default Oracle accounts that have not had their passwords reset before upgrade (and are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete.

Because of this new feature, default accounts that are changed to schema-only accounts become unavailable for password authentication. The benefit of this feature is that administrators no longer have to periodically rotate the passwords for these Oracle Database-provided schemas. This feature also reduces the security risk of attackers using default passwords to hack into these accounts.

If you want to prevent these Oracle accounts from being set to schema-only accounts during the upgrade, then you must either set a valid strong password for the account before you start the upgrade, or set a valid strong password for these accounts after upgrade, or unlock the accounts before you log in to the upgraded Oracle Database.
After the upgrade, an administrator can also enable password authentication for schema-only accounts. However, for better security, Oracle recommends that you keep these accounts as schema only accounts.

Copying Transparent Encryption Oracle Wallets

If you use Oracle wallet with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home.
You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.

1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
3. Open the Oracle wallet in mount.

Understanding Password Case Sensitivity  and SEC_CASE_SENSITIVE_LOGON parameter

Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode.

For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.

Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:

• Identify if you have accounts that use only 10G case-insensitive password authentication versions.
• Identify if you have Oracle Database 11g release 2 (11.2.0.3) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.
• Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.

Running Upgrades with Read-Only Tablespaces

Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade. Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE.

If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB.

Deprecated Parameters and Desupported Parameters

Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases.
The Pre-Upgrade Information Tool displays any deprecated parameters and desupported parameters it finds in the Deprecated Parameters and Desupported Parameters sections, respectively.

Invoke DBUA

Once all prerequisite checks are successful, run DBUA. Pleaes make sure that environment variables are pointing to Target Oracle 19c.

DBUA (Step 1 of 10)

Choose the database for upgrade to Oracle 19c. In this case, it is cdb12201 container database.

DBUA (Step 2 of 10)

From 12.2, we can prioritize the upgrade of the pluggable database. In the given screen, the cdb12201 container database is having three pdbs : pdb12201, pdb1, pdb2.
The priority of pdb12201 is set to 1 whereas pdb1,pdb2 set with priority 2. Based on this selection DBUA will upgrade pdb12201 first followed by pdb1, pdb2.

DBUA (Step 3 of 10)

Once you choose the database, the DBUA will perform the pre-upgrade steps. It will execute the preupgrade scripts against all the pdbs and show errors / warnings. Choose “Show All Container” for the reported errors. If the errors are fixable than click on “Fix & Check Again”. If there are any errors which can not be fixed by DBUA then manually fix and verify that there are no errors in the prerequisite checks page.

DBUA (Step 4 of 10)

Once the preupgrade warnings / errors has been addressed, In step 4, we can choose options to Enable Parallel upgrade, Recompile Invalid Objects in post-upgrade, Timezone upgrade for all the pdbs (The timezone version on 19c is 32), Gather statistics for all the pdbs.

DBUA (Step 5 of 10)

In the given screen, there are recovery options available. you can choose to Create a Guranteed Restore Point or RMAN backup incase of failure of upgrade.

DBUA (Step 6 of 10)

We can configure new listener or upgrade the existing “LISTENER_12101” listener which is running from 12.2.0.1 home to Target 19c home.

DBUA (Step 7 of 10)

This screen is to configure EM express or register the upgraded database with EM Cloud control.

DBUA (Step 8 of 10)

This is the summary screen before the actual upgrade starts. Click on Finish to proceed with the upgrade.

DBUA (Step 9 of 10)

Once the ugprade stats, DBUA will configure and bring up the database in upgrade mode from 19c home and it will perform the upgrade of CDB$ROOT container.

Based on the priority order choosen on Step 2 of 10. DBUA will configure P1, P2 priority list for PDBs : pdb12201, pdb1 and pdb2. Priority of PDB12201 is set to 1 and priority of pdb1, pdb2 is set to 2.
PDBs in P1 priority list (PDB$SEED, PDB12201) will be upgraded after the upgrade of CDB$ROOT. and PDBs in P2 priority list (PDb1, PDB2) will be upgraded after the upgrade of PDBs of P1 priority list.

After completing the upgrade to root container, DBUA will perform the upgrade of P1 priority list PDBs (PDB$SEED, PDB12201).

then DBUA will perform the upgrade of P2 priority list (pdb1, pdb2).

DBUA (Step 10 of 10)

Once DBUA complete the upgrade, it will show the results.

Below screen shows the total duration and Timezone information.

Oracle 19c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c

PURPOSE

This document is created for use as a guideline and checklist when manually upgrading from Oracle 11gR2 (11.2) or Oracle 12c Release 1 (12.1) or Oracle 12c Release 2 (12.2) to Oracle 19c Release

SCOPE

Database Administrators, Support

DETAILS

Step 1: Upgrade Path for 19c Oracle database

Minimum version of the database that can be directly upgraded to Oracle 19c

Intermediate upgrades needs to be carried for following releases

For any multi-step upgrade, if you must carry out two upgrades to upgrade to the current release, then you must run the preupgrade script twice: First, for the intermediate upgrade release, and second, for the target upgrade target release.  For example, if the database from which you are upgrading is running Oracle Database 10g, then follow these steps:

• 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.
• Upgrade Oracle Database 12c release 1 (12.1.0.2) directly to Oracle Database 19c. Use the instructions in this article, Oracle Database Upgrade Guide, including running the preupgrade script for 19c.

Upgrade to an intermediate Oracle Database release that can be directly upgraded to the current release. Upgrade Oracle Database releases that are not supported for direct upgrade in this release to an intermediate Oracle Database release that is supported for direct upgrade.

This restriction does not apply if you use Oracle Data Pump export/import to migrate data to the new release.

For example:

• If you are upgrading from release 11.2.0.2 or 11.1.0.7, then you must first upgrade to Oracle Database 11g release 2 (11.2.0.4).
• If you are upgrading from release 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 or 10.1.0.5, then you must first upgrade to release 11.2. or 12.1
• If you are upgrading from release 9.2.0.8, then you must first upgrade to a sequence of intermediate Oracle Database releases:
• Upgrade from release 9.2.0.8 to release 11.2.0.4. Then upgrade from release 11.2 to 19c

Changes in 19c release

Support for DBMS_JOB

Oracle continues to support the DBMS_JOB package. However, you must grant the CREATE JOB privilege to the database schemas that submit DBMS_JOB jobs.

Oracle Scheduler replaces the DBMS_JOB package. Although DBMS_JOB is still supported for backward compatibility, Oracle strongly recommends that you switch from DBMS_JOB to Oracle Scheduler.

• During the 19c upgrade for each job in DBMS_JOB a corresponding entry will be created with DBMS_SCHEDULER
• The old DBMS_JOB interface still works. But using it will always create a corresponding entry in the scheduler
• Pre-upgrade check in preupgrade.jar checks for inconsistencies or any issues

Desupport of Oracle Multimedia

Oracle Multimedia is desupported in Oracle Database 19c, and the implementation is removed.

As an alternative for image processing and conversion, Oracle recommends that you store multimedia content in SecureFiles LOBs, and use third party products, such as Piction. The ORDIM component remains in the registry and still has a VALID status. Oracle Multimedia objects and packages remain in the database. However, these objects and packages no longer function, and raise exceptions if there is an attempt made to use them. Oracle Locator is not affected by the desupport of Oracle Multimedia.

Desupport of Oracle Streams

Starting in Oracle Database 19c (19.1), Oracle Streams is desupported. Oracle GoldenGate is the replication solution for Oracle Database.

Note that Oracle Database Advanced Queuing is not deprecated, and is fully supported in Oracle Database 19c. Oracle Streams did not support features added in Oracle Database 12c (12.1) and later releases, including the multitenant architecture, LONG VARCHAR, and other new features. Oracle Streams replication functionality is superseded by GoldenGate.

Preupgrade check “STREAMS_SETUP” will warn if Oracle Streams is presented.  To remove Oracle streams in 18c refer to Oracle documentation, similarly refer to section “Removing an Oracle Streams Configuration” in the Oracle Streams Concepts and Administration Guide specific for the Oracle release from which you are removing.

Step 2: Requirements and recommendations for source database

• Take a cold or hot back up of the source database.
• Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.
• Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before you upgrade an 11g database to Oracle Database 19c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 19c to define the data security roles again.
• If you upgrade an 11g database to Oracle Database 19c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 19c database.
• If your source version’s time zone is already higher than the destination’s home, then apply the matching or any higher time zone patch to your destination home before upgrade.  For latest DST patch refer to Note 412160.1
• IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB.  Refer to Note 1088970.1
  
• No INVALID object / Components in Source
• Execute Preupgrade scripts before upgrade and review the preupgrade log for any issues.
• Execute dbupgdiag.sql (refer Note 556610.1 to download this script) and verify whether there are any INVALID components or objects owned by SYS/SYSTEM. If any, fix them before proceeding to upgrade the database. You can execute utlrp.sql multiple times to VALIDate them, if still objects are INVALID, create a service request with Oracle support.

Step3: Requirements and recommendations for target ORACLE_HOME

• Verify the whether your operating system is certified for 19c.  Click here to launch certification portal
• Install 19c, verify there are no installation related issues.
• Download and install latest RU if any
• Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
  • Remove any _ (underscore) parameter, obsolete and deprecated parameters in pfile
  • Note min value of COMPATIBLE parameter to upgrade 19c is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
• Review patch recommendations as given in the article “Patches to apply before upgrading Oracle GI and DB to 19c (Doc ID 2539751.1)”
• Apply patch 29213893 on target ORACLE_HOME to avoid ORA-01422 error – refer: Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS (Doc ID 2525596.1)

Step 4: Pre-upgrade checks

Clean up database

Empty the recycle bin
Check for INVALID objects in SYS and SYSTEM
Check for duplicate objects in SYS and SYSTEM
Check for INVALID, mandatory, obsolete components

Note: These will be taken care by preupgrade.jar.

Check materialized views

Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views.
Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logs
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Run the following SQL query:

Note: These will be taken care by preupgrade.jar.  Review the preupgrade logs

Schema-Only Accounts and Upgrading EXPIRED Password Accounts

Before starting your upgrade, determine if you want to use password authenticate to default Oracle Database accounts where their passwords are in EXPIRED status, and their account is in LOCKED status

During upgrades to Oracle Database 19c, default Oracle accounts that have not had their passwords reset before upgrade (and are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete.

Because of this new feature, default accounts that are changed to schema-only accounts become unavailable for password authentication. The benefit of this feature is that administrators no longer have to periodically rotate the passwords for these Oracle Database-provided schemas. This feature also reduces the security risk of attackers using default passwords to hack into these accounts.

If you want to prevent these Oracle accounts from being set to schema-only accounts during the upgrade, then you must either set a valid strong password for the account before you start the upgrade, or set a valid strong password for these accounts after upgrade, or unlock the accounts before you log in to the upgraded Oracle Database.

After the upgrade, an administrator can also enable password authentication for schema-only accounts. However, for better security, Oracle recommends that you keep these accounts as schema only accounts.

Copying Transparent Encryption Oracle Wallets

If Oracle wallet used with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home.  You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.

1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.

Open the Oracle wallet in mount.

For example:

Understanding Password Case Sensitivity

Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode.

For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.

Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:

• Identify if you have accounts that use only 10G case-insensitive password authentication versions.
• Identify if you have Oracle Database 11g release 2 (11.2.0.3) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.
• Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.

For more information refer 19c Oracle database documentation

Checking for Accounts Using Case-Insensitive Password Version

Identify if the Oracle Database that getting upgraded has accounts or configuration parameters that are using a case-insensitive password version.

By default, in Oracle Database 12c release 2 (12.2) and later releases, the 10G password version is not generated or allowed.

If you do not set SQLNET.ALLOWED_LOGON_VERSION_SERVER to a permissive authentication protocol that permits case-insensitive versions, and you do not want user accounts authenticated with case-insensitive password versions to be locked out of the database, then you must identify affected accounts, and ensure that they are using case-sensitive password versions.

For more information refer 19c Oracle database documentation

Running Upgrades with Read-Only Tablespaces

Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade.  Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE.  If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB.

Viewing Tablespace Commands In Upgrade Log Files

If a catastrophic upgrade failure occurs, then you can navigate to the log directory (Oracle_base/cfgtoologs/dbua), and run commands in the log files manually to bring up tablespaces. You can view tablespace commands in the following log files:

Non-CDB Upgrades: catupgrd0.log
PDB databases: catupgrdpdbname0.log, where pdbname is the name of the PDB that you are upgrading.

At the beginning of each log file, you find SQL statements such as the following, which sets tables to READ ONLY:

Near the end of each log file, you find SQL statements to reset tables to READ WRITE:

Preparing the New Oracle Home for Upgrading

• Copy configuration files from the Oracle home of the database being upgraded to the new release Oracle Database Oracle home.
• If you have a password file that resides within the old environment Oracle home, then move or copy the password file to the new Oracle home.  It is recommended to recreate the password file freshly to leverage the new features (if any) provided by orapwd utility.
• Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases. Also, alter any parameter whose syntax has changed in the new release.
• If you are upgrading a cluster database, then you need to change CLUSTER_DATABASE=FALSE before starting upgrade

Prerequisites for Preparing Oracle Home on Windows

System must meet these requirements before starting Oracle Database upgrade on Microsoft Windows platforms.

For security reasons, different Microsoft Windows user accounts configured as Oracle home users for different Oracle homes are not allowed to share the same Oracle Base.

• Database upgrade is supported when the same Windows user account is used as the Oracle home user in both the source and destination Oracle homes.
• Database upgrade is supported when the Oracle home from which the database is being upgraded uses the Windows Built-in Account. Releases earlier than Oracle Database 12c (release 11.2 and earlier) only supported the built-in account option for the Oracle home user on Windows.
• The Oracle home user may not have access to files outside its own Oracle Base and Oracle home. If that is the case, then if you choose a different Oracle Base during upgrade, it is possible that Oracle Database services cannot access files in the older Oracle Base. Using DBUA for database upgrade ensures that the Oracle home user has access to files outside of its own Oracle Base and its own Oracle home.
• Before upgrading manually, or before using the custom files from the older Oracle Base (for example, wallets, configuration files and other custom files ), you must grant access to the Oracle home user for these outside files, or copy these files to the new Oracle Base.

Databases That Use Oracle Label Security and Oracle Database Vault

Audit Table Preupgrade and Archive Requirements

For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade.

If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move.

Running the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database.

The olspreupgrade.sql script creates a temporary table PREUPG_AUD$ in the SYS schema and moves the SYSTEM.aud$ records to SYS.PREUPG_AUD$. As a safety measure, Oracle recommends that you archive your audit trail before running the olspreupgrade.sql script. If Oracle Label Security is installed on your database, and you are upgrading from an earlier release, then you must run the OLS preprocess script before upgrading.

Oracle Database Vault and Upgrades of Oracle Database Release 11.2

If Oracle Label Security is installed in the earlier release that you are upgrading, then grant the DV_PATCH_ADMIN role to SYS.

To run the OLS preprocess script on a release 11.2 database before upgrading:

1.    Copy the following scripts script from the newly installed Oracle home (19c) to the Oracle home of the database that needs to be upgraded (11.2):

2.    Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.

3.    Run the following statement:

4.    At the system prompt, connect SYS as SYSDBA:

5.    Run the preprocess scripts for Data Vault

You may continue to run your applications on the database while the preprocess scripts are running.

6.    After the olspreupgrade.sql completes its run successfully, start SQL*Plus and connect to the database as DVOWNER.

7.    Run the following SQL statement:

Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault

If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts – olspreupgrade.sql, emremove.sql, catnoamd.sql

Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.

Run the following statement:

Manually remove DB control with emremove.sql

Stop/shutdown DB control

 
Login as sysdba

Manually remove ORACLE_HOME/HOSTNAME_SID/ and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directory from the system
On windows delete DB Console service OracleDBConsoleSID

Ensure no files are in Back up mode before starting the upgrade

Run the following statement:

Purge Recycle bin

To empty the database recycle bin, run the following command:

Note: The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time.

Performance

Preserve performance statistics
Check network performance
Gather Optimizer statistics

To decrease the amount of downtime, gather statistics. Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement:

Checking Time zone settings

Time zone should less than or equal to target database time zone version. If source is having higher time zone, then apply time zone patch on target ORACLE_HOME to match the source.

About Upgrading Oracle OLAP Data Security Policies

Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before upgrading Oracle Database Release 11g to a current Oracle Database release, delete any data security roles that are defined in the 11g database. After the upgrade, you can use the new release Oracle Database Analytic Workspace Manager to define the data security roles again.

If DB us upgraded from an 11g database without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the later Oracle Database releases.

Step 5: Preupgrade step

Execute Preupgrade script from source home

FILE – Use this option to direct output to a file
TEXT – Use this option to specify log should be in Text format (other option is to have XML output)
DIR – Logs will be created under <output_dir>

Note: You can download latest preupgrade script from the document – How to Download and Run Oracle’s Database Pre-Upgrade Utility (Doc ID 884522.1)

The Pre-Upgrade Tool (preupgrade.jar) creates the following files:

The log file preupgrade.log.
The preupgrade_fixups_pdbname.sql (for PDBs, where pdbname is the name of the PDB) or preupgrade_fixups.sql script (Non-CDB databases).
The postupgrade_fixups_pdbname.sql (for PDBs, where pdbname is the name of the PDB) or postupgrade_fixups.sql script (Non-CDB databases).   Execute this script once upgrade is completed.

It is recommended to execute pre-upgrade fixup script, if any, which are AUTO FIXABLE

Preupgrade fixup script

Before upgrade is started, execute preupgrade fixups (preupgrade_fixups.sql) script manually in SQL*Plus to resolve many of the issues identified by the preupgrade tool.

Dependencies on Network Utility Packages

After executing preupgrade script, verify the preupgrade log

WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the Database Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has dependent objects.
.... USER SYSMAN has dependent objects.
.... USER FLOWS_010600 has dependent objects.

Execute the following query to view DBA_DEPENDIENCIES

To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment.  After the upgrade, grant specific required privileges. Access is based on the usage in the original database.

Check Time zone version

Check if target database’s time zone version is lower than the source database time zone version.  If yes, before starting upgrade time zone should be upgrade without fail.  RDBMS DST patches are available in Note 412160.1

Backing Up Oracle Database for Upgrading

Suggested to backup Oracle database after you run the Pre-Upgrade Information Tool.  Take backup or create a guaranteed restore point or both.  Test your backup.  Ensure there is a proper fallback plan in case of any issues.

Back Up Files to Preserve Downgrade and Recovery Options

Oracle Data Guard Broker Configuration File and Downgrades

With upgrades to Oracle Database 19c and later releases, you must back up the Data Guard broker configuration file to preserve the capability to downgrade to an earlier release.

In releases before Oracle Database 19c, Oracle Database settings that are mapped to Oracle Data Guard broker properties are maintained in the Oracle Data Guard broker configuration file, and can be modified using the DGMGRL command-line interface. However, starting with Oracle Database 19c, these database settings are no longer stored in the broker configuration file. As a result of this change, although you can continue to modify these properties using DGMGRL, the values that you modify are no longer stored in the Oracle Data Guard broker configuration file. Instead, the DGMGRL commands directly modify the Oracle Database initialization parameters or database settings to which these Oracle Data Guard Broker properties are mapped.

Because of this change to the way that property settings are managed, if you use Oracle Data Guard broker, then Oracle recommends that you export your earlier release Oracle Data Guard broker configuration file to a secure backup location before you start the upgrade. If you do not back up the Oracle Data Guard broker configuration file before the upgrade, then after the upgrade, you cannot downgrade to an earlier release and retain the property options you previously selected for Oracle Data Guard

Exporting a Broker Configuration

Use the EXPORT CONFIGURATION command to export the metadata contained in the broker configuration file to a text file.

The directory in which the broker configuration file is stored must be accessible to the Oracle server process.

Connect to the primary database.

The following command exports the broker configuration and stores it in a file named myconfig.txt in the trace directory.

Step 6: Upgrade Database to 19c

Start the upgrade

Shut down the database.

Copy the SPFILE.ORA or INIT.ORA file from the old Oracle home to the target Oracle home.

Start DB in upgrade mode from target ORACLE_HOME

On Linux/Unix

On Windows

Execute Post-Upgrade Status Tool, utlusts.sql and review the upgrade spool log file.  You run the Post-Upgrade Status Tool in the environment of the new release.

Note: In 19c Earlier version utluNNNs.sql is replaced by utlusts.sql

If you are using Oracle Clusterware, set CLUSTER_DATABASE=TRUE and you must upgrade the Oracle Clusterware keys for the database.  Run srvctl for Oracle Database 19c to upgrade the database. For example:

Check upgrade status

Execute dbupgdiag.sql and review the logs.  To download this script refer to Note 556610.1

Recompile INVALID Objects

Execute utlrp.sql multiple times till the number of INVALID objects becomes constant

Step 7: Post-upgrade

Setting Environment variables on Linux and Unix

Confirm that the following environment variables point to the directories of the new Oracle home:

ORACLE_HOME
PATH

Update oratab entries

Modify the corresponding entry in the /etc/oratab file to point to the new ORACLE_HOME location.

Post-upgrade fixup script

Execute post-upgrade fixup scripts generated by the pre-upgrade script.

Create or Migrate Your Password File with ORAPWD

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to EXCLUSIVE, then create or migrate the password file with ORAPWD. Oracle Database 12c and later releases provide a new option to ORAPWD for migrating the password file from your existing database.

With Oracle Database 12c release 2 (12.2) and later releases, if REMOTE_LOGIN_PASSWORDFILE is set to SHARED, then you receive a pre-upgrade check validation warning. You can choose one of the following options to correct this issue:

Disable the password file-based authentication entirely by setting REMOTE_LOGIN_PASSWORDFILE = NONE

Limit the password file-based authentication by setting REMOTE_LOGIN_PASSWORD = EXCLUSIVE

Recovery Catalog Upgrade

If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command

Please refer to Oracle documentation under “Upgrading the Recovery Catalog” for complete information and steps

Upgrade the Time Zone File Version After Upgrading Oracle Database

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version.

For latest DST patch refer to Note 412160.1.

The following scripts get delivered with Oracle Database 18c onward

$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.

$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.

$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Time zone upgrade check script

?/rdbms/admin/utltz_upg_apply.sql
Time zone apply script. Warning: This script will restart the database and adjust time zone data.

Upgrading Statistics Tables

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running DBMS_STATS.UPGRADE_STAT_TABLE.  In the following example, SYS is the owner of the statistics table and ‘dictstattab’ is the name of the statistics table.

Perform this procedure for each statistics table.

Oracle 18c – DBUA In Silent Mode

GOAL

How to Run DBUA in Silent mode ?

SOLUTION

You can use DBUA with the -silent command line option to carry out noninteractive (“silent”) upgrades.

In silent mode, DBUA does not present a user interface. DBUA writes messages (including information, errors, and warnings) to a log file in ORACLE_HOME/cfgtools/dbua/upgradesession_timestamp, where session_timestamp represents the timestamp for the upgrade that DBUA has run.

All the features of the Interactive DBUA are available in non-interactive as well ,some of those

• It is the recommended method for performing Database upgrade at Release and patch level.
  
• DBUA performs major release upgrade of oracle database that are supported for direct upgrade.
• It performs all the upgrade tasks automatically.
• DBUA performs preupgrade check and lists the unsatisfied prerequisites.
• DBUA has option to upgrade timezone information, make user tablespaces to read only, take RMAN backup before upgrade starts.
• DBUA also provides opportunity to restore database backup to rollback DB upgrade.
• It can upgrade existing Listener to 18c or create new listener in 18c during upgrade.
• DBUA shows progress of Upgrade activity in an enhanced manner.

DBUA captures all the activities in logs located at

If Oracle Base is set then the log files will be generated under $ORACLE_BASE/cfgtoollogs/dbua/upgradesession_<timestamp>/<SID>/

If Oracle Base is not set then the log files will be generated under $ORACLE_HOME/cfgtoollogs/dbua/upgradesession_<timestamp>/<SID>/

If Oracle Home is Read Only then the log files will be generated under $ORACLE_BASE/cfgtoollogs/dbua/upgradesession_<timestamp>/<SID>/

Supported Oracle Versions for 18c Upgrade

• DBUA can upgrade only supported versions of direct upgrade.

  Direct Upgrade to 18.x:

  Source Database	Target Database
  11.2.0.3 and Higher	18.x
  12.1.0.x (12.1.0.1 – 12.1.0.2)	18.x
  12.2.0.1	18.x

   

  Indirect Upgrade to 18.x:

  Source Database		Intermediate upgrade path		Target database
  11.2.0.1/11.2.0.2	–>	11.2.0.3/11.2.0.4	–>	18.x
  11.1.0.6/11.1.0.7	–>	11.2.0.3/11.2.0.4	–>	18.x
  10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5	–>	11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2	–>	18.x
  10.1.0.5	–>	11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2	–>	18.x
  9.2.0.8 or earlier	–>	11.2.0.3/11.2.0.4	–>	18.x

DBUA Silent Mode

Execution steps:

When you run DBUA by using the command-line option, you can specify all valid DBUA options in a script form. The script form enables you to avoid entering configuration information in a graphic user interface dialog.

Below is the description of options / flags :

Example

The following command selects the database orcl for upgrade using the noninteractive (“silent”) option:

Few more Cases for Running DBUA in Noninteractive (”Silent”) Mode

Oracle 18c – Complete Checklist for upgrading Oracle 12.x Container Database (CDB) to Oracle Database 18c (18.x) using DBUA

PURPOSE

The purpose of this article is to help DBA’s / Support Teams to perform the upgrade of a database using DBUA to 18.x.

SCOPE

DBA, Support

DETAILS

Database Upgrade Assistant (DBUA)

Database Upgrade Assistant (DBUA) provides a graphical user interface to guide you through the upgrade of Oracle Database.

About Read-Only Oracle Homes

Upgrade Path / Compatibility Matrix for 18.x Oracle Database

DBUA can upgrade only supported versions of direct upgrade.

Direct Upgrade to 18.x:

Requirements and Recommendation for source database

Requirements and Recommendations for Target database

Prerequisites for Preparing Oracle Home on Windows

Pre Upgrade

 
FILE|TERMINAL – Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then defaultr is FILE.
TEXT – Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR – Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

Check for Invalid Objects / Components

If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps:

Run utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.

From 12.1 home,execute the utlrp.sql against the container and all the pluggable databases.

using -b utlrp, the log file utlrp0.log is generated as the script is run. The log file provides results of the recompile.

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS   procedure to gather these statistics. For example, enter the following SQL statement:

Above command gather dictionary statistics for all PDBs in a container database.

you can use the below command to gather statistics against a particular PDB.

For PDB1 pluggable database :

For CDB$ROOT container:

Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.

 It will verify against all the pluggable databases or below query can be used:

Check of TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 12c release 2 (18.x) is version 26.

Case 1 Timezone version of source database is lower or equal 26.

If the source database is using a timezone file lower than version 26 then there is no DST patch to apply in source oracle home or target 12cR2 home.

Case 2 Timezone version of source database is higher than 26.

If the source database uses a Timezone version higher than 26 then BEFORE the upgrade you MUST patch the target 12cR2 $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.

Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Purging Recycle Bin before upgrade

Below command can be used to purge from all the pdbs:

we can purge the recycle bin via DBUA.

Databases That Use Oracle Label Security and Oracle Database Vault

Audit Table Preupgrade and Archive Requirements

For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade.

If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move.

Running the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database.

Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault
If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts – olspreupgrade.sql, emremove.sql, catnoamd.sql

Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.

Run the following statement:

Copying Transparent Encryption Oracle Wallets

If you use Oracle wallet with Transparent Data Encryption (TDE), and you use Database Upgrade Assistant (DBUA) to upgrade the database, then copy the sqlnet.ora and wallet file to the new 18.x Oracle home.

You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
3. Open the Oracle wallet in mount.

Check the accounts use Case-Insensitive password version

Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.

About Password Case Sensitivity

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.

Invoke DBUA

DBUA ( Step 1 of 10 )

Select the database to be upgrade , as there is only 1 database in example , it is auto selected — ORCL

DBUA ( Step 2 of 10 )

We can prioritize the upgrade of the pluggable database. in the given screen, the ORCL container database is having three pdbs : pdb1, pdb2, pdb3. The priority of pdb1 is set to 1 whereas for pdb2,pdb3 it is 2. Based on this selection DBUA will upgrade pdb1 first followed by pdb2, pdb3.

DBUA ( Step 3 of 10 )

Once the selection is done, DBUA will perform the pre-upgrade steps. It will execute the preupgrade scripts against all the pdbs and show errors / warnings. Choose “Show All Container” for the reported errors. If the errors are fixable than click on “Fix & Check Again”. If there are any errors which can not be fixed by DBUA then manually fix and verify that there are no errors in the prerequisite checks page.

DBUA ( Step 4 of 10 )

Once the preupgrade warnings / errors has been addressed, In step 4, we can choose options like Enable Parallel upgrade, Recompile Invalid Objects in post-upgrade, Timezone upgrade for all the pdbs (The timezone version on 18.x is 26), Gather statistics for all the pdbs.

DBUA ( Step 5 of 10 )

In the given screen, there are recovery options available. you can choose to Create a Guaranteed Restore Point or RMAN backup in case of failure of upgrade.

DBUA ( Step 6 of 10 )

We can configure new listener or upgrade the existing “LISTENER_ORCL” listener which is running from 12.1.0.2 home to Target 18.x home.

DBUA ( Step 7 of 10 )

This screen is to configure EM express or register the upgraded database with EM Cloud control.

DBUA ( Step 8 of 10 )

DBUA will show the summary of what actions it will perform

DBUA ( Step 9 of 10 )

The DBUA will start the upgrade process of ORCL database. Once the upgrade starts, DBUA will first perform the upgrade of CDB$ROOT container. it will perform the pre-upgrade steps and then it will start upgrading Oracle Server,XML component followed by other components except Oracle APEX ( which is not upgraded as a part of Database Upgrade , you need to do it separately )

Once the upgrade completed for CDB$ROOT, database will be restarted and DBUA will perform the post upgrade steps like running catuppst.sql, configuring listener for 18.x home, recompile invalid objects and upgrading timezone,configuring EM Express.

Once the CDB$ROOT gets upgraded successfully, DBUA will use the priority list where the PDB1 will be upgraded first. The PDB1 priority is set to 1.So based on the priority, DBUA will consider priority list “P1” for pluggable database PDB$SEED and PDB1 to be upgraded first. In below screen, DBUA will parallely perform the upgrade for PDB$SEED and PDB1. It will perform the upgrade of components viz Oracle Server, XDB, Workspace Manager component for both PDB$SEED, PDB1 pdbs.

Note that the CDB$ROOT and PDB$SEED has highest priority.

After completing the upgrade of PDB$SEED and PDB1, DBUA will start the post upgrade process for “P1” priority list for PDB$SEED and PDB1 pdbs. DBUA will bounce back both the pdbs PDB$SEED and PDB1 for post upgrade steps.

Once the upgrade of P1 list gets completed, DBUA will start the upgrade of P2 priority list which contains PDB2 and PDB3. In the below screen we can see that the PDB2 and PDB3 pdbs are getting upgraded parallely.Followed by the post upgrade steps for both the PDB’s respectively.

DBUA ( Step 10 of 10 )

The final screen will show the result of the container database upgraded to 18.x. It will show the list of pluggable databases upgraded. It will show the detailed summary for the time taken by DBUA to complete the upgrade of individual PDBs

It will show the timezone upgrade details for CDB$ROOT and all the pluggable databases.

Post Upgrade

Execute dbupgdiag.sql script against the CDB$ROOT container and all the pluggable databases PDB1, PDB2, PDB3 to verify the status of objects and components . If there are invalid objects then run utlrp.sql to recompile the invalid objects as follows:

Oracle 18c – Complete checklist for Manual Upgrade for Multitenant Architecture Oracle Databases from 12.1.x.x to 18.x.x.x

PURPOSE

This document is created for use as a guideline and checklist when upgrading 12cR1, 12cR2 multitenant database to higher version 18.x.x.x

SCOPE

Database Administrators, Support

DETAILS

Introduction

Multitenant architecture database can either be upgraded in parallel, or in sequence.

Starting with Oracle Database 12c release 1 (12.1), Oracle provides multitenant architecture, which enables the creation and administration of pluggable databases (PDBs) in a container database (CDB). You can upgrade multitenant architecture systems using either Oracle Database Upgrade Assistant (DBUA), or using the Parallel Upgrade Utility to carry out manual upgrades.

There are two techniques for upgrading Oracle Databases using the multitenant architecture:

• In parallel. With this technique, you carry out one upgrade operation that upgrades the CDB, and then upgrades the PDBs in parallel.
• Sequentially. With this technique, you install a new release CDB, prepare and unplug PDBs from the earlier release CDB, plug the PDBs into a later release CDB, and then complete the upgrade for each PDB.

Upgrading Oracle Multitenant In Parallel

With the In Parallel technique, you first upgrade CDB$ROOT using the Parallel Upgrade Utility (catctl.pl), using parameters to set the degree of parallel processing and availability:

The -n parameter defines how many parallel processes run the upgrade, up to 8.

The -M parameter determines if the CDB$ROOT stays in UPGRADE mode through the entire upgrade, or becomes available for access after the CDB upgrade is complete. If you do not run the upgrade with the -M parameter, then when the CDB$ROOT upgrade is complete, PDBs then become available for access as soon as each PDB completes its upgrade. If you run the upgrade with the -M parameter, then CDB$ROOT stays in UPGRADE mode, and PDBs do not become available until upgrade of all PDBs is complete.

Upgrading Oracle Multitenant In Sequence

With the In Sequence technique, you install the new release multitenant architecture CDB. Next, in the earlier release multitenant architecture CDB, you issue SQL commands to run preupgrade scripts to prepare one or more PDBs to upgrade, and shut them down. You then unplug PDBs, plug them into the new release multenant architecture CDB, and complete the upgrade sequentially for each PDB.

Manually Upgrading a Multitenant Container Oracle Database (CDB)

Step 1: Recommendations for source CDB / PDB database

• Make sure there are no INVALID objects/components in the source database.  Verify the database health by executing dbupgdiag.sql (refer Note 556610.1 to download this script).
  • If there are any INVALID objects are found execute $ORACLE_HOME/rdbms/admin/utlrp.sql multiple times to validate them

• Take a cold or hot backup of the source database
• Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.

Check the status of PDB. It should be in open with Read write mode

These commands will also make sure the Multitenant Option is enabled.

Example:

• Execute Preupgrade scripts before upgrade and review the preupgrade log for any issues.
• Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
• Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Step2: Requirements and recommendations for target ORACLE_HOME

• Verify the whether your operating system is certified for 18.1. Click here to launch certification portal
• Install 18.1.0.0, verify there are no installation related issues.
• Download and install latest RU / RUR if any
• Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target homeReview patch recommendations as given in the article “Patches to apply before upgrading Oracle GI and DB to 18c (Doc ID 2414935.1)”
  • Remove any _ (underscore) parameter, obsolete and deprecated parameters in pfile
  • Note min value of COMPATIBLE parameter to upgrade 18.1 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
• Review patch recommendations as given in the article “Patches to apply before upgrading Oracle GI and DB to 18c (Doc ID 2414935.1)”
• Apply patch 29213893 on target ORACLE_HOME to avoid ORA-01422 error – refer: Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS (Doc ID 2525596.1)

Step 3: Pre-Upgrade Steps

1. Open all PDBs

SQL> alter pluggable database all open;

2. Run the Pre-Upgrade Information Tool (preupgrade.jar), using the following syntax:

Use space-delimitation for lists. On Linux and UNIX, define the list by placing the list inside single quotes: ‘. On Windows systems, define the list by placing the list inside double quotes “.

Linux and UNIX:

Windows:

Note: You must use Java 1.5 or later to run the Pre-Upgrade Information too.
If no parameters are passed to the preupgrade.jar , it is run against all the container databases ( CDB and PDB’s ).

3. Review any generated fixup scripts and log files.

If ORACLE_BASE is defined, then the fixup files are placed in

Linux and UNIX:

Windows:

If ORACLE_BASE is not defined, then fixup files are placed in one of the following paths:

Linux and UNIX:

Windows:

4. Execute the preupgrade_fixups script, or individual PDB scripts.\

Note: fixup script for PDB$SEED is preupgrade_fixups_pdb_seed.sql

5. For Oracle RAC database set CLUSTER_DATABASE to false

6. Shut down the database in the old Oracle home.

For example, where db_unique_name is your database name:

7. Copy the PFILE or SPFILE from the old Oracle home to the new Oracle home

Step 4: Upgrade Steps

8. Connect with SQL*Plus:

Note: Make sure that you have set ORACLE_HOME, PATH which points to target version

9. Bring the CDB$ROOT instance into upgrade mode:

10. Bring all PDBs into upgrade mode:

11. Check the status of PDBs to confirm that they are ready to upgrade:

For all PDBs, ensure that the status is set to MIGRATE.
12. Exit from SQL*Plus, and change directory to the new Oracle home $ORACLE_HOME/rdbms/admin:

13. Start the upgrade using the Parallel Upgrade Utility (catctl.pl, using the shell command dbupgrade), where -d specifies the location of the directory:

If you do not specify any parameters, then the Parallel Upgrade Utility runs the upgrade in parallel on the number of PDBs equivalent to the number of CPUs divided by 2. On a server with 64 CPUs, 64 divided by 2 equals 32 PDBs upgraded in parallel, carried out with two SQL processors for each PDB. CDB$ROOT remains in NORMAL mode for the duration of the upgrade.

14. Review the upg_summary.log to confirm that the upgrade was successful, and if necessary, review other logs.

Step 5: Post-Upgrade Steps

15. Open all PDBs, so that you can recompile the databases:

16. Exit from SQL*Plus, and change directory to the new Oracle home path $ORACLE_HOME/rdbms/admin:

17. Run the catcon.pl script and the postupgrade_fixups.sql script that is supplied with the new release Oracle Database.

The following example shows the command strings for running catcon.pl, using the -n parameter to specify one parallel processor for each PDB, using the -d parameter to specify the path where the preupgrade script that you want to run is located, using the -l parameter to specify the location where you want the scripts to place log files, and using the -b flag to specify the log file prefixes for the postupgrade_fixups.sql script:

18. Run postupgrade_fixups.sql.

19. Run utlu122s.sql to verify that there are no upgrade issues.

log file utlu122s0.log is generated.

20. For Oracle RAC environments set the initialization parameter value for CLUSTER_DATABASE to TRUE, and to start the Oracle RAC database

Upgrading PDBs Using the Parallel Upgrade Utility with Priority Lists

Oracle Database 12.2 and later releases, you can upgrade PDBs using a priority list to upgrade a set of PDBs ahead of other PDBs, and we can modify that upgrade priority.  This list enables to group and upgrade PDBs based on the priority.  A priority list is a text file with comma-delimited lists defining the order of upgrade priority, and the PDBs in each numeric priority group. We can execute the Parallel Upgrade Utility (dbupgrade, dbupgrade.cmd, or catctl.pl) using the -L option to run the upgrade using a priority list, and to call that list as the upgrade runs.

Format for creating priority list

Note:

• Create priority lists using a plain text editor, such as vi on Linux and UNIX, or Notepad on Windows.
• Here numeral represents the priority for the PDB.

Syntax to run the Parallel Upgrade utility using a priority list:

After upgrade is complete PDB priority states are maintained in the CDB.  If we upgrade the database to next release, it uses the same priority.  To change this we have to execute

PDB Upgrades Using Priority Lists, Inclusion Lists, and Exclusion Lists

The following terms designate types of upgrade list processing:

Example of Inclusion List

With following priority list

1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB2,CDB1_PDB4
2.CDB1_PDB5

The upgrade order is carried out in the following sequence:

CDB1_PDB2, CDB1_PDB4
CDB1_PDB5

The Parallel Upgrade Utility processes only the PDBs that are in the inclusion list, and in the order of the priority list.

Example of Exclusion List

With following priority list

1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB1,CDB1_PDB2
2,CDB1_PDB3,CDB1_PDB4
3,CDB1_PDB5

Because CDB$ROOT is excluded, the priority processing shifts. The upgrade order is carried out in the following sequence:

PDB$SEED, CDB_PDB1
CDB_PDB2, CDB_PDB3
CDB1_PDB4, CDB1_PDB5

Exclusion List using CATCTL_LISTONLY

With following priority list

1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB1,CDB1_PDB2
2,CDB1_PDB3
3,CDB1_PDB5

With the CATCTL_LISTONLY option, PDBs that are not in the priority list are excluded from the upgrade.  Specifying the keyword CATCTL_LISTONLY in the exclusion list turns the priority list into an inclusion priority list. Only PDBs in the list are processed.

Upgrading Pluggable Databases (PDBs) In Parallel

Using the In-Parallel technique, you can upgrade the CDB, and then immediately upgrade PDBs using parallel SQL processors.

Container databases (CDBs) can contain zero, one, or more pluggable databases (PDBs). By default, the Parallel Upgrade Utility (catctl.pl) updates the CDB and all of its PDBs in the same upgrade window. The Parallel Upgrade Utility uses the number of computer processing units (CPUs) to determine the maximum number of PDBs that are upgraded simultaneously. The number of PDBs that are upgraded in parallel is determined by dividing the parallel SQL process count (-n option) by the parallel PDB SQL process count (-N option).

Steps to upgrade PDBs in parallel

1. Take necessary backup
2. Execute pre-upgrade tool, fix up any issues found, make sure there are no INVALID objects/components
3. un the Parallel Upgrade Utility. In sequence, the following upgrades are carried out:

a. Cycle 1: CDB$ROOT is upgraded to the new Oracle release
b. Cycle 2 to Cycle x: PDB$SEED and PDBs are upgraded in parallel, with the number of cycles of upgrades as determined by the parameter settings you specify with -n.
4. Complete post-upgrade steps.

Upgrading Multitenant Container Databases In Parallel

Use this technique to upgrade CDB$ROOT, PDB$SEED, and all PDBS in the CDB in one upgrade operation.
Oracle recommends that you use this approach if you can schedule downtime, because it provides a direct procedure for upgrades and simplicity of maintenance. Using this procedure upgrades in parallel all the PDBs in the multitenant architecture container database, depending on your server’s available processors (CPUs).

1. Ensure that you have a proper backup strategy in place.
2. Open all PDBs.
For example:

3. Run the Pre-Upgrade Information Tool (preupgrade.jar), using the following syntax:

4. Review any generated fixup scripts and log files.

5. Run the preupgrade_fixups script, or individual PDB scripts.

6. For Oracle RAC databases, set the cluster database initialization parameter to false:

7. Shut down the database in the old Oracle home.

8. Copy the PFILE or SPFILE from the old Oracle home to the new Oracle home
9. Connect with SQL*Plus:

10. Bring the CDB$ROOT instance into upgrade mode:

11. Bring all PDBs into upgrade mode:

12. Check the status of PDBs to confirm that they are ready to upgrade:

For all PDBs, ensure that the status is set to MIGRATE.

13. Exit from SQL*Plus, and change directory to the new Oracle home $ORACLE_HOME/rdbms/admin:

14. Start the upgrade using the Parallel Upgrade Utility (catctl.pl, using the shell command dbupgrade), where -d specifies the location of the directory:

15. Review the upg_summary.log to conf8irm that the upgrade was successful, and if necessary, review other logs.
16. Open all PDBs, so that you can recompile the databases:

17. Exit from SQL*Plus, and change directory to the new Oracle home path $ORACLE_HOME/rdbms/admin:

18. Run the catcon.pl script and the postupgrade_fixups.sql script that is supplied with the new release Oracle Database.

The following example shows the command strings for running catcon.pl, using the -n parameter to specify one parallel processor for each PDB, using the -d parameter to specify the path where the preupgrade script that you want to run is located, using the -l parameter to specify the location where you want the scripts to place log files, and using the -b flag to specify the log file prefixes for the postupgrade_fixups.sql script:

19. Run postupgrade_fixups.sql.

20. Run utlu122s.sql to verify that there are no upgrade issues.

When you use catcon.pl to run utlu122s.sql, the log file utlu122s0.log is generated.

21. 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: