Tuesday, July 30, 2013

How to change the open mode of all pluggable databases

When you need to modify the open mode of all your PDBs at the same time (look at this post if you want to change the open mode of only a specific pluggable database) you can use ALTER PLUGGABLE DATABASE command and the ALL option.
As usual you have to ensure that the current container is the root.
SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
The following statement for example changes the open mode of all your pluggable databases at the same time.
SQL>ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4
When you need to change the open mode of all pluggable database except for listed ones you can include also the EXCEPT option as in the following example:
SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT PDB001 OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          READ WRITE    4
To open all my pluggable databases I can simply execute the following command. It doesn't take care that PDB002 is already open in READ WRITE mode: any error is returned because the pluggable databases are in different open mode.
SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4
However, if any of your pluggable databases are in READ ONLY mode, then the statement returns the error "ORA-65019: pluggable database PDB001 already open" as you can see:
SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE PDB001 OPEN READ ONLY;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database PDB001 already open
Even if the statement fails for pluggable database PDB001 it was able to open PDB002 as requested:
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          READ WRITE    4
To avoid the error ORA-65019 you can include the FORCE option in your command:
SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4
To shutdown all your pluggable databases except one you can use the following command:
SQL> alter pluggable database all except PDB002 close immediate;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE
To list some of your pluggable databases you can use the comma as in the following example where I need to open all my pluggable databases (PDB001 and PDB002) except those listed (just PDB001 and PDB002!!). Nothing happens of course.
SQL> alter pluggable database all except PDB002,PDB001 open;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE
That's all.

8 comments:

Anonymous said...

The core values of the church traditionally, are humility, tough manual labor, and simple
rural living. Online degree programs have become
more popular with busy, working individuals. • It will
not only make you an expert in the field, but also boost your current capabilities.


my site :: Diagnostic Cardiac Sonographer - http://cardiacsonographer.weebly.com/ -

Anonymous said...

When someone writes an paragraph he/she retains the idea of
a user in his/her mind that how a user can understand it. So that's why this paragraph is perfect.

Thanks!

Here is my blog gain muscle mass fast

Anonymous said...

Hi! This is kind of off topic but I need some help from an established blog.
Is it hard to set up yor own blog? I'm not very techincal but I can figure things out pretty fast.

I'm thinking about creatig my owwn but I'm not sure where
to start. Do you have any tips or suggestions?
Cheers

Also visit my homepage ... soul guardians age of midgard hack android

Anonymous said...

Wɦat's up, I rеad yoiur blog daily. Your story-telling style is witty,
keеp it up!

Alѕo visit mʏ bloɡ post ... learn Spanish fast and free online auto

Anonymous said...

Hi, i believe that i noticed you visited my blog so i came to
return the prefer?.I'm attempting to in finding things to improve my website!I guess
its adequate to make use of some of your ideas!!

Here is my blog post; appraisers national association

Anonymous said...

  However, if you sign out of the chat before other users do, only the part of the Group Chat that you participated
in will be saved. To be sure that very idea of messaging,
you will find two methods of an individual to convey his
messages. Apart from all that, the smartphone features motion sensor, magnetic sensor and proximity sensor.


my web blog: webcamchat

Anonymous said...

Have you ever thought about including a little bit more than just your articles?
I mean, what you say is important and all. Nevertheless think about if
you added some great photos or video clips to give your posts
more, "pop"! Your content is excellent but with images and videos, this website could certainly be one of the best in its
field. Awesome blog!

My weblog :: Free downloads - www.downloadicus.com,

Anonymous said...

I used tо be able to find good advice ffrom your blog posts.


Take a look at my homeρage - How to Learn Spanish fast children