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 databaseWhere 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 UNPLUGGEDTo 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:00Log 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 NORMALNow 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: 9I 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 E2B9BE56B8B936CEE045000000000001The 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.