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 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 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 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select instance_name from v$instance;
SQL> show con_name;
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
---------- ------------------------------ ----------


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
---------- ------------------------------ ----------

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
---------- ------------------------------ ----------
SQL> shutdown immediate;

Pluggable Database closed.

SQL> select con_id, name, open_mode from v$pdbs;
CON_ID     NAME                           OPEN_MODE
---------- ------------------------------ ----------

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
---------- ------------------------------ ----------

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.  


twitter: @curtisbl294




  1. Reblogged this on Thoughts from James H. Lui and commented:
    And most of these new commands are available via a simple right-click in the current release of Oracle’s SQLDeveloper (DBA Menu).

  2. Thanks a bunch.. I was just scratching my head as how to resolve this.. Nice work mate…Cheers !

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs


Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

Stuff that interests me, if not you!

Uwe Hesse

about Database Technology

%d bloggers like this: