Oracle Metalink useful notes
Note:144152.1 Understanding 9i Real Application Clusters Cache Fusion Recovery
When error comes for temporary tablespace with version <= 9i
Step-1 Run @temp_use.sql as following. Following is the code for temp_use.sql SELECT b.TABLESPACE, b.segfile#, b.segblk#, b.blocks, a.SID, a.serial#, a.username, a.osuser, a.status, c.sql_text FROM v$session a, v$sort_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND a.SQL_HASH_VALUE = c.HASH_VALUE and a.sid not in (select distinct sid from v$mystat) ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks / set pagesize…
Read More “When error comes for temporary tablespace with version <= 9i” »
Establishing trusted relationship between dbmonitor( central monitoring) and monitoring targets.
[1] We have logserver as existing central monitoring server. [2] need to establish other monitoring server same as logserver. [3] so login as oracle copy ~/.ssh from logserver1 to dbmonitor. on dbmonitor: cd .ssh cat id_dsa.pub id_rsa.pub > both_keys then do the following for target servers. pls note that where ssh2 is installed the file…
Finding last recovered file on DR and remove all chanracters before any “/”
sunflower1/800P>ssh sunlight1 “grep “Media ” /EMCdb19/oracle/800L/bdump/alert_800L.log| grep -v grep | grep “arch” | tail -1 ” Media Recovery Log /EMCdb16/archlog/800P/arch0000296866.arc sunflower1/800P> sunflower1/800P>ssh sunlight1 “grep “Media ” /EMCdb19/oracle/800L/bdump/alert_800L.log| grep -v grep | grep “arch” | tail -1 | sed -e s#^.*/##” arch0000296866.arc sunflower1/800P>
replace alphabets using sed
Following will replace anything except numeric digits. sunflower1/800P>echo /db15/archlogbak/800P/arch0000295711.arc | awk -F”/” ‘{print $NF}’ | sed -e ‘s|[^0-9]||g’ 0000295711
Reading parameter file and printing
#/bin/ksh -x for myread in `cat /export/home/oracle/scripts/check_recovery.par` do TARGET_SID=`echo $myread | awk -F”,” ‘{print $1}’` SOURCE_SSH=`echo $myread | awk -F”,” ‘{print $2}’` SOURCE_HOST=`echo $myread | awk -F”,” ‘{print $3}’` SOURCE_ENVFILE=`echo $myread | awk -F”,” ‘{print $4}’` SOURCE_ORACLE_HOME=`echo $myread | awk -F”,” ‘{print $5}’` TARGET_SSH=`echo $myread | awk -F”,” ‘{print $6}’` TARGET_HOST=`echo $myread | awk -F”,”…
checking connectivity between two servers
LASTFILE=`${PRDCMODE} ${PRDHOST} “ls -latr ${PRDARCH}/*.arc” |nawk ‘{print $9}’|tail -1|sed ‘s/[A-Za-z/. ]//g’|sed ‘s/^0*/ /g’` if [ $? != 0 ];then print “${STB1SID}: ${ALIAS} Standby on ${STB1HOST} – CRITICAL ERROR” >> ${NOTOKLOG} 2>/dev/null exit 50 fi
Find long Running Transaction
+++++++++++++longtx_12HR.sh #!/bin/sh . /opt/app/oracle/oracle_env NOTIFY_LIST=dbaoracle@1800flowers.com $ORACLE_HOME/bin/sqlplus -s “/ as sysdba” @/opt/app/oracle/scripts/longtx_12HR.sql if [ `cat /opt/app/oracle/scripts/log/long_transaction_list.lst | grep -i “*****ATLAS NOTIFICATION REQUIRED*****” | wc -l` -n e 0 ]; then mailx -s “LONG RUNNING TRANSACTION ON ATLAS `hostname` on `date` ” $NOTIFY_LIST < /opt/app/oracle/scripts/log/long_transactio n_list.lst fi +++++++++++++longtx_12HR.sql set lines 120 pages 300 set serveroutput on exec...
Check_recovery.sh program to run sqlplus and return its values remotely.
#/bin/ksh -x NOTIFY_LIST=”pmehta@1800flowers.com” SOURCE_ARCH_DEST=/db5/archlog/800P TARGET_SID=UOC SOURCE_SSH=/usr/local/bin/ssh TARGET_SSH=/usr/local/bin/ssh SOURCE_HOST=nyuocdbprod01s TARGET_HOST=uocdbprod21 TARGET_ORACLE_HOME=/oracle/app/oracle/product/10.1 TARGET_ENVFILE=/oracle/app/oracle/oraprocs/oracle_env_UOC SOURCE_ORACLE_HOME=/oracle/app/oracle/product/10.1 SOURCE_ENVFILE=/export/home/oracle/oraprocs/oracle_env_10g_UOC MY_SQL=”select max(SEQUENCE#) from v$log_history;” MY_SQL1=”select count(1) from v$log_history where first_time >= (sysdate – (45/1440));” TARGET_SEQ=`$TARGET_SSH $TARGET_HOST “. $TARGET_ENVFILE; $TARGET_ORACLE_HOME/bin/sqlplus -S / as sysdba
Running select from V$ views from remote server
#/bin/ksh -x NOTIFY_LIST=”pmehta@1800flowers.com” CONNECT_USER=”/ as sysdba” TARGET_ORACLE_HOME=/oracle/app/oracle/product/10.1 MY_SQL=”select max(SEQUENCE#) from v$log_history;” RUNNING=`/usr/local/bin/ssh uocdbprod21 “. /oracle/app/oracle/oraprocs/oracle_env_UOC; $TARGET_ORACLE_HOME/bin/sqlplus -S / a s sysdba