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.

25 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

Anonymous said...

If you are going for best contents like me, only go to see this web site everyday as it provides quality contents,
thanks

my weblog :: house flipping project (http://ii.sk/bail_bond_122877)

Anonymous said...

It is not my first time to go to see this site, i am browsing this site
dailly and obtain fastidious information from here daily.


Feel free to visit my homepage - how long does the withdrawal from opiates last
(minecraftfreegamesonline.com)

Anonymous said...

Hi there to every body, it's my first pay a quick visit of this web
site; this blog includes awesome and in fact excellent material in favor of readers.


Also visit my site; Vapir one.

Anonymous said...

What do you think about this site give your comments please
http://www.wallsc.com

Anonymous said...

The price of Avene Diacneal in India is Rs.
1300 for 30 ml. The price is justified by the effectiveness since you
are getting two of the most effective ingredients :
a retinoid and glycolic acid in a non-comedogenic, light base.


Visit my weblog :: best makeup for acne

Anonymous said...

It's remarkable to visit this site and reading the views of all mates concerning this paragraph, while I am also keen of getting know-how.


Feel free to surf to my web page - private jets luxury

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

Anonymous said...

Harga Mobil Suzuki Bekas Jakarta
Mobil Suzuki Bekas
Mobil Suzuki Splash
Harga Mobil Suzuki Splash Matic
Harga Mobil Suzuki 2015
Harga Mobil Suzuki
Mobil Suzuki Terbaru Indonesia 2015
Mobil Suzuki Terbaru Murah
Mobil Suzuki SX4 Terbaru
Mobil Suzuki SX4
Harga Mobil Suzuki Ertiga
Suzuki Ertiga Sporty

Nike Free said...

Katie Holmes spent $15,000 on lingerie following her split from Tom Cruise.The former 'Dawson's Creek' actress - whose divorce from the http://www.christianlouboutinsaleflagshipstore.com actor was finalized earlier this week - reportedly went on a lavish spending spree christian louboutin sale to cheer herself up following the breakdown of her five-year marriage.A source told National christian louboutin Enquirer magazine: ''Katie treated herself to a lavish post-split pick me up shopping spree. christian louboutin sale She splurged on nearly $15,000 worth of sexy lingerie and undergarments alone.''She also bought christian louboutin 2016 15 pairs of pricey Christian Louboutin shoes and went on a tear at Barneys louboutin outlet New York department store, buying a closet's worth of clingy cocktail dresses and power christian louboutin suits to wear to auditions and meetings.''
"I definitely keep it safe because I'm Red Bottom Shoes not that kind of girl. I'm not risque - I'm very normal and grounded. christian louboutin outlet So I definitely don't want to get red sole shoes older when I have a lot of time to do that."Rosie Huntington-Whiteley's Christian Louboutin shoes make her "feel like a woman".The Victoria's Secret model thinks it is "really worth" saving up for christian louboutin store a pair of the red-lacquered sole footwear because they will automatically make girls appear christian louboutin shoes "very sexy".She said: "I feel they make you look very sexy, the cuts are louboutin shoes beautiful; The Lines, the colours the embellishments.

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose

Venu Gopalan said...

housekeeping agencies in mumbai | cctv camera dealers in mumbai | security guard services navi mumbai | plastic pouches manufacturers | tiffin services mumbai | hair products for women | buy dry fruits online | dry skin care | deodorant for men |