When running datapatch on your database you may encounter this error:
Adding patches to installation queue and performing prereq checks…done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB2 PDB3
The following interim patches will be rolled back:
37102264 (OJVM RELEASE UPDATE: 19.26.0.0.250121 (37102264))
26749785 (PERF_DIAG NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK)
27605010 (DST UPGRADE SLOW (BEGIN_UPGRADE AND UPGRADE_DATABASE) EVEN WITH PATCH 19704999)
37470729 (DATAPUMP BUNDLE PATCH 19.26.0.0.0)
Patch 38291812 (Database Release Update : 19.29.0.0.251021 (38291812)):
Apply from 19.26.0.0.0 Release_Update 250122025807 to 19.29.0.0.0 Release_Update 251018113041
The following interim patches will be applied:
38194382 (OJVM RELEASE UPDATE: 19.29.0.0.251021 (38194382))
38535360 (DATAPUMP BUNDLE PATCH 19.29.0.0.0)
Unsupported named object type for bind parameter at /oracle/app/oracle/product/19.29.0.0.251021-214/sqlpatch/sqlpatch.pm line 6072.
This error happens because one TEMP tablespace is missing a tempfile or have a tempfile corrupt.
Lets find out which database is missing a tempfile.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 PDB2 READ WRITE NO
7 PDB3 READ WRITE NO
SQL> select distinct CON_ID from cdb_temp_files;
CON_ID
----------
7
1
3
We can see that a tempfile is missing from our PDB number 5.
SQL> alter session set container=PDB2;
Session altered.
SQL> select name from v$tempfile;
NAME
-----------------------------------------------------------------------------
+DATA3/CDBPRE3A/3C166BEB280301E4E063AC131CAE7139/TEMPFILE/temp.315.1208871765
so we have a file. Lets try to add a new one on the tablespace
SQL> alter tablespace temp add tempfile;
alter tablespace temp add tempfile
*
ERROR at line 1:
ORA-01122: database file 207 failed verification check
ORA-01110: data file 207: '+DATA/CDB3A/4D277CFC391410F5F174BD242DBF8240/TEMPFILE/temp.315.1208871765'
ORA-01203: wrong incarnation of this file - wrong creation SCNSo our tempfile is from an old incarnation. We can see the same error on the alert log. To fix this, we recreate a new temporary tablespace because Oracle won’t let you drop the datafile.
SQL> create temporary tablespace temp2;
Tablespace created.
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SQL> create temporary tablespace temp;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> drop tablespace temp2 including contents and datafiles;
Tablespace dropped.one recreated, you can rerun datapatch