Manually Upgrade Oracle Database from 11gR2 to 12cR2

This article provides an overview of upgrading an existing non-CDB database from Oracle 11.2.0.4 to Oracle 12.2.0.1. Upgrades can be very complicated, so this is highly recommended to read the oracle provided manual and test thoroughly before considering an upgrade of a production environment.

1. Upgrade Path for 12.2 Oracle database:

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

2.1. Take a cold or hot back up of the source database:

During database upgradation cutover window, we can take backup. Details on session 5.6.

2.2. Disable any custom triggers that would get executed before / after DDL:

No Custom Trigger.

2.3. Timezone should less than or equal to target database timezone version:

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        14

SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SET LINESIZE 150
SQL>
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'; 

PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_UPGRADE_STATE                                  NONE
DST_PRIMARY_TT_VERSION                             14
DST_SECONDARY_TT_VERSION                           0

2.4. Upgrade APEX in the source DB first before upgrading DB:

If APEX is installed on your database then it is recommended to upgrade APEX in the source DB first before upgrading DB.

3. Requirements and recommendations for target database:

  • Install Oracle Database 12.2.0.1 (Software Only)
  • Install 12.2.0.1, verify there are no installation related issues.
  • Download and install latest PSU if any
  • Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
  • Remove any _parameter, obsolete and deprecated parameters in pfile
  • Note min value of COMPATIBLE parameter to upgrade 12.2 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater

3.1. Perform Oracle Installation Prerequisites:

[root@dbupgrade ~]# yum install oracle-database-server-12cR2-preinstall -y
Loaded plugins: langpacks
ol7_UEKR3                                                                                           | 2.5 kB  00:00:00
ol7_latest                                                                                          | 2.7 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-server-12cR2-preinstall.x86_64 0:1.0-5.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================
 Package                                             Arch               Version               Repository              Size
===========================================================================================================================
Installing:
 oracle-database-server-12cR2-preinstall             x86_64             1.0-5.el7             ol7_latest              19 k

Transaction Summary
===========================================================================================================================
Install  1 Package

Total download size: 19 k
Installed size: 55 k
Downloading packages:
oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64.rpm                                        |  19 kB  00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64                                                1/1
  Verifying  : oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64                                                1/1

Installed:
  oracle-database-server-12cR2-preinstall.x86_64 0:1.0-5.el7

Complete!

3.2. Install Oracle Database 12.2.0.1 (Software Only):

./runInstaller -ignorePrereq -waitforcompletion -silent  \
    -responseFile /u01/software/database/response/db_install.rsp               \
    oracle.install.option=INSTALL_DB_SWONLY                                    \
    ORACLE_HOSTNAME=dbupgrade.localdomain                                         \
    UNIX_GROUP_NAME=oinstall                                                   \
    INVENTORY_LOCATION=/u01/app/oraInventory                                  \
    SELECTED_LANGUAGES=en                             		                   \
    ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1                      \
    ORACLE_BASE=/u01/app/oracle                                                 \
    oracle.install.db.InstallEdition=EE                                        \
    oracle.install.db.OSDBA_GROUP=dba                                          \
    oracle.install.db.OSBACKUPDBA_GROUP=dba                                    \
    oracle.install.db.OSDGDBA_GROUP=dba                                        \
    oracle.install.db.OSKMDBA_GROUP=dba                                        \
    oracle.install.db.OSRACDBA_GROUP=dba                                       \
    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false                                 \
    DECLINE_SECURITY_UPDATES=true

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 16889 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 6143 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-10-05_02-42-58PM. Please wait ...[WARNING] [INS-32016] The selected Oracle home contains directories or files.
   ACTION: To start with an empty Oracle home, either remove its contents or choose another location.
You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2020-10-05_02-42-58PM.log

The installation of Oracle Database 12c was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2020-10-05_02-42-58PM.log' for more details.

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/oracle/product/12.2.0.1/dbhome_1/root.sh



Successfully Setup Software.

3.3. Run root script:

[oracle@dbupgrade database]$ su - root
Password:
Last login: Sun Oct  4 23:23:22 EDT 2020 from 192.168.56.1 on pts/0
[root@dbupgrade ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@dbupgrade ~]# /u01/app/oracle/product/12.2.0.1/dbhome_1/root.sh
Check /u01/app/oracle/product/12.2.0.1/dbhome_1/install/root_dbupgrade.localdomain_2020-10-05_14-49-20-407901660.log for the output of root script

3.4. Install 12.2.0.1, verify there are no installation related issues:

Verified and No error.

3.5. Copy spfile or pfile from source database 11.2.0.4 to target database 12.2.0.1:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

[oracle@dbupgrade dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@dbupgrade dbs]$ ls -lrt
total 24
-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r-----. 1 oracle oinstall   24 Oct  4 15:56 lkORCL
-rw-r-----. 1 oracle oinstall 1536 Oct  4 15:56 orapworcl
-rw-r-----. 1 oracle oinstall  931 Oct  5 15:05 pfile_orcl.ora
-rw-rw----. 1 oracle oinstall 1544 Oct  5 15:10 hc_orcl.dat
-rw-r-----. 1 oracle oinstall 2560 Oct  5 15:10 spfileorcl.ora

[oracle@dbupgrade dbs]$ cp *ora /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs
[oracle@dbupgrade dbs]$ cp orapworcl /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs

3.6. COMPATIBLE Values (should be minimum 11.2 on source DB):

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0

4. Pre-upgrade Checks:

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

4.1 Download dbupgdiag.sql and execute on source database:

SQL> @dbupgdiag.sql

Enter location for Spooled output:

Enter value for 1: /home/oracle

06_Oct_2020_0113 .log

orcl_



                          *** Start of LogFile ***

  Oracle Database Upgrade Diagnostic Utility       10-06-2020 13:13:59

===============
Hostname
===============

dbupgrade.localdomain

===============
Database Name
===============

ORCL

===============
Database Uptime
===============

12:58 06-OCT-20

=================
Database Wordsize
=================

This is a 64-bit database

================
Software Version
================

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

=============
Compatibility
=============

Compatibility is set as 11.2.0.4.0

================
Archive Log Mode
================

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

================
Auditing Check
================
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
                                                 mp
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

================
Cluster Check
================

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#

===========================================
Tablespace and the owner of the aud$ table  ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
===========================================

OWNER        TABLESPACE_NAME
------------ ------------------------------
SYS          SYSTEM

============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================

         0

============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================

         0

==========================================
Oracle Label Security is installed or not
==========================================

Oracle Label Security is NOT installed at database level

================
Number of AQ Records in Message Queue Tables
================

SYS - ALERT_QT - 0
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_PROP_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_REMDB_JOBQTAB - 0
SYS - SCHEDULER_FILEWATCHER_QT - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
SYSMAN - MGMT_LOADER_QTABLE - 0
SYSMAN - MGMT_NOTIFY_INPUT_QTABLE - 0
SYSMAN - MGMT_NOTIFY_QTABLE - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_1 - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_2 - 0
SYSMAN - MGMT_TASK_QTABLE - 27
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0

================
Time Zone version
================


        14

================
Local Listener
================

================
Default and Temporary Tablespaces By User
================
USERNAME                     TEMPORARY_TABLESPACE   DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS                          TEMP                   SYSTEM
SYSTEM                       TEMP                   SYSTEM
OUTLN                        TEMP                   SYSTEM
MGMT_VIEW                    TEMP                   SYSTEM
FLOWS_FILES                  TEMP                   SYSAUX
MDSYS                        TEMP                   SYSAUX
ORDSYS                       TEMP                   SYSAUX
EXFSYS                       TEMP                   SYSAUX
DBSNMP                       TEMP                   SYSAUX
WMSYS                        TEMP                   SYSAUX
APPQOSSYS                    TEMP                   SYSAUX
APEX_030200                  TEMP                   SYSAUX
OWBSYS_AUDIT                 TEMP                   SYSAUX
ORDDATA                      TEMP                   SYSAUX
CTXSYS                       TEMP                   SYSAUX
ANONYMOUS                    TEMP                   SYSAUX
SYSMAN                       TEMP                   SYSAUX
XDB                          TEMP                   SYSAUX
ORDPLUGINS                   TEMP                   SYSAUX
OWBSYS                       TEMP                   SYSAUX
SI_INFORMTN_SCHEMA           TEMP                   SYSAUX
OLAPSYS                      TEMP                   SYSAUX
SCOTT                        TEMP                   USERS
ORACLE_OCM                   TEMP                   USERS
XS$NULL                      TEMP                   USERS
MDDATA                       TEMP                   USERS
DIP                          TEMP                   USERS
APEX_PUBLIC_USER             TEMP                   USERS
SPATIAL_CSW_ADMIN_USR        TEMP                   USERS
SPATIAL_WFS_ADMIN_USR        TEMP                   USERS


================
Component Status
================

Comp ID Component                          Status    Version        Org_Version    Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
AMD     OLAP Catalog                       VALID     11.2.0.4.0
APEX    Oracle Application Express         VALID     3.2.1.00.12
APS     OLAP Analytic Workspace            VALID     11.2.0.4.0
CATALOG Oracle Database Catalog Views      VALID     11.2.0.4.0
CATJAVA Oracle Database Java Packages      VALID     11.2.0.4.0
CATPROC Oracle Database Packages and Types VALID     11.2.0.4.0
CONTEXT Oracle Text                        VALID     11.2.0.4.0
EM      Oracle Enterprise Manager          VALID     11.2.0.4.0
EXF     Oracle Expression Filter           VALID     11.2.0.4.0
JAVAVM  JServer JAVA Virtual Machine       VALID     11.2.0.4.0
ORDIM   Oracle Multimedia                  VALID     11.2.0.4.0
OWB     OWB                                VALID     11.2.0.4.0
OWM     Oracle Workspace Manager           VALID     11.2.0.4.0
RUL     Oracle Rules Manager               VALID     11.2.0.4.0
SDO     Spatial                            VALID     11.2.0.4.0
XDB     Oracle XML Database                VALID     11.2.0.4.0
XML     Oracle XDK                         VALID     11.2.0.4.0
XOQ     Oracle OLAP API                    VALID     11.2.0.4.0



======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================


Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects

DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#


no rows selected


================================
List of Invalid Database Objects
================================


Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects

DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#


no rows selected


======================================================
Count of Invalids by Schema
======================================================

==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================

DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC>               Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC>              OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#


Metadata Initial DB Creation Info
-------- -----------------------------------
B047     Database was created as 64-bit

===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================

Counting duplicate objects ....


  COUNT(1)
----------
         0

=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================

Querying duplicate objects ....


DOC>
DOC>################################################################################
DOC>Below are expected and required duplicates objects and OMITTED in the report .
DOC>
DOC>Without replication installed:
DOC>INDEX           AQ$_SCHEDULES_PRIMARY
DOC>TABLE           AQ$_SCHEDULES
DOC>
DOC>If replication is installed by running catrep.sql:
DOC>INDEX           AQ$_SCHEDULES_PRIMARY
DOC>PACKAGE         DBMS_REPCAT_AUTH
DOC>PACKAGE BODY        DBMS_REPCAT_AUTH
DOC>TABLE           AQ$_SCHEDULES
DOC>
DOC>If any objects found please follow below article.
DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC>Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#

========================
Password protected roles
========================

DOC>
DOC>################################################################################
DOC>
DOC> In version 11.2 password protected roles are no longer enabled by default so if
DOC> an application relies on such roles being enabled by default and no action is
DOC> performed to allow the user to enter the password with the set role command, it
DOC> is recommended to remove the password from those roles (to allow for existing
DOC> privileges to remain available). For more information see:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
DOC>#

Querying for password protected roles ....


Password protected Role        Assigned by default to user
------------------------------ ------------------------------
OWB$CLIENT                     OWBSYS

================
JVM Verification
================


================================================
Checking Existence of Java-Based Users and Roles
================================================

DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found, it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#


User Existence
---------------------------
No Java Based Users

DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#


Role
------------------------------
There are 6 JAVA related roles

Roles


ROLE
------------------------------
JAVA_DEPLOY
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN

=========================================
List of Invalid Java Objects owned by SYS
=========================================

There are no SYS owned invalid JAVA objects

DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#


no rows selected


DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC>   select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC>   *
DOC>   ERROR at line 1:
DOC>   ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#


JAVAVM TESTING
---------------
foo

===================================
Oracle Multimedia/InterMedia status
===================================

.
Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.4.0 and status: VALID
.
Checking for installed Database Schemas...
ORDSYS user exists.
ORDPLUGINS user exists.
MDSYS user exists.
SI_INFORMTN_SCHEMA user exists.
ORDDATA user exists.
.
Checking for Prerequisite Components...
JAVAVM installed and listed as valid
XDK installed and listed as valid
XDB installed and listed as valid
Validating Oracle Multimedia/interMedia...(no output if component status is valid)

PL/SQL procedure successfully completed.


                            *** End of LogFile ***



Upload db_upg_diag_orcl_06_Oct_2020_0113.log from "/home/oracle" directory

5. Preupgrade Step:

5.1 Run Preupgrade Script:

[oracle@dbupgrade dbs]$ export ORACLE_11G_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@dbupgrade dbs]$ export ORACLE_12C_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
[oracle@dbupgrade dbs]$
[oracle@dbupgrade dbs]$ $ORACLE_11G_HOME/jdk/bin/java -jar $ORACLE_12C_HOME/rdbms/admin/preupgrade.jar FILE TERMINAL TEXT

Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  ORCL
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
         Compatible:  11.2.0.4.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Enterprise Manager Repository   [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Expression Filter                      [to be upgraded]  VALID
  Rule Manager                           [to be upgraded]  VALID
  Oracle Application Express             [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID


==============
BEFORE UPGRADE
==============

  Run <preupgradeLogDirPath>/preupgrade_fixups.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             490 MB  ENABLED      1404 MB  None
     SYSTEM                             740 MB  ENABLED      1245 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                            30 MB  ENABLED       400 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.




   + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

     Parameter                         12.2.0.1.0 minimum
     ---------                         ------------------
     processes                                        300

  RECOMMENDED ACTIONS
  ===================
   + Remove the EM repository.

     - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
     12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

     Step 1: If database control is configured, stop EM Database Control,
     using the following command

       $> emctl stop dbconsole

     Step 2: Connect to the database using the SYS account AS SYSDBA

       SET ECHO ON;
       SET SERVEROUTPUT ON;
      @emremove.sql

     Without the set echo and serveroutput commands, you will not be able to
     follow the progress of the script.

     The database has an Enterprise Manager Database Control repository.

     Starting with Oracle Database 12c, the local Enterprise Manager Database
     Control does not exist anymore. The repository will be removed from your
     database during the upgrade.  This step can be manually performed before
     the upgrade to reduce downtime.

   + Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
     $ORACLE_HOME/olap/admin/catnoamd.sql script.

     The OLAP Catalog component, AMD, exists in the database.

     Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
     desupported and will be automatically marked as OPTION OFF during the
     database upgrade if present. Oracle recommends removing OLAP Catalog
     (OLAP AMD) before database upgrade.

   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 11.2.0.4
     Oracle Database Performance Tuning Guide.

   + Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
     trigger or drop and re-create the trigger with a user that was granted
     directly with such. You can list those triggers using "SELECT OWNER,
     TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND
     OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
     PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')"

     There is one or more database triggers whose owner does not have the
     right privilege on the database.

     The creation of database triggers must be done by users granted with
     ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
     directly.

  INFORMATION ONLY
  ================
   + Consider upgrading APEX manually, before the database upgrade.

     The database contains APEX version 3.2.1.00.12 and will need to be
     upgraded to at least version 5.0.4.00.12.

     To reduce database upgrade time, you can upgrade APEX manually before
     the database upgrade.  Refer to My Oracle Support Note 1088970.1 for
     information on APEX installation upgrades.

=============
AFTER UPGRADE
=============

  Run <preupgradeLogDirPath>/postupgrade_fixups.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 14 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.

   + Gather statistics on fixed objects two weeks after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

     This recommendation is given for all preupgrade runs.

     Fixed object statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans.  Those
     statistics are specific to the Oracle Database release that generates
     them, and can be stale upon database upgrade.

  INFORMATION ONLY
  ================
   + Check the Oracle documentation for the identified components for their
     specific upgrade procedure.

     The database upgrade script will not upgrade the following Oracle
     components:  OLAP Catalog,OWB

     The Oracle database upgrade script upgrades most, but not all Oracle
     Database components that may be installed.  Some components that are not
     upgraded may have their own upgrade scripts, or they may be deprecated
     or obsolete.

Preupgrade generated files:
    /u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
    /u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

5.2 Gather Dictionary Statistics:

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

5.3. Compile Objects:

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2020-10-05 17:24:10

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2020-10-05 17:24:10

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

5.4. List of Invalid Objects:

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

5.5. Purge Recyclebin:

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

5.6 Execute preupgrade fixup script:

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2020-10-05 15:42:49

For Source Database:     ORCL
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
em_present                Failed  Manual fixup recommended.
amd_exists                Failed  Manual fixup recommended.
dictionary_stats          Passed  None
trgowner_no_admndbtrg     Failed  Manual fixup recommended.
apex_upgrade_msg          Failed  Manual fixup recommended.

PL/SQL procedure successfully completed.

5.7. Database Backup:

Highly recommended to take cold backup. If not possible due to database size and downtime, then take online database full backup using RMAN before starting actual upgradation, stop all application connection then take incremental or archive log backup.

RMAN> run
{
allocate channel ch01 type disk format '/u01/software/ch01_orcl_before_upgrade%U.rman' ;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel ch01;
}

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=39 device type=DISK


Starting backup at 06-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1053027873
input archived log thread=1 sequence=6 RECID=2 STAMP=1053027899
input archived log thread=1 sequence=7 RECID=3 STAMP=1053029450
channel ch01: starting piece 1 at 06-OCT-20
channel ch01: finished piece 1 at 06-OCT-20
piece handle=/u01/software/ch01_orcl_before_upgrade0bvc9v3c_1_1.rman tag=TAG20201006T145748 comment=N
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=127 STAMP=1053043069
input archived log thread=1 sequence=2 RECID=128 STAMP=1053089940
channel ch01: starting piece 1 at 06-OCT-20
channel ch01: finished piece 1 at 06-OCT-20
piece handle=/u01/software/ch01_orcl_before_upgrade0cvc9v3d_1_1.rman tag=TAG20201006T145748 comment=N
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=4 STAMP=1053030528
channel ch01: starting piece 1 at 06-OCT-20
channel ch01: finished piece 1 at 06-OCT-20
piece handle=/u01/software/ch01_orcl_before_upgrade0dvc9v3e_1_1.rman tag=TAG20201006T145748 comment=N
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=129 STAMP=1053097068
channel ch01: starting piece 1 at 06-OCT-20
channel ch01: finished piece 1 at 06-OCT-20
... ... ...
input archived log thread=1 sequence=128 RECID=125 STAMP=1053042455
channel ch01: starting piece 1 at 06-OCT-20
channel ch01: finished piece 1 at 06-OCT-20
piece handle=/u01/software/ch01_orcl_before_upgrade0gvc9v5r_1_1.rman tag=TAG20201006T145748 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
Finished backup at 06-OCT-20

Starting backup at 06-OCT-20
channel ch01: starting compressed full datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/orcl/orcl/system01.dbf
input datafile file number=00002 name=/u02/oradata/orcl/orcl/sysaux01.dbf
input datafile file number=00003 name=/u02/oradata/orcl/orcl/undotbs01.dbf
input datafile file number=00004 name=/u02/oradata/orcl/orcl/users01.dbf
channel ch01: starting piece 1 at 06-OCT-20
channel ch01: finished piece 1 at 06-OCT-20
piece handle=/u01/software/ch01_orcl_before_upgrade0hvc9v7t_1_1.rman tag=TAG20201006T150013 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:25
Finished backup at 06-OCT-20

Starting backup at 06-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=130 STAMP=1053097238
channel ch01: starting piece 1 at 06-OCT-20
channel ch01: finished piece 1 at 06-OCT-20
piece handle=/u01/software/ch01_orcl_before_upgrade0ivc9v8m_1_1.rman tag=TAG20201006T150038 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-20

Starting Control File and SPFILE Autobackup at 06-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_10_06/o1_mf_s_1053097239_hqshpqlq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-20

released channel: ch01

5.8. Create a Flashback Guaranteed Restore Point:

Flashback Guaranteed Restoration option will help you to restore your database from any point (if failed or need to roll backup the upgradation processes) to earlier point and it will simple to restore and will save time.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
ORCL      READ WRITE           ARCHIVELOG

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area                                            db_recovery_file_dest_size           big integer 4182M
recovery_parallelism                 integer     0

SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0

SQL> select * from V$restore_point;

no rows selected

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190

SQL>
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        05-OCT-20 07.56.01.000000000 PM

6. 6. Upgradation Task:

6.1. Set Environment Variable (Target Oracle Home -> 12.2.0.1) and startup DB with upgrade:

[oracle@dbupgrade ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
[oracle@dbupgrade ~]$ export PATH=$PATH:$ORACLE_HOME/bin

[oracle@dbupgrade ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 6 15:08:06 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2415919104 bytes
Fixed Size                  8623544 bytes
Variable Size             654314056 bytes
Database Buffers         1744830464 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS
--------- -------------------- --- ----------------- ------------
ORCL      READ WRITE           NO  12.2.0.1.0        OPEN MIGRATE

6.2. Run Database Upgrade Command:

6.2.1. Following two ways you can run the upgrade command:

# Regular upgrade command.
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
#You can alter the level of parallelism using the "-n" parameter
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql

# Shorthand command.
$ $ORACLE_HOME/bin/dbupgrade

6.2.2. Run dbupgrade:

[oracle@dbupgrade ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@dbupgrade admin]$ pwd
/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin
[oracle@dbupgrade admin]$ ls -l catctl.pl catupgrd.sql
-rw-r--r--. 1 oracle oinstall 333980 Dec 19  2016 catctl.pl
-rw-r--r--. 1 oracle oinstall   7357 Dec 21  2015 catupgrd.sql

[oracle@dbupgrade admin]$ cd /u01/app/oracle/product/12.2.0.1/dbhome_1/bin

[oracle@dbupgrade bin]$ ls -l dbupgrade
-rwxr-x---. 1 oracle oinstall 3005 Jan 26  2017 dbupgrade
[oracle@dbupgrade bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = 0
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1/dbhome_1]
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1/dbhome_1]

Analyzing file /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20201006152641]

catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20201006152641/catupgrd_catcon_6140.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20201006152641/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/cfgtoollogs/upgrade20201006152641/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 1
Database Name         = orcl
DataBase Version      = 11.2.0.4.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641/catupgrd_catcon_6140.lst]
catcon: See [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641]

Parallel SQL Process Count            = 4
Components in [orcl]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]

------------------------------------------------------
Phases [0-115]         Start Time:[2020_10_06 15:26:42]
------------------------------------------------------
***********   Executing Change Scripts   ***********

Serial   Phase #:0    [orcl] Files:1    Time: 47s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl] Files:5    Time: 27s
Restart  Phase #:2    [orcl] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [orcl] Files:19   Time: 13s
Restart  Phase #:4    [orcl] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [orcl] Files:6    Time: 10s
*****************   Catproc Start   ****************
Serial   Phase #:6    [orcl] Files:1    Time: 8s
*****************   Catproc Types   ****************
Serial   Phase #:7    [orcl] Files:2    Time: 7s
Restart  Phase #:8    [orcl] Files:1    Time: 1s
****************   Catproc Tables   ****************
Parallel Phase #:9    [orcl] Files:69   Time: 23s
Restart  Phase #:10   [orcl] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [orcl] Files:1    Time: 22s
Restart  Phase #:12   [orcl] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [orcl] Files:97   Time: 11s
Restart  Phase #:14   [orcl] Files:1    Time: 0s
Parallel Phase #:15   [orcl] Files:118  Time: 15s
Restart  Phase #:16   [orcl] Files:1    Time: 0s
Serial   Phase #:17   [orcl] Files:13   Time: 2s
Restart  Phase #:18   [orcl] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [orcl] Files:33   Time: 23s
Restart  Phase #:20   [orcl] Files:1    Time: 0s
Serial   Phase #:21   [orcl] Files:3    Time: 5s
Restart  Phase #:22   [orcl] Files:1    Time: 0s
Parallel Phase #:23   [orcl] Files:24   Time: 63s
Restart  Phase #:24   [orcl] Files:1    Time: 0s
Parallel Phase #:25   [orcl] Files:11   Time: 32s
Restart  Phase #:26   [orcl] Files:1    Time: 1s
Serial   Phase #:27   [orcl] Files:1    Time: 0s
Serial   Phase #:28   [orcl] Files:3    Time: 1s
Serial   Phase #:29   [orcl] Files:1    Time: 0s
Restart  Phase #:30   [orcl] Files:1    Time: 1s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [orcl] Files:1    Time: 0s
Restart  Phase #:32   [orcl] Files:1    Time: 0s
Serial   Phase #:34   [orcl] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [orcl] Files:283  Time: 11s
Serial   Phase #:36   [orcl] Files:1    Time: 0s
Restart  Phase #:37   [orcl] Files:1    Time: 0s
Serial   Phase #:38   [orcl] Files:1    Time: 3s
Restart  Phase #:39   [orcl] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [orcl] Files:3    Time: 31s
Restart  Phase #:41   [orcl] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [orcl] Files:13   Time: 44s
Restart  Phase #:43   [orcl] Files:1    Time: 1s
Parallel Phase #:44   [orcl] Files:12   Time: 12s
Restart  Phase #:45   [orcl] Files:1    Time: 0s
Parallel Phase #:46   [orcl] Files:2    Time: 1s
Restart  Phase #:47   [orcl] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [orcl] Files:1    Time: 2s
Restart  Phase #:49   [orcl] Files:1    Time: 1s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [orcl] Files:1    Time: 12s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [orcl] Files:1    Time: 0s
Restart  Phase #:52   [orcl] Files:1    Time: 0s
****************   Upgrading Java   ****************
Serial   Phase #:53   [orcl] Files:1    Time: 152s
Restart  Phase #:54   [orcl] Files:1    Time: 0s
*****************   Upgrading XDK   ****************
Serial   Phase #:55   [orcl] Files:1    Time: 28s
Restart  Phase #:56   [orcl] Files:1    Time: 0s
*********   Upgrading APS,OLS,DV,CONTEXT   *********
Serial   Phase #:57   [orcl] Files:1    Time: 34s
*****************   Upgrading XDB   ****************
Restart  Phase #:58   [orcl] Files:1    Time: 0s
Serial   Phase #:60   [orcl] Files:3    Time: 13s
Serial   Phase #:61   [orcl] Files:3    Time: 4s
Parallel Phase #:62   [orcl] Files:9    Time: 2s
Parallel Phase #:63   [orcl] Files:24   Time: 2s
Serial   Phase #:64   [orcl] Files:4    Time: 5s
Serial   Phase #:65   [orcl] Files:1    Time: 0s
Serial   Phase #:66   [orcl] Files:30   Time: 1s
Serial   Phase #:67   [orcl] Files:1    Time: 0s
Parallel Phase #:68   [orcl] Files:6    Time: 1s
Serial   Phase #:69   [orcl] Files:2    Time: 13s
Serial   Phase #:70   [orcl] Files:3    Time: 45s
Restart  Phase #:71   [orcl] Files:1    Time: 0s
*********   Upgrading CATJAVA,OWM,MGW,RAC   ********
Serial   Phase #:72   [orcl] Files:1    Time: 46s
****************   Upgrading ORDIM   ***************
Restart  Phase #:73   [orcl] Files:1    Time: 0s
Serial   Phase #:75   [orcl] Files:1    Time: 0s
Parallel Phase #:76   [orcl] Files:2    Time: 29s
Serial   Phase #:77   [orcl] Files:1    Time: 36s
Restart  Phase #:78   [orcl] Files:1    Time: 1s
Parallel Phase #:79   [orcl] Files:2    Time: 5s
Serial   Phase #:80   [orcl] Files:2    Time: 1s
*****************   Upgrading SDO   ****************
Restart  Phase #:81   [orcl] Files:1    Time: 0s
Serial   Phase #:83   [orcl] Files:1    Time: 17s
Serial   Phase #:84   [orcl] Files:1    Time: 1s
Restart  Phase #:85   [orcl] Files:1    Time: 0s
Serial   Phase #:86   [orcl] Files:1    Time: 17s
Restart  Phase #:87   [orcl] Files:1    Time: 0s
Parallel Phase #:88   [orcl] Files:3    Time: 74s
Restart  Phase #:89   [orcl] Files:1    Time: 1s
Serial   Phase #:90   [orcl] Files:1    Time: 2s
Restart  Phase #:91   [orcl] Files:1    Time: 0s
Serial   Phase #:92   [orcl] Files:1    Time: 1s
Restart  Phase #:93   [orcl] Files:1    Time: 1s
Parallel Phase #:94   [orcl] Files:4    Time: 63s
Restart  Phase #:95   [orcl] Files:1    Time: 0s
Serial   Phase #:96   [orcl] Files:1    Time: 1s
Restart  Phase #:97   [orcl] Files:1    Time: 0s
Serial   Phase #:98   [orcl] Files:2    Time: 30s
Restart  Phase #:99   [orcl] Files:1    Time: 0s
Serial   Phase #:100  [orcl] Files:1    Time: 0s
Restart  Phase #:101  [orcl] Files:1    Time: 0s
***********   Upgrading Misc. ODM, OLAP   **********
Serial   Phase #:102  [orcl] Files:1    Time: 15s
****************   Upgrading APEX   ****************
Restart  Phase #:103  [orcl] Files:1    Time: 0s
Serial   Phase #:104  [orcl] Files:1    Time: 399s
Restart  Phase #:105  [orcl] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:106  [orcl] Files:1    Time: 0s
*************   Final Upgrade scripts   ************
Serial   Phase #:107  [orcl] Files:1    Time: 125s
**********   End PDB Application Upgrade   *********
Serial   Phase #:108  [orcl] Files:1    Time: 0s
*******************   Migration   ******************
Serial   Phase #:109  [orcl] Files:1    Time: 32s
Serial   Phase #:110  [orcl] Files:1    Time: 0s
Serial   Phase #:111  [orcl] Files:1    Time: 37s
*****************   Post Upgrade   *****************
Serial   Phase #:112  [orcl] Files:1    Time: 71s
****************   Summary report   ****************
Serial   Phase #:113  [orcl] Files:1    Time: 1s
Serial   Phase #:114  [orcl] Files:1    Time: 0s
Serial   Phase #:115  [orcl] Files:1     Time: 24s

------------------------------------------------------
Phases [0-115]         End Time:[2020_10_06 15:56:02]
------------------------------------------------------

Grand Total Time: 1769s

 LOG FILES: (/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:29m:29s]

6.3. Upgrade Details:

Either you can review the log file generate by upgrade command or Execute Post-Upgrade Status Tool:

6.3.1. Log File: Last few lines like -> (/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641/upg_summary.log)

6.3.2. Execute Post-Upgrade Status Tool:

[oracle@dbupgrade admin]$ pwd
/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin
[oracle@dbupgrade admin]$ ls -lrt utlu122s.sql
-rw-r--r--. 1 oracle oinstall 1079 Aug  8  2016 utlu122s.sql
[oracle@dbupgrade admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 6 17:10:27 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> Select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS
--------- -------------------- --- ----------------- ------------
ORCL      READ WRITE           NO  12.2.0.1.0        OPEN

SQL> @/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/utlu122s.sql

Oracle Database 12.2 Post-Upgrade Status Tool           10-06-2020 17:10:53

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.2.0.1.0  00:07:05
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:02:31
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:00:37
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:07
OLAP Catalog                         OPTION OFF      11.2.0.4.0  00:00:00
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:10
Oracle XDK                             UPGRADED      12.2.0.1.0  00:00:27
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:25
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:01:25
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:07
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:01:11
Spatial                                UPGRADED      12.2.0.1.0  00:03:27
Oracle Application Express             UPGRADED     5.0.4.00.12  00:06:38
Final Actions                                                    00:02:37
Post Upgrade                                                     00:01:11

Total Upgrade Time: 00:28:14

Database time zone version is 14. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641/upg_summary.log

6.4. Catuppst.sql Execution Status:

Verify the upgrade log whether catuppst.sql has been executed or not.  If not, execute it manually from new ORACLE_HOME, located at $ORACLE_HOME/rdbms/admin directory

6.4.1. Log from upgradation log:

[oracle@dbupgrade upgrade20201006152641]$ pwd
/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/orcl/upgrade20201006152641
[oracle@dbupgrade upgrade20201006152641]$ ls -lrt
total 223036
-rw-r--r--. 1 oracle oinstall       519 Oct  6 15:26 catupgrd_catcon_6140.lst
-rw-r--r--. 1 oracle oinstall         0 Oct  6 15:51 catupgrd_datapatch_upgrade.err
-rw-r--r--. 1 oracle oinstall       602 Oct  6 15:51 catupgrd_datapatch_upgrade.log
-rw-r--r--. 1 oracle oinstall         0 Oct  6 15:54 catupgrd_datapatch_normal.err
-rw-r--r--. 1 oracle oinstall       602 Oct  6 15:54 catupgrd_datapatch_normal.log
-rw-r--r--. 1 oracle oinstall   5727579 Oct  6 15:55 catupgrd2.log
-rw-r--r--. 1 oracle oinstall   7500433 Oct  6 15:55 catupgrd1.log
-rw-r--r--. 1 oracle oinstall   6428016 Oct  6 15:55 catupgrd3.log
-rw-r--r--. 1 oracle oinstall      1563 Oct  6 15:56 upg_summary.log
-rw-r--r--. 1 oracle oinstall 208690571 Oct  6 15:56 catupgrd0.log
[oracle@dbupgrade upgrade20201006152641]$ cat catupgrd0.log | grep catuppst.sql
15:27:19 SQL> Rem                           for bug 17526621 to catuppst.sql
15:27:24 SQL> -- NOTE: move revoke select on cdb_keepsizes for project 47511 to catuppst.sql
15:33:40 SQL> Rem    maba        02/02/15 - remove create_base_view to catuppst.sql
15:53:17 SQL> Rem    traney      03/12/12 - bug 13719175: move stats to catuppst.sql
15:53:41 SQL> -- be done later in catuppst.sql, only if the _utlmmig_table_stats_gathering
15:53:41 SQL> Rem        This catalog script can run from utlmmig.sql or catuppst.sql.
15:53:41 SQL> Rem        FALSE it will be run from catuppst.sql. This script gathers
15:53:41 SQL> Rem    jerrede     04/17/12 - Moved from catuppst.sql
15:53:41  69                 'Startup database in normal mode and run catuppst.sql');
15:53:48 SQL> -- case where we are running in catuppst.sql
15:54:26   4  :catuppst_name := '?/rdbms/admin/catuppst.sql';
15:54:26 SQL> Rem $Header: rdbms/admin/catuppst.sql /main/52 2016/06/14 23:41:26 cmlim Exp $
15:54:26 SQL> Rem catuppst.sql
15:54:26 SQL> Rem        catuppst.sql - CATalog UPgrade PoST-upgrade actions
15:54:26 SQL> Rem BEGIN catuppst.sql
15:54:26 SQL> -- DBUA_TIMESTAMP: catuppst.sql begins
15:54:26 SQL> Rem        This catalog script can run from utlmmig.sql or catuppst.sql.
15:54:26 SQL> Rem        FALSE it will be run from catuppst.sql. This script gathers
15:54:26 SQL> Rem    jerrede     04/17/12 - Moved from catuppst.sql
15:54:26  69                 'Startup database in normal mode and run catuppst.sql');
15:54:26 SQL> -- case where we are running in catuppst.sql
15:54:37 SQL> Rem    SQL_CALLING_FILE: rdbms/admin/catuppst.sql
15:55:37 SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed
15:55:37 SQL> -- DBUA_TIMESTAMP: catuppst.sql finished
15:55:37 SQL> Rem END catuppst.sql
@catuppst.sql

6.4.2. Run catuppst.sql:

If we had no errors, the “catuppst.sql” script would have been run as part of the upgrade. Since we did have errors, we need to run it manually.

@$ORACLE_HOME/rdbms/admin/catuppst.sql

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql

Output of catuppst.sql:

SQL> Rem
SQL> Rem $Header: rdbms/admin/catuppst.sql /main/52 2016/06/14 23:41:26 cmlim Exp $
SQL> Rem
SQL> Rem catuppst.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2016, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catuppst.sql - CATalog UPgrade PoST-upgrade actions
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         This post-upgrade script performs remaining upgrade actions that
SQL> Rem         do not require that the database be open in UPGRADE mode.
SQL> Rem         Automatically apply the latest PSU.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         You must be connected AS SYSDBA to run this script.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cmlim       06/06/16 - bug 23215791: add more DBUA_TIMESTAMPS during db
SQL> Rem                           upgrades
SQL> Rem    anupkk      04/03/16 - Bug 22917286: Moved call to olstrig.sql to
SQL> Rem                           olsdbmig.sql
SQL> Rem    raeburns    02/29/16 - Bug 22820096: revert ALTER TYPE to default
SQL> Rem                           CASCADE
SQL> Rem    rmorant     02/11/16 - Bug22340563 add parallel hint
SQL> Rem    atomar      02/04/16 - move aq action to release specific script
SQL> Rem    raeburns    12/09/15 - Bug 22175911: add SERVEROUTPUT OFF after
SQL> Rem                           catuptabdata.sql
SQL> Rem    rmorant     11/27/15 - bug22271668 add append hint
SQL> Rem    welin       11/11/15 - Bug 21099929: 12.2 cleanup
SQL> Rem    nneeluru    09/14/15 - Add Java name translation for longer identifiers
SQL> Rem    raeburns    08/24/15 - use catuptabdata.sql instead of inline code
SQL> Rem    raeburns    06/05/15 - Bug 21322727: upgrade Oracle-maintained table data
SQL> Rem    rmorant     05/19/15 - Bug19651064 added upgrade actions
SQL> Rem    amadan      05/08/15 - Bug 21027329 remove AQ upgrade dequeue log
SQL> Rem    rpang       04/28/15 - Bug 20723336: remove network ACL check
SQL> Rem    jaeblee     03/09/15 - lrg 14235955: ignore ORA-65173 on revoke from
SQL> Rem                           cdb_keepsizes
SQL> Rem    ssubrama    02/12/15 - bug 20494207 sharded q flag during upgrade
SQL> Rem    maba        01/28/15 - fix bug 20184738
SQL> Rem    cderosa     07/03/14 - Gather table stats on logminer dictionary tables
SQL> Rem                           to initialize incremental mode.
SQL> Rem    wesmith     05/23/14 - Project 47511: data-bound collation: move fix
SQL> Rem                           for bug 17526621 from c1201000.sql
SQL> Rem    surman      05/19/14 - 17277459: Remove call to catbundle
SQL> Rem    jerrede     01/17/14 - Fix Bug 18071399 Add Post Upgrade Report Time
SQL> Rem    surman      05/31/13 - 16790144: Use @@
SQL> Rem    cmlim       05/15/13 - bug 16816410: add table name to errorlogging
SQL> Rem                           syntax
SQL> Rem    surman      03/19/13 - 16094163: Add catbundleapply.sql
SQL> Rem    cmlim       03/01/13 - bug 16306200: remove the workaround (added in
SQL> Rem                           txn in bug 16085743) that re-updated
SQL> Rem                           oracle-supplied bit in views owned by SYS after
SQL> Rem                           bootstrap.  Workaround not needed once the shared
SQL> Rem                           pool is flushed in catuposb.sql (bug 16306200).
SQL> Rem    jerrede     01/14/13 - XbranchMerge jerrede_bug-16097914 from
SQL> Rem                           st_rdbms_12.1.0.1
SQL> Rem    jerrede     01/11/13 - Move Removal of EXF/RUL to upgrade.
SQL> Rem                           LogMiner/Standyby can not deal with removing
SQL> Rem                           a component outside of upgrade.
SQL> Rem    sjanardh    01/10/13 - XbranchMerge maba_bug-14615619 from main
SQL> Rem    jerrede     12/19/12 - Bug#16025279 Add Event for Not Removing EXF/RUL
SQL> Rem                           Upgrade Components
SQL> Rem    surman      12/10/12 - XbranchMerge surman_bug-12876907 from main
SQL> Rem    maba        11/26/12 - fixed bug 14615619
SQL> Rem    jerrede     11/05/12 - Add Exadata Bundle support
SQL> Rem    cmlim       10/27/12 - bug 14258301 : gather fixed obj stats if none of
SQL> Rem                           the fixed object tables have had stats collected
SQL> Rem    mfallen     09/20/12 - bug 14390165: check if AWR data needs update
SQL> Rem    jerrede     10/23/12 - Unset _ORACLE_SCRIPT
SQL> Rem    jerrede     10/23/12 - Add Session Info
SQL> Rem    maba        09/13/12 - added create dequeue log for bug 14278722
SQL> Rem    jerrede     06/26/12 - Set event to optionally update required stats
SQL> Rem                           during upgrade
SQL> Rem    rpang       05/21/12 - Add network ACL migration status check
SQL> Rem    traney      05/09/12 - lrg 6949943: mask ORA-942s
SQL> Rem    jerrede     04/17/12 - Moved Mandatory Changes to catrequired.sql
SQL> Rem    traney      04/04/12 - lrg 6762280: drop DBMS_DDL_INTERNAL_LIB
SQL> Rem    traney      03/12/12 - bug 13719175: move post-utlmmig stats here
SQL> Rem    cdilling    12/13/11 - drop SYSMAN schema - removal of EM component for
SQL> Rem                           upgrade to 12.1
SQL> Rem    aramappa    06/22/11 - Always run olstrig.sql when OLS installed in DB
SQL> Rem    xbarr       04/28/11 - move DMSYS removal code to odmu112.sql
SQL> Rem    xbarr       10/25/10 - run dmsysrem.sql to drop DMSYS schema if exists
SQL> Rem    cdilling    07/21/10 - add call to catbundle.sql for bug 9925339
SQL> Rem    srtata      12/16/08 - run olstrig.sql when upgrading from prior to 10.2
SQL> Rem    srtata      10/15/08 - put back olstrig.sql as we found it cannot be run
SQL> Rem                           as part of upgrade
SQL> Rem    srtata      02/26/08 - move olstrig.sql to olsdbmig.sql
SQL> Rem    ushaft      02/05/07 - post upgrade for ADDM tasks.
SQL> Rem    cdilling    12/06/06 - add support for error logging
SQL> Rem    rburns      11/10/06 - post upgrade actions
SQL> Rem    rburns      11/10/06 - Created
SQL> Rem
SQL>
SQL> Rem =====================================================================
SQL> Rem Call Common session settings
SQL> Rem =====================================================================
SQL> @@catpses.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catpses.sql /main/4 2015/07/23 11:34:46 jerrede Exp $
SQL> Rem
SQL> Rem catpses.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2015, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catpses.sql - CATalog and CATProc SESsion script
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         This script initializes the session for running catalog
SQL> Rem         and/or catproc scripts
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         It is used as the session script for parallel processes
SQL> Rem         when catalog.sql and/or catproc.sql is run using multiprocesses
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catpses.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catpses.sql
SQL> Rem SQL_PHASE: CATPSES
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE: rdbms/admin/cdstrt.sql
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    jerrede     06/03/15 - Remove Session End we do not want to set
SQL> Rem                           ORACLE_SCRIPT to false for session files in the
SQL> Rem                           upgrade
SQL> Rem    surman      12/29/13 - 13922626: Update SQL metadata
SQL> Rem    jerrede     05/08/12 - Added session info for CDB.
SQL> Rem    rburns      10/23/06 - add session script
SQL> Rem    rburns      10/23/06 - Created
SQL> Rem
SQL>
SQL> @@?/rdbms/admin/sqlsessstart.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/1 2013/03/13 13:08:33 surman Exp $
SQL> Rem
SQL> Rem sqlsessstart.sql
SQL> Rem
SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         sqlsessstart.sql - SQL session start
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         Any commands which should be run at the start of all oracle
SQL> Rem         supplied scripts.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         See sqlsessend.sql for the corresponding end script.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts
SQL> Rem    surman      03/08/13 - Created
SQL> Rem
SQL>
SQL> alter session set "_ORACLE_SCRIPT" = true;

Session altered.

SQL>
SQL> Rem =====================================================================
SQL> Rem Assure CHAR semantics are not used in the dictionary
SQL> Rem =====================================================================
SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

SQL>
SQL>
SQL>
SQL> Rem *********************************************************************
SQL> Rem BEGIN catuppst.sql
SQL> Rem *********************************************************************
SQL> Rem Set identifier to POSTUP for errorlogging
SQL>
SQL> SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'POSTUP';
SQL>
SQL> -- DBUA_TIMESTAMP: db shutdown/startup is finished by now
SQL> SELECT dbms_registry_sys.time_stamp('DBRESTART') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART              2020-10-06 17:37:25
DBUA_TIMESTAMP DBRESTART     FINISHED 2020-10-06 17:37:25
DBUA_TIMESTAMP DBRESTART         NONE 2020-10-06 17:37:25


1 row selected.

SQL>
SQL> -- DBUA_TIMESTAMP: catuppst.sql begins
SQL> SELECT dbms_registry_sys.time_stamp_display('CATUPPST') AS timestamp FROM DUAL;

TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST       STARTED 2020-10-06 17:37:25

1 row selected.

SQL>
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_BGN') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN             2020-10-06 17:37:25
DBUA_TIMESTAMP POSTUP_BGN    FINISHED 2020-10-06 17:37:25
DBUA_TIMESTAMP POSTUP_BGN        NONE 2020-10-06 17:37:25


1 row selected.

SQL>
SQL>
SQL> Rem =======================================================================
SQL> Rem  Run Post Upgrade Operations
SQL> Rem =======================================================================
SQL>
SQL> @@catrequired.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catrequired.sql /main/1 2012/07/19 11:27:56 jerrede Exp $
SQL> Rem
SQL> Rem catrequired.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catrequired.sql - Catalog Mandatory Upgrade Script
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         This catalog script is a place holder
SQL> Rem         for other things that may be added in the future.
SQL> Rem         Right now it only calls catrequtlmg.sql.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         You must be connected AS SYSDBA to run this script.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem          jerrede       04/17/12 - Created
SQL> Rem
SQL>
SQL>
SQL> Rem *********************************************************************
SQL> Rem BEGIN catrequired.sql
SQL> Rem *********************************************************************
SQL>
SQL> Rem
SQL> Rem Display Start TimeStamp
SQL> Rem
SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_bgn') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN             2020-10-06 17:37:25
DBUA_TIMESTAMP CATREQ_BGN    FINISHED 2020-10-06 17:37:25
DBUA_TIMESTAMP CATREQ_BGN        NONE 2020-10-06 17:37:25


1 row selected.

SQL>
SQL>
SQL> Rem
SQL> Rem Post-utlmmig statistics gathering
SQL> Rem
SQL> @@catrequtlmg.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catrequtlmg.sql /main/5 2016/05/08 17:26:15 hvieyra Exp $
SQL> Rem
SQL> Rem catrequtlmg.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2016, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catrequtlmg.sql - Catalog Mandatory Upgrade Script
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         This catalog script can run from utlmmig.sql or catuppst.sql.
SQL> Rem         The event _utlmmig_table_stats_gathering determines where it
SQL> Rem         is run.  If TRUE (the default) it is run from utlmmig.sql, if
SQL> Rem         FALSE it will be run from catuppst.sql. This script gathers
SQL> Rem         statistics on migration stats that are recreated after an
SQL> Rem         upgrade occurs.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         You must be connected AS SYSDBA to run this script.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    hvieyra     05/03/16 - Fix for bug 23223406. Remove estimate_percent
SQL> Rem                           clause.
SQL> Rem    anighosh    09/03/15 - #(21774511): create cluster index name
SQL> Rem                           based on whether operating under utlmmig
SQL> Rem                           or not.
SQL> Rem    anighosh    08/16/15 - #(21377496): Gather cluster index stats
SQL> Rem    jerrede     12/20/12 - Turn off set serveroutput
SQL> Rem    jerrede     04/17/12 - Moved from catuppst.sql
SQL> Rem                           which was written by Tom Raney.
SQL> Rem
SQL>
SQL>
SQL> Rem *********************************************************************
SQL> Rem BEGIN catrequtlmg.sql
SQL> Rem *********************************************************************
SQL>
SQL> Rem =======================================================================
SQL> Rem Statistics gathering
SQL> Rem =======================================================================
SQL> -- DBMS_STATS now depends on DBMS_UTILITY which may have gotten invalidated
SQL> -- by some preceeding DDL statement, so package state needs to be cleared to
SQL> -- avoid ORA-04068, reset_package causes set serveroutput on to not work.
SQL>
SQL> execute dbms_session.reset_package;

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL>
SQL> declare
  2
  3
  4    c_TRACEEVENT CONSTANT VARCHAR2(30)  := '_utlmmig_table_stats_gathering';
  5    c_POSTUPGRADE CONSTANT VARCHAR2(19) := 'CATREQ_POST_UPGRADE';
  6    c_BOOTERR CONSTANT VARCHAR2(23)     := 'BOOTSTRAP_UPGRADE_ERROR';
  7    c_MIGTABLE CONSTANT VARCHAR2(4)     := '$MIG';
  8    c_POSTUPGTABLE CONSTANT VARCHAR2(1) := '$';
  9    s_TableName VARCHAR2(4)             := c_MIGTABLE;
 10    s_IndexName VARCHAR2(3)             := 'MIG';
 11    b_InUtlMig BOOLEAN := sys.dbms_registry_sys.select_props_data(c_BOOTERR);
 12    b_UpgradeMode BOOLEAN := sys.dbms_registry.is_in_upgrade_mode();
 13    b_StatEvt  BOOLEAN := sys.dbms_registry.is_trace_event_set(c_TRACEEVENT);
 14    b_SelProps BOOLEAN := sys.dbms_registry_sys.select_props_data(c_POSTUPGRADE);
 15    b_Props BOOLEAN := TRUE;
 16
 17  begin
 18
 19    --
 20    -- Debug Info
 21    --
 22    IF (b_StatEvt) THEN
 23      sys.dbms_output.put_line('catrequtlmg: b_StatEvt     = TRUE');
 24    ELSE
 25      sys.dbms_output.put_line('catrequtlmg: b_StatEvt     = FALSE');
 26    END IF;
 27
 28    IF (b_SelProps) THEN
 29      sys.dbms_output.put_line('catrequtlmg: b_SelProps    = TRUE');
 30    ELSE
 31      sys.dbms_output.put_line('catrequtlmg: b_SelProps    = FALSE');
 32    END IF;
 33
 34    IF (b_UpgradeMode) THEN
 35      sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = TRUE');
 36    ELSE
 37      sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = FALSE');
 38    END IF;
 39
 40    IF (b_InUtlMig) THEN
 41      sys.dbms_output.put_line('catrequtlmg: b_InUtlMig    = TRUE');
 42    ELSE
 43      sys.dbms_output.put_line('catrequtlmg: b_InUtlMig    = FALSE');
 44      s_TableName := c_POSTUPGTABLE;
 45      s_IndexName := '';
 46    END IF;
 47
 48    --
 49    -- b_StatEvt = FALSE indicates don't collect stats
 50    --             in upgrade mode.
 51    --
 52    -- Don't do the migration stats in UPGRADE mode.
 53    -- Stats will run no matter what mode we are in
 54    -- if post upgrade data is found in sys.props$.
 55    --
 56    IF (b_StatEvt = FALSE AND b_SelProps = FALSE) THEN
 57
 58      --
 59      -- In Upgrade Mode Only
 60      --
 61      IF (b_UpgradeMode) THEN
 62
 63        --
 64        -- Set sys.props$ table indicating that it
 65        -- needs to be run in the post upgrade script.
 66        --
 67        b_Props := sys.dbms_registry_sys.insert_props_data(c_POSTUPGRADE,
 68                 'Run Migration Stats',
 69                 'Startup database in normal mode and run catuppst.sql');
 70        IF (b_Props) THEN
 71          sys.dbms_output.put_line('catrequtlmg: insert_props_data: Success');
 72        ELSE
 73          sys.dbms_output.put_line('catrequtlmg: insert_props_data: Failure');
 74        END IF;
 75
 76      END IF;
 77
 78      RETURN;
 79
 80    END IF;
 81
 82    --
 83    -- b_StatEvt = TRUE indicates collect stats
 84    --             in upgrade mode.
 85    --
 86    -- Don't do the migration stats in NORMAL mode.
 87    -- Stats will run no matter what mode we are in
 88    -- if post upgrade data is found in sys.props$.
 89    --
 90    IF (b_StatEvt = TRUE AND b_SelProps = FALSE AND b_UpgradeMode = FALSE) THEN
 91
 92        RETURN;
 93
 94    END IF;
 95
 96    --
 97    -- Updating migration stats in post upgrade. Write an entry to
 98    -- sys.props$ table to indicate that stat collection has started.
 99    -- If this entry is present then this routine has failed.
100    --
101    IF (b_SelProps) THEN
102
103      b_Props := sys.dbms_registry_sys.update_props_data(c_POSTUPGRADE,
104                                           'Started Migration Stats');
105      IF (b_Props) THEN
106        sys.dbms_output.put_line('catrequtlmg: update_props_data: Success');
107      ELSE
108        sys.dbms_output.put_line('catrequtlmg: update_props_data: Failure');
109      END IF;
110
111    END IF;
112
113
114    --
115    -- Delete Stats
116    --
117    sys.dbms_output.put_line('catrequtlmg: Deleting table stats');
118    sys.dbms_stats.delete_table_stats('SYS', 'OBJ' || s_TableName);
119    sys.dbms_stats.delete_table_stats('SYS', 'USER' || s_TableName);
120    sys.dbms_stats.delete_table_stats('SYS', 'COL' || s_TableName);
121    sys.dbms_stats.delete_table_stats('SYS', 'CLU' || s_TableName);
122    sys.dbms_stats.delete_table_stats('SYS', 'CON' || s_TableName);
123    sys.dbms_stats.delete_table_stats('SYS', 'TAB' || s_TableName);
124    sys.dbms_stats.delete_table_stats('SYS', 'IND' || s_TableName);
125    sys.dbms_stats.delete_table_stats('SYS', 'ICOL' || s_TableName);
126    sys.dbms_stats.delete_table_stats('SYS', 'LOB' || s_TableName);
127    sys.dbms_stats.delete_table_stats('SYS', 'COLTYPE' || s_TableName);
128    sys.dbms_stats.delete_table_stats('SYS', 'SUBCOLTYPE' || s_TableName);
129    sys.dbms_stats.delete_table_stats('SYS', 'NTAB' || s_TableName);
130    sys.dbms_stats.delete_table_stats('SYS', 'REFCON' || s_TableName);
131    sys.dbms_stats.delete_table_stats('SYS', 'OPQTYPE' || s_TableName);
132    sys.dbms_stats.delete_table_stats('SYS', 'ICOLDEP' || s_TableName);
133    sys.dbms_stats.delete_table_stats('SYS', 'TSQ' || s_TableName);
134    sys.dbms_stats.delete_table_stats('SYS', 'VIEWTRCOL' || s_TableName);
135    sys.dbms_stats.delete_table_stats('SYS', 'ATTRCOL' || s_TableName);
136    sys.dbms_stats.delete_table_stats('SYS', 'TYPE_MISC' || s_TableName);
137    sys.dbms_stats.delete_table_stats('SYS', 'LIBRARY' || s_TableName);
138    sys.dbms_stats.delete_table_stats('SYS', 'ASSEMBLY' || s_TableName);
139
140    --
141    -- Gather Stats
142    --
143    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OBJ' ||
144                             s_TableName);
145    sys.dbms_stats.gather_table_stats('SYS', 'OBJ' || s_TableName,
146                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
147    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats USER' ||
148                             s_TableName);
149    sys.dbms_stats.gather_table_stats('SYS', 'USER' || s_TableName,
150                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
151    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COL' ||
152                             s_TableName);
153    sys.dbms_stats.gather_table_stats('SYS', 'COL' || s_TableName,
154                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
155    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CLU' ||
156                              s_TableName);
157    sys.dbms_stats.gather_table_stats('SYS', 'CLU' || s_TableName,
158                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
159    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CON' ||
160                              s_TableName);
161    sys.dbms_stats.gather_table_stats('SYS', 'CON' || s_TableName,
162                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
163    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TAB' ||
164                             s_TableName);
165    sys.dbms_stats.gather_table_stats('SYS', 'TAB' || s_TableName,
166                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
167    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats IND' ||
168                             s_TableName);
169    sys.dbms_stats.gather_table_stats('SYS', 'IND' || s_TableName,
170                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
171    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOL' ||
172                             s_TableName);
173    sys.dbms_stats.gather_table_stats('SYS', 'ICOL' || s_TableName,
174                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
175    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LOB' ||
176                              s_TableName);
177    sys.dbms_stats.gather_table_stats('SYS', 'LOB' || s_TableName,
178                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
179    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COLTYPE' ||
180                             s_TableName);
181    sys.dbms_stats.gather_table_stats('SYS', 'COLTYPE' || s_TableName,
182                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
183    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats SUBCOLTYPE' ||
184                             s_TableName);
185    sys.dbms_stats.gather_table_stats('SYS', 'SUBCOLTYPE' || s_TableName,
186                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
187    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats NTAB' ||
188                             s_TableName);
189    sys.dbms_stats.gather_table_stats('SYS', 'NTAB' || s_TableName,
190                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
191    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats REFCON' ||
192                             s_TableName);
193    sys.dbms_stats.gather_table_stats('SYS', 'REFCON' || s_TableName,
194                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
195    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OPQTYPE' ||
196                             s_TableName);
197    sys.dbms_stats.gather_table_stats('SYS', 'OPQTYPE' || s_TableName,
198                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
199    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOLDEP' ||
200                             s_TableName);
201    sys.dbms_stats.gather_table_stats('SYS', 'ICOLDEP' || s_TableName,
202                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
203    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TSQ' ||
204                             s_TableName);
205    sys.dbms_stats.gather_table_stats('SYS', 'TSQ' || s_TableName,
206                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
207    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats VIEWTRCOL' ||
208                             s_TableName);
209    sys.dbms_stats.gather_table_stats('SYS', 'VIEWTRCOL' || s_TableName,
210                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
211    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ATTRCOL' ||
212                             s_TableName);
213    sys.dbms_stats.gather_table_stats('SYS', 'ATTRCOL' || s_TableName,
214                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
215    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TYPE_MISC' ||
216                             s_TableName);
217    sys.dbms_stats.gather_table_stats('SYS', 'TYPE_MISC' || s_TableName,
218                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
219    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LIBRARY' ||
220                             s_TableName);
221    sys.dbms_stats.gather_table_stats('SYS', 'LIBRARY' || s_TableName,
222                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
223    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ASSEMBLY' ||
224                             s_TableName);
225    sys.dbms_stats.gather_table_stats('SYS', 'ASSEMBLY' || s_TableName,
226                                  method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
227
228
229    -- [21377496]: Gather_Table_Stats does not collect stats for cluster index.
230    -- Cluster index is not associated with any table, but only with a cluster.
231    -- Thus we need to explicitly collected stats for this index.
232    --
233    -- [21774511]: Note that utlmmig may not be invoked for patch upgrades.
234    -- Given that, create the index name appropriately depending on whether
235    -- we are inside utlmmig or not.
236
237    -- Delete Cluster Index Stats
238
239    sys.dbms_output.put_line('catrequtlmg: Deleting cluster index stats');
240    sys.dbms_stats.delete_index_stats('SYS', 'I_USER#' || s_IndexName);
241    sys.dbms_stats.delete_index_stats('SYS', 'I_OBJ#' || s_IndexName);
242
243    -- Gather Cluster Index Stats
244
245    sys.dbms_output.put_line('catrequtlmg: Gathering Index Stats I_USER#' ||
246                             s_IndexName);
247    sys.dbms_stats.gather_index_stats('SYS', 'I_USER#' || s_IndexName);
248
249    sys.dbms_output.put_line('catrequtlmg: Gathering Index Stats I_OBJ#'||
250                             s_IndexName);
251    sys.dbms_stats.gather_index_stats('SYS', 'I_OBJ#' || s_IndexName);
252
253    --
254    -- Delete any previous entry that may have been stored in
255    -- sys.props$ table.
256    --
257    b_Props := sys.dbms_registry_sys.delete_props_data(c_POSTUPGRADE);
258    IF (b_Props) THEN
259      sys.dbms_output.put_line('catrequtlmg: delete_props_data: Success');
260    ELSE
261      sys.dbms_output.put_line('catrequtlmg: delete_props_data: No Props Data');
262    END IF;
263
264  end;
265  /
catrequtlmg: b_StatEvt     = TRUE
catrequtlmg: b_SelProps    = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig    = FALSE

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Set serveroutput off
SQL> --
SQL> set serveroutput off;
SQL>
SQL> --
SQL> -- Reset Package to be on the safe side for the
SQL> -- case where we are running in catuppst.sql
SQL> --
SQL> execute dbms_session.reset_package;

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catrequtlmg.sql
SQL> Rem *********************************************************************
SQL>
SQL>
SQL>
SQL> Rem
SQL> Rem Display End TimeStamp
SQL> Rem
SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_end') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END             2020-10-06 17:37:25
DBUA_TIMESTAMP CATREQ_END    FINISHED 2020-10-06 17:37:25
DBUA_TIMESTAMP CATREQ_END        NONE 2020-10-06 17:37:25


1 row selected.

SQL>
SQL> Rem *********************************************************************
SQL> Rem END catrequired.sql
SQL> Rem *********************************************************************
SQL>
SQL>
SQL> --
SQL> -- These were created in utlmmig.sql but could not be dropped until now.
SQL> -- Suppress "does not exist" errors.
SQL> --
SQL> set serveroutput on;
SQL> begin
  2    sys.dbms_output.put_line('catuppst: Dropping library DBMS_DDL_INTERNAL_LIB');
  3    execute immediate 'drop library DBMS_DDL_INTERNAL_LIB';
  4  exception
  5    when others then
  6    if sqlcode = -4043 then
  7        null;
  8    end if;
  9  end;
 10  /
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    sys.dbms_output.put_line('catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG');
  3    execute immediate 'drop view "_CURRENT_EDITION_OBJ_MIG"';
  4  exception
  5    when others then
  6      if sqlcode = -942 then
  7        null;
  8      end if;
  9  end;
 10  /
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    sys.dbms_output.put_line('catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG');
  3    execute immediate 'drop view "_ACTUAL_EDITION_OBJ_MIG"';
  4  exception
  5    when others then
  6      if sqlcode = -942 then
  7        null;
  8      end if;
  9  end;
 10  /
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

SQL>
SQL> Rem *************************************************************************
SQL> Rem Bug 17526621 revoke select_catalog_role
SQL> Rem *************************************************************************
SQL> begin
  2    execute immediate 'revoke select on cdb_keepsizes from select_catalog_role';
  3  exception when others then
  4    if sqlcode in (-1927, -942, -65173) then null;
  5    else raise;
  6    end if;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> set serveroutput off
SQL>
SQL>
SQL>
SQL> Rem =======================================================================
SQL> Rem Bug 14258301 - Gather fixed objects stats IF NONE of the fixed object
SQL> Rem tables has stats
SQL> Rem =======================================================================
SQL>
SQL> set serveroutput on
SQL> declare
  2    has_stats_cnt  number := 0;  -- # of fixed object tables that have stats
  3  begin
  4    -- find # of fixed object tables that have had stats collected
  5    execute immediate
  6      'select count(*) ' ||
  7      'from sys.dba_tab_statistics ' ||
  8      ' where owner = ''SYS'' and table_name like ''X$%'' ' ||
  9      '       and last_analyzed is not null'
 10      into has_stats_cnt;
 11
 12    -- if none of the fixed obj tables have had stats collected
 13    -- then gather fixed objects stats
 14    -- else do nothing
 15    if (has_stats_cnt = 0) then
 16      sys.dbms_output.put_line('catuppst: Gathering fixed objects stats now...');
 17      sys.dbms_stats.gather_fixed_objects_stats;
 18      sys.dbms_output.put_line('catuppst: Gathering fixed objects stats done.');
 19    end if;
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL>
SQL> Rem =======================================================================
SQL> Rem Gather Fixed Objects Stats end
SQL> Rem =======================================================================
SQL>
SQL> Rem =======================================================================
SQL> Rem Gather stats on Logminer Dictionary tables to initialize incremental
SQL> Rem stats mode
SQL> Rem =======================================================================
SQL>
SQL> @@execlmnrstats.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/execlmnrstats.sql /main/1 2014/12/17 08:57:27 cderosa Exp $
SQL> Rem
SQL> Rem execlmnrstats.sql
SQL> Rem
SQL> Rem Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         execlmnrstats.sql - Gather stats on Logminer dictionary tables.
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         Gather stats on Logminer dictionary tables. This is the first
SQL> Rem         time stats are called after incremental prefs are set, so this
SQL> Rem         will set up the incremental infrastructure.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         This is called during db creation and during upgrade across 12.1
SQL> Rem
SQL> Rem    BEGIN SQL_FILE_METADATA
SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/execlmnrstats.sql
SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/execlmnrstats.sql
SQL> Rem    SQL_PHASE: EXECLMNRSTATS
SQL> Rem    SQL_STARTUP_MODE: NORMAL
SQL> Rem    SQL_IGNORABLE_ERRORS: NONE
SQL> Rem    SQL_CALLING_FILE: rdbms/admin/execlmnr.sql
SQL> Rem    END SQL_FILE_METADATA
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cderosa     07/03/14 - Initial statistics gathering after incremental
SQL> Rem                           table prefs are set.
SQL> Rem    cderosa     07/03/14 - Created
SQL> Rem
SQL>
SQL> @@?/rdbms/admin/sqlsessstart.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/1 2013/03/13 13:08:33 surman Exp $
SQL> Rem
SQL> Rem sqlsessstart.sql
SQL> Rem
SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         sqlsessstart.sql - SQL session start
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         Any commands which should be run at the start of all oracle
SQL> Rem         supplied scripts.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         See sqlsessend.sql for the corresponding end script.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts
SQL> Rem    surman      03/08/13 - Created
SQL> Rem
SQL>
SQL> alter session set "_ORACLE_SCRIPT" = true;

Session altered.

SQL> DECLARE
  2          cursor table_name_cursor  is
  3                  select  x.name table_name
  4                  from sys.x$krvxdta x
  5                  where bitand(x.flags, 12) != 0;
  6          filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
  7          obj_lst    DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
  8          ind number := 1;
  9  BEGIN
 10     for rec in table_name_cursor loop
 11        begin
 12          filter_lst.extend(1);
 13          filter_lst(ind).ownname := 'SYSTEM';
 14          filter_lst(ind).objname := 'LOGMNR_'|| rec.table_name||'';
 15          ind := ind + 1;
 16        end;
 17     end loop;
 18     DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SYSTEM', objlist=>obj_lst, obj_filter_list=>filter_lst);
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/sqlsessend.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/1 2013/03/13 13:08:33 surman Exp $
SQL> Rem
SQL> Rem sqlsessend.sql
SQL> Rem
SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         sqlsessend.sql - SQL session end
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         Any commands which should be run at the end of all oracle
SQL> Rem         supplied scripts.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         See sqlsessstart.sql for the corresponding start script.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts
SQL> Rem    surman      03/08/13 - Created
SQL> Rem
SQL>
SQL> alter session set "_ORACLE_SCRIPT" = false;

Session altered.

SQL>
SQL>
SQL>
SQL> Rem =======================================================================
SQL> Rem Logminer End
SQL> Rem =======================================================================
SQL>
SQL> Rem =======================================================================
SQL> Rem Upgrade types in Oracle-Maintained tables if any have not already
SQL> Rem been upgraded to the latest versions of evolved types.
SQL> Rem =======================================================================
SQL>
SQL> @@catuptabdata.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catuptabdata.sql /main/2 2016/01/09 07:48:40 raeburns Exp $
SQL> Rem
SQL> Rem catuptabdata.sql
SQL> Rem
SQL> Rem Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catuptabdata.sql - CATalog UPgrade oracle-maintained TABle DATA
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         This script runs ALTER TABLE UPGRADE statements for any
SQL> Rem         Oracle-Maintained tables that are flagged as having type data
SQL> Rem         that needs to be upgraded.  The utluptabdata.sql script performs
SQL> Rem         ALTER TABLE UPGRADE statements for customer tables that
SQL> Rem         depend on Oracle-Maintained types and need to be upgraded.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         This script must be run connected AS SYSDBA.
SQL> Rem
SQL> Rem    BEGIN SQL_FILE_METADATA
SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/catuptabdata.sql
SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/catuptabdata.sql
SQL> Rem    SQL_PHASE: CATUPTABDATA
SQL> Rem    SQL_STARTUP_MODE: NORMAL
SQL> Rem    SQL_IGNORABLE_ERRORS: NONE
SQL> Rem    SQL_CALLING_FILE: rdbms/admin/catuppst.sql
SQL> Rem    END SQL_FILE_METADATA
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    raeburns    12/09/15 - Bug 22175911: remove serveroutput off,
SQL> Rem                           improve error message
SQL> Rem    raeburns    08/24/15 - script to upgrade types in
SQL> Rem                           Oracle-Maintained tables
SQL> Rem    raeburns    08/24/15 - Created
SQL> Rem
SQL>
SQL> Rem ====================================================================
SQL> Rem BEGIN catuptabdata.sql
SQL> Rem ====================================================================
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    CURSOR tabs IS
  3       SELECT DISTINCT u.name owner, o.name name
  4       FROM sys.obj$ o, sys.user$ u, sys.col$ c, sys.coltype$ t
  5       WHERE bitand(t.flags,256) = 256 AND -- NOT upgraded
  6             t.intcol# = c.intcol# AND
  7             t.col# = c.col# AND
  8             t.obj# = c.obj# AND
  9             c.obj# = o.obj# AND
 10             o.owner# = u.user# AND
 11             o.owner# IN  -- Oracle-supplied user
 12                (SELECT user# FROM sys.user$
 13                 WHERE type#=1 and bitand(spare1, 256)= 256);
 14  BEGIN
 15     FOR tab IN tabs LOOP
 16       BEGIN
 17         EXECUTE IMMEDIATE 'ALTER TABLE ' ||
 18                     dbms_assert.enquote_name(tab.owner)||
 19                     '.' || dbms_assert.enquote_name(tab.name) ||
 20                     ' UPGRADE INCLUDING DATA';
 21         dbms_output.put_line ('Table ' || tab.owner || '.' ||
 22                                           tab.name || ' upgraded.');
 23       EXCEPTION
 24         WHEN OTHERS THEN
 25           dbms_output.put_line
 26                ('Table ' || tab.owner || '.' || tab.name || ' not upgraded.');
 27             dbms_output.put_line
 28                ('..' || SUBSTR(SQLERRM, 1, 78));
 29       END;
 30     END LOOP;
 31  END;
 32  /

PL/SQL procedure successfully completed.

SQL>
SQL> Rem ====================================================================
SQL> Rem END catuptabdata.sql
SQL> Rem ====================================================================
SQL>
SQL> SET SERVEROUTPUT OFF
SQL>
SQL> Rem =======================================================================
SQL> Rem Bug 19651064 - Copy data to new WRH$_SYSMETRIC_HISTORY table
SQL> Rem =======================================================================
SQL>
SQL> begin
  2    execute immediate 'insert /*+ APPEND parallel enable_parallel_dml */ into
  3                       WRH$_SYSMETRIC_HISTORY select /*+ PARALLEL */ * from
  4                       TMP_SYSMETRIC_HISTORY';
  5    execute immediate 'drop index TMP_SYSMETRIC_HISTORY_INDEX';
  6    execute immediate 'drop table TMP_SYSMETRIC_HISTORY';
  7    commit;
  8  exception when others then
  9    if sqlcode in (-942, -1418) then null;
 10    else raise;
 11    end if;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> Rem =======================================================================
SQL> Rem Bug 19651064 - End
SQL> Rem =======================================================================
SQL>
SQL> Rem =======================================================================
SQL> Rem Component Postupgrade action for 12.2
SQL> Rem =======================================================================
SQL>
SQL> Rem =======================================================================
SQL> Rem If EM in the database, run @emremove.sql to remove EM schema
SQL> Rem This is only needed for upgrading database from 11.2 and prior
SQL> Rem =======================================================================
SQL>
SQL> COLUMN :em_name NEW_VALUE em_file NOPRINT;
SQL> VARIABLE em_name VARCHAR2(30)
SQL> DECLARE
  2  BEGIN
  3     IF dbms_registry.is_loaded('EM') IS NOT NULL THEN
  4        :em_name := '@emremove.sql';   -- EM exists in DB
  5     ELSE
  6        :em_name := dbms_registry.nothing_script;   -- No EM
  7     END IF;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> SELECT :em_name FROM DUAL;




1 row selected.

SQL> @&em_file
SQL> Rem $Header: rdbms/admin/nothing.sql /main/3 2014/05/19 21:59:08 aketkar Exp $
SQL> Rem
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
SQL> Rem SQL_PHASE: NOTHING
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL>
SQL> Rem =======================================================================
SQL> Rem EM End
SQL> Rem =======================================================================
SQL>
SQL>
SQL> Rem =======================================================================
SQL> Rem Do Java longer identifiers name translation, if necessary
SQL> Rem =======================================================================
SQL>
SQL> declare
  2    ret varchar2(20);
  3  begin
  4    ret := dbms_java_test.funcall('-lid_translate_all', ' ');
  5  exception
  6    when others then
  7      null;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> Rem =======================================================================
SQL> Rem Java longer identifiers name translation End
SQL> Rem =======================================================================
SQL>
SQL> Rem =======================================================================
SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed
SQL> Rem =======================================================================
SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_END') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END             2020-10-06 17:37:27
DBUA_TIMESTAMP POSTUP_END    FINISHED 2020-10-06 17:37:27
DBUA_TIMESTAMP POSTUP_END        NONE 2020-10-06 17:37:27


1 row selected.

SQL>
SQL> -- DBUA_TIMESTAMP: catuppst.sql finished
SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST               2020-10-06 17:37:27
DBUA_TIMESTAMP CATUPPST      FINISHED 2020-10-06 17:37:27
DBUA_TIMESTAMP CATUPPST          NONE 2020-10-06 17:37:27


1 row selected.

SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> Rem
SQL> Rem Set _ORACLE_SCRIPT to false
SQL> Rem
SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=false;

Session altered.

SQL>
SQL> Rem *********************************************************************
SQL> Rem END catuppst.sql
SQL> Rem *********************************************************************

6.5. Invalid Object List:

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
      8305

SQL> @/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/utlrp.sql
... ... ... 
...Starting validation 17:29:54
...Database user "SYS", database schema "APEX_050000", user# "121" 17:29:54
...grant execute on "SYS"."DBMS_CRYPTO_INTERNAL" to APEX_050000 17:29:56
...272 packages
...265 package bodies
...465 tables
...8 functions
...16 procedures
...4 sequences
...497 triggers
...1582 indexes
...255 views
...0 libraries
...14 types
...5 type bodies
...0 operators
...0 index types
...Begin key object existence check 17:29:58
...Completed key object existence check 17:29:58
...Setting DBMS Registry 17:29:58
...Setting DBMS Registry Complete 17:29:58
...Exiting validate 17:29:58

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

1 row selected.

6.6. Run postupgrade fixup script:

SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

LOG:
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
SQL> REM
SQL> REM    Oracle POST-Upgrade Fixup Script
SQL> REM
SQL> REM    Auto-Generated by:       Oracle Preupgrade Script
SQL> REM                             Version: 12.2.0.1.0 Build: 1
SQL> REM    Generated on:            2020-10-05 15:42:49
SQL> REM
SQL> REM    Source Database:         ORCL
SQL> REM    Source Database Version: 11.2.0.4.0
SQL> REM    For Upgrade to Version:     12.2.0.1.0
SQL> REM
SQL>
SQL> REM
SQL> REM    Setup Environment
SQL> REM
SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.

No errors.

1 row selected.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2020-10-05 15:42:49

For Source Database:     ORCL
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
old_time_zones_exist      Failed  Manual fixup recommended.
post_dictionary           Passed  None
fixed_objects             Passed  None
upg_by_std_upgrd          Failed  Manual fixup recommended.

PL/SQL procedure successfully completed.

Session altered.

6.7. Time Zone Upgrade:

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        14

SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%';  

PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_SECONDARY_TT_VERSION                           0
DST_PRIMARY_TT_VERSION                             14
DST_UPGRADE_STATE                                  NONE

3 rows selected.

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade;
ORACLE instance started.

Database mounted.
Database opened.

SQL> SET SERVEROUTPUT ON
DECLARE
  v_tz_version PLS_INTEGER;
BEGIN
  v_tz_version := DBMS_DST.get_latest_timezone_version;
  DBMS_OUTPUT.put_line('v_tz_version=' || v_tz_version);
  DBMS_DST.begin_upgrade(v_tz_version);
END;
/

v_tz_version=26
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SET LINESIZE 150
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME; 

PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_PRIMARY_TT_VERSION                             26
DST_SECONDARY_TT_VERSION                           14
DST_UPGRADE_STATE                                  UPGRADE

3 rows selected.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Database mounted.
Database opened.
SQL>
SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;  

PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_PRIMARY_TT_VERSION                             26
DST_SECONDARY_TT_VERSION                           14
DST_UPGRADE_STATE                                  UPGRADE

3 rows selected.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
          v_failures   PLS_INTEGER;
        BEGIN
          DBMS_DST.upgrade_database(v_failures);
          DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : v_failures=' || v_failures);
          DBMS_DST.end_upgrade(v_failures);
          DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : v_failures=' || v_failures);
        END;
        /

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_050000"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
DBMS_DST.upgrade_database : v_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : v_failures=0

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;  

PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_PRIMARY_TT_VERSION                             26
DST_SECONDARY_TT_VERSION                           0
DST_UPGRADE_STATE                                  NONE

3 rows selected.

6.8. Verify Database Registry::

SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS
--------- -------------------- --- ----------------- ------------
ORCL      READ WRITE           NO  12.2.0.1.0        OPEN

SQL> COL COMP_ID FOR A20
SQL> COL COMP_NAME FOR A45
SQL> COL VERSION FOR A20
SQL> SET LINESIZE 180
SQL> SELECT COMP_ID,COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

COMP_ID              COMP_NAME                                     VERSION              STATUS
-------------------- --------------------------------------------- -------------------- -----------
CATALOG              Oracle Database Catalog Views                 12.2.0.1.0           VALID
CATPROC              Oracle Database Packages and Types            12.2.0.1.0           VALID
JAVAVM               JServer JAVA Virtual Machine                  12.2.0.1.0           VALID
XML                  Oracle XDK                                    12.2.0.1.0           VALID
CATJAVA              Oracle Database Java Packages                 12.2.0.1.0           VALID
APS                  OLAP Analytic Workspace                       12.2.0.1.0           VALID
OWM                  Oracle Workspace Manager                      12.2.0.1.0           VALID
CONTEXT              Oracle Text                                   12.2.0.1.0           VALID
XDB                  Oracle XML Database                           12.2.0.1.0           VALID
ORDIM                Oracle Multimedia                             12.2.0.1.0           VALID
SDO                  Spatial                                       12.2.0.1.0           VALID

COMP_ID              COMP_NAME                                     VERSION              STATUS
-------------------- --------------------------------------------- -------------------- -----------
XOQ                  Oracle OLAP API                               12.2.0.1.0           VALID
AMD                  OLAP Catalog                                  11.2.0.4.0           OPTION OFF
APEX                 Oracle Application Express                    5.0.4.00.12          VALID

14 rows selected.

6.9. Database Backup:

[oracle@dbupgrade dbhome_1]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Oct 6 17:38:22 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1580708519)

RMAN> run
{
allocate channel ch01 type disk format '/u01/software/ch01_orcl_after_update%U.rman' ;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel ch01;
}

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=55 device type=DISK


Starting backup at 05-OCT-20
current log archived
Starting backup at 05-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1053027873
... ... ...
input archived log thread=1 sequence=124 RECID=120 STAMP=1053036043
input archived log thread=1 sequence=125 RECID=121 STAMP=1053041661
input archived log thread=1 sequence=126 RECID=122 STAMP=1053041906
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl_after_update06vc899t_1_1.rman tag=TAG20201005T233826 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
Finished backup at 05-OCT-20

Starting backup at 05-OCT-20
channel ch01: starting compressed full datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/orcl/orcl/undotbs01.dbf
input datafile file number=00001 name=/u02/oradata/orcl/orcl/system01.dbf
input datafile file number=00002 name=/u02/oradata/orcl/orcl/sysaux01.dbf
input datafile file number=00004 name=/u02/oradata/orcl/orcl/users01.dbf
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl_after_update07vc89bu_1_1.rman tag=TAG20201005T234046 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
Finished backup at 05-OCT-20

Starting backup at 05-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=127 RECID=123 STAMP=1053042111
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl_after_update08vc89e0_1_1.rman tag=TAG20201005T234151 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-20

Starting Control File and SPFILE Autobackup at 05-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_10_05/o1_mf_s_1053042113_hqqsw18p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-20

released channel: ch01

6.10. Check COMPATIBLE parametr values and change to 12.2:

If there is no chance to rollback, then change COMPATIBLE parameter to 12.2.

SQL> show parameter COMPATIBLE
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0

SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE;
 
System altered.
 
SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
 
Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1275068416 bytes
Database Buffers          268435456 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
 
SQL> show parameter COMPATIBLE
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
noncdb_compatible                    boolean     FALSE

7. Rollback to Earlier Version (11.2.0.4):

If you get any major issue after upgradation and need to rollback (Not recommended to rollback but no other option) then follow the following steps.

7.1. Database Status on 12.2.0.1:

SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS
--------- -------------------- --- ----------------- ------------
ORCL      READ WRITE           NO  12.2.0.1.0        OPEN

7.2. Flashback Status on 12.2.0.1:

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
SQL>
SQL>
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        05-OCT-20 07.56.01.000000000 PM

7.3. Restore Database using Guarantee Flashback:

It will take couple of minutes to complete.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2415919104 bytes
Fixed Size                  8623544 bytes
Variable Size             654314056 bytes
Database Buffers         1744830464 bytes
Redo Buffers                8151040 bytes
Database mounted.
SQL>
SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE;

Flashback complete.

SQL> shu immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

7.4. Start Database from older version(11g):

[oracle@dbupgrade ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@dbupgrade ~]$ export ORACLE_SID=orcl
[oracle@dbupgrade ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@dbupgrade ~]$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@dbupgrade ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 5 23:47:13 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2405122048 bytes
Fixed Size                  2255592 bytes
Variable Size             603981080 bytes
Database Buffers         1795162112 bytes
Redo Buffers                3723264 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;

Database altered.

SQL> select name,open_mode,version,status from v$database,v$instance;

NAME      OPEN_MODE            VERSION           STATUS
--------- -------------------- ----------------- ------------
ORCL      READ WRITE           11.2.0.4.0        OPEN

7.5. Invalid Object List:

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

7.6. COMPATIBLE Values:

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0

7.7. Time Zone Status:

SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%';  2    3

PROPERTY_NAME                                      VALUE
-------------------------------------------------- --------------------
DST_UPGRADE_STATE                                  NONE
DST_PRIMARY_TT_VERSION                             14
DST_SECONDARY_TT_VERSION                           0

7.8. Drop Flashback Restore Point:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME
--------------------------------------------------------------------------------
GUA TIME
--- ---------------------------------------------------------------------------
PRE_UPGRADE
YES 05-OCT-20 07.56.01.000000000 PM


SQL> col name for a20
SQL> col GUARANTEE_FLASHBACK_DATABASE for a10
SQL> col TIME for a60
SQL> set lines 190
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        05-OCT-20 07.56.01.000000000 PM

SQL> drop restore point PRE_UPGRADE;

Restore point dropped.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Doc ID 556610.1)

Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (Doc ID 2173141.1)