datapatch Fails with temp file issue with Error:"ORA-25153: Temporary Tablespace is Empty" or "ORA-01157: cannot identify/lock data file" or "ORA-01187: cannot read from file because it failed verification tests" (Doc ID 2285159.1)

Last updated on JUNE 27, 2024

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

sqlpatch_invocation.log shows:

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
..
Patch Patch_number rollback (pdb PDB$SEED): WITH ERRORS
logfile: unknown (errors)
-> Error at line 0: script None
- Could not find logfile in SQL registry

sqlpatch_debug.log shows:

'PDB$SEED' => {
'msg' => 'DBD::Oracle::st bind_param failed: ORA-25153: Temporary Tablespace is Empty (DBD ERROR: OCILobCreateTemporary) [for Statement "DECLARE
..
..

'PDB$SEED' => {
'msg' => 'DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "DECLARE

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._Î.

Symptom 5

sqlpatch_invocation.log shows:

Unsupported named object type for bind parameter at $ORACLE_HOME/sqlpatch/sqlpatch.pm line 4982.

*** START final state end of patching ***

sqlpatch_debug.log shows:

ADD_TO_QUEUE PDB$SEED: <DBD::Oracle::st bind_param failed: ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 202: '/u01/oradata/sid/pdbseed/temp02.dbf' (DBD ERROR: OCILobCreateTemporary) [for Statement "DECLARE
rec dba_registry_sqlpatch%ROWTYPE;
BEGIN
rec.patch_id := :patch_id;
rec.patch_uid := :patch_uid;
rec.patch_type := :patch_type;
..
..
:patch_directory='PK........﮿Vú6..��l......35354406.xmlµSÁr.0.¼��µ..0¦u;@.iǮ؎8$3=u..R!.$ܺ﫮1¤ö��´ˮ{o.Áկ.Á.ԮV<D@xV宮.Q£ɮ]EV _حVٮ¬.!��Ϯ÷ȮӓpúҮé)3.滮댩X.F3¬´.ᮨ³.㏻2D߮»��VXW9 .¯ꮳ1L¤¢%V$.µö!¥¥fù. »±G.ý¼Àyl(˛{mV
]»縳¹㸋ǻ㭉̪*..צ»IƌѶ...vXmҤ·Z.Ѯ'Ք䡴쮣´nFÀt挍"ˊ숸ھ}Xî¹I⻮q.|.᫝À.w.oû.ޮö...}3.@̮dU)*N¸.úȮ¦.䀹¨'*».'2t©Á .费¦.uQt.§��'.��....K·뵹eEf Aû..&��.8ù.¢..hH´Or¨?DǊЫú.��.r.0c¶޳!.A§ڵ°[¯sۺ¸..Ź.e.؃÷ݔv.., ܋\ہ.+EU.MnZ¨U菮..k=k.gU@߉÷..:G)¢.2��.鍳»µԮ.aְ5.1½..]׶.PK........﮿VH¥.W_...........35354406_rollback.sql.SMO㰮½ûW̮ՅB´|t.[±¤¦.8.��嶩.M.l.Vˊ.¿NӮ'..[..y3ｮT. ü.rϮS_.Ǯ┮.e��<ʊX¤9dº.?À٠lp~ޮֻ....ý.>.ѫ.Q.ӻE..A.îtN.²<Ƿ..¿㍥ڦ.£·ꮸ>.Ū..g$¨C- ¸��Q婮ϴü.ªޮԡ.Aᶂ.t§B͓¤..]¼*¼Lָ.��D/C|.¶.滎aS½ YUW Wꮆիf.
&䡮빸.)M¶H森·a.²QՄzʮ.^5)£eX.z..tug.шM&§犵..US段".*.°±Oƴ.¸.ۮaf¡."Z..
..ø.~.~ͽ.֮..õL.ᮡ1õ<.:-= .¦.Nؿ)��R祮܃#Ǉ殴.ת;!m.
Iܩ=G°ұ°e<.®蘦.ª'¶,T³Ů..',
..
..

Unsupported named object type for bind parameter at $ORACLE_HOME/sqlpatch/sqlpatch.pm line 4982.

Symptom 6

alert.log shows:

QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2024-06-20T12:25:18.992321-05:00
PDB$SEED(2):ORA-1652: unable to extend temp segment by 131072 in tablespace TEMP [PDB$SEED] ◄ ◄
2024-06-20T12:25:19.382381-05:00
alter pluggable database "PDB$SEED" close immediate force instances=('v01or550')
2024-06-20T12:25:19.407010-05:00
PDB$SEED(2):Pluggable database PDB$SEED closing