On Oracle Database, Multitenant feature will be turning 9 in July, but it’s not yet widely deployed as most of the databases are still running on non-CDB architecture. With 19c, Multitenant is now mature and free if you stick to a maximum of 3 PDBs per CDB. If the benefits of Multitenant are not obvious at first glance, it’s a game changer when it comes to future migrations.
Migration without Multitenant
It’s been decades that migration is done by mostly choosing between these 2 methods:
- in-place upgrade of the database files by connecting them to a new DB home and running the catalog upgrade
- export data from source DB to a dumpfile and import this dumpfile into a newly created database already running the target version
The first option is fast, as soon as you stay on the same server, but after years using this method you still keep the old database structure with its legacy configurations, which is not so good. I remember, several years ago, working on an Exadata and not being able to use modern features because database files came from an old 8i or 9i.
The second migration option is the most popular and pretty clean, but rather slow because it’s based on DDL and DML statements. And you first need to identify schemas and dependencies you’d like to migrate. It’s easier to do a full export/import operation, but it generates a lot of errors you must analyze and solve. It’s because dictionary metadata and objects metadata are mixed, and importing dictionary metadata in a new database is not possible as dictionary already exists. But DBAs get used to this.
A third option is a combination of both: exporting objects metadata only and copying selected datafiles to the new database. It’s called Transportable Tablespaces, but it also needs schemas and dependencies analysis, and it only works if every segment resides in its expected tablespace.
Purpose of a container in Multitenant
A container database (CDB) will not be as important as a non-container database (non-CDB). It’s important as long as pluggable databases (PDBs) are running inside. But as your PDBs are easily movable, at some point you will move them. And the old container becomes useless. One of the reasons of moving these PDBs is migrating them to a newer version.
This is why your container shouldn’t have a name related to the applications it runs. The PDB has its own name, and the associated service will follow the PDB when you move it to another container.
Imagine you are preparing the 23c migration of the 3 PDBs in your 19c container C1917TST. You will create a new C2301TST container, with the same settings as C1917TST, move the PDB to this new container, and your migration is done. You can then delete the old container. Let’s test this.
Testing a migration from 19c to 21c
The 23c is the next long term release, but it’s not yet available. So the tests will be done with the current innovation release: 21c.
Migrating a PDB to a newer version could be limited to unplugging the database and plugging it into a new container. In this example, source CDB is 19c, and target will be 21c.
I’m pretty sure it will work fine, but as I’m using multitenant, I can duplicate my PDB and I will safely migrate the copy instead. If something goes wrong, I still have my original PDB with its original name in the source CDB.
. oraenv <<< CDB19C1 sqlplus / as sysdba create pluggable database JRQDUP from JRQO4D; alter pluggable database JRQDUP open; ... alter pluggable database JRQDUP save state; alter pluggable database JRQDUP close immediate;
Now let’s unplug this PDB and remove it from the source CDB:
alter pluggable database JRQDUP unplug into '/home/oracle/jrqdup.xml'; drop pluggable database JRQDUP keep datafiles; exit
Let’s plug it into the new container. The MOVE option is used to move the datafiles to the correct subfolder:
. oraenv <<< CDB21C4 create pluggable database JRQDUP using '/home/oracle/jrqdup.xml' MOVE; alter pluggable database JRQDUP open; ...
Opening this PDB is not immediate, let’s have a look at what’s happening in another sqlplus session:
show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 JRQDUP MIGRATE YES
PDB is first opened in restricted mode to do the upgrade of the metadata.
Once finished, my PDB is automatically opened in normal mode:
show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 JRQDUP READ WRITE NO
And that’s it. Upgrade of the PDB is automatic when opening the database.
Next step would be stopping and dropping the source PDB and renaming the new one if you want to keep its original name.
With Multitenant, it’s very easy to migrate to a newer version. Just unplug the PDB you want to migrate from the old container and plug it into the new one, and Oracle will do the migration job.