ExaCC: PDB in restricted mode and bind parameter error when applying RU SQL patch

Share on:

A couple of weeks ago, I was helping out a teammate 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 was 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

It seemed that the SQL patch wasn’t installed in the PDB (pluggable database) but it was in the CDB (constant database), so what we did next was run the 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 didn’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 I added 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 wasn’t very clear on what was erroring out, so finding the issue based on a non-descriptive error was what caused a delay in solving this issue. Hope this blog helps you in solving this issue should you face it when patching.

Share on:

More from this Author

OCI, Terraform & IaC Creating Compartments for CIS Foundation Architecture by Gustavo

OCI, Terraform & IaC: Creating Compartments for CIS Foundation Architecture

In this third blog post series, we will be creating four main compartments Security Compartment Network Compartment App/Dev Compartment Database ... Read More

OCI, Terraform & IaC Creating a Compartment

OCI, Terraform & IaC: Creating a Compartment

In my previous post, I talked about the setup of Terraform and a primer on what it is. In this blog post, I will create a simple resource in OCI. One ... Read More

Back to Top