Remote PDB Clone: ORA-65180

The more databases you clone, the more you learn about the cloning process, and the more challenges you come across. If things always work out as per the documentation, or someone showing you, then you don’t learn as much. That is why, I actually enjoy getting failures in processes from time to time, to learn something new!

That being said, I tried cloning a remote PDB, and the challenge was that the source and target PDB have the same name and same file location.

The environment setup:

Production and test databases are located on separate servers, prod01 and test01. The database version is 19.3.
Production database name is PRODCDB, and has PDB1 as a pluggable database.
Test database name is TESTCDB, and has PDB1 as a pluggable database. There is a database link between test and prod CDB, called prodcdb.

The challenge:

I wanted to refresh PDB1@PRODCDB into PDB1@TESTCDB, by keeping the database name and file names the same.
Let me explain. If the DATA1 tablespace in PDB1@PRODCDB resides here: +DATA/PDB1/data1.dbf on server: prod01, then I wanted the same tablespace’s datafile to reside in the same location on server test01. Because the CDBs are on different servers, prod01 and test01, I thought the file location for the datafiles can be identical.

Let’s find out if this actually works!

Try #1

Because my assumption is that the file names can be identical, I did not specify the FILE_NAME_CONVERT clause during the cloning process. And because my database is not using OMF (Oracle Managed Files), I received the error
ORA-65016: FILE_NAME_CONVERT must be specified:

create pluggable database PDB1 from PDB1@prodcdb;
*
ORA-65016: FILE_NAME_CONVERT must be specified

Try #2

On the second attempt I tried specifying the FILE_NAME_CONVERT with the same names for source and target. Remember I wanted the files to be in the same location:

create pluggable database PDB1 from PDB1@prodcdb
FILE_NAME_CONVERT = ('+DATA/PDB1', 
                     '+DATA/PDB1');
*
ORA-65180: duplicate file name encountered 

In this attempt I got the following error: ORA-65180: duplicate file name encountered
Oracle did not like the fact that the source and the target database are using the same file name. I looked up this error, and came across a bug on Oracle Support: Bug 29649694 ORA-65180 error when cloning the PDB from 12c to 19c / 19c to 19c when FILE_NAME_CONVERT is using same path for source and destination

This bug happens when you try to clone a PDB over a database link, because FILE_NAME_CONVERT clause was used with one or more of the source and destination names matching exactly. The workaround solution for this is to use different file names in the target. This bug is fixed in 19.10 (Jan 2021 DB RU) and in 20.1.0. I personally have not confirmed by testing that this is fixed.

Try #3

Knowing all of this now, try #3 should be a charm, and succeed. I made sure that the test PDB1 files, are placed in a different location than the production PDB1 files. If you really want them in the same location, you would have to manually rename them. Indeed this method worked!

create pluggable database PDB1 from PDB1@prodcdb
FILE_NAME_CONVERT = ('+DATA/PDB1', 
                     '+DATA/PDB1TST');

Have you come across this? Next time, I’ll show you another thing I came across during cloning a remote PDB! Stay tuned!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the One Year Anniversary Best Price, only $13.99 CAD available until April 8th!