IV91530: ITM KRZ AGENT DOES NOT CORRECTLY LOCATE ALERT LOG FILE IN ORACLE DB 12C

ITCAM Extended Agent for Oracle Database: 6.3.1.2-TIV-ITM_KRZ-IF0005
ITCAM Extended Agent for Oracle Database: 6.3.1.2-TIV-ITM_KRZ-IF0006
ITCAM Extended Agent for Oracle Database: 6.3.1.2-TIV-ITM_KRZ-IF0007

APAR status

• Closed as program error.

Error description

• When the Oracle Database Extended agent monitors Oracle 12c
  database, the query issued to discover the database alert log
  does not correctly locate the database's Alert Log path. This
  only applies to 12c.
  

Local fix

• 1) Stop the agent
  2) Go to the <AGENT_HOME>/TMAITM_x64 directory
  3) Backup the krzsql.xml file
  4) Modify the following
  
  <SQLInfo ID="GetAlertLogDefinedInformation">
  <DatabaseType value="DG"/>
  <DatabaseType value="ASM"/>
  <DatabaseType value="RDB"/>
    <SQL DBVersion="101">
      <SQLText>
  select  i.instname, p.dump_dir, i.hostname, p.db_name from
  (
  select inst_id, instance_name instname, host_name hostname from
  gv$instance) i,
  (select inst_id, max(case when name='db_name' then value end)
  db_name,
    max(case when name='background_dump_dest' then value end)
  dump_dir from gv$parameter
    where name in ('db_name', 'background_dump_dest') group by
  inst_id
  ) p
  where i.inst_id = p.inst_id
  </SQLText>
      <BaseTable QueryName="GV$INSTANCE"
  GrantName="GV_$INSTANCE"/>
      <BaseTable QueryName="GV$PARAMETER"
  GrantName="GV_$PARAMETER"/>
    </SQL>
  </SQLInfo>
  
  
  To the following
  
  
  <SQLInfo ID="GetAlertLogDefinedInformation">
  <DatabaseType value="DG"/>
  <DatabaseType value="ASM"/>
  <DatabaseType value="RDB"/>
    <SQL DBVersion="101">
      <SQLText>
  select  i.instname, p.dump_dir, i.hostname, p.db_name from
  (
  select inst_id, instance_name instname, host_name hostname from
  gv$instance) i,
  (select inst_id, max(case when name='db_name' then value end)
  db_name,
    max(case when name='background_dump_dest' then value end)
  dump_dir from gv$parameter
    where name in ('db_name', 'background_dump_dest') group by
  inst_id
  ) p
  where i.inst_id = p.inst_id
  </SQLText>
      <BaseTable QueryName="GV$INSTANCE"
  GrantName="GV_$INSTANCE"/>
      <BaseTable QueryName="GV$PARAMETER"
  GrantName="GV_$PARAMETER"/>
    </SQL>
     <SQL DBVersion="120">
      <SQLText>
    select a.instance_name instname,b.value dump_dir,a.host_name
  hostname,c.db_name from
    (select inst_id,instance_name,host_name from gv$instance) a,
    (select inst_id,value from gv$diag_info where name = 'Diag
  Trace') b,
    (select inst_id, max(case when name='db_name' then value end)
  db_name from gv$parameter where name in ('db_name') group by
  inst_id) c
        where a.inst_id=b.inst_id and b.inst_id=c.inst_id
        </SQLText>
      <BaseTable QueryName="GV$INSTANCE"
  GrantName="GV_$INSTANCE"/>
      <BaseTable QueryName="GV$PARAMETER"
  GrantName="GV_$PARAMETER"/>
      <BaseTable QueryName="GV$DIAG_INFO"
  GrantName="GV_$DIAG_INFO"/>
    </SQL>
  </SQLInfo>
  
  5) Save the file
  6) start the agent
  

Problem summary

• The issue was caused by oracle changing their alert log location
   in 12c. We updated a query to check the the correct version on
  12c
  

Problem conclusion

• This request has been resolved in patch
  6.3.1.2-TIV-ITM_KRZ-IF0005. The documentation can be found here:
  https://www-01.ibm.com/support/docview.wss?uid=swg24044064
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  ITCAM EXT AGNT

• Fixed component ID

  5724V09OR

Applicable component levels

• R631 PSY

     UP