Upgrading Multitenant Database to Oracle 19c

Prerequisities

Make sure you have all the OS prerequisites in place by running the 19c preinstall package. On Oracle Linux you can do this by installing the preinstall package. It probably makes sense to update the remaining packages also.

yum install -y oracle-database-preinstall-19c
yum update -y
Install 19c Software

The following commands will perform a silent installation of the 19c software.

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export SOFTWARE_DIR=/u01/software
export ORA_INVENTORY=/u01/app/oraInventory

mkdir -p ${ORACLE_HOME}
cd $ORACLE_HOME

/bin/unzip -oq ${SOFTWARE_DIR}/LINUX.X64_193000_db_home.zip

./runInstaller -ignorePrereq -waitforcompletion -silent                        \
    -responseFile ${ORACLE_HOME}/install/response/db_install.rsp               \
    oracle.install.option=INSTALL_DB_SWONLY                                    \
    ORACLE_HOSTNAME=${ORACLE_HOSTNAME}                                         \
    UNIX_GROUP_NAME=oinstall                                                   \
    INVENTORY_LOCATION=${ORA_INVENTORY}                                        \
    SELECTED_LANGUAGES=en,en_GB                                                \
    ORACLE_HOME=${ORACLE_HOME}                                                 \
    ORACLE_BASE=${ORACLE_BASE}                                                 \
    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

Run the root scripts when prompted.

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

At this point you should also patch the new Oracle home, but in this case we will forgo that step to keep things simple.

Run preupgrade.jar

Download the latest “preupgrade.jar” file. If you don’t have MOS access you can miss out the next step.

 At the time of writing, the latest preupgrade.jar is the one shipped with 19c, so there is no need to download a newer one. I’s worth checking to see if this situation has changed when you do your upgrade.

Put the latest “preupgrade.jar” into the 19c Oracle home.

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
cd $ORACLE_HOME/rdbms/admin
unzip -o /u01/software/preupgrade_19_cbuild_??_lf.zip

Make sure you are using the original Oracle home and run the “preupgrade.jar”.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1

$ORACLE_BASE/product/19.0.0/dbhome_1/jdk/bin/java -jar $ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

Here is the output from an example run against a 12.2 database. There is output for the root container, seed and any user-defined pluggable databases.

$ $ORACLE_BASE/product/19.0.0/dbhome_1/jdk/bin/java -jar $ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Perform Pre-Upgrade Actions

The output from the “preupgrade.jar” lists a number of pre-upgrade recommendations. Some must be manually applied. Others are incorporated into the “preupgrade_fixups.sql” script. In the following example we will only run the “preupgrade_fixups.sql” script and recompile invalid objects. Remember, in a multitenant database we must run the pre-upgrade actions on the root, seed and all user-defined pluggable databases.

$ORACLE_HOME/perl/bin/perl \
    -I$ORACLE_HOME/perl/lib \
    -I$ORACLE_HOME/rdbms/admin \
    $ORACLE_HOME/rdbms/admin/catcon.pl \
    -l /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/ \
    -b preup_${ORACLE_SID} \
    /u01/app/oracle/cfgtoollogs/${ORACLE_SID}/preupgrade/preupgrade_fixups.sql

We can recompile invalid objects in the all containers except the seed database using the following command.

$ORACLE_HOME/perl/bin/perl \
    -I$ORACLE_HOME/perl/lib \
    -I$ORACLE_HOME/rdbms/admin \
    $ORACLE_HOME/rdbms/admin/catcon.pl \
    -l /u01/app/oracle/cfgtoollogs/${ORACLE_SID}/preupgrade/ \
    -b preup_${ORACLE_SID}_recompile \
    -C 'PDB$SEED' \
    $ORACLE_HOME/rdbms/admin/utlrp.sql
Upgrade the Database

With the pre-upgrade actions complete we can start the upgrade. Shutdown the source database.

sqlplus / as sysdba <<EOF
shutdown immediate;
exit;
EOF

Copy the config files from the old to the new Oracle home. You should check the contents of the “listener.ora” file to see if there are any references to the Oracle home path. If there are, amend them.

cp $ORACLE_HOME/network/admin/*.ora $ORACLE_BASE/product/19.0.0/dbhome_1/network/admin

# Add this to $ORACLE_BASE/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
# Need to correct password versions and remove this.
cat >> $ORACLE_BASE/product/19.0.0/dbhome_1/network/admin/sqlnet.ora <<EOF
# This should be temporary while you deal with old passwords.
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
EOF

cp $ORACLE_HOME/dbs/orapw${ORACLE_SID} $ORACLE_BASE/product/19.0.0/dbhome_1/dbs/
cp $ORACLE_HOME/dbs/spfile${ORACLE_SID}.ora $ORACLE_BASE/product/19.0.0/dbhome_1/dbs/

Switch to the 19c listener.

lsnrctl stop

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export PATH=${ORACLE_HOME}/bin:$PATH

lsnrctl start

Start the database using the 19c Oracle home, ready for the upgrade. All user-defined PDBs must also be open in upgrade mode, or they will not be included in the upgrade, and will need to be upgraded manually later.

sqlplus / as sysdba <<EOF
startup upgrade;
alter pluggable database all open upgrade force;
exit;
EOF

You can run the upgrade using either of the following commands. The second is actually just a shorthand for the former.

# Regular upgrade command.
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

# Shorthand command.
$ORACLE_HOME/bin/dbupgrade

Here is the output from an example run against a 12.2 multitenant database. The output looks quite long, as the upgrade is run against the root, seed and user-defined PDBs.

$ $ORACLE_HOME/bin/dbupgrade

Check out the “upg_summary.log” file, and if anything looks wrong, check out the associated “catupgrd*.log” file. At this point I do a shutdown and startup to make sure everything is running in the correct mode. Also make sure the user-defined PDBs are open before doing the post upgrade steps.

sqlplus / as sysdba <<EOF
shutdown immediate;
startup;

column name format A30
select name, open_mode from v\$pdbs;

exit;
EOF
Perform Post-Upgrade Actions

The output from the “preupgrade.jar” lists a number of post-upgrade recommendations. Some must be manually applied. Others are incorporated into the “postupgrade_fixups.sql” script. In the following example we will only run the “postupgrade_fixups.sql” script. Remember, in a multitenant database we must run the post-upgrade actions on the root, seed and all user-defined pluggable databases.

$ORACLE_HOME/perl/bin/perl \
    -I$ORACLE_HOME/perl/lib \
    -I$ORACLE_HOME/rdbms/admin \
    $ORACLE_HOME/rdbms/admin/catcon.pl \
    -l /u01/app/oracle/cfgtoollogs/${ORACLE_SID}/preupgrade/ \
    -b postup_cdb1 \
    /u01/app/oracle/cfgtoollogs/${ORACLE_SID}/preupgrade/postupgrade_fixups.sql

Assuming you didn’t hit any problems along the way, your database is upgraded and ready to go now.

Final Steps

Edit the “/etc/oratab” file, correcting the Oracle home path.

From:
cdb1:/u01/app/oracle/product/12.2.0.1/db_1:Y
To:
cdb1:/u01/app/oracle/product/19.0.0/dbhome_1:Y

You need to consider what you are going to do with the COMPATIBLE parameter. I prefer to reset it as soon as possible.

sqlplus / as sysdba <<EOF
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
EXIT;
EOF

If local undo is not enabled, you need to enable it.

sqlplus / as sysdba <<EOF
shutdown immediate;
startup upgrade;

alter database local undo on;

shutdown immediate;
startup;
EXIT;
EOF