Difference between revisions of "Snippets"

From dbawiki
Jump to: navigation, search
(→‎Restart ITM Monitoring agent)
(→‎Restart ITM Monitoring agent)
Line 33: Line 33:
 
export ITM_HOME=/opt/IBM/ITM
 
export ITM_HOME=/opt/IBM/ITM
 
$ITM_HOME/bin/itmcmd config -A or
 
$ITM_HOME/bin/itmcmd config -A or
 +
</pre>
 +
===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.
 +
<pre>
 +
export ITM_HOME=/opt/IBM/ITM
 +
cd $ITM_HOME/config
 +
vi solax005_or_SRV2R.cfg
 +
</pre>
 +
<pre>
 +
#  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
 +
</pre>
 +
* Alerts due to password expiring (or being changed in the database but not in ITM!)
 +
<pre>
 +
cd $ITM_HOME/logs
 +
ls -altr | grep WM820T
 +
tail -20 solax005_or_WM820T_col.out
 +
</pre>
 +
<pre>
 +
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
 +
</pre>
 +
Solution: Reconfigure the agents and setup the new password for the candle user (tivoli)
 +
<pre>
 +
</pre>
 +
* 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
 +
<pre>
 +
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             
 +
</pre>
 +
<pre>
 +
$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             
 +
</pre>
 +
Solution: Add these instances to the itmora list
 +
<pre>
 +
./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
 +
</pre>
 +
Kill any agents still running as root
 +
<pre>
 +
ps -ef |grep kor | grep root
 +
kill -9 <process id>
 +
</pre>
 +
<pre>
 +
$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             
 +
</pre>
 +
 +
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.<br />
 +
The simplest way of doing this is to run lockdown.
 +
<pre>
 +
$ITM_HOME/smitools/scripts/lockdown.sh
 
</pre>
 
</pre>
  

Revision as of 14:39, 1 August 2013

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>
/



References






					

						Retrieved from "https://mailnest.com/dbawiki/index.php?title=Snippets&oldid=621"