Difference between revisions of "Snippets"

Revision as of 23:36, 28 November 2013

1 Restart ITM Monitoring agent
2 Troubleshooting
3 Log file reports pipe errors
4 Monitoring says database inactive
5 Alerts due to password expiring (or being changed in the database but not in ITM!)
6 Many agents started with root user instead of itmora
7 How many redo log switches per hour?
8 Korn shell timestamp function
9 Mass update of files using perl inline script
10 Update RMAN configuration parameters from Sql*Plus
11 Execute an SQL on all databases on all servers for a customer
12 Where is the alert log/alertlog?
13 What is my WAN ip address?
14 Wait for child pid to finish in background and report its status
15 Create a comma separated list of columns from a select statement
- 15.1 References
16 Display horizontal bar graph in HTML table data cell

Restart ITM Monitoring agent

To see if the Oracle module is installed

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/bin/cinfo -i

and see which agents are running

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/bin/cinfo -r

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

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/smitools/scripts/kciedit -h /opt/IBM/ITM list

Add an instance to be monitored

$ITM_HOME/smitools/scripts/kdyedit -h /opt/IBM/ITM -r itmora -t or add -i $ORACLE_SID

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 -t or add -i $ORACLE_SID

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 $ORACLE_SID
or
/usr/bin/su - itmora -c "/opt/IBM/ITM/bin/itmcmd agent  -o EMREP  stop or"
/usr/bin/su - itmora -c "/opt/IBM/ITM/bin/itmcmd agent  -o EMREP  start or"

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

Log file reports pipe errors

Found these in the log file

solax005:root[/opt/IBM/ITM/logs]# tail solax005_or_RMANV11_col.out
CIR1880E (024955) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30024832_5346_pipe
CIR1880E (030938) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30030830_5406_pipe
CIR1880E (031923) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30031832_5434_pipe
CIR1880E (033934) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30033831_5495_pipe
CIR1880E (034918) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30034833_5521_pipe
CIR1880E (041927) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30041835_5610_pipe
CIR1880E (042931) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30042832_5642_pipe
CIR1880E (045928) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30045835_5730_pipe
CIR1880E (052927) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30052838_5819_pipe
CIR1880E (090929) Open Probe pipe error 2. Pipe=/opt/IBM/ITM/tmp/solax005_or_RMANV11/prb_RMANV11_kor_solax005_30090833_6487_pipe

This could be due to a timeout on the pipe.
Increase parameters and restart agent. Add following lines to or.config:

export COLL_WAIT_TIMEOUT='200'
export WAIT_TIMEOUT='200'

Monitoring says database inactive

There may be a clue in the log file. Check /opt/IBM/ITM/logs/`hostname`_or_${SID}_col.out
Check limits for itmora account in /etc/security/limits. Should be same as oracle user.
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

Solution:

Change all occurrences of 11.2.0.1 to 11.2.0.3
Restart agent

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:

Run agent reconfigure to setup the new password for the candle user (tivoli)

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/bin/itmcmd config -A or

Restart agent

export ITM_HOME=/opt/IBM/ITM
$ITM_HOME/smitools/scripts/StartAgent.sh restart or -o WM820T

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

$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

Kill any agents still running as root

ps -ef |grep kor | grep root
kill -9 <process id>

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.

export CANDLEHOME=/opt/IBM/ITM
$CANDLEHOME/smitools/scripts/lockdown.sh

How many redo log switches per hour?

select to_char(first_time,'yyyymmdd hh24')
,      count(1)
from   v$log_history
group  by to_char(first_time,'yyyymmdd hh24')
order  by 1

Korn shell timestamp function

Example usage: echo "`ts`: Checking backup status"

function ts {
    date +'%d-%b-%Y %H:%M:%S'
}

Mass update of files using perl inline script

for server in `cat /home/tools/etc/oracle/oracle_servers`; do
    ssh $server “perl -p -i -e 's/T01/D01/' /home/tools/scripts/rman/inclexcl.lst”
done

Update RMAN configuration parameters from Sql*Plus

If you backup the controlfile with:
alter database backup controlfile to trace as '/oracle/${OSID}/admin/${NSID}_controlfile.sql';
This example found in backup controlfile

-- Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('/oracle/product/11.2.0.3/dbs/snapshot_controlfile_WM820T.ctl');
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 31 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','''SBT_TAPE''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' FORMAT   ''LOG_%d_t%T_s%s_u%U'' PARMS  ''ENV=(TDPO_OPTFILE=/oracle/WM820T/admin/tdpo.opt)''');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 1 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','''SBT_TAPE'' TO ''LOG_%d_controlfile_%F.rman''');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''snapshot_controlfile_WM820T.ctl''');

Execute an SQL on all databases on all servers for a customer

Run from customer's management server

./dosh -c "su - oracle -c '/home/tools/scripts/oracle/all_db_do -v \"select username, account_status, profile from dba_users order by 3;\"'" >user_status.lst

Where is the alert log/alertlog?

Older systems

select value from v$parameter where name='background_dump_dest';

Newer systems

select value from v$diag_info where name='Diag Trace';

What is my WAN ip address?

curl -A "Mozilla/4.0" http://checkip.dyndns.org/ 2>/dev/null | perl -ne 'print $1 if /Current IP Address: (\d+\.\d+\.\d+\.\d+)/'

Wait for child pid to finish in background and report its status

# submit a process to the background
export_schema1.ksh &
bgpid=$!

while (ps -ef | grep $bgpid | grep -v grep); do
    # still running...
    sleep 600
done

# should be finished
wait $bgpid
bgstatus=$?
echo background process ended with status $bgstatus

or

#!/usr/bin/ksh
# submit a few processes to the background
# wait for them all to finish
# concat their logfiles

(./sleeper.sh;echo "sleeper 1 finished")>sleeper1.log &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."
sleep 5

(./sleeper.sh;echo "sleeper 2 finished")>sleeper2.log &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."
sleep 5

(./sleeper.sh;echo "sleeper 3 finished")>sleeper3.log &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."
sleep 5


echo "Now 15 seconds later, we have 3 pids: $PIDLIST"
for PID in $PIDLIST; do
    wait $PID
    echo "$PID exited with status $?"
done

# all jobs should have ended, concat their logs
cat sleeper*log > sleepers.log
cat sleepers.log

Real world example

#!/usr/bin/ksh
# ==============================================================================
# Name         : export_JDBEOP1.ksh
# Description  : Run export in pieces for performance reasons
#
# Parameters   : 
#
# Notes        : Also decide whether to exclude PDARC schema or not
#                depending on what day it is
#
#
# Modification History
# ====================
# When      Who               What
# ========= ================= ==================================================
# 15-OCT-13 Stuart Barkley    Created
# ==============================================================================

STARTTIME=`date '+%Y%m%d%H%M%S'`
DAYNO=`date +%u`
PROGNAME=`basename $0`
SCRIPTS_DIR=/home/tools/scripts/oracle
DATA_DIR=/oracle/JDBEOP1/admin/change
EXPORT_DIR=/oracle/export/JDBEOP1

# marker so we can find our files later
MARKERFILE=/tmp/start_$PROGNAME_$$
touch $MARKERFILE

$SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u PRODDTA -p 4 -k 2 -f $DATA_DIR/JDBEOP1_PRODDTA.parfile &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."

$SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u PRODCTL -p 2 -k 2 -f $DATA_DIR/JDBEOP1_PRODCTL.parfile &
PIDLIST="$PIDLIST $!"
echo "PIDLIST is $PIDLIST ..."


if [[ $DAYNO -eq 1 ]]; then
    $SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u NOPROD -p 4 -k 2 -f $DATA_DIR/JDBEOP1_NOPROD.parfile &
    PIDLIST="$PIDLIST $!"
    echo "PIDLIST is $PIDLIST ..."
else
    $SCRIPTS_DIR/expdp_PARFILE.ksh -s JDBEOP1 -u NOPROD_NOPDARC -p 4 -k 2 -f $DATA_DIR/JDBEOP1_NOPROD_NOPDARC.parfile &
    PIDLIST="$PIDLIST $!"
    echo "PIDLIST is $PIDLIST ..."
fi

# now sit and wait for the exports to finish
for PID in $PIDLIST; do
    echo "waiting for pid $PID to finish"
    wait $PID
    echo "$PID exited with status $?"
done

# collect all log files up into one
cd $EXPORT_DIR
for i in `find . -name "expdp_JDBEOP1*log" -newer $MARKERFILE`; do
    cat $i > expdp_JDBEOP1_D_FULL_${STARTTIME}.log
done

rm $MARKLERFILE

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

Display horizontal bar graph in HTML table data cell

Using Perl

print ("<td class=\"left\">" .
            "<div style=\"background-color:#8097BE;width:",$allocpercused  ,"%;\" />" .
            "<div style=\"background-color:red;    width:",$automaxpercused,"%;\" />" .
            $tspace .
       "</td>"
);

Depending on which way around the divs are, you get different interpretations

print ("<td class=\"left\">" .
            "<div style=\"border-right:2px solid red; width:",($automaxpercused>99)?(100):($automaxpercused),"%;\" />" .
            "<div style=\"background-color:#8097BE;width:",$allocpercused  ,"%;\" />" .
            $tspace .
       "</td>"
);