Friday, August 23, 2024

How to cleanup temporary tablespaces in a PDB and create new ones in the right location

Notice how temporary tablespace groups can be used instead of directly referencing the temporary tablespace name:
alter session set container=PDB1;

CREATE TEMPORARY TABLESPACE TEMP3
TEMPFILE
  '/disk1/oradata/PDB1/temp03.dbf' SIZE 20G AUTOEXTEND ON NEXT 128K MAXSIZE 20G
TABLESPACE GROUP TEMP_NEW
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
FLASHBACK ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE
  '/disk1/oradata/PDB1/temp01.dbf' SIZE 1G AUTOEXTEND ON NEXT 128K MAXSIZE 20G
TABLESPACE GROUP TEMP
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
FLASHBACK ON;

CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE
  '/disk1/oradata/PDB1/temp02.dbf' SIZE 1G AUTOEXTEND ON NEXT 128K MAXSIZE 20G
TABLESPACE GROUP TEMP
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
FLASHBACK ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE TEMP3 INCLUDING CONTENTS AND DATAFILES;

exit
More about temporary tablespace groups in Oracle 19c here

Our solution to ORA-00600: internal error, arguments: [kkmmctbf:bad intcoln]

We were facing a problem in one of our databases with jobs that stopped immediately after attempting to start, and then aborting their respecitive processes immediately.

Error:
2024-08-20T10:09:51.641274+02:00
Errors in file /orasw/rdbms/diag/rdbms/cdb/cdb/trace/cdb_j000_3187052.trc:
ORA-00600: intern feilkode, argumenter: [kkmmctbf:bad intcoln], [0], [], [], [], [], [], [], [], [], [], []
2024-08-20T10:09:51.645053+02:00
opidrv aborting process J000 ospid (3187052) as a result of ORA-600
Cause:

Right prior to the incident we ran out of free space in the SYSTEM tablespace, and a new datafile was promptly added.
The internal table SYS.SOURCE$ contains all the PL/SQL code for the database ( Oracle provided code and your own code )
Our schemas are editioned, and for some reason we seem to be hitting bug number 14163397: "Trigger on editioning view can get ORA-600 [kkmmctbf:bad intcoln] (Doc ID 14163397.8)"

Solution:
alter system set "_ignore_fg_deps"=ALL container=all scope=both;
The above parameter is used to ignore fine grain dependencies during invalidation. Flush the shared pool and buffer cache if its holding up in memory the old versions:
alter system flush shared_pool;
alter system flush buffer_cache;
Wait for a little while, then the jobs started to run automatically again.

The above solution was found with assistance from Oracle Support and should not be attempted in a production environment without prior investigation and only upon recommendation from Oracle Support.

Wednesday, August 7, 2024

How to find a failed job in the data dictionary tables

My adrci output showed a failed job (abbreviated):
ADR Home = /u01/oracle/diag/rdbms/mydb01/mydb01:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- -------------------------
9720716              ORA 600 [kdxolrs2_comp: samekey]                            2024-08-06 17:37:32.674000 +02:00
9720796              ORA 600 [kdxolrs2_comp: samekey]                            2024-08-06 17:37:24.616000 
2 rows fetched (*** more available ***)

adrci> show incident -mode detail -p "incident_id=9720716"

   INCIDENT_ID                   9720716
   STATUS                        ready
   CREATE_TIME                   2024-08-06 17:37:32.674000 +02:00
   PROBLEM_ID                    21
   PROBLEM_KEY                   ORA 600 [kdxolrs2_comp: samekey]
   FIRSTINC_TIME                 2024-08-06 17:37:24.616000 +02:00
   LASTINC_TIME                  2024-08-06 17:37:32.674000 +02:00
   KEY_NAME                      PQ
   KEY_NAME                      Service
   KEY_VALUE                     SYS$USERS
   KEY_NAME                      Module
   KEY_VALUE                     DBMS_SCHEDULER
   KEY_NAME                      Action
   KEY_VALUE                     ORA$AT_OS_OPT_SY_125693
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/oracle/diag/rdbms/mydb01/mydb01/incident/incdir_9720716/mydb01_p007_11096_i9720716.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/oracle/diag/rdbms/mydb01/mydb01/trace/mydb01_p007_11096.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/oracle/diag/rdbms/mydb01/mydb01/incident/incdir_9720716/mydb01_mz00_1311372_i9720716_a.trc
1 row fetched

The file mydb01_p007_11096_i9720716.trc shows:
*** MODULE NAME:(DBMS_SCHEDULER) 2024-08-06T17:37:32.699564+02:00
*** ACTION NAME:(ORA$AT_OS_OPT_SY_125693) 2024-08-06T17:37:32.699567+02:00

[TOC00002]
========= Dump for incident 9720716 (ORA 600 [kdxolrs2_comp: samekey]) ========

*** 2024-08-06T17:37:32.709272+02:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=82xfc2m10quym) -----
select /*+ opt_param('_optimizer_use_auto_indexes' 'on')  
parallel_index(t, "mypk",8)  
dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) 
no_monitoring xmlindex_sel_idx_tbl 
opt_param('optimizer_inmemory_aware' 'false') 
no_substrb_pad  no_expand index_ffs(t,"mypk") */ 
count(*) as nrw,approx_count_distinct(sys_op_lbid(632968,'L',t.rowid)) as nlb,null as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) 
as clf from "myuser"."mytable" t where "ID" is not null
[TOC00003-END]
To find the record in the data dictionary holding the history for this run, use the following query:
set lines 300
col client_name format a30
col job_name format a30
col job_status format a20
col job_start_time format a30
col job_duration format a20
col job_info format a80

SELECT client_name,job_name,job_status,to_char(job_start_time,'dd.mm.yyyy hh24:mi:ss') "job_start_time" ,job_duration,job_info
FROM  dba_autotask_job_history 
WHERE JOB_NAME = 'ORA$AT_OS_OPT_SY_125693';
CLIENT_NAME JOB_NAME JOB_STATUS job_start_time JOB_DURATION JOB_INFO
auto optimizer stats collection ORA$AT_OS_OPT_SY_125693 FAILED 06.08.2024 17:00:02 +000 00:37:34 ORA-12801: error signaled in parallel query server P007 ORA-00600: internal error code, arguments: [kdxolrs2_comp: samekey], [0], [0], [0], [0], [], [], [], [], [], [], []
Further details can be seen in the view DBA_OPTSTAT_OPERATION_TASKS, if necessary.

Workaround for ORA-65011: Pluggable database does not exist in an EBS environment

If you have upgraded an EBS database to Oracle 19c, you will also have migrated to the multitenant architechture, which is required.

During the upgrade, Oracle set the parameter _pdb_name_case_sensitive to TRUE by default, as explained in Doc ID 2642230.1.

If you then proceeded to convert your old EBS database to a pluggable database with a lower case name, this has the effect that a "flashback pluggable database" operation will fail when using sqlplus:
oracle@oraserver01:[pdb1]# echo $ORACLE_SID
cdb
oracle@oraserver01:[pdb1]# sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 6 10:23:55 2024
Version 19.23.0.0.0

SYS@_container_name SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 pdb1                           MOUNTED
SYS@_container_name SQL> flashback pluggable database "pdb1" to restore point SD76759_1;
flashback pluggable database "pdb1" to restore point SD76759_1
*
ERROR at line 1:
ORA-65011: Pluggable database PDB1 does not exist.


Workaround: Use RMAN instead
oracle@oraserver01:[pdb1]# rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 6 10:26:51 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB (DBID=2245029826)

RMAN> flashback pluggable database "pdb1" to restore point SD76759_1;

Starting flashback at 06-AUG-24

starting media recovery

archived log for thread 1 with sequence 21576 is already on disk as file /oradisk3/recoveryarea/CDB/archivelog/2024_07_11/o1_mf_1_21576__nw9trq2c_.arc
media recovery complete, elapsed time: 00:00:01
Finished flashback at 06-AUG-24