Posts from the ‘18c’ Category

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:

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

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 18c Release

SCOPE

Database Administrators, Support

DETAILS

Step 1: Upgrade Path for 18.1 Oracle database

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

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 18c. Use the instructions in this book, Oracle Database Upgrade Guide, including running the preupgrade script for 18.1.

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.3).
• 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.3 or 11.2.0.4. Then upgrade from release 11.2 to 18c

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 12c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 12c to define the data security roles again.
• If you upgrade an 11g database to Oracle Database 12c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 12c database.
• Timezone should less than or equal to target database timezone version.
• IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB
• No INVALID object / Components in Source
• If you are installing 64-bit Oracle Database software, and your existing Oracle Database is a 32-bit Oracle Database installation, then your existing Oracle Database is automatically converted to 64-bit during the upgrade to the new Oracle Database release.
• 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.
• Execute utlrp.sql multiple times and verify there are no INVALID objects.

Step3: 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 home
  • 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 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

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:

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 18.1 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.
• 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.

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 (18.1) 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:

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.

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>

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

Preupgrade fixup script

Execute Preupgrade fixup scripts preupgrade_fixups.sql

Dependencies on Network Utility Packages

Execute the following query

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

Step 6: Upgrade Database to 18c

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, utlu122s.sql and review the upgrade spool log file.  You run the Post-Upgrade Status Tool in the environment of the new release.

Verify the upgrade log whether catuppst.sql (under $ORACLE_HOME/cfgtoollogs/<SID>/upgrade<timestamp>/) has been executed or not.  If not, execute it manually from new ORACLE_HOME, located at $ORACLE_HOME/rdbms/admin directory.  If there are no errors during upgrade, then you can skip this step, since upgrade script will execute this by default.

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

Check the state of the Oracle Data Dictionary for diagnosing upgrades and migrations.  The dbupgdiag.sql script collects upgrade and migration diagnostic information about the current state of the data dictionary.  You can run the script in SQL*Plus on the upgraded database as the SYS user.  Refer Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.  After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database.  Run srvctl for Oracle Database 18c to upgrade the database. For example:

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.

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.

Follow the procedure in Oracle documentation under “Steps to Upgrade Time Zone File and Timestamp with Time Zone Data” and Note 1509653.1 “Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST”

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.