Friday, 10 June 2016

Unix scripts for monitoring

1.To find and delete the files from results of find command

Before deleting files check the output to make sure that you are going to delete  run the command below

find . -name *.gz

Once you are sure then delete the files using following command

find . -name *.gz| xargs rm


2.To find the size of biggest files in your directory

for i in G M K; do du -ah | grep [0-9]$i | sort -nr -k 1; done | head -n 1
 
 
 3.To monitor CPU and Memory of top processes.

This script will run in the loop and wait for 10 sec before capturing memory and cpu of
top 10 process.
Output of the script can be sent out to a file for later analysis. In order to stop 
this script press control C.
 
 #!/bin/bash

while true
do
    echo "-------`date`--------"
    echo "\t\t%MEM\t%CPU"
    ps ax -o comm,%mem,%cpu | sort -nk3 | tail -n 5
    sleep 10
done




Script below is to monitor CPU and memory utilisation by an application. So if an application
has 10 processes then it will sums up the results and provide total memory and cpu utilisation. Frequency and 
amount of script run can be modified by changing parameters within the script.

-----------------------------------------------------------------------------------------------
#!/usr/bin/env bash

#Script to check memory and process usage by a service
#Accepts maximum of 3 arguments (default)
#Usage ./script process-name memory-threshold(in MB) CPU-threshold(in percentage)

###set -xv #uncomment to debug
cpu=$3
mem=$2

if [ $# -ne 3 ]  #script must be run with 3 arguments
then
    echo "Usage: ./script process memory-threshold(in MB) CPU-threshold(in percent)"
    echo "Example: ./script chrome 2000 30"
    exit 1
fi

total_memory=`grep MemTotal /proc/meminfo | awk '{print $2}'`
in_mb=`echo \$total_memory / 1024 | bc`
echo "Total memory is $in_mb MB" 

#monitor cpu usage by a process for n seconds and average the result , this will give better metrics
nPid=`top -b -n 1 | grep $1 | awk '{print $1}' | sort -nr | tail -n1` #get the PPID if the process has sub-processes
nTimes=5 # no of snapshots
delay=1 # with delay in seconds
calc=`top -d $delay -b -n $nTimes -p $nPid \
  |grep $nPid \
  |sed -r -e "s;\s\s*; ;g" -e "s;^ *;;" \
  |cut -d' ' -f9 \
  |tr '\n' '+' \
  |sed -r -e "s;(.*)[+]$;\1;" -e "s/.*/scale=2;(&)\/$nTimes/"`;
cpu_usage=`echo "$calc" |bc -l`

ps -C $1 -o %mem | grep -iv "MEM" | awk '{print $1}' > /tmp/mem  # redirect memory usage to a file

FILENAME=/tmp/mem
total=0
while read LINE
do
        total=`echo \$LINE + \$total | bc`  #if the process has sub processes then sum them
done < $FILENAME

mem_used2=$(bc -l <<< 'scale=4; '$total/100'*'$in_mb'') #calculate memory usage in MB
echo "[$(date '+%Y-%m-%d %T')] CPU % usage for $1:" $cpu_usage "--------" "Memory usage for $1: $mem_used2 MB"
echo "Load averages for the last 1, 5, 15 minutes are:" [$(uptime | awk -v f=8 -v t=10 '{for(i=f;i<=t;i++) printf("%s%s",$i,(i==t)?"\n":OFS)}')]
#echo "$1 memory usage in MB is $mem_used2"

if [[ $(echo "$mem_used2 >= $2" | bc) -eq "1" ]]; then
    echo "$1 critical memory usage alert"
fi
if [[ $(echo "$cpu_usage >= $3" | bc) -eq "1" ]]; then
    echo "$1 critical CPU usage alert"
fi
exit 0
-----------------------------------------------------------------------------------------------


To run the script to monitor cpu and memory for firefox
./monitor.sh firefox 10 10


Monday, 12 October 2015

AWR Analysis

Since the introduction of statspack and then AWR reports, it has become a specialist skill to be able to analyse these reports. This further become novelty if AWR reports are done on Exadata.

Any way I believe below notes can help in analyzing the AWR.

Automatic Workload Repository (AWR) Reports - Start Point (Doc ID 1363422.1)
FAQ: How to Use AWR reports to Diagnose Database Performance Issues (Doc ID 1359094.1)
Further to that I am going to add my own experience.

HOW TO GATHER AWR REPORT

there are two scripts comes with automatic workload repository reports (awrrpt.sql and awrrpti.sql). Both scripts generate similar report but the difference is that awrrpt.sql generate the consolidated report for a database whereas awrrpti.sql work per instance. The reports can be generated as follows.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

You need to follow Top-Down Approach

. Report starts with settings overview
. Next provides Top-5 waits
. Use the Waits to guide further analysis

Thursday, 19 September 2013

OGG-01031 Pump Abending

A fairly common occurance in the Golden Gate world. The pump abends as the target destination becomes full.
In this scenario, I had tried restarting pump process and clearing down space but that didn't resolve the problem.
The pump error message is below


2013-09-19 10:25:42 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, test_ext.prm: EXTRACT TEST_EXT started. 2013-09-19 10:25:42  INFO OGG-01226 Oracle GoldenGate Capture for Oracle, test_ext.prm: Socket buffer size set to 27985 (flush size 27985). 2013-09-19 10:25:42  ERROR OGG-01031 Oracle GoldenGate Capture for Oracle, test_ext.prm: There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file "/oracle/ggate/dirdat/rp/rp000213" (error 13, Permission denied)). 2013-09-19 10:25:42  ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, test_ext.prm: PROCESS ABENDING.

In this case we have to manually tell Extract to rollover

alter extract TEST_EXT etrollover
2013-09-19 10:57:19 INFO OGG-01520 Oracle GoldenGate Command Interpreter for Oracle: Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.


Run info pumpname to get the new trail file name.

At the target end, the replicat should have parsed all the data received until the drive became full and data stopped coming through.
To let the target know to switch to the new file, use below command.

alter replicat TEST_EXT ,EXTSEQNO 214 , EXTRBA 0 --- replicat was reading the seqno 213. moved to 214.





Installing Grid Infrastructure for a Standalone Server

There is a very good article with a tutorial to install Grid Infrastructure on a Standalone Server

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/install/gridinstss/gridinstss.htm

Tuesday, 3 September 2013

DROP DATABASE VIA RMAN

If drop database command is run under RMAN then it will drop datafiles, online redo logs ,controlfiles , backups and also unregister database from recovery catalog. Only thing I noticed that was left unclean was flashback logs that has to be deleted manually.

In order to drop database within RMAN, make sure that database is in mount state and in restrictive mode.

RMAN> CONNECT TARGET /


connected to target database: TESTDB1 (DBID=39523451)

RMAN> STARTUP FORCE MOUNT
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;


NOPROMPT will not ask for confirmation so if you want to be sure then use
 RMAN> DROP DATABASE INCLUDING BACKUPS;

Above command will use controlfile of the database so in order to user catalog use

RMAN > CONNECT TARGET SYS@TESTDB CATALOG RMAN@RMANCAT


 

Monday, 2 September 2013

DATA PUMP COMMAND

Data pump was introduced in oracle 10G and has become very common tool in 11G and 12C databases.

quick reference to use data pump export and import command with sysdba privileges is


For data pump you need to create a directory in database
$sqlplus / as sysdba
sql> create or replace directory DUMP_DIR as '/tmp/data_dump';


$expdp \'/ as sysdba \' dumpfile=exp_test_user.dmp logfile=exp_test_user.log directory=DUMP_DIR schemas=TEST_USER


Similar command for data pump import utility

$impdp \'/ as sysdba \' dumpfile=exp_test_user.dmp logfile=imp_test_user.log directory=DUMP_DIR tables=TEST_USER.TEST


If you just want to gnerate script for object creation then

$expdp \'/ as sysdba \' dumpfile=exp_test_user.dmp logfile=exp_test_user.log directory=DUMP_DIR schemas=TEST_USER content=METADATA_ONLY


$impdp \'/ as sysdba \' dumpfile=exp_test_user.dmp logfile=imp_test_user.log directory=DUMP_DIR tables=TEST_USER.TEST
content=METADATA_ONLY sqlpfile=testscript.sql


 

Golden Gate commands -- Quick Reference

GGCGI is golden gate command line interface which can be invoked by running
./ggsci


TO CHECK PROCESS
1> info all
or
1> show all

TO SEE DETAIL OF SPECIFIC PROCESS
2> info extract ext1


TO CHECK VERSION

to display operating system and database version information. Use DBLOGIN to connect to the database first
GGSCI  11> dblogin userid ggadmin, password gg_pwd
Successfully logged into database.
GGSCI 12> versions
Operating System:
Linux
Version #1 SMP Mon Mar 02  08:19:03 EDT 2012 Release 2.6.18-194.el5PAE
Node: linux01.oncalldba.com
Machine: i686
Database:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production



TO RUN SHELL SCRIPT WITHIN GGSCI

GGSCI  13> shell ls -l /home/oracle/goldengate/dirdat/
total 50588
-rw-rw-r- 1 oracle oinstall     5962 Jul 16 14:57 tst000000
-rw-rw-r- 1 oracle oinstall 9976629 Jul 19 13:09 tst000001

OBEY: to process a file that contains a list of GoldenGate commands. OBEY is useful for executing commands that are frequently used in sequence.
Suppose we want to run some Goldengate commands and execute those from a Unix shell script which can be called via say cron.
So we have a start_goldgate.sh shell script which will use the GGSCI command OBEY to call a text file which has the Goldengate commands which we would like to run in sequence.
[oracle@linux01 goldengate]$ cat start_goldgate.sh
cd /home/oracle/goldengate
./ggsci < EOF
OBEY /home/oracle/goldengate/startup.txt
EOF
[oracle@linux01 goldengate]$ cat startup.txt
START MANAGER
START EXTRACT EXT3
START EXTRACT DPUMP
INFO ALL
[oracle@linux01 goldengate]$ ./start_goldgate.sh
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 08:50:50

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.



GGSCI  1>
GGSCI  2> START MANAGER


Manager started.


GGSCI  3> START EXTRACT EXT3

EXTRACT EXT3 is already running.


GGSCI  4> START EXTRACT DPUMP

EXTRACT DPUMP is already running.


GGSCI  5> INFO ALL


Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:01
EXTRACT     ABENDED     EXT2        00:00:00      172:36:58
EXTRACT     RUNNING     EXT3        00:00:00      00:00:04
 
 
 

Thursday, 22 August 2013

ORACLE RAC 11g R2 Commands


This is collection of commands works on 11g R2. I found that lots of command have changed from 11G R1 to 11G R2 so this list can be used to quickly find relevant command.

1. To register a listener in CRS

$srvctl add listener -l LISTENER_TEST1 -p TCP:1521/IPC:LISTENER_TEST1 -o /ora/grid/product/11.2.0.3

2. To start the new listener

$srvctl start listener -l LISTENER_TEST1

3. To register a database with new listener

SQL> sqlplus / as sysdba
connected.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST1)))' scope=both;

SQL> show parameter local_listener



4. The most used to command "crs_stat -t" is gone (deprecated) on 11gR2. Instead you

crsctl status resource [the output will be status of all resources]

You can still use the "-t" option to get a tabular report though ;)

crsctl status resource -t

Specify resource name "oracle.mydb.vip" to get the status of any particular resource -
crsctl status resource oracle.mydb.vip
 
 

Deprecated Command
Replacement Commands
crs_stat

crsctl check cluster -all
crsctl stat res -t
crs_register



crsctl add resource
crsctl add type
crsctl modify resource
crsctl modify type
crs_unregister
crsctl stop resource

crsctl modify resource resource_name -attr
"AUTO_START=never"
crs_start


crsctl start resource
crsctl start crs
crsctl start cluster
crs_stop


crsctl stop resource
crsctl stop crs
crsctl stop cluster
crs_getperm

crsctl getperm resource
crsctl getperm type
crs_profile





crsctl add resource
crsctl add type
crsctl status resource
crsctl status type
crsctl modify resource
crsctl modify type
crs_relocate
crsctl relocate resource
crs_setperm

crsctl setperm resource
crsctl setperm type
crsctl check crsd
crsctl check crs
crsctl check cssd
crsctl check css
crsctl check evmd
crsctl check evm
crsctl debug res log resource_name:level
crsctl set log
crsctl set css votedisk
crsctl add css votedisk

crsctl delete css votedisk

crsctl query css votedisk

crsctl replace css votedisk
crsctl start resources
crsctl start resource -all
crsctl stop resources
crsctl stop resource -all
 

Wednesday, 21 August 2013

To Calculate and find reason of High Redo generation

To know the exact reason for the high redo, we need information about the redo activity and the details of the load. Following information need to be collected for the duration of high redo generation.


1] To know the trend of log switches below queries can be used.

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select trunc(first_time, 'HH') , count(*)
2 from v$loghist
3 group by trunc(first_time, 'HH')
4 order by trunc(first_time, 'HH');


We can also get the information about the log switches from alert log (by looking at the messages 'Thread 1 advanced to log sequence' and counting them for the duration), AWR report.


a) AWR Report

-- Create an AWR snapshot when you are able to reproduce the issue:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

-- After 30 minutes, create a new snapshot:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

-- Now run
$ORACLE_HOME/rdbms/admin/awrrpt.sql


b) Statspack Report

SQL> connect perfstat/
SQL> execute statspack.snap;

-- After 30 minutes
SQL> execute statspack.snap;
SQL> @?/rdbms/admin/spreport


In the AWR/Statspack report look out for queries with highest gets/execution. You can check in the "load profile" section for "Redo size" and compare it with non-problematic duration.


2] We need to mine the archivelogs generated during the time frame of high redo generation.

-- Use the DBMS_LOGMNR.ADD_LOGFILE procedure to create the list of logs to be analyzed:

SQL> execute DBMS_LOGMNR.ADD_LOGFILE('',options => dbms_logmnr.new);
SQL> execute DBMS_LOGMNR.ADD_LOGFILE('',options => dbms_logmnr.addfile);

-- Start the logminer

SQL> execute DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

SQL> select operation,seg_owner,seg_name,count(*) from v$logmnr_contents group by seg_owner,seg_name,operation;


Please refer to below article if there is any problem in using logminer.
Note 62508.1 - The LogMiner Utility

We can not get the Redo Size using Logminer but We can only get user,operation and schema responsible for high redo.


3] Run below query to know the session generating high redo at any specific time.

col program for a10
col username for a10
select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like 'redo%'
order by value;

Oracle RAC node killed by CRS

I had faced this error message


[    CSSD]2012-01-12 15:24:19.352 [1199618400] >TRACE:   clssnmWaitThread: thrd(2), timeout(1000), wakeonpost(0)
[    CSSD]2012-01-12 15:24:19.353 [1220598112] >ERROR:   ###################################
[    CSSD]2012-01-12 15:24:19.353 [1220598112] >ERROR:   clssscExit: CSSD aborting from thread clssnmRcfgMgrThread
[    CSSD]2012-01-12 15:24:19.353 [1220598112] >ERROR:   ###################################

First of all location of log file is $CRS_HOME/log//cssd/
and file name is ocssd.log

There could be many reason for this error but in a nutshell CSSD has killed the local host connection to rest of RAC cluster. In this case, you will notice a hint on a line above ERROR which says that timeout is happening. Further investigating the log files I noticed that heartbeat between nodes is not fast enough. 
When I checked interface used by interconnect then noticed that it is running on slow speed.

By Changing speed of network interface resolve this problem.
 

Dropping Disk from ASM Group

Dropping disk in ASM by this method will mark the disk for drop and then start doing rebalance. Once it completes moving data off the ear-marked disks then it will remove that disk from diskgroup.
This is different concept than dropping disks on OS and storage level, as in both disk are unavailable straight away after comman executes.

This also helps to do addition and deletion of disks by doing one rebalance. So if new disks are added and straight after that old disks are dropped in ASM then ASM will only do one rebalance considering both new and removed disks.

Alter diskgroup DATA drop disk '/dev/data_disk1';
Diskgroup altered.



 

Calculate how much time/work is left to finish by RMAN

This command is specific to monitor RMAN activity but can be modified easily to monitor activity by other sessions

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

 
or use  this

select OPERATION,MBYTES_PROCESSED from v$rman_status where status='RUNNING';

Monday, 20 August 2012

ORA-15401 Diskgroup Space exhaust Error message in ASM


Symptoms
An ORA-15041 ( diskgroup space exhausted ) occurs during a rebalance or adding of a disk (which implicitly does a rebalance unless told otherwise)
Cause
One or more disks have V$ASM_DISK.FREE_MB are below the threshold level needed to be able to do a successful rebalance (50-100mb)
Solution
1) Determine which (if any) disks contain no free space (ie are below the threshold)
select group_kfdat "group #",
number_kfdat "disk #",
count(*) "# AU's"
from x$kfdat a
where v_kfdat = 'V'
and not exists (select *
from x$kfdat b
where a.group_kfdat = b.group_kfdat
and a.number_kfdat = b.number_kfdat
and b.v_kfdat = 'F')
group by GROUP_KFDAT, number_kfdat;
If no rows are returned ... the following query can also be used

select disk_number "Disk #", free_mb
from v$asm_disk
where group_number = *** disk group number ***
order by 2;
If rows are returned from the first query ... or FREE_MB is less than 100mb in the second ... then there is probably insufficient disk space to allow a rebalance to occur ... Note the Disk #'s for later


2) Determine which files have allocation units on the disk(s) that are on exhausted disks
select name, file_number
from v$asm_alias
where group_number in (select group_kffxp
from x$kffxp
where group_kffxp=*** disk group number ***
and disk_kffxp in (*** disk list from #1 above ***)
and au_kffxp != 4294967294
and number_kffxp >= 256)
and file_number in (select number_kffxp
from x$kffxp
where group_kffxp=*** disk group number ***
and disk_kffxp in (*** disk list from #1 above ***)
and au_kffxp != 4294967294
and number_kffxp >= 256)
and system_created='Y';
 


3) Free up space so that the rebalance can occur

Using the file list from #2 above ... we will need to either drop or move tablespace(s)/datafile(s) such that all disks that are exhausted have at least 100mb free ...

NOTE ... the AU count above ... should relate to 1mb AU size ... so if a single file ... with at least 100 au's can be dropped or moved ... this
should be sufficient to free up enough space to allow the rebalance to occur

Droppable tablespaces may be things like:
* temporary tablespaces
* index tablespaces (assuming you know how to rebuild the indexes)

If none of the tablespaces are droppable then the tablespace(s)/datafile(s) will need to be
* moved to another diskgroup (at least temporarily) ...
* dropped using RMAN (with the database shutdown) and will be restored later

 
4) Check to see if there is sufficient FREE_MB on the problem disks
select disk_number "Disk #", free_mb
from v$asm_disk
where disk_group = *** disk group number ***
and disk_number in (*** disk list from #1 above ***)
order by 2;

If the disks do not have at least 100mb free ... repeat #3 above
 
5) Rebalance
alter diskgroup rebalance power ;
 

6) Monitor the progress of the rebalance until finished
select sofar "AUs moved So Far", est_work "Aprox AU's to be moved"
from v$asm_operation
where group_number = *** disk group number ***;

Continue to monitor until the rebalance has completed
 

7) Check the balance of the disks
select disk_number, total_mb-free_mb
from v$asm_disk
where group_number = *** disk group number ***;
TOTAL_MB - FREE_MB = amount of space used on the disk

The amount of space used on each disk (without regard to size) should be approximately the same (within a few megabytes)

 

8) Put things back where they wereIf the datafiles were moved ... reverse the process and return them to their original diskgroup and location

if the tablespace(s) were dropped ... recreate them ... if needed

If the datafiles were dropped ... restore them using RMAN


Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.

Thursday, 26 July 2012

Oracle Enterprise Manager Agent Upload Troubleshooting


There are times when Oracle Enterprise Manger agent stop uploading.


There are many reasons for the agent not being able to upload.

Examine the agent

1. First I always check the status of the agent
 
emctl status agent

2. Examine status output, may see that the agent is not uploading by see
 
Last successful upload : (none)
Last attempted upload : (none)
Total Megabytes of XML files uploaded so far : 0.00
Number of XML files pending upload : 828
Size of XML files pending upload(MB) : 56.61
Available disk space on upload filesystem : 8.50%
Last successful heartbeat to OMS : 2012-07-21 08:45:39


3. Next I always attempt to do a manual upload for the agent to check/verify upload problem
 
emctl upload agent

4. Check the following log file for errors
 
$AGENT_HOME/sysman/log/emagent.trc
Other logfiles are in the $AGENT_HOME/sysman/log/ directory.

In Most cases it is due to a bad .xml file or unable to contact the management service. If it is due to contacting the management service you will need to fix the OMS Service or connectivity to the service, however if it is due to a bad xml file you can then try to just remove that file from the upload location. I usually save the file to another location so that I can send the file to Oracle Support where hopefully they can tell me why the file would not upload.Once you have removed the file from the upload location you can attempt a manual upload again.emctl upload agent Check the logfile again for errors. you can repeat for subsequent bad xml files, but if all else fails we can always clear the agent completely. Keep in mind you will lose data from the pending xmls therefore the data will not make it to the Grid Control Repository.
Clear the agent up

1. Stop the agent on the target node
 
emctl stop agent
2. Delete any pending upload files from the agent home
 
rm -r $ORACLE_HOME/sysman/emd/state/*
rm -r $ORACLE_HOME/sysman/emd/collection/*
rm -r $ORACLE_HOME/sysman/emd/upload/*
rm $ORACLE_HOME/sysman/emd/lastupld.xml
rm $ORACLE_HOME/sysman/emd/agntstmp.txt
rm $ORACLE_HOME/sysman/emd/blackouts.xml

3. agent clearstate
 
emctl clearstate agent

4. Start the agent again
 
emctl start agent

5. Force an upload to the Oracle Management Server/Service (OMS)
 
emctl upload agent


There could be another reason when you delete an agent and try to reconfigure it in OMS. Issue arise when previous delete is not completed successfully.

You can have following error messages.


EMD upload error: Failed to upload file A0000001.xml: Fatal Error.
Response received: 500|ORA-20618: The specified agent is in the process of being deleted from the repository, wait for deletion to complete before restarting the agent.(agent name = dbp1405.xx.xx.xxxxx.xxx:1830)(agent guid = 35F8188A75ABF6AFAAA6871B64D1C0B1)
ORA-06512: at "SYSMAN.TARGETS_INSERT_TRIGGER", line 30
ORA-04088: error during execution of trigger 'SYSMAN.TARGETS_INSERT_TRIGGER'


This is due to duplicate entry in OMS database.

I tried to delete that entry manually in OMS database by connecting as SYSMAN user


SQL> exec mgmt_diag.PurgeOrphanTarget(HEXTORAW('35F8188A75ABF6AFAAA6871B64D1C0B1'));
BEGIN mgmt_diag.PurgeOrphanTarget(HEXTORAW('35F8188A75ABF6AFAAA6871B64D1C0B1')); END;
*
ERROR at line 1:
ORA-20000: Target is in pending delete state
ORA-06512: at "SYSMAN.MGMT_DIAG", line 1437
ORA-06512: at line 1

 This means that it can not be deleted manually due to previous attempt still running. In order to find when it started then I ran this query

select delete_request_time, delete_complete_time, last_updated_time from mgmt_targets_delete where target_name='dbp1405.xx.xx.xxxx.xxx:1830' AND target_type='oracle_emd';

DELETE_REQUEST_TIME           DELETE_COMPLETE_TIME          LAST_UPDATED_TIME
----------------------------- ----------------------------- -----------------------------
01-jun-2012 11:48:36                                        01-jun-2012 11:48:36
  

This means that previous deletion hung for a long time.


There are couple  methods of progressing it further from this stage is to use repvfy utility to remove duplicate host from repository. If repvfy won't work then you can remove it manually by using the following steps.

Stop the agent

emctl stop agent

Then login to repository database using SYSMAN user the find the details about GUID for that object from MGMT_TARGETS_DELETE  table or use this statement.

SQL> select TARGET_NAME,TARGET_TYPE,EMD_URL from MGMT_TARGETS_DELETE WHERE TARGET_GUID='35F8188A75ABF6AFAAA6871B64D1C0B'

Check for all below tables:

Main tables are:

MGMT_TARGETS_DELETE
MGMT_EMD_PING
MGMT_EMD_PING_CHECK
MGMT_AVAILABILITY
MGMT_AVAILABILITY_MARKER
MGMT_CURRENT_AVAILABILITY

Also some reference tables,request you to check the below tables also:

MGMT_AGENT_SEC_INFO
MGMT_BLACKOUT_PROXY_TARGETS
MGMT_CURRENT_METRICS
MGMT_LAST_VIOLATION
MGMT_METRICS_1DAY
MGMT_POLICY_ASSOC_EVAL_DETAILS
MGMT_POLICY_ASSOC_EVAL_SUMM
MGMT_PURGE_POLICY_TARGET_STATE
MGMT_RT_BOOTSTRAP_TIMES
MGMT_STRING_METRIC_HISTORY
MGMT_TARGET_PROPERTIES
MGMT_VIOLATIONS


If in any table you find the entry for agent dbp1405.nl.eu.abnamro.com so please delete the target

example:

SQL> delete from MGMT_TARGETS_DELETE where TARGET_GUID='35F8188A75ABF6AFAAA6871B64D1C0B'

This should delete the duplicate or old entry from OMS and allow you to continue securing and uploading agent.



  

Wednesday, 25 January 2012

Oracle RAC node killed by CRS

I had faced this error message


[    CSSD]2012-01-12 15:24:19.352 [1199618400] >TRACE:   clssnmWaitThread: thrd(2), timeout(1000), wakeonpost(0)
[    CSSD]2012-01-12 15:24:19.353 [1220598112] >ERROR:   ###################################
[    CSSD]2012-01-12 15:24:19.353 [1220598112] >ERROR:   clssscExit: CSSD aborting from thread clssnmRcfgMgrThread
[    CSSD]2012-01-12 15:24:19.353 [1220598112] >ERROR:   ###################################

First of all location of log file is $CRS_HOME/log//cssd/
and file name is ocssd.log

There could be many reason for this error but in a nutshell CSSD has killed the local host connection to rest of RAC cluster. In this case, you will notice a hint on a line above ERROR which says that timeout is happening. Further investigating the log files I noticed that heartbeat between nodes is not fast enough. 
When I checked interface used by interconnect then noticed that it is running on slow speed.

By Changing speed of network interface resolve this problem.
 

Tuesday, 3 January 2012

BOOK REVIEW: Oracle 11g R1/R2 RAC Essentials

ORACLE 11g R1/R2 RAC ESSENTIALS
ISBN 978-1-849682-66-4



This is always a need of comprehensive book on this topic. I think a very good effort has been done in writing the above mentioned book. I have bought this book and as I am going through chapters, I'll update this page with my observation.


In First chapter , under the topic of High Availibity: Oracle 11g R1 RAC authors has mentioned that RAC is not a true disaster recovery solution because it does not protect against site failure or database failure.
I think this point need further clarification. In my opinion it depends on your setup, I have recently created a RAC instance using ASM disks with Normal redundancy on two physically separated location. So in this case not only nodes are on different data center but storage is also in two different data center. So ASM has diskgroup has two failgroup of one on each site using stretched SAN. As instance is combination of background processes where is database is combination of data on storage. In the above mentioned setup both database and instance will be available in case of site failure.

Further points still need to come .....................

Friday, 23 December 2011

Connection 1,***,6201 connect (Connection refused)

PROBLEM:Con­nec­tion 1,***,6201 con­nect (Con­nec­tion refused) where *** is the server’s ip address might occur on Ora­cle Appli­ca­tion Server 10g Infra­struc­ture or MiddleTier. I faced this problem when trying to apply patch on OID infrastructure.


SOLUTION: Try to understand where you problem is and then try this solution
This prob­lem is due to file per­mis­sions on the Appli­ca­tion Server direc­tory , try a chmod –R oracle:dba on the direc­tory and then run root.sh on both infra­struc­ture and portal. For my specific problem I ran root.sh from OID home which resolved the problem.




Thursday, 24 November 2011

RMAN backup details require to restore a database

 When you have to restore a database from RMAN backup, last thing you want to do is to identify which backups, tapes and dates you need to use for database restore. There are few sql commands that can be very handy in order to extract those details.


column handle format a60
column media format a10
column tag format a20
column start_time format a20
column name format a10
set lines 200
    select d.name,    p.tag,   p.handle , p.media ,    s.incremental_level "LEVEL",
     to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
     s.elapsed_seconds/60 "MINUTES",   p.backup_type
      from  RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s  
      where
        d.name='ELPM'  and
        d.db_key = p.db_key  and
        s.db_key = p.db_key     and
        p.bs_key = s.bs_key      and
       s.start_time between to_date('08-SEP-11 00:00','dd-MON-yy hh24:mi')
       and to_date('10-SEP-11 00:00','dd-MON-yy hh24:mi')
      Above script can give you backup details by providing database name and dates after connecting to RMAN repository.
      Another method is to connect to RMAN prompt using control file

      $>rman connect target / nocatalog

      RMAN> restore database preview summary;





      Changing VIP address in Oracle RAC 11g R1

      I just want to share my experience with changing ip addresses of running Oracle RAC.
      Before I go into details , let me explain my environment. This is real-life experience on a client site which was running 11G R1 ASM and CRS but databases are running on 10G R2 on Red Hat release 4 (RHEL4). So ip address changes require on CRS level.

      First of all I just go through the steps that I have taken according to the issue i was facing. Later I'll try to add other steps/command to cover as much as possible around this topic.


      This case was simple as only thing that was changed was ip address and subnet and LAN remain the same.

      1. Stop all databases on all RAC nodes by using this command
      srvctl stop database -d poem
      srvctl stop database -d poid
      srvctl stop database -d prman

      2.Stop all nodeapps on every node
      NODE 1

      srvctl stop nodeapps -n dbm1301

      NODE 2
      srvctl stop nodeapps -n dbm1401


      3. Make sure that ip addresses has been changed in dns for vip.
      ping dbm1301-vip

      checks that it return new ip address.

      4. Check that which bond this vip is using by runing ifconfig

      /sbin/ifconfig

      if it is using bond0:1 then in CRS configuration you have to specify bond0 which will create an alias of bond0:1. In my case bond0 is bonded with eth0 which is using production LAN.

      5. Run the following command just in case to enforce the new ip address

      /ora/product/11.1.0/crs/bin/srvctl modify nodeapps -n dbm1301 -A dbm1301-vip/255.255.255.0/bond0


      6. restart CRS to pick up new ip address
      crsctl stop crs

      crsctl start crs


      7. Restart listener so that it picks up new ip address too.
      srvctl stop listener -n dbm1301
      srvctl start listener -n dbm1301

      8. repeat the same steps on other node

      9. Start nodes apps and databases
      srvctl start nodeapps -n dbm1301

      srvctl start database -d poem
      srvctl start database -d prman
      srvctl start database -d poid

      10. check that all services are running
      srvctl status service -d poem
      if need to start it then use srvctl start service -d -s  

      This has changed ip address on a node which was running RAC successfully.



      Another brilliant article is here
      http://www.db-nemec.com/RAC_IP_Change.html