How to remote Clone a PDB from a READ ONLY Refreshable PDB (Tips & Tricks)
I didn’t know I had that many things to write about refreshable PDB (Pluggable Database) clones, but I do like the techno and how useful it can be to application teams that want to replicate QA (Quality Assurance) environments out of their prod database very quickly. After showcasing how to automate the refresh of a PDB clone using an Oracle scheduler and discovering the hidden target archives created upon each refresh, today we’re going to learn how to perform a remote clone from a refreshable PDB clone that can never be opened in read-write.
1. Use Case and Goals
For the sake of clarity, let’s just remind the scope of our environment and what we want to achieve. Obviously, a multitenant license would allow creating an unlimited number of PDBs but I am called BrokeDBA for a reason, so let’s assume we can only have 3 PDBs per CDB and the dev team needs 5 PDB clones. Both DEV and Test CDBs are on the same server.
As shown in the below diagram, we have 3 environments in total
1. Production CDB where the source PDB (PSPROD) resides
2. DEV CDB where the refreshable PDB Clone (PSPDB_RO) is refreshed daily
3. TEST/QA CDB where the dev PDB should be cloned
Steps one + two (Refresh and Local Clone)
The first two steps are easily done and covered in a previous post. If you want to review how I created the refreshable PDB clone please check my previous blog post.
1. Setup the refreshable PDB clone => see detailed configuration steps here
2. Create a local clone from the Refreshable PDB (PSPDB_RO): Easy peasy as shown below
SQL> CREATE PLUGGABLE DATABASE PSDEV01 FROM PSPDB_RO File_Name_convert=('K:\ORACLE\CDB\PSPDB_RO\DATA\','I:\ORACLE\CDB\PSDEV01\DATA\', 'K:\ORACLE\CDB\PSPDB_RO\TEMP\','I:\ORACLE\CDB\PSDEV01\TEMP\');
2. The Dilemma
When you want to do a local clone from an existing PDB it is quite simple as all you need to do is run the CREATE PLUGGABLE DATABASE FROM PDB. But a remote clone has additional requirements including a common user to make the db_link work between the source and target CDB.
3. Database Link How To’s
Although both TEST & DEV CDBs are in the same server we still need to deliver that database link required to perform our remote cloning from the refreshable PDB Clone that can only be open in Read-Only mode.
OPTION A: Using a New Common User
1- Create a common user in the source CDB2 (where the refreshable PDB is) with the required privileges
DEV-CDB2> create user c##dev2_clone identified by welcome1 container=all; DEV-CDB2> grant create session ,create pluggable database to c##dev2_clone container=all;
2- Create a Database Link at the target CDB3 (test) using the source TNS Alias
TEST-CDB3> create database link dev2_clone_link connect to c##dev2_clone identified by welcome1 using 'SourceCDB2';
This won’t work as the common user we just created doesn’t exist in the refreshable PDB (PSPDB_RO). See the error message below
TEST-CDB3> CREATE PLUGGABLE DATABASE PSTEST01 FROM PSPDB_RO@dev2_clone_link File_name_convert=('K:\ORACLE\DATA\','E:\ORACLE\DATA\'); * ERROR at line 1:ORA-17628: Oracle error 1435 returned by remote Oracle server ORA-01435: user does not exist
The newly created source common user at the CDB level couldn’t be created at the PDB level because the refreshable PDB (FPSDB_RO) is in read-only mode.
OPTION B: Reusing the common user created for the refresh in Prod CDB
Since our refreshable PDB can’t be altered it won’t be a bad idea to reuse a common user that already exists in this PDB (itself imported during the refresh from prod).
1- Drop the faulty DB link and check the common user existing within the refreshable PDB (PS_PDB_RO)
TEST-CDB3> drop database link dev2_clone_link -- Check imported existing common user in the refreshable PDB DEV-CDB2> alter session set container=PSPDB_RO; DEV-CDB2> select username from dba_users where username like 'C##%'; USERNAME --------------- C##PSPROD_CLONE ---> imported from prod PDB during Refreshable PDB creation
2- ReCreate a database link at the target CDB3 (test) using the existing source common user & TNS alias
TEST-CDB3> create database link dev2_clone_link connect to C##PSPROD_CLONE identified by welcome1 using 'SourceCDB2';
Before performing the remote clone with our new DB link, I just wanted to test it to see if it was really working
TEST-CDB3> Select * from dual@dev2_clone_link; ERROR at line 1: internal error code, arguments: [ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user], [C##PSPROD_CLONE], , , , , , , , , , ], , , , , , ORA-02063: preceding line from DEV2_CLONE_LINK
The source refreshable PDB was shipped with the prod common user used by the source DB link but this user still doesn’t exist at CDB (CDB2) level
All we need to do is to create that common user at the CDB level in the source CDB2 where the refreshable PDB clone is hosted
DEV-CDB2> Alter session set container=cdb$root; DEV-CDB2> Select username from dba_users where username like 'C##%'; USERNAME ------------- C##DEV2_CLONE ---> Created earlier which doesn’t exist in PSPDB_RO -- Recreate the common user existing in PSPDB_RO(refreshable) but at CDB level DEV-CDB2> Create user c##psprod_clone identified by welcome1 container=all; DEV-CDB2> Grant create session, create pluggable database to c##psprod_clone container=all; DEV-CDB2> Drop user C##DEV2_CLONE cascade; -- useless now DEV-CDB2> Select username from dba_users where username like 'C##%'; USERNAME ------------- C##PSPROD_CLONE ---> just what we need for our remote PDB clone now
Now we just recreate the DB link in the target CDB (CDB3), re-run our remote PDB cloning command and voila.
TEST-CDB3> CREATE PLUGGABLE DATABASE PSTEST01 FROM PSPDB_RO@dev2_clone_link File_name_convert=('K:\ORACLE\DATA\','E:\ORACLE\DATA\');
With this last post, we came to learn that even if you get to leverage the awesome refreshable PDB clones feature, there are still little things to tweak to make it even more powerful by remote cloning it to external CDBs.
I hope this little trick will be helpful to those who want to perform such cloning.