How to convert a non-cdb database to cdb database using metadata in form xml file?
This is probably very first question every dba will think when he/she will be given a task to find out the process for migrating a non-cdb database and plug in into a cdb database. Below steps will explain how to do that using a metadata file in the from of XML file.
Step 1. Create a target cdb database and apply latest RU.
Use dbca to create database and apply latest RU in database home.Verify sql registry like below
COL action_time for a30
COL action for a10
COL status for a10
select patch_id,patch_type,action,status,action_time from dba_registry_sqlpatch;
Step 2. Start source database in read only mode
SQL>shutdown immediate
SQL>startup open read only
Extract metadata from source non-cdb database in the form of XML file
BEGIN
DBMS_PDB.DESCRIBE( pdb_descr_file => 'path/ncdb.xml');
END;
/
3. Execute in target container database to find plugin violation errors
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => 'path/ncdb.xml',
pdb_name => 'NCDB')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
If output comes as YES then you are lucky enough to proceed and create pluggable database using extracted xml file.If output comes as NO then please follow next step.
4. Resolve all violation errors before plugin non-cdb database
Execute below sql for violation in target database
col name format a20
col cause format a20
col type format a20
col message format a80
select name,cause,type,message from pdb_plug_in_violations;
We can ignore where type shows as warning but error needs to be fixed before plugin
Ref:-Known Issues With PDB_PLUG_IN_VIOLATIONS (Doc ID 2288129.1)
In my case this was big list so I am just showing only errors
NAME CAUSE TYPE MESSAGE
-------------------- ------------------------------ -------------------- ------------------------------------------------------------
PDB$SEED SQL Patch ERROR Interim patch 30484981/23301661 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981)): Installed in the CDB but not in the PDB
PDB$SEED SQL Patch ERROR '19.6.0.0.0 Release_Update 1912270314' is installed in the CDB but no release updates are installed in the PDB
DBACP1 SQL Patch ERROR Interim patch 32876380/24326386 (OJVM RELEASE UPDATE: 19.12. 0.0.210720 (32876380)): Installed in the CDB but not in the PDB
DBACP1 SQL Patch ERROR Interim patch 30484981/23301661 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981)): Not installed in the CDB but installed in the PDB
DBACP1 SQL Patch ERROR '19.12.0.0.0 Release_Update 2107200205' is installed in the CDB but '19.6.0.0.0 Release_Update1912270314' is installed in the PDB
NCDB SQL Patch ERROR Interim patch 32876380/24326386 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)): Installed in the CDB but not in the PDB
NCDB SQL Patch ERROR Interim patch 30484981/23301661 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981)): Not installed in the CDB but installed the PDB
NCDB SQL Patch ERROR '19.12.0.0.0 Release_Update 2107200205' is installed in the CDB but '19.6.0.0.0 Release_Update 1912270314' is installed in the PDB
Apart from this I saw an warning related to character set mismatch because source data
database charecterset was UT8 and taget cdb database was AL32UTF8.This can be safely ignored as that restriction was removed from 12.2 onwards.See this below link
https://mikedietrichde.com/2017/10/23/can-you-select-a-pdbs-character-set/
Action:For Cause related to SQL patch run ./datapatch -verbose under $ORACLE_HOME/OPatch
Verify sql registry like below
COL action_time for a30
COL action for a10
COL status for a10
select patch_id,patch_type,action,status,action_time from dba_registry_sqlpatch;
PATCH_ID PATCH_TYPE ACTION STATUS ACTION_TIME
---------- ---------- ---------- ---------- ------------------------------
30484981 INTERIM APPLY SUCCESS 01-DEC-20 02.21.49.109411 PM
30557433 RU APPLY SUCCESS 01-DEC-20 02.21.49.101972 PM
30484981 INTERIM ROLLBACK SUCCESS 02-MAR-22 07.09.43.493816 PM
32876380 INTERIM APPLY SUCCESS 02-MAR-22 07.09.56.676145 PM
32904851 RU APPLY SUCCESS 02-MAR-22 07.09.56.653985 PM
oracle@dbac/db:$ opatch lspatches
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
From the above output we can see that binary registry is now matching with SQL registry
Now clear all pdb violation using below procedure and rerun compatibility check
exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS;
Still it was showing below 3 violations
SQL> select name,cause,type,message from pdb_plug_in_violations;
NAME CAUSE TYPE MESSAGE
------------------------------ ------------------------------ --------- --------------------------------------------------------------------------------
DBACP1 SQL Patch ERROR Interim patch 32876380/24326386 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380
)): Installed in the CDB but not in the PDB
DBACP1 SQL Patch ERROR Interim patch 30484981/23301661 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981)
): Not installed in the CDB but installed in the PDB
DBACP1 SQL Patch ERROR '19.12.0.0.0 Release_Update 2107200205' is installed in the CDB but '19.6.0.0.0
Release_Update 1912270314' is installed in the PDB
set the container and execute the procedure again
alter session set container=DBACP1;
exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS;
Now all violations are removed from pdb_pulg_in_violations
5. Plugin the database
We need to take some consideration here.If we create cdb in same server as pdb and a new file system is mounted for datafiles to be created then it will be easier for plugin.
Otherwise we need to copy all datafiles in a shred NFS and then change all paths used in xml for datafiles.
Create pluggable database cisx using 'path/ncdb.xml' copy
file_name_convert = ('path of source datafles','path of destination datafiles');
Once it is created successfully it stays in mount mode
