Shutdown/Startup vs. Alter Statement – 12c Edition

When everyone finally moves to Oracle pluggable databases, one thing to remember is that stopping and starting a database is not going to be as simple as it use to be.  Shutting down and starting up a database is done simply with the following commands:

shutdown normal

shutdown immediate

shutdown abort

startup

startup nomount

startup mount

If we use one of these traditional shutdown methods at the container database (CDB) layer, we will bring down the CDB and the associated PDBs under it. This is something to be aware of when looking to reboot a container databases.  Lets take a look at bringing down a CDB.

[oracle@oel ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 21 21:42:53 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
ora12c

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> alter session set container=PDB1;
alter session set container=PDB1
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

Now, that we established that SHUTDOWN IMMEDIATE at the CDB level will close the CDB and all other PDBs; how can we shutdown just a single PDB if needed?  There are two different ways to shutdown a PDB.  The first of which is from the CDB layer.  OK, I know why are we doing this from the CDB layer?  Simple answer is because we can.  Not so simple answer is because there will be times where we want to take the PDB offline for some form of maintenance without affecting the other PDBs around it.  We can shutdown a PDB from the CDB level using the ALTER PLUGGABLE DATABASE command.  

The ALTER PLUGGABLE DATABASE statement takes four different clauses that affect the state of the PDB.  These four clauses are:

 alter pluggable database open read write

 alter pluggable database open read only

 alter pluggable database open upgrade

 alter pluggable database close

Before we take a look at a an example, we need to find the current state of the PDB(s).  This can be done by querying theV$PDBS view.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID     NAME                           OPEN_MODE
---------- ------------------------------ ----------
 2         PDB$SEED                       READ ONLY
 3         PDB1                           MOUNTED
 4         PDBTEST                        MOUNTED
 5         PDB2                           MOUNTED

As you can see, I have a few PDBs that are in MOUNTED state.  MOUNTED in the pluggable world is the same as being closed in a traditional model.  How do we bring one of these PDBs online in READ WRITE mode?  Lets focus on PDB1.

What is the current state of PDB1?  As indicated previously, the current state is MOUNTED (closed).  If we want to bring PDB1 online, we need to execute an ALTER PLUGGABLE DATABASE statement, as follows:

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID     NAME                           OPEN_MODE
---------- ------------------------------ ----------
 2         PDB$SEED                       READ ONLY
 3         PDB1                           READ WRITE
 4         PDBTEST                        MOUNTED
 5         PDB2                           MOUNTED

Notice that the OPEN command in the ALTER PLUGGABLE DATABASE will actually open the PDB in READ WRITE mode.  If we want to close the PDB, we would specify CLOSE as part of the ALTER PLUGGABLE DATABASE command.

Since we have covered the ALTER PLUGGABLE DATABASE command; is there any other ways of shutting down a PDB.  The answer is yes!  We can use the normal SHUTDOWN commands as we would with a traditional database.  In order to do this, we have to be in that PDB container first.  Here is an example of closing PDB1 with the SHUTDOWN command.

SQL> alter session set container=PDB1;

Session altered.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID     NAME                           OPEN_MODE
---------- ------------------------------ ----------
 3         PDB1                           READ WRITE

SQL> shutdown immediate;

Pluggable Database closed.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID     NAME                           OPEN_MODE
---------- ------------------------------ ----------
 3         PDB1                           MOUNTED

After moving into the PDB1 container, we can shutdown the PDB using the SHUTDOWN IMMEDIATE command.  Once the PDB is shutdown, you can still query the V$PDBS and it will show that the PDB is in the MOUNTED state.  Remember, MOUNTED is the same as CLOSED in the pluggable world.  Using this same train of thought, we can open the PDB by using the STARTUP command.

SQL> startup

Pluggable Database opened.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID     NAME                           OPEN_MODE
---------- ------------------------------ ----------
 3         PDB1                           READ WRITE

Through this blog post, we have looked at two different ways to close and reopen as pluggable database.  Both ways are acceptable for bringing a PDB offline.  The key thing to remember is where you are at within the pluggable architecture.  If you are in the CDB, the ALTER PLUGGABLE DATABASE command should be used.  If you are connected to the PDB, the traditional command of SHUTDOWN/STARTUP will work.  

Enjoy!

twitter: @curtisbl294

blog: http://dbasolved.com

Please follow and like: