Database Experts

Oracle 11g:Uprade Database server Operating system from AIX 6.1 to 7.1 When there is plan for upgrading OS of database server that time need to do some operation in Oracle binaries. We need to generally relink Oracle binaries after OS upgrade. Please read MOSS   (Doc ID 131321.1). Follow this steps carefully to relink Oracle binaries. 1.Shut down  database properly. 2.O.S Upgrade 3.Relink Oracle Homes binaries. 4.Export ORACLE_HOME=? $ORACLE_HOME/bin/relink all >>relink.out 5.Check any errors or warnings. 6. Start up database normally.

Oracle 10g/11g:Database monitoring Tools/Utility This shell script mainly developed for monitoring database basic things in regular interval. This is home made utilities that inside set of sql queries that give required output. This utility basically useful for getting info of active sessions,inactive session,running jobs,top wait events,block and locking sessions and current load of server. For this utility implement,Need to create set of queries and shell script which execute anywhere. Follow this steps for create home made monitoring tool. 1.create directory on /usr/bin. mkdir psmon 2.Create shell script for execute set of sql queries. #!/bin/ksh clear echo uptime prc=`ps -e -o pid,pcpu|awk '$2>0 {print $1}'` while : do echo "#############################################################################################" echo "|r=Refresh|e=End|S=Query_By_SID|L=LongOP_QUERY|Q=Remaining_Query|t=Topas...

Backup archives on hourly basis To get full recovery of database,We backup archives on hourly basis. For this, Use this script for backup. Check comments for better understanding... #!/usr/bin/ksh # Go to backup location. cd /rmanbck/arch ddy=`date +%a` dt=`date +%Y%m%d` #last archive was backed up sequence lastredono=`ls -rt *arch*. log|tail -1|sed 's/^.*arch_//;s/[.]log//'|awk -F_ '{print $2}'` fno=`expr ${lastredono} \- 2` #From Archive destination, Find the max sequence generations tno=`find /oracle/arch -name "*.arch" -print| sort|tail -1|sed 's@/oracle/arch/@@'|awk -F_ '{print $1}'` FNAME=${dt}_${ddy}_arch_${fno}_${tno} echo $FNAME rman target / trace /rmanbck/arch/$FNAME.log << EOF run { ALLOCATE CHANNEL disk3 DEVICE TYPE DISK; backup as compressed backupset FORMAT '/rmanbck/arch/%T_${ddy}_arch_${fno}_${tno}_%U.set' archivelog from sequence ${fno} unt...

SQL Tuning Advisor Dear User, Read More about SQL Tuning Advisor from Oracle site http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#CHDJDFGE ***Tuning SQL Queries by Oracle SQL Tuning Advisor*** A)SQL Query tuning by SQL_TEXT. 1.Get the Query info from AWR Report like  Top CPU Consuming Queries or SQL Queries by Elapsed time. SQL>select SQL_TEXT,sql_id from v$sqlarea where sql_id='&sql_id'; 2.Get bind variable details from V$SQL_BIND_CAPTURE or DBA_HIST_SQLBIND. SQL>select address, hash_value,name,last_captured,DATATYPE_STRING,VALUE_STRING from  V$SQL_BIND_CAPTURE where sql_id= '&sql_id';  order by last_captured; SQL>select sql_id,name,last_captured,DATATYPE_STRING,VALUE_STRING from DBA_HIST_SQLBIND where sql_id= '&sql_id'   and trunc(last_captured)=trunc(sysdate); 3.Prepare Query and add bind variable values(VALUE_STRING) into SQL Queries.Values sho...