Intro

I didn’t know I had that many things to write about refreshable PDB clones, but I do like the techno and how useful it can be to application teams that want to replicate QA environments out of their prod database very quickly. After showcasing how to automate the refresh of a PDB clone using oracle scheduler and discovering about 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 & 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 to create 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 & Test CDBs are in 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

Step one + two (refresh & 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:ORACLECDBPSPDB_RODATA','I:ORACLECDBPSDEV01DATA',
'K:ORACLECDBPSPDB_ROTEMP','I:ORACLECDBPSDEV01TEMP');

2. 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 DATABSE 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. DB link how to

Although both TEST & DEV CDBs are in the same server we still need to deliver that database link required to perform our remote cloning form 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 source TNS alias

TEST-CDB3> create database link  dev2_clone_link connect to c##dev2_clone identified by welcome1 using 'SourceCDB2';

Epic fail

This won’t work as the common user we just created doesn’t exist in the refreshable PDB (PSPDB_RO). see error message below

TEST-CDB3> CREATE PLUGGABLE DATABASE PSTEST01 FROM PSPDB_RO@dev2_clone_link File_name_convert=('K:ORACLEDATA','E:ORACLEDATA');
*  ERROR at line 1:ORA-17628: Oracle error 1435 returned by remote Oracle server
ORA-01435: user does not exist

Reason:
The newly created source common user at CDB level couldn’t be created at 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 exist 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';

Almost There

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

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

TEST-CDB3> CREATE PLUGGABLE DATABASE PSTEST01 FROM PSPDB_RO@dev2_clone_link 
File_name_convert=('K:ORACLEDATA','E:ORACLEDATA');
 

Conclusion

With this last post, we came to learn that even if you get to leverage the awesome refreshable PDB clone 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.    

        Thank you for reading