Optimize materialization of Exadata PDB sparse clones

Well, this blog is unfortunately reserved to DBA folk running Oracle engineered systems #Exadata, #ExaCC, #ExaCS and Oracle Multitenant database (aka. CDB) architecture.

One of our #ExaCC customer has a strong DevOps focused business and heavily relies on Exadata sparse clones to quickly provision “lightweight” database clones for their development teams.

As part of the move of an mission critical application to #ExaCC we enhanced our spare PDB materialization scripts.

What’s wrong with the Materialization?

At a first look, nothing! it’s a straightforward command as easy as creating a PDB using “CREATE PLUGGABLE DATABASE .. FROM …”. Except that it takes time…

below some “fun facts”:

Indeed, we have set a “fixed” degree of parallelism to “constraint” the resources (CPU, IO) used by all kind of cloning activities.

Nevertheless, the “ALTER PLUGGABLE DATABASE MATERIALIZE” is at least 5 times slower compared to the creation of a full PDB clone when copying all database block onto SPARSE diskgroup.

Why does it take so much time?

A simple check in the database alert*log provides some insights:

2025-05-25T09:02:46.980450+02:00

Moving datafile +SPRC4/AVQD001T_SITE1/35F11D075E0FB991E0631A1FA10A6291/DATAFILE/data_000014750.2483.1202028085 (2967) to +DATAC4

Indeed, we get flooded by “ALTER DATABASE MOVE DATAFILE” commands. A quick session tracing activity confirmed that Oracle performs sequentially online database move operations. Last one but not least, this operations runs all database server (VMcluster node).

*** 2025-05-22T21:22:52.961911+02:00 (CDB$ROOT(1))

Moving datafile +SPRC4/AVQD001T_SITE1/360EA26B7CE6F135E0631A1FA10A00C8/DATAFILE/data_000011575.2664.1202154873 (6656) to +DATAC4

kcffo_mv_prepare: the secondary file +DATAC4/AVQD001T_SITE1/360EA26B7CE6F135E0631A1FA10A00C8/DATAFILE/data_000011575.2015.1202162415 is created with size 216

kcffo_mv_domove: Blocks copied for file +DATAC4/AVQD001T_SITE1/360EA26B7CE6F135E0631A1FA10A00C8/DATAFILE/data_000011575.2015.1202162415 size 216

Move operation committed for file +DATAC4/AVQD001T_SITE1/360EA26B7CE6F135E0631A1FA10A00C8/DATAFILE/data_000011575.2015.1202162415

Are we able to monitor the “materialize” process ?

Yes, of course as usual operations which last for more than 6 seconds we can query GV$SESSION_LONGOPS:

> SELECT sid, serial#, opname, sofar, totalwork,
                 ROUND(sofar/totalwork*100,2) PCT_COMPLETE
     FROM   V$SESSION_LONGOPS
   WHERE TOTALWORK != 0
        AND    SOFAR != TOTALWORK
 ORDER BY 1

      SID   SERIAL# OPNAME                                               SOFAR     TOTALWORK   PCT_COMPLETE
------- ---------- ---------------------------- ----------------- ----------------- -----------------
   2228      30474  Online data file move                  4796186624       8615100416                    55.67

1 row selected.

Can we parallelize “ALTER PLUGGABLE DATABASE” process?

Actually, IMHO Oracle shall provide through any kind of “magic” parameters. Unfortunately, researches on https://oradiff.oracle.com as of Oracle 19.27 were unfruitful.

Luckily, we did all scripting with #python using #cx_oracle driver and such quickly develop the necessary function to iterate over all pluggable database datafiles using parallel task execution with conurrent.futures module.

The optimized process using 8 database sessions, all moving database files in parallel was worth; The runtime for materializing the sparse clone is equal (or even less) to the time required to create a full PDB clone.

Wrap-up

For those, who already are confident with Exadata sparse clone (& Exadata sparse ASM diskgroup) you might have spotted the we moved the database into the DATA diskgroup. This can be achieved by setting DB_CREATE_FILE_DEST parameter at pluggable database level before starting to materialize the datafiles. So, Materializing the PDB sparse cloning into DATA diskgroup is only 2 times slower compared to copying all blocks to SPARSE diskgroup.

Oracle Exadata Exascale & Oracle 23ai will definitively bring more flexibility to Exadata PDB sparse clones, at least the known limitations for Oracle19c will be from the past as communicated by Oracle.
However, the whole materialization remains uncovered so far.

At the time of writing this post the Oracle “Cloud” support team has been informed.

Last one but not least, from time to time we need to cleanup some leftovers from ASM sparse diskgroup (subject to another Oracle Service request).

2025-05-25T09:02:35.623108+02:00
WARNING: Cannot delete old file +SPRC4/AVQD001T_SITE1/35F11D075E0FB991E0631A1FA10A6291/DATAFILE/data_000014770.1000.1202028085 left after datafile move
2025-05-25T09:02:35.623248+02:00
Errors in file /u02/app/oracle/diag/rdbms/avqd001t_site1/AVQD001T1/trace/AVQD001T1_ora_203496.trc:

ORA-15028: Oracle Automatic Storage Management (Oracle ASM) file '+SPRC4/AVQD001T_SITE1/35F11D075E0FB991E0631A1FA10A6291/DATAFILE/data_000014770.1000.1202028085' not dropped; currently being accessed

Keep you posted!