8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

Upgrade Non-CDB to Oracle Database 21c (Manual) - Replay Upgrade

Oracle 21c is an innovation release, so it may not be suitable for most production systems. Please read this before using Oracle 21c for anything real.

This article provides an overview of a manual upgrade of an existing non-CDB database to Oracle 21c using Replay Upgrade. Upgrades can be very complicated, so you must always read the upgrade manual, and test thoroughly before considering an upgrade of a production environment.

Related articles.

Assumptions

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

You should probably be using the AutoUpgrade method to perform this style of upgrade.

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.

In this example we are doing a manual upgrade from 19c non-CDB to 21c. 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.

Remember, this article is not a replacement for reading the upgrade documentation. Each upgrade has the potential to be different, depending on what options are installed.

Prerequisites

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

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.

There are two settings necessary for Replay Upgrade to work, but they are the defaults, so we don't need to set these manually.

alter database upgrade sync on;
alter database property set upgrade_pdb_on_open='true';

Create PDB From non-CDB

It would make sense to run the AutoUpgrade analyze before trying the upgrade, to make sure there aren't any issues that might need fixing before you continue. You can read about those here.

We switch the non-CDB instance to read-only mode, describe the database using the DBMS_PDB package, then shutdown the non-CDB instance.

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=mydb

sqlplus / as sysdba <<EOF
shutdown immediate;
startup open read only;

begin
  dbms_pdb.describe(
    pdb_descr_file => '/tmp/mydb.xml');
end;
/

shutdown immediate;

exit;
EOF

We can see there are no user-defined PDBs in the root container.

export ORACLE_HOME=$ORACLE_BASE/product/21.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=cdb1

sqlplus / as sysdba


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
SQL>

We create a pluggable database from the non-CDB database using the "/tmp/mydb.xml" file. We are using the COPY option, which copies the datafiles to create the new PDB. We could use the MOVE option, which would use the existing files to save space.

create pluggable database mydb using '/tmp/mydb.xml' copy;

We see the following messages in alert log.

2021-08-21T09:59:43.993485+00:00
create pluggable database mydb using '/tmp/mydb.xml' copy
2021-08-21T09:59:44.027947+00:00
Opatch validation is skipped for PDB MYDB (con_id=4)
2021-08-21T09:59:52.674090+00:00
MYDB(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database MYDB with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000009
****************************************************************
MYDB(4):Pluggable database MYDB pseudo opening
MYDB(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
MYDB(4):Autotune of undo retention is turned on.
MYDB(4):Undo initialization recovery: Parallel FPTR complete: start:5860713 end:5860715 diff:2 ms (0.0 seconds)
MYDB(4):Undo initialization recovery: err:0 start: 5860709 end: 5860715 diff: 6 ms (0.0 seconds)
MYDB(4):[18913] Successfully onlined Undo Tablespace 2.
MYDB(4):Undo initialization online undo segments: err:0 start: 5860715 end: 5860764 diff: 49 ms (0.0 seconds)
MYDB(4):Undo initialization finished serial:0 start:5860709 end:5860768 diff:59 ms (0.1 seconds)
MYDB(4):Database Characterset for MYDB is AL32UTF8
MYDB(4):Pluggable database MYDB pseudo closing
MYDB(4):JIT: pid 18913 requesting stop
MYDB(4):Closing sequence subsystem (5860811822).
MYDB(4):Buffer Cache flush started: 4
MYDB(4):Buffer Cache flush finished: 4
Completed: create pluggable database mydb using '/tmp/mydb.xml' copy

Now we open the pluggable database in read-write mode, which triggers the upgrade of the PDB, and conversion from a non-CDB to a PDB. We do not need to run the "noncdb_to_pdb.sql" script for this operation. As you might expect, this upgrade and conversion takes some time to complete. In this example a little over 30 minutes.

alter pluggable database mydb open read write;

We can use the DBA_REPLAY_UPGRADE_ERRORS view to check for errors. In the alert log we see messages to indicate the upgrade and conversion are taking place.

2021-08-21T10:03:35.400676+00:00
alter pluggable database mydb open read write

...

****************************************************************
Post plug operations are now complete.
Pluggable database MYDB with pdb id - 4 is now marked as NEW.
****************************************************************

...


MYDB(4):alter pluggable database application APP$CDB$CATALOG begin upgrade
  '19.0.0.0.0' to '21.0.0.0.0.partial' on error capture
MYDB(4):Completed: alter pluggable database application APP$CDB$CATALOG begin upgrade
  '19.0.0.0.0' to '21.0.0.0.0.partial' on error capture

...


MYDB(4):SERVER ACTION=NONCDB_TO_PDB id=: Converted non-CDB to PDB in release 21.3.0.0.0 Container=MYDB Id=4
MYDB(4):alter pluggable database application app$cdb$pdbonly$ncdbtopdb end upgrade
MYDB(4):Completed: alter pluggable database application app$cdb$pdbonly$ncdbtopdb end upgrade
2021-08-21T10:31:03.540665+00:00
MYDB(4):Finished Conversion from non-CDB on PDB Open
Violations: Type: 2, Count: 1
Pluggable database MYDB opened read write
Completed: alter pluggable database mydb open read write

We can see the PDB is now open in read-write mode.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 MYDB                           READ WRITE NO
SQL>

We save the state so the PDB opens automatically with the instance.

alter pluggable database mydb save state;

It's worth checking for violations. They should all be from before the upgrade and conversion.

column name format a10
column message format a60

select time, name, message from pdb_plug_in_violations order by time;

Run datapatch for the PDB.

cd $ORACLE_HOME/OPatch
./datapatch -pdbs MYDB

Final Steps

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

export ORACLE_HOME=$ORACLE_BASE/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

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.