ANBOB™

一套oracle 19c 多租户环境，安装19.19RU时datapatch失败，日志提示其中某个PDB执行SQL时，ORA-25153: Temporary Tablespace is Empty (DBD ERROR: OCILobCreateTemporary) ，简单记录处理方法.

sqlpath_debug.log
search “error” key word.

[2023-09-01 13:35:20] run_preinstall_steps: preparing to read /local/oracle/product/19.3.0/db_1/sqlpatch/35050341/25148755/35050341.zip
[2023-09-01 13:35:20] run_preinstall_steps: Read 9264 bytes
[2023-09-01 13:35:20] sql_error_handler called: [2023-09-01 13:35:20] 1 ADD_TO_QUEUE PDB_XXXX:<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='apply', :description='OJVM RELEASE UPDATE: 19.19.0.0.230418 (35050341)', :flags='NJ', :patch_descriptor=OCIXMLTypePtr=SCALAR(0x3fbc5f0), :patch_directory='PK........Uhq

原因
Temporary tablespace in container(PDB_XXXX) does not contain any files

Verify if tempfile exist in temporary tablespace of PDB$SEED:

SQL> alter session set container=PDB_XXXX;

SQL> select * from dba_temp_files;

解决方法

SQL> alter session set container=PDB_XXXX;

There can be more than one temporary tablespace

SQL> select TABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces where contents='TEMPORARY';

Find the default temporary tablespace :

SQL> select * from DATABASE_PROPERTIES where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME               PROPERTY_VALUE      DESCRIPTION
--------------------------  -----------------   -------------------------------------
DEFAULT_TEMP_TABLESPACE     TEMP                Name of default temporary tablespace

Add temp file to default temporary tablespace found from above command.

SQL> alter tablespace TEMP add tempfile '< Give temp file name here>';

Re-execute the datapatch:

./datapatch -verbose

打赏

微信扫一扫，打赏作者吧～