iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

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;

 

CON_ID

NAME

OPEN_MODE

2

PDB$SEED

READ ONLY

3

OLD_MYPDB1

READ WRITE

4

OLD_MYPDB2

READ WRITE

 

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;

 

PDB_NAME

STATUS

OLD_MYPDB1

UNPLUGGED

OLD_MYPDB2

UNPLUGGED

 

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;

 

CON_ID

NAME

OPEN_MODE

DBID

2

PDB$SEED

READ ONLY

3654327692

3

MYPDB1

READ WRITE

6783241652

4

MYPDB2

READ WRITE

7549320153

5

MYPDB2_CLONE

READ WRITE

8734290212

6

PDB_MYDBS

READ WRITE

2378654330

7

OLD_MYPDB1

MOUNTED

7865403211

8

OLD_MYPDB2

MOUNTED

2213429876

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;

 

CON_ID

NAME

OPEN_MODE

DBID

2

PDB$SEED

READ ONLY

3654327692

3

MYPDB1

READ WRITE

6783241652

4

MYPDB2

READ WRITE

7549320153

5

MYPDB2_CLONE

READ WRITE

8734290212

6

PDB_MYDBS

READ WRITE

2378654330

7

OLD_MYPDB1

READ WRITE

7865403211

8

OLD_MYPDB2

READ WRITE

2213429876

 

Good Luck!

 

 

Google
 
Web web site