Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB)
 in Oracle Database 12c Release 1 (12.1)
The multitenant option introduced in Oracle Database 12c allows a single container database (CDB)
to host multiple separate pluggable databases (PDB).

Container Database (CDB)

Startup and shutdown of the container database is the same as it has always been for regular instances.
The SQL*Plus STARTUP and SHUTDOWN commands are available when connected to the CDB as a privileged user.

Some typical values are shown below.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]

Pluggable Database (PDB)

Pluggable databases can be started and stopped using SQL*Plus commands or the ALTER PLUGGABLE DATABASE command.

SQL*Plus Commands

The following SQL*Plus commands are available to start and stop a pluggable database, when connected to
that pluggable database as a privileged user.
STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN [IMMEDIATE];
Some examples are shown below.

STARTUP FORCE;
SHUTDOWN IMMEDIATE;

STARTUP OPEN READ WRITE RESTRICT;
SHUTDOWN;

STARTUP;
SHUTDOWN IMMEDIATE;
ALTER PLUGGABLE DATABASE

The ALTER PLUGGABLE DATABASE command can be used from the CDB or the PDB.

The following commands are available to open and close the current PDB when
connected to the PDB as a privileged user.

ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];
Some examples are shown below.

ALTER PLUGGABLE DATABASE OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
The following commands are available to open and close one or more PDBs when connected to the CDB as a privileged user.

ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE <pdb-name-clause> CLOSE [IMMEDIATE];
The <pdb-name-clause> clause can be any of the following:

One or more PDB names, specified as a comma-separated list.
The ALL keyword to indicate all PDBs.
The ALL EXCEPT keywords, followed by one or more PDB names in a comma-separate list, to indicate a subset of PDBs.
Some examples are shown below.

ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;
Pluggable Database (PDB) Automatic Startup

The 12.1.0.2 patchset has introduced the ability to preserve the startup state of PDBs,
 so you probably shouldn't be implementing a trigger in the manner discussed in this section.

Prior to 12.1.0.2, when the CDB is started, all PDBs remain in mounted mode.
There is no default mechanism to automatically start them when the CDB is started.
The way to achieve this is to use a system trigger on the CDB to start some or all of the PDBs.

CREATE OR REPLACE TRIGGER open_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/

You can customise the trigger if you don't want all of your PDBs to start.

Preserve PDB Startup State (12.1.0.2 onward)

The 12.1.0.2 patchset introduced the ability to preserve the startup state of PDBs through a CDB restart. 

This is done using the ALTER PLUGGABLE DATABASE command.

We will start off by looking at the normal result of a CDB restart.
Notice the PDBs are in READ WRITE mode before the restart, but in MOUNTED mode after it.

SQL>  SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

SQL>


SHUTDOWN IMMEDIATE;
STARTUP;


SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED

SQL>
Next, we open both pluggable databases, but only save the state of PDB1.

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
SQL> ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

The DBA_PDB_SAVED_STATES view displays information about the saved state of containers.

SQL> COLUMN con_name FORMAT A20
SQL> COLUMN instance_name FORMAT A20

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME             INSTANCE_NAME        STATE
-------------------- -------------------- --------------
PDB1                 cdb1                 OPEN

SQL>
Restarting the CDB now gives us a different result.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE




SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;


SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           MOUNTED


The saved state can be discarded using the following statement.

SQL> ALTER PLUGGABLE DATABASE pdb1 DISCARD STATE;

SQL> COLUMN con_name FORMAT A20
SQL> COLUMN instance_name FORMAT A20

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected


To connect to a PDB using the SQL*Plus CONNECT command:

Configure your environment so that you can open SQL*Plus.


Start SQL*Plus with the /NOLOG argument:

[oracle@sujeet ~]$ sqlplus /nolog
Issue a CONNECT command using easy connect or a net service name to connect to the PDB.

To connect to a PDB, connect to a service with a PDB property.

Connecting to a PDB in SQL*Plus Using the PDB's Net Service Name

The following command connects to the hr user using the PDB service.
The PDB service has a PDB property for the hrpdb PDB. This example assumes that the client is
configured to have a net service name for the hrapp service.

[oracle@sujeet ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 14 08:51:55 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> CONNECT hr@PDB
Enter password:
Connected.


[oracle@sujeet~]$ sqlplus /nolog
SQL> CONNECT sonu@PDB
Enter password:
Connected.

SQL> show user
USER is "sonu"


SQL> SELECT name, open_mode FROM v$pdbs;
NAME                           OPEN_MODE
------------------------------ ----------
PDB                        READ WRITE

SQL> CONNECT system@PDB
Enter password:
Connected.


Here is a brief list of some of the usage notes explained in the documentation.

The state is only saved and visible in the DBA_PDB_SAVED_STATES view if the container is in READ ONLY or READ WRITE mode.
The ALTER PLUGGABLE DATABASE ... SAVE STATE command does not error when run against a container in MOUNTED mode,
but nothing is recorded, as this is the default state after a CDB restart.
Like other examples of the ALTER PLUGGABLE DATABASE command, PDBs can be identified individually,
 as a comma separated list,
 using the ALL or ALL EXCEPT keywords.
The INSTANCES clause can be added when used in RAC environments. The clause can identify instances individually,
 as a comma separated list, using the ALL or ALL EXCEPT keywords. Regardless of the INSTANCES clause,
 the SAVE/DISCARD STATE commands only affect the current instance.


How to Stop and Start a Pluggable Database

The following explains how to stop and start a the container database and the containers (pluggable databases).

1. Shutdown a container database (CDB) and all pluggable databases (PDBs)

sqlplus '/ as sysdba'
SQL> show connection
NB Make sure you are on the root CDB$ROOT
SQL> shutdown immediate
2. Startup the CDB

sqlplus '/ as sysdba'
SQL> startup
Note: When you start a CDB it does not automatically start the PDBs

3. Check the status of the PDBs

sqlplus '/ as sysdba'
SQL> select name, open_mode from v$pdbs;
Note: Any PDBs are in mounted status.

4. Start a PDB

sqlplus '/ as sysbda'
SQL> alter pluggable database myplugdb3 open;
NB This will open pluggable database myplugdb3.
SQL> alter pluggable database all open;
NB This will open all pluggable databases.
5. Stop a PDB

sqlplus '/ as sysdba'
SQL> alter pluggable database myplugdb3 close immediate;
NB This will close pluggable database myplugdb3
SQL> alter pluggable database all close immediate;
NB This will close all pluggable databases
6. Using a trigger to open all pluggable databases.

sqlplus '/ as sysdba'
SQL> CREATE OR REPLACE TRIGGER pdb_startup AFTER STARTUP ON DATABASE
SQL> BEGIN
SQL> EXECUTE IMMEDIATE 'alter pluggable database all open';
SQL> END pdb_startup;
SQL> /


Start and Shutdown Pluggable Database

I will tell about start and shutdown pluggable database in this my post.This is as you know that tradational
start and shutdown process. But there is a little difference when you use container database.
If you have container database which have pluggable database and when you start container database at the same
time you should all pluggable database manually after starting container database. Let’s look this process.

Start and Shutdown Pluggable Database: This operation can be made in two ways.
First one is you can start or shutdown direct from inside pluggable database.
The second one is “alter pluggable database” command from root container.
You know first way. You set container parameter and do it what you want.

1)You can start or shutdown direct from inside pluggable database.

SQL> alter session set container=PDB2;
Session altered.

SQL> shutdown immediate;
Pluggable Database closed.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           MOUNTED

You can start with same way.

SQL> startup
Pluggable Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

2) The another way you can use “alter pluggable database” command from root container to start and
shutdown pluggable database.You connect to container database.

SQL> alter pluggable database PDB1 close;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           READ WRITE
You can start pluggable database with same way.

SQL> alter pluggable database PDB1 open;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

Maybe you have a lot of pluggable database in the container database and these shutdown operation would
be disturbed.We can shutdown all pluggable database with one command from root container.

SQL> alter pluggable database all close;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED
To open all PDB’s

SQL> alter pluggable database all open;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

You may want close all pluggable database except one pluggable database. You can do this except command as following.

SQL> alter pluggable database all except PDB2 close;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           READ WRITE

When you open all pluggable database you can do same thing  “open” command  instead “close” command .

Or you can specify pdb list to perform operation.

SQL> alter pluggable database pdb1,pdb2 close;
Pluggable database altered.

NOTE:When you shutdown container database all PDB will shutdown too. But when you start container
database any PDB is not start automaticly.
To start PDB we should do manually intervention or we can create a trigger as following.

SQL> CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END open_pdbs;
/