Oracle Technology Blog

OraTech Blog provides tips,tricks,scripts and how-to type answers for Oracle related technologies. I use these commands very often, so I thought it would be useful to share with everybody. Please feel free to include your comments/corrections/questions.

Tuesday, June 09, 2009

How to monitor physical standby database

SQL> select * from v$dataguard_stats;

NAME VALUE UNIT TIME_COMPUTED
-------------------------------- ------------------------------ ------------------------------ ------------------------------
apply finish time +00 00:00:00.5 day(2) to second(1) interval 09-JUN-2009 13:37:22
apply lag +00 00:00:50 day(2) to second(0) interval 09-JUN-2009 13:37:22
estimated startup time 15 second 09-JUN-2009 13:37:22
standby has been open N 09-JUN-2009 13:37:22
transport lag +00 00:00:07 day(2) to second(0) interval 09-JUN-2009 13:37:22

$cat check_log_apply.sh
export ORACLE_SID=pfds1
echo $ORACLE_SID
/u01/sq/ora_1/oracle/db/10.2.0/bin/sqlplus -s "/ as sysdba" @/u01/sq/ora_1/oracle/db/10.2.0/scripts/check_log_apply.sql

$cat check_log_apply.sql
set head off
set feed off
spool /u12/log_apply_$ORACLE_SID.log
select substr(VALUE,5,2) from v$dataguard_stats where NAME='apply lag';
exit
spool off


#!/bin/ksh
###############################################################################
# Author: Saminathan Seerangan
###############################################################################

FIELD1=`cat /u12/log_apply_pfds1.log`
echo $FIELD1
if test $FIELD1 -ge 10
then
mailx -s "Log apply delay for PFDS " my_phone_number@vtext.com fi

Friday, June 05, 2009

How to attach or detach ORACLE_HOME to inventory

/u01/sq/ora_1/oracle/db/10.2.0/oui/bin/runInstaller -silent -attachHome -invPtrLoc /u01/sq/ora_1/oracle/oraInst.loc ORACLE_HOME="/u01/sq/ora_2/oracle/db/10.2.0" ORACLE_HOME_NAME="Superstack10g_10204" CLUSTER_NODES="{njqp1dsmdbc03,njqp1dsmdbc04}" -local


/u01/sq/ora_1/oracle/db/10.2.0/oui/bin/runInstaller -silent -detachHome -invPtrLoc /u01/sq/ora_1/oracle/oraInst.loc ORACLE_HOME="/u01/sq/ora_2/oracle/db/10.2.0" ORACLE_HOME_NAME="Superstack10g_10204" CLUSTER_NODES="{njqp1dsmdbc03,njqp1dsmdbc04}" -local

opatch lsinventory -detail -oh /u01/cr/ora_1/oracle/crs/10.2.0