Plugging an Unplugged Pluggable Database

Overview

    Purpose

    This tutorial covers the steps required to plug an unplugged pluggable database (PDB) from a container database (CDB) into another CDB.

    Time to Complete

    Approximately 20 minutes

    Introduction

    You can disassociate or unplug a PDB from a CDB and reassociate or plug the PDB into the same CDB or into another CDB. This capability is suitable for the following situations:

    • You have to upgrade a PDB to the latest Oracle version, but you do not want to apply it on all PDBs. Instead of upgrading a CDB from one release to another, you can unplug a PDB from one Oracle Database release, and then plug it into a newly created CDB from a later release.
    • You want to test the performance of the CDB without a particular PDB. You unplug the PDB, test the performance without the PDB and, if necessary, replug the PDB into the CDB.
    • You want to maintain a collection of PDB “gold images” as unplugged PDBs.

    Scenario

    In this tutorial, you perform a PDB unplugging operation from a CDB. Next, you perform a plugging operation of the same PDB into another CDB by using SQL*Plus.

    Different plugging scenarios are allowed:

    • Plug the unplugged PDB by using the data files of the unplugged PDB. The unplugged PDB is disassociated from the source CDB.
      • The source data files are used with or without any copy.
      • The source data files are used after being moved to another location.
    • Plug the unplugged PDB as a clone to:
      • Allow developers and testers to rapidly and repeatedly provision a well-known starting state
      • Support self-paced learning
      • Provide a new way to deliver a brand-new application

    Prerequisites

Before starting this tutorial, you should:

    • Install Oracle Database 12c.
    • Create two CDBs with two PDBs in the first CDB.

    The environment used in the development of this tutorial is as follows:

    • ORACLE_HOME: /u01/app/oracle/product/12.1.0
    • TNS Listener port: 1521
    • Container databases:
      • SID: cdb1
      • SID: cdb2
    • Pluggable databases (in cdb1):
      • pdb1
      • pdb2

Unplugging the PDB

    To unplug a PDB, you first close it and then generate an XML manifest file. The XML file contains information about the names and the full paths of the tablespaces, as well as data files of the unplugged PDB. The information will be used by the plugging operation.

    In this section, you unplug two PDBs to plug them with different methods.

    Use SQL*Plus to close the PDBs before they can be unplugged. Note: The pdb2 database may not have been opened, so you may receive an error that the PDB is already closed.

    . oraenv

    [enter cdb1 at the prompt]

    sqlplus / as sysdba

    alter pluggable database pdb1 close immediate;

    alter pluggable database pdb2 close immediate;

    Unplug the closed PDB and then specify the path and name of the XML file.

    alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/pdb1.xml';

    alter pluggable database pdb2 unplug into '/u01/app/oracle/oradata/pdb2.xml';

    Drop the closed PDB and keep the data files.

    drop pluggable database pdb1 keep datafiles;

    drop pluggable database pdb2 keep datafiles;

    Verify the status of the unplugged PDB.

    select pdb_name, status from cdb_pdbs where pdb_name in ('PDB1', 'PDB2');

    [you should see no rows]

    exit

    The unplugging operation makes changes in the PDB data files to record that the PDB was properly and successfully unplugged. Because the PDB is still part of the CDB, you can back it up in Oracle Recovery Manager (Oracle RMAN). This backup provides a convenient way to archive the unplugged PDB. After backing it up, you then remove it from the CDB catalog. But, of course, you must preserve the data files for the subsequent plugging operation.

Plugging the PDB into the Same or Another CDB

    In this section, you plug the unplugged PDB into another CDB by using different methods.

    Checking the Compatibility of the Unplugged PDB with the Host CDB

      Before starting the plugging operation, make sure that the to-be-plugged-in PDB is compatible with the new host CDB. Execution of the PL/SQL block raises an error if it is not compatible.

      Execute the following PL/SQL block:

      . oraenv

      [enter cdb2 at the prompt]

      sqlplus / as sysdba

      [if cdb2 is not started up, start it up now.]

      set serveroutput on

      DECLARE
         compatible BOOLEAN := FALSE;
      BEGIN  
         compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
              pdb_descr_file => '/u01/app/oracle/oradata/pdb1.xml');
         if compatible then
            DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
         else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
         end if;
      END;
      /

      DECLARE
         compatible BOOLEAN := FALSE;
      BEGIN   
         compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
              pdb_descr_file => '/u01/app/oracle/oradata/pdb2.xml');
         if compatible then
            DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? YES');
         else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? NO');
         end if;
      END;
      /

    Plugging the Unplugged PDB: NOCOPY Method

      Use the data files of the unplugged PDB to plug the PDB into another CDB without any copy.

      create pluggable database pdb_plug_nocopy using '/u01/app/oracle/oradata/pdb1.xml'
      NOCOPY
      TEMPFILE REUSE;

      This operation lasts a few seconds. The original data files of the unplugged PDB now belong to the new plugged-in PDB in the new host CDB. A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE_REUSE clause is required.

      Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.

      select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_NOCOPY';

      select open_mode from v$pdbs where name='PDB_PLUG_NOCOPY';

      List the data files of the plugged PDB.

      select name from v$datafile where con_id=3;

      exit

    Plugging the Unplugged PDB: COPY Method

      Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.

      mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_copy

      sqlplus / as sysdba

      Use the data files of the unplugged PDB to plug the PDB into the CDB and copy the data files to a new location.

      create pluggable database pdb_plug_copy using '/u01/app/oracle/oradata/pdb2.xml'
      COPY
      FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_copy');

      Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.

      select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY';

      select open_mode from v$pdbs where name='PDB_PLUG_COPY';

      List the data files of the plugged PDB.

      select name from v$datafile where con_id=4;

      exit



    Plugging the Unplugged PDB: AS CLONE MOVE Method

      Create and define a destination for the new data files, use the data files of the unplugged PDB to plug the PDB into another CDB, and then move the data files to another location.

      mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_move

      sqlplus / as sysdba

      Plug the PDB into the CDB and move the data files to a new location.

      create pluggable database pdb_plug_move using '/u01/app/oracle/oradata/pdb2.xml'
      MOVE
      FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');

      An error message is returned because of the non-uniqueness of the GUID. This is a good example of using the AS CLONE clause.

      create pluggable database pdb_plug_move
      AS CLONE using '/u01/app/oracle/oradata/pdb2.xml'
      MOVE
      FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');

      Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.

      select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_MOVE';

      select open_mode from v$pdbs where name='PDB_PLUG_MOVE';

      List the data files of the plugged PDB.

      select name from v$datafile where con_id=5;

Opening the Plugged PDB

    Open and check the availability of the plugged PDB. 

    Open the plugged-in PDBs.

    alter pluggable database pdb_plug_nocopy open;

    alter pluggable database pdb_plug_copy open;

    alter pluggable database pdb_plug_move open;

    Connect to the plugged-in PDBs and verify the container name that you are connected to.

    connect sys/oracle@localhost:1521/pdb_plug_nocopy AS SYSDBA

    show con_name

    connect sys/oracle@localhost:1521/pdb_plug_copy AS SYSDBA

    show con_name

    connect sys/oracle@localhost:1521/pdb_plug_move AS SYSDBA

    show con_name

    exit

Resetting Your Environment

    Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.

    Close all pluggable databases.

    . oraenv

    [enter cdb2 at the prompt]

    sqlplus / as sysdba

    alter pluggable database all close immediate;

    Unplug and then drop the PDB_PLUG_NOCOPY database so that you can use it to re-create the pdb1 database.

    alter pluggable database pdb_plug_nocopy unplug into '/u01/app/oracle/oradata/pdb_plug_nocopy.xml';

    drop pluggable database pdb_plug_nocopy keep datafiles;

    Unplug the PDB_PLUG_COPY database so that you can use it to re-create the pdb2 database.

    alter pluggable database pdb_plug_copy unplug into '/u01/app/oracle/oradata/pdb_plug_copy.xml';

    Drop the PDB_PLUG_COPY database.

    drop pluggable database pdb_plug_copy;

    Drop the PDB_PLUG_MOVE database.

    drop pluggable database pdb_plug_move;

    Plug the pdb2 database back into the cdb1 container database.

    connect sys/oracle@localhost:1521/cdb1 as sysdba

    create pluggable database pdb2 AS CLONE using '/u01/app/oracle/oradata/pdb_plug_copy.xml'
    MOVE
    FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb2/pdb_plug_copy','/u01/app/oracle/oradata/cdb1/pdb2');

    Plug the pdb1 database back into the cdb1 container database.

    create pluggable database pdb1 using '/u01/app/oracle/oradata/pdb_plug_nocopy.xml' nocopy tempfile reuse;

    Open the pdb2 database and then close again.

    alter pluggable database pdb2 open;

    alter pluggable database pdb2 close immediate;

    Open the pdb1 database.

    alter pluggable database pdb1 open;

    exit

    Remove the files that are no longer needed in the file system.

    rm $ORACLE_BASE/oradata/pdb*.xml
    rm -rf $ORACLE_BASE/cdb2/pdb_plug*

Summary

    In this tutorial, you learned how to unplug a PDB and plug it into another CDB.

    Resources

    Credits

    Curriculum Developers: Dominique Jeunot and Jean-François Verrier

To navigate this Oracle by Example tutorial, note the following:

Hide Header Buttons:
Click the title to hide the buttons in the header. To show the buttons again, click the title again.
Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.