Skip to content

Upgrading Oracle Database 11g to Oracle Database 19c (Non-CDB to CDB)

    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=db11g
    export ORAENV_ASK=NO
    . oraenv
    export ORAENV_ASK=YES
    
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/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 an 11.2 database.

    $ $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 run all the manual operations as well as the “preupgrade_fixups.sql” script.

    # 1) Increase the processes parameter.
    sqlplus / as sysdba <<EOF
    alter system set processes=300 scope=spfile;
    shutdown immediate;
    startup;
    exit;
    EOF
    
    # 2) Remove EM DB Console config.
    cp $ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/emremove.sql $ORACLE_HOME/rdbms/admin/emremove.sql
    sqlplus / as sysdba <<EOF
    SET ECHO ON;
    SET SERVEROUTPUT ON;
    @$ORACLE_HOME/rdbms/admin/emremove.sql
    exit;
    EOF
    
    # 3) Remove OLAP catalog.
    sqlplus / as sysdba <<EOF
    @$ORACLE_HOME/olap/admin/catnoamd.sql
    exit;
    EOF
    
    # 4) Ignoring the APEX upgrade. This is a junk test instance. Normally APEX would be at latest version.
    # 5) Included in AUTOFIXUP.
    # 6) This will be unnecessary after removal of EM repository above.
    # 7) Included in AUTOFIXUP.
    
    # Recompile invalid objects.
    sqlplus / as sysdba <<EOF
    @$ORACLE_HOME/rdbms/admin/utlrp.sql
    
    SET SERVEROUTPUT ON;
    EXECUTE DBMS_PREUP.INVALID_OBJECTS;
    exit;
    EOF
    
    # Run preupgrade-fixups.sql script.
    sqlplus / as sysdba <<EOF
    @/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql
    exit;
    EOF
    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/orapwdb11g $ORACLE_BASE/product/19.0.0/dbhome_1/dbs/
    cp $ORACLE_HOME/dbs/spfiledb11g.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.

    sqlplus / as sysdba <<EOF
    startup upgrade;
    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 an 11.2 database.

    $ $ORACLE_HOME/bin/dbupgrade

    The database is shutdown at the end of the upgrade process, so you need to start it before moving on to the post-upgrade actions.

    sqlplus / as sysdba <<EOF
    startup
    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 run all the manual operations as well as the “postupgrade_fixups.sql” script.

    # 11) Time zone file.
    sqlplus / as sysdba <<EOF
    
    -- Check current settings.
    SELECT * FROM v$timezone_file;
    
    SHUTDOWN IMMEDIATE;
    STARTUP UPGRADE;
    
    -- Begin upgrade to the latest version.
    SET SERVEROUTPUT ON
    DECLARE
      l_tz_version PLS_INTEGER;
    BEGIN
      l_tz_version := DBMS_DST.get_latest_timezone_version;
    
      DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
      DBMS_DST.begin_upgrade(l_tz_version);
    END;
    /
    
    SHUTDOWN IMMEDIATE;
    STARTUP;
    
    -- Do the upgrade.
    SET SERVEROUTPUT ON
    DECLARE
      l_failures   PLS_INTEGER;
    BEGIN
      DBMS_DST.upgrade_database(l_failures);
      DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
      DBMS_DST.end_upgrade(l_failures);
      DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
    END;
    /
    
    -- Check new settings.
    SELECT * FROM v$timezone_file;
    
    COLUMN property_name FORMAT A30
    COLUMN property_value FORMAT A20
    
    SELECT property_name, property_value
    FROM   database_properties
    WHERE  property_name LIKE 'DST_%'
    ORDER BY property_name;
    
    exit;
    EOF
    
    # 12) Ignored
    # 13) AUTOFIXUP
    
    # 14) Gather fixed object stats.
    sqlplus / as sysdba <<EOF
    EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
    exit;
    EOF
    
    # AUTOFIXUP
    sqlplus / as sysdba <<EOF
    @/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql
    exit;
    EOF

    Assuming you didn’t hit any problems along the way, your database is upgraded and ready to go now. The following steps are only necessary if you want to convert the non-CDB instance to a pluggable database (PDB).

    Create New Container Database (CDB)

    There are a number of considerations when creating a container database, but for this example we will keep it simple. The command below creates a new container database (CDB) with no user-defined pluggable databases (PDBs).

    # Create new instance.
    export SYS_PASSWORD=SysPassword1
    export ORACLE_SID=cdb1
    
    dbca -silent -createDatabase                                                    \
         -templateName General_Purpose.dbc                                          \
         -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} -responseFile NO_VALUE           \
         -characterSet AL32UTF8                                                     \
         -sysPassword ${SYS_PASSWORD}                                               \
         -systemPassword ${SYS_PASSWORD}                                            \
         -createAsContainerDatabase true                                            \
         -numberOfPDBs 0                                                            \
         -databaseType MULTIPURPOSE                                                 \
         -memoryMgmtType auto_sga                                                   \
         -totalMemory 2000                                                          \
         -storageType FS                                                            \
         -datafileDestination "/u02/oradata/"                                       \
         -redoLogFileSize 500                                                       \
         -emConfiguration NONE                                                      \
         -ignorePreReqs

    For a proper conversion we would have to make sure the CDB had all necessary configuration in place, including options and initialisation parameters. We will ignore the rest of the instance setup to keep it simple.

    Convert Non-CDB to PDB

    Describe the non-CDB instance and turn it off.

    export ORACLE_SID=db11g
    sqlplus / as sysdba <<EOF
    
    SHUTDOWN IMMEDIATE;
    STARTUP OPEN READ ONLY;
    
    BEGIN
      DBMS_PDB.DESCRIBE(
        pdb_descr_file => '/u01/software/db11g.xml');
    END;
    /
    
    SHUTDOWN IMMEDIATE;
    
    exit;
    EOF

    Create a PDB using description of the non-CDB, moving the datafiles into the new location, remembering to run the “noncdb_to_pdb.sql” to clean up before opening the pluggable database.

    export ORACLE_SID=cdb1
    mkdir -p /u02/oradata/CDB1/pdb1/
    
    sqlplus / as sysdba <<EOF
    
    CREATE PLUGGABLE DATABASE pdb1 USING '/u01/software/db11g.xml'
      MOVE
      FILE_NAME_CONVERT = ('/u02/oradata/db11g/', '/u02/oradata/CDB1/pdb1/');
    
    ALTER SESSION SET CONTAINER=pdb1;
    
    @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
    
    ALTER PLUGGABLE DATABASE OPEN;
    ALTER PLUGGABLE DATABASE SAVE STATE;
    
    exit;
    EOF
    Final Steps

    We need to clean up the remains of the non-CDB instance. We can use the DBCA to do this.

    dbca -silent -deleteDatabase -sourceDB db11g -sysDBAUserName sys -sysDBAPassword ${SYS_PASSWORD}

    We can delete any remaining files.

    rm /u01/app/oracle/product/11.2.0.4/db_1/dbs/*db11g*
    rm /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*db11g*
    
    rm -Rf $ORACLE_BASE/admin/db11g
    rm -Rf /u01/app/oracle/diag/rdbms/db11g
    rm -Rf /u02/oradata/db11g

    Edit the “/etc/oratab” file as required.