Database 12c: Get back from PDB to Non-CDB

I’vh written many articles about converting a Non-CDB to a PDB. I’m going to check the way back to a Non-CDB now.

First check what a convert to PDB is. The plug-in process of a Non-CDB consists of two parts:

  1. (PHYSICAL) Migration of the necessary datafiles
  2. (LOGICAL) Conversion of the data dictionary with the noncdb_to_pdb.sql script

How it works?

“The non-CDB’s datafiles, together with the manually created manifest, can now be treated as
if they were an ordinarily unplugged PDB and simply plugged in to the target CDB and
opened as a PDB. However, as will be explained immediately, it must be opened with the
Restricted status set to YES. The tablespaces holding quota-consuming data are immediately
viable, just as if this had been a Data Pump import using transportable tablespaces. However,
the former non-CDB’s data dictionary so far has a full description of the Oracle system. This
is now superfluous, and so it must simply be removed. While still with the Restricted status set
to YES, the noncdb_to_pdb.sql script (found on the admin_directory under Oracle Home) must be
run. Now the PDB may be closed and then opened ordinarily.” –> Found here

The seconds step make all physical revert step impossible, because there is datafile 1 or real SYSTEM tablespace any more. These steps are:

  1. RMAN Full or Point-in-time-recovery will restore the whole CDB and not only the PDB. Further this is no PDB to Non-CDB conversion
  2. Transportable Database is not usable because datafile 1 is not the real SYSTEM tablespace

All in all the root problem is, that no datafile 1 or independent SYSTEM tablespace exists any more.

And now the ways to get back:

  1. Transportable Tablespace (this is the smoothest way)
  2. DataPump full export/import
  3. Other logical migration ways like Oracle GoldenGate

I’vh tested old export tool (exp) too, but exporting a PDB will end with the error “EXP-00062: invalid source statements for an object type”

If there are more ways I will appreciate a comment 🙂