Nov

Connecting & Managing Pluggable Database

In previous article we saw the process for creating Container and Pluggable Databases using Database Configuration Assistance (DBCA). There are difference in connecting to pluggable database as compared to those of container database and databases in previous releases. Also the way we need startup and shutdown the pluggable database.

It is really surprising and interesting to know, when we start the container database, all the pluggable database within are automatically mounted. There is no special mechanism to start pluggable database when we start the container database. We can manage the pluggable database in two ways:

When connected to pluggable database itself, we can use SQL*Plus commands to startup and shutdown the database.
When connected to Container database, we can use “Alter Pluggable Database” commands with various options.

Connecting to Container Database

We created the container database named CBD.

To check the services under container database, and information of which pluggable database it holds, you can use the dictionary view V$SERVICES

To get the details of all the services, you can use “lsnrctl” utility

You can get the details of current container with 2 methods as show:

You can switch to container inside container simply with “Alter Session Set” Command as:

Connecting to pluggable database

You can connect to pluggable database using “tnsname” or direct connect using service name. Connect to database and make sure that you are connected to appropriate database as:

Start up and Shut Down Pluggable database.

Startup and Shutdown process is simple as that of databases of previous releases. There is difference in managing pluggable database. When connected to container database, you can use “Alter Pluggable database” statements to manage the database. Some examples of the same are given below.

Following are possible options to perform startup operations on pluggable database:

ALTER PLUGGABLE DATABASE <pdb_name>OPEN READ WRITE [RESTRICTED] [FORCE];

ALTER PLUGGABLE DATABASE <pdb_name> OPEN READ ONLY [RESTRICTED] [FORCE];

ALTER PLUGGABLE DATABASE <pdb_name> OPEN UPGRADE [RESTRICTED];

ALTER PLUGGABLE DATABASE <pdb_name> CLOSE [IMMEDIATE];

If you have multiple pluggable database, “Alter Pluggable Database” can be used manage one or more database with single command. You need to specify the names of pluggable database as below:

ALTER PLUGGABLE DATABASE <pdb_1_name>,<pdb_2_name> [OPEN]/[CLOSE];

In case your container has few pluggable database and you wish to keep some of them shutdown and start rest you can use “Alter Pluggable database…EXPECT” command, as:

ALTER PLUGGABLE DATABASE ALL EXPECT pdb OPEN;

As said earlier that there is no special mechanism to start pluggable database when we start the container database, but we can achieve that creating the trigger on container database when starts to perform the startup of the pluggable databases automatically within it.

CREATE OR REPLACE TRIGGER openallpluggabledatabases

AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;

END openallpluggabledatabase;

This was all about connecting and managing startup/shutdown of pluggable databases. In coming article we shall understand how to view the information/properties of container and pluggable database using data dictionary view or SQL*Plus.

Managing Pluggable Database