HOL 19c Plugin UPGR into CDB2


In this part of the Hands-On Lab you will now plugin UPGR into CDB2.

We could have done this with AutpUpgrade already – you can see this in the OPTIONAL AutoUpgrade exercise (Parameter: target_cdb=CDB2). But we rather decided that you should do these steps manually to understand the implications.

CDB2 is a Multitenant Container database.
And UPGR will be converted into a PDB, and then become a pluggable database.

The key is, that – in order to plugin a non-CDB such as the UPGR database – it has to be upgraded first to the same release as the CDB it gets plugged into.


1. Preparation UPGR as non-CDB

Switch to the UPGR database in 18c environment:

. upgr19
sqlplus / as sysdba

Shutdown UPGR and start it up read only:

shutdown immediate
startup open read only;

Create the XML manifest file describing UPGR’s layout and information:

exec DBMS_PDB.DESCRIBE('/home/oracle/pdb1.xml');

Shutdown UPGR:

shutdown immediate

Switch to CDB2:

. cdb2
sqlplus / as sysdba

2. Compatibility check

Ideally you do a compatibility check before you plugin finding out about potential issues. This step is not mandatory but recommended. The check will give you YES or NO.

Compatibility check:

set serveroutput on

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/pdb1.xml', pdb_name => 'PDB1') WHEN TRUE THEN 'YES' ELSE 'NO'
DBMS_OUTPUT.PUT_LINE('Is the future PDB compatible? ==> ' || compatible);

If the result is “NO” (and it is NO very often), then don’t be in panic.

In this case, the result should be “YES“.

3. Plugin Operation

Plugin UPGR with its new name PDB1 – from this point there’s no UPGR database anymore. In a real world environment, you would have a backup or use a backup/copy to plug in. In our lab the database UPGR will stay in place and become PDB1 as part of CDB2.

Please use the proposed naming as the FILE_NAME_CONVERT parameter and TNS setup have been done already.
Use the NOCOPY option for this lab to avoid additional copy time and disk space consumption. The show pdbs command will display you all existing PDBs in this CDB2.

create pluggable database PDB1 using '/home/oracle/pdb1.xml' nocopy tempfile reuse;
show pdbs

As you couldn’t do a compatibility check beforehand, you’ll open the PDB now and you will recognize that it opens only with errors.

alter pluggable database PDB1 open;

Find out what the issue is:

column message format a50
column status format a9
column type format a9
column con_id format 9

select con_id, type, message, status from PDB_PLUG_IN_VIOLATIONS
where status<>'RESOLVED' order by time;

As you can see, a lot of the reported issues aren’t really issues. This is a known issue. Only in the case you see ERROR in the first column you need to solve it.

The only real ERROR says:

PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.

Kick off this sanity script to adjust UPGR and make it a “real” pluggable database PDB1 with noncdb_to_pdb.sql. Runtime will vary between 10-20 minutes. Take a break while it is running. The forced recompilation takes quite a bit.

alter session set container=PDB1;

Now SAVE STATE. This ensures, that PDB1 will be opened automatically whenever you restart CDB2. Before you must restart the PDB as otherwise it opens only in RESTRICTED mode.

alter pluggable database PDB1 save state;
alter session set container=CDB$ROOT;
show pdbs

Try to connect directly to PDB1 – notice that you can’t just connect without specifying the service name as PDB1 is not visible on the OS level.

sqlplus "sys/oracle@pdb1 as sysdba"


As alternative you could also use the EZconnect (speak: Easy Connect)

sqlplus "sys/oracle@//localhost:1521/pdb1 as sysdba"


Share this: