What is the $ORACLE_HOME/dbs/hc_$ORACLE_SID.dat file?

In every $ORACLE_HOME/dbs folder you will find a file named hc_$ORACLE_SID.dat.

What is it, and is it essential for your instance?

Oracle Support states:

The $ORACLE_HOME/dbs/hc_.dat is created for the instance health check monitoring. It contains information used to monitor the instance health and to determine why it went down if the instance isn't up.

The file can be deleted while the instance is up, it won't cause any harm to your instance.

In earlier versions of the Oracle database software a bug existed that would trigger an ORA-7445 if the file was deleted while the database was up, but this was fixed as early as in version 11.2.

The file is created at every instance startup.

Source: What Is The $ORACLE_HOME/dbs/hc_.dat File? (Doc ID 390474.1) from Oracle Support

Where does an Oracle EBS 12.2 appserver save logs from the concurrent worker processes?

Look in the directory $ADOP_LOG_HOME

In here, every session will create its own subfolder. In my case

10  11  2  3  4  5  6  7  8  9

In my case, I had to enter the folder named after session 11.

In here you will find folders named according to exection time, for example

20240916_135516

Inside this folder, you will find folders named according to action, for example "prepare", "cutover", or "apply".

In my case, step inside the "apply" directory and you will find a folder named after your appserver.

Finally, you will find a folder named according to the patch number, for example

36876222_N

with a log directory underneath it.

So the path $ADOP_LOG_HOME/11/20240916_135516/apply/oric-ebsapp01/36876222_N/log is the complete path to your my log directory for the session I am looking for.

How do I store multiple connections to the same database in my wallet?

If you have a wallet which already contains an alias for a database, you cannot add another one pointing to the same alias. If you do, mkstore will give you the following error:

Secret Store error occurred: oracle.security.pki.OracleSecretStoreException: Credential already exists

This is actually documented:

Each user account must have its own unique connection string; you cannot create one connection string for multiple users.

So what to do, then, if you find yourself in a situation where you need two different users to access the database in a passwordless manner using a wallet?

Workaround:

Use multiple aliases in tnsnames.ora pointing to the same database service.

List the current contents of the wallet:

mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: mydb1_scott scott

So user scott can log into the database passwordless using his own password which is securly stored in the wallet.

The tnsnames.ora knows which service to connect to whenever "mydb01" is called:

mydb1_scott =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb1.oric.no)
      (INSTANCE_NAME = cdb)
    )
  )

Let's add another connect_identifier in the tnsnames.ora file:

mydb1_jim =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb1.skead.no)
      (INSTANCE_NAME = cdb)
    )
  )

Let's test it, using a random user, for example, system:

sqlplus system@mydb1_jim
Enter password:
SQL>show user
USER is "SYSTEM"

So the connect identifier is working, it points towards a valid service being serviced by the listener on the server.

Let's add another entry to the wallet for user jim, so that he can connect without supplying his password:

mkstore -wrl $TNS_ADMIN/wallet -createCredential mydb1_jim jim
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line
Enter your secret/Password: <-- password for user jim
Re-enter your secret/Password: <-- reenter password for user jim
Enter wallet password: <-- wallet password

Jim can now login using his own password stored in the wallet:

sqlplus /@mydb1_jim
SQL>show user
USER is "JIM"

The contents of the wallet is now:

mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
2: mydb1_jim jim
1: mydb1_scott scott

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.

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