datapatch fails when running against an Oracle Container Database because of missing temp files in PDBs

When going to 19.15. a customer hit this error when running datapatch:

Unsupported named object type for bind parameter at /u01/app/oracle/product/19.0.0.0/dbhome1915/sqlpatch/sqlpatch.pm line 5849, line 4452.

After anlyzing the issue it turned out to be related to what has been documented in My Oracle Support Note
Datapatch Failed with Error:”ORA-25153: Temporary Tablespace is Empty” (Doc ID 2285159.1)

In our case we had 3 pluggable DBs, which had a temporary tablespace TEMP without temp-files. I.e. this issue may happen not only for PDB PDB$SEED as mentioned in the Note, but also for any other PDB as well.
To check if you are affected you may run the following statements when connected to the root container:

SQL> alter session set "_exclude_seed_cdb_view"=FALSE;

Session altered.

REMARK: It’s important to set the parameter “_exclude_seed_cdb_view”=FALSE here, because otherwise the view cdb_temp_files would hide PDB$SEED.

SQL> column file_name format a32
SQL> select pd.con_id, name, file_name
  2  from v$pdbs pd left outer join cdb_temp_files tem on tem.con_id=pd.con_id
  3  where file_name is null;

    CON_ID NAME       FILE_NAME
---------- ---------- ------------------------------
         8 CRMV12
        10 CRMV122
        11 APP128

If everything would be correct above query would not return rows.

So let’s check the default temporary tablespace for our PDBs:

SQL> connect / as sysdba
SQL> select con_id, property_value 
  2  from cdb_properties
  3  where property_name='DEFAULT_TEMP_TABLESPACE'
  4  and con_id in (8,10,11);

    CON_ID PROPERTY_VALUE
---------- ---------------
         8 TEMP
        10 TEMP
        11 TEMP

To fix the missing temp files just add them. E.g. for PDB CRMV12:

SQL> alter session set container=CRMV12;

Session altered.

SQL> select name from v$tempfile;

no rows selected

SQL> alter tablespace temp add tempfile;

Tablespace altered.

REMARK: As I’m using ASM with Oracle Managed Files (OMF), I do not need to provide a file-name.

SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------------------
+DATA/CRM12X_011/D6A585D8BD6226BCE05387775C0AC88B/TEMPFILE/temp.1622.1111235741

Finally just double check that everything is correct then:

SQL> connect / as sysdba
SQL> alter session set "_exclude_seed_cdb_view"=FALSE;

Session altered.

SQL> select pd.con_id, name, file_name
  2  from v$pdbs pd left outer join cdb_temp_files tem on tem.con_id=pd.con_id
  3  where file_name is null;

no rows selected

Afterwards datapatch will (hopefully 😉 ) run through.

Summary: In Data Guard environments the Standby-DB may not have temporary files in PDBs, because with Active Data Guard (ADG) no temporary files are created on Standby when creating a PDB on Primary (with ADG other files will be copied when creating a PDB from another PDB in the CDB, but not the temporary files). If you do a switchover and then run datapatch then datapatch may fail on the new primary because of the missing temp-files. Just add the temp-files as mentioned in this Blog to fix the issue. There’s usually no need to specify a size for the temp-file as that information will be taken from the definition of the temporary tablespace (TEMP in the case above). If you have more than 1 temp-file then add files accordingly.