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

About these ads

2 comments

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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

Martins Blog

Trying to explain complex things in simple terms

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

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Follow

Get every new post delivered to your Inbox.

Join 1,501 other followers

%d bloggers like this: