Skip to content

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