datapatch Fails with Error:"ORA-25153: Temporary Tablespace is Empty" (Doc ID 2285159.1)

Last updated on DECEMBER 21, 2022

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Symptom 1

$ datapatch
SQL Patching tool version 19.8.0.0.0 Production on Wed Aug 19 06:28:49 2020
Copyright (c) 2012, 2020, Oracle. All rights reserved.
|
Bootstrapping registry and package to current versions...done
Determining current state...done
|
Current state of release update SQL patches:
Binary registry:
19.8.0.0.0 Release_Update 200703031501: Installed
PDB CDB$ROOT:
Applied 19.7.0.0.0 Release_Update 200404035018 successfully on 12-JUL-20 05.34.12.082123 AM
PDB PDB$SEED:
Applied 19.7.0.0.0 Release_Update 200404035018 successfully on 12-JUL-20 05.34.16.197940 AM
PDB <PDBNAME>:
Applied 19.7.0.0.0 Release_Update 200404035018 successfully on 12-JUL-20 05.34.21.325563 AM
|
Unsupported named object type for bind parameter at <ORACLE_HOME>/sqlpatch/sqlpatch.pm line 5382.

From sqlpatch_debug.log

sql_error_handler called: [2020-08-19 08:24:46] 1 ADD_TO_QUEUE PDB$SEED: <DBD::Oracle::st bind_param failed: ORA-25153: Temporary Tablespace is Empty (DBD ERROR: OCILobCreateTemporary) [for Statement "DECLARE
rec dba_registry_sqlpatch%ROWTYPE;
BEGIN
rec.patch_id := :patch_id;
rec.patch_uid := :patch_uid;

Symptom 2

datapatch execution failed tempfile in temporary tablespace is empty in PDB$SEED and also it shows junk characters :

DBD::Oracle::st bind_param failed: ORA-25153: Temporary Tablespace is Empty (DBD ERROR: OCILobCreateTemporary) [for Statement "BEGIN
               dbms_sqlpatch.set_patch_metadata(
                 p_patch_id => 25397136,
                 p_patch_uid => 21203239,
                 p_superseding => FALSE,
                 p_patch_descriptor => :descriptor,
                 p_patch_directory => :directory);
             END;" with ParamValues: :descriptor=OCIXMLTypePtr=SCALAR(0x1019fd6c0), :directory='PK........Mv.J4նƴ...ͣ......25397136_apply.sqlͮ[sÈ..཮}Ѯ3Uٮ..lj׆q.ʕ.J͌ϞP.Lٰ.ӵ?~ۮV4ɮҴ}ڂ֦..ަ...��ࠎ&׳2.5.S}Hۉ.��qJ.+wȮ*˭+I..Y.ֆ.��.׫.쇮yKτ..ŷs?��ɮO..םǒd.b/"7ퟰ��иǕh��ᯤ߮ôԚ..ꠌНmގ".xrđ0��.発ئ��9ةҽ2Yb..KЫ饋;"虮ʹ⩷.��QݨҹA..k...zͷů.ҭ`��..̍~Юʮ诙jAP.IR/    ,Ȼ٤8ˊ®~in.C��.ژힴȬ#ٮzYn.    ��.ø��ٮZ汯..E^૆ҼĮ.ū<��q:..ȟ\+ج+��ʸ< g..>.让.aƟȭ4��پذ!4\ꯃ��Cݮu...ÝXµfAÐhݗ?��׮3.å٣~姾.��ʙ%ڥ    o._Q\<즫⹮ծX��.篮.Iͮ��.߮իV,Ț
».߮..W޺NՂa؍lֲ֮ͤ毫.β쇕˶��ZӖͧGܬ%Ѻ߮ʹ)=..F.<",Xzؓɾ..FƮv깮F׵..ۣ.ѸMY0]Yn鈗��:j[.:暣..ʣu��靈Ү4.Oܯ.:ѧԱո͌ƣ}uǚ㺮.7..˖qZ.미
..޽;;ʭ8Ю˫..s.h..خ��פʜ.ՖզI.m'y/0.JY��eծI.ۮkͮB.=O.ጱ.8��C��᧡ͮ..N.ம3%٣h® g...>抳.\&.}j=u..7Yb٨n8...ȊԈ:Jïɮ��..A.Ω¦Ȯ ˦"ڋ-׻.*AS.}ςϱ.hĘ.Ӫk`.୨.[4瞯.hM.㥯,..A̬X.ܰքծՔP.̰.��)Js߮AуV.8<t.࠷ᰮ.7~.¨ʐP͜[ɭe_ͦ.��^ʪ...'] at <ORACLE_HOME>/sqlpatch/sqlpatch.pm line 3192.

Symptom 3

PDB$SEED: DBD::Oracle::st execute failed: ORA-02291: integrity constraint (SYS.DBMS_SQLPATCH_FILES_FK) violated - parent key not found
ORA-06512: at "SYS.DBMS_SQLPATCH", line 608

Symptom 4

You may also get error when tempfile is corrupt, then error will be shown like below:-

DBD::Oracle::st bind_param failed: ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '+DATA/<location>/DATAFILE/pdbseed_temp010017-02-16_12-21-19-pm.dbf' (DBD ERROR: OCILobCreateTemporary) [for Statement "BEGIN
dbms_sqlpatch.set_patch_metadata(
p_patch_id => 24917972,
p_patch_uid => 20791781,
p_superseding => FALSE, .Ãë+àºkÆó©X."(C×(.û.±¡.¾¤l.G.ø.õñV5"¢.>.¼
L2XÅ.Hx¶|.ñÙÅéùÅùx4>9W¿¾.¢)¹1l..(ÏÊ8.Kèíñ0~\ÉP.r.Ãã.^.ü1Í.o5²ØFA(X*6R._Î.