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;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
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;

NAME         OPEN_MODE
-------------   ----------
PDB$SEED READ ONLY
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';

PDB_NAME   STATUS
---------- -------------
PDB003    UNPLUGGED
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 vsi08devpom.mydomain.it:/opt/app/oracle/oradata/CDBTEST/PDB003
oracle@vsi08devpom.mydomain.it'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 vsi08devpom.mydomain.it:/opt/app/oracle/oradata
oracle@vsi08devpom.mydomain.it'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 12.1.0.1.0 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 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL@vsi08devpom> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE

SQL@vsi08devpom> select pdb_name, status from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDBTEST1   NORMAL
PDBTEST2   NORMAL
PDBTEST3   NORMAL
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
"/app/oracle/oradata/CDB001/PDB003/system01.dbf"
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_NAME   STATUS        DBID    CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
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_NAME   STATUS        DBID    CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
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;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
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;

  COUNT(*)
----------
       100

That's all.

12 comments:

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

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

yanmaneee said...

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

Sujeet Dan said...

Thanks for sharing such nice blog post, I always prefer to read quality and informative content.
https://ello.co/pharmaceuticaldevelopmentgroup
https://fancy.com/PharmaceuticalDevelopmentGroup
https://www.cometdocs.com/profile/PDGroup
https://www.blurb.com/user/Pharmaceutic
https://www.fyple.com/company/pharmaceutical-development-group-uq6bzbj/
https://pharmaceutical-development-group.hub.biz/
http://www.ringmybiz.com/profile/Pharmaceutical-Development-Group
https://www.bloglovin.com/@aaryan27/about-complete-guidance-513g-exempt-devices
https://www.apsense.com/article/investigational-new-drug-application-indspecial-protocol-assessment-spa.html

jasonbob said...

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

Eliana Grace said...

The Global Ecotourism Market is expected to grow at a very fast pace over the projection period. Rising awareness of global warming and visitor devastation of natural beauty are primary drivers of the ecotourism market’s expansion. Ecotourism allows for the exploration of new locations, the opportunity to socialize with the local community, and participation in daring activities. Ecotourism mostly caters to tourists that want to visit natural, unknown locations while protecting the environment. The Global Ecotourism Market report provides a holistic evaluation of the market. The report offers a comprehensive analysis of key segments, trends, drivers, restraints, competitive landscape, and factors that are playing a substantial role in the market.

shethadys said...

replica bags seoul replica bags lv replica bags hong kong

Write my Essay said...

Warm regards to all! Here is San welcoming you. You seem to be having trouble with Write My Book Report. No need to take the stress! I'm here to answer any inquiries or address any concerns you may have.

theglobalhues said...

Discover the stories of entrepreneurs and leaders who have achieved remarkable success and are changing the world.
theglobalhues

tom elis said...

YesMovies, established in the early 2010s, stands out as a popular online streaming platform, providing a user-friendly experience for movie and TV enthusiasts. With its extensive collection categorized by genre, release year, and IMDb ratings, it offers a convenient and enjoyable way to discover new content. Users appreciate the platform's accessibility. While YesMovies has gained popularity, it's essential to be aware of copyright considerations and explore legal alternatives for a secure and ethical entertainment experience. Embracing legitimate streaming services ensures continued support for the creative industry, allowing users to indulge in their favorite content responsibly and guilt-free.
Yes movies

brown bomber leather jacket for men said...

I am new to databases. What about the storing data on online php database