By Mouhamadou Diaw
With Oracle 20c, the non-multitenant architecture is no longer supported. So, people will have to migrate their databases to container if they want to use Oracle 20c. There are many methods to transform a non-cdb database to a pluggable one.
-Datapump
-Full Trabsportable Tablespaces
-Plugging non-cdb database , upgrade the plugged database and then convert
-Upgrading the non-cdb database, then plug it the container and then convert it ( But I am not sure that this method will work with Oracle 20c as there is non-cdb architecture)
We can find useful information about these methods in Oracle documentation and on Mike Dietrich blogs
In this blog I am going to use the method plugging the database to migrate a non-cdb Oracle 19c database prod19
1
2
3
4
5
6
7
8
9
10
11
|
********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : prod19 OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : NO VERSION : 19.0.0.0.0 CDB Enabled : NO ************************************* |
into an Oracle 20c container database prod20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : prod20 OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : YES FORCE_LOGGING : YES VERSION : 20.0.0.0.0 CDB Enabled : YES List PDB(s) READ ONLY : PDB$SEED List PDB(s) READ WRITE : PDB1 ************************************* |
The first step is to open the source database on READ-ONLY mode and then generate the metadata xml file of the non-cdb prod19 database using dbms_pdb.describe procedure.
1
2
3
4
5
|
SQL> exec DBMS_PDB.DESCRIBE( '/home/oracle/upgrade/prod19.xml' ); Procedure PL/SQL terminee avec succes. SQL> |
The generated xml file is used to plug the non-cdb database into the container prod20. But before plugging the database I run the following script to detect eventual errors
1
2
3
4
5
6
7
8
|
DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/upgrade/prod19.xml' , pdb_name => 'prod19' ) WHEN TRUE THEN 'YES' ELSE 'NO' END ; BEGIN DBMS_OUTPUT.PUT_LINE( 'Is the future PDB compatible? ==> ' || compatible); END ; / |
When querying PDB_PLUG_IN_VIOLATIONS, I can see following error
1
2
|
PROD19 ERROR PDB 's version does not match CDB' s version: PDB 's version 19.0.0.0.0. CDB' s version 20.0.0.0.0. |
But as explained by Mike Dietrich in his blog I ignore the error and then plug prod19 into the CDB prod20
1
2
3
|
SQL> create pluggable database prod18 using '/home/oracle/upgrade/prod19.xml' file_name_convert=( '/u01/app/oracle/oradata/PROD19/' , '/u01/app/oracle/oradata/PROD20/prod19/' ); Base de donnees pluggable creee. |
At this state the database prod19 is plugged into the container prod20, but need to be upgraded to Oracle 20.
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PROD19 MOUNTED SQL> alter session set container=prod19 ; Session modifiee. SQL> startup upgrade Base de donnees pluggable ouverte. |
And we can now upgrade the PDB prod19 using dbupgrade
1
|
oracle@oraadserverupgde:/home/oracle/ [prod20 (CDB$ROOT)] dbupgrade -l /home/oracle/logs -c prod19 |
A few minutes after, the upgrade is finished. Below some truncated output
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
... ... ------------------------------------------------------ Phases [0-106] End Time :[2020_05_01 17:13:42] Container Lists Inclusion:[PROD19] Exclusion:[NONE] ------------------------------------------------------ Grand Total Time : 1326s [PROD19] LOG FILES: (/home/oracle/upgrade/log//catupgrdprod19*.log) Upgrade Summary Report Located in : /home/oracle/upgrade/log//upg_summary.log Time : 1411s For PDB(s) Grand Total Time : 1411s LOG FILES: (/home/oracle/upgrade/log//catupgrd*.log) Grand Total Upgrade Time : [0d:0h:23m:31s] oracle@oraadserverupgde:/home/oracle/ [prod20 (CDB$ROOT)] |
Having a quick check to log files to see if all was fine during the upgrade
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
oracle@oraadserverupgde:/home/oracle/upgrade/log/ [prod19] cat upg_summary.log Oracle Database Release 20 Post-Upgrade Status Tool 05-01-2020 17:13:2 Container Database : PROD20 [CON_ID: 4 => PROD19] Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server UPGRADED 20.2.0.0.0 00:11:26 JServer JAVA Virtual Machine UPGRADED 20.2.0.0.0 00:02:07 Oracle XDK UPGRADED 20.2.0.0.0 00:00:33 Oracle Database Java Packages UPGRADED 20.2.0.0.0 00:00:05 Oracle Text UPGRADED 20.2.0.0.0 00:01:02 Oracle Workspace Manager UPGRADED 20.2.0.0.0 00:00:41 Oracle Real Application Clusters UPGRADED 20.2.0.0.0 00:00:00 Oracle XML Database UPGRADED 20.2.0.0.0 00:01:57 Oracle Multimedia UPGRADED 20.2.0.0.0 00:00:39 LOCATOR UPGRADED 20.2.0.0.0 00:01:11 Datapatch 00:00:30 Final Actions 00:00:45 Post Upgrade 00:00:06 Total Upgrade Time : 00:21:14 [CON_ID: 4 => PROD19] Database time zone version is 32. It is older than current release time zone version 34. Time zone upgrade is needed using the DBMS_DST package. Grand Total Upgrade Time : [0d:0h:23m:31s] oracle@oraadserverupgde:/home/oracle/upgrade/log/ [prod19] |
After the upgrade, we have to convert prod19 to a pluggable database.
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PROD19 MOUNTED SQL> alter session set container=prod19; Session modifiee. QL> alter session set container=prod18; SQL> @?/rdbms/admin/noncdb_to_pdb.sql |
After the noncdb_to_pdb script runs successfully, the PDB prod19 can be now opened in read write mode
1
2
3
4
5
6
7
|
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PROD19 READ WRITE NO |