Recently, I found some huge tracefiles in the ADR of some databases. Look at this:
[oracle@server01b trace]$ ls -lrth | tail -10 -rw-r----- 1 oracle dba 1.2K Apr 9 03:44 ICM01_vktm_29023.trc -rw-r----- 1 oracle dba 329K Apr 9 06:36 ICM01_dbrm_29047.trm -rw-r----- 1 oracle dba 4.7M Apr 9 06:36 ICM01_dbrm_29047.trc -rw-r----- 1 oracle dba 1.5K Apr 9 07:58 ICM01_mmon_29120.trm -rw-r----- 1 oracle dba 14K Apr 9 07:58 ICM01_mmon_29120.trc -rw-r----- 1 oracle dba 471M Apr 9 08:02 ICM01_ora_29162.trm -rw-r----- 1 oracle dba 33G Apr 9 08:02 ICM01_ora_29162.trc -rw-r----- 1 oracle dba 1.3M Apr 9 08:02 alert_ICM01.log -rw-r----- 1 oracle dba 12M Apr 9 08:02 ICM01_lmhb_29080.trm -rw-r----- 1 oracle dba 112M Apr 9 08:02 ICM01_lmhb_29080.trc
The file(s) were not purged automatically from ADR since they simply don’t get older. So I had a look into the file and I found repeating content like this:
Trace file /u01/app/oracle/diag/rdbms/icm01a/ICM01/trace/ICM01_ora_29162.trc Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/126.96.36.199/dbhome_5 System name: Linux Node name: server01b Release: 4.1.12-112.16.8.el6uek.x86_64 Version: #2 SMP Fri Apr 13 15:13:46 PDT 2018 Machine: x86_64 Instance name: ICM01 Redo thread mounted by this instance: 1 Oracle process number: 35 Unix process pid: 29162, image: oracle@jus-oda-lrz01b *** 2019-03-15 14:28:19.983 *** SESSION ID:(464.27491) 2019-03-15 14:28:19.983 *** CLIENT ID:() 2019-03-15 14:28:19.983 *** SERVICE NAME:(SYS$USERS) 2019-03-15 14:28:19.983 *** MODULE NAME:(emagent_SQL_oracle_database) 2019-03-15 14:28:19.983 *** CLIENT DRIVER:(jdbcthin) 2019-03-15 14:28:19.983 *** ACTION NAME:(problemTbspUndo) 2019-03-15 14:28:19.983 psdgbt: bind csid (1) does not match session csid (873) psdgbt: session charset is AL32UTF8 *** 2019-03-15 14:28:19.983 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0) ----- Error Stack Dump ----- ----- Current SQL Statement for this session (sql_id=a0qc12302fzfk) ----- begin dbms_application_info.set_module(:1 , :2 ); end;
There was some more callstack dumped etc., but this was not relevant to identify the root cause for this. Obviously the Enterprise Manager Agent is doing something nasty to the database. There are several documents in MOS, the best match of them is “After restart of database suddenly PLS-00553: character set name is not recognized are seen in the alert.log (Doc ID 1599864.1)“. Basically, the Agent connects too early to a starting database and picks up US7ASCII characterset. The only solution as of now is to bounce the Agent.
As a conclusion, it might be a good idea to bounce the Agent every time a database was started. May it be because the database was newly created, restarted due to maintenance or in a Data Guard switch-/failover scenario. Or just do it on a regular basis so no one has to do it by hand.