There is a fancy new command to unplug a PDB in Oracle Database 12.2.0.1:
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO 'pdb1.pdb';
The nice thing with this command differing in the file ending of ‘pdb
‘ instead of ‘xml
‘ as you used it in Oracle 12.1 (and the ‘xml
‘ option is still available of course): Instead of just creating an xml
description file it zip
s everything together into a PDB archive.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> create pluggable database PDB1 admin user adm identified by adm
2 file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb1');
Pluggable database created.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb';
Pluggable database altered.
Now lets have a quick look into the created file:
-rw-r--r--. 1 oracle dba 108414 May 19 16:20 pdb1.pdb $ unzip pdb1.pdb Archive: pdb1.pdb inflating: system01.dbf inflating: sysaux01.dbf inflating: undotbs01.dbf warning: stripped absolute path spec from /home/oracle/pdb1.xml inflating: home/oracle/pdb1.xml
Not bad. Actually pretty cool.
Of course the same command fails in Oracle Database 12.1.0.2:
SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb'; alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb' * ERROR at line 1: ORA-65125: valid XML file name is required
Unfortunately there’s a significant flaw when your PDB is stored in ASM:
The zip file does not contain the datafiles of your PDB.
Please see:
The fix is included since the July 2017 Update (RU) and in Oracle Database 18c.
A really handy feature – but don’t use it when your PDBs are located in ASM before Oracle 18c.
Danger zone!
And be very aware that the larger your PDB is, the longer this will take. As the archive gets zip compressed, the “standard” way may be a bit more inconvenient but complete much faster. And NEVER … really NEVER … CTRL-C
it as this will damage the PDB. Your fallback then will be a restore and recover of the PDB (if your database runs in ARCHIVELOG
mode).
–Mike
Hi Mike,
it is fixed!
The fix for 25715167 is first included in
12.2.0.1.170718 (Jul 2017) Database Release Update (DB RU)
During test on RAC with OMF ans ASM I run into following error:
create pluggable database PDB_TEST6_c as clone using ‘/var/sourcen/PDB_TEST6.pdb’
*
FEHLER in Zeile 1:
ORA-65169: Fehler beim Kopieren der Datei /var/sourcen/system.298.948749695
ORA-65227: PDB-Archivdatei /var/sourcen/PDB_TEST6.pdb kann nicht geoffnet
werden
Solution was to copy the pdb archive to all RAC-nodes in the same named directory.
In addition If you run in this error it leaves an orphaned folder in the ASM DG:
ASMCMD [+DATA/dbtest] > ls -l 54ABEF20852E0944E0530B4C650A3484/DATAFILE
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE JUL 19 14:00:00 Y SYSAUX.282.949761617
DATAFILE UNPROT COARSE JUL 19 14:00:00 Y UNDO_2.266.949761617
I have to remove the orphaned folders manually, which will not be great in a prod. env.
Regards Peter
Thanks for the update, Peter 🙂
As the archive gets zip compressed, the “standard” way may be a bit more inconvenient but complete much faster. Is a *.pdb file a zip archive or a proprietary Oracle archive method. In other words can I unzip the *.pdb file and plug it in with the extracted XML file ?
Standard zip algorithm – very simple realized.
Just unzip it.
And yes, you can unzip and plug back.
Cheers,
Mike
Mike,
Take a Windows 12.2 Container (I think even a 19.5) with a plug. Unplug it to a *.pdb file. Then unzip it and try to plug it in with the unzipped XML. You will receive an ORA error. Checking the files with dbv FILE=… will tell you that the dbf file is corrupt. It seems the zipping of the *.pdb is using a different algo as the unzip from the %ORACLE_HOME%\bin\unzip .exe.
Regards,
Paulfrans Musolf
PS: plugging in the unplugged *.pdb file again will work… no problem there.
That is interesting – I will try this on my Linux box to see if it works the same.
Did you have an SR for this?
Thanks,
Mike
Mike,
On Linux there is no problem. Its is Windows only related.
I have yet to raise a SR …but I have one which points to the same problem.
SR 3-21269776311
There is something wrong with the zip routines in Oracle. During the Beta Test of Oracle 19 I discovered the issue. But it took till 2 weeks ago before development acted on it.