Snippets
From dbawiki
Contents
Restart ITM Monitoring agent
To see if the Oracle module is installed
export ITM_HOME=/opt/IBM/ITM $ITM_HOME/bin/cinfo -i
See which instances have been setup to be monitored
export ITM_HOME=/opt/IBM/ITM $ITM_HOME/smitools/scripts/kdyedit -h /opt/IBM/ITM list
Add an instance to be monitored
$ITM_HOME/smitools/scripts/kdyedit -h /opt/IBM/ITM -r itmora -i $ORACLE_SID -t or add
This one should also be done in order that rc.d scripts know to start the agent as itmora instead of root
$ITM_HOME/smitools/scripts/kciedit -h /opt/IBM/ITM -r itmora -i $ORACLE_SID -t or add
Restart the agent
export ITM_HOME=/opt/IBM/ITM $ITM_HOME/smitools/scripts/StartAgent.sh restart all or $ITM_HOME/smitools/scripts/StartAgent.sh start or -o SRV2R
Check the agent is running
ps -ef | grep koragent
Reconfigure agent
export ITM_HOME=/opt/IBM/ITM $ITM_HOME/bin/itmcmd config -A or
Troubleshooting
Monitoring says database inactive
- Check the config file. We had this issue when a new ORACLE_HOME was installed but the cfg file still had the old one in it.
export ITM_HOME=/opt/IBM/ITM cd $ITM_HOME/config vi solax005_or_SRV2R.cfg
# IBM Tivoli Monitoring for Databases: Oracle Agent # Configuration file: solax005_or_SRV2R.cfg # Written by CandleDBconfig version: 1.4 on 31Jul13 13:39:41 # Note: this is a Korn-shell script that is "sourced" to create the # environment for a Monitoring Agent for Oracle. It can be used to create # the environment to run the IBM-supplied database grant scripts. # IBM does not recommend that you modify this file, but you can change the # data values or add new exported variables as long as the file is a valid ksh # script that executes with zero return code, and values remain quoted with "". # Following environment variables are set for convenience in running grants. export ORACLE_SID="SRV2R" export ORACLE_HOME="/oracle/product/11.2.0.1" # IY92195 COLL_DISABLE_CURSORS setting export TNS_ADMIN="/oracle/product/11.2.0.1/network/admin" # Following variables are set for the setup scripts db_sid="SRV2R" db_home="/oracle/product/11.2.0.1" db_initfilename="" db_login="tivoli" db_password="795D8822BC49477323815CD21C1E66E966E48EEE19C6A98056224D649B0FE316E6A11DC3F4E9BB5E226B65A8" db_ver="11.x" db_type="kor" db_tns="/oracle/product/11.2.0.1/network/admin" db_extparms="" db_hostname="solax005" db_reason="AIX_ps_ceww" db_pipedir="" db_installstatus="CONFIGSUCCESS" # User-defined environment variables
- Alerts due to password expiring (or being changed in the database but not in ITM!)
cd $ITM_HOME/logs ls -altr | grep WM820T tail -20 solax005_or_WM820T_col.out
CGN1521E (161614) Interval collection failed for cursor KORHART2 RCD0110S (161614) Invalid Oracle logon id (tivoli) or password given Please contact DBA to correct userid or password CGN1521E (161614) Interval collection failed for cursor KORHART4 CGN1525E (161614) One or more interval cursors failed RCD0110S (161714) Invalid Oracle logon id (tivoli) or password given Please contact DBA to correct userid or password CGN1521E (161714) Interval collection failed for cursor KORHART2 RCD0110S (161714) Invalid Oracle logon id (tivoli) or password given Please contact DBA to correct userid or password CGN1521E (161714) Interval collection failed for cursor KORHART4 CGN1525E (161714) One or more interval cursors failed
Solution: Reconfigure the agents and setup the new password for the candle user (tivoli)
- Many agents started with root user instead of itmora
This can happen if the server is rebooted before the agents have been added to the itmora list using kcyedit / kciedit
export CANDLEHOME=/opt/IBM/ITM export ITM_HOME=/opt/IBM/ITM $ITM_HOME/smitools/scripts/kdyedit list Type Runas Inst or root AGPDEV or root BO1R or root DEV3 or root EMREP or root ICR or itmora MAPDEV or itmora MAPER or itmora PED or itmora PER or itmora RMANV11 or itmora SRV2R or itmora WM820Q or itmora WM820T
$ITM_HOME/smitools/scripts/kciedit list Type Runas Inst or root AGPDEV or root BO1R or root DEV3 or root EMREP or root ICR or itmora MAPDEV or itmora MAPER or itmora PED or itmora PER or itmora RMANV11 or itmora SRV2R or itmora WM820Q or itmora WM820T
Solution: Add these instances to the itmora list
./kdyedit -t or -i AGPDEV -r itmora add ./kciedit -t or -i AGPDEV -r itmora add ... ./kdyedit -t or -i ICR -r itmora add ./kciedit -t or -i ICR -r itmora add
Kill any agents still running as root
ps -ef |grep kor | grep root kill -9 <process id>
$ITM_HOME/smitools/scripts/kciedit list Type Runas Inst or itmora AGPDEV or itmora BO1R or itmora DEV3 or itmora EMREP or itmora ICR or itmora MAPDEV or itmora MAPER or itmora PED or itmora PER or itmora RMANV11 or itmora SRV2R or itmora WM820Q or itmora WM820T
Because these agents were owned by root, there will be permissions issues when trying to restart the agents with itmora so these need to be fixed before restarting the agents.
The simplest way of doing this is to run lockdown.
$ITM_HOME/smitools/scripts/lockdown.sh
Create a comma separated list of columns from a select statement
Method 1:
SELECT parent_id,
RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()'),',') AS "Children"
FROM parentChildTable
WHERE parent_id = 0
GROUP BY parent_id
/
or
SELECT parent_id,
LISTAGG(child_id, ',') WITHIN GROUP (ORDER BY child_id) AS "Children"
FROM parentChildTable
WHERE parent_id = 0
GROUP BY parent_id
/
Method 2 (undocumented and cannot therefore be relied on to continue working in the same manner):
SELECT wmsys.wm_concat(<column_name>) FROM <table_name> /
