Thursday, August 1, 2013

How to create a pluggable database by unplugging an existing PDB and plugging it into another container

So I want to create a new pluggable database into the CDBTEST container located into vsi08devpom machine (@vsi08devpom in this post) and I want the exact copy of PDB003 pluggable database currently contained into CDB001 located into my local machine (@localhost in this post).

Why not simply unplug it from my local machine and plug it into the target machine ?

When you want to disconnect a pluggable database from a multitenant container you can unplug it, specifying a file containing its metadata information formatted in XML, used eventually if you want to plug it in another container like we want to do.

To successfully unplug a pdb it must be put first in MOUNTED mode otherwise the following error is thrown:
SQL@localhost> select name, open_mode from V$PDBS;

------------------------------ ----------
PDB001          READ WRITE
PDB002          READ WRITE
PDB003          READ WRITE

SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';
alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml'
ERROR at line 1:
ORA-65025: Pluggable database PDB003 is not closed on all instances.
So before proceeding with the unplug operation you have to close the pluggable database
SQL@localhost> alter pluggable database pdb003 close immediate;

Pluggable database altered.

SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';

Pluggable database altered.
Even if the previous operation is not destructive, the container knows that a pluggable database was available, currently the database administrator no longer want to use it, you of course cannot open an unplugged database.
SQL@localhost> select name, open_mode from V$PDBS;

-------------   ----------
PDB001         READ WRITE
PDB002         READ WRITE
PDB003         MOUNTED

SQL@localhost> alter pluggable database pdb003 open;
alter pluggable database pdb003 open
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database
Where you can have information about the plugged or unplugged status of your pluggable databases ?
Query the CDB_PDBS view: it displays information about the PDBs associated with the CDB, including the status of each PDB.
SQL@localhost> select PDB_NAME, STATUS from CDB_PDBS where PDB_NAME = 'PDB003';

---------- -------------
To create a new pluggable database on vsi08devpom machine I have to copy the datafiles of the unplugged database to the target machine.
[oracle@localhost PDB003]$ ll
total 921688
-rw-r-----. 1 oracle oinstall   5251072 Aug  1 14:37 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Aug  1 14:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Aug  1 14:37 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Aug  1 14:30 temp01.dbf
[oracle@localhost PDB003]$ scp -r ../PDB003's password: 
temp01.dbf                                               100%   20MB 620.9KB/s   00:33    
system01.dbf                                             100%  260MB 543.4KB/s   08:10    
PDB001_users01.dbf                                       100% 5128KB   1.0MB/s   00:05    
sysaux01.dbf                                             100%  635MB 545.1KB/s   19:53    
[oracle@localhost PDB003]$ scp /app/oracle/oradata/pdb003.xml's password: 
pdb003.xml                                               100% 4003     3.9KB/s   00:00   
Log into the target machine.
My current multitenant container CDBTEST located on vsi08devpom is formed by three pluggable databases:
[oracle@vsi08devpom CDBTEST]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom CDBTEST]$ sqlplus / as sysdba

SQL*Plus: Release Production on Thu Aug 1 15:32:55 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@vsi08devpom> select name, open_mode from V$PDBS;

------------------------------ ----------

SQL@vsi08devpom> select pdb_name, status from CDB_PDBS;

---------- -------------
Now it's time to plug into CDBTEST container on vsi08devpom machine the unplugged database coming from CDB container (running on my local machine). The source location of the datafiles contained into the XML file is different from the target destination, so I cannot use just the following command, because the error "ORA-27041: unable to open file" is thrown as you can see.
SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse;
create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse
ERROR at line 1:
ORA-19505: failed to identify file
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
I need to appropriately use the SOURCE_FILE_NAME_CONVERT clause in the command:
SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' source_file_name_convert=('/app/oracle/oradata/CDB001','/opt/app/oracle/oradata/CDBTEST') nocopy tempfile reuse;

Pluggable database created.
The new PDB003 pluggable database on vsi08devpom machine has the same DBID of that one...
SQL@vsi08devpom> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;

---------- ------------- ---------- ---------- --------------------------------
PDB$SEED   NORMAL  4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8
PDBTEST1   NORMAL  3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC
PDBTEST2   NORMAL  2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671
PDBTEST3   NORMAL  2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7
PDB003    NEW   1448206714 1448206714 E2B9BE56B8B936CEE045000000000001
... unplugged from my local machine
SQL@localhost> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;

---------- ------------- ---------- ---------- --------------------------------
PDB$SEED   NORMAL  4063489868 4063489868 E19363E52C005C9AE045000000000001
PDB001    NORMAL  1701599811 1701599811 E1F26215682E1142E045000000000001
PDB002    NORMAL  1497027100 1497027100 E1F329ECE0F411E6E045000000000001
PDB003    UNPLUGGED  1448206714 1448206714 E2B9BE56B8B936CEE045000000000001
The new plugged database is in MOUNTED mode so I need to open it first:
SQL@vsi08devpom> select name, open_mode from V$PDBS;

------------------------------ ----------
PDB003          MOUNTED

SQL@vsi08devpom> alter pluggable database PDB003 open;

Pluggable database altered.
My data is all there as expected!!!
SQL@vsi08devpom> alter session set container=PDB003;

Session altered.

SQL@vsi08devpom> select count(*) from marcov.T1;


That's all.


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

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

Cracktaking said...

We gather a lot of software in our website which help you easily install font in Mac. you can download this
cracktaking without any charge. Are you interested to download and use this software. You are welcome in our website :

ciitnoida said...

Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me.

Best Salesforce Training Institute in Noida
Best Salesforce Admin Training Institute in Noida

yanmaneee said...

jordan shoes
kobe byrant shoes
supreme clothing
fila shoes
hermes birkin
off white jordan 1
ferragamo belt
adidas stan smith
jordan shoes

Sujeet Dan said...

Thanks for sharing such nice blog post, I always prefer to read quality and informative content.

jasonbob said...

steph curry shoes
golden goose superstar
adidas yeezy
pandora jewelry
curry shoes
longchamp handbags
canada goose outlet

Sarah Wilson said...

Just what I was looking for. I am struggling with my accounting assignment. I want anaccounting assignment help tutor to offer me two services. One is to complete my accounting assignments and the other is to provide me with online classes. I believe you are experienced enough to offer bothaccounting homework help and online classes. I know you charge based on the bulk. Tell me how much you charge for the online classes per hour.assignments

Sarah Wilson said...

As much as there are discouragements, it is true that mathematics is hard. Like in my case, I was never discouraged by anyone about math but I still find it very hard and that is why I am requesting your Math assignment help. I am tired of struggling with mathematics and spending sleepless nights trying to solve sums that I still don’t get right. Having gone through your Math homework help, I am sure that I will get the right help through you. Please tell me what I need to be able to hire you.

Sarah Wilson said...

I have submitted my assignment to your website without any challenges. The economics assignmenthelp expert handling my assignment has already contacted me and I am certain that my work is underway. I am just hoping that I will get quality economics homework help. I have a lot of hopes in you and I am just hoping that you will not disappoint me.

Sarah Wilson said...

How much do you charge for a Statistics Assignment Help task? Take, for my case, where I need you to provide me with the Statistics Homework Help on plotting a scatter plot with a regression line? How much should that cost? Do you charge on the basis of the workload or have a constant payment?

Hanna Bell said...

Hello. Please check the task I have just sent and reply as soon as possible. I want an adjustment assignment done within a period of one week. I have worked with an Accounting Homework Help tutor from your team and therefore I know it’s possible to complete it within that period. Let me know the cost so that I can settle it now as your Accounting Assignment Help experts work on it.

Hanna Bell said...

That is a huge number of students. Are they from the same country or different countries? I also want your math assignment help. I want to perform in my assignments and since this is what you have been doing for years, I believe you are the right person for me. Let me know how much you charge for your math homework help services.

Hanna Bell said...

I don’t have time to look for another expert and therefore I am going to hire you with the hope that I will get qualityeconomics assignment help. .Being aneconomics homework help professor I expect that your solutions are first class. All I want to tell you is that if the solutions are not up to the mark I am going to cancel the project.

Hanna Bell said...

Hey there, I need an Statistics Homework Help expert to help me understand the topic of piecewise regression. In our lectures, the concept seemed very hard, and I could not understand it completely. I need someone who can explain to me in a simpler way that I can understand the topic. he/she should explain to me which is the best model, the best data before the model and how to fit the model using SPSS. If you can deliver quality work then you would be my official Statistics Assignment Help partner.