Oracle 12c New Features

Online Oracle Training

LESSON 10

How to merge two CDBs

Let us assume that you have another CDB named "old_mycdb" and you want to merge all PDBs of old_mycdb into the created CDB (mycdb).

Steps to merge:

1/ unplug all PDBs of the old_mycdb CDB.

2/ Drop all PDBs but keep their data files.

3/ Check the PDBs of old_mycdb are compatible with the mycdb CDB.

4/ Go to the mycdb CDB and plug the PDBs.

Step 1:

Unplug all PDBs of old_mycdb

Connect to the multitenant container database old_mycdb to unplug all PDBs.

Run its profile.

# . ./.profile

Login to the old_mycdb CDB to unplug all PDBs

# sqlplus /nolog

SQL> connect / as sysdba

Now, list the V$PDBS view to check pluggable databases in the multitenant container old_mycdb

SQL> SELECT con_id, name, open_mode FROM v$pdbs;

Since the OLD_MYPDB1 and OLD_MYPDB2 pluggable databases are in the READ WRITE mode, we should close the PDBs first. Then unplug them.

SQL> ALTER PLUGGABLE DATABASE old_mypdb1 CLOSE IMMEDIATE;

SQL> ALTER PLUGGABLE DATABASE old_mypdb2 CLOSE IMMEDIATE;

Now, you can unplug each pluggable database into an xml file.

SQL> ALTER PLUGGABLE DATABASE old_mypdb1

   2   UNPLUG INTO "/oradata/old_mycdb/xml_old_mypdb1.xml";

SQL> ALTER PLUGGABLE DATABASE old_mypdb2

   2   UNPLUG INTO "/oradata/old_mycdb/xml_old_mypdb2.xml";

Step 2:

Drop all PDBs but keep their data files.

Now, list the CDB_PDBS view to check pluggable databases in the multitenant container old_mycdb were unplugged.

SQL> SELECT pdb_name, status FROM cdb_pdbs

   2   WHERE pdb_name like "OLD%";

Drop them but keep their data files.

SQL> DROP PLUGGABLE DATABASE old_mypdb1 KEEP DATAFILES;

SQL> DROP PLUGGABLE DATABASE old_mypdb2 KEEP DATAFILES;

Step 3:

Check the PDBs of old_mycdb are compatible with the mycdb CDB.

Login to the multitenant container mycdb and see whether the unplugged databases are compatible with it using DBMS_PDB.CHECK_PLUG_COMPATIBILITY function.

Run the mycdb profile and then connect to the mycdb multitenant container and run the following procedure.

SQL> connect / as sysdba

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

   2   v_ok BOOLEAN := FALSE;

   3   BEGIN

   4      v_ok := DBMS_PDB. CHECK_PLUG_COMPATIBILITY(

   5      pdb_descr_file=> "/oradata/old_mycdb/xml_old_mypdb1.xml",pdb_name=>"old_mypdb1");

   6      IF v_ok THEN

   7         DBMS_OUTPUT.PUT_LINE("Okay! It is compatible.");

   8      ELSE

   9         DBMS_OUTPUT.PUT_LINE("It is not compatible.");

  10    END IF;

  11   END;

  12   /

You should get "Okay! It is compatible "

If your SGA_TARGET parameter mismatch, you may also get "It is not compatible " To make sure use the PDB_PLUG_IN_VIOLATIONS view.

SQL> SELECT message, action FROM PDB_PLUG_IN_VIOLATIONS

   2   WHERE name="OLD_MYPDB1";

Any memory messages will have no impact if you create the PDBs. You can ignore them!

Step 4:

Go to the mycdb CDB and plug the PDBs.

SQL> CREATE PLUGGABLE DATABASE old_mypdb1

   2   USING "/oradata/old_mycdb/xml_old_mypdb1.xml" NOCOPY;

SQL> CREATE PLUGGABLE DATABASE old_mypdb2

   2   USING "/oradata/old_mycdb/xml_old_mypdb2.xml" NOCOPY;

We used the NOCOPY option because we have no intention to moving them where they are.

Now, query the V$PDBS view.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

Notice that created PDBs are in the MOUNTED mode. You need to open them.

Open the OLD_MYPDB1 and OLD_MYPDB2 pluggable databases.

SQL> ALTER PLUGGABLE DATABASE old_mypdb1 OPEN;

SQL> ALTER PLUGGABLE DATABASE old_mypdb2 OPEN;

List the V$PDBS view again.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

Good Luck!