3 Ways to Migrate a Non-CDB Database to a PDB

Hi Everyone,

Time is fast and everything is changing constantly. Multi-tenant architecture has come many years ago and starting by 21c, it is the only option! So,  old “non-cdb” databases must be migrated/converted to a PDB (Pluggable database). I must admin that I was being lazy for many years and I didn’t work much to learn new things and now I am literally trying to learn everything. One of the first goal was about multi-tenant architecture and now, since I feel that I am confident enough about it, I will move my clients’ databases to multi-tenant architecture. Also, I must say, I loved multi-tenant architecture.

I won’t be going details about what multi-tenant or a pdb is in this post. I will cover only Migration / Conversion.

So, for a long time I am testing about migrating noncdb databases to pdb and I want to explain 3 main methods to do that.

• Plugging in a noncdb as pdb
• Creating a pdb from noncdb over a database link (dblink)
• Autoupgrade

Methods like export import can be used of course but I don’t think that can be count as a “migrate”. So, I won’t be talk about it in this post. Let’s start then.

Environment

For all 3 methods same environment is used: Oracle Linux 8.5 (virtual box), Oracle Database 19.14 Enterprise Edition. (I will add changes on 21c in my post)

Noncdb Database SID        : orcl
Multi-tenant Database SID: cdb

Both databases are in same home (could be in different homes, that would change nothing).

I always use OMF so I won’t be using FILE_PATH_CONVERT parameter in my examples. if you don’t use OMF (I don’t know why you do that) then you must use file_path_convert and point necessary directories.

I put around 4.5 GB of data in orcl (noncdb) database because amount of data will affect the completion time of the process.

By the way, I will suppress some outputs to keep it short and emphasis real results.

Pre Steps

Steps that I am about to show you should (must to me) do whatever method you are using to migrate your database. You must always check if noncdb database is applicable as a pdb. To do that, you will use simple pdb plug in method basically. Of course in this case, we cannot “unplug” our noncdb database because it is not a pdb, so what?

As you know, you can plug in and unplug a PDB to/from a container database in multi-tenant architecture. this is the coolest option I think. As if you are plugging in a pdb, you can plug in a noncdb as a PDB. While plugging in a pdb, we need an XML file that defines properties and necessary information about the pdb like compatibility, nls info, data file locations etc. To plug in a noncdb, we will need that file as well. Oracle already embedded a package to achieve that. DBMS_PDB. Describe procedure in this package will create XML file that we need.

if you are going to use PLUG IN method then put your source database (non-cdb database) into transactionally consistent state which means open it in READ ONLY mode. Reason is XML file contains some SCN information too but if db is up and running scn will move. When you tried to plug in database, you will get an error about data file scn number and config file (XML file) does not match. Other 2 methods doesn’t require that! I personally prefer this in db link method too. I don’t want any process keep doing something while converting a noncdb to pdb.

for plug in method:

I connect to “orcl” database and create necessary xml file:

Now we have an xml file that defines our noncdb database.

Then, check if this is a compatible database with our container database. Connect to CDB database and check the xml file:

DBMS_PDB has a function called “check_plug_compatibility”. this function reads XML file and returns true or false. if true then you can plug this noncdb as pdb (maybe not so fast). if result is false then you must check what is wrong? even if result of this function is true you must check “violations”. every CDB database has a data dictionary view called PDB_PLUG_IN_VIOLATIONS. After running “check_plug_compatibility” function, errors/warnings/infos will be written to this view and you must check them before plugging in the database.

if you see any ERROR in this view you must not proceed until solving the problem.  You might see various problems here like different database parameters like open_cursors could be 500 on noncdb but 300 on cdb. remember, cdb parameter values will override previous parameters.  (I will explain “PDB plugged in is a non-CDB, requires noncdb_to_pldb.sql be run” warning later)

Some important points are DB OPTIONS. Your databases might have different options. Let’s say your noncdb database has SPATIAL option installed but it is not installed on CDB. if so, then you must install SPATIAL option before plug in the database. how to install options on a database? You can check my previous post about it:  https://mustafakalayci.me/2022/03/03/how-to-install-oracle-database-options-like-spatial-label-security-etc-after-db-creation/

As a rule, your non-cdb database might have equal or less than compatible parameter value. if this is the situation you will see a row in PDB_PLUG_IN_VIOLATIONS.

if you resolve a problem on PDB_PLUG_IN_VIOLATIONS, it will still be there. even so, after solving a “warning” problem (like installing an option) will be listed as PENDING not resolved. it requires open the pluggable database.

As I said, these steps should be mandatory to you before migrating non-cdb to pdb. after checking violations, you can proceed to plug in operation.

1- Plugging in a Noncdb as PDB

At this point we should have checked the xml file and solved the violation problems. Please remember you should have created the XML file when noncdb database in READ ONLY mode!

Now, we have XML file so we can plug in noncdb as a pdb. Before do that, close source database (noncdb).

then connect to CDB and create a pluggable database via plug in:

I don’t want to go to detail of create PDB via plug in but as a summary; you can use a different PDB name. instead of orcl, you can use orcl_pdb for example. Also, default mode for plugging in a pdb is COPY which means it will copy the datafiles that orcl.xml pointed out (they are under /u01/app/oracle/oradata/orcl directory currently). PLUG IN method will copy them under /u01/app/oracle/cdb directory (wherever db_create_file_dest is).  this way, noncdb database (orcl) will be still available (it is in shutdown state).

it takes 10 seconds to complete on my laptop. as I said before it has 4.5GB extra dummy data in this database. total size is around 7.5GB. 10 seconds is the time of copying datafiles to a new location. if you use MOVE instead of COPY method it will take much less time.

as you see it only takes 0.68 seconds. size of data is not matter now because data files are MOVEd to another location and this is a very fast operation. even if you have 1TB of data it will be completed in seconds. Also, if you are satisfied where datafiles reside currently, you can use NOCOPY option too.

if you use same with noncdb database then you must sure that noncdb is closed because there will be 2 services with same name.

you can use this method even if your databases are on different servers. just create XML file on noncdb database, then move datafiles and XML file to new server, modify XML file because file locations might be changed and wherever you copy the data files, change locations in XML file to new location and then run create pluggable database command.

Always check PDB_PLUG_IN_VIOLATIONS view after creating PDB because there might be new issues.

Now we have a new pdb called orcl:

NOTE: Another amazing option we have is we can create a PDB from a lower version noncdb. for example, if you have a 12.2 noncdb database, just install 19c and follow the same steps. Cool right? there is only one thing to do! before calling noncdb_to_pdb.sql file run dbupgrade -c “ORCL” command on shell. dbupgrade is kind of a shortcut for catupgrd.sql file. this will upgrade your newly created pluggable database first.

as you see it is in MOUNTED mode which means it is closed but we CANNOT open it yet on 19c database. For 19c, we must connect to newly created pdb and run noncdb_to_pdb.sql script which is under $ORACLE_HOME/rdbms/admin

it will take a while. as I said, don’t open pdb. set your container as new pdb then run noncdb_to_pdb.sql file. question mark means ORACLE_HOME in sqlplus. so I basically called @/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/noncdb_to_pdb.sql

21c UPDATE: After plugging in (create pluggable database) command, you don’t need to run dbupgrade (if noncdb is a lower db version) or noncdb_to_pdb.sql scripts. when you run ALTER PLUGGABLE DATABASE OPEN command, 21c will automatically run them but open command, of course, will take some time to complete.

That is it.

2- Create PDB from Non-CDB via DBLINK

This step is much easier for an Admin but takes much more time to complete. Good thing about this method is your source DB can stay open during the operation and when pdb creation is completed, it will apply archivelog of noncdb database. So, PDB will be transactionally consistent at the time of END of the pdb creation.

Do the PRE STEPs first as described above. create XML file and check for the violations. if everything is OK then, you can proceed.

This method just requires a database link (dblink) from CDB to NONCDB. Dblink must be created at the cdb$root container. First create a user on noncdb database which will be use to connect in dblink.

Remember, this user must have resource, connect and create pluggable database privileges. after that create database link on CDB:

from now on we will create a pdb from a remote link:

it completed in 10 seconds. if source database was bigger it would take much more time. Since non-cdb has no PDB name, we use NON$CDB.

as I explained in previous methods, for 19c, if you created pdb from a lower version run dbupgrade command. after that, you must set current container as new pdb and run noncdb_to_pdb.sql file.

if this is a 21c you can simply open pdb. 21c will do the job but open command will take some time.

I am not sure if this is supported (I did not checked yet but test it of course). I use this method to create a PDB from a noncdb which is on a different server and different OS and it worked!

3- Autoupgrade

New King of the upgrades/convertions/migrations 🙂 Autoupgrade is an amazing tool and support many options now. Even, I have just read that version 2 is released while I was writing this post on Mike Dietrich’s blog ( https://mikedietrichde.com/2022/03/25/autoupgrade-2-0-has-been-released-and-got-reuploaded/ )

You can use autoupgrade to convert a noncdb database to pdb. Autoupgrade will take care of everything even pre steps and post steps. It will even run the fix scripts.

I still would like to do my “Pre STEPS” to check the operation.

autoupgrade is a jar file and requires java to run but no worries, everything you need is in the ORACLE_HOME. Before take an action, always download the latest version of autoupgrade.jar file from https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1 it is a single jar file and put it under $ORACLE_HOME/rdbms/admin for 19c and above.

autoupgrade has different modes and I won’t detail all of them here. What we use here is ANALYZE and DEPLOY modes. ANALYZE mode will run all the steps just skipping the actual operations and will show you a result. After that you can run it in DEPLOY mode which will do the actual job. autoupgrade requires a config file to identify what to do and necessary info. here is my sample:

it is quite self explanatory. source is upg1.sid which is noncdb and upg1.target_cdb is cdb which autoupgrade understands this is a noncdb to pdb conversion. you can give a new name for your pdb (my_pdb in my example). define a start time, set if a timezone upgrade will be proceed etc. I saved this file under /home/oracle/noncdb_to_pdb.cfg.

EDIT: I forgot to mention about something. By default, autoupgrade will remove your noncdb! it is creating PDB with MOVE option so datafiles of noncdb are gone. if you want to prevent this, use KEEP_SOURCE_PDB=yes in your config file like:

upg1.keed_source_pdb=yes

Before Running Autoupgrade : This is an important note. it might be solved already because autoupgrade team is really fast but I had some problems while doing that because of glogin.sql file. for details: https://mustafakalayci.me/2022/03/20/666/ thanks to Mike Dietrich and Joseph Errede, they take care of it really fast.

just run autoupgrade in analyze mode first:

then check the result and run it in DEPLOY mode. Deploy mode will start a CLI (command line interface) so you can watch the steps by typing “lsj” command:

When operation is over it will automatically exit the cli and will print the log file locations. as you see in my example, it already started noncdb_to_pdb.sql file. Yes, all of those steps are automatic in autoupgrade.

here is the end:

here is the html file screen shot:

that’s it.