What Is A Refreshable Clone PDB?

Did you know you can create a Clone PDB, that can be refreshed at a later time with new data from the source database? After creating the Clone PDB, you can refresh the PDB with updates that occurred in the source database, by applying the redo that accumulated since the last redo log apply. This is called a Refreshable Clone PDB. This feature was introduced in 12.2 and it has been improved since. I don’t know about you, but when I see the Refreshable Clone, I think of Data Guard in a way, but without the cost and complexity.

3 Reasons why would you want to create a refreshable clone:

• if you open the Refreshable Clone PDB in read-only mode, you could use it for reporting purposes. The clone PDB will get out of sync at one point, but still this would be a great use for it.
• the clone PDB could be the source of other PDB clones, in order to not impact the production system in any way. The impact on the production system (I/O) if multiple clones are required, could be pretty high. As the data in the cloned PDB becomes stale with time, the refreshable clone PDB solves the problem of stale data. Whenever required, the refreshable clone PDB could be refreshed with redo from production. Oracle recommends to create a “golden master” refreshable clone of your source PDB. Then create a PDB-level snapshot (we’ll learn about that in the next post), and create clones from the PDB snapshot into other non-prod environments. It sounds a bit complicated but it could work like magic!
• you could also switch over to your clone, in case of an emergency (disaster), if the source becomes unavailable either planned or unplanned. Kind of like in a Data Guard environment, without the complexity.

There are a few requirements that must be in place in order to setup Refreshable Clone PDBs

• you need to have a database link to the source CDB to create the refreshable clone PDB
• in 19c, the db link can point to the same CDB or a remote CDB (a different CDB). In 12.2, the db link had to point to a remote CDB.
• Oracle recommends using OMF or set the PDB_FILE_NAME_CONVERT parameter in the spfile, of the target CDB.
• the source PDB must be in ARCHIVELOG mode and local undo mode
• when creating the Clone PDB, you must be connected to the root container or the application root.
• the Refreshable Clone PDB, after creation can be either in CLOSED state or OPEN READ ONLY. Once you open it read write, you can no longer refresh it.

There are 2 refresh modes: automatic and manual.

Automatic Refresh Mode:

• the refresh can be at set intervals, ie. every 30 minutes
• even if the PDB is in automatic refresh mode, you can also do a manual refresh of the PDB on demand.
• the refreshable clone PDB must be closed during the refresh process, it cannot be open, not even in read only mode. Do you know why? Because redo transactions are applied during the refresh, from the source PDB. If the automatic refresh mode PDB is not closed, then the refresh is postponed to the next refresh time, and no error is returned.
• to create the refreshable clone PDB, use the REFRESH MODE clause of the CREATE PLUGGABLE DATABASE: REFRESH MODE EVERY X MINUTES, where X is the number of minutes you want the refresh to occur.

Manual Refresh Mode:

• as the name suggests the clone PDB will be refreshed manually, when you issue the refresh statement (on demand).
• to create the refreshable clone PDB, use the REFRESH MODE clause of the CREATE PLUGGABLE DATABASE: REFRESH MODE MANUAL
• the refreshable clone PDB must be closed during the refresh process, it cannot be open, not even in read only mode. Do you know why? Because redo transactions are applied during the refresh, from the source PDB. You will receive an error if you attempt to refresh manually a PDB that is not closed: ORA-65025: Pluggable database *** is not closed on all instances.
• you can alter the clone PDB to be automatic refresh from manual and vice versa.

Now that you have an understanding of the concepts and requirements, I’ll show you how to do it! All my examples were run in version 19c. My database is setup with OMF. In the examples below, I will create a refreshable clone PDB of TESTPDB in CDB1. The clones will reside also in CDB1, and will be called: TESTPDB_REF_AUTO and TESTPDB_REF_MAN. Let’s get to it!

First step is to create the database link that will point to the source CDB. TESTPDB is the PDB that resides inside CDB1, and we want to clone it:

SQL> create database link CDB1_LINK connect to 
     system identified by oracle using 'CDB1';
Database link created.

Now we are going to create a PDB with automatic refresh every 30 minutes, called TESTPDB_REF_AUTO and one with manual refresh, called TESTPDB_REF_MAN:

SQL> create pluggable database TESTPDB_REF_AUTO from TESTPDB@CDB1_LINK 
refresh mode every 30 minutes;
Pluggable database created.

SQL> create pluggable database TESTPDB_REF_MAN from TESTPDB@CDB1_LINK
refresh mode manual;
Pluggable database created.

Here are some things you can do with these clones. You can modify the manual refreshable PDB to be automatic, and vice versa:

SQL> alter pluggable database TESTPDB_REF_MAN refresh mode every 30 minutes;
Pluggable database altered.

SQL> alter pluggable database TESTPDB_REF_MAN refresh mode manual;
Pluggable database altered.

You can manually refresh the PDBs:

SQL> alter pluggable database TESTPDB_REF_MAN refresh;
Pluggable database altered.

SQL> alter pluggable database TESTPDB_REF_AUTO refresh;
Pluggable database altered.

You can open the PDBs in Read Only mode:

SQL> alter pluggable database TESTPDB_REF_MAN open read only;

Pluggable database altered.

-- Whoops! refresh the clone PDB, you get error, as expected.

SQL> alter pluggable database TESTPDB_REF_MAN refresh;
alter pluggable database TESTPDB_REF_MAN refresh
*
ERROR at line 1:
ORA-65025: Pluggable database TESTPDB_REF_MAN is not closed 
           on all instances.

What do you think? Isn’t this a great idea? Give it a try and let me know! There are so many cases where this feature could make your life easier!

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 best price, only $18.99 CAD available until the end of the month!