20 Jan ExaCC : PDB In Restricted Mode And Bind Parameter Error When Applying RU Sql Patch
A couple of weeks ago, I was helping out a teamate with a post patch issue on the ExaCC, The problem was that after applying the 19.12 RU to the DB ORACLE_HOME, the PDBs were left in restricted mode and couldn’t be opened. So my first train of thought is to check the PDB_PLUG_IN_VIOLATIONS to see what was going on
SYS@hostname1> SET LINESIZE 200
COLUMN name FORMAT A30
COLUMN cause FORMAT A30
COLUMN message FORMAT A30
COLUMN type format a10
SELECT name, type,cause, message
FROM pdb_plug_in_violations
WHERE status <> 'RESOLVED'
ORDER BY name;
NAME TYPE CAUSE MESSAGE
...
PDBORCL ERROR SQL Patch Interim patch 32876380/24269510 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)): Installed in the CDB but not in the PDB
Seems that the SQL patch wasn’t installed in the PDB but it was in the CDB, so what we did next was to run datapatch against the CDB. And what we saw was the error “Unsupported named object type for bind parameter at “
[oracle@hostname1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.12.0.0.0 Production on Thu Jan 6 14:57:10 2022
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_156802_2022_01_06_14_57_10/sqlpatch_invocation.log
For the following PDBs: PDBORCL
The following interim patches will be rolled back:
32067171 (OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171))
Patch 32904851 (Database Release Update : 19.12.0.0.210720 (32904851)):
Apply from 19.10.0.0.0 Release_Update 210108185017 to 19.12.0.0.0 Release_Update 210716141810
The following interim patches will be applied:
32876380 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380))
Unsupported named object type for bind parameter at /u02/app/oracle/product/19.0.0.0/dbhome_7/sqlpatch/sqlpatch.pm line 5674.
Please refer to MOS Note 1609718.1 and/or the invocation log
/u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_156802_2022_01_06_14_57_10/sqlpatch_invocation.log
for information on how to resolve the above errors.The main problem was that looking at the log it did’t say anything significant, except that same error . So what I did was run the datapatch in debug mode and found in the sqlpatch_debug.log the following error “DBD::Oracle::st bind_param failed: ORA-25153: Temporary Tablespace is Empty” . What this error meant is that another PDB in the CDB didn’t have any TEMP files.
[oracle@hostname1 OPatch]$ ./datapatch -verbose -debug
[oracle@hostname1 OPatch]$ more /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_156803_2022_01_06_17_31_30/sqlpatch_debug.log
...
[2022-01-06 17:33:14] check_queue_prereqs checking patch 32067171/23947975 for PDB TEMPPDB
[2022-01-06 17:33:14] patch_zip: /u02/app/oracle/product/19.0.0.0/dbhome_7/sqlpatch/32067171/23947975/32067171.zip
[2022-01-06 17:33:14] descriptor from File::Slurp: <?xml version="1.0" encoding="utf-8"?>
<sqlPatch ID="32067171"
uniquePatchID="23947975"
applicationPatch="NO"
jvm="YES"
startupMode="normal"
estimatedApplyTime="5"
estimatedRollbackTime="5"
buildTimestamp="201105040502"
buildDescription="NONE"
patchType="INTERIM"
featureVersion="19"
ruVersion="19.10.0.0.0">
<patchDescription>OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171)</patchDescription>
<!-- All components contained in this patch -->
<component sequence="1">CATALOG</component>
<!-- All files contained in this patch -->
<sqlFiles>
<file mode="apply" new="yes" estimatedTime="5" component="CATALOG" sequence="1">javavm/install/jvmpsupi.sql</file>
<file mode="rollback" new="yes" estimatedTime="5" component="CATALOG" sequence="1">javavm/install/jvmpsupdi.sql</file>
</sqlFiles>
<!-- Release Update Data for this patch -->
<ruData>
</ruData>
<!-- Contents of rollback_files for this patch -->
<rollbackFilesData>
<rollbackVersion>19.1.0.0.0</rollbackVersion>
</rollbackFilesData>
</sqlPatch>
[2022-01-06 17:33:14] preparing to read /u02/app/oracle/product/19.0.0.0/dbhome_7/sqlpatch/32067171/23947975/32067171.zip
[2022-01-06 17:33:14] Read 8333 bytes
[2022-01-06 17:33:14] sql_error_handler called: [2022-01-06 17:33:14] 1 ADD_TO_QUEUE TEMPPDB: <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;
rec.patch_type := :patch_type;
rec.action := :action;
rec.description := SUBSTR(:description, '1', '100');
rec.flags := :flags;
rec.patch_descriptor := :patch_descriptor;
rec.patch_directory := :patch_directory;
rec.source_version := :source_version;
rec.source_build_description := :source_build_description;
rec.source_build_timestamp :=
TO_TIMESTAMP(:source_build_timestamp, 'YYMMDDHH24MISS');
rec.target_version := :target_version;
rec.target_build_description := :target_build_description;
rec.target_build_timestamp :=
TO_TIMESTAMP(:target_build_timestamp, 'YYMMDDHH24MISS');
sys.dbms_sqlpatch.set_patch_metadata(rec);
END;" with ParamValues: :action='rollback', :description='OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171)', :flags='NJ', :patch_descriptor=OCIXMLTypePtr=SCALAR(0x4099070), :patch_directory='PK........Ð@+R;~`...........32067171_rollback.sql.SQOÛ0.~÷¯¸.©..¢.Ø*V1aRÓFJ.Êv@ìÅr..ÂÒ$KÒjLüø9MS...¸·8w÷Ý}ßw.J.._QîùrêKð.B.º@²,~.b.GY 4.L.ó;8>:ü6è.ú½£ãÓ.Áéà+.ÑK.Q.ÓÛE..A.Ã.tú.Y.ã» ßñ¦Rm..ÑkuE..Âb.ÇÍ3.Ô¡..Üyj.áñ.p.¨ò4.gzþKUï.ê°. p=¡ ºÅC¡æiR..Ê.^.a.&k¼.x.ÌT¢.!Þ.[.ó.g¯©Þ.¬ª«.Ö+uLãêu3Ä..ò0õ<|.)M¶Hæã nÃ.e£ªô.5!¼jRFË°(õ23éêÆ..&£..L.N.k+!ª¦Ì.jD$U.ac.é.q)·-ÂÌBOD´....1.ñ.ý&ý.{.¬¿.ºÔ3].C.ÄÔó.è´ô0.H..8a.¤p¸ÓG.:/£ä..9Þ3.¤ <WKÜ.i»THâNë9....m¬þ!B.tl³MU0[.ªÙâ Þ%J¢2Òqô7ìfjû.Ô.g?vSì¿JÍhêWÛ.¦¾.þ.õ¯wø¸E$Þ."£ü.õv'.%Ft.ñâ.Ì.ª¾7..q$åæ2.°=..?.Ï9eR kB]b.Ì.9|S&Ë.T*Ýëµ^/{[ÈÞýz..«,.. Z£ÎÇe...S ö.y.\8.(ç..ß.y¢.¼-Ùóm»ø½y.c.ç7nÇ9?ï|.£ÚN¯Zi.%.#
¡.é
B/é»0ø¨ï?eûú.ÓÅ.ý.PK........Ð@+R.ì|6[...........32067171_apply.sql.SÁNã0.½û+æ.ê"!J.ÝXaRÓFJ.Êv@ìÅ2M
Ai.mÒjYññë$M!, ¹Å..7óÞ.A%.ʯ)÷|9õ%x !N] Y.?A>[FY.ót ..f.pr|ô}Ð.ô{Ç'g§.³Á74¢W6£.§÷ó(..h çÐé#dy.ï2È.ÇU¥Ú$.£7ê.8>.ù*..g$¨C- ¸óÒ.Ãó3à.Qér"U>"¨Ã".ÂÍ.2èæO¹.¥I.þ)ºx..Ë0Yã.À³àN%z.â}°.0ßqö.ê*dY]&´^©c..¯Õ..&,Ã,Ö³ðc¤4Ù ..=Ü..ÊFe.é)kBxÙ¤..a^èEfÒÕ 7MF#5...*×VB.M.#...', :patch_id='32067171', :patch_type='INTERIM', :patch_uid='23947975', :source_build_description=undef, :source_build_timestamp=undef, :source_version=undef, :target_build_description=undef, :target_build_timestamp=undef, :target_version=undef]>
[2022-01-06 17:33:14] contents of failed PDB array:
[2022-01-06 17:33:14] $VAR1 = 'Data::Dumper';
$VAR2 = {
'TEMPPDB' => {
'msg' => '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;
rec.patch_type := :patch_type;
rec.action := :action;
rec.description := SUBSTR(:description, \'1\', \'100\');
rec.flags := :flags;
rec.patch_descriptor := :patch_descriptor;
rec.patch_directory := :patch_directory;
rec.source_version := :source_version;
rec.source_build_description := :source_build_description;
rec.source_build_timestamp :=
TO_TIMESTAMP(:source_build_timestamp, \'YYMMDDHH24MISS\');
rec.target_version := :target_version;
rec.target_build_description := :target_build_description;
rec.target_build_timestamp :=
TO_TIMESTAMP(:target_build_timestamp, \'YYMMDDHH24MISS\');
sys.dbms_sqlpatch.set_patch_metadata(rec);
END;" with ParamValues: :action=\'rollback\', :description=\'OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171)\', :flags=\'NJ\', :patch_descriptor=OCIXMLTypePtr=SCALAR(0x4099070), :patch_directory=\'PK........Ð@+R;~`...........32067171_rollback.sql.SQOÛ0.~÷¯¸.©..¢.Ø*V1aRÓFJ.Êv@ìÅr..ÂÒ$KÒjLüø9MS...¸·8w÷Ý}ßw.J.._QîùrêKð.B.º@²,~.b.GY 4.L.ó;8>:ü6è.ú½£ãÓ.Áéà+.ÑK.Q.ÓÛE..A.Ã.tú.Y.ã» ßñ¦Rm..ÑkuE..Âb.ÇÍ3.Ô¡..Üyj.áñ.p.¨ò4.gzþKUï.ê°. p=¡ ºÅC¡æiR..Ê.^.a.&k¼.x.ÌT¢.!Þ.[.ó.g¯©Þ.¬ª«.Ö+uLãêu3Ä..ò0õ<|.)M¶Hæã nÃ.e£ªô.5!¼jRFË°(õ23éêÆ..&£..L.N.k+!ª¦Ì.jD$U.ac.é.q)·-ÂÌBOD´....1.ñ.ý&ý.{.¬¿.ºÔ3].C.ÄÔó.è´ô0.H..8a.¤p¸ÓG.:/£ä..9Þ3.¤ <WKÜ.i»THâNë9....m¬þ!B.tl³MU0[.ªÙâ Þ%J¢2Òqô7ìfjû.Ô.g?vSì¿JÍhêWÛ.¦¾.þ.õ¯wø¸E$Þ."£ü.õv\'.%Ft.ñâ.Ì.ª¾7..q$åæ2.°=..?.Ï9eR kB]b.Ì.9|S&Ë.T*Ýëµ^/{[ÈÞýz..«,.. Z£ÎÇe...S ö.y.\\8.(ç..ß.y¢.¼-Ùóm»ø½y.c.ç7nÇ9?ï|.£ÚN¯Zi.%.#
¡.é
B/é»0ø¨ï?eûú.ÓÅ.ý.PK........Ð@+R.ì|6[...........32067171_apply.sql.SÁNã0.½û+æ.ê"!J.ÝXaRÓFJ.Êv@ìÅ2M
Ai.mÒjYññë$M!, ¹Å..7óÞ.A%.ʯ)÷|9õ%x !N] Y.?A>[FY.ót ..f.pr|ô}Ð.ô{Ç\'g§.³Á74¢W6£.§÷ó(..h çÐé#dy.ï2È.ÇU¥Ú$.£7ê.8>.ù*..g$¨C- ¸óÒ.Ãó3à.Qér"U>"¨Ã".ÂÍ.2èæO¹.¥I.þ)ºx..Ë0Yã.À³àN%z.â}°.0ßqö.ê*dY]&´^©c..¯Õ..&,Ã,Ö³ðc¤4Ù ..=Ü..ÊFe.é)kBxÙ¤..a^èEfÒÕ 7MF#5...*×VB.M.#...\', :patch_id=\'32067171\', :patch_type=\'INTERIM\', :patch_uid=\'23947975\', :source_build_description=undef, :source_build_timestamp=undef, :source_version=undef, :target_build_description=undef, :target_build_timestamp=undef, :target_version=undef]',
'phase' => 'ADD_TO_QUEUE'So what I did was add a TEMP file to TEMPPDB, reran the datapatch and bounced the PDBs and everything was up and running again
SYS@hostname1> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATAC1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
Tablespace altered.
SYS@hostname1>exit
[oracle@hostname1 OPatch]$ ./datapatch -verbose
...
SQL Patching tool complete on Thu Jan 6 18:58:05 2022
SYS@hostname1> alter pluggable database PDBORCL close immediate instances=ALL;
Pluggable database altered.
SYS@hostname1> alter pluggable database PDBORCL OPEN instances=ALL;
Pluggable database altered.
SYS@hostname1> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
8 PDBORCL READ WRITE NO
As you can see, the fix was quite simple, but the problem was that the datapatch log really wasn’t very clear on what was erroring out, so finding the issue based on a non descriptive error was what caused a delay on solving this issue. Hope this blog helps you in solving this issue should you face it when patching.
Sorry, the comment form is closed at this time.