Upgrade Non-CDB to Oracle Database 21c (AutoUpgrade)

Oracle 21c is an innovation release, so it may not be suitable for most production systems.

This article provides an overview of upgrading a non-CDB database to Oracle 21c on the same server using AutoUpgrade.

◉ Assumptions

This article is focused on upgrading a non-CDB database. If your starting point is a database using the multitenant architecture, you should be reading this article instead.

- Multitenant : Upgrade to Oracle Database 21c (AutoUpgrade)

This article assumes your source database is of a version supported for direct upgrade to 21c.

19c, 18c, 12.2

The non-CDB architecture is desupported in Oracle 21c, so we have two choices when upgrading a non-CDB database to 21c.

◉ Convert the existing non-CDB database to a PDB in the current version of the database, then do a multitentant upgrade.

◉ Convert the non-CDB to a PDB in the new version of the database. The new PDB will be upgraded once it is opened.

In this example we are doing an upgrade from 19c non-CDB to 21c using AutoUpgrade. The process is very similar for all supported versions.

It's important to have backups of everything before you start! Some of these steps are destructive, and if something goes wrong you have no alternative but to restore from backups and start again.

◉ Prerequisities

Make sure you have all the OS prerequisites in place by running the 21c preinstall package. It probably makes sense to update the remaining packages also.

yum install -y oracle-database-preinstall-21c

yum update -y

◉ Install 21c Software

You can read about the installation process in more detail here (OL7, OL8), but for this example we'll keep it brief. The following commands will perform a silent installation of the 21c software.

export ORACLE_HOME=$ORACLE_BASE/product/21.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=cdb1

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_213000_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/21.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.

◉ Create 21c Container Database

We need to create a container database (CDB) as the destination for the resulting PDB. The following example create a CDB called "cdb1" with no PDBs.

#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1

dbca -silent -createDatabase \

 -templateName General_Purpose.dbc \

 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \

 -characterSet AL32UTF8 \

 -sysPassword SysPassword1 \

 -systemPassword SysPassword1 \

 -createAsContainerDatabase true \

 -numberOfPDBs 0 \

 -databaseType MULTIPURPOSE \

 -memoryMgmtType auto_sga \

 -totalMemory 1536 \

 -storageType FS \

 -datafileDestination "/u02/oracle/" \

 -redoLogFileSize 50 \

 -emConfiguration NONE \

 -ignorePreReqs

Prepare for db operation

10% complete

Copying database files

40% complete

Creating and starting Oracle instance

42% complete

46% complete

52% complete

56% complete

60% complete

Completing Database Creation

66% complete

69% complete

70% complete

Executing Post Configuration Actions

100% complete

Database creation complete. For details check the logfiles at:

 /u01/app/oracle/cfgtoollogs/dbca/cdb1.

Database Information:

Global Database Name:cdb1

System Identifier(SID):cdb1

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/cdb13.log" for further details.

$

We enable the fast recovery area, Oracle Managed Files (OMF) and archivelog mode.

sqlplus / as sysdba <<EOF

alter system set db_recovery_file_dest_size=40g;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

alter system set db_create_file_dest = '/u02/oradata';

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

exit;

EOF

You will need to adjust the instance parameters to make sure the container can cope with the demands of the final PDB, but for this example we will ignore that.

◉ Run AutoUpgrade Analyze

Download the latest "autoupgrade.jar" file from MOS 2485457.1. If you don't have MOS access you can miss out the next step.

cd $ORACLE_BASE/product/21.0.0/dbhome_1/rdbms/admin

mv autoupgrade.jar autoupgrade.jar.`date +"%Y"-"%m"-"%d"`

cp /tmp/autoupgrade.jar .

Make sure you are using the original Oracle home before running the "autoupgrade.jar" commands.

export ORACLE_SID=mydb

export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1

Generate a sample file for a non-CDB to PDB upgrade.

$ORACLE_BASE/product/21.0.0/dbhome_1/jdk/bin/java \

  -jar $ORACLE_BASE/product/21.0.0/dbhome_1/rdbms/admin/autoupgrade.jar \

  -create_sample_file config /tmp/config.txt noncdbtopdb

Edit the resulting "/tmp/config.txt" file, setting the details for your required upgrade. In this case we used the following parameters.

upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/mydb

upg1.sid=mydb

upg1.source_home=/u01/app/oracle/product/19.0.0/dbhome_1

upg1.target_cdb=cdb1

upg1.target_home=/u01/app/oracle/product/21.0.0/dbhome_1

upg1.target_pdb_name=mydb

#upg1.target_pdb_copy_option=file_name_convert=('emp', 'emppdb')

upg1.start_time=NOW          # Optional. 10 Minutes from now

upg1.upgrade_node=localhost  # Optional. To find out the name of your node, run the hostname utility. Default is 'localhost'

upg1.run_utlrp=yes           # Optional. Whether or not to run utlrp after upgrade

upg1.timezone_upg=yes        # Optional. Whether or not to run the timezone upgrade

upg1.target_version=21       # Oracle version of the target ORACLE_HOME.  Only required when the target Oracle database version is 12.2

Run the upgrade in analyze mode to see if there are any expected issues with the upgrade.

$ORACLE_BASE/product/21.0.0/dbhome_1/jdk/bin/java \

  -jar $ORACLE_BASE/product/21.0.0/dbhome_1/rdbms/admin/autoupgrade.jar \

  -config /tmp/config.txt -mode analyze

AutoUpgrade tool launched with default options

Processing config file ...

+--------------------------------+

| Starting AutoUpgrade execution |

+--------------------------------+

1 databases will be analyzed

Type 'help' to list console commands

upg> Job 101 completed

------------------- Final Summary --------------------

Number of databases            [ 1 ]

Jobs finished                  [1]

Jobs failed                    [0]

Jobs pending                   [0]

Please check the summary report at:

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

The output files list the status of the analysis, and any manual intervention that is needed before an upgrade can take place. The output of the "status.log" file is shown below. The detail section gives a file containing the details of the steps in the upgrade process. If you've seen the output from the "preupgrade.jar", it will look familiar. If there are any required manual actions in the main log file, the detail file should give more information.

==========================================

          Autoupgrade Summary Report

==========================================

[Date]           Sun Aug 22 07:41:07 UTC 2021

[Number of Jobs] 1

==========================================

[Job ID] 101

==========================================

[DB Name]                mydb

[Version Before Upgrade] 19.12.0.0.0

[Version After Upgrade]  21.3.0.0.0

------------------------------------------

[Stage Name]    PRECHECKS

[Status]        SUCCESS

[Start Time]    2021-08-22 07:40:55

[Duration]      0:00:12

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/prechecks

[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/prechecks/mydb_preupgrade.log

                Precheck passed and no manual intervention needed

------------------------------------------

The log directory contains a number of files, including a HTML format of the detailed report. It's the same information as the log file, but some people my prefer reading this format.

Once any required manual fixups are complete, run the analysis again and you should see a clean analysis report.

◉ Run AutoUpgrade Deploy

We are now ready to run the database upgrade with the following command. The upgrade takes some time, so you will be left at the "upg" prompt until it's complete.

$ORACLE_BASE/product/21.0.0/dbhome_1/jdk/bin/java \

  -jar $ORACLE_BASE/product/21.0.0/dbhome_1/rdbms/admin/autoupgrade.jar \

  -config /tmp/config.txt -mode deploy

AutoUpgrade tool launched with default options

Processing config file ...

+--------------------------------+

| Starting AutoUpgrade execution |

+--------------------------------+

1 databases will be processed

Type 'help' to list console commands

upg>

Use the "help" command to see the command line options. We can list the current jobs and check on the job status using the following commands.

upg> lsj

+----+-------+---------+---------+-------+--------------+--------+----------------+

|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|         MESSAGE|

+----+-------+---------+---------+-------+--------------+--------+----------------+

| 102|   mydb|DBUPGRADE|EXECUTING|RUNNING|21/08/22 07:44|07:49:08|22%Upgraded MYDB|

+----+-------+---------+---------+-------+--------------+--------+----------------+

Total jobs 1

upg> status -job 102

Progress

-----------------------------------

Start time:      21/08/22 07:44

Elapsed (min):   6

End time:        N/A

Last update:     2021-08-22T07:49:08.827

Stage:           DBUPGRADE

Operation:       EXECUTING

Status:          RUNNING

Pending stages:  7

Stage summary:

    SETUP             <1 min

    GRP               <1 min

    PREUPGRADE        <1 min

    PRECHECKS         <1 min

    PREFIXUPS         <1 min

    DRAIN             1 min

    DBUPGRADE         4 min (IN PROGRESS)

Job Logs Locations

-----------------------------------

Logs Base:    /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb

Job logs:     /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102

Stage logs:   /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/dbupgrade

TimeZone:     /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/temp

Additional information

-----------------------------------

Details:

[Upgrading] is [22%] completed for [mydb-mydb]

                 +---------+-------------+

                 |CONTAINER|   PERCENTAGE|

                 +---------+-------------+

                 |     MYDB|UPGRADE [22%]|

                 +---------+-------------+

Error Details:

None

upg>

Here is some sample output during the upgrade process.

upg> lsj

+----+-------+---------+---------+-------+--------------+--------+----------------------------+

|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|                     MESSAGE|

+----+-------+---------+---------+-------+--------------+--------+----------------------------+

| 102|   mydb|PREFIXUPS|EXECUTING|RUNNING|21/08/22 07:44|07:44:28|Loading database information|

+----+-------+---------+---------+-------+--------------+--------+----------------------------+

Total jobs 1

upg> lsj

+----+-------+-----+---------+-------+--------------+--------+------------------+

|Job#|DB_NAME|STAGE|OPERATION| STATUS|    START_TIME| UPDATED|           MESSAGE|

+----+-------+-----+---------+-------+--------------+--------+------------------+

| 102|   mydb|DRAIN|EXECUTING|RUNNING|21/08/22 07:44|07:45:31|Executing describe|

+----+-------+-----+---------+-------+--------------+--------+------------------+

Total jobs 1

upg> lsj

+----+-------+---------+---------+-------+--------------+--------+-------+

|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|MESSAGE|

+----+-------+---------+---------+-------+--------------+--------+-------+

| 102|   mydb|DBUPGRADE|EXECUTING|RUNNING|21/08/22 07:44|07:46:03|Running|

+----+-------+---------+---------+-------+--------------+--------+-------+

Total jobs 1

upg> lsj

+----+-------+---------+---------+-------+--------------+--------+----------------+

|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|         MESSAGE|

+----+-------+---------+---------+-------+--------------+--------+----------------+

| 102|   mydb|DBUPGRADE|EXECUTING|RUNNING|21/08/22 07:44|07:55:09|52%Upgraded MYDB|

+----+-------+---------+---------+-------+--------------+--------+----------------+

Total jobs 1

upg> lsj

+----+-------+-------------+---------+-------+--------------+--------+-------------------+

|Job#|DB_NAME|        STAGE|OPERATION| STATUS|    START_TIME| UPDATED|            MESSAGE|

+----+-------+-------------+---------+-------+--------------+--------+-------------------+

| 102|   mydb|NONCDBTOPDBXY|EXECUTING|RUNNING|21/08/22 07:44|08:02:46|noncdb_to_pdb - 64%|

+----+-------+-------------+---------+-------+--------------+--------+-------------------+

Total jobs 1

upg> lsj

+----+-------+----------+---------+-------+--------------+--------+-------------+

|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME| UPDATED|      MESSAGE|

+----+-------+----------+---------+-------+--------------+--------+-------------+

| 102|   mydb|POSTFIXUPS|EXECUTING|RUNNING|21/08/22 07:44|08:09:53|Remaining 2/4|

+----+-------+----------+---------+-------+--------------+--------+-------------+

Total jobs 1

upg>

Once the job completes a summary message is displayed.

upg> Job 102 completed

------------------- Final Summary --------------------

Number of databases            [ 1 ]

Jobs finished                  [1]

Jobs failed                    [0]

Jobs pending                   [0]

Please check the summary report at:

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html

/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

The "status.log" contains the top-level information about the upgrade process.

==========================================

          Autoupgrade Summary Report

==========================================

[Date]           Sun Aug 22 08:11:31 UTC 2021

[Number of Jobs] 1

==========================================

[Job ID] 102

==========================================

[DB Name]                mydb

[Version Before Upgrade] 19.12.0.0.0

[Version After Upgrade]  21.3.0.0.0

------------------------------------------

[Stage Name]    GRP

[Status]        SUCCESS

[Start Time]    2021-08-22 07:44:09

[Duration]      0:00:01

[Detail]        Please drop the following GRPs after Autoupgrade completes:

                 AUTOUPGRADE_9212_MYDB1912000

------------------------------------------

[Stage Name]    PREUPGRADE

[Status]        SUCCESS

[Start Time]    2021-08-22 07:44:11

[Duration]      0:00:00

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/preupgrade

------------------------------------------

[Stage Name]    PRECHECKS

[Status]        SUCCESS

[Start Time]    2021-08-22 07:44:11

[Duration]      0:00:16

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/prechecks

[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/prechecks/mydb_preupgrade.log

                Precheck passed and no manual intervention needed

------------------------------------------

[Stage Name]    PREFIXUPS

[Status]        SUCCESS

[Start Time]    2021-08-22 07:44:27

[Duration]      0:00:15

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/prefixups

------------------------------------------

[Stage Name]    DRAIN

[Status]        SUCCESS

[Start Time]    2021-08-22 07:44:42

[Duration]      0:01:19

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/drain

------------------------------------------

[Stage Name]    DBUPGRADE

[Status]        SUCCESS

[Start Time]    2021-08-22 07:46:02

[Duration]      0:15:14

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/dbupgrade

------------------------------------------

[Stage Name]    NONCDBTOPDBXY

[Status]        SUCCESS

[Start Time]    2021-08-22 08:01:16

[Duration]      0:08:25

------------------------------------------

[Stage Name]    POSTCHECKS

[Status]        SUCCESS

[Start Time]    2021-08-22 08:09:42

[Duration]      0:00:09

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/postchecks

[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/postchecks/mydb_postupgrade.log

------------------------------------------

[Stage Name]    POSTFIXUPS

[Status]        SUCCESS

[Start Time]    2021-08-22 08:09:51

[Duration]      0:01:40

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/postfixups

------------------------------------------

[Stage Name]    POSTUPGRADE

[Status]        SUCCESS

[Start Time]    2021-08-22 08:11:31

[Duration]      0:00:00

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/postupgrade

------------------------------------------

[Stage Name]    SYSUPDATES

[Status]        SUCCESS

[Start Time]    2021-08-22 08:11:31

[Duration]      0:00:00

[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/sysupdates

------------------------------------------

Upgrade Summary: /u01/app/oracle/cfgtoollogs/autoupgrade/mydb/mydb/102/dbupgrade/upg_summary.log

Check out the "upg_summary.log" file, and if anything looks wrong, check out the associated log files. At this point I do a shutdown and startup to make sure everything is running in the correct mode.

export ORACLE_HOME=$ORACLE_BASE/product/21.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=cdb1

sqlplus / as sysdba <<EOF

alter pluggable database mydb save state;

shutdown immediate;

startup;

show pdbs

exit;

EOF

◉ Final Steps

Edit the "/etc/oratab" file and any environment files as required.

If you are using APEX or ORDS, you probably want to validate them (validate APEX, validate ORDS).

◉ Appendix

The following commands are used to rebuild the databases if you want to rerun the examples.

Rebuild the CDB1 multitenant database.

export ORACLE_HOME=/u01/app/oracle/product/21.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=cdb1

#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1

dbca -silent -createDatabase \

 -templateName General_Purpose.dbc \

 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \

 -characterSet AL32UTF8 \

 -sysPassword SysPassword1 \

 -systemPassword SysPassword1 \

 -createAsContainerDatabase true \

 -numberOfPDBs 0 \

 -databaseType MULTIPURPOSE \

 -memoryMgmtType auto_sga \

 -totalMemory 1536 \

 -storageType FS \

 -datafileDestination "/u02/oradata/" \

 -redoLogFileSize 50 \

 -emConfiguration NONE \

 -ignorePreReqs

sqlplus / as sysdba <<EOF

alter system set db_recovery_file_dest_size=40g;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

alter system set db_create_file_dest = '/u02/oradata';

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

exit;

EOF

Rebuild the MYDB non-CDB database.

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=mydb

#dbca -silent -deleteDatabase -sourceDB mydb -sysDBAUserName sys -sysDBAPassword SysPassword1

dbca -silent -createDatabase \

 -templateName General_Purpose.dbc \

 -gdbname mydb -sid mydb -responseFile NO_VALUE \

 -characterSet AL32UTF8 \

 -sysPassword SysPassword1 \

 -systemPassword SysPassword1 \

 -createAsContainerDatabase false \

 -databaseType MULTIPURPOSE \

 -memoryMgmtType auto_sga \

 -totalMemory 1536 \

 -storageType FS \

 -datafileDestination "/u02/oradata/" \

 -redoLogFileSize 50 \

 -emConfiguration NONE \

 -ignorePreReqs

sqlplus / as sysdba <<EOF

alter system set db_recovery_file_dest_size=40g;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

alter system set db_create_file_dest = '/u02/oradata';

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

exit;

EOF