When going to 19.15. a customer hit this error when running datapatch:<\/p>\n\n\n\n
Unsupported named object type for bind parameter at \/u01\/app\/oracle\/product\/19.0.0.0\/dbhome1915\/sqlpatch\/sqlpatch.pm line 5849, line 4452.<\/p>\n\n\n\n
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)<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
SQL> alter session set \"_exclude_seed_cdb_view\"=FALSE;\n\nSession altered.<\/code><\/pre>\n\n\n\nREMARK:<\/strong> It’s important to set the parameter „_exclude_seed_cdb_view“=FALSE here, because otherwise the view cdb_temp_files would hide PDB$SEED.<\/p>\n\n\n\nSQL> column file_name format a32\nSQL> select pd.con_id, name, file_name\n 2 from v$pdbs pd left outer join cdb_temp_files tem on tem.con_id=pd.con_id\n 3 where file_name is null;\n\n CON_ID NAME FILE_NAME\n---------- ---------- ------------------------------\n 8 CRMV12\n 10 CRMV122\n 11 APP128\n<\/code><\/pre>\n\n\n\nIf everything would be correct above query would not return rows.<\/p>\n\n\n\n
So let’s check the default temporary tablespace for our PDBs:<\/p>\n\n\n\n
SQL> connect \/ as sysdba\nSQL> select con_id, property_value \n 2 from cdb_properties\n 3 where property_name='DEFAULT_TEMP_TABLESPACE'\n 4 and con_id in (8,10,11);\n\n CON_ID PROPERTY_VALUE\n---------- ---------------\n 8 TEMP\n 10 TEMP\n 11 TEMP\n<\/code><\/pre>\n\n\n\nTo fix the missing temp files just add them. E.g. for PDB CRMV12:<\/p>\n\n\n\n
SQL> alter session set container=CRMV12;\n\nSession altered.\n\nSQL> select name from v$tempfile;\n\nno rows selected\n\nSQL> alter tablespace temp add tempfile;\n\nTablespace altered.\n<\/code><\/pre>\n\n\n\nREMARK: As I’m using ASM with Oracle Managed Files (OMF), I do not need to provide a file-name.<\/p>\n\n\n\n
SQL> select name from v$tempfile;\n\nNAME\n---------------------------------------------------------------------------------------\n+DATA\/CRM12X_011\/D6A585D8BD6226BCE05387775C0AC88B\/TEMPFILE\/temp.1622.1111235741\n<\/code><\/pre>\n\n\n\nFinally just double check that everything is correct then:<\/p>\n\n\n\n
SQL> connect \/ as sysdba\nSQL> alter session set \"_exclude_seed_cdb_view\"=FALSE;\n\nSession altered.\n\nSQL> select pd.con_id, name, file_name\n 2 from v$pdbs pd left outer join cdb_temp_files tem on tem.con_id=pd.con_id\n 3 where file_name is null;\n\nno rows selected\n<\/code><\/pre>\n\n\n\nAfterwards datapatch will (hopefully \ud83d\ude09 ) run through.<\/p>\n\n\n\n
Summary:<\/strong> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"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 NoteDatapatch Failed with Error:“ORA-25153: Temporary Tablespace is Empty“ (Doc ID 2285159.1) […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[11,3,10,12],"class_list":["post-12","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-datpaatch","tag-oracle","tag-pdb","tag-temporary-files"],"_links":{"self":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts\/12","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/comments?post=12"}],"version-history":[{"count":1,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts\/12\/revisions"}],"predecessor-version":[{"id":13,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/posts\/12\/revisions\/13"}],"wp:attachment":[{"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/media?parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/categories?post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/cbleile.de\/wordpress\/wp-json\/wp\/v2\/tags?post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}