The other day, I found myself praising refreshable clone PDBs to a customer (which I often do because it’s a killer feature). They liked the feature too but asked:

> We are concerned about the impact on the source database. When AutoUpgrade connects to the source database and clones the database, can we offload the work to a standby database?

Refreshable clone PDBs can eat up your resources if you don’t constrain the target CDB. So, let’s see what we can do.

Mounted Standby Database

This won’t work, because you must be able to connect to the database via a regular database link. Further, AutoUpgrade and the cloning process must be able to execute queries in the source database, which is not possible on a mounted database.

Open Standby Database / Active Data Guard

What if you stop redo apply and open the standby database? Or if you have Active Data Guard?

In this case, the database would be open in read-only mode, and those queries would work. However, the refreshable clone PDB feature was developed to work in and require a read-write database, so this won’t work either – Not even if you enable automatic redirection of DML operations (ADG_REDIRECT_DML).

Even if this case would work, we wouldn’t recommend it. Because, we recommend that you run analyze and fixups mode on the source database, which wouldn’t be possible on a read-only database. You could run analyze and fixups on the primary database. But is that really an option? If you’re worried about affecting your primary and want to offload to the standby, would running those commands on the primary be an option?

Snapshot Standby Database

What about a snapshot standby? That’s a read-write database. Let’s give it a try.

1. Convert the source standby to a snapshot standby:

   DGMGRL> convert database '...' to snapshot standby;
   

   • The standby must remain a snapshot standby for the entire duration of the job. If you need to switch over or fail over to the standby, you must restart the entire operation.
2. Ensure the PDB is open on the source standby.

   alter pluggable database ... open;
   

   • Otherwise, you will run into ORA-03150 when querying the source database over the database link.
3. In the source standby, create the user used by the database link and grant appropriate permissions:

   create user dblinkuser identified by ...;
   grant create session, create pluggable database, select_catalog_role to dblinkuser;
   grant read on sys.enc$ to dblinkuser;
   

4. In the target CDB, create a database link that points to the PDB in source standby:

   create database link clonepdb
   connect to dblinkuser identified by ...
   using '';
   

5. Create an AutoUpgrade config file:

   global.global_log_dir=/home/oracle/autoupgrade/log
   global.keystore=/home/oracle/autoupgrade/keystore
   upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
   upg1.target_home=/u01/app/oracle/product/23.0.0/dbhome_1
   upg1.sid=CDB19
   upg1.target_cdb=CDB23
   upg1.pdbs=PDB1
   upg1.source_dblink.PDB1=CLONEPDB 300
   upg1.target_pdb_copy_option.PDB1=file_name_convert=none
   upg1.start_time=+12h
   

6. Start AutoUpgrade in deploy mode:

   java -jar autoupgrade.jar ... -mode deploy
   

7. Convert the source standby back to a physical standby:

   DGMGRL> convert database '...' to physical standby;
   

Is It Safe?

Using a standby database for anything else than your DR strategy, is sometimes perceived as risky. But it is not, as I explain in this blog post (section What Happens If I Need to Switch Over or Fail Over?).

Happy upgrading!