When using the utility datapatch to either apply or rollback patches in my Oracle 19c ORACLE_HOME, I was suprised at the time it took to (in this case) rollback the patches from version 19.23 to 19.22.
The patching was just "hanging" without any sign of progress. I left the shell for several hours, thinking progress would eventually be made and patience would be needed.
When I came back the morning after, it had not progressed at all.
The solution was found in the logfiles located $ORACLE_BASE/cfgtoollogs:
cd $ORACLE_BASE/cfgtoollogs/sqlpatch
# list your directories. Enter the most recent ones:
ls -latr
drwxr-xr-x 2 oracle dba 4.0K Feb 1 18:25 sqlpatch_9072_2024_02_01_18_13_47
drwxr-xr-x 2 oracle dba 4.0K May 14 09:21 sqlpatch_2561578_2024_05_14_09_20_33
-rw-r--r-- 1 oracle dba 0 May 23 18:12 36199232_25601966.lock
drwxr-xr-x 3 oracle dba 4.0K May 23 18:12 36199232
-rw-r--r-- 1 oracle dba 0 May 23 18:12 36420641_25643730.lock
drwxr-xr-x 3 oracle dba 4.0K May 23 18:12 36420641
-rw-r--r-- 1 oracle dba 0 May 23 18:12 36233263_25638263.lock
drwxr-xr-x 3 oracle dba 4.0K May 23 18:12 36233263
drwxr-xr-x 2 oracle dba 4.0K May 23 18:25 sqlpatch_4642_2024_05_23_18_11_41
drwxr-xr-x 2 oracle dba 4.0K Jul 12 10:30 sqlpatch_1073748_2024_07_12_10_13_30
cd sqlpatch_1073748_2024_07_12_10_13_30
ls -altrh
total 75M
-rw-r--r-- 1 oracle dba 12K Jul 12 10:14 install1.sql
-rw------- 1 oracle dba 3.7M Jul 12 10:26 sqlpatch_catcon_0.log
-rw------- 1 oracle dba 689 Jul 12 10:26 sqlpatch_catcon__catcon_1073748.lst
-rw-r--r-- 1 oracle dba 12K Jul 12 10:26 sqlpatch_summary.json
-rw-r--r-- 1 oracle dba 133 Jul 12 10:26 sqlpatch_progress.json
-rw-r--r-- 1 oracle dba 5.5M Jul 12 10:26 sqlpatch_invocation.log
-rw-r--r-- 1 oracle dba 66M Jul 12 10:26 sqlpatch_debug.log
The source of the error was found in the file sqlpatch_catcon_0.log:
CREATE OR REPLACE PACKAGE BODY ku$_dpload AS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.SOURCE$ by 8192 in tablespace SYSTEM
This simple error caused the rest of the script to fail:
SQL> --
SQL> -- ============================================================================
SQL> -- BEGINNING OF APPLY EXECUTION
SQL> -- ============================================================================
SQL> --
SQL> -- Initial phase sets up internal infrastructure for rest of dpload.
SQL> --
SQL> SELECT ku$_dpload.prepare('setup') AS fname FROM sys.dual;
SELECT ku$_dpload.prepare('setup') AS fname FROM sys.dual
*
ERROR at line 1:
ORA-04067: not executed, package body "SYS.KU$_DPLOAD" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KU$_DPLOAD"
ORA-06512: at line 1
Obvious solution: Increase the tablespace datafile, or add a new file:
sqlplus / as sysdba
alter session set container=PDB01;
select f.file_name,f.bytes/1024/1024 "MB", f.autoextensible,f.maxbytes/1024/1024 "MaxMB", t.bigfile
from dba_data_files f, v$tablespace t
where f.tablespace_name='SYSTEM'
and f.tablespace_name = t.name;
| FILE_NAME |
MB |
AUTOEXTENSIBLE |
MaxMB |
BIGFILE |
| /oradata/pdb01/system01.dbf |
32712 |
YES |
32767,984375 |
NO |
So we have a smallfile tablespace which is full!
Add a datafile, still in the same session connected to the PDB01:
alter tablespace system
add datafile '/oradata/pdb01/system02.dbf' size 256M autoextend on next 128M maxsize unlimited;
