3.6 Managing Pluggable Databases (PDBs) Using SQL Developer

«« Previous
Next »»

1. Overview


Purpose

This tutorial describes three important activities to help you manage pluggable databases (PDB) using Oracle SQL Developer.

Time to Complete

Approximately 45 minutes

Introduction

The tutorial covers the three following activities:
  • Modifying the state of a pluggable databas
  • Creating a pluggable database from an existing pluggable database
  • Unplugging and plugging a pluggable database
Prerequisites

Before starting this tutorial, you should have:
  • Oracle SQL Developer 4.0 software, which is available on the Oracle Technology Network
  • Oracle Database 12c software, which is available at Oracle Database Software Downloads
  • Understand Oracle Database 12c concepts. Documentation is available online at Oracle® Database Concepts 12c Release 1 (12.1).

2. Setting up the Environment


  • Creating a Database Connection
1. Double-click on sqldeveloper.exe to launch SQL Developer.

Managing Pluggable Databases (PDBs) Using SQL Developer

The Oracle SQL Developer window is displayed.

Managing Pluggable Databases (PDBs) Using SQL Developer

2. On the Connections tab, right-click Connections and select New Connection.

Managing Pluggable Databases (PDBs) Using SQL Developer

The New / Select Database Connection dialog box is displayed.

Managing Pluggable Databases (PDBs) Using SQL Developer

3. You will be connecting to the root multitenant container database (CDB). The root container is where you can manage the pluggable databases (PDB). Enter the parameters for the new connection. In this example, the entries are:

Name: sys
Username: sys
Password: Enter the password for your system.
Save Password: Selected
Connection Type: Basic
Role: SYSDBA
Hostname: localhost
Port: 1521
SID: orcl

Click Test and confirm a status of "Success."

Managing Pluggable Databases (PDBs) Using SQL Developer

4. Click Save.

5. Close the New / Select Database Connection dialog box.
  • Adding a Connection to the DBA Navigator
In SQL Developer, the DBA Navigator enables you to perform full administration of container databases and pluggable databases.

1. Select View > DBA.

Managing Pluggable Databases (PDBs) Using SQL Developer

2. On the DBA tab, right-click Connections and select Add Connection.

Managing Pluggable Databases (PDBs) Using SQL Developer

3. In the Select Connection dialog box, select sys and click OK.

Managing Pluggable Databases (PDBs) Using SQL Developer

The sys connection is added to the DBA Navigator.

3. Modifying the State of a Pluggable Database


Before you can clone a pluggable database, it has to be Closed and Opened in Read Only mode.

1. Expand the sys connection. In the Container Database node, right-click PDB1 and select Modify State.

Managing Pluggable Databases (PDBs) Using SQL Developer

The Modify Pluggable State dialog box is displayed. Since database PDB1 is opened, the value of New State is CLOSE. Click Apply.

Managing Pluggable Databases (PDBs) Using SQL Developer

2. Right-click PDB1 and select Modify State.

Managing Pluggable Databases (PDBs) Using SQL Developer

3. Click to open the State Option list. Select READ ONLY.

Managing Pluggable Databases (PDBs) Using SQL Developer

4. Click Apply.

Managing Pluggable Databases (PDBs) Using SQL Developer

5. When the success message is displayed, click OK.

Managing Pluggable Databases (PDBs) Using SQL Developer

6. In the PDB1 tab, under General, confirm that the value for OPEN_MODE is READ ONLY.

Managing Pluggable Databases (PDBs) Using SQL Developer

You are now ready to create a new Pluggable Database by cloning an existing Pluggable Databse.

4.  Cloning a Pluggable Database


You can create additional pluggable databases by cloning an existing pluggable database. Perform the tasks below to create a clone of PDB1 named PDB3.

1. Expand the sys node and then the Container Database node.

Managing Pluggable Databases (PDBs) Using SQL Developer

2. Right click PDB1 and select Clone.

Managing Pluggable Databases (PDBs) Using SQL Developer

The Clone Pluggable Database dialog box is displayed.

3. The Clone Pluggable Database dialog box is displayed.

On the Properties tab, provide the parameters for cloning. In this example, the parameters are:

Database Name: PDB3
Source PDB: PDB1
Database Link: NONE (default)
Storage: Unlimited
File Name Conversions: Custom Names (creates datafiles for your new PDB3 by copying the files from the PDB1 datafiles).

Managing Pluggable Databases (PDBs) Using SQL Developer

4. When you select Custom Names, Source File and Target File paths are revealed. Examine the Target file entries.

Managing Pluggable Databases (PDBs) Using SQL Developer

5. Double-click the Target File entries and revise them to reflect the cloned database name, according to the following instructions:

Revise each Target File entry by changing the folder name where the .dbf.clone files reside to the folder name for the new (cloned) database. Example: /datafile/01_mf_system_90t6yfk4_.dbf.clone changes to /pdb3/01_mf_system_90t6yfk4_.dbf.clone.

Change the folder name for each file entry. Your results should look similar to this:

Managing Pluggable Databases (PDBs) Using SQL Developer

6. Click the SQL tab to view the CREATE statement (optional), and then click Apply.

Managing Pluggable Databases (PDBs) Using SQL Developer

7. When the success message is displayed, click OK.

Managing Pluggable Databases (PDBs) Using SQL Developer

PDB3 is created and displayed under Container Database.

Managing Pluggable Databases (PDBs) Using SQL Developer

8. To view the data files for the cloned database, click PDB3.

Managing Pluggable Databases (PDBs) Using SQL Developer

9. Click the DataFiles tab to view the data files.

Managing Pluggable Databases (PDBs) Using SQL Developer

10. Right Click on PDB3 and select Modify State. Change state to Open with mode set to Read and Write. 

Managing Pluggable Databases (PDBs) Using SQL Developer

5.  Unplugging and Plugging a Pluggable Database


  • Unplugging the Database
You can unplug a database from one container database and later plug it into the same (or another) container database.

1. A Pluggable Database has to be 'Closed' before it can be unplugged. In the Container Database tree, right-click PDB3 and select Modify State. Click Apply to 'Close' PDB3.

Managing Pluggable Databases (PDBs) Using SQL Developer

2. In the Container Database tree, right-click PDB3 and select Unplug.

Managing Pluggable Databases (PDBs) Using SQL Developer

3. In the Unplug Database dialog box, confirm the database name PDB3 and XML file name PDB3.XML. The XML file stores the location of the data files for the unplugged database. 

Managing Pluggable Databases (PDBs) Using SQL Developer

4. Optional: To view the code, click SQL, view the code, and then click Cancel.

Managing Pluggable Databases (PDBs) Using SQL Developer

5. Click Apply.

Managing Pluggable Databases (PDBs) Using SQL Developer

PDB3 is no longer displayed in the Container Database tree.

Managing Pluggable Databases (PDBs) Using SQL Developer

  • Plugging in the Database
Note: The steps for plugging a PDB into a different container database are essentially the same as those for plugging it into the container database from which it was unplugged. Here, you will plug PDB3 back into the same container database--while recognizing that, in the real world, selecting a different container database would make more business sense.

1. In the Connections tree, right-click the Container Database node, and then select Plug In Pluggable Database.

Managing Pluggable Databases (PDBs) Using SQL Developer

The Plug In Pluggable Database dialog box is displayed.

Managing Pluggable Databases (PDBs) Using SQL Developer

2. Enter the following:
Database Name: PDB3 
XML File Name: PDB3.XML

Click Apply.

Managing Pluggable Databases (PDBs) Using SQL Developer

Database PDB3 is plugged in and, once again, appears in the Container Database tree.

Managing Pluggable Databases (PDBs) Using SQL Developer

«« Previous
Next »»