Introduction
Oracle Multitenant allows you to copy and move Pluggable Databases (PDBs) across Container Databases (CDBs) in various ways, including PDB (Refreshable) Clones, PDB Relocate, and PDB Unplug and Plug.
Unplug and Plug might be preferred if you are dealing with very large databases, as copying the data files over scp may be faster than PDB cloning, especially when using Standard Edition which does not support parallelism.
When you unplug a PDB and plug it into another CDB on a different database server, you will most likely have a different location – different file system path – for the datafiles on the target database server. This blog post shows options that allow you to put the data files on a different file system path on the target database server.
But before we begin, remember that the Multitenant Architecture is the only available architecture in Oracle Database 23ai. If you are still using the Non-CDB architecture, move to Multitenant as soon as possible. Starting with Oracle Database 19c, having up to three user-created PDBs does not require the Multitenant option license.
The Environment
- CDB named CDBAD1 with PDB named PDBAD1 on database server named lvm23ad1
- CDB named CDBAD2 with PDB named PDBAD2 on database server named lvm23ad2
Both the source and target databases use Oracle Managed Files (OMF).
We will unplug the source PDBAD1 from CDBAD1 and plug it into the target CDBAD2.
Unplug the Source PDB
On the source CDB, query the data file location of the source PDB:
SQL> alter session set container=PDBAD1;
SQL> set lines 120
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_system_m4v5nh58_.dbf
/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_sysaux_m4v5n6bk_.dbf
/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_undotbs1_m4v5np3z_.dbf
/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_users_m4v5nrmh_.dbf
Unplug the source PDB:
SQL> alter session set container=PDBAD1;
SQL> administer key management export keys with secret <PW_to_protect_this_file> to '/home/oracle/pdbad1.p12' force keystore identified by <TDE_wallet_PW>;
SQL> alter session set container=CDB$ROOT;
SQL> alter pluggable database PDBAD1 close immediate;
SQL> alter pluggable database PDBAD1 unplug into '/home/oracle/PDBAD1.xml';
Copy the Files to the Target Database Server
Copy the XML metadata file and the wallet file generated in the previous step alongside the PDB data files to the target database server:
[oracle@lvm23ad1 ~]$ scp -p PDBAD1.xml oracle@lvm23ad2:/home/oracle
[oracle@lvm23ad1 ~]$ scp -p pdbad1.p12 oracle@lvm23ad2:/home/oracle
[oracle@lvm23ad1 ~]$ scp -p /u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/* oracle@lvm23ad2:/home/oracle/datafiles
Check the Data Files Location
Now, the data files on the target database server are stored in /home/oracle/datafiles. However, on the source database server they were on a different path, and this is also stored in the XML metadata file:
[oracle@lvm23ad2 ~]$ cat PDBAD1.xml | grep path
<path>/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_system_m4v5nh58_.dbf</path>
<path>/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_sysaux_m4v5n6bk_.dbf</path>
<path>/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_undotbs1_m4v5np3z_.dbf</path>
<path>/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_temp_m4v5nv3n_.tmp</path>
<path>/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/o1_mf_users_m4v5nrmh_.dbf</path>
Plug In the PDB with Data Files on a Different Location
If you just try to plug in the PDB into the target CDB:
SQL> create pluggable database pdbnew1 as clone using '/home/oracle/PDBAD1.xml';
create pluggable database pdbnew1 as clone using '/home/oracle/PDBAD1.xml'
*
ERROR at line 1:
ORA-19505: failed to identify file
"/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C
5/datafile/o1_mf_system_m4v5nh58_.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Help: https://docs.oracle.com/error-help/db/ora-19505/
This is expected, as there are no data files in
/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C
5/datafile/ on the target database server.
Now someone could argue why not just create that path and put the data files there? In this case, that would work, as both systems have the /u02 disk. But what if the target system has completely different disk names? Also, the source path includes the source CDB name “CDBAD1“, which would be confusing on the target database server, where the CDB name is “CDBAD2“.
Option 1: Plug In and Convert Data File Names
Use the SOURCE_FILE_NAME_CONVERT clause:
-- first, we need to import the TDE key
SQL> administer key management import keys with secret <PW_protecting_this_file> from '/home/oracle/pdbad1.p12' force keystore identified by <TDE_wallet_PW> with backup;
-- plug in the PDB
SQL> create pluggable database pdbnew1 as clone using '/home/oracle/PDBAD1.xml' SOURCE_FILE_NAME_CONVERT = ('/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/', '/home/oracle/datafiles/') NOCOPY TEMPFILE REUSE;
SQL> alter pluggable database pdbnew1 open;
SQL> alter session set container=pdbnew1;
SQL> set lines 120
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/home/oracle/datafiles/o1_mf_system_m4v5nh58_.dbf
/home/oracle/datafiles/o1_mf_sysaux_m4v5n6bk_.dbf
/home/oracle/datafiles/o1_mf_undotbs1_m4v5np3z_.dbf
/home/oracle/datafiles/o1_mf_users_m4v5nrmh_.dbf
Note: As a “quick and dirty” option, you could also edit the data file paths directly in the .xml file to reflect the current file location on the target database server instead of using SOURCE_FILE_NAME_CONVERT.
The new PDB will be opened in RESTRICTED mode. Query the pdb_plug_in_violations view:
set lines 300
col status for a10
col type for a10
col cause for a20
col message for a90
SQL> SELECT status, type, cause, message FROM pdb_plug_in_violations;
STATUS TYPE CAUSE MESSAGE
---------- ---------- -------------------- ------------------------------------------------------------------------------------------
PENDING ERROR Wallet Key Needed PDB needs to import keys from source.
So, you need to execute the import key again, this time from within the PDB, close the PDB, and open it again:
SQL> administer key management import keys with secret <PW_protecting_this_file> from '/home/oracle/pdbad1.p12' force keystore identified by <TDE_wallet_PW> with backup;
SQL> alter pluggable database PDBNEW1 close immediate;
SQL> alter pluggable database PDBNEW1 open;
A new directory with the new PDB GUIDs name is created with an empty “datafiles” subdirectory:
SQL> select guid from v$pdbs where name = 'PDBNEW1';
GUID
--------------------------------
190D53FF8AB37537E0638E000A0A16D9
[oracle@lvm23ad2 ~]$ ls -l /u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190D53FF8AB37537E0638E000A0A16D9
drwxr-x--- 2 oracle oinstall 4096 May 22 15:29 datafile
This is actually where you want your data files to be finally. Luckily, we can move the data files online one at a time:
SQL> alter database move datafile '/home/oracle/datafiles/o1_mf_system_m4v5nh58_.dbf';
SQL> alter database move datafile '/home/oracle/datafiles/o1_mf_sysaux_m4v5n6bk_.dbf';
SQL> alter database move datafile '/home/oracle/datafiles/o1_mf_undotbs1_m4v5np3z_.dbf';
SQL> alter database move datafile '/home/oracle/datafiles/o1_mf_users_m4v5nrmh_.dbf';
All data files are at the final destination:
SQL> set lines 120
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190D53FF8AB37537E0638E000A0A16D9/datafile/o1_mf_system_m4w7qock_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190D53FF8AB37537E0638E000A0A16D9/datafile/o1_mf_sysaux_m4w7s09l_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190D53FF8AB37537E0638E000A0A16D9/datafile/o1_mf_undotbs1_m4w7sc8t_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190D53FF8AB37537E0638E000A0A16D9/datafile/o1_mf_users_m4w7smon_.dbf
Option 2: Plug In, Convert Data File Names, and Move to Final Destination
This is the same as option 1, however, moving the data files to the final destination in one go using the MOVE clause:
-- copy the datafiles from the source again
[oracle@lvm23ad1 ~]$ scp -p /u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/* oracle@lvm23ad2:/home/oracle/datafiles
-- create a new pdb
SQL> create pluggable database pdbnew2 as clone using '/home/oracle/PDBAD1.xml' SOURCE_FILE_NAME_CONVERT = ('/u02/app/oracle/oradata/CDBAD1_23ai/CDBAD1_23AI/16E9F79186922679E0631701F40AB0C5/datafile/', '/home/oracle/datafiles/') MOVE;
SQL> alter pluggable database PDBNEW2 open;
SQL> alter session set container=PDBNEW2;
SQL> administer key management import keys with secret <PW_protecting_this_file> from '/home/oracle/pdbad1.p12' force keystore identified by <TDE_wallet_PW> with backup;
SQL> alter pluggable database PDBNEW2 close immediate;
SQL> alter pluggable database PDBNEW2 open;
Check the location of the data files:
SQL> select guid from v$pdbs;
GUID
--------------------------------
190EA2B51DAA2EA4E0638E000A0A2E04
SQL> set lines 120
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EA2B51DAA2EA4E0638E000A0A2E04/datafile/o1_mf_system_m4w85qb8_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EA2B51DAA2EA4E0638E000A0A2E04/datafile/o1_mf_sysaux_m4w85qbr_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EA2B51DAA2EA4E0638E000A0A2E04/datafile/o1_mf_undotbs1_m4w85qbw_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EA2B51DAA2EA4E0638E000A0A2E04/datafile/o1_mf_users_m4w85qc1_.dbf
Option 3: Unplug into an Archive File
Instead of using an XML metadata file and copying the data files, unplug into an archive file (.pdb instead of .xml), which includes the data files as well.
The XML file includes the full paths of the PDB data files as we have seen previously.
When a .pdb archive file is specified, the XML metadata file contains the relative file names only.
During plugging in a PDB using a .pdb archive file, Oracle Database extracts the .pdb archive and places the PDB data files in the same directory.
Plug in the source PDB again into the source CDB, so we can unplug it into an archive file:
SQL> drop pluggable database PDBAD1 keep datafiles;
SQL> create pluggable database PDBAD1 using '/home/oracle/PDBAD1.xml';
SQL> alter pluggable database PDBAD1 open;
SQL> alter session set container=PDBAD1;
SQL> administer key management import keys with secret <PW_protecting_this_file> from '/home/oracle/pdbad1.p12' force keystore identified by <TDE_wallet_PW> with backup;
SQL> alter pluggable database PDBAD1 close immediate;
SQL> alter pluggable database PDBAD1 open;
Unplug into an archive file (.pdb):
SQL> alter session set container=PDBAD1;
administer key management export keys with secret <PW_to_protect_this_file> to '/home/oracle/pdbad1.p12' force keystore identified by <TDE_wallet_PW>;
SQL> alter session set container=CDB$ROOT;
SQL> alter pluggable database PDBAD1 close immediate;
SQL> alter pluggable database PDBAD1 unplug into '/home/oracle/PDBAD1.pdb';
Copy the wallet file and the archive file to the target database server:
[oracle@lvm23ad1 ~]$ scp -p PDBAD1.pdb oracle@lvm23ad2:/home/oracle
[oracle@lvm23ad1 ~]$ scp -p pdbad1.p12 oracle@lvm23ad2:/home/oracle
Plug in into the target database server:
-- create a new pdb
SQL> create pluggable database pdbnew3 as clone using '/home/oracle/PDBAD1.pdb' MOVE;
SQL> alter pluggable database pdbnew3 open;
SQL> alter session set container=PDBNEW3;
SQL> administer key management import keys with secret <PW_protecting_this_file> from '/home/oracle/pdbad1.p12' force keystore identified by <TDE_wallet_PW> with backup;
SQL> alter pluggable database PDBNEW3 close immediate;
SQL> alter pluggable database PDBNEW3 open;
This is definitely much easier. No SOURCE_FILE_NAME_CONVERT, no “alter database move datafile” command, and no worries about any paths.
Check the data file location:
SQL> select guid from v$pdbs where name = 'PDBNEW3';
GUID
--------------------------------
190EF9AEB78C62BDE0638E000A0A6F19
SQL> set lines 120
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EF9AEB78C62BDE0638E000A0A6F19/datafile/o1_mf_system_m4w9mbj5_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EF9AEB78C62BDE0638E000A0A6F19/datafile/o1_mf_sysaux_m4w9mbj6_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EF9AEB78C62BDE0638E000A0A6F19/datafile/o1_mf_undotbs1_m4w9mbj6_.dbf
/u02/app/oracle/oradata/CDBAD2_23ai/CDBAD2_23AI/190EF9AEB78C62BDE0638E000A0A6F19/datafile/o1_mf_users_m4w9mbj7_.dbf
Done! Everything is already in its final location!
The /home/oracle/PDBAD1.pdb is still there on the target database server, but is not needed anymore. You can delete it.
Clean Up
After the plug-in on the target CDB is successful, you can drop the PDB on the source CDB, this time including the data files:
SQL> drop pluggable database PDBAD1 including datafiles;
Conclusion
Oracle Multitenant provides many options for cloning and relocating PDBs across different CDBs. Unplug and Plug might be preferred for very large databases and/or when using Standard Edition, as copying over scp might be faster than PDB cloning.
Dealing with the data file paths at the target database server is much easier when using unplug into an archive file (.pdb). However, it requires a free space on your file system as large as your database.
Database Heartbeat 