With the announcement of 26ai on premises, many people (myself included) using 19c will be looking to migrate their existing databases over to 26ai. As announced earlier, 19c is the last release in which the option to use a non-CDB is allowed; from 21c onwards, it’s mandatory to change your database architecture to multitenant. Therefore, anyone using a 19c non-CDB must migrate to a CDB before upgrading (using autoupgrade). There are many other methods I may not be aware of (kindly comment on them). There is also the opportunity to export and import data using Data Pump from a non-CDB on 19c to a 23c/26ai PDB. This article describes how to create a multitenant or container database with a pdb from a non-CDB using the DBMS_PDB method, perhaps in preparation for upgrading to Oracle Database 26ai.
Note: this blog describes doing this for single-instance installations only
Firstly, you will need to create a CDB if you do not already have one. This can be done using the DBCA utility.
I will create a new CDB on 19c called CDB1 and migrate/plug my non cdb called ORCL into it.
Step 1: Create a new CDB using DBCA
- Launch dbca from the command line using the command dbca
- Select Create a Database, click Next, select Advanced Configuration, and click Next.
- On the next page, select Oracle single-instance database for the database type and general or transaction processing for the template, then click Next.
- global database name set as cdb1, create no pdb
- Select storage option
- Accept defaults for fast recovery area, network configuration & data vault option.
- Select Automatic shared memory management.
- uncheck configure enterprise manager (em) database express
- Set the same password for both system administrative accounts
- Select Create Database and click Next, verify parameters, and click Finish:
Once this is done, and assuming you have an existing 19c database, here are the steps to migrate to a CDB from a non-CDB
Step 2: Extract the XML file from the Non-CDB
steps
a) connect to the non-CDB as sys, shut it down, and start it up in read-only mode
As mentioned earlier, I will be migrating data from a pre-existing non-CDB database called ORCL
To extract the XML file containing all the information needed to plug in this non-CDB as a PDB, the Database must be open in read-only mode. The code below can be used to accomplish this from an already running Oracle instance.
sqlplus / as sysdba;
shutdown immediate;
startup mount;
alter database open read only;If the instance is shut down, then we start it up in mount mode and open it in read-only mode as shown below.
b) execute the DBMS_PDB.DESCRIBE procedure, providing the file name that will be generated.
The resulting XML file contains a list of data files to be plugged in, along with other metadata about the source database. The XML file and the metadata it describes comprise an unplugged PDB. The following commands can be used to accomplish this:
EXEC DBMS_PDB.DESCRIBE ('/tmp/ORCL.xml');Step 3: Check Violations
Connect to the CDB root as a common user with the CREATE PLUGGABLE DATABASE privilege, such as a sysdba or similar privileged user, and run the CHECK PLUG COMPATIBILITY procedure.
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT BOOLEAN := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/tmp/ORCL.xml',
pdb_name => 'ORCL');
BEGIN
IF compatible THEN
DBMS_OUTPUT.PUT_LINE('Compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('Incompatible');
END IF;
END;
/
If the PDB is not compatible, violations are listed in the PDB_PLUG_IN_VIOLATIONS view. If the PDB is compatible, then we proceed to create a new PDB ORCL inside CDB1 using our XML as the source.
Step 4: Plug the PDB into CDB
For this step, depending on your setup, you need to know the location of your data files for the source database and where you want them to be, whether to move, copy, or create new ones. I created a new directory for the new database datafiles and set the permissions as follows:
[oracle@localhost home]$ ll /u01/app/oracle/oradata/ORCL/datafile/
total 2988844
-rw-r-----. 1 oracle oinstall 1216356352 Apr 30 05:07 o1_mf_sysaux_kyvgt8qh_.dbf
-rw-r-----. 1 oracle oinstall 1184899072 Apr 30 05:07 o1_mf_system_kyvgshkq_.dbf
-rw-r-----. 1 oracle oinstall 173023232 Mar 5 21:01 o1_mf_temp_kyvgw8j5_.tmp
-rw-r-----. 1 oracle oinstall 450895872 Apr 30 05:07 o1_mf_undotbs1_kyvgtr0p_.dbf
-rw-r-----. 1 oracle oinstall 27533312 Apr 30 05:07 o1_mf_users_kyvgts1z_.dbf
-rw-r-----. 1 oracle oinstall 10493952 Apr 30 05:07 o1_mf_users_m8ds2jxz_.dbf
[oracle@localhost home]$
[oracle@localhost home]$ mkdir -p /u01/app/oracle/oradata/CDB1/ORCL
[oracle@localhost home]$
[oracle@localhost home]$ ll -d /u01/app/oracle/oradata/ORCL/datafile/
drwxr-x---. 2 oracle oinstall 208 Jul 4 2024 /u01/app/oracle/oradata/ORCL/datafile/
[oracle@localhost home]$
[oracle@localhost home]$ chmod 720 /u01/app/oracle/oradata/CDB1/ORCL
[oracle@localhost home]$
[oracle@localhost home]$ ll -d /u01/app/oracle/oradata/CDB1/ORCL
drwx-w----. 2 oracle oinstall 6 Apr 30 05:44 /u01/app/oracle/oradata/CDB1/ORCL
[oracle@localhost home]$
[oracle@localhost home]$
Connect as a privileged user in the CDB and use the CREATE PLUGGABLE DATABASE command to create the pdb. See the docs for a full list of command options.
CREATE PLUGGABLE DATABASE ORCL
USING '/tmp/ORCL.xml'
MOVE
FILE_NAME_CONVERT = (
'/u01/app/oracle/oradata/ORCL/datafile/',
'/u01/app/oracle/oradata/CDB1/ORCL'
)
TEMPFILE REUSE;
After the PDB is successfully created, the next step is to switch to the PDB and run the
$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script
to delete unnecessary metadata from the system tablespace and convert the local data dictionary of the standalone Database into a PDB-compatible format.
ALTER SESSION SET CONTAINER = ORCL;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sqlThis script is required for plugging non-CDBs only and must be run before the PDB is opened for the first time.
Step 5: Open the PDB to verify the application tables
After running the noncdb_to_pdb script, our PDB is mounted. We can verify our connection and pdb details using:
show con_name --show current container_name
show con_id --show container_id, typically 3 or greater for pdbs, 1 is for rootcdb, 2 for seedpdb
show pdbs -- displays pdb details id, name and open_mode
SELECT con_id, name, open_mode FROM v$pdbs; -- displays pdb details id, name and open_mode
The PDBs are created with the status of ‘NEW’. They must be opened in READ WRITE mode at least once for the PDB integration into the CDB to be complete. We can open the pdb using.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCL MOUNTED
SQL>
SQL>
SQL>
SQL> ALTER PLUGGABLE DATABASE ORCL OPEN READ WRITE;
Pluggable database altered.
We can check the status again.
COLUMN pdb_name FORMAT A20
SQL> select pdb_name, status from dba_pdbs;
PDB_NAME STATUS
-------------------- ----------
ORCL NORMAL
selectCOLUMN name FORMAT A20
SQL> select name, open_mode
2 from v$pdbs ;
NAME OPEN_MODE
-------------------- ----------
ORCL READ WRITE
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCL READ WRITE NO
SQL>
SQL>
Verify Users and Data
All the data from the pre-existing non-CDB should be available in the new CDB
SQL> select username
2 from dba_users
3 where oracle_maintained = 'N'
4 order by username ;
USERNAME
--------------------------------------------------------------------------------
HR
JUSTLEE
ORACLE_DP_USER
SCOTT
4 rows selected.
col owner for a20
SQL> select owner, object_type, count(object_name) number_of_object_type
from dba_objects
where owner in (select username
from dba_users
where oracle_maintained = 'N' )
GROUP BY owner, object_type
ORDER BY 3 DESC
/
OWNER OBJECT_TYPE NUMBER_OF_OBJECT_TYPE
-------------------- ----------------------- ---------------------
HR INDEX 22
JUSTLEE TABLE 16
HR TABLE 15
JUSTLEE INDEX 13
SCOTT TABLE 4
HR FUNCTION 3
HR SEQUENCE 3
HR PROCEDURE 2
HR LOB 2
SCOTT INDEX 2
HR TRIGGER 2
HR VIEW 1
JUSTLEE PROCEDURE 1
JUSTLEE FUNCTION 1
JUSTLEE LOB 1
HR PACKAGE 1
You can check for any objects and the relevant data, test connections, verify the location of the datafiles, etc., as required to confirm a successful Migration. At this point, the migration is complete, and you have a new, fully functioning pdb inside a CDB.
CONCLUSION
This post walked through the process of converting a 19c non-CDB database into a pluggable database (PDB) within a multitenant container database (CDB) using the DBMS_PDB method. The key steps included generating the XML file, validating compatibility, plugging the database into a CDB, and running the noncdb_to_pdb.sql script to complete the conversion. Hopefully, this post showed you how easy it is to get a new CDB with a PDB up and running, and perhaps got you one step closer to the new day. Kindly leave a comment, and thanks for reading.
Cheers, Harris